We've put data in a pivot table and a pivot chart to make them comprehensible and easy-to-find trends. However, that is not enough. The easier it is to see the trends, the more effective the work is; and just like how famous brands use marketing to their advantage, we are going to package our little product to make it more appealing, more meaningful.
We will focus on features outside of pivot tables and pivot charts since there are millions of ways to represent the data in those two. Instead, we will build upon what we have prepared so far to redesign, organize, and clean up our data.
From the previous recipe, prepare the file CustomColumns_v1.xlsx. Open it up, copy the sheet DeliveryTime04, and save the copied sheet as Result.
Can we present this to our boss? No, of course not. The pivot chart does not mean anything, there is a field called Row Labels, and it just does not look presentable in any way or form. This is no different than just providing the raw database's data and telling someone to interpret it, not to our standard.
Then let's start re-packaging our results. Firstly, let us name items by changing the Row Labels value to
Year/Monthas shown in the following screenshot:
Then, also change the names of slicers as follows:
Change CountryRegionCode to
Country NameChange OnlineOrderFlag to
Online Order?Change ShippingDays to
Shipment Duration (days)Change Name to
Product NameMove all vertical slicers to horizontal
Click on the pivot chart. Under the Design tab, click on Switch Row/Column. Now our pivot table will be represented horizontally. However, this is too long! Move the Year dataset from Column Labels to Row Labels.
Oh! As we have changed our row, Row Labels and Column Labels appeared again. Rename as
YearandMonthrespectively. Also change the Sum of OrderQty and Sum of LineTotal options toOrder QuantityandOrder Amount ($)respectively.Create an additional pivot chart (copy over the previous one). Set the first chart type as doughnut, and the second chart type as bar graph. Place underneath the pivot table.
Above the pivot table, extend the horizontal slicers to match the size of the pivot table as well. Rearrange as necessary. It may sound feasible to remove the Month slicer because we can easily compare the monthly results year over year, but we will not remove it because it helps us to filter our graphs, which would otherwise show all 12 months at the same time.
Since we can see the direct numbers from the pivot table, we don't need exact numbers in the pivot charts. Instead, the pivot chart should be used to observe trends. Thus, we will modify our bar graph, which doesn't show us much information other than the Order Amount ($) values due to the sheer largeness of the order amount in comparison to the order quantity.
Double-click on vertical axis, and under Axis Options, check the Logarithmic Scale option. Then manually specify the Minimum value as
100. Now we have represented the values in logs of 10. The result should be as shown in the following screenshot:
Even though we may not have specific values, it is so much easier to observe the trends—whether the sales have increased or decreased over the months. We are able to observe in more detail if we use the horizontal slicers to filter using per product, or per year. And if we need specific figures, we can always refer to the preceding pivot table.
For instance, by filtering on yearly basis, we can clearly observe that the increase in number of products sold signifies greater sales revenue. Although it may sound obvious that the more products we sell, the more money we make, it is not necessarily the case as 2007 December appears to have sold fewer products than 2007 August, but has higher revenue ($ amount sold). Referring to the pivot table, we can observe that it is indeed true: December sold about 3,000 fewer products, but generated about $200,000 more than August.
We also notice in the bar graph that the online orders have increased sharply in the middle of 2007, and that orders have been increasing ever since. By filtering based on Months, the doughnut graph makes it easy to view year-over-year changes in the orders.
We can filter through more, but the format is not good as the pivot table seems to resize based on which slicers we use. Rearrange the orders to slicers, pivot charts, and then pivot tables. The slicer also has many criteria, and we can remove the overlapping ones. In this case, that would be the seasons, as we can manually filter by selecting the months, instead of the seasons (that is, clicking on 1, 2, and 12 using the Month slicer will give the same result as clicking on the Winter slicer). Thus, remove the Seasons filter.
Also remove Shipment Duration (days) as we are not interested anymore in few outliers, which took one day more to ship. That leaves more space for the Product Name slicer, enabling us to scroll through faster.
Now, since our pivot table, chart, and slicers look good, it's time to make the final arrangements using Excel.
Under the third tab, Page Layout, uncheck the View button of the Gridlines button. It removed all gridlines in the background, making it look more clean and crisp. Also change the color of the sheet to red, highlighting that this is the final, most relevant sheet. The final product should appear as shown in the following screenshot:
In short, the final preparation step for creating a presentation-worthy PowerPivot is as follows:
Rearrange all data such that the graphs and/or tables are not too wide or long, so that a glance should provide some understanding of the material
Rename all filters, tables, and charts so that anyone who is somewhat familiar with the material is able to comprehend at once what each items are
Perform typical Excel steps in creating an overview/result/summary sheet by removing backgrounds, highlighting sheets, and saving the file with a proper naming convention
We are done in preparing our file! The final product appears comprehensive, understandable, and presents both graphs for trends and table for actual figures. The background is clean, white space, the figures change based on our slicers, and we are able to make an analysis on it, and are able to send it to others to share our results!
We have managed to achieve this by rearranging, reconfiguring our data, renaming, and adding/removing items and backgrounds to make it look more appealing in both a data-analytical sense and in an aesthetical sense.