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.
The Middle Market CFO 
I use the find formulas (& links) and highlight them blue (my favorite color),along with highlighting the input cells using the input cells style, that way people know where to enter new data and where the formulas are so they can be cautious when updating the worksheet.
Great tip Laura. I use the same colors as you (great minds think alike). Keep them coming!
A great navigation tip for going back and forth between the cell you are located in and the source cell referenced in a formula and is to use the following keyboard commands.
Control [
(pressing the control and open bracket keys at the same time when you want to go to the source)
Typing that command will immediately bring you to the exact cell referenced in the formula, even if it is on a separate tab or spreadsheet. If the spreadsheet being referenced happens to be closed, Excel will open it.
Typing F5 will bring you back to where you started.
This works best when working with complex, linked spreadsheets and saves a lot of time drilling around, looking for cells and tabs.
Great tip Brian, and very well explained. Thanks for sharing!
If you have any questions about the best way to do anything in Excel, feel free to post them here. Or for more advanced Excel info, visit http://www.ozgrid.com or http://www.excelhero.com – both are great resources.
Greg,
Great meeting you! Thanks for the blog post and for generating this Excel discussion…Excel rules!
Thanks
Jeff
You’re very welcome Jeff. Thanks again for the Excel tips.
Great tips, thanks.
I find that whenever a Finance person send me a spreadsheet the first thing I have to do is remove the subtotals so that I can create a pivot table.
(When an Engineer sends me a spreadsheet it usually has a PT in it).
Anyone know any tricks to allow Subtotals and Pivot Tables to co-exist in the same spreadsheet?
Microsoft is unwittingly helping to enforce the border between Operations andf Finance…
Hi Ian. If you have a table of data, rather than inserting subtotals into the data table, you can ‘filter’ the data and at the bottom of the data table, put a Total (with the sum function) and a Subtotal (with the Subtotal Function). This will not interfere with the pivot table the way subtotals do when they are in the data table, but it will allow users to toggle between different categories to see the subtotal of each without having to use pivot tables. Or you can simply use pivot tables. I may be a finance guy, but I side with the engineers on this one. You can also design files with one worksheet for ‘data’ (without subtotals), one for pivot tables from that data, and one for end user reports from the data that include subtotals and formatting to make them presentable and printable. Anyone else have any better ideas for Ian?
Ian – I nearly forgot to respond to your question about enforcing the border between Operations and Finance. The engineers I work with recommend electrical fences. Of course they are electrical engineers. The mechanical engineers I used to work with used trap doors.
Regarding enforcement of the Finance-Engineering border – my company employs sharks with laser beams on their heads.
very good – that works for me.
There are numerous best practices that should be followed to eliminate inefficiencies and these are some good ones. In addition, more emphasis needs to be placed on eliminating errors. Virtually all training courses concentrate on formulas and functions; there is almost no instruction on establishing spreadsheet controls. However, it is very simple for users of even modest proficiency to build a Spreadsheet Control System utilizing existing functionality. Not only is this user-friendly system extremely effective at preventing costly mistakes, but systematic controls also add efficiencies that greatly enhance employee productivity by streamlining the work process. Thus, one can increase both efficiency and accuracy with proper training.
Hi Breck,
Do you recommend any resources related to the spreadsheet control system you mentioned?
Training for this is provided at StopSpreadsheetErrors.com. This system has something that is probably similar to the “Error Check worksheet” that you mention.