Category: GRAPHS AND CHARTS

Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart

{21 Comments}

Assume a simple Sales dataset from which a Pivot Table has been created.  The Pivot Table has been sliced by two columns of the dataset.  To represent data graphically, a Stacked Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook).  The Stacked Pivot Chart has Months on the X-axis and each month has stacks for various products sold in that month.  By design, a Pivot Chart never displays data from the Grand Total column of a Pivot Table.  The Select Data button the Pivot Chart Tools button does not allow the user to reselect the Source data to include the Grand Total column.  The only option left in this case is to copy the Pivot Table and paste it as Paste Special > Values in another range and then create a Normal Stacked chart from this Table.  But in doing so, any change in the slicer or Base data will not have any effect on the Stacked Chart because the source of the Stacked Chart is a static range.

This problem can be overcome by using the PowerPivot tool and CUBE functions (available in Excel 2007 + versions).  You may download the solution workbook from here.

You may watch a short video of my solution here

Show Slicer selection on a Graph

{6 Comments}

Assume a simple Sales dataset from which a Pivot Table has been created.  The Pivot Table has been sliced by two columns of the dataset.  To represent data pictorially, a Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook).  Now let's say, a user makes a few slicer selections on the Pivot Table worksheet.  When one now clicks on the Pivot Chart worksheet, one does not see what selections were made in the slicers (which are placed on the Pivot Table worksheet).  So one has to go back and forth between the two worksheets to keep track of the slicer selections made.  One may want to view the slicer selections made on the Pivot Chart as well.  Changes made in the slicer selections should automatically reflect on the Pivot Chart worksheet.

This can be accomplished by using the PowerPivot tool and CUBE functions (available in Excel 2007 + versions).  You may download the solution workbook from here.

You may watch a short video of my solution here

Applying conditional formatting to column charts

{8 Comments}

Line of Business (LoB) wise CAGR (%) is shown in range A3:B8.  A benchmark growth rate is shown in cell B11.  The task to show a column chart of the data presented in range A3:B8.  LoB's with a CAGR (%) greater than the benchmark rate should appear in Green colour and those with a CAGR (%) lesser than the benchmark rate should appear in Red colour.  The colour of the column should change when:

a. Data in range B4:B8; and/or
b. The benchmark rate in cell B11 changes

You may refer to my solution in this workbook.

Create charts on different sheets by clicking a button

{6 Comments}

Assume a five column database with the first column containing codes, second one containing a separator, third one containing some alpha values, fourth one is a combination of the first three columns and the last column has response times.

One may want to create as many charts as there are codes in column A.  The charts should get created on different sheets with the click of a button.

You may refer to my solution in this workbook.

Updating charts for columns added to source data in Excel 2003

{0 Comments}

Having created a graph from a certain source data, if columns get added later to the source, they do not automatically append to the graph.  While this is easy enough to accomplish in Excel 2007 and higher versions, doing the same in Excel 2003 is a different procedure altogether.  Instructions are very clearly mentioned in this workbook.