Microsoft Office Tutorials and References
In Depth Information
Pivot tables are really powerful tools for exploring data. Nevertheless, they often serve as the
first step in the production of complex reports that gather data from a PivotTable, and then
perform computations, format findings, and provide the final results in compact reports,
sometimes called dashboards. Roughly speaking, a dashboard is nothing but a report that
contains several pieces of information, each one taken from a query to the database; the
result is a compact representation of the company status.
Let us suppose that you want to produce a report that contains the total sales, the growth in
percentage of the total sales, and the shares of Internet and reseller sales of the last three years.
The report should contain information divided by region so that you can use it to find which
regions need your attention.
In Figure 8-19, we show such a report in its final form. You can find the report in the
companion workbook CH08-06-Dashboard.xlsx.
FIguRE 8-19 AdventureWorks dashboard for sales by region.
As usual, we are more interested in technique than we are in the real results. So, with a
clear idea of the results you want to produce, let us take a closer look at the problems
you need to solve.
■ The first issue is with the geographical slicing. In the final report, you have data at the
group, country, and regional level, but the countries that have only one region have been
compacted, removing the region level, which is useless. A PivotTable does not handle this
sort of compacting by itself, even though it is a very common request for reports.
■ The headers contain total sales and growth divided by year (and the selection of years
in the two is different because growth is not shown for the 2002 year). Moreover, in the
columns header, you want to show years divided by sales type (Internet and reseller).
This mixed kind of slicing is not something that can be realized by a PivotTable.
■ Data in the cells is a mix of Internet and reseller sales for some cells (the total sales cells)
and a mix of computations in others.
■ Moreover, using the conditional formatting option in Excel, the report shows cells
of various colors to attract the attention of the reader to the most interesting ones.
Clearly, the conditional formatting uses different formulas for different cells.