How to Check if the Contents of Two Ranges/Worksheets is Different

You are not sure if some ranges of data in your worksheet, or in two different worksheets, contain the same data. How to  figure it out? And if they are different, is the difference significant?

Use ARRAY formula to check it out. You can count the differing cells using e.g. this kind of array formula in Sheet1 to compare with cell contents in Sheet2:

=SUM(IF(B2:G100=Sheet2!B2:G100,0,1))

Remember that to enter it you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets.

Also, make sure that the size of compared ranges is the same. Location, however, can be different, like e.g. A2:F20 in one worksheet and C5:H23 in another.

To COUNT any Characters or Strings in any Range of Cells

Use  an ARRAY formula. You can find out the number of occurrences of anything (e.g. BS, 2016, s, graph, three days, 50%, etc.) within a given cell or a range of cells.

Assuming that your data range is e.g. A2:D100, select any cell outside that range and type the formula similar to this one:

 =SUM((LEN(A2:D100)-LEN(SUBSTITUTE(A2:D100,"Fig.","")))/LEN("Fig."))

and simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets. And the cell where you array-entered the formula will show the result, like here in cell G1:

Extract Formulas to External File

If your workbook/worksheet is full of Excel formulas it may be worth to keep track of them in one place, in a compact listing, in a separate text file.

Here is a simple short macro that serves the purpose:

Sub ListFormulas()
Dim rng As Range
'Extracts all formulas from a worksheet to a text file (Notepad/Wordpad)
Open "C:\Users\Adam\Desktop\XFormulas.txt" For Output As #1
For Each rng In Sheets("Formulas").UsedRange.Cells

If rng.Formula <> "" And Left(rng.Formula, 1) = "=" Then
           Print #1, rng.Address; Tab; rng.Formula
End If

Next
Close #1
End Sub

Open Workbook with Startup Worksheet

If you create a workbook that will be opened and used frequently by yourself and - especially - if you design it for sharing with other users, you may want to start it in a compelling, attractive way.

I'm presenting here an example of initializing a worksheet which you might want to appear when the workbook is being started. The workbook is macro-driven, so it must be saved as Macro-Enabled Workbook. This is how the worksheet looks like after loading with a randomly selected photo (this is a screenshot of the left part of my computer screen):

Function N() = Insert Comment: TRUE or FALSE

When we return to our workbook created some time ago and try to revive/use it, it quite often happens that we've forgotten what we had intended to achieve by composing some complex formulas or entering even simple data points. We can avoid such situations in the future by adding comments or notes to some cell contents.

The Insert Comment feature is OK, but in most cases the better choice is to use the N() function, as illustrated below:

 

TIPS for CLICKS: About using Mouse in Excel

Hello, mouse!

• Click means here: left button click 
• Fill Handle in Excel means: small black rectangle in the lower right corner of a cell/range selection.

The following table provides 30 helpful tips related to the use of mouse in the Excel app. It covers the most useful mouse 'shortcuts' enhancing user's skills and productivity, and explains how to use them. It's worth to utilize them in your work.

Keyboard Functions: Cheat Sheet

All in one place, practical reference to your keyboard functions. Very helpful for every user of Excel, especially in situations when you can't use your mouse.

This is what you can do with your keyboard Function Keys, when combined also with control keys (Ctrl, Alt, Shift).