Spreadsheets are great for playing with data. Their free form structure is ideal for designing custom reports. And because Excel is so widely adopted, it is often the tool of choice for creating and sharing information. But with such a range of user experience, we often get spreadsheets that are not well structured, cannot scale or can easily become unmanageable. So I’d love to share some of my best practices for building reports in Excel.
When we want to use Excel to summarize data, our best approach is to treat it as a free form database. We can use the worksheets to replicate database features; Data Tables, Queries and Reports. Here’s how:
Data Tables are defined as a range of cells that contain related data. The first row of data is reserved for Field Headers and are needed to describe the data in each column. Each column is a “field” (or category, ex: Name, Date, Price etc.) and each row is a “record” (or list item).
When data is formatted in a proper table, Excel recognizes the structure and offers a selection of tools to analyze your data. This tells us that Excel is designed to work with structured data tables.
Tips:
Excel PivotTables are fantastic for reading and summarizing the data in your Table. Because they form a connection with your data, any changes you make, (for example: add more records) can automatically flow through to the PivotTable report.
Let’s create a PivotTable. Start by selecting all the columns on your “Data” worksheet that contain values. Then select PivotTable in your Insert tool bar. Choose to place your PivotTable on a New Worksheet.
Tips:
You can then shape your PivotTables to create the views you need for reporting. If you need several views, you can create more than one PivotTable on the Query worksheet
If your PivotTable looks good enough for you, you’re all set, you can use that for your report. If you want to combine data from different sources, run calculations and enjoy freedom to format as you like, then you will need a Report. Create your ideal layout and use the GetPivotData formula to pull data into your report. Create calculated metrics and other automations to make updating easy.
This is an example of a simple Excel file that follows the best practices of; Data, Query and Report. You are welcome to download it here. Take a look at the formulas on the “Report” worksheet, even the date can be automated. I’m hoping this helps and would love to hear from you if you need more. There is so much we can do with a solid foundation for reporting. Please feel free to reach out to me directly if you have any open needs for reporting and analytics.
-Jane