Automating Tasks with the Table Feature

I consider the Table feature to be a superpower in Excel that unlocks tremendous automation capabilities with just a keyboard shortcut or a couple of mouse clicks. For instance, formulas and features that reference data in Tables, versus normal ranges of cells, automatically “see” any new data that you add to the Table. This eliminates having to rewrite formulas to expand the cell range and circle back to features such as PivotTables to expand the source data. Slicers eliminate the need to navigate drop-down menus when filtering, while enabling the Total Row option only tallies visible rows instead of the entire list. Tables allow Charts and Sparklines to be self-updating—the list truly goes on and on. In this chapter, I will cover the following main topics:

• Excel’s unwritten rule
• What is a Table?
• Table enhancements
• Table automation opportunities
• Navigation and selection nuances
• Troubleshooting...

Technical requirements

All the keyboard shortcuts in this chapter work in Excel for Windows, but only some of them will work in Excel for macOS. Unless otherwise noted, all features in this chapter will work the same way in Excel for Windows and macOS. A workbook with all the examples that I used in this chapter is available for download from GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter07.

Excel’s unwritten rule

Over the years, I’ve observed an unwritten rule that I find many users unwittingly break: the first row of any list should be a single row of unique titles—or Headers in Excel vernacular. This applies to every list that you create in Excel, no matter if you’re using the Table feature or not. Headers are so crucial that the Table feature adds placeholder Headers when needed. Both Tables and PivotTables require that each Header be unique and will append numbers to duplicate Headers to enforce compliance.

Tip

An addendum to the preceding rule is that lists should not have any blank rows or columns. Most Excel features act on the Current Region, which is the contiguous block of non-blank cells surrounding the active cell. Blank rows and columns truncate the Current Region, and they require you to manually select the entire list before sorting or filtering instead of being able to choose a cell within the list and then sort or filter...

What is a Table?

The Feature Comparison worksheet contrasts the Table, PivotTable, and Data Table features. Let’s compare each feature:

• Tables – Tables transform static cell ranges into self-expanding lists, as shown in Figure 7.2, which, as you’ll soon see, unlocks multiple layers of automation:

Figure 7.2 – Table

• PivotTables – PivotTables are a report-writing feature that summarizes lists of data inside cell ranges or Tables into concise formats, as shown in Figure 7.3:

Figure 7.3 – PivotTable

Tip

I briefly explore PivotTables in this chapter and Chapter 9, Excel Quirks and Nuances.

• Data Tables – Data Tables enable you to swap one or more sets of inputs through a single anchor formula to compare multiple scenarios, as shown in Figure 7.4:

Figure 7.4 – Data Table

Nuance

The TABLE worksheet function, as shown...

Removing Tables

Typically, you’ll want to keep Tables in place in your workbooks, but sometimes you might change your mind. Or you might find that the Table feature conflicts with another feature, such as Custom Views (see Chapter 8, Custom Views). Removing a Table can be as simple as choosing Table Design | Convert to Range; if you do so prematurely, you might solve one problem and create new ones. Here’s what I recommend:

1. Select any cell within a Table and then activate the Table Design tab of Excel’s Ribbon.
2. Choose Clear at the bottom of the Table Styles gallery.

Nuance

It’s important to remove an existing Table Style before you convert a Table into a normal range of cells. Otherwise, you or another user might mistakenly rely on automation that isn’t available in a normal range of cells masquerading as a Table. See the Selecting a portion of a Table section for more details.

3. Optionally, you can clear the Total Row...

Table automation opportunities

Spreadsheets tend to involve lots of calculations, so first, we’ll see how Table improve and sometimes alter formula behavior. Formulas that reference Table, either by residing within a Table, or referring to a Table will automatically reference additional data that you add to a Table. This eliminates the need to monitor and rewrite formulas that refer to normal ranges of cells. Then, I’ll show you an easy technique for applying number formatting.

From there, I’ll discuss the concept of structured references for formulas. Then, I’ll look at filtering data within Table and automating certain filtering tasks with Slicers. We’ll then see how Table eliminate a common data integrity issue with PivotTables, followed by enabling self-expanding charts.

Let’s begin by seeing how to automate formula management within Table.

Calculated Columns

A Calculated Column within a Table consists entirely of formulas....

Other Table techniques

This section offers a grab bag of techniques related to Tables. First, I’ll show you how to customize Table Styles and then how to transfer Table Styles that you have created between workbooks. After that, I’ll show you how to copy data from a Table and then choose between pasting it as a Table or as normal range of cells. Finally, I’ll compare how keyboard shortcuts that you might already use to navigate around worksheets can behave differently within Tables.

Customizing Table Styles

Ironically, the Table feature enables you to automate many repetitive tasks and yet will spawn new repetitive tasks for some users, meaning removing or changing Table Styles. There are only a couple of Table-related settings that you can manage on a global basis, which I’ll discuss in the upcoming Troubleshooting Tables section. Otherwise, you must customize your Table settings on a workbook-by-workbook basis.

Setting a default Table Style

...

Troubleshooting Tables

Tables should always expand to incorporate new data that you add immediately to the right or below the Table. However, as you’ll see, it’s relatively easy to accidentally turn off the Include new rows and columns in Table option. Turning this setting back on requires a bit of persistence. You can inadvertently turn off the Fill formulas in Tables to create calculated columns option in a similar fashion. Even when both options are enabled, another scenario can cause Tables and Calculated Columns to not work as expected.

The Include new rows and columns in Table option

First, let’s purposefully prevent a single Table from expanding to incorporate a new row of data:

1. Activate the Formula References worksheet and then type a number in the next available row of the Item column, such as 11 in cell L14.
2. Click on the AutoCorrect Options button, and then choose Undo Table AutoExpansion, as shown in Figure 7.32:

...

Summary

This chapter began with a discussion of Excel’s unwritten rule, which is about ensuring that the first row of any lists in your spreadsheet comprises a single row of unique titles. We then compared Tables, PivotTables, and data Tables, followed by a discussion of two approaches for converting a normal range of cells into a Table. The Table feature adds automatic enhancements, some of which you can disable or supplement with additional characteristics. Remember to be mindful when converting Tables into a normal range of cells to prevent anyone from mistakenly relying on non-existent automation in a normal range of cells that is masquerading as a Table.

Tables particularly enhance formulas in Excel, both by way of Calculated Columns within Tables and self-resizing formulas outside of Tables. Structured references make it easier to write and audit formulas that reference data within Tables. We went from formulas to filtering, and then you saw how Slicers can kick a Table...