Opening the Vault on Excel, part 5

Excel expert Neale Blackwood CPA revisits some of his earlier Excel how-tos. Updated this month: inserting a new line within a cell, 3D Sum, dynamic ranges and sheet navigation icons.

I answered four questions between August and October 2003.

1. Is it possible to insert a new line within a cell?

To insert a line break within a cell, hold the Alt key down and press Enter. You can do this multiple times if necessary. This is similar to Excel’s Wrap Text format, but the line break is fixed to the position where you used Alt + Enter. 

This can be a handy technique when you are creating headings in tables. In general, table headings should only use one row. Having the line break within the cell allows you to keep the column width to a minimum. 

In Figure 1, I have shown the comparison between the Alt + Enter and the Wrap Text format.

Figure 1.

Figure 1.

If you link to a cell that has used the Alt + Enter technique and you haven’t included a space between the words, then they will appear as one word – see cell A4 in Figure 2.

Figure 2.

Figure 2.

There is a small arrow icon at the far end of the Formula Bar. You can click this icon to see all the text in the Formula Bar. The companion video also covers resizing the Formula Bar. 

2. Is it possible to easily add up the same cell across spreadsheets?

This is a very powerful technique, and it allows you to easily add up the same cell across multiple spreadsheets. For the technique to work, the sheet tabs need to be adjacent. It is sometimes called a 3D SUM.

Figure 3 has an example that we will use to demonstrate this technique. 

Figure 3.

Figure 3.

We have four sheets that are named after states. The state sheets are identically laid out, and we need to add up the same cell across all four sheets.

In the Summary sheet, we click in cell C3 and type =SUM( we then use the mouse to click in the NSW sheet on cell C3. Then we hold the Shift key down and click on the WA sheet and press Enter.

The formula that is entered is:

=SUM(NSW:WA!C3)

This adds up cell C3 across all four sheets. This technique is the only time when the location of the sheet tab is important. If you dragged another sheet in between the NSW and WA sheets, that sheet would also be included in the calculation.

You can also type this formula in, but if the sheet names have spaces it can be problematic, and using the mouse is often the easier way.

A slight modification of this technique that is more flexible is to use boundary sheets as shown in Figure 4. These are blank sheets that have a short name and surround the sheets that you want to add up. I name these sheets using the single lowercase letters of the alphabet.

Figure 4.

Figure 4.

The formula that will work for Figure 4 is even shorter.

=SUM(a:z!C3)

The beauty of this technique is that you can have blank sheets between existing sheets to create subtotals for different sections or regions. If you look at Figure 5, I have added another blank sheet (b) between VIC and SA.

Figure 5.

Figure 5.

Now it is possible to add up just NSW and VIC by using the following formula:

=SUM(a:b!C3)

We can also just add up SA and WA by using this formula:

=SUM(b:z!C3)

The original =SUM(a:z!C3) formula is still valid.

The reason I use lower case letters is to avoid confusion with column references. Using alphabetical order is also intuitive for users when creating extra subtotals with lots of sheets.

This technique requires that the sheets be laid out identically. Identical layouts of the sheets are only restricted to the areas being summed up and not to the remainder of the sheet. Typically, the identical areas are at the top of the sheet, allowing the remainder of the sheet to contain other types of information and calculations – enabling each sheet to be tailored to the user’s needs.

Excel’s other basic functions can also work like this. AVERAGE, COUNTA, MIN and MAX can perform these multi-sheet calculations.

3. What is a dynamic range?

A dynamic range is a range whose size varies based on the entries in the range. A common use of a dynamic range is the source for a drop-down list. As you add entries to the bottom of the list, the entries will automatically appear in the drop-down list. 

Dynamic ranges are also used in reporting models.

Dynamic ranges are much easier to create in the latest versions of Excel because we can leverage the power of formatted tables. Before formatted tables, you had to use complex formulas. I have included the complex formulas below the formatted table solution. Formatted tables are a much easier solution as they require far less typing.

Formatted table

Formatted tables automatically expand as data is added to them. You can use a reference to a formatted table (called a structured reference) in formulas, but they tend to make the formulas longer and harder to read. Using range names simplifies their use. We can use a range name to refer to a column in a formatted table to allow us to create a dynamic range. Formatted tables were added in Excel 2007. Formatted tables are an upgrade to an old Excel feature called Lists.

Figure 6 has a short list of states.

Figure 6.

Figure 6.

Click inside the list, press Ctrl + T and press Enter. This creates a formatted table with the default colour and name. 

Select the range A2:A5, click the Formulas tab and click the Define Name icon. The New Name dialog should open. Change the name to States1 and click OK. See Figure 7.

Figure 7.

Figure 7.

We can now create a drop-down list on the sheet and test the name. 

Click cell C1 and press in sequence Alt A V V (don’t hold the keys down). This opens the Data Validation dialog. Select List in the Allow drop-down and type =States1 in the Source box (see Figure 8) and click OK.

Figure 8.

Figure 8.

We can see the resulting drop-down list in Figure 9.

Figure 9.

Figure 9.

If we add a couple of extra states, they should appear automatically in the drop-down – see Figure 10.

Figure 10.

Figure 10.

Formula-based solution

If you don’t want to use a formatted table, then there are formula-based alternatives to create a dynamic range. There are different formulas for different situations. These all start with the same steps, and each one enters a different formula. I will use a different sheet for each of the following examples that don’t have a formatted table defined.

Click the first cell in the range, in this case A2. Click the Formulas ribbon tab, then the Define Name icon.

No blanks

If your range won’t have blanks or empty cells, you can use this formula. I will use the range name States2.

Click in the Refers To box and press F2. This makes it easier to edit. Press : and then create the formula below; you can click on the sheet to create the formula references. See Figure 11.

=Q3_2!$A$2:INDEX(Q3_2!$A:$A,COUNTA(Q3_2!$A:$A))

The INDEX function returns a reference to the last cell in the column. This assumes no blanks and no entries below the list.

Figure 11.

Figure 11.

Numbers with blanks

If the list has numbers and blanks, use this formula. I will use the name States3. See Figure 12.

=Q3_3!$A$2:INDEX(Q3_3!$A:$A,MATCH(1E+100,Q3_3!$A:$A))

Figure 12.

Figure 12.

Text with blanks

If you have a list with text and blanks, use this formula. This will be named States4. See Figure 13.

=Q3_4!$A$2:INDEX(Q3_4!$A:$A,MATCH("*",Q3_4!$A:$A,-1))

Figure 13.

Figure 13.

Text and number with blanks

If you have a list with text, numbers and blanks, the formula is even more complex. This will be named States5. See Figure 14.

=Q3_5!$A$2:INDEX(Q3_5!$A:$A,MAX(MATCH(1E+100,Q3_5!$A:$A),MATCH("*",Q3_5!$A:$A,-1)))

Figure 14.

Figure 14.

As you can see, all the formula methods are complex. The tables method is much easier to implement.

4. Is it possible to see a list of sheets within a workbook?

To see a list of sheet names, right-click the sheet navigation icons – the icons are located to the left of the first sheet (tab) name – see Figure 15.

Figure 15.

Figure 15.

This is a navigation feature, so you can open any of the sheets in the list by clicking it.


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.

Recommended for You

READ MORE

Opening the vault on Excel, part 3


READ MORE

Opening the vault on Excel, part 4


READ MORE

Opening the Vault on Excel, part 5


November 2019
November 2019

Read the November 2019 issue of INTHEBLACK magazine.

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

CONTENTS