Ten Things Every Excel File Should Have

Even if you just have a handful of sheets in your file, a table of contents can be a huge help.  It communicates to users what’s in the file, and when you include hyperlinks, it helps them navigate.  I once worked with an Excel financial planning model that contained over 500 sheets…adding a table contents was the only way to use it!

There are two tools I’ve used over the years to add a hyperlinked table of contents in one step – one adds a simple list of the sheets in the file, while another one adds thumbnails of the sheets.  Check them out here and the output in the screenshot below.

 

Instructions are absolutely crucial for every user of your Excel files, perhaps most importantly your future self!  A good instructions sheet shows you care about quality.  It should include comprehensive steps about how your file works and what actions should be taken to use it and update it.

You might also include any assumptions, drivers, or inputs on the instructions sheet.  If there are many of them, it often makes sense to separate those into their own sheet.  Additionally, include documentation throughout your file to memorialize your thinking and logic.  Cell notes work great for this.  To centralize all of the actions you can take with cell notes (formerly called cell comments), I use a tool called the Comment Picker (check it out here).  See an example of a good instructions sheet below.

 

Wait, isn’t the idea to communicate data to people?  Yes, but if you throw too much data on a sheet, people will get lost, kind of like TL;DR with an email.  Take a look at your sheets and ask yourself which data points are crucial and which are supplementary.  Find a way to hide, collapse, or separate the supplementary data so users can focus on the most important data points.  Here are some suggestions:

• Collapse prior period/budget amounts and just show the variances to them.
• Display roll-ups instead of every account, location, department, etc.
• Include an Executive Summary near the beginning that focuses on the most important data points (with links to more details when desired).

Below is a screenshot where you can see that supplementary columns have been grouped and collapsed, as evidenced by the + icons above the columns. This makes them easy to quickly expand and collapse to see more details and keeps the page from being too cluttered. If you want to go further and hide the columns, rows, or sheets, check out these two tools that help you toggle them similar to the grouping icons: Toggle Hidden Cells and Toggle Hidden Sheets.  

Careful formatting is often an afterthought in Excel files, but don’t discount how powerful it can be.  By carefully using colors, borders, alignment, and number formats, you can make it much easier to understand the message you’re trying to communicate. While there are endless approaches you can take with formatting, here are four I’d recommend including right away:

• Round numbers to an appropriate number of digits
• Limit currency symbols to the top and bottom amounts in a column
• When displaying comparison/variance amounts, color them green for positive/good and red for negative/bad
• Include row banding (alternating fill colors) to make them easier to read across

See the screenshot below for these approaches.  To apply number formats super-quickly, check on the Toggle Number Formats and Search Number Formats macros.  

Ideally, your Excel files should require minimal data entry, unless you’re using them as a light database.  Yet too many people copy and paste data all over the place.  This is time-consuming and risky – it’s so easy to forget to update something or accidentally miskey an item.

Minimize your inputs to items like dates, names, identifiers, etc. and link formulas to them.  Centralize all your inputs so they are in one place.  Use tools like Power Query to automatically pull in updated data you can link formulas or PivotTables to.  And whatever you do, don’t mix formulas and hard-coded amounts – it’s a recipe for disaster!

See the screenshot below for an example of an inputs sheet.  To ensure you don’t have hard-coded amounts throughout your sheets, check out the Conditional Formatting Picker that has options to highlight hard-coded amounts or formulas with hard-coded components.

 

While it’s helpful to minimize data inputs, you usually can’t eliminate them completely. When you do need to capture data, use data validation to ensure it’s entered correctly. Between number ranges, date ranges, drop-down lists, or even custom definitions, Excel’s data validation is quite robust. It’s not bulletproof, but it’s much better to use it than not to. Identify and eliminate errors as early as you can in the process.

The other benefit is that you can guide your users with input instructions. I find that drop-down lists are great for speeding up data entry as well. See an example of this in the screenshot below. Check out the Data Validation Picker, which streamlines adding data validation to your cells.

 

Tables are a great way to structure and format your source data.  They can automatically expand as the data grows, and apply styling automatically.  They also play nicely with other Excel tools like Power Query, PivotTables, and Slicers.

Anytime you have tabular data that feeds into formulas or PivotTables, make it a point to set it up as a table.  See the screenshot below for an example.

 

If you’re not taking a modern approach to building formulas, you’re really missing out!  By modern, I mean building formulas in a way that they can spill across multiple cells.  This speeds up calculations, can combine multiple formulas into one, and can automatically expand as the data grows.  It’s a great way to ensure completeness so you don’t miss anything. See the screenshot below that has several modern formulas that leverage dynamic array functions (including the selected cell’s formula).  Check out the Global Search and Search Formulas macros that help you re-use your custom and non-custom formulas!  

Freeze panes is a feature that most Excel users know about and use regularly.  But it’s still something I see forgotten in many sheets that would make them easier to use.  Make it a point to add freeze panes as soon as you start setting up your sheets, and adjust if you need to.  You might also consider putting totals and other key data points at the top above your data so you don’t have to scroll down to find them.

See the screenshot below for an example of a good place to put freeze panes in a common P&L report structure.  Two tools I’ve used over the years to apply freeze panes (and many more settings!) to a lot of sheets at once are the Freeze Selected Sheets, Bulk Update Sheets, and Multi Sheet Format macros. 

I see way too many Excel files with no visuals in them, and it makes it easy to get lost in a sea of data.  Visuals like charts and icons make it exponentially easier to understand data, and may just prevent you from missing something important.

Between standard charts, PivotCharts, sparklines, and even conditional formatting icons, Excel has a variety of tools that make building visuals easy and powerful.  Make sure you use the right kind of chart for the job – here are three common examples:

• Use line charts to show trends
• Use column/bar charts to show comparisons and rankings
• Use pie/donut charts to show parts of the whole

See the screenshot below for an example of some visuals I used in the past that really helped the data come alive by using charts that have the data points accompanying them below.