July 20, 2021

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.

Updating the color and feel of cubes in excel using the design functionality

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

PivotTable Options in Excel; useful for Excel solutions

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.

Print preview image of an Excel document. Use print preview to see how your worksheet will look before printing or saving as a pdf

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.