Formatting your Pivot Table – Part 2


Microsoft Excel 2010 introduced several special formatting features to dress up your boring Excel-based reports.  The first of these features is Sparklines.  Sparklines allow you to create a mini-chart within a cell to show the trend of data in a column or row.  The two primary Sparkline types are Line and Column data representations.  At least these are the two I will use here.  The Win/Loss Sparkline type would be a great way to represent daily stock price gains and losses.

I am going to add two Line Sparklines, one beneath each of the year columns of my data.  I’ll begin by clicking in the cell immediately beneath the Grand Total row in the 2002 column.  Then from the Insert ribbon, I’ll select the Line style in the Sparklines group.

The Create Sparklines dialog requires two pieces of information, the data range to be used in creating the graph, and the cell where the resulting graph should appear.  Yes, a Sparkline can span multiple cells, but that would not be appropriate here, nor is it done by defining a range in this dialog.  Notice that you can enter the cell ranges directly or you can use the button to the right of each of the range definitions to visually select the cells from the spreadsheet.  When done, click the OK button.

Note: You cannot define a multiple cell location range in the Create Sparklines dialog.  You can only specify a single cell even though the label says: Range.  However, after you create the sparkline, select the cell containing the sparkline and several adjacent cells in the same row or column or even a rectangular area and select Merge & Center from the Alignment group of the Home ribbon.

The following figure shows the resulting Sparklines under each of the two data columns.

For the Grand Total column, I will use the Column Sparkline style.  Because we have a relatively small number of data point, the individual columns are well defined and clearly show the relative increases and decreases in the data.  Note that the smallest value in the data range essentially defines the lower vertical axis value of the columns.

My last format change will be to conditional format some of the cells. I want to highlight in red all the months in which the cost of vaccines provided fell below the monthly average during this two year period.   I could calculate the average by using a separate cell and the AVERAGE() function.  However, I can use functions and calculations directly in the definition of the conditional format.

I’ll begin by selecting the cells that I want to format.  In this example, that would be the values for each month of each of the two years.  Then from the Home ribbon, I’ll click the bottom portion of the Conditional Formatting button to view the different formatting options.  Hovering over the Highlight Cell Rules I’ll select the Less Than option from the secondary menu as shown in the following figure.

When you first select Less Than… the Less Than dialog box appears and prompts for the value to compare the selected cells to in order to format those cells.  In this case, we do not want to compare the selected cells to a fixed value or even the value in another cell.  Rather we want to compare the selected cells to the calculated value returned by the following function:

= AVERAGE($I$5:$J$16)

Be sure to include the equal sign or Excel will try to interpret the value as a string instead.

I am going to leave the default formatting of a light red fill with a dark red text and just click the OK button.

This average monthly value over these two years should be 4,739,515 for the selected cells.  Therefore, any cell with a value less than this should be formatted with a light red fill and dark red text as shown in the following figure.

Now you might be wondering, after taking the time making all of these formatting changes, what happens if the user changes the dimensions or if there are hierarchy of dimensions and the user expands or contracts some of the dimensions.  Well, the good news is that the overall formatting of the measure data is retained.  However, the custom formatting applied to a specific range of cells does not expand or contract with changes in the pivot table.  For example, if we were to add quarters under years in our column hierarchy, the quarterly data would be formatted with the global format of currency.  The year columns would still be formatted as general numbers and they would still display the conditional formatting.  If we add more rows to the table, the sparklines would be overwritten.  They will not move down as rows are inserted by the pivot table.  In summary, all custom formatting is potentially at risk. So why bother formatting at all?  The bottom line is that you probably only want to go to the trouble of dressing up your Pivot Table with custom formatting when you are ready to capture it for a report.

Next time, we will look at one last feature with pivot tables, how to hide columns and rows and how to define different value row and column sets for displaying data.

Advertisements

3 comments on “Formatting your Pivot Table – Part 2

  1. Hi there. I have a similar problem, but the numbers I want to format are not in the sum area but n a column. So I want to list the different prices for a particular range of items, so I have put the price as a column, not a sum. I cannot get the format to stay 2 decimals. Can you help with this ?

    • If you want to format a column of data, go to the Design tab of the PowerPivot window and find the Formatting group. The first item at the top is Data Type. Make sure to select Currency. Then for the format, you can leave the format: General, but using the buttons below Format, select the rightmost button to reduce the number of decimal places.

      Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s