Too many teams spend almost as long hacking together their Excel reporting structures as they do pulling the actual data set together. Yes, Microsoft Excel is an invaluable tool… but Excel formatting isn’t always pretty. Luckily, are a few equally universal ways to fix this universal problem. And for the most part, they involve common sense simplifications!
Incorporating a few oft-overlooked Excel best practices will help make your reports easier to parse and read, but they also serve another, arguably more valuable purpose: the simpler and more to-the-point your sheets are, the easier they will be to reproduce every month. The less time you spend on formatting busy work, the more time you’ll have to get more important tasks done – like analysis! EBM and our Excel experts can help. We asked our team to share their personal favorite time-saving Excel “hacks.” Here’s what they came up with:
1. How to Clean up your Excel reporting
First thing’s first: finance teams constantly add too much information to their Excel spreadsheet. The more info you add, the more cumbersome your report is. A good report tells one, cohesive story per data tab.
Luckily, there are some surprisingly simple ways to make your information much more readable:
A. Turn off Gridlines
Go into the view tab and turn off gridlines to make your spreadsheet look far less busy.
KEYBOARD SHORTCUT:
TURN OFF GRIDLINES: “Alt,” W,” “V,” and “G” at the same time
B. Freeze labels and headers
“Freezing” your row labels and column headers keeps them in view, even as you scroll.
SHORTCUT:
FREEZE LABELS AND HEADERS: “Alt,” “W,” and “F” at the same time
C. Insert blank rows and columns
Keep the first row and column of each of your tabs blank whenever you can.
SHORTCUTS:
INSERT BLANK ROWS: “Shift” and “Space” to select a row (1), and then “Shift,” “Ctrl,” and the “+” button at the same time (2)
(1) “Shift” and “Space”
(2) “Shift” and “Ctrl” and “Plus”
INSERT BLANK COLUMNS: Press “Control” and “Space” to select a column (1), and then “Shift,” “Ctrl,” and the “+” button at the same time (2)
(1) “Control” and “Space”
(2) “Shift” and “Ctrl” and “Plus”
D. Use Grand Totals and Sub-Totals only when necessary
Only use the Excel calculations you absolutely need. Remove Grand Total and Sub-Total options to make your report cleaner, easier to read, and more relevant.
E. Group rows or columns to easily hide/unhide
GROUPING ROWS: Press “Shift” and “Space” to select a row (1), hold “Shift” and select additional rows to group (2). Then hit “Alt,” “A,” and “G” (3). You can also click and drag the fill handle to select a row, column, or range of cells.
(1) “Shift” and “Space”
(2) Hold “Shift” and use arrow keys
(3) “Alt,” “A,” and “G”
UNGROUPING ROWS: With the rows selected, hit “Alt,” “A,” and “U” (for ungroup) at the same time.
(1) “Alt,” “A,” and “U”
GROUPING COLUMNS: Press “Control” and “Space” to select a column (1), continue holding “Shift” and select additional columns to group (2). Then hit “Alt,” “A”, and “G” at the same time (3).
(1) “Control” and “Space”
(2) Hold “Shift” and use arrow keys
(3) “Alt,” “A,” and “G”
UNGROUPING COLUMNS: With the columns selected, hit “Alt,” “A,” and “U” at the same time.
(1) “Alt,” “A,” and “U”
F. Update Cubes using PivotTable Design
PivotTables are easy to format using the “Design” function. Color-code tables, size tables to importance, and commit to consistent title and presentation conventions.
Using the Design functionality to color code and re-size tables is easy and makes your worksheet look much more readable.
2. Make Your Excel Reporting Drill Downs and Updates Simple
The best way to make your monthly reporting easy is to standardize it. When you know exactly where your source data needs to go you’ll spend far less time putting it there.
Here are some easy ways to standardize your update process:
A. Simplify Header Information
Try to reference static information as little as possible.
B. Minimize necessary updates
Try to organize your tables so you have to manually update them as infrequently as possible. Instead, conduct a quick data validation check every month to make sure your numbers are accurate.
C. Set up automatic calculations
Use Excel formulas to set up calculations in your PivotTables or cells using conditional formatting. When you update these tables or cells, formulas automatically re-calculate.
D. Pre-configure PivotTable options
Pre-configure each of your pivot tables in your reporting package with the following options. You can select these options by right-clicking on a cube:
Auto-fit column widths on update
For error values show _____
For empty cells show ____
Show items with no data on rows/columns
PivotTable Options in Excel
Use the PivotTable options menu to pre-configure your pivot tables
E. Use Flash Fill for patterns
Flash fill automatically fills in every blank cell that follows a pattern, such as “A B C A B C.” If you have a repeating data or cell style in your report, Flash Fill can help you fill them in fast.
3. Don’t Forget to Present!
Your report can’t just contain the right information; it needs to present that information clearly and look good. Luckily, making your spreadsheet look good also helps you make it easier to reproduce.
Here are a few easy tips you can implement to make your reports easier to present:
A. Create a Cover page
Create a cover page for your report with the company logo, report date and report title.
B. Create a Table of Contents
Link sheet headers from each tab so users can click links within your table of contents to quickly navigate to the right place.
C. Color code tabs
Color code each tab in your spreadsheet according to the data it contains.
D. Check the print preview
Before you finalize your report, check it in the “print preview” by selecting “File > Print.” This will show you how the report will look when you print it out.
Using print preview will allow you to see what your report looks like before you print it or share it as a pdf.
4. Quality Control Check
A little monthly quality control now can save you a lot of time and trouble later. After you’ve standardized your report, run through it one more time to catch anything you may have missed.
Here are three areas finance teams frequently forget to check:
A. Fonts and font sizes
Double-check for consistency throughout.
B. Titles
Confirm their accuracy and relevance.
C. Sheet file names
Make sure you check the names of the actual tabs and sheets within the report, as well.
D. File Size
Try to keep your Excel files from getting too large, or they’ll take too long to load. When your files are starting to get slow, consider splitting them into multiple documents.
FP&A Cloud for Easier Excel Reporting
There’s one more way to make monthly reporting a lot easier: use EBM Software’s Catalyst® FP&A Cloud™. Catalyst and its Data Cubes remove a ton of the legwork from reporting by automatically aggregating and populating all data you require.
With Catalyst, reports that used to take days to put together can be automated with a few simple double clicks. More in-depth analysis, like SKU and customer profitability or channel analysis, can be refreshed from Data Cubes right from your Excel pivot tables, too.
With real time insight into your business all month, you’ll have far fewer surprises to uncover when month end rolls around. With Excel tips and tricks like these, you can stop dreading your reporting and start spending your time building value.