Data Reports: Making them Simple, Clean and Effective
Creating Data Reports in Excel; A good visual report for a stakeholder can move mountains! Let’s create a simple static report that speaks volumes.
Suddenly, the data you’ve been trying to explain via tables is so clear to your non-technical client or manager! Apart from a presentation, the two best ways to share data insights with no-code stakeholders are reports and dashboards. As I’ve discussed creating a dashboard in a different data story, this article will cover how to create effective data reports with clean visuals. Data reports present data insights in a concise and understandable format. Static data reports like the one we’ll dive into here offer a fixed snapshot of data insights for a specific time period.
I was inspired to re-create a data report I found on Twitter with minor changes in the insights presented. The report will be created from UK accident incidence and vehicle datasets subset to 2015. The Excel file for this project is available on my github. Let’s get into the process.
After importing the file into Excel, I inspected the columns and corrected any data format issues, like formatting the date column as date type using the Text to Columns tool. After this, I used VLOOKUP queries to match data I copied from the vehicle dataset into the accident dataset. This resulted in the following table for the pivot analysis.
The Vehicle Age segments were created to categorize the ages of the vehicles for visualization purposes using the IF statement below:
IF(K2>30,"Antique (>30 Yrs)", IF(K2>20,"Classic (20-30 Yrs)", IF(K2>10,"Old (10-20 Yrs)", "Modern (<10 Yrs)")))The next step is to insert a pivot table for your data in a new worksheet. Pivot tables are essential tools for calculating, summarizing and analyzing your data in Excel. To create one, go to Insert > Pivot Table, highlight your data range and choose an existing or new worksheet to place your created pivot table. From there, you can easily copy and paste to re-create the pivot table and switch around the Pivot Table Fields to suit your desired calculation.
Pivot charts which are the key elements organized in the data report, are created from the pivot tables. Simply select an entire pivot table, go to Insert > Pivot Chart and choose your desired chart type. Do these for each insight you’ll like to present in your report, then format your chart elements like colours, fonts and titles. Once your charts are ready, copy and paste each one into the sheet you’ve created for your Report.
On the Report sheet, you’ll need to get creative with text boxes and shapes to finish with a simple yet visually stunning report like my finished report below:
Though I maintained the blue colour theme, I changed chart types and included other insights that I found interesting. For example I used a gauge chart instead of a bar chart to present speed limit data. I also looked at the ages of the vehicles involved and discovered that majority of the recorded accidents involved modern cars less than 5 years of age.
Sharing such elegant yet concise reports can help your team quickly catch up on data insights without technical difficulties. I hope you enjoyed reading! Remember to check out my github for the data report file and view my portfolio for more data stories.
