Excel Quirks and Nuances

We've all had experiences where something goes awry in a spreadsheet. Software bugs can are not unheard of in Excel, but much of the time there is a disconnect between what we tell Excel to do versus what we intend for Excel to do. I wrote this book to broaden awareness of what's possible in Excel, with a specific hope that going forward you won't experience such instances nearly as often. And yet, sometimes things are just flat out weird in Excel. In this chapter I've assembled an array of features and techniques that can and do catch unsuspecting users by surprise, along with a powerful spreadsheet auditing feature that Microsoft has deigned only certain users can access.

Along the way you will learn about the following topics:

• Compatibility Checker feature
• Mouse tricks for navigating within worksheets
• Enter mode versus Edit mode
• Excluding weekend dates from charts
• Sparklines
• Circular references
• Inquire...

Technical requirements

Some but not all features will work in Excel for macOS, while most will work in all versions of Excel for Windows. The Inquire add-in requires a Microsoft 365 Apps for enterprise subscription. An example workbook is available on GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter09.

Compatibility Checker feature

If you share spreadsheets with others, both inside and outside of your organization, there's a decent chance that the recipient is using an older version of Excel, meaning Excel 2016 or earlier. Microsoft 365 brings us a steady stream of new functionality, such as the dynamic array functions that I discuss in Chapter 10, Lookup and Dynamic Array Functions, and the somewhat new LET function as well as the newer LAMBDA function that I discuss in Chapter 11, Names, LET, and LAMBDA. It’s invigorating to have new tools to bring to bear in Excel, until the user you shared a workbook with asks “Why am I seeing #NAME? everywhere in this workbook?”. It's hard to keep tabs on what new functionality works where. For instance, you can use LET but not LAMBDA in Excel 2021. You can't use either in Excel 2019 and earlier. I regret to inform any Excel for macOS users that the Compatibility Checker is not available to you, but it is...

Double-click trick for navigating within worksheets

Let’s say that you want to edit the contents of a worksheet cell. Many of us reflexively double-click on the cell in question and get to work. Folks that are into keyboard shortcuts often press F2 (Fn + F2 on certain keyboards) instead of double-clicking. Or you might select a cell and then click into the Formula Bar. All three are valid, but a slight variation on the first approach may make you think Excel is possessed. Allow me walk you through a scenario:

1. Activate the Double-Click Navigation worksheet, as shown in Figure 9.6:

Figure 9.6 – A four-pointed arrow indicates the navigation mode

2. Position your mouse over the middle of cell B4 and notice how your cursor presents as a white cross, and then click once to select cell B4.
3. Position your mouse over the bottom border of cell B4 and notice how your cursor changes to a four-pointed arrow, as shown in Figure 9.6. Now, here...

Enter Mode Versus Edit Mode

Often, users add data to a chart and then must manually resize the chart to include the new data. I show how to eliminate this repetitive task in Chapter 7, Automating Tasks with the Table Feature, but I want to compare two manual approaches here. Let’s jump in:

1. Activate the Enter Mode Versus Edit Mode worksheet in the example workbook for this chapter.
2. Notice that the chart does not reflect the March values. The first way to resolve this is to do the following:
   1. Click once on the chart.
   2. Drag the Selection Handle shown in cell D5 in Figure 9.7 across to cell E5:

Figure 9.7 – Using the Selection Handle to resize charts

3. At this point, your chart should reflect the month of March.

Nuance

If you don’t see the Selection Handle in Figure 9.7, choose File | Options | Advanced, and then turn on the Enable fill handle and cell drag-and-drop setting.

3. Now, let’s add April and...

Excluding weekend dates from charts

Sometimes, Excel becomes overly helpful, leaving us having to figure out how to undo the unwanted help. For instance, as shown in Figure 9.11, the Excluding Chart Weekend Dates worksheet in the example workbook includes data for two Monday to Friday periods, and yet Excel insists on creating a gap for the weekend:

Figure 9.11 – Phantom dates inserted into a chart

Fortunately, there’s an easy fix, although many of us would be hard-pressed to figure it out on our own. Check this out:

1. Right-click on the Horizontal (Category) axis and then choose Format Axis.
2. Choose Axis Options | Axis Type | Text axis to close the gap. The settings are illustrated in Figure 9.12:

Figure 9.12 – Choosing Text axis to remove phantom dates from charts

As you can see, when the Chart Axis options are set to Automatically select based on data or Date axis, Excel interpolates any missing...

Sparklines

Traditional charts in Excel may to take up a lot of space on the screen or printed page, but are not the only game in town. Sparklines are tiny charts that appear within worksheet cells, as shown in cells B4:B8 in Figure 9.13:

Figure 9.13 – Sparklines

For instance, you might use Sparklines as part of a dashboard, which is a spreadsheet that communicates a large amount of information in compact form. Let’s first walk through the process of creating each type of Sparkline and then I’ll show you an odd quirk. Follow the next steps:

1. Activate the Sparklines worksheet in the example workbook for this chapter.
2. Select cells F4:I4 and then Insert | Line within the Sparklines group to display the Create Sparklines dialog box.
3. As shown in Figure 9.14, the Data Range setting is prefilled with the range of cells you selected:

Figure 9.14 – Create Sparklines dialog box

Nuance

Often in...

Circular references

Circular references are formulas that refer to themselves, like a snake trying to eat its own tail. Let’s say that you entered the formula =SUM(B3:B5) in cell B5 of the Circular References worksheet, as shown in Figure 9.15. Typically, circular references are created accidentally. Excel alerts you with the prompt shown in Figure 9.15.

Figure 9.15 – Circular reference

If you click OK on this prompt, the formula may return an amount, or it may return zero. Excel displays a Circular References message on the Status Bar informing you of the cell that contains a circular reference, as shown in Figure 9.16:

Figure 9.16 – Circular reference Status Bar message

Conversely, if a circular reference exists on another worksheet or even another open workbook, no cell reference will appear, and the status bar will simply report Circular References. Fortunately, there’s a hidden menu command that you...

Inquire add-in

Most Excel features are baked into the software, but sometimes Microsoft provides features through a type of software known as add-ins. I discuss the Solver add-in for Excel for Windows in Chapter 6, What-If Analysis. Since you cannot enable add-ins within Excel for macOS the Solver feature is built into the Data tab of the Ribbon. Let’s turn our attention to the Inquire add-in, which is available in Microsoft 365 Apps for enterprise. I’m going to cross my fingers and hope you can access this great feature:

1. Choose File | Account.
2. As shown in Figure 9.19, the Account screen may display Microsoft 365 Apps for enterprise, or it may simply reference Microsoft 365.

If you don’t see the words Apps for enterprise as shown in Figure 9.19, then this section will be a dead end for you; otherwise, read on to see how to enable the Inquire add-in:

Figure 9.19 – Account page of Excel’s Backstage view

Here...

Summary

In this chapter, you learned how to ensure that your spreadsheets are backwards compatible with Excel 2021 and earlier. The rolling release of new features across three channels presents a special challenge for Microsoft 365 users looking to take advantage of the rapid pace of improvements. You can determine if other users have the same functionality as you --or better --by comparing the build and version numbers between your copy of Excel and theirs.

You also learned about one of the biggest nuances in Excel, the difference between Enter mode and Edit mode, so that you can now spend less time fighting with Excel when you need to make changes to formulas and cell references. We also looked at an odd quirk of the Sparklines feature where the order in which you carry out steps will predicate whether you can complete a task or not. Fortunately, this is an outlier in Excel—I don’t know of any other feature that has this curious restriction.

If circular references...