Excel Pivot notes: Table, Pivot Formula, 2D, 3D charts and secondary axes.
Posted by jpluimers on 2019/10/04
Since I don’t do Excel visualisations often enough, I always forget the details on Pivot Charts, some links and tips below.
TL;DR
You can’t have enough axes
The tips below assume you can create a pivot table from an existing table (that already can contain formulas), then show you:
- additional formulas in your original table can make life easier
- formulas for pivot tables themselves (named “Calculated Fields”)
Problem at hand
Creating graphs out of up and down time durations over time, aggregated by day.
Ideas for correlations that might matter:
- linear over time during a few weeks
- by week and by day of week
Incoming data:
- end-timestamp
- state (down or up)
- duration of that state
Calculations
First of all, I needed “day of month”, “day of week”, and “week number” so I could group by those. Based on Readable weekdays in Excel, you get formulas like these:
=DAY(B4)=WEEKDAY(B4)and=TEXT(B4;"dddd")=WEEKNUM(B4)
Then I needed to split the duration of the state in distinct up/down durations. So I made a few formulas:
=("Up", A4)to have a boolean for up/down=("Up", A4)to have a boolean for up/down=IF(D4;C4;0)to split the up duration from the state duration=IF(NOT(D4);C4;0)to split the down duration from the state duration
A pivot table could aggregate total up and down durations, but I wanted a measure of up ratio, so I needed a formula inside the pivot table itself.
Following the steps at [WayBack] Calculate values in a PivotTable Use different ways to calculate values in calculated fields in a PivotTable report in Excel 2010, I got to this one:
This aggregates nicely: drag it to the aggregates column, then change the aggregation to “Average”:
Putting it in a 3D Pivot Chart
The most tricky part was getting the 3-D rotation right, so lets start with that:
- No “Right Angle Axes”
- X = 35
- Y = 20
- Perspective = 5
Easier was to have both Weekday (numeric) and WeekdayText (text) in the legend: Excel automatically combines them and sorts them numerically.
That got me the below graph (from the top data): Average of UpRatio plotted against Row-axis WeekNumber and Column-axis Weekday – WeekdayText.
Secondary axes
Finally I wanted to see what happened linearly over time, especially wanting to see the number of up/down events during the day correlated to the up time ratio.
Putting that in a graph turned out wrong, as from the table on the right, I got this:
It is hard to correlate ratios being less than 1, to event counts being much larger. Luckily I found [WayBack] Add or remove a secondary axis in a chart:
When the values in a 2-D chart vary widely from data series to data series, or when you have mixed types of data, you can plot one or more data series on a secondary vertical (value) axis. You can also add a secondary horizontal (category) axis, which may be useful in an xy (scatter) chart or bubble chart.
The steps are easy:
- in the cart select the red series
- on that series, in the toolbar, select “Format Selection”
- move it to the “Secondary Axis”
–jeroen
Axe image credits (clock-wise from top-left):
- [WayBack] 2.5 lb. Single Bit Boy’s Axe with 28 in. American Hickory Handle-12120 – The Home Depot
- [Archive.is] Our War Hawk combines the tactical styling and material used in our popular Trench Hawk with historically inspired design elements from fighting hand-axes of old.
- [WayBack] Axe
- [WayBack] 3.5# Jersey Miner’s Axe w/ 26″ Handle
The Wiert Corner - irregular stream of stuff 
Leave a comment