26 October, 2021

Find ADDRESSES of Specific CELL CONTENTS in Excel Workbook

Let's say we are dealing with Excel table several columns wide and hundreds and hundreds, or even thousands of rows long. Just for illustration I'm providing here a small 'fake' table, a fragment of a big one:

Working on such a large table we may need to find addresses of cells containing some specific values, name, date, number, etc., and we need to look for them quickly.

25 October, 2021

Header/Footer: What can be entered there?

Excel offers the following built-in headers and footers. They can be added to your worksheet easily, with a single mouse click.

However, we're not limited to these options. In fact, you can enter any text/string into your worksheet cell and add it to any part of the header or footer with just one line of VBA code of a macro. E.g., this line will add contents of cell B2 to the left part of a header:


Such a line can be included in the VBA code I'm presenting at the bottom of this post.

20 October, 2021

In Reverse: Going Backwards with ARRAY formulas

Do you need to type or use expressions or numbers in reverse?

Trivial or not, in fact we need sometimes to display or read some numbers or text / expressions in reverse. There are several options available in Excel to do just that. You can do it in a macro way or using some known Excel formulas. E.g. you may use the following ARRAY formula for reversing digits in a number:


However, at a closer look, the formula works fine with digits only. If you enter 12481632 into cell B2, it returns 23618421, but after entering 2.35 it returns error #VALUE! . In addition, it doesn't return trailing zeroes.

So, after some experimenting, I've created my own dynamic ARRAY formula. Here is its exemplary application:

The formula reverses whatever string, number or a mix of letters and digits you'd like to reverse. Just remember: this is an ARRAY formula, so - after typing it in - you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER on your keyboard.


17 October, 2021

TOP TIP: Counting Cells Containing Numbers Meeting Specific Criteria

Most of the time we organize our data in orderly Excel tables making their analysis pretty straightforward. Sometimes, however, we may have to deal with chaotic sets of numbers located all over different ranges/worksheets, like in this somewhat bloated example:

How to count and analyse them? Couple of examples follow.

14 October, 2021

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:


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.

13 October, 2021

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:


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:

12 October, 2021

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
Close #1
End Sub

10 October, 2021

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):

08 October, 2021

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:


04 October, 2021

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.

01 October, 2021

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).