Search Not Just Numbers


Tuesday, 17 November 2015

Excel Tip: An introduction to dynamic ranges

A few months ago I wrote a post  introducing the use of ranges in Excel:

Excel Tip: A short introduction to named ranges

In that post I alluded to dynamic ranges but didn't really say much about them, so I thought I would expand a little on the subject now.

As the name suggests, dynamic ranges are ranges that are not fixed. The range referred to varies based upon either the cell in which the reference is used or a variable (or variables) referred to in a formula, or both.

If we go to name a new range using the Name Manager on the Formulas ribbon (if you need a refresher on this, please visit my earlier post), and in the "Refers to" box, we highlight a range, then by default this range is absolute (Excel automatically inserts the dollar signs to fix the row and column references).

So, if we type "Test" in the Name field and delete the contents of the "Refers to" box, then click in cell A1 of Sheet1 (while still in the "Refers to" box), Excel populates the "Refers to" box with:


This means that if we refer to the range "Test" anywhere else in the workbook, it will look at the contents of cell A1 of Sheet1. This is a normal (fixed) range.

Now let's create a dynamic range.

We will follow the same process to create second range, but this time, ensure that cell B1 is selected before we start. Again create a new range in the Name Manager but call it Test2 this time. Delete the contents of the "Refers to" box again, then, again, click in cell A1. This will populate the "Refers to" box, as before, with:


This time though, before clicking OK, use the F4 key to remove the dollar signs.

The "Refers to" box should now read:


This is now a relative reference, relative to the cell we had selected prior to defining the range (B1). The range "Test2", will now refer to the contents of the cell immediately to the left of the cell that you use it in (as A1 is the cell immediately to the left of B1).

Try typing the formula =Test in multiple cells. This will always return the contents of cell A1. But type =Test2 in any cell, and it will return the contents of the cell to its left. "Test2" is therefore a dynamic range - the cell(s) it refers to change depending on where it is used.

Note that this is not just the case with a single cell, we can refer to a number of cells.

Let's take an example. Let's say that we have a 12 month budget, with the 12 monthly values in columns B to M. Click in cell N1 and name a new range in the Name Manager called "FullYear" and type the following into the "Refers to" box:


We can either type this in, or as before, highlight the cells B1 to M1, but then use the F4 key on each of the references to remove the dollar from the row reference.

Note that I have left the dollars on the column references. This means that the row will change, but the columns will stay fixed. This means that we can use the formula =SUM(FullYear) and it will always sum columns B to M of the current row.

Sticking with this same example, we can use the OFFSET function to extend this further and allow us to have dynamic ranges for the current month's balance, and the year to date balance.

If we store the number of the current month (1 to 12) in cell P1, then we can name a range called "MonthBalance" by clicing in cell N1, and creating the range as before, but typing the following formula in the "Refers to" box:


Note the use of the dollars, The column of B1 is fixed but the row is left relative, whereas P1 is entirely fixed.

This will return the contents of the cell P1-1 cells to the right of column B, on the same row. So if the month number entered in P1 is 3, P1-1 =2. As column B is the month 1 balance, 2 columns to the right in column D is the month 3 balance!

So we can enter =MonthBalance in any cell and it will return the current month's balance from that row.

We can define the range "YTD" as:


This will return a range P1 wide and 1 row high starting in column B of the current row, i.e. all of the months to date. As this will usually be more than one cell, you will need to use SUM to total the range.

We can now use =SUM(FullYear), =MonthBalance and =SUM(YTD), to give us these totals on any row of the sheet. In addition, MonthBalance and SUM(YTD) will change depending on the month number entered in P1.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 27 October 2015

Excel Tip: Return the formula text from a cell, rather than its value

Just a short little tip this week, due to a hectic diary.

When you reference a cell in Excel, you will get its value, but what if you want to return the formula itself?

Excel, as usual, provides a way to do this.

NB: This function is only available in Excel 2013 onward. If you need this functionality in earlier versions of Excel, Bill Jelen has a simple video on how to create a User-defined function to do it.

Let us say that cell A1 contains:


If we enter the following into cell C1:


then cell C1 will show the same number that A1 shows, i.e. the sum of the numbers in cells B1 to B10.

What if we wanted to see the actual formula in cell A1?

We can use the FORMULATEXT function in C1:


This will return the text:


This may be useful for its own sake (for example, to show how numbers are calculated alongside the numbers themselves), or to return the text of the formula, so that you can manipulate it.

A couple of things to note:

  • FORMULATEXT will return #N/A if:
    • the cell does not contain a formula
    • the formula is more than 8,192 characters!
    • worksheet protection doesn't allow the formula to be shown
    • it refers to an external workbook that is not open.
  • If the range argument is more than one cell, it will return the formula in the top left cell of the range.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 13 October 2015

Excel Tip: Show negative numbers in brackets

Working with accountants, one of the requirements I often get asked for, is to show negative numbers in brackets.

Surprisingly, this is not one of the standard number formats in Excel, not even if you choose the Accounting format!

Fortunately, however, this can be remedied using a custom number format.

It is usually easier to start with a format that is close to what you want. We will start with the Accounting format, to 2 decimal places, and no currency symbol.

To select this, right-click the cell, choose Format Cells and, on the Number Format tab, selecting Accounting. In the information to the right, select 2 decimal places and None (for the currency symbol).

Click OK to store this as the number format for that cell (or the range of cells selected).

Now follow the same sequence again, but this time choose Custom as the Number Format. You will see the code for the existing format (as previously selected) in the box at the top of the section on the right:

_-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-

We will edit this code to give us our brackets.

The first thing that we need to be aware of is the different sections of the code. This format uses the maximum four sections, each section being separated by semicolons.

The sections are as follows:

  1. Positive Numbers
  2. Negative Numbers
  3. Zero
  4. Text
It is not necessary to have all of these.  Any sections not included will follow the formatting set in section 1.

In this case, however, we have all four sections, but we are only interested in changing the first three - in particular Section 2 for negative numbers.

From above, we can see that the current formatting for negative numbers as follows:

-* #,##0.00_-

The first character is simply the minus sign.

This is followed by an asterisk (*) and a space. The asterisk tells Excel to repeat the character that follows it, to fill the remainder of the cell. As this is followed by a space, this tells Excel to pad out the area between the minus sign and the number with spaces, so that the whole cell is occupied. This is why the minus sign is shown on the far left in the Accounting format.

The #,##0.00 tells Excel that we want to see the number to two decimal places, with commas to mark thousands.

Finally, the _- creates a space the width of the minus sign. The underscore (_) symbol, inserts a space the width of the character that follows it.

To edit this to show our brackets, we can do the following:
  • Delete the leading - sign, as the brackets will denote the negative
  • Place an opening bracket immediately before the number, assuming that we want the opening bracket to be placed after the leading spaces
  • Replace the _- with a closing bracket. We no longer need the minus width space at the end, but we do want a closing bracket here
The new negative format should now look like this:

* (#,##0.00)

This is not the end of the story, however. We want to also slightly tweak the positive and zero sections, so that they line up correctly with the bracketed negatives.

The positive section currently looks like this:

_-* #,##0.00_-

We need to ensure that we have a space the width of a bracket in the same places that we have the brackets in the negative number. We also don't need the space the width of a - at the start and end, as these were removed from the negative.

The new positive format looks like this:

* _(#,##0.00_)

Finally, we need to make the same changes to the zero format which currently looks like this:

_-* "-"??_-

But, applying these changes, becomes:

* _("-"??_)

So, the complete new number format is:

* _(#,##0.00_);* (#,##0.00);* _("-"??_);_-@_-

We could trim this down even further if we like.

We don't really require the leading spaces, as there is nothing to the left of them, so we could remover the Asterisk and the space at the start of the positive, negative and zero sections.

We also, don't really need the text section, as this was only adding the leading space to coincide with the minus on the negative numbers, as well as the trailing space that was against all of the numbers.

Our streamlined format now looks like this:


And if you don't care how we got here, you can just select Custom Format and paste the above code in!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 29 September 2015

Excel Tip: Display current sheetname

Sometimes it can be very handy in a spreadsheet with multiple sheets, to show the sheet name in the sheet itself.

Say you have a sheet for each salesperson and new sheets are regularly being added and you name the sheets with the salesperson's names and want the same name to appear in the heading on the sheet. This is particularly important when printing the sheets as the worksheet name will not be visible.

You could, of course, type the same name in the header as the name of the sheet, but this has a few disadvantages:

  1. It's twice as much work
  2. You may forget, and have a different salesperson's name on the printed reports as is being used to pay them for example. This kind of inconsistency can lead to all sorts of problems!
Fortunately, it is relatively easy to get at the sheet name in a formula.

We can use the CELL command to do most of the work. This allows you to pull information about the current cell. In this case we are going to use it to pull the file path (including the sheet name). This is done by entering "filename" as the info type argument in the CELL command as follows:


This will show something like:


where MySpreadsheet.xlsx is the name of the spreadsheet and SheetName is the name of the sheet that contains the CELL function above.

As we just want the sheet name, we can use the fact that the workbook name is enclosed in square brackets as follows:


This earlier post gives more information on both the FIND and MID functions.

Essentially, this returns the 999 characters starting at the position after it finds the "]" (the +1 ensures that we don't start until the character after the "]"). I have simply used 999 to ensure that all of the characters after the "]" are returned. It will not return any extra characters, so will just return all of the characters after the "]", i.e. the sheet name!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".