Pivot table

I attended an IMA meeting this evening and the guest speaker was Jeff Lenning, President of Click Consulting and author of “Excel University: Microsoft Excel Training for CPA’s and Accounting Professionals”.

The theme of the presentation was basically “eliminating inefficiencies in recurring use spreadsheets”.  I build a lot of large spreadsheets with vlookups, pivot tables, charts, if statements, conditional formatting and other relatively advanced functions.  To be honest, I did not expect to learn much, but as is often the case, the more you know, the more you realize how much you don’t know.  I quickly learned several relatively simple tricks that I can apply more consistently throughout my recurring use spreadsheets to save a lot of time for myself and my clients.  I’ll summarize some of these below:

• Stop using SUM where SUBTOTAL is more efficient.  The SUBTOTAL function excludes other subtotals within the range, so it eliminates the need to revise the ‘grand total’ formula when new rows or subtotals are inserted into the range.
• Add a “skinny row” before formula rows.  A “skinny row” is a blank row with a relatively small row height.  Adding a skinny row eliminates the need to adjust the formula row or the formatting when inserting a new row at the bottom of the range.
• Hide rows and columns using Data-Outline-Group feature rather than using the hide function.
• Use the FILTER function in conjunction with the SUBTOTAL function to create methods for other users to sort and subtotal data that are a little simpler to use than pivot tables.  I like pivot tables, but they aren’t for everyone so this is a good alternative.
• Use a “Start Here” worksheet for input areas and highlight input cells with the “input” cell style.
• Use an “Error Check” worksheet to ensure integrity throughout multiple worksheets.
• Use hyperlinks to ease navigation between source data and other worksheets.
• Use keyboard functions (rather than right click or ribbon icons) for common features such as “F2” to edit a cell, “F4” to toggle through cell references, Arrow+Shift/CTRL to scoot around your worksheet more efficiently than with your mouse, and double-clicking the lower right corner of a formula cell to push the formula down as far as there are adjacent cells.

I have used many of the tips above, but the key is to use them more consistently.  If you have any additional Excel tips or tricks you like, please share them in the comment section below.