How to Futureproof Aspects of Spreadsheets with Two Keystrokes

Readers of a certain age will remember a television show in which contestants would bid against each other to identify a song based in as few notes as possible. The equivalent in Excel is to be able to futureproof spreadsheets in as few keystrokes (or mouse clicks) as possible. In this article you’ll see how the unsung Table feature in Excel can vastly improve the integrity of features and formulas within Microsoft Excel spreadsheets.

Depending upon your version of Excel, the Table feature is the second or third command from the left on the Insert menu. Ostensibly this feature makes filtering lists of data easier, but it offers many other benefits, and one frustrating side-effect. You’ll use the Table feature whenever you have a list of data in Excel, particularly a list that may have additional items added to it down the line. To transform a list into a table:

1. Click any cell within the list.
2. Choose Table from the Insert menu.
3. Click OK when the Create Table dialog box appears.

Or, press Ctrl-T in lieu of step 2 above, and press Enter instead of clicking OK. Several things will change about your list:

• The Filter arrows will appear at the top of the list, which makes filtering easier.
• Every other row of the table will be shaded. Rest assured this cosmetic aspect is easy to remove.
• The Design menu appears at the right of Excel’s menu tabs.
• If your entire list cannot be viewed onscreen, the headings from the first row will move up to the worksheet frame, in lieu of the column letters. Keep in mind the headings remain visible only while your cursor is within the list. When you move your cursor outside of the list both the headings and the Design menu will vanish, but both reappear when you click back inside the list.
• The Design menu contains a Total Row option, which adds a total row to the bottom of the list. The last column of the list will either be counted or summed. If the last column is comprised of text or dates, then the total row will show a count of the visible rows. If the last column contains numbers, the total row will show the sum. Click any cell within the total row to display an arrow that will allow you to place additional calculations as needed. The total row only tallies the visible records within the table, so if you filter the list by way of the arrows in the first row of the table, the total row omits any hidden rows.
• In Excel 2013 and later you can use the Slicer feature to filter tables with a mouse click. Hold down the Ctrl key to select two or more items from a slicer or press Ctrl-6 to temporarily hide slicers that may be obscuring part of a worksheet. Press Ctrl-6 again to display the slicers again.

If you wish to remove the shading, click the arrow in the corner of the Table Styles section of the Design menu and then choose the first option (the word None appears when you over it) or choose Clear at the bottom of the palette. Either removes the shading from the table but keeps all other characteristics. If you wish to remove the table entirely, click Convert to Range on the Design menu, and then click OK when prompted whether to convert the table to a normal range.

It is important to remove the formatting from a table before you convert it to a normal range, as the Convert command leaves the formatting in place. This could lead you or another user to mistakenly assume that a list in a spreadsheet is formatted as a table when really, it’s only masquerading as such. The significance will become clear as we explore the hidden benefits of the Table feature:

• Self-expanding charts: Let’s assume that you have a chart that you periodically add additional data for another week, month, quarter, or year. In such instances most users find themselves manually resizing charts by way of the Select Data command on the Design menu related to charts. Futureproof charts by clicking any cell within the source data and pressing Ctrl-T, and then press Enter. New or existing charts will both automatically reflect new columns and/or rows of data that you add to the table.
• Self-expanding PivotTables: Microsoft recently reported that 80% of the 750 million Office users around the world have never used the PivotTable feature in Excel. If you’re using Excel 2013 and later, click any cell within a list, and then choose Recommended PivotTables from the Insert menu. This artificial intelligence feature will suggest reports to create based on the data. Of course, you can manually create a PivotTable in any version of Excel by clicking any cell within a list, and then choosing the PivotTable command from the Insert menu. You can then build a report by choosing fields from the PivotTable Field List that appears. Regardless, a hazard of PivotTables is that they don’t automatically reflect additional data that you may add to the bottom of an existing list that a PivotTable is based on. You can of course manually resize a PivotTable by choosing the Change Data Source button on the Analyze menu related to PivotTables in Excel 2013 and later (Options in Excel 2007 and 2010). Conversely, if you click on any cell within a list that a PivotTable is based upon and press Ctrl-T and then Enter, going forward the Pivot Table will automatically “see” the additional data, with the caveat that you must still refresh the pivot table after appending any new data. You can right-click a PivotTable and choose Refresh or look on the Data or Analyze/Options menus for Refresh commands.
• Self-expanding formulas: Let’s say that you’re using VLOOKUP or SUMIF to return data from a list. Traditionally adding new data to said list would require one to then update the formulas to reference the additional rows of data. Hopefully you’re seeing a pattern here, two keystrokes and going forward the formulas rewrite themselves when you append more data.
• Other self-expanding features: In short, any Excel feature that relies on a range of worksheet cells can become self-expanding. This includes the Sparklines feature, Data Validation lists, and among others that I’m sure I’ve overlooked.

Oh, and about that side effect: you cannot use the Table feature and the Custom Views feature simultaneously in an Excel workbook. Custom Views provides a host of automation capabilities that I’ll discuss in a future column. Certain older versions of Excel for Mac do permit the simultaneous use of Tables and Custom Views, but that’s no longer possible in the latest version of Excel for Mac.