# The Wiert Corner – irregular stream of stuff

• ## Email Subscription

Join 2,158 other followers

## 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:

• 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:

1. linear over time during a few weeks
2. 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 [WayBackCalculate 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:

1. in the cart select the red series
2. on that series, in the toolbar, select “Format Selection”
3. move it to the “Secondary Axis”

–jeroen

Axe image credits (clock-wise from top-left):

This site uses Akismet to reduce spam. Learn how your comment data is processed.