Opening the vault on Excel, Part 9

Excel expert Neale Blackwood CPA is fast approaching his 200th INTHEBLACK article since he began writing his monthly Excel Yourself column in INTHEBLACK magazine in 2002. He recently “opened the vault” on those early articles to review the information and add newer and easier solutions or built-in functions. See what’s new.

I answered three questions between June and August 2004.

1. When copying and pasting cells, I sometimes get an error message that says: “Cannot change part of a merge cell”. How do I get around this error?

The merged cells format causes a lot of issues in Excel. Its icon on the Home ribbon is shown below in Figure 01.

Figure 1.

As well as stopping you from copying and pasting, it can also crash macros. In general, I advise against using the merged cell format. There is an exception, which I will discuss. 

Instead, I recommend people use another option called Center Across Selection (Excel uses US spelling). This format doesn’t cause copy/paste errors or macros to crash.

To apply the Center Across Selection format, first select the range you want to apply it to.

Click the little arrow icon on the bottom right corner of the Alignment section on the Home ribbon – highlighted yellow in Figure 02.

Figure 2.

This opens the Alignment tab of the Format Cells dialog shown in Figure 03.

Figure 3.

Click the Horizontal drop-down. Choose the second to last option, Center Across Selection, and then click OK. 

The downside of this recommendation is that there is no icon on the ribbon, and it doesn’t have an icon you can add to the Quick Access Toolbar. As you saw above, it requires a few clicks to apply the format. There are a couple of ways around this.

  1. Create a Style (Home ribbon) – Styles are standard formats that you can apply across a file. There are a number of built-in Styles. You can also create your own custom Styles. The problem with this technique is that custom Styles are saved at the workbook level, not the Excel level. To have a custom Style available in a new file, you need to add it to a Template.
  2. Macro – You can record a macro to apply the format, and then add the macro to a button on the Quick Access Toolbar and run it whenever you need it. I will demonstrate how to do this on the companion video.

Limitation

Center Across Selection does not work vertically. You must use the merged cells format for vertical ranges. 

2. I format many of my reports to zero decimal places. This means that in some cases, the SUM function will show a different total than the displayed numbers indicate. If I format two numbers 5.5 and 7.5 with zero decimal places, they will display as 6 and 8, respectively. When summed the result is 13. Can I get the SUM function to SUM the displayed values, in this case 14?

The answer to this back in 2004 was an array formula. Array formulas will be used less and less in the future as Dynamic Arrays start being rolled out this year. Array formulas were used by very few Excel users. In my estimate, fewer than 1% of Excel users would use array formulas. Honestly, it might be less than 0.1%.

Because of that, I will offer a non-array solution to this issue.

In Figure 04, we can see the problem mentioned in the question.

Figure 4.

The formula that can calculate the SUM based on the values as displayed (zero decimal places) is 

=SUMPRODUCT(ROUND(A1:A2,0))

Figure 05 shows the formula and the result.

Figure 5.

The SUMPRODUCT function works by first applying the rounding across the range before it adds up the range. Note: most functions currently do not work like this. In the future they will, as Excel is changing its calculation engine.

The dynamic arrays mentioned earlier change how functions work. The companion video will demonstrate this new calculation feature.

This formula assumes that you consistently use the zero decimals format and want to display the results to display based on this zero decimal format. Note: Excel always rounds 0.5 away from zero, so 0.5 is rounded to 1 and -0.5 is rounded to -1.

3. I use range names for rows. How do I reference a specific cell in a named row?

Row-based range names are commonly used in budget and forecasting models. They allow you to easily extract values from the same column in another sheet. To see how they work, examine Figure 06.

Figure 6.

I have named row 2. It is named WA_Sales. You can see the range name in the Name Box at top left of the image. The name displays whenever I select row 2.

To create a range name, select the range and then type the name into the Name Box and press Enter. 

Note: range names can’t 

  • Include a space
  • Start with a number
  • Be a cell reference. For example, Q1 or QTR1 are not allowed

Latest version issue

In the latest version of Excel, Dynamic Arrays have been implemented. This is a different calculation engine, and there are some formula differences. It will change the way you create formulas in the future. In general, it is an improvement.

Instead of entering 

=WA_Sales 

In a cell, as you would have done in all the previous versions of Excel, you now need to enter

[email protected]_Sales

This is new. It is only used if you have the very latest subscription version of Excel. You will need to get used to seeing the @ symbol appearing in Excel formulas. Figure 07 has an example.

Figure 7.

Wherever you use that range name, WA_Sales Excel returns the corresponding column from row 2. In every sheet in the file, if you use that range name in column D, it will return 1110.

When used in budgets and forecast files, you need to ensure all the month columns are consistent across all the sheets. In the example above, Jul must always be in column B.

Now let’s say we wanted to refer to August sales in column B.

We have at least two options.

i. We can use the following formula

=INDEX(WA_Sales,1,3)

This means extract the first row (1) and the third (3) column from the range name WA_Sales.

The INDEX function is flexible when using single row range, and you can shorten it to 

=INDEX(WA_Sales,3)

Because there is only one row, the 3 refers to the column number or item number to extract.

ii. As well as horizontal range names (row-based), you can also define vertical range names (column-based). I have named column C Aug – see Figure 08.

Figure 8.

I can now use the following formula to refer to the intersection of the horizontal name and the vertical name – see Figure 09

=WA_Sales Aug

Figure 9.

There is a space between WA_Sales and Aug. The space is Excel’s intersection operator and the reason you can’t use a space in a range name. The sequence in which you use the names is not important. The following formula will also work.

=Aug WA_Sales

There is a space after Aug.


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 8


READ MORE

Opening the vault on Excel, Part 7


READ MORE

Opening the vault on Excel, Part 6


May 2020
May 2020

Read the May 2020 issue of INTHEBLACK magazine.

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

CONTENTS