5 Ways to improve your spreadsheet reporting

Intro, and what people do.

Most spreadsheets I see don’t have any reporting functionality, but those that do, often get it wrong. They end up with a thrown together page of loads of graphs, for all sorts of data. This ends up in a report that is pretty much impossible to read, so the users end up looking at one or two key graphs and ignoring the rest. It also means that as the company grows, more graphs need to be added. This is seldom the best route to take, so I want to show you five things than you can implement in your reporting, to make it far more efficient. As always if you like these ideas, but can’t implement them yourself, please get in touch and ask for my help. So, let me tell you the 5 aspects that I implement in most of the spreadsheets I make.

Separate dashboard

I think this is key. I often suggest having a dashboard and a report. Both have kind of merged into the same thing for many people, but they are not. A report is more detailed and should be something to look at when analysing. So, think about looking back over the year and analysing trends, targets, issues, etc. That is all stuff that should be in a report. So, for month end reviews, annual reports, etc, all reporting. A dashboard should be a very high-level report for a daily basis. So, you open the spreadsheet, see the dashboard, and it should alert you to issues that need to be addressed. Immediate, high level, daily alerts. This is why I make my dashboards visible on one screen, so there’s no scrolling or faffing. That’s for the reports.

Filterable report

So many reports I see are duplicated. For example, Company A wants to see the sales figures for the year. So, they have a graph showing that. Then they want to see Andrew’s sales figures for the year, so they have a graph for that. Then they realise that they have 8 sales staff, so 7 more graphs appear. Then they want to track the sales for a particular key product, so 9 more graphs appear. Next thing, they have loads of graphs which require scrolling on forever. Not only that, but when a new salesperson joins, they are then trying to add even more graphs. What I do. Is to do a filterable report. So, in the above scenario, we would only need one graph. Sales per Month. I would then have a filter option. Leave that blank, or select a staff, as you select a staff, the WHOLE report filters for that staff. I could also include a product filter, or a branch filter, etc. That way, you could make all the filter selections, and the entire report will recalculate for the desired filter. This means that the number of reporting options are almost endless, but yet the report size is manageable and easy to read. Also, when a new staff member starts, you simply add their name, and then you can select them from the drop-down list.

Dynamic date ranges

In a similar way to the filters, I often let clients toggle the date ranges too. So, in order to keep the reporting manageable, I usually set the reporting period to 12 months. This can either be set to your financial or business year, or it can be a rolling 12 months. When it’s a rolling 12 month period, as you start a new calendar month, the 12 month period updates. However, what happens if you want to see a report for a previous 12 month period (provided you have the data to support it)? Well, I can also give the option to select the final month of the 12 month period, therefore letting you decide what 12 months to show. When you combine this functionality with the filterable report functionality, it really does leave you with many reporting options. All this, in a fraction of the space that is needed if you did a separate report for each option.

PDF-friendly layout

Most people want to view their reports in Excel. Also, with the filter functionality, they can see what they want by selecting options. Having said that, many do want to save PDFs of various reports, to file away and keep on record. This is why I make reports to print nicely to PDF. So, that this is an option to those who want to do so. I usually have the filter off the printable page, so it doesn’t get included in the PDF. In this case, I do my best to state what filters have been applied on the page, so you can see what filters you had set when saving the PDF. This way it lets you compare apples with apples when comparing previous PDFs.

Diverse data sets (possible exports)

Some people prefer text, some prefer graphs, some prefer tables, some even prefer graphics. I would suggest that under normal circumstances, the dashboard needs to be more visual (graphs and graphics) but the report needs to be more ‘diverse’. Yes, have graphs, but have the raw data there too. Sometimes you can’t see exact figures from a chart, but you can spot trends. Having the raw data helps to see exact numbers. Sometimes, when there is lots of activity on the report, I often split the two. So, the report would be more graphs, and graphics. Then we could have a ‘Data’ tab, which shows the table so of data that support the report. This also means that if the client wants to further analyse the reporting data, they can copy those tables and paste them (using paste values or paste link) into a new spreadsheet and do what they wish with the numbers.

Conclusion

I hope you enjoyed those five aspects that can be implemented to a spreadsheet to make the reporting way more efficient. Adding the dynamic elements, the clean layout, the separate dashboard and report, and then the supporting data, can all make the spreadsheet far more user-friendly. If these are things you can add yourself, then I hope this helps you. If not, I would love to chat to you about making your spreadsheet(s) for you. Please get in touch should you want to discuss this option.