Creating Attractive Business Reports in Excel
Your CFO calls for a quick report on sales trends. What should you do?
This post will explain how to create a clean, interactive table that highlights the important features of a data set. Through this example, we will provide an introduction to pivot tables, conditional formatting and pivot slicers. Not only will the table call attention to the sales strengths and weaknesses, it will also allow your CFO to drill down and make data-driven business decision.
Tool(s) required: Excel 2013
Example Final Table:
Tutorial: Reporting the Strengths and Weaknesses of the Sales Operations
First, you need to decide what information will be important to your audience. Since this is the CFO, he/she is probably more interested in the trees than the weeds, meaning he/she will be looking for a high-level overview of sales within categories that make business sense. A business executive rarely has time to dive into data intricacies, but we won’t deny him/her the opportunity if they see fit. Therefore, keep the categorical stratification variables in your dataset such as customer, region, brand or sales channel. In this situation, the variable of interest is total sales or gross margin (sales minus cost). Be sure to pull data for two seasons, or time-periods, to see trends.
a. Convert the Sales Table to a Pivot Table
A pivot table allows Excel to cache the data enabling for quick filtering, slicing and calculated fields. To create a pivot table simply click anywhere in the sales table, and from the Insert tab in the banner, click on pivot table in the tables section.
Now, drag the categorical variables into the rows tab. For drill down purposes, keep the largest categories above the smaller categories. Place the variable of interest into the values tab.
Add a calculated field to display year-over-year percentage change (YoY % Change). The calculated field will enable Excel to determine specified calculations at any level of the pivot-table report. To do this, click on Fields, Items, & Sets under the Analyze tab in the PIVOTTABLE TOOLS section of the toolbar. Type the formula for the field into the specified area: (Sales Y2-Sales Y1) / Sales Y1.
At this point, we will need to format the columns for readability. Click on each of the variables in the values section of the PivotTable Fields panel, and select Value Field Settings. For each variable, edit the Custom Name to name the column, and choose the appropriate Number Format.
Now that the pivot table is built with drill-down capabilities and correct formatting, we can focus on the table presentation.
b. Draw Attention to the Issues
We’ll need to highlight the underlying sales-related issues found in the data. To begin, we will sort the column headings based on the values of the variable of interest. Decision makers are usually interested in their biggest accounts or categories, so we will sort based on the most recent season’s values.
To accomplish this, right click on the categorical headers, hover over sort, and click more sort options. Click the radio button next to Descending, and choose the most reason season from the dropdown menu.
In order to draw the audience’s eye to the low or high performing areas, we will use conditional formatting. In this feature, Excel will apply formatting to cells based on their values.
The initial step is to select the cells in the % Change column. Next, click on Conditional Formatting in the Styles section of the Home tab. Choose an option from the color scales category that matches your objective such as the green-white-red selection.
The color scale defaults to highlighting based on percentiles, but it should be centered at 0 if possible. To do this, click on manage rules under the Conditional Formatting drop down in the Styles section. Expand the new rule and set the Midpoint type to Number, and verify that 0 is entered into the Value area.
c. Make it Interactive
To assist the audience in finding the information they need, we will use Pivot Table Slicers. The slicer enables a user to filter the data with one click. It also increases the production quality of your report, somewhat resembling a dashboard.
The slicer may be added by clicking on Insert Slicer from the Filter section of the Analyze tab in the PIVOTTABLE TOOLS area. Slicers work well with categorical variables, so choose the same stratification variables that were used in the rows section of the pivot table.
The final step is to format the slicer. Under the Options tab of the SLICER TOOLS header, you can update the visual appeal of the slicer. For example, you should optimize the Columns in the Buttons tab to utilize slicer space.
We’ll also benefit from deviating from the default slicer styles. Building a custom style will make the slicer look more professional and customized to your report. To create a new style, expand the selection in the Slicer Styles tab by clicking on the down arrow, and choose New Slicer Style.
From this section, you can choose how to display the different elements of the pivot slicer. In general, we’ll need to update the font, background and border options for the following:
- Whole Slicer
- Selected Item with Data
- Unselected Item with Data
- Hovered Unselected Item with no Data
- Hovered Unselected Item with Data
This will create a dynamic experience for your user. For example, when you hover a new slicer item, a border and darker background appear. Best practices include lighter backgrounds for unselected data and larger fonts for readability.
We’ve now reached our final table:
Again, this table incorporates elements that allow the CFO to quickly determine which business areas are performing/underperforming in sales by:
- Displaying clean, sorted information that may be filtered or expanded/collapsed
- Highlighting important areas based on cell values with conditional formatting
- Adding a custom formatted slicer for filtering
We can easily see that sale are trending downward, with the exception of the North Region. Stores 125 and 5 are performing better than others in their categories, and Store 65 grew the most during this time period.
The beauty of analytics is that an answered question typically leads to several further questions. We will now need to explore the root-causes behind these findings using more advanced methods. However, for the time being, the CFO may target campaigns towards the underperforming areas.
Please let us know if this tutorial was helpful in the comments section below!