Building and Using Efficient, Insightful Reports

Summary

I’ve worked with many people throughout my career that spend a lot of time downloading, organizing, building, analyzing, reviewing, or distributing reports.  Obviously many of those were financial in nature, but many were around other data points such as tracking employees, customer surveys, website traffic, and all sorts of database records.  Regardless of the data type, there were often many manual steps involved in getting the right data to the right people at the right time.  I try to minimize those manual steps and automate them wherever possible.

If you spend time doing these types of manual steps, this article will help you save quite a bit of time:

• Hand-keying in lots of data
• Downloading a report and formatting like crazy
• Endless sorting, filtering, moving, and updating
• Moving back and forth between sheets

In this article, we’ll discuss how to streamline those steps and utilize best practices I’ve found helpful throughout my career. Here are some high-level takeaways we’ll discuss:

• The structure of a good report template
• Building and maintaining reports
• Distributing, presenting, and reviewing reports
• Specific XLEV8 add-in macros that help with reports

The Structure of a Good Report Template

For most people I’ve observed working with reports, the process goes something like this:

1. Go to the reporting section of the system containing the data.
2. Enter parameters for the data (dates, entities, locations, etc.).
3. Download the report and open in Excel.
4. Add formatting to make the report look more appealing.
5. Search for exceptions, variances, key metrics, and other discussion points and highlight them.
6. Save the report on a shared drive, cloud drive, etc.
7. Email the report to various people (hopefully not the wrong people!).

Lots of manual steps, lots of potential error points.  The best reporting systems will let you save parameters (sometimes even dynamic, like “current month”), set up the exact structure and formatting you want, and send to the appropriate people on a schedule – completely automated!  Unfortunately, most reporting systems don’t do that.  But you can still automate almost every step above.

When I build a template, I generally have a common set of sheets.  A brief description of each is displayed below.  Note that not all of these are always needed, but it’s still good to consider whether they might be helpful.

1. Instructions – These should be fairly detailed, clearly describing each of the steps required so that a qualified person can follow them. Including screenshots can be quite helpful here.

Tip: Keep inputs as minimal as possible and let other items calculate off of them (i.e. one date with previous month/quarter/year referencing it).

2. Inputs – If the inputs are minimal, often these can be included with the instructions.  These will drive calculations, so they tend to be dates, locations/regions/departments, and other metrics to calculate.

Tip: Keep inputs as minimal as possible and let other items calculate off of them (i.e. one date with previous month/quarter/year referencing it).

3. Review/Checks – This also may not be needed but I often find it’s helpful. Build completeness checks comparing totals per the source data to totals you are calculating.  Maybe something new or dropped off the source data that your calculations need to reflect.  Anything you tend to manually tie-out to ensure completeness and accuracy could potentially be automated.

Tip: Use conditional formatting to quickly identify exceptions!

4. Summary/Report – This is what you’ll be ultimately sharing, presenting, or distributing. In my templates, these are generally full of calculations – VLOOKUPS, SUMIFS, subtotals, etc.  I hide the lookup/sum conditions off to the sides if needed.  You’ll want this to be as well-formatted and easy to consume/use as possible.  There may be multiple Summary sheets based on different dimensions.

Tip: Show the most pertinent information, pre-sorted, and group/hide the rest.

5. Source Data – This contains the data you need to use. Ideally, it’s easy to run and structured consistently so you don’t have to change anything that references it.  If you often work with several similar reports where the structure is the same but a parameter is different (date range, location, division, etc.), try to get all that data in one run.  You can link calculations to that source data in order to get everything looking the way you want.  Sometimes the source data can be refreshed within Excel from an add-in, query, etc. which makes it easier to update.

Tip: Get source data in a tabular, database-style format – it’s easier to link to formulas.

6. Lookups – These aren’t always necessary but can be really helpful for pulling in repetitive information. For example, your source data may have a store #, but not city, open date, manager name, etc.  Those values can be stored in a lookup table and referenced to get into your shared report.

Tip: Make sure the lookup data is up-to-date.  The lookups could also be source data!

7. Calculations – Sometimes you need intermediary calculations to perform complex or voluminous calculations, such as ranking different metrics to a scorecard or dashboard. Simple calculations can usually just be included into the Summary sheet.

Tip: Build in checks to this sheet to make sure you have completeness over the data.

Building and Maintaining Reports

When you set out to build your report, begin with the end in mind, asking yourself some key questions:

• What is the purpose of the report?
• Who needs to use it?
• What are the important data points and different dimensions in which they should be viewed?
• In what format should the data be presented?
• What source reports are available to facilitate compiling the report?

The most important thing I keep in mind as I’m building reports is to link everything I can back to the source data or other calculations.  I look to build reports as flexibly as possible, so that the data can be analyzed by product, by time period, by division, by location, comparison type, etc. and any roll-ups of those dimensions.  I like to include drop-down lists of valid options (such as the store # and description) on the Summary sheet, so that users can easily flip between them.  Below are some thoughts on setting up the different sheets in your report.

Instructions

Again, be as descriptive as possible here.  I bold and color important things like the name of a sheet, range, or report.  Separate the instructions for different users (preparers vs. reviewers).  Include sufficient quality review steps – track the errors and gotchas (they’re inevitable).  Have others perform the steps so you can see if the instructions make sense and work as expected.

Inputs

I like to highlight the input cells in yellow that I will likely change for each report version, and cells that reference those in separate color.  I also often set a named range for each input so that it’s easier to reference them.  The most common data points you’ll likely have in your inputs are dates.  It could be a start date and end date, or just an end date with the month-to-date, quarter-to-date, year-to-date, and comparable periods automatically calculated off the end date – I’m a big fan of the EOMONTH function.

Summary/Report

You’ll want to make these sheets as sharp as possible since these are what everyone is going to see!  But as good is it looks, nothing is as important as relevant information.  Make sure to clearly understand what everyone wants to see and they story you want to tell.  Consider including charts to go along with the data – they are great for absorbing information quickly – especially for comparative information like dates, locations, people, etc.  Trend charts are great because it helps you find outliers very quickly and perform a reasonableness check.  Below is an example of one of my favorite chart types – a PivotChart that contains different stores with current year/prior year ratings, sorted best to worst.

Even if you’ve never seen this chart before, it’s easy to figure out what’s going on because of the way it’s presented:
• The sorting (best to worst)
• The grouping (store CY and PY together)
• The colors used (green for best, grey for the comparative period)
• The data points presented (dates and labels)
For tabular data, formatting is even more important because it’s even easier to get lost in the data! The fonts, number formats, borders, and colors all contribute to the usability. It’s easy to want to show all kinds of data that users can look at, but resist the urge and keep the data points to a minimum. For example, do you need to show the budget or prior year value when people really care more about the variance? Have it available but hide it so it’s there if needed. Sorting can also be really helpful for comparability. Pick the most important metric and sort on it, and indicate which column(s) are sorted on. Better yet, include a macro that makes it easy for users to pick which fields to sort on. The screenshot below contains some great examples of formatting and presentation.

There are many formatting settings in play here for the benefit of users:

• Borders help group data into digestible chunks and separate the totals
• Headings and totals (labels and values) are bolded, succinct, and clear – $ and % figures are noted and the sorted column has an arrow indicating which direction
• Comparative values use a better/worse “B (W)” format – this eliminates the need for the user to decide whether higher/lower is good or bad. Good (or better) is a green, positive number and bad (or worse) is a red, negative number
• Numbers are displayed as rounded and formatted cleanly so that it’s easier to read them
• Rows are banded (alternating row colors), making it easier to follow across and tell apart
• Non-crucial data points and lookup values are hidden with grouped/collapsed columns and rows
• Freeze panes is used at just the right spot so column headers and row labels are locked
• Formula errors have been handled (i.e. #DIV/0) with IFERROR and other functions, and potential error flags (the little green cell triangles that sometimes show up) have been hidden/ignored
• Rows with $0 values or blanks all the way across have been hidden
• Columns are labeled with letters so they can quickly be referred to. This can also work with labeling the rows.

It’s also important to consider how the report will be consumed – is someone just going to project the report in Excel to a screen?  Will it be printed (if so, include good headers/footers)?  Will it be saved as a PDF?  Will it be copied into PowerPoint?  These are all important points to consider as to what data points you present and how you present them.

When I use a separate sheet dedicated to calculations, it’s usually for one of three reasons:

• There are so many individual calculations that the Summary sheet would be cluttered up or take a long time to calculate
• Intermediary calculations need to be performed, such as rankings; the rankings are then referenced by the Summary sheet in a scorecard format
• The source data needs to be aggregated, summarized, or rolled up in some fashion

One recommendation is to not reference external files – keep everything self-contained in one file if you can.  It’s so easy to accidentally fry links or get them out of sync.  Sometimes for the source data, I’ll reference external files, then copy and paste values only in a separate sheet.

I do use a trick to easily split off the Summary sheet from references to other sheets – by using the IFERROR and INDIRECT formulas with a dummy reference.  For example, consider the formula below.

The components of this formula are:

1. Lookup value – this can be a displayed label or hidden value
2. Lookup table – containing the data you are referencing
3. Value rounding – for readability and to reduce clutter
4. Dummy reference – the file Book9999.xlsx is not found, so the IFERROR formula returns a 1

The dummy reference always returns a 1, which will return the value as intended.  When you want to convert the formulas containing this dummy reference to hard-coded values, simply break the links to the Book9999.xlsx file.  This is significantly faster than pasting as values and retains all the subtotals or intra-file references.  Just make sure you have a backup file with the formulas so you don’t have to rebuild them.  I generally include a macro that will save a copy of the Summary sheet with hard-coded values instead of formulas referencing the source values/calculations.

Lookups

Lookup sheets are useful for managing all sorts of data that would otherwise be repeated.  Here are some examples:

• Location details – the source data likely has just a store # while the lookup could have a label, city/state, open date, etc.
• Account rollups – depending on how you want to aggregate your data, there could be many different ways you want accounts to roll up – by location, by region, by type, etc.
• People details – maybe you have a list of IDs that are associated with names, locations, hire dates, etc.
• Lists – months, days of the week, etc.

To make lookups easier to reference, I like to use dynamic named ranges.  You can find a previous article about using named ranges here.  When the named range is dynamic, you can add new values to the list and the named range will automatically expand to include the new values.  If not named ranges, use fixed column references so you don’t accidentally forget any data within the references.

Source Data

It’s not much of a report without source data!  Consistency is the biggest key when it comes to source data.  If the structure changes frequently, you’ll be chasing your tail.  Sometimes there are multiple sheets dedicated to source data.  However many there are, ideally you should not have to manipulate these sheets after you replace the data.  When the source data is in a tabular format, it’s much easier to reference in VLOOKUP, SUMIFS, and similar formulas.  If it makes sense, you can use macros to automatically pull in source data – from other folders/files or even from email attachments.  I’ve even used add-ins to query data with special formulas.  If you do something like this, I highly recommend having separate sheets for values-only data rather than the formulas, just in case something breaks (similar to linking to external files, as discussed above).

Distributing, Presenting, and Reviewing Reports

Here are the questions I ask when deciding how to distribute reports:

• Should I send a copy of files I’ve prepared or just a link (“push” vs. “pull”)?

Answer: It depends who you’re sending to.  For people you collaborate with and you’re comfortable with them accessing your working file, a link is fine.  It helps prevent version control issues.  Otherwise I’d only send links to read-only files or non-working files to people who will be able to access them.  I sometimes send both the file and a link for convenience.  Better yet, if there are multiple files, you can send a link to the folder containing them.

• Should I send the full Excel file or just the summary?

Answer: Again, it depends.  If your audience will want to tinker with things and change around the inputs or views, you’ll want to send the whole file.  If the summary sheet(s) contain everything they’ll need or if changing inputs/views causes a long calculation time (> 10 seconds), I would just send the summary sheet(s).  An even better option is to build in all the different views they may want to look at.  For example, if sending reports to a regional manager who oversees 5 stores, build the file to include a sheet for each store so they have everything in one place.  Bonus: build a sheet that compares them all together!  They will appreciate your efforts.

• Should I send files in Excel format or save them to PDF first?

Answer: Shocker – it depends!  I often send both in case someone doesn’t have Excel on the device they mainly use for email.  Excel is easier to navigate, edit (formats/sizes, etc.), view (freeze panes), build off of, so I generally prefer to receive that myself, but others may not care.  If there are any confidential data points in the file, anything hidden in Excel will not be in the PDF version.

The most common approach I’ve taken in distributing files is using a macro to separately save off the summary sheets (both Excel and PDF formats), create emails to the groups that should see them, and copy a screenshot of the most important key metrics to the body of the email message.  Almost every step of the process was automated with the macro (save summary files, create emails, attach files, copy summary screenshot, send emails) – the only manual steps were around copying in the source data and maintaining the distribution lists.

Presenting Reports

I’ve also seen people take a variety of approaches with presenting reports (or data from them) – sharing the Excel file, a PDF file, or screenshots copied into PowerPoint.  I used to like just presenting Excel – it’s easy and it’s dynamic if you want to change views or inputs that drive the calculations.  However it’s not the ideal presentation solution, even if you hide all the buttons.  Projecting a PDF file can also work, but it can be a pain to piece together many different pages into one PDF file.

My favorite way to present information is within a PowerPoint presentation for several reasons:

• It’s designed for presenting – slides show up as full-screen
• You can include animations to help focus the audience
• It’s easy to navigate between slides and animations
• Charts and tables can be copied in as images from Excel (or linked)
• Excel and PowerPoint can work together with macros

That last point is a big one that has saved me hundreds of hours.  At one company, I maintained 10 separate Excel templates with different information that were used to compile a PowerPoint file with about 120 slides, almost all of which had to be updated with new data every month.  Before automating the process, I spent about 30 hours each month updating and re-updating those slides – copying and pasting lots of tables and charts from Excel to the slides in PowerPoint – making sure the size and position of each item was the same as before.  We automated this so Excel would open up the right file, copy the right data, and paste to the right spot in the PowerPoint file, and loop through every item where that was needed.  It only took 10 minutes to run.  Last minute changes weren’t near as brutal anymore.  That time was then saved to perform analysis and provide valuable commentary.

Reviewing Reports

When you’re preparing reports, you want to make it as easy for your reviewers as possible, and when you’re reviewing, you want to make it as easy as possible to get through everything, communicate any comments, and follow up on resolutions.  As a preparer, always try to think like your reviewer – what will they be looking at, what questions will they ask, what are they concerned with, etc.  Develop consistent expectations to guide your review.  Make it efficient – rather than looking at everything, look at the exceptions and the outliers.  Here are some tips I’ve learned along the way:

• Include a table of contents that makes it easy to move around in the file
• Include instructions for prep steps and review steps so no one is reinventing the wheel
• Include a summary of your review and analysis they can start from
• Include a consistent place they can add comments rather than all over the file
• Make it as easy as possible for them to find what they are looking for (default sorting, filtering)
• Include comparative information – period over period, actual to budget, division by division, etc.

XLEV8 Add-in Macros for Reports 

There are dozens of macros in the XLEV8 Excel add-in that can help you build, maintain, and distribute reports.  Here are the top ten that I’ve used, grouped based on type:

1. Formatting
   • Toggles – There are several toggle macros that speed up formatting (cell highlights, number formats, font type/size/color, borders, etc.). They can be customized for the styles you use most often.  A great example is the Toggle Cell Highlight
   • Conditional Formatting Picker – This macro presents you with several common types of conditional formatting – banding, green/red colors for variance calculations, icons, data bars, etc. The table above includes some of these.
   • Sort Ranges – If your report has several separate areas you want to sort (I’ve had over 50 in one report!), this lets you set the sort parameters and all the ranges will be sorted for you – a big time-saver!
2. Navigation
   • Sheet Action Picker – This presents you with several options working with sheets – adding, renaming, deleting, moving, hiding, unhiding, etc. but I personally use the navigation options the most – jump to first or last sheet or search for one, or add a table of contents with hyperlinks to all the sheets.
   • Show Cell References – Chances are you’ll build formulas with lots of cell references in them. If you need to trace back to the referenced cells, this macro makes it easy by showing you a list of all cells referenced in the formula.  Just pick the range to jump to, and it takes you there.  Then if you want to jump back, run it again and choose the back option.
3. File Management
   • Save Sheets to PDF – This allows you to specify the sheets you want to save as PDF files, including where the PDF files should be saved and what they should be named. Because you can reference those to inputs (such as dates), it works very well in a template – set it and forget it.  The Save Active Sheet To PDF is a similar option for saving just the active sheet as a PDF file in the same folder as the active file.
   • Refresh PowerPoint Slide – This macro lets you quickly copy items from Excel as images into PowePoint, by specifying the exact Excel item to copy (chart/image or range of cells), as well as the exact spot to paste it to in PowerPoint – what slide, what size, and what position. It will delete the previous item in the slide that is in the same spot.  This allows you to refresh a lot of slides with repetitive information very quickly.
4. Distribution / Review
   • Email File Link – This is a quick macro that creates an email in Outlook with a link to the active file you are working in, a link to the folder containing it, and optionally the file itself. This is helpful when collaborating on files so other users don’t have to go searching for them, and avoiding version control from sending files back and forth.
   • Create Send Email Messages – When you send emails very repetitively, this macro lets you set up a list of messages with key fields (recipients, subject, message, attachments, etc.) and once populated, when you run again, it will create the messages based on those conditions, and optionally send the messages for you. Because you can link the conditions to other data points (such as the date, store name, etc.), it becomes very dynamic.
   • File Comments – This is a very helpful feature for reviewing. When it’s first run in a file, it creates a “File_Comments” sheet with relevant columns.  This is a central place where comments can be added.  A hyperlink back to the cell where it was run is also added.  When run again from that sheet, it copies the comment into a cell comment back where you first ran it.  Run it again to add another comment/hyperlink to the comment list.

There are many example reports in our support section (including many in the screenshots above).  Feel free to download these and use them as a starting point for your own templates!

If you have your own Excel report tips and tricks, please share in the comments below!

Coming Soon!

What is your current approach in working with reports?

Very manual

Some automation

Extensive automation

Reports are fed to me directly from source systems

Results Vote

What is your current approach in working with reports?

Very manual 0 ( 0 % )

Some automation 0 ( 0 % )

Extensive automation 0 ( 0 % )

Reports are fed to me directly from source systems 0 ( 0 % )

Back

Report Template Demonstration