Look Ma, No Pivot Tables
Pivot Tables and Crystal Xcelsius: What's the deal?
For those of you who are not familiar with Excel pivot tables, let me first explain what a pivot table is. A pivot table is an analysis tool that allows you to create an interactive view of your dataset. With a pivot table report, you can quickly and easily categorize your data into groups and summarize large amounts of data into meaningful information.
Figure 1 demonstrates a pivot table that summarizes revenue for each product in a given month and region. As you can see here, this pivot table is set to show product revenue for the North region in January. Keep in mind that this pivot table is not merely selecting one row of data and presenting it. It is actually aggregating all the rows that meet the month and region criterion and presenting the sum value of those rows. That is the power of pivot tables.
Figure 1: Pivot tables allow you categorize and aggregate large amounts of data.
As you may well know, Crystal Xcelsius does not support the use of Excel pivot tables. That is to say, you can not use pivot tables or their functionality in your visual models. The primary reason for this is due to the way pivot tables work.
When you work with a pivot table on your spreadsheet, it may feel as though you are working with an object that is connected to your dataset. In fact, you are actually working with a disconnected cache of data called a pivot cache. The pivot cache is essentially a snapshot of your dataset stored in memory on your local system. This is why your must refresh your pivot table when your dataset changes; to take another snapshot. The bottom line is that when look at a pivot table on your spreadsheet, you are merely seeing values that have been returned by the pivot cache.
Now stay with me on this thought. When Crystal Xcelsius imports your Excel model, it captures all of the values and formulas on your spreadsheet. Since what you perceive to be a pivot table is actually a set of values that have been returned by the pivot cache, Crystal Xcelsius only imports those values. Crystal Xcelsius can not reach into your system and pull in the pivot cache that makes up the core of your pivot table. This is essentially why you can not use pivot tables in your visual models.
Now before you get that defeated look on your face, you must know that pivot tables are not the end-all-be-all when it comes to data analysis. There are a handful of Excel functions that, if used correctly, can help simulate pivot table functionality. In this article we will explore one of these functions and use it to create a dashboard that works like a pivot table!
Introducing the SUMIF function
Excel's SUMIF function allows you to sum up several rows in a range if a value in those rows meets a criterion you specify. In English, this means the SUMIF function tells Excel to add up the rows in a range of cells only if a condition is true.
The SUMIF function requires three arguments in order to work properly:
The syntax will look like this: SUMIF(range, criteria, sum_range)
Let's walk through a simple example of using the SUMIF function. In Figure 2, we have a table that shows us foods, colors, and units.
Figure 2: Basic table of foods, colors, and units.
Suppose we wanted to find out how many units of red foods we have. We could use the following SUMIF formula: =SUMIF(B4:D11,"Red",D4:D11). As you can see in Figure 3, giving the SUMIF formula the criteria "Red" adds up the units for all the red foods.
Figure 3: You can explicitly enter your criteria within the SUMIF formula.
Note: In a SUMIF formula, your criteria must come from the first column in the range.
Interestingly enough, you don't have to explicitly enter the criteria as in Figure 3. You can define your criteria by referencing a cell. In Figure 4, we reference cell A6 to supply the criteria value for the SUMIF formula. This essentially tells the formula to add up all the rows that have the color Yellow.
Figure 4: You can also use a cell reference to supply a criteria value.
Now that we have a basic understanding of the SUMIF function, let's look at how we can use it to create a dashboard that works like a pivot table.
Simulating a Pivot Table with the SUMIF function
We start with a basic table, shown here in Figure 5, that gives us the revenue and units sold by product for each region in our organization. This is further broken down by month.
Figure 5: Basic Table that shows revenue and units by product, region and month.
Next, we add a column that will give us a concatenated value that identifies the region, month and product for that row. This is the value we will use as the criteria in our SUMIF formulas. The concatenated value shown in Figure 6 was created by using the following formula: =B10&C10&D10.
Figure 6: Create a concatenated value to use as your criteria in your SUMIF formulas.
Now we copy the first row of our table (plus the headers) to the top of the Excel model. This is the area Crystal Xcelsius will work with. At this point your worksheet should look similar to Figure 7.
Figure 7: Copy first row of data to the top of the sheet. Note that the Key column is still a formula.
From here, we can replace the Revenue value in cell E2 with the following SUMIF formula: =SUMIF(A9:F252,A2,E9:E252). This formula tells Excel to return the sum of the "Revenue" column from the table in A9:F252 where the first column equals the value in cell A2. As you can see in Figure 8, the concatenated "Key" field we created is doing its job, serving as a field that Excel uses to categorize and sum.
Figure 8: Create the first SUMIF formula to return aggregate revenues.
Do the same thing for Units by replacing the hard-coded value with the formula shown here in Figure 9.
Figure 9: Create a SUMIF formula to return aggregate units.
Let's stop here and take a moment to think about what we've built. Each time we change the region, month or product, we get new aggregate totals! So the idea is to tie region and month to Combo Box components so a user can select which aggregate they would like to see.
Figure 10: The cells in yellow will be tied to combo box components, allowing users to interactively change the aggregate totals for the products.
Next, we'll hard-code the product names so they are in fixed positions on our dashboard. Then we will create the criteria key for each product by using the month and region values that come from the Combo Box components. Figure 11 demonstrates the formula that will do this.
Figure 11: Create a new row for each product and fill the criteria key by using the formula shown here.
All there is left to do is fill in the table by copying the SUMIF formulas down. In the end, you will have a table that looks similar to the one in Figure 12.
Figure 12: Copy the formulas down to fill the table.
Warning: Make sure the cell references in your formulas don't shift as you copy them down. You can use absolute references to avoid this problem.
I've taken this Excel model and built the dashboard in Figure 13 on top of it. As you can see, the dashboard has a pivot table feel to it. With each change in region and market, Crystal Xcelsius recalculates the aggregate totals shown in the grid.
Tip: View the step-by-step video on this very technique. Click here to watch the video!
As you think about what you have just learned, consider this; the SUMIF function is not the only function that can be used for these types of analyses. Crystal Xcelsius supports a whole range of functions that enable you to perform aggregate calculations; DSUM, DCOUNT, DAVERAGE and SUMPRODUCT are just a few. Familiarizing yourself with these supported Excel functions will open a whole new level of possibilities, allowing you to go far beyond the perceived limitations of Crystal Xcelsius.
Michael Alexander is the author of several books including "Crystal Xcelsius for Dummies." He currently lives in Frisco, Texas where he runs DataPigTechnologies.com, providing training to beginning and intermediate users of Excel, Access, and Crystal Xcelsius.