How to automate reports in Excel

Learning Excel Excel in Practice Dec 02, 2021

One of the repetitive tasks in many companies is reporting (projects, cost estimates, sales…). Reports are usually prepared manually in many cases in Excel. Working in Excel often implies dealing with a huge amount of xlsx tables. The tables are completed manually, the data are not controlled, hence there is a high chance of errors (typing errors, same content entered in different ways, etc.). In addition to this, input tables for reports are prepared in different formats and shared as attachments via emails. To be honest, making a report from several tables in a short time is impossible, because yet another table needs to be created – which is again done manually. It is a demanding and time-consuming task.

Input table, code list and automated interactive report

The automation of reporting in Excel is achieved by working properly with one common input table, code list and interactive report generated automatically from the common input table - a simple database in the background. The result is:

  • Error-free, controlled data entry, facilitated by mandatory fields with centralized code list
  • Real-time refreshing of data from different tables (sources)
  • A common table linked to several input tables. It is a "database" used to create interactive reports, monitor KPIs, create dashboards

How to build an automated report in Excel

  1. Create an input table which can be easily upgraded and corrected. The data in the reports is then always automatically refreshed without any changes to the file structure.
  2. Create centralized code lists and link them to the data validation in drop-down lists to reduce the number of entries and prevent entry errors.
  3. Use specific signalization with conditional formatting settings to control data entry
  4. Create an additional, hidden fields to display additional information not otherwise visible in the report.
  5. Write and upgrade formulas relative to different dates, VLOOKUP function (XLOOKUP), Today () function, IF function.
  6. Design input table and report: use proper formatting to stabilize the result format in reports.
  7. Link different reports with slicers to automatically control the amount of data in the input table or to efficiently display results in reports using counters.

Learn more on how to automate reporting with Excel in Practice courses ›. We suggest that you start with Projects 1› Excel course. If you already have basic Excel skills, you can easily jump to another level and simplify your work. Try the test case template ›.