How To Create A Pivot Chart With A Secondary Axis

If you're like me, you probably have banged your heads on this one. If you haven't had the pleasure to create a pivot report with a secondary axis, in Microsoft Excel, prior to today, consider yourself one of the lucky ones.

Typically, your pivot chart will contain one X and Y axis. For example, your X axis may represent employee names, while your Y axis may represent salary range. The reason is that when you drop any field into the data field of your pivot table layout, Excel will aggregate those fields.

Let’s say that you want to add another field to your pivot table’s data field, such as years of employment, Excel will then combine the two fields into one data column.

Now, when you go ahead and try to convert that to a chart, Excel will not split the columns, but rather post them on one axis on the chart. The bottom-line: your chart will look bad.

The only way you can have salary and years of employment as two separate axes, you must split the data column in your pivot table.

The video that I have put together will show you just how simple it is, and will save you a lot of anguish.

P.S. Loss of formatting is a known problem with pivot charts. Changing a PivotChart or even data refresh will remove formatting in your Excel pivot chart. Please read the following Microsoft Knowledgebase article, which suggests recording a macro as you apply the formatting.

Enjoy!