Small multiple lines

Small multiples are created by repeating simple versions of the same chart, to allow the individual charts to be easily compared. They can be very useful as a way of avoiding overly complex line charts with lots of overlapping lines. You can, of course, make them by simply making lots of small line charts, but this method uses a single PowerPoint line chart to make lots of separate charts.

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: 

https://docs.google.com/presentation/d/1UExqGd35DYvxmcsaRdKA17oQmFEWmsfG?rtpof=true&usp=drive_fs

What you’ll need

• Line chart

• Data manipulation

• Chart manipulation

• Custom labelling

How you do it

We’re going to make a line chart with multiple consecutive data series and then introduce a gap between each series filled with a placeholder data point. By converting that final series into a column chart we can make a space, separating out the individual lines from each other.

Details

Insert the chart

We’re going to start by inserting a standard PowerPoint line chart

As usual PowerPoint will create an example chart and open Excel to show us the dummy data behind it.

The example gives us three lines laid over the top of each other, but we’re going to want them laid out sequentially across the chart.

To achieve this, we’re going to want to layout the data series sequentially as rows in the data.

We can just drag the values for each series down to make new rows. This will then separate the lines out across the chart.

Each of these lines is going to be, effectively, a separate chart, but they will all share the same y-axis and will therefore need to share the same x-axis. So we need to copy the x-axis labels for each line.

But this means we’re also going to need gaps between the lines. We can create these by adding in new rows between each data series.

We’re also going to need an element to make into the physical gap between the lines, so we’ll add a new data series, with values just in those gaps. The values will be something higher than any other value in the chart. In this case our highest value in the data is 5, so we’ll set this new data series to values of 6.

Once the new data series in added, we won’t need to do any more with the data and we can close the Excel chart

Tweak the chart layout

The first thing we need to do is to set up the x-axis. We can do this in the Format Pane. We can open this by right-clicking on the axis and selecting ‘Format Axis…’

We’re going to set the maximum x-axis value to be lower than the value we gave to our extra ‘spacer’ data series. But we also need it to be higher than the highest value in our actual data, so that we have some space around our lines. In this case, we’re going to set it to 5.5.

This also means that we need to set the Minimum value too, even if it's just to ‘0’, so that we can also set values for our gridlines in the Major units. By setting the Minimum value for the axis to 0 and then our Major units to 2.5, we get gridlines at 2.5 and 5, even though our axis now goes to 5.5.

Then we’re going to style the gridlines. We can do this in the Format Pane too. If we already have the Format Pane open we could just select the gridlines. Or we could right-click on them and select ‘Format Gridlines…’

We’re going to set the gridlines to dotted lines, just to de-emphasise them a bit.

Create the gaps

Now we’re going to make the gaps between the individual lines. First we need to select that extra ‘spacer’ data series. We can do that by just selecting the data series in the Legend.

With that selected, we’re going to change its chart type using the ‘Change Chart Type’ button in the Chart Design tab on the ribbon.

We’re going to change that data series into a column chart and then fill that column with the background colour of our slide to create what appears to be an empty space.

Add data labels

We’re also going to add markers and data labels for the beginning and end points of each line. We can do this in the Format Pane. If it’s not already open, we can access it by right-clicking and selecting ‘Format Data Series’

We can find the Marker options under the ‘Fill & Line’ tab (the little paint pot icon) in the Format Panel. We’ll have to select the ‘Marker’ button and open the ‘Marker Options’ panel. We’re going to select the built-in circle marker at an 8pt size.

This will add markers to all the data points and we only want them at the beginning and end, so we’ll have to select the markers in between and delete them. You can do this by just hitting ‘delete’ or selecting ‘None’ under the Marker options.

We’re going to then select the first marker and add a data label. We can do this by selecting ‘Data labels’ under the ‘Add Chart Element’ button in the ‘Chart Design’ tab on the ribbon.

We’re then going to repeat this process for the last data point in the series.

And then for the first and last data points in the other two data series.

Tidy the chart

Finally we’re going to remove the line on the x-axis. We can do this in the Format Pane. We can open this by right-clicking on the axis and selecting ‘Format Axis…’

We’re then going to set the line options under the ‘Fill & Line’ tab (the little paint pot icon) to ‘No Line’.

This will remove the solid line by the horizontal gridline will still be there. Crucially, though, this line is broken by our ‘spacer’ data series, giving us our apparently separate line charts.

So that’s how we make that in PowerPoint

Small multiples are obviously most useful in multiples, with many charts set up against each other, but it can be really useful in making line charts with lines that are very similar in value but which all need to be clearly distinguished.

Bars with change arrows

This is a common request: to add some indication of change into something like a bar or column chart. In this case, we’re going to add some little up and down arrows to indicate whether the key value has changed.

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: 

https://docs.google.com/presentation/d/1JuMc8Inkf8t5snQSVtGp7qffzfGwUbV3?rtpof=true&usp=drive_fs

What you’ll need

• Stacked bar

• Data manipulation

• Chart manipulation

• Design tweaks

• Custom labeling

How you do it

We’re basically going to create a stacked bar, in which one segment visualizes the data and then use other segments as containers for the custom visuals. We’ll use Excel formulas to create segments for rising and falling values and then use custom fills to add appropriate arrows to those segments.

Details

Add the chart

We’ll start by adding a stacked bar chart. In this case we’ll use the standard stacked bar rather than the 100% option.

PowerPoint will then create an example chart and open Excel to show us the dummy data its populated with.

Although PowerPoint opens a whole Excel spreadsheet to show us that data, its only every visualising the section in the blue frame. We can move that around and not affect the chart at all.

However, the spreadsheet still operates as a conventional Excel document. We can add data, formulas and other content to it and it will all be saved in PowerPoint.

Add the data

In this case, we’ll add a table with our actual data in. We’ve got a column of values and then a column of change (from some previous time period, presumably).

Now we’re going to add a formula. What we’re going to want to do is to have one data series that shows our values and then a data series (or two) that contain our arrows. But we want the latter to line up neatly, which means we’re going to want a series in between to make a gap to even everything out.

We’re going to calculate this based on the maximum value in our values series. We can use the Excel MAX() function to do this.

Calculate values to visualise

We’re then going to create rows for all our values inside the blue frame, to add them to our chart. We can name our categories by simply referring to our data table, using the Excel ‘=’ function.

We can do the same to add a column of our actual values.

This then gives us our basic bar chart of our data.

But now we’re going to add a gap at the end of each bar so that our arrows will line up vertically.

We’re going to calculate this by subtracting the value of the data point from the maximum we found earlier, so the formula will look something like:

=SUM($E$2-G2)

Don’t forget that adding the ‘$’ to cell references will mean that Excel won’t try and update the reference when you copy it down a column. Instead it will stay referring to a specific cell. The other reference, however, will update to refer to all the cells in our Value column.

This then gives us a set of bars with equal totals: the values and then the remainder of that value subtracted from the maximum.

Add data series for arrows

We’re then going to create a data series for each kind of marker we want to add. We’ll want an arrow for positive change, so we’ll add a data column for that.

Now we want to fill those cells only if we have a positive change in our change column.

We can do this easily but using an Excel IF() statement. They take the form of IF(thing to test, do this if that’s true, do this if its false). In this case, IF (change is positive, add a value to this cell, otherwise don’t add anything).

The value we’re going to add if it’s true is totally arbitrary and depends on the rest of the chart. Basically we want a section of bar long enough to contain an arrow and a data label. We can devise a formula to calculate this, but in this case we’ll just set it to ‘1’.

So our resulting IF() statement will look something like this:

=IF(C2>0,1,NA())

That NA() simply defines a cell as not having a value in it at all, stopping PowerPoint from visualising anything.

We’re then going to want to do the reverse for negative change - testing to see whether the change is negative and filling those cells with the value ‘1’:

IF(C2<0,1,NA())

You can see that this then ends each bar with an equally sized section, but in two different data series. PowerPoint has coloured these using default colours from our palette: green and yellow.

We’re also going to add a column for where there’s no change:

=IF(C2=0,1,NA())

In this case, this makes no difference, as all the values have changed, but it could prove useful in the future.

Now we've done all we need to do with the data - for the moment - and we can close the Excel spreadsheet.

Layout the chart

To start with we need to get the basics of the chart sorted out. We can start by getting the vertical axis in the right order (PowerPoint always reverses the order from the data for some reason).

We can do this in the Format Pane. To open we can right click on the axis and select ‘Format Axis’.

We can then just tick the ‘Categories in reverse order’ to flip the axis the right way up.

While we’ve got that open, let’s remove the axis line from the y-axis, as its entirely superfluous. We can do this by clicking on the Paint Pot icon in the Format Pane and then selecting ‘No line’ under the Line options.

There’s a bunch of other chart elements that we don’t need too.

Because we’ve manipulated the data to make the chart, the legend doesn’t help, so we can get rid of that. We can just select it and hit delete, or right-click and select ‘Delete’.

Another way to delete things is to untick them in the ‘Add Chart Element’ menu, which we’ll do to the horizontal axis and the gridlines.

the chart

Now we’ve got the basics out of the way, we can concentrate on the visualisation. To begin with we want to make our ‘gap’ data series at the end of each bar go away.

We can do this by selecting the data series and selecting ‘No Fill’ in the Fill options. You can do this in a number of ways, in the Format Pane, for instance, or just using the usual Fill tools in the ribbon.

Add the arrows

Now we can add the arrows. This means creating a custom fill. 

We can start by adding a rectangle using the Shape menu in the Insert tab on the ribbon.

We can set that rectangle to be roughly the same proportions as the blocks for the change data series that we added in the spreadsheet - the green and yellow segments in our chart.

Then we’re going to remove the fill colour from that rectangle by setting it to ‘No Fill’.

Now we’re going to add our arrow for positive change. We’re going to just use an upward pointing triangle.

We’re going to want to position that triangle over the rectangle background we’ve made.

We can then fill that triangle with whatever colour we want to use for positive change.

And we’re going to want to make sure that it’s properly vertically centred against the rectangle, although we’re going to want to keep it over to the left hand side.

With both still selected we’re then going to remove then going to remove the outlines.

This will leave us with a green triangle against an entirely transparent rectangle. We’re going to want to keep them both selected and copy them both.

Then we want to select the data series for positive change - the green series in our example - and open the Format Pane. We can do this by right-clicking on any data point from that series and selecting ‘Format Data Series’.

Then, under the Fill options, we’re going to selecting ‘Picture or texture fill’. By default this will fill all this data series with a picture of a canvas texture.

Underneath, however, there are ‘Picture source’ options and we can just click on ‘Clipboard’ to fill the bars with the shapes we just copied.

As you can see, we added the rectangle in the background to give us a bar fill that just features a triangle on the left, with everything else being transparent. Everything’s a bit distorted at the moment, but we’ll get to that.

First we want to add arrows for the negative change. We can do this in exactly the same way. Let’s recolour that triangle for negative change.

And then invert it so it’s pointing downwards.

Then we’re going to select both it and the invisible rectangle (the easiest thing is to just drag around them both) and copy them.

Then we’ll select the negative change data series.

And do the same as the positive change - fill from the clipboard.

And we have our up and down arrows for change.

One more thing, let’s tweak those proportions. Basically, our bars ought to be closer together. We can set this in the Series Options in the Format Pane. By lowering the Gap Width we make the bars wider, which gets the proportions of the triangles looking nicer.

Add labels

We also need some data labels. The labels for the main bars are easy: just right-click on the bars and select ‘Add Data Labels’.

We can then format those labels in the Format Pane by right-clicking on them and selecting ‘Format Data Labels’.

We can set them to appear Inside End on the bars and set the text colour to contrast against the bar fill.

We’re then going to do the same with the change segments. We can right-click on the positive change arrows and select ‘Add Data Labels’.

But these labels are just going to read ‘1’, as that was the default value we gave to all these segments. We can change that though.

We can start by opening the Format Pane for these labels.

Then, in the Label Contains options, we’re going to select ‘Value From Cells’ options. This will open the Excel spreadsheet with the chart data in. It will then open a dialog box and ask us to select a cell range to use as labels.

We’re going to use our original column of change values.

We can then untick all the other options - these are the only labels we want. Because we’ve set the bars with no positive change to NA(), PowerPoint doesn’t try and label them.

Because we’ve set up the positive and negative changes as separate data series, we’re going to have to repeat this operation for the negative change too.

We’ll select the same column for our Value From Cells labels, and this will then add the negative labels to this data series.

So that’s how we make that in PowerPoint

The hardest part of making these is getting the arrow data series and the arrow graphics themselves the right size, so they look good once they’re added to the chart. It may take some tweaking, especially if you’re making something that you’re going to want to reuse, but it’s worth it.

The great advantage of these change arrows is that they allow you to emphasise the main values but still add in the change. It’s always better to add a separate visualisation rather than just keep trying to shoehorn in elements onto a chart until the whole thing becomes impossible to understand.

Sized arrows

Proportionally sized arrows are often a useful - and eye-catching - way of showing change, especially given how visualising using area means we can accommodate wide ranges of data and still have them all visible.

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: 

https://docs.google.com/presentation/d/1JuMc8Inkf8t5snQSVtGp7qffzfGwUbV3?rtpof=true&usp=drive_fs

What you’ll need

• Bubble chart

• Data manipulation

• Chart manipulation

• Design tweaks

• Custom labelling

How you do it

We’re basically going to use custom fills to turn the seized bubbles of a bubble chart into sized circles. While we’re at it, we’re going to use one series of bubbles to add a bunch of custom labels to a bubble table that are driven by the data spreadsheet and don’t have to be edited by hand.

Details

Insert the chart

We’re going to start by inserting a Bubble chart, which you’ll find under the Scatter plots in the PowerPoint chart menu

As usual PowerPoint will generate an example chart and open Excel to show the dummy data behind it in a spreadsheet

Although it opens a whole Excel spreadsheet, the PowerPoint charting engine is only ever looking at the data inside the blue frame. We can move that data around and PowerPoint will just carry on visualising it, ignoring the rest of the spreadsheet.

But that spreadsheet still works like an Excel spreadsheet, so we can use it in different ways to help manipulate the data we’re visualising.

Add the data

We’re going to add the data we’re going to visualise in a different part of the spreadsheet. We’re going to make a table of sized arrows, so we’re going to make a table of our example data. We’ll make some of the values negative, as well, so we can have two kids of arrows - positive and negative.

Layout the data

We want our visualisation to be a table of sized arrows, so it’s only the size of the arrows that will actually be linked to our data. We’re going to use the X and Y values just to layout the table into rows (from the Y values) and columns (from the X values).

By adding three rows of data, with X values of 1, 2, 3 but all the same Y values (and all the same Size values), we get a row of bubbles, all in a line.

We’re going to use this row to create our column headers using custom labelling, so let’s prepare for that by adding the column names into an Excel column outside of our data frame (we’re also going to call this data series ‘Labels’, just so we’ll know what is going on if we ever try and reuse this chart).

We’re then going to create two rows in our data by adding two rows to our data. Both the same X value this time: 0, but Y values of 2 & 1, giving us two bubbles down the left hand side of the chart.

These will be the labels for our rows, so we’ll add those names to the column by the side of the data frame.

Generate the data

We now need to add the data for our actual arrows. We have two rows and three columns of data, but as also have two data series: positive and negative. We’re going to want these series to look different, so we’re going to add them as different data series in our spreadsheet.

PowerPoint normally differentiates between data series by requiring each one to be in a distinct Y values column. For a bubble chart this becomes two new columns: Y value and size for each data series.

So to create our new Positive data series, we’re going to add two new columns for the Y value and the Size.

We’re then going to add three new rows for the X values for the three columns: 1, 2 and 3.

The Y values for these three new Excel rows will all be the same: 2, to create  a new row directly under the top row of blue bubbles we already have.

BUT we only want to add values where we have a Positive value in our data, so we’re going to have to use an IF Excel formula to test whether the value in question is higher than zero. IF statements in Excel take the form: test something is true, if it is true do this, if its false do this.

So the IF statement for our first Y value will be:

IF(B2 >= 0, 2, NA())

The ‘NA()’ just fills the cell with a null value, meaning PowerPoint won’t try and visualise anything.

We’re going to want to do the same thing for the Size, but here, having checked that the value is above 0, we’re going to want to add the value of the cell, as this is what we’re going to visualise in our bubble.

IF(B2 >= 0, B2, NA())

Then we’re going to want to repeat this process for the next value in our table.

This value is negative, so we’ll get a NA() entry. We’ll do the same thing for the final value in this first row in our data table.

You can see that PowerPoint has now added two new circles as a second row in our bubble table.

Now we repeat the same process for the second row in the Excel table.

And we get another circle in the bottom row of the bubble table, as we only have one positive value in that row.

That’s all our positive values, so now we’re going to add two more columns for the negative values.

As before, the first column will be our y-values, placing our bubbles into rows, but, like before, we’re only going to want to add a bubble if our value is negative. So we’re going to write another IF statement, but this time testing if the data is below 0 and, only if it is, giving a y-value of 2 to make our first row.

IF(B2 < 0, 2, NA())

And then we’re going to want to run the same test to add the size value for the bubble, but this time with a slight change.

Sized scatters can visualise negative numbers, but this is done by default using a bubble with only an outline, no fill. We’re going to create our own distinction between positive and negative numbers using these series, so we don’t want our value to be negative. We want to plot a positive value but then style it as negative in the chart design. Fortunately, we can easily convert a negative number to positive using the ABS() function in Excel, which gives us the absolute value of a cell: ABS(B2), for example.

So our IF statement in this case will look like this:

IF(B2 < 0, ABS(B2), NA())

Then, as before, we’re going to copy those IF statements for all the data points in our table.

This gives us our 3 x 2 table of positive and negative numbers.

Label the rows and columns

While we have the data sheet open, let’s add the custom labels for the rows and columns, as we’ll need to reference the data to do this.

We’ll select that first data series - the same-sized bubbles across the top and down the left hand side, and add data labels to them. We can do this by right-clicking on the bubbles and selecting ‘Add Data Labels’.

We then want to format those labels, so we’ll select them (you can just click on any of the labels and PowerPoint should select all of them by default), right click and select ‘Format Data Labels’ from the dropdown.

This will open up the Format Pane. Under the Label Options tab, under ‘Label Contains’ we’re then going to select ‘Value from Cells’.

This will then open a dialogue box, asking us to select a range cells in our Excel spreadsheet with which to populate the data labels.

We don't have to select cells from a data range, they can be any cells in the spreadsheet but the range must be the same size as the data being labelled. So we will select the whole column containing the label content that we created earlier.

Once we’ve selected that column we don’t need any of the other, default label content, so we can untick them in the options. We can then centre our new labels in the bubbles.

And now we can close the spreadsheet and concentrate on styling the chart.

Layout the chart

First we’re going to get our table neatly laid out. We’ll start by refining the axes. We can open the Format Pane for the x-axis by right-clicking on it and selecting ‘Format Axis’.

We’re then going to set the Minimum and Maximum bounds for this axis. We have three columns along the x-axis at the x values of 1, 2 and 3. We want to centre these columns in the plot area, so we’ll add an equal amount of space to either side by setting the axis to run from 0 to 4.

So we set the Minimum Bound to 0 and the Maximum to 4.

Now we’ll do the same to the y-axis. If we click on it with the Format Pane still open, the pane will update to show us the values for that axis.

In this case, though, we want to leave some room for our column headers, so we’ll set the Maximum to 3.5, and the Minimum to 0.

But now we no longer need those axes. They don’t show any relevant data, those values are just there to make our rows and columns, so we can delete them.

We can do this by de-selecting them in the Add Chart Element menu under the Chart Design tab.

We can get rid of the gridlines, too, because they’re not serving any real purpose either.

The last thing we’re going to do with the layout is polish up those row and column headers. We’re going to select the bubbles behind the labels.

And then we’re going to set them to No Fill and No Outline to make them completely invisible.

This leaves us with just our row and column labels showing.

Make the arrows

Now we can get on and make the sized arrows to show the two data series. We’re going to do this by creating custom fills for the bubbles. So we’re going to start by adding a circle to the slide, to give us our bubble shape. We can do this using the Shape menu under the Insert tab.

We can make sure that the shape is properly even by setting the width and height in the Shape Format settings.

We’re then going to remove the Fill from the circle, leaving just the outline.

And we’re also going to move that circle off of the slide. This is a really useful little feature. We can have all kinds of graphics stored outside the bounds of the slide - they won’t show up when we’re in presentation mode, but they’ll still be there when we need them.

Now we’re going to add our arrow shape by adding an upward pointing triangle to the slide.

We then want to centre it over the top of our circle, aligning it with the top edge and making sure all the tips touch the circumference.

We can then style that triangle with whatever fill colour we want to use. We’re going to use the options in the Format Pane to do this because we also want to give it a 20% transparency, making it slightly see through. This will come in useful if our shapes overlap.

We’re then going to select both the arrow and the circle underneath - you can do this by holding down shift as you click one after the other, or by simply dragging around both. And then we’re going to remove the outlines from both by selecting ‘No Outline’ under the Shape Format tab.

The circle in the background is now entirely invisible but, crucially, PowerPoint still knows it’s there. We now have a semi-transparent arrow within an invisible circle. Now, with both still selected, we’re going to copy them.

Then we’re going to select the first series of bubbles, the series showing positive change, and open the Format Pane.

In the Format Pane we’re going to set the Fill options to ‘Picture or texture fill’. By default, PowerPoint will fill the bubbles with a canvas texture.

We can change that by hitting the ‘Clipboard’ button underneath. This will fill the bubble with whatever is on the clipboard, in this case our arrow inside the invisible circle.

Now we’re going to make the custom fill for the negative change. We’re going to do this by selecting the semi-transparent triangle and flipping it upside down.

We should also change the colour of this data series, but we need to ensure that whatever colour it is, its still 20% transparent.

Then we’re going to select both the triangle and the invisible circle behind it (it’s easiest to just drag over both in this instance), and this time align the triangle to the bottom of the circle.

And again, with both selected, we’re going to copy them.

The we’re going to do the same thing as we did with the first data series: select the negative data series and Fill with Picture or texture fill from the clipboard.

So we now have our two data series, positive and negative change, shown by up and down arrows.

Add labels

We can easily label the triangles using the Add Chart Element menu by selecting the positive data series and then choosing ‘Centre’ from the Data Labels options.

By default PowerPoint will label using the y value, but we can change that in the Format Pane. We can right click and select ‘Format Data Labels’.

In the Label contains options, we can switch the label to ‘Bubble size’, which is our data point.

Now we want to do the same for the negative data series. We can simply right click on it and select ‘Add Data Labels’.

Then we’ll need to change those labels to ‘Bubble Size’ in the Format Pane.

However, because we set the Size value to be absolute, to get a full bubble, that label is now positive, when we need it to be negative. Fortunately we can set a Custom Number format to amend this.

We then want to make the positive value appear negative, so we can just set it to have a minus sign, “-”, before it.

The number format structure is format for positive number, semi-colon, format for negative number. The format for the negative number already has a minus sign in front of it: -#,##0 (the ‘#’ signs simply show the formatting for the thousands separator, then the 0 the decimal points), so we can adjust the positive number by adding: “-”#,##0 (the inverted commas let PowerPoint know we’re adding text to the number.

So we now have positive and negative labels for our positive and negative data series.

Emphasise the data

There’s one more thing we can do. At the moment we’re just got a bunch of little arrows and nothing looks terribly impressive, but we can fix that.

If we right-click on a data series and select ‘Format Data Series’, we should get the Format Pane with the Scale Data Series option

For reasons I don’t pretend to understand you can scale between 1 & 300. I have no idea what those numbers mean. If we scale to 200, say, we get nice big arrows and we start to see how having a little transparency can help when triangles overlap.

So that’s how we make that in PowerPoint

I’ve deliberately chosen rises and falls here, so I could show you how to make different sized shapes for different data series, but obviously you could use this technique to fill your bubbles with any shape you wanted.

You should, though, restrict yourself to simple shapes: squares and triangles and circles. Anything more complicated and it gets harder and harder to judge the differences in area and thus harder and harder to get anything meaningful out of the chart.

Definitely NOT sized icons, right?

Thistledown

The trouble with radar charts is how useful they are. They give us a really easy way to draw shapes with data. This makes them really tempting to use to make small multiples, making a whole series of shapes so that we can quickly see the difference between them, the difference between a series of categories across a range of datasets.

And this is the trouble: because radar charts draw continuous lines, making complete shapes, they suggest causal and sequential relationships between those categories which, unless they’re days of the week or hours of the day, there rarely is.

Fortunately this problem is also its own solution. We can use radar charts to draw shapes - shapes that are more useful for tracing these relationships, like polar area charts, quadrants or thistledowns.

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: 

https://docs.google.com/presentation/d/1IbmUxJgxBtLa8x7QAGPTB62frOsBqTGg?rtpof=true&usp=drive_fs

What you’ll need

• Radar chart with markers

• Data manipulation

• Design tweaks

How you do it

We’re going to use the radar chart to draw lines from the centre, ending in a marker for the data points. Because we’re using the actual values we can easily use the built-in data labels and legends to add detail to the chart.

Details

Insert the chart

Add a Radar with Markers chart to the slide - they’re hidden away under ‘Waterfall’ charts in the menu for some reason:

As usual PowerPoint will insert a dummy chart and then open Excel to show the example data behind that chart

Although PowerPoint opens an entire Excel spreadsheet, it's only ever actually visualizing the data in the blue frame - we can move that frame around so we can work with the rest of the sheet and the PowerPoint chart won’t care.

This means we can have our data in a nice orderly table and then manipulate it to make the chart.

Manipulate the data

Firstly, that first column - the x axis around the outside of the radar chart - is in date format. We don’t actually need that, so we can reformat it to make it a little more legible. We can do this by selecting the cells and then changing the format, either using the Format menu or just right clicking and selecting ‘Format cells’.

We’re going to reformat them to just numbers.

This will give us some very weird looking results - if you’re interested in why, try investigating the ‘Unix Epoch’ and if that doesn’t sound enough like a beach-side spinner rack sci-fi novel to pique your interest, I don’t know what will.

Anyway, we can renumber that column to make more sense. We’ve got ten data points, so lets number it from 1 to 10.

Now we want to add our data.

Each data point is going to need it’s own series, so we can have each line coming from the centre outwards, so each data point is going to have to be in a separate column of y values.

To add to the complication, we’re going to want to draw round in a circle, so, for the moment, our first column is just going to be a single data point.

The second data point is where the fun really begins. Each data series needs to overlap with the previous, because we want it starting on the same radial as the previous data point. Each data series will also start with a ‘0’ because we want to draw a line from the centre of the visualisation out to the data point, which is our second row in this column.

We’re then going to repeat this for the other data points: creating a new column of y values, adding a 0 as the first entry and then our actual data point for the other.

We are going to follow that all the way down our column of data.

We’ll finish by adding a final ‘0’ in the first column, adding the start to our original data point.

Now we can close the data sheet and concentrate on the chart itself.

Design the chart

The first thing to do is to sort out the axes. The vertical axis is a bit busy and dominant, so we can simplify it a bit. Find the axis options in the Format Pane - the easiest way to do this is to right-click and select ‘Format Axis’ from the options.

Under the Axis options, we’ll set the Major Units to half the total value of the axis, giving us a halfway line.

We could also calm those gridlines down a bit by selecting ‘Format Gridlines’

Then we can set them to a dotted line, making them a little visually lighter.

As far as the axis around the outside goes, we don’t need it at all, it’s simply there to arrange the data, so we can go ahead and delete it.

Now we want to style up the data points

Style the data points

We’re going to start by selecting the last of the data series. This is simply because its slightly easier to go anti-clockwise around the chart. Open the Format Pane for that data series.

The Format Pane should default to the options for the line. Here we can set the line width and colour we want.

Then we can switch to the Marker settings using the buttons at the top of the panel. If we then open the Marker Options we can set the marker style and size.

We can then set the marker colour to match the line (and also remove the border by setting it to ‘No line’).

Then we want to select the bottom marker, the one in the centre of the chart. Clicking it twice should select it: once selects all the markers, twice the marker you’ve clicked on.

Set this marker to ‘None’ in the Marker Options panel. This will remove that central marker, leaving the one on the end of the line.

There is an alternative way of doing this: you can remove all the makers and use the End Arrow options for the line itself to add a circle onto the end of it, although this gives you less control over size and appearance (and PowerPoint can be a little fussy about it, too).

We then repeat that process for all the data series.

So that’s how we make that in PowerPoint

And here’s where I admit that I made up the name ‘thistledown’. I’m not sure this chart has a proper name, but they are useful alternatives to radars when you want to use multiple charts to allow the audience to see differences between data series across multiple categories.

There are also lots of different styling options, using different markers or line arrows, or even not having markers at all and just letting the lines make a star shape - especially if you add a little transparency to thick lines and generate a little starburst in the centre of each chart.

Bar bells

Bar bells are a really great way of showing ranges - either the range within a dataset or the difference between two data points. They can even be used as an elegant way of showing uncertainty, a range which the data may fall.

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here:

https://docs.google.com/presentation/d/1IOQaYhFiCZKHqIufCU6eHQ9jsibf8bSM?rtpof=true&usp=drive_fs

What you’ll need

• Connected scatter chart

• Data manipulation

• Design tweaks

• Custom labelling

How you do it

We’re going to use a connected scatter to make the actual bar bells along the axis - a line with markers at each end, using the y axis values to line the bar bells up into rows. We’re then going to use the ‘Value from cells’ custom labeling option to replace the y-axis with row labels.

Details

Insert the chart

Insert a connected scatter chart or, as PowerPoint calls it, ‘Scatter with straight lines and markers’:

As usual, PowerPoint will create a dummy chart then open Excel to show us the data behind it.

Manipulate the data

Although PowerPoint opens a whole Excel spreadsheet to show us the data, it’s only visualising the cells surrounded by the blue frame. And it only ever does. We can move those cells around and PowerPoint will still only see them:

We can still use the rest of the spreadsheet as a spreadsheet, though, out of the sight of PowerPoint.

We’re going to put our data into a little table outside of the frame and then set up the cells within the frame to create the right structure for our visualization.

We’re going to use the x-axis to show our actual data, so we’re going to add our values into the X-values column, pair by pair:

To keep each range as a separate pair of values, we want to make each pair a distinct data series. For PowerPoint each new data series is distinguished by being a new column of y-values.

We’re going to give each pair the same y-value, which will line the pairs up into rows. We’re giving the first pair of values the y-axis value of 3, to make them our top row:

By giving the other pairs the values of 2 and 1 respectively, we can make two more rows in our chart:

Prepare the labels

We still have that empty column of y values - an empty data series. We’re going to use that column to make a data series to hold our row labels.

First move all the other data series down three rows, as we need to make space for three row labels.

Then we’re going to add a data series at the far left, at the beginning each row, so we’ll give everything a x-axis value of 0, and then the y values of 3, 2, 1, corresponding to our rows:

Having done that, we can close the data sheet for now and concentrate on the chart.

Layout the chart

First we want to adjust the vertical axis. Select it and open the Format Pane (you can do this by right-clicking the axis and selecting ‘Format Axis…’ from the menu:

In this case PowerPoint has generated an axis of 0 to 3.5. This is the axis we want, but we want to stipulate it as custom Maximum and Minimum for the axes to stop PowerPoint changing it as we make other adjustments, so we’re going to set the Minimum and Maximum Bounds in the Format Pane:

Now we want to adjust the horizontal gridlines - we want to use them as the threads along which our bar bells will sit - so we’re going to set the Major unit for the y-axis to 1, giving us a horizontal gridline for each of our rows are 1,2 and 3:

Because we set the maximum of the y axis to 3.5, we get a gridline at 3, but still have a little headroom, so our bar bells aren’t jammed right up against the top of the chart.

Having done this, we can delete the vertical axis, as its not doing anything other than lay out our rows. We can do this by unticking it in the Add Chart Element menu in the Chart Design tab.

We also want to delete the vertical gridlines

While we’re at it, we might as well format the horizontal axis too. Open the Format Pane for the x-axis:

And set the Major Unit to 1, to make it a little less fussy:

Style the chart

Now we’ll set the appearance of the bar bells themselves. Click on the first data series to select it and open the Format Pane.

Under the Fill & Line options (the paintpot icon), we’ll up the line thickness a little to make it more dominant:

But that means we also have to tweak the markers. We need to the select the ‘Marker’ option at the top of the panel.

In there we can select a Marker style (always better to do it deliberately than rely on defaults) and increase their size. We want them to be a decent size because we’re going to add labels inside them.

We can add labels using the Add Chart Element menu, choosing to position them in the Centre of the marker.

We then need to make sure we’re showing the right label - select label (clicking on one should select both by default) and open the Format Pane.

In the options for Label Contains we can select X Values and deselect the others.

We can then style the labels using the usual text styling options

Because PowerPoint differentiates each data series by style, we have do the same thing for each of the data series:

We need to make sure the labels are legible against the markers

Now we can turn our attention to our row labels

Label the rows

We can start by selecting the label series running down the vertical axis and adding labels to it:

Now we want to customise those labels, so we select them and open the Format Pane

Under the Label Contains options, we select ‘Value from cells’, which will open the data sheet for the chart and ask us to select a range of cells to use as labels.

This can be any column in the spreadsheet, as long as the row count matches the rows of the data being charted.

By selecting the column of series names, those become the labels for the data points

We can now deselect the other label options

Now we want to remove the actual visuals for this data series, as they’re only there to position the labels. We can do this by selecting the data series and opening the Format Pane:

We can then remove the line by setting the options to ‘No line’:

And similarly, remove the Markers by selecting ‘None’ under the marker options

One last touch - at the moment the labels are laying over the top of the gridlines, making them hard to read. We can remedy that by giving the labels a background colour, covering up the gridlines

So that’s how we make that in PowerPoint

Bar bells aren’t that hard to make, and are extremely useful, especially when we want to emphasise the difference between data points, rather than just show the data points themselves.

Using different colours for markers can help with the comparison, although that all has to be done by hand, sadly.

Waffle charts with icons

Waffle charts give us a striking alternative to pie charts for composition stories and they can be amped up visually further by turning them from a grid of shapes into a grid of icons.

There are multiple ways to make waffle charts in PowerPoint by using columns, stacked columns or scatter plots. In this case we’re going to make a waffle using a scatter plot, replacing all the markers with icons.

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: https://docs.google.com/presentation/d/1HRjfX-m3Ik907bwgdW5eIu4nqrthuMos?rtpof=true&usp=drive_fs

What you’ll need

• Scatter plot

• Data manipulation

• Excel formulas

• Design tweaks

• Custom labelling

How you do it

We’re going to use a scatter plot to make a 10 x 10 grid of 100 hundred dots, giving us our waffle. We’re then going to fill all the markers in the scatter with icons, make an icon grid.

Details

Insert the chart

Insert a standard scatter plot

As usual PowerPoint will add a dummy chart to the slide and open Excel to show us the data behind that chart

In PowerPoint charts, the chart only visualises the data within the blue frame in Excel. We can move that data around and PowerPoint will carry on visualising it and ignoring the rest of the spreadsheet, which is very useful for messing about with the charting engine.

Structure the data

We’re going to use the data in that frame to make our 10 x 10 grid. The first column is the horizontal - x - values, the second the vertical - y -values. We’re going to make our first row of ten dots by creating 10 rows on the x axis, numbered 1 to 10.

And then giving each of those rows the same y value: 1 - creating a row of 10 dots across the plot at 1 on the y-axis.

We’re then going to do the same thing, but for the y value of 2, giving us two rows of dots.

Do this ten times and you end up with ten rows of ten dots, a grid of 100 dots.

Calculate the data

Now we’ve got our grid, we’re going to add in the values we want to show in that grid. Three percentages that add up to 100%.

Those values are currently formatted as Excel percentages, which means that, in reality, they’re fractions of 1 - Excel multiplies them by 100 to present them as percentages. We’re going to want to do some calculations with them and it would be easier to do them on the whole numbers, so we’re going to use the NUMBERVALUE() function to get the first value as a number and multiply by 100. As an Excel formula that would be something like:

=NUMBERVALUE(B2)*100

For the other values, we’re going to want to create a running total, as this will help with the calculations we have to do to fill the grid, so we’re going to calculate the actual number and then add it to the previous value. This means the formula has to update to something like:

=(NUMBERVALUE(C2)*100)+F2

We’ll do this for all the values (this will mean that the final value will be 100, the total for all the values in this case).

Now we’re going to use those values to create data series in our chart.

In most PowerPoint charts data series are differentiated by being placed in different series of y values, so our first data series is going to replace our current column of y values.

Basically we want to add values to the y series until we have enough dots to match the data point we want to chart. The values we want to add are the values that made our rows of dots - ten ones, followed by ten twos, etc. In this way we can build rows of dots, starting from the bottom left, until we have enough dots to match our data point.

To do this we basically want to work through each dot in our grid, starting from the bottom left, comparing the number of dots to the data point. Once they’re equal, we stop add dots.

This is made easier because our grid of 100 dots is made up of 100 rows of data - ten rows of ten dots. We can use the ROW() function in Excel, which returns the row number the cell is on, we can then use that number to compare the data point we’re looking at with the target value. The one wrinkle is that we have column headers, so our data starts at row 2, which means we’re going to have to use ROW()-1 to get the right number.

This also makes figuring out the right y value easier too. Everything under 10 needs to go in row 1, and so have a y value of 1; everything between 10 and 20, a value of 2, and so on. We can again use the ROW() function, this time dividing the number by 10 and rounding up to the nearest whole number, so everything between 1 and 10 will give us a value of 1, between 10 and 20, a value 2 and so on.

All of this can go in an Excel IF() statement. The format of these is: IF(thing to test, what to do is that’s true, what to do if that’s false), so that would look something like this:

IF(

( ROW() - 1 ) <= TARGET VALUE 

<then> ROUNDUP( (ROW() - 1 )/100 ) 

<else> NA()

)

The ROUNDUP() function in Excel is a little more complex than that. Since you have to tell it how many decimal places to include in the result. The NA() function just gives us an cell without a number in it, meaning PowerPoint won’t try and add anything to the chart.

In the case of the example, the final formula is:

=IF(

(ROW()-1)<=$F$2,

ROUNDUP((ROW()-1)/10,0),

NA()

)

This should give us the right number of dots for our first value and then just empty space.

We can add a new data series by just adding a fresh column of y values - but the formula here gets a little harder.

We only want to add a dot in the next series if it’s below or equal to the target value AND above the previous value. This is why we created the running total earlier. Basically we want to add a nested IF() statement to our previous formula. If our initial test is true we then have to run another - is our ROW()-1 >= the previous value.

=IF(

(ROW()-1)<=$G$2,

IF((

ROW()-1)>$F$2,

ROUNDUP((ROW()-1)/10,0),

NA()),

NA()

)

But at least we can pretty much reuse this formula for all our other data series in new y columns

Once we’ve created a column for each of our data series we should have our complete grid of 100 dots, so we can now close that and concentrate on sorting our chart.

Layout the chart

We now have a chart of 100 dots, with our different data values in different data series but it’s all looking rather bare and inelegant at the moment.

The first thing we’re going to do is sort the axes.

Open the Format Pane for the Axis options. There are many ways to do this, but the easiest is to right click and select the ‘Format Axis…’ option.

Our values only run from 1 to 10, so we can set the Minimum and Maximum values of the axis to match - in this case we’ll set them to 0 and 11, giving us a little margin at each end.

We’re going to do this for both axes and then we can delete them - and everything else apart from our dots - the rest of the chart: gridlines, tick marks, etc, doesn’t tell us anything useful about the data and is just distracting.

You can do this by just selecting the elements and hitting delete or by unticking them in the Add Chart Element menu.

This will leave us with just our grid of 100 dots.

We can then easily resize this to be a square, which is the shape we want our final waffle to be.

Now we just have to add our icons.

Add the icons

For the purposes of this walkthrough, we’re going to use icons from the built-in PowerPoint icon library, but you could use any image (SVGs - vector graphics - are best).

Once we’ve found all the icons we need we can just add them to the slide (make sure you don’t have the chart selected when you do this, otherwise PowerPoint will add them inside the chart object).

Because these are Microsoft icons we can easily style and recolour them.

Then we select the icon we want to use for the first data series and copy it to the clipboard.

We select that first data series in the chart by click on one of the dots and open the Format Pane (we can do this by right-clicking and selecting ‘Format Data Series’).

We need the marker options for the data series - these are under the tab with the little Paint Pot - you may have to click the ‘Marker’ button to open them.

Under the ‘Fill’ options, choose ‘Picture or texture fill’ and then select ‘Clipboard’ as a Picture source. This will fill each of the dots with a copy of our icon.

But it’s tiny. We can fix that by adjusting the marker size. Under the Marker Options at the top, select a ‘Built-in’ marker to match your icon - we’re going to use a circle in this case - and then tweak the size up until you’re happy with how the grid looks.

We should probably also set the Border to ‘No line’ while we’re at it, so we just have our icon.

Now we just have to repeat that process with our other data series and their icons.

Until we have our full grid of icons.

However, now we have to label it.

Add the labels

Because of our mucking about with the data, it can be tricky to add chart labels that make sense - it’s possible, but tricky. We’re going to take the easy way out and just add our labels manually.

We’re going to use a table to help us with the layout, so let’s start by adding one: one column with three rows.

Then we want to make the table itself invisible, under the Table Design tab, set the Shading to ‘No Fill’ and select no Borders.

Now we’re just going to enter the data points in, one per row, matching the order of the data series in the chart.

Then it’s just a matter of arranging the table so that the labels line up with the data series - you can do this by just dragging the row dividers until they’re the right height. We’ve also coloured the labels to match the data.

So that’s how we make that in PowerPoint

Now, to be honest, this walkthrough is really here to demonstrate how to make multi-series waffle chart using a scatter plot. Icon charts can be incredibly effective, but they are also incredibly hard to pull off, and we tend to use them very sparingly indeed.

You can, though, use this technique to make nice, simple waffle charts with just plain shapes and one data point - which are much more dependable.

Waffle charts (multiple series)

Waffle charts are a useful alternative to pie charts for composition data. We ten to use them for single values - one percentage out of 100% - but, like, pie charts, they can also be used to multiple series (although, again like pie charts, not too many)

There are multiple ways to make waffle charts in PowerPoint by using columns, stacked columns or scatter plots. In this case we’re going to make a multiple series waffle using a clustered bar chart - although you could make one using any of the other methods (with a little jiggery pokery)

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here:

https://docs.google.com/presentation/d/1HQ2oFKbMJAiKdeTY8v6mxFOL5OHGMYyt?rtpof=true&usp=drive_fs

What you’ll need

• Clustered column chart

• Data manipulation

• Excel formulas

• Design tweaks

• Custom labelling

How you do it

We’re going to use the Stack and scale options for custom fills to make ten columns of ten squares: a waffle of 100 blocks. We’ll also use a quirk of the clustered column where, if you set the Series Overlap to a positive number, PowerPoint will stack columns up in front of each other, obscuring the columns behind. This means we can make columns for each of our data series and then arrange them one in front of another to make a grid.

Details

Insert the chart

Add a standard Clustered bar to the slide. This is the default chart in PowerPoint, the first in the menu when you click on Chart in the Insert menu.

PowerPoint will create a dummy chart and open Excel to show the data behind that chart.

This helps us understand the structure of the data that the chart expects. Each Row is a cluster of columns, each Column, a data series. To create our grid, we’re going to need 10 clusters of columns, and we’re going to have 3 data series.

Let’s delete the dummy data and lay out our Rows and Columns.

However, the data we’re going to be putting in those cells is going to be generated by formulas based on the actual percentages, so let’s shift our cells out of the way to make room for our values and our calculations.

PowerPoint only visualises the data within the blue frame and will ignore everything else, but the rest of the sheet still behaves like a conventional Excel document, so we can add in numbers, formulas, anything we might ordinarily have in a spreadsheet.

Prepare the data

To start with, we’re going to drop in our dataset: three percentages that add up to 100%.

But we’re going to have to perform various calculations on these numbers to use them in our chart.

The first thing is, in fact, to turn them into numbers rather than percentages. When Excel formats a number as a percentage it treats it as a number between 0 and 1, with 1 equalling 100%. So, as far as most calculations we might do in Excel are concerned, that 43% is, in fact, 0.43. To make everything a little more comprehensible, we’re going to turn it into a number by multiplying it by 100. This means reformatting it from being a percentage first, which we can do using the NUMBERVALUE() formula. So our formula is going to look something like this:

=NUMBERVALUE(B2)*100

However, for our other values, we want to do something slightly different. To make our grid, we’re going to make three series of columns overlaid on top of each other. This means our 25% column is going to be behind our 43% column. We actually want it to be 43 + 25 = 68% high, because the bottom 45% will be covered by the first column, leaving just 25% visible.

So our formula for the next value will be something more like:

=(NUMBERVALUE(C2)*100)+F2

And then we’ll do the same for our final value, giving us values of 43, 68 and, of course, 100.

However, we’re not doing these values as single columns, we’re splitting them up over then columns. We can’t just divide the values by 10, though. We want to create a grid of 100 blocks. We don’t want to show 0.3 of a block, we want to show 43 by showing 43 individual, whole blocks. Split across ten columns that would mean ten columns of 4 blocks, giving us 40, and then an extra block on three of those columns to give us 43. In other words, because we start from the bottom left and go left to right, three columns of 5 blocks and seven more of four.

So we need to calculate how many ‘10s’ we have in our value, and then how many extra blocks we have to add on to that.

This, fortunately, is easy. First of all, we’re going to divide the value by 10 and round down to the nearest whole number, using the ROUNDDOWN() Excel function:

=ROUNDDOWN(F2/10,0)

That trailing zero just tells the formula to return whole numbers. Then we’re going to repeat that for all our values

The next part is to figure out how many extra blocks we’re going to have to add, basically the remainder of the previous calculation, which we can find using the MOD() function:

=MOD(F2,10)

And again repeat that for each of the remaining values

Finally, having done these calculations, we can use the values we’re derived to plot our chart.

Calculate the values

What we’re doing is calculating the individual values for ten columns for each data series. When rendered, each column will be filled with a number of blocks, a number in proportion to the underlying value. A column of value 4 will be filled with 4 blocks, for instance.

For our value of 43, we have calculated a minimum value of 4. Ten columns of value 4 will give us, in the end, 40 blocks. We need to add another 3 blocks to that total to give us our 43 blocks. That means we need 3 columns of value 5, which will give us 3 extra blocks in a row.

Each row in the dataset is a column in our chart, so what we can do is to test whether the current row we’re looking at is below or equals the remainder we’ve calculated. If it does, then we can add 1 to its value, to get our missing 3.

To express that as a formula, in this case: IF(This row <= 3), THEN (Value=4+1), ELSE (Value=4)

Because we’ve numbered our rows in the Columns column in the dataset, we can use that number to test - alternatively we could use the Excel ROW() function to find the row number (although we’d have to use ROW()-1, because our first row is our headers).

This gives us an Excel function something like this:

=IF(J1<=F4,F3+1,F3)

However, because of how PowerPoint renders column charts, we have to start with the last data series, which would give us something like:

=IF(J1<=H4,H3+1,H4)

Because that final value is 100, that just gives us 10 columns of value 10 - which is what we need.

The next data series shows us a little better how the calculations work:

And we repeat that formula for all our data series:

And we’re finally done with the data and can close the spreadsheet (for the moment)

Layout the chart

We now have a clustered column with three data series across ten columns

We want to turn this into a ten by ten grid of blocks, stacked up to show our different data values.

Let’s start by formatting our axes. Open the Format Pane - you can do this several ways, although the easiest is probably to right-click on the y-axis and select ‘Format Axis’

By default, PowerPoint adds a little give to its axes, so that y-axis extends up to 12. We don’t need that, so under the Axis Options, we can set the Upper Bound to 10.

Then we can actually remove the axes, since we’ve manipulated the data to make our chart and they’re not telling us anything useful anymore. We can do this by unselecting them in the Add Chart Element menu.

I’m going to leave in the Title and Legend, though, as they can be useful.

Now we’re ready to start making our waffle.

Make the waffle grid

First we need to make the blocks out of which our grid will be composed. Add a square to the slide - you can do this from the Shape menu under the Insert tab on the ribbon:

Set the size of the square - I usually set them to 6cm x 6cm, just out of habit (yes PowerPoint still uses centimetres for measurements). Note that I’ve put the square off the edge of the slide. PowerPoint will save anything in that space, but not show it in Slideshow mode, so it’s a useful place to store elements you’re not using (like off of an Artboard in Illustrator).

Set the fill colour for the square to ‘No Fill’, but keep the outline

This gives us a transparent square. Duplicate that square.

Give the new square a fill colour.

And then set it to be slightly smaller than the original - I’ve gone for 5cm x 5cm.

Then select both squares (by holding shift and clicking on them both) and make sure they’re vertically and horizontally centred.

With them both still selected, turn off their outlines by selecting No Outline

Keep them both selected and copy the pair of them.

Now select the first data series in the chart by clicking on a column and open the Format Pane (if it's not already open)

Under the Fill options for the data series, select ‘Picture or Texture Fill’. By default PowerPoint will fill the column with a canvas texture.

Below, under ‘Picture Source’ select ‘Clipboard’. This will fill the column with the squares we’ve just copied.

At the moment, the squares are all stretched to fill the column, and we want them repeated and tiled. Fortunately PowerPoint has a built-in way of doing this. Under the Picture options select ‘Stack and scale with’.

This stacks the image up within the column. You’ll notice that underneath there is a text box that allows you to set how the image stacks. By default PowerPoint sets this to Units/Picture: 1 - one picture represents one unit in our data - which is precisely what we want. One block represents one unit in the total for each column.

Then we want to do the same for our other columns. Set a different colour for the filled square.

Then select both squares and copy them, select the second column and fill that with the Clipboard, setting the Fill to Stack and Scale with 1 Units/Picture:

And then do the same thing for the last data series with yet another colour.

With that final column still selected, switch the Format Pane to Series Options - the little column chart icon.

Set the Series Overlap to 100%.

This pushes each cluster of columns together, laying each over the top of the previous. You can hopefully see how this is now going to make a grid of squares.

Now select the background of the chart by clicking between the columns. This will select the Plot Area, giving you handles to resize the plot within the chart object.

Resize the plot to make it square - it should give you tooltips showing you the height and width which will help in this.

Once you’ve got it reasonably square, go back to the Series Options in the Format Pane

And set the Gap Width to 0%

This pushes our columns together, giving us a square plot of 100 squares: our waffle grid.

Add labels

However, the data making that chart isn’t the data the chart is showing: the chart shows 43 little blocks all stacked up, the chart is ten columns, three of 5 blocks and seven of 4. We’re going to need custom labels to show our actual data.

Select the first series and add data labels using the Add Chart Elements menu. We’re adding them Inside End, so they match the top of the data series.

At the moment these labels are showing the actual values of those columns, which we don’t want, but we can change that. Open the Format Pane by right-clicking on the labels and selecting ‘Format Data Labels’

Under Label Options, click ‘Value from cells’. This will open up the Excel sheet behind the chart and ask you to select a range of cells to use as a label. We’re going to want to choose the source data for this data series.

Make sure you select ten rows to match the ten rows of data in the chart, even though nine of those rows are empty.

Because only one cell has any content, only the first label will have the 43% added to it, the rest will just show the values. We can remove those values by unticking them in the Label Options.

This leaves us with just that 43% label - the only one we need. Now we just need to style it.

The main thing we’re going to do is to push it outside of the grid by right-aligning the text and then giving it a huge right margin by switching to the text box attributes in the Format Pane (the tab with the little box on) and setting the right margin to 3cm. This pushes the label out to the left.

Using the margins like this means that the labels stay positioned ‘Inside End’ - if the value changes the label will move up and down to match it

Then we do the same to the other data series; adding labels using the source data

And then positioning them to the side using the margins

Then we can style up the labels using the usual text styling tools

So that’s how we make that in PowerPoint

Now, these waffles use squares with a transparent margin, which means they’re easy to adapt to different coloured backgrounds, but that can mean that the colours behind the blocks on top can bleed through (even though those blocks are all technically the same size). One way round that is to fill the larger square in the background the same colour as your slide background, but that does mean you’ll have to update the fill more often.

The key thing to remember with multiple series waffles is that, like pie charts, they can’t take too much information - they can easily get too busy and hard to read - so use with caution.

Waffle charts (single series)

Waffle charts are a delightful alternative to pie charts for composition data, especially for single values. They do a really nice job of emphasising the ‘part’ bit of ‘part to whole’, showing us the individual elements that go to make up the total.

There are multiple ways to make waffle charts in PowerPoint by using columns, stacked columns or scatter plots. In this case we’re going to make a waffle showing a single percentage using a simple stacked column chart

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here:

https://docs.google.com/presentation/d/1HP5pVDtvim9PJf32RPaAsA7ofELctNWv/edit?usp=drive_link&ouid=112240584160970239353&rtpof=true&sd=true

What you’ll need

• Stacked column chart

• Data manipulation

• Excel formulas

• Design tweaks

• Custom labelling

How you do it

We’re going to use a stacked bar chart to show a percentage and then its remainder out of 100%. We’re going to make a chart of ten columns, each of a maximum value of ten, giving us that 100%. The value is then split across those ten columns, allowing us to show the percentage. We can use the custom fill options on the columns and the ‘stack and scale with’ feature to fill each column with ten shapes, which, with ten columns, gives us a grid of 100 shapes: our waffle.

Details

Insert the chart

Insert a standard clustered bar - you could use a 100% stacked bar, but we’re going to be creating that 100% fill with formulas anyway, and a standard clustered bar is a little easier to work with.

As usual, PowerPoint will create a dummy chart and then open Excel to show you the data structures behind that chart

By default, PowerPoint creates a stacked column with three data series - we’re only going to need two of those, so we can grab hold of that blue rectangle that frames the data that PowerPoint is visualising and drag it in to exclude that third column of data.

Structure the data

First of all, let’s get the data into the right structure for our waffle.

We’re going to need ten columns to make our 10 x 10 grid, so we will need ten rows of data. We can also delete the dummy data as we’re going to be filling those cells using formulas.

I’ve numbered the rows, because that will make the formulas I write easier, and I’ve named the columns just to make everything clearer.

We can actually fill out that ‘Remainder’ column already, because it’s just going to be the remainder of our percentage subtracted from the total 100%. However, remember that we’re going to have ten columns, totalling ten each, so the value of that Remainder is going to be 10 - (whatever is in the first column). In an Excel formula that will look like:

=SUM(10-[@Data])

At the moment there’s nothing in the Data column, so we get 10 columns all of a value of 10:

Next we’re going to change that and start calculating the percentage we want to show, and in order to make room for that, we’re going to move the data we’re visualising over - we can just grab the cells in the blue frame and pull them across. PowerPoint will just carry on chart whatever is in the blue frame and ignore everything else.

The chart remains entirely unchanged.

Calculate the data

First we need a value to calculate, so let’s add that in with a little table:

The point of laying it out like this is to make the chart reusable - having clear labelling, even just for a single value like this - just 43% - is that if we come back to this chart in the future and want to update it, or copy and paste and make a duplicate, it’s really easy to open the data and understand what needs to be updated.

Our first problem is that for the sake of clarity (and also to make our labelling of our chart easier, as you’ll see), we’ve used a percentage for our value. The problem is that in Excel, from the point of view of formulas, those percentages are actually decimals. Any calculations performed on that percentage will be treating it as if it’s actually 0.43, not 43%. So first we’re going to convert it to a whole number. This means changing it from a percentage using the Excel NUMBERVALUE() function and then multiplying it by 100:

=SUM(NUMBERVALUE(B2)*100)

We can then use this number to calculate the other values we need for our chart.

The end result we want for this waffle chart is for 100 squares, 43 of which are filled in, to represent the 43%. We colour in the squares from the bottom left, moving along the rows left to right until we reach 43, so we end up with four rows of ten coloured squares and one final row of three:

But we’re making this out of columns, so what we actually have is three columns of 5 squares and then seven columns of 4 squares.

So these are the values we want to plug into that first column in the charted data in the spreadsheet.

However, we want this to be an easily updatable chart, so we’re going to generate those values with formulas. We just have to work out what those formulas should be.

To begin with we’re thinking in terms of rows of ten squares. If our percentage was 40% we’d have four rows of ten squares, so each of our ten columns would have a value of 4, or 4 divided by 10.

In fact we can think about our 43 squares as four rows of ten squares plus three: all of our columns are going to be at least four high. So we can begin by calculating that minimum height for our columns by using the ROUNDDOWN() function in Excel, dividing our percentage by 10 and then rounding down with no decimal points:

=ROUNDDOWN(D2/10,0)

This will give us our minimum value of 4. Now we just have to add on our extra 3 squares to take it to 43. To do this, we’re essentially going to add a square on top of each column, starting with the first, until we reach the correct total. So what we need to know is how many columns we need to add a square to. Our ten columns of 4 are going to give us the 40, we need to find the remainder, the 3. Fortunately there’s an easy mathematical way of discovering this: modulo, which gives us the remainder of any division, in Excel MOD().

=MOD(D2,10)

This will give us the remainder when 43 is divided by 10: 3.

We can use these two numbers to calculate the heights of our ten columns using a simple IF statement.

IF the column we’re calculating is lower in number than the remainder, THEN give it the height of the minimum value plus 1, ELSE just give it the minimum value.

In an Excel formula - IF(TEST STATEMENT, IF TRUE, IF FALSE) - that looks like this:

=IF([@Columns]<=F2,E2+1,E2)

Only we’re going to put $ signs in F2 and E2 to fix the references to those cells, so we can copy the formula down and it will work cleanly.

There - we have our three columns of 5 and our seven columns of 4, totalling to our 43, and our Remainder column has updated to fill in the total to 10 x 10.

Now we can close the data for a moment and concentrate on the chart

Lay out the chart

The first thing we need to do is to set our axes. PowerPoint automatically gives a little leeway when it generates a value axis - for instance, our y-axis currently goes to 12:

But the whole point of this chart is that we’re only ever going to have a total of ten, so we want to set that. Open up the Format pane for that axis - one way to do this is to right-click on the axis and select ‘Format Axis…’

We’re going to set the Maximum Bound of the y-axis to 10, to let us use as much of the chart space as possible.

Having set that, we no longer need the axes - they’re not telling us anything useful about the data - we’re mostly just using those values for layout purposes, so we can delete them. You can do this by simply selecting them and hitting Delete or by unticking them in the ‘Add Chart Element’ menu.

In fact we’re going to get rid of everything apart from the title (we’re not actually going to use it in this example, but it would be useful in practice).

Now we want to adjust the appearance of the chart itself. A 10 x 10 waffle ought to be square in shape, so we’re going to select the plot area of the chart and resize it by dragging to make it square.

Now we want to make the actual visualisation square. Open the Format Pane for the chart - you can do this by right clicking and selecting ‘Format Data Series’.

Open the ‘Series Options’ pane (the little chart icon) if it’s not already open and set the Gap Width to 0%. This will remove the gaps between the columns, turning them into a solid square, 43% of which is coloured differently to the rest (because it's a different data series).

Now we need to turn those blocks into waffles.

Style the chart

Deselect the chart (you can do this by just clicking outside it, on the slide background) - this helps because we’re going to be adding a bunch of elements to the slide and PowerPoint will add them inside the chart by default if we still have selected.

Insert a square shape - you can do this under the Insert tab in the ribbon.

It helps to set a size for it. We’ve set this to 6 cm x 6 cm (PowerPoint still, inexplicably, uses centimetres for its measurements instead of pixels).

Then set the fill options to ‘No Fill’ (you can do this under the Shape Format tab in the ribbon). This should give you a square with an outline but transparent in the middle.

Copy and paste that square and make the duplicate slightly smaller. We’ve made it 5 cm x 5 cm.

You want to make this square coloured, so pick a Fill colour.

Select both squares and make sure they are neatly centred, one inside the other. You can do this by using the Align options on the Shape Format tab.

Then, with them both still selected, set their outline to ‘No Outline’. This will make the bigger square entirely transparent, but you’ll see it’s still selected. PowerPoint still knows it’s there, it’s just now entirely invisible.

Keeping them both still selected, copy them both:

Now select the value columns in the chart - the first data series - and open the Format Pane.

Under the Fill options (click on the little paint pot icon), set the Fill to ‘Picture or texture fill’

By default, PowerPoint will fill the columns with a canvas texture. But we’re going to fix that. Under ‘Picture Source’ select ‘Clipboard’. This will fill the columns with the squares we just copied.

This looks weird, but that’s because ‘Stretch’ is selected by default for the fill options. Instead set it to ‘Stack and Scale with’.

By default the ‘Stack and Scale with’ option is set to 1 Unit/Picture - which means PowerPoint will fill the chart by repeating the fill in a 1:1 relationship to the data - one picture for every unit in the data.

Which is perfect for our purposes - it gives us our 43 blocks to show our percentage.

Now we just need to do the same for the Remainder.

Go back to the coloured square and choose a different colour to distinguish from our key value. We’ve used a paler colour here to make it more of a background visual.

Once that’s done, select both that and the invisible square behind it and copy them both.

Then do the same thing with the other data series. Set the Fill to ‘Picture or texture’, select the image on the Clipboard and set to ‘Stack and scale’ with 1 Units/Picture.

…and we have our waffle, showing 43%. But we really ought to say that explicitly with a label.

Add data label

The problem we’re now going to have with the built in data labels is that the data in the chart isn’t the data we want to label with - we had to manipulate the data to make the chart - but, fortunately, there are ways round that.

Although we have ten columns, we only need one label, so we’ll add one to just the first column.

Select the first column and then, under the Add Chart Element dropdown (under the Chart Design tab) add a data label inside the end of the column.

By default PowerPoint will label the column by it’s value, but we want to change that. Open the Format Pane (by right-clicking on the label and selecting ‘Format Data Label’ and in the Label options, un ‘Label Contains’ select ‘Value from cells’. This will re-open the underlying Excel spreadsheet and ask you to pick a column of cells to draw the label from. We’re going to pick that second column where we put our original data point.

This will add that ‘43%’ to our label. We can then turn off the ‘Value’ option to leave just that ‘43%’. Note: you will need to select that particular label directly to do this. Clicking on once will select all the labels for that data series (even if there aren’t any others, as in this case), clicking twice will select that specific label.

We can then style up that label with the usual tools.

We can also adjust its positioning, by going into the Text box options in the Format Pane and giving the label a wide Right Margin. This will push the label left, outside of the waffle, but still adjacent to the column.

Creating the label this way means that if we update the original value in the spreadsheet, not only will all the formulas update the chart, but the label will update correctly too.

So that’s how we make that in PowerPoint

Phew. Those formulas were a bit mad, but there is a point to it all. But setting all of that up to generate the chart, we make a thoroughly reusable visualisation.

Visualising a single, important percentage is a common ask, and hard to do in a properly arresting and visually striking way: waffles give us one, simple but striking way to do this.

Split circles

Split circles are a nicely attention grabbing way to visualise comparisons, especially a before and after, or two regions/demographics/departments.

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: https://docs.google.com/presentation/d/1mFdfAGiiy4-ccbUS5lhLD2MRIEA6k0gB/edit?usp=drive_link

What you’ll need

• X Y (Scatter) - Bubble

• Data manipulation

• Custom shape fills

How you do it

Short version: we’re going to use a standard Excel bubble scatter but use the x & y coördinates to arrange the bubbles on top of each other. Then we’re going to use a custom fill to make one half of each bubble transparent, giving us two separate halves to compare.

The details

Insert the chart

Open a blank PowerPoint slide. Under Insert/Chart select 'X Y (Scatter)' and then 'Bubble' (no, NOT '3D Bubble'. What is *wrong* with you?)

PowerPoint will drop in a Bubble Chart and open an Excel spreadsheet with sample data in it

Edit the data

This usually has a row of headers and then three rows and three columns of data

These rows and columns are then outlined with a blue frame. This frame shows us the data that PowerPoint is visualising in the chart

Have a look at the structure of that sample data. Each row is a separate entry, with the columns giving the different dimensions. The first column in the position on the x-axis, the second the position on the y-axis and the last, the size of the bubble.

We're just going to be using the size to represent data

For this example we’re just going to make one pair of split circles, so we only need two rows of data. .Pull the blue frame up so it’s just highlighting the top two rows and delete the bottom row of data

• Note

  Due to some unguessed-at trauma, PowerPoint does not like Bubble charts and will do its best to forget how they work

  Especially if you copy and paste a Bubble chart from somewhere else and then try and add data

  Frequently PowerPoint will simple refuse to acknowledge new data or new cells

  If this happens to you, try to adjust the blue frame

  But frequently you will have to resort to more powerful tools

  Under the 'Chart Design' tab in the ribbon is a 'Select Data' button

  This will open up a dialogue box in Excel that will enable you to tell PowerPoint precisely which cells to look for data in

  It can be a beastly thing to deal with, but there are plenty of how-to guides online

Instead of using them to represent data, we’re going to use the X-Values and Y-Values to position the bubbles. Change the Y-Values for both rows to be ‘1’

If you look at the PowerPoint, you'll see that this lines all the bubbles up in a row

Make data series

At the moment PowerPoint is displaying everything in one colour. This is because everything is currently one data series. Often we are going to want to colour our two halves differently, to distinguish them. Of course, we could just do this by colouring the bubbles differently using PowerPoint's Format tools. But it's better to represent those series in the data structure

Go back to the Excel.

PowerPoint usually uses the Y-Values to represent a data series. By creating new columns of Y-Values we can delineate new series in the data set. With a bubble chart this means creating new columns of Y-Values and Sizes

Highlight the Y-Values and Size in the second row of data (so cells B3 & C3)

Pull that data across to the right, until its in two columns on its own (so into columns D & E, so it now occupies cells D3 and E3)

Notice that I’ve also changed the names of the Y-Values columns (cells B1 and D1)

This is because, by default, PowerPoint will draw the labels for a Legend from those column titles. By setting them in the Sheet, I make sure that if I add a Legend it will automatically have the right labels

Flick back to the PowerPoint and you'll see that the two bubbles are now different colours.

PowerPoint will automatically colour data series differently. As usual it will do this by just taking the next colour in the slide template colour palette. But now we’re going to get properly crazy and mess with those choices

Make the half fill

Essentially we want to only half fill those circles, so that one half of each circle is transparent. To do this, we need to make a fill that’s half transparent

We could do this using a gradient fill, making half of it transparent and half of it a solid fill, which does work, but PowerPoint has a habit of forgetting how that gradient is supposed to work, and we’ll come back in six months and discover everything looks like an eighties computer game title screen.

Instead, we’ll use a different technique that’s a little fiddlier but more reliable (so far)

Add a square to the slide

Make sure the square has a fill, but no border

You can find these tools in lots of different ways: in the Format Pane, by right-clicking and selecting ‘Format Shape’ or by selecting the shape and then going to the Shape Format tab in the ribbon

Make sure the shape is a square by setting the width and height to be equal (for example, making both 4 cm)

Now copy and paste that shape, so we now have two identical squares

Adjust the width of the new shape to be slightly under half the width of the first shape - but still the same height (for example, keeping the height at 4cm but making the width 1.9cm)

Now select the original square and remove the fill entirely, so you’re left with an completely transparent shape

Select both shapes and under the Arrange options, choose ‘Align’ and the ‘Align to Middle’ - this will line them up precisely

Keep them both selected and then align them both left

Keep them selected and copy the whole group

Now select the circle that’s going to be on the left of our split

Open the Fill properties for that shape - you can find those in the Format Pane, or by right-clicking and selecting ‘Format Data Series’ from the pop-up menu

Go to the ‘Fill and Line’ tab, with the paint bucket icon and under ‘Fill’ select ‘Picture or texture fill’

PowerPoint will fill the shape with a default texture and open a fresh set of options

Click ‘Clipboard’ under Picture Source

PowerPoint should now have filled the bubble with the half-transparent group.

We’re now going to reverse that group to do the other bubble

If you want to, change the colour of the filled rectangle.

Once you have the colour set, select both the rectangle and the invisible square behind it. Under the Arrange/Align options, select to ‘Align to Right’

Now copy both shapes

Select the right-hand bubble and again, edit the Fill, selecting ‘Picture or texture fill’ and the ‘Clipboard’ option

This will then will fill the bubble with the right-hand half transparent fill

Now we need to make these two bubbles into a split circle

Make the split circle

Go back to the Excel Sheet

We’re going to use the X-Values to lay one bubble over the top of the other

Change both X-Values to 1

If you look at the PowerPoint, you’ll see that this places both bubbles at the same point on the x-axis, placing them one on top of the other

But shoved all up in one corner there, so let’s tidy that up

Tidy up the chart

Select the y-axis and open up the Format Pane to the Axis Options tab - the one with the little chart icon on it

We want to centre the bubbles on the y-axis, with an equal space above and below it. We do this by adjusting the Bounds under the Axis Options - these set the lowest and highest values on the y-axis

We just need to set these to give an equal count above and below whatever we’ve set our Y-Value to in our data (in our case, since our Y-Value is 1, we can set the Minimum Bound to 0 and the Maximum to 2)

Now we need to do the same to the x-axis

Open up the Format Axis panel for the x-axis and edit the Bounds to place the bubbles in the centre of the table (since we only have one split circle at position 1 on the x-axis, we’ll make those Bounds 0 & 2, but if we had, say, 3, at positions 1, 2 & 3, we’d make the Bounds 0 & 4)

Now we’ve evened everything up we can get rid of the axes, as they’re not needed for reading the chart (in fact, they’re actively confusing)

You can select them and just hit delete, or use the ‘Add Chart Elements’ menu and untick them under the ‘Axes’ option

And let’s get rid of the gridlines too

While we’ve got the ‘Add Chart Elements’ menu open, we could also add some chart elements like titles or legends (if we need them)

The single split circle looks a little lonely in the middle of the screen there, but PowerPoint gives us a quick tool for scaling up bubbles without losing their relative size

Select a bubble and, in the Format Pane, find the Series Options tab (the one with the little chart icon)

There you will find an option to ‘Scale bubble to’ and a textbox that is set by default to 100

You can scale to a number between 0 and 300. I have no idea what those numbers mean and why they stop at 300. PowerPoint insists on preserving some of its mystique.

I usually just shove random numbers in there until something looks good (in this case, with just one split circle, we can probably safely go all the way up to 300 (300 what? Spartans? Degrees centigrade? Trombones? We may never know))

Add Labels

We should also add some labels to our bubbles

This is going to be tricky, because we have two bubbles overlaying each other, so their automatically generated labels will also overlay each other. We’d prefer labels inside the bubbles, but if we just centre them both, they’ll get in each other’s way.

Select the whole chart, rather than a single series and then, under the 'Add Chart Element' menu select Data Labels and select the ‘Left’ option

You will see that PowerPoint, by default, labels them with the Y-Value, not the size

This means we're going to have to change that, since the relevant data is in the Size columns

Unfortunately, if we have multiple series, we can only change one series at a time because.... <waves hand at Redmond>

Select one of the labels (start with the one on the right, as we’ve got everything aligned left, currently) and open the Format Pane

Under the 'Label Options' tab, there is a 'Label Options' pane

In the 'Label Contains' options there, deselect 'Y Value' (and 'Show Leader Lines', while you're at it)

Select 'Bubble Size' - the labels should update

While we’re here, we’re now going to get the position of this label where we want it

Under Label Position, select Centre

The label will shift over to sit over the gap between the bubbles - the centre of the bubble (if half of it wasn’t transparent)

So now we’re going to want to shift that over, into the right hand half of the circle

Select the next tab over - the ‘Size and Properties’ tab with the little measured square icon

Under Alignment we’re going to increase the left margin to push the label rightwards

PowerPoint measures this in cm because that’s a totally normal thing to do with digital elements <side-eye.gif>. Anyway, this means it’s a bit trial and error (1 cm seems about right here)

The good news is that if you have a whole load of circles, you only have to do this once per data series.

So let’s do it to the other data series

Click on the other label and open the Label Options in the format pane

Update it to ‘Bubble Size’ and centre align it

In this case, we’re also going to want to change the text colour for legibility, which we can do with the standard text tools in the Home tab on the ribbon, or by opening the Text Options in the Format Pane, or with the Text Fill option in the Format tab on the ribbon. And probably in other ways I’ve temporarily forgotten.

Now go to the Size and Properties tab for this label

This time we want to adjust the Right margin to shift the label leftwards (2cm looks ok)

And there we are, neatly aligned labels, one for each half, both pulling from the data, so if we update the numbers, the labels will automatically update

So that’s how we make that in PowerPoint

Obviously, we’ve only made one split circle here - which is not an uncommon need. But all we have to do if we need more is add more bubbles, changing the y-axis values to make columns, or the x-axis values to make rows.

(In general split circles should be used sparingly in PowerPoint - we tend to only make single rows rather than tables, for instance)

Have a look at our tutorial on Bubble Tables for a proper walkthrough of this technique for lining up bubbles.

Also, you can find an example PowerPoint here, which has the slide we just made, along with more complex editable examples like the one below, which can explore and update for yourself. Enjoy!

Slope charts

Slope charts are essentially line charts that emphasise the difference between just two points in time. This makes them particularly useful for stories about change, especially dramatic changes.

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: https://docs.google.com/presentation/d/1mFTrQFm8lQq3SAtXi-ODXIFGQ3L88usU/edit?usp=drive_link&ouid=112240584160970239353&rtpof=true&sd=true

What you’ll need

• Line chart

• Chart layout tweaks

How you do it

Really, a slope chart is just a line chart with just two points on the x-axis for two points in time, so most of the work lies in choosing those two points, rather than in the charting. We’ll just be using a standard line chart, although we often need to make some chart layout tweaks to make everything neat.

The details

Insert chart

We’ll insert a Line Chart. We could just use a standard line, but typically in slope charts you want to emphasise the ends of the lines, since we only have two data points on each line, so we’ll use a Line with Markers (you can find that under 2-D Lines in the PowerPoint chart tool).

As ever PowerPoint will create an example chart and open Excel to show us the example data behind it.

For a slope chart, we only want two points on the x-axis, a beginning and ending, so let’s delete the bottom two rows of data in the spreadsheet.

We don’t need to do anything else to the data, so we can close the spreadsheet now.

Tweak the chart layout

First of all, lets get rid of the elements we don’t need. We’re going to be labelling the markers directly, so we can delete the Legend (you can just select it and hit delete).

Now let’s format the x-axis. Open the Axis Options in the Format Pane (you can do this by double-clicking on the axis, or right clicking and selecting Format Axis).

Under Axis position, select ‘On tick marks’ to use the whole axis.

We’re also going to remove the baseline from the axis, so under Fill & Line (the paint pot icon) set the Line to ‘No line’.

Next we’ll tweak the Gridlines. Under Add Chart Element, select Primary Major Vertical to set the sides of the chart. But we don’t want the horizontal gridlines, so, under the same menu, untick those.

Finally lets tweak that vertical, y-axis labelling. Select the axis and, under the Format Axis options, set the Major Units to be the same as the maximum value of the axis. This will give us just minimum and maximum labels on that axis.

Now to set the real attribute of slope charts, their narrow width. Within the chart object, click behind the lines to select the plot background.

Drag in the sides of the plot till it becomes narrower, but still centred within the Chart Object.

Doing this also makes sure that we have space within the chart for our data labels.

Style the chart

Select one of the lines in the chart. Open the Format Pane and the Fill & Line tools (you can right-click on the line and select Format Data Series, then choose the paint pot icon).

Check you’re happy with the line colour and width.

To style the markers, click on the Marker button and open the Marker Options. Set the Marker to ‘Built-In’ and a circle. Make it big enough to be clearly visible.

Check the fill colour matches the line and remove any border by selecting ‘No line’.

Now add data labels. Under the Add Chart Element options select Data labels and set them to Left.

Select the labels and open the Label Options in the Format Pane (you can right-click and select ‘Format Data Labels’).

Select the Series Name as well as the Value, so that each point is clearly labelled.

Now select the right hand label (you can just click on it) and set the Label Position to ‘Right’.

Because each line is a separate data series, we’re going to have to repeat that process for all of them.

So that’s how we make that in PowerPoint

A slope chart is a really effective way of showing change, but they only really work if we lean into the simplicity and make them as clear and direct as possible.

But then how else would we chart anything, eh? EH?

Quadrants

Quadrants allow us to compare four different categories against each other. This is a more common use case than you’d expect, particularly when comparing business quarters.

They also allow us to replace Radar Charts when comparing patterns across different data series - giving us an oversight, at a glance, of where the series differ in trends and shape.

This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: https://docs.google.com/presentation/d/1mDQIUBhK-_wJCpJpvrSkMOEA_3i1cfvy/edit?usp=drive_link&ouid=112240584160970239353&rtpof=true&sd=true

What you’ll need

• A Filled Radar chart

• Data manipulation

• Design tweaks

• Custom labelling

How you do it

To make the four squares of a Quadrant Chart, we’re going to use a Radar Chart as a means of drawing with numbers (the only thing it’s good for, frankly, it certainly sucks as a chart). We can manipulate the underlying data (warning, this requires trigonometry) to make squares of an area equal to the values in our data.

The details

Insert the chart

We’re going to start by inserting a Filled Radar chart. They’re buried away under the ‘Waterfall’ group of charts in the PowerPoint insert chart menu, right at the bottom, hopefully where no one will find them.

They’re probably the least useful chart PowerPoint has to offer. However, they do give us a tool with which to draw shapes using data, which is going to turn out to be useful.

Arrange the data

We’re going to use the radar chart to make four segments, so we’re going to have to arrange our data to make those segments.

Radar charts draw their shapes by plotting down radial axes from the centre. This means that to make squares, each segment will need three data points, one for each corner of the square, with the fourth corner being the centre of the plot.

The first thing to do is to delete the second column of data - we’re not going to need that right now.

Then reformat the first column, which PowerPoint makes dates as default, to be just a General format (you can do this by selecting the column, right-clicking and selecting ‘Format Cells’). We start with five rows of data by default - add three more and make the contents of this first column the numbers 2-9 to match the spreadsheet row numbers (this column could be anything - but numbering them helps us keep track).

Hopefully you should now see that the chart has 8 axes coming out from the centre.

How we’re going to make the segments. Repeat the first value three times in the first column, in rows 2,3 and 4.

Then repeat the second value three times in a new column. We want this segment to overlap with the first, so we start it on row 4 and then repeat the value in rows 5 and 6.

Now do the same for the third value, adding it in a new column at row six and repeating it rows 7 and 8.

With the fourth value we’re going to add it in a new column in row 8 and then repeat it in row 9. Then, to make sure it overlaps with the first segment, we’re going to repeat it in row 2.

Now we have our data arranged into four segments. However, they’re not yet squares and they’re all measured by the raw values instead of the area of the shape. So, here comes the maths.

Manipulate the data

What we actually want to draw is a square with the area equal to our data point. Of our three values in each column in our spreadsheet, the first and third values are just going to be equal to the lengths of the sides of that square.

We know the area of that square, so we know that that area is the length of the sides squared, so to find the length of the sides we can just square root our area - our original data point.

In Excel we can use a formula for this: “=SQRT(#value)”.

However, the middle value is a little harder - that’ll be the top right corner of our square, so to find its position, we’ll have to calculate the length of the diagonal across the square.

Fortunately really all we’re doing there is calculating the length of the hypotenuse of a right-angled triangle, where the length of the other two sides is what we just calculated: the square root of our data point. And the length of that hypotenuse is, of course, the square root of the sum of the squares on the other two sides.

We can simplify this, then, down to: “=SQRT(#value*2)”

Hopefully this will give you a little square.

It’s going to look a little too little right now, but that’s because we need to repeat these calculation for the other segments.

Do that and we should then have four squares (PowerPoint should automatically adjust the bounds of the axes to make sure the segments are as big as possible).

We can close the spreadsheet now.

Customise the chart

Because we’re just using the chart to draw with numbers, most of the other chart elements like axes labels and gridlines aren’t going to be terribly helpful when trying to read the chart. We’re going to have to label by hand.

One thing we should do is make sure our axis bounds make sense, especially if we’re going to be comparing two charts, where we’re absolutely going to need matching axes.

Open the Format Pane to edit the Axis bounds (you can do this by right-clicking on the vertical axis and selecting ‘Format Axis’). Under the Axis Options tab (the little chart icon) set the Minimum and Maximum Bounds. Your minimum is probably 0, the maximum should be whatever maximises the size of the squares - ie: just larger than the highest value in the spreadsheet.

Both the chart title and the legend can still be useful, but delete the axes and gridlines.

You can set the colour of the segments by selecting the segment and then selecting a Shape Fill colour in the Format tab or in the Marker settings in the Data Series Fill & Line options in the Format Pane.

Add labels

There are ways round this but they’re complex and for the purposes of this walkthrough, the easiest approach is to label the squares by hand.

Add a 2x2 table to the slide (go to Insert and Table and select one with two columns and two rows). A table helps us keep everything neatly aligned and spaced.

Make the table square (I’ve made mine 6cm x 6cm out of force of habit). PowerPoint will, by default, give it a header row and banding - turn these off in the Table Design tab. You’re also going to want to set the Shading to ‘No Fill’ and turn off any table borders. We’re just using the table to lay things out, we don’t want it to be visible.

Centre the table over the chart, then add the labels into the cells.

Style up the text, sizing it and making sure the text colour is appropriately contrasting against the chart fill.

Select the first row and Bottom Align the text (you can do this in a number of ways, but the easiest is probably in the Layout tab of the Table tools).

Select the first column and Right Align the text.

This should give us a table with all the labels neatly arranged around the centre of the chart.

So that’s how we make that in PowerPoint

Quadrants probably seem a little niche, but we seem to end up using them more often than you would expect. Financial quarters, seasons, demographic quadrants: we’re often comparing patterns across different data series.

They’re also a really neat example of using the Radar chart to draw shapes, which is a useful skill to master, I promise.

Pyramid chart

Pyramids are most usually used for populations, comparing patterns of sex splits by age, but they are also a useful alternative to clustered bars when comparing any two data series. They allow us to see each series as a coherent bar chart, while still comparing the two series for patterns of difference.

[his tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: https://docs.google.com/presentation/d/1m9GSa-F-ZC64uhP266Ftb0vmRDF62BYI/edit?usp=drive_link&ouid=112240584160970239353&rtpof=true&sd=true

What you’ll need

• Clustered bar chart

• Amended chart layout

• Custom labelling

How you do it

We can make a pyramid using a standard clustered bar, but changing one set of values to be negative, so that they run right to left from the axis. We can then set the series to overlap, so that the two series are symmetrical across the vertical axis. Finally, using custom number formatting, we can display the data in the ‘negative’ bar as positive.

The details

Insert chart

Insert a bar chart. PowerPoint inserts a clustered bar chart by default.

By default PowerPoint will generate an example chart and open Excel to show us the example data behind that chart.

We only want two data series for the pyramid, so we can delete the third column of data.

We’re then going to amend the second column to be negative.

That’s all we need to do the data, everything else is going to be altering the chart layout, so we can close the spreadsheet for now.

Amend the chart

First let's make the bars run opposite each other. Open the Format Data Series options (you can do this by right-clicking on the bars and selecting ‘Format Data Series’).

Set the Series Overlap to 100%. This will set the bars to be opposing across the central axis.

While; we’re at it, let’s set the Gap Width to be something slightly less, so the bars are closer together.

Now let’s tidy up the rest of the chart.

Let’s set the Legend to be at the top (you can do this using the options under the Add Chart Element menu). Incidentally, this is the reason we set the second column of data to be negative: because the PowerPoint chart legend shows the second data series first, so this way the colours on the chart line up with the colours in the legend.

Now let’s arrange the axes labels somewhere more legible. Open the Axis Format options (you can right-click the vertical axis and select ‘Format Axis Options’).

Under the Label options set the Label Position to ‘Low’. This will push the axis labels out to the left of the chart.

We also need to make sure the horizontal axis is evenly distributed. Open the axis formatting options (right-click and select ‘Format Axis’).

Make sure the Minimum and Maximum bounds are even - the same negative Minimum as positive Maximum (-5 and 5 in our example).

While we’re at it, we can reduce the number of gridlines. We can set the Major Units to half the Maximum Bound, giving us just a single mid-point gridline.

Custom labelling

Now let’s add the data labels.

Select the right-hand data series and add data labels (you can do this by selecting Data Labels in the Add Chart Element menu - set the position to Inside End).

Make sure the text colour is appropriately contrasting against the bar fill.

Now do the same to the left hand data series.

These labels, however, show the data (correctly) as negative. But we only made the data negative so it was ranged left to right. We still want the values to be listed as positive. We can adjust this using a custom number format.

Open the Data Label formatting options (you can right-click on the data labels and open Format Data Labels…).

Open the Number formatting options and select Custom as the Category.

Set the Format Code to: #,##0.0;#,##0.0

Everything before the semicolon is the format for positive numbers, everything after for negative numbers. Hashes represent numbers, the 0s tell PowerPoint to include series and also, in this case a single decimal place. By not including a minus sign in the format for negative numbers we force PowerPoint to display the negative values as positive.

We can use the same Number Format in the horizontal axis (right-click on the axis and select Format Axis, then set the same Custom Format under the Number options).

So that’s how we make that in PowerPoint

It goes without saying, I hope, that the Pyramid is only really of use when comparing two positive data series, but it is really when comparing two demographics responding to survey questions, for instance, or distributions of demographics across different categories.

There is a slightly more complex way of making these using a stacked bar chart, if you want to be able the position the axis labels between the two sides of the chart, but that’s fiddlier to make and update. Let’s be thankful for what we have, eh?

Proportional pyramids

Proportional pyramids are highly specialised charts. They are used to break down populations where each subsequent element is a part of the previous one. Hm. That didn’t make much sense, did it? Basically they’re used a lot by market research companies when you want to break down brand preference from people who have heard of a product down to people who choose it every time. The latter are part of the former, but it’s also useful to see them as a separate measurement.

Basically, if you don’t know what I’m talking about, I don’t believe you’ll need this chart, but if you do, you do, so here we go.

[This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine]

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, you can find the dataset we used to make the example at the top, and a PowerPoint deck with that slide in, and other examples, here: https://docs.google.com/presentation/d/1m7ZojiwcFllKBb3UgLKi73KXk12qfaEh?rtpof=true&usp=drive_fs

What you’ll need

• A stacked bar chart

• Data tweaking

• Custom labels

How you do it

Essentially what we’re doing is making a series of stacked bars that break down each of the different levels of our data. We then use shading to suggest that each bar is extracted from the bar before it.

The details

Insert the chart

Add a stacked bar to the slide (you can find them under column charts in the chart library).

PowerPoint will add in an example chart and open Excel to show you the data behind it in a spreadsheet.

We’re going to need as many rows of data as we have columns, so we can delete that last row in the example data.

What we then want is diminishing stacks, but we want the data series to match up by level, rather than value.

In the second row we’re going to repeat Series 1 & 2 in the columns for Series 2 & 3.

Then in Row 3, we’ll put that data point from Series 2 into Series 3.

That’s all the data for the chart, however we also need to generate data for the labels. This is going to require some Excel formulas.

In a column outside of the blue frame that delineates which data PowerPoint is visualising, add a cell that adds up all the values in the first row of data (=SUM(B2:D2) in our case).

We then want to repeat that addition for all the rows.

Now we can close the spreadsheet and concentrate on the charting.

Layout the chart

First, let’s get rid of the things we don’t need. We can remove the Legend, Horizontal axis and the Gridlines. We can do this by deselecting them in the Add Chart Element menu.

Then we’ll fix the axis, since PowerPoint has done its usual trick of charting the spreadsheet in reverse, with the first row of data at the bottom of the chart. Open the Axis Format tools (you can right-click and select ‘Format Axis).

Then, under the Axis Options, select ‘Categories in reverse order’ to reverse the reverse order, which is perverse.

While we’re here let’s get rid of the Axis line, because we won’t need it. Go into the Fill & Line tools and select No Line.

Now we’ll adjust the chart itself. Select the first series and open the formatting tools (you can just right-click and select Format Data Series).

Under the Series Options set the Gap Width to 0%.

With the first series still selected, open the Fill and Line tools (or just use the Shape Fill in the Format tab on the ribbon) and set the fill colour of that first series to the darkest shade of your chosen fill colour.

Select the next series and fill with a lighter shade.

And so on through the data series. This progressive lighter colour gives the sense of data series overlapping, emphasising how each column is an extract of the previous.

Custom labelling

Now we need to label each of those columns with their total.

Fortunately, PowerPoint now allows us to pull labels from any set of data we like, so we can use those totals we calculated earlier.

Select the final data series and, using the Add Chart Element, add Data Labels and set them to Inside End.

Select the labels and open the formatting tools (you can right-click and select Format Data Labels).

Under the Label Contains options, tick Value From Cells and select the column of totals you created.

Untick any other options and you should now have the labels set to those totals.

Make sure the label colour contrasts sufficiently with the series fill.

So that’s how we make that in PowerPoint

These preference pyramids are a very specific kind of chart for a very specific purpose and not everyone will need one, but for those that do, they often have to be shown in PowerPoint and its useful to have a way to make them.

One note - these are usually more pyramid shaped. I think these left aligned bars are easier to read, but you can use similar techniques to make something more triangular.

Polar Area

A variation on the Nightingale-Rose — the chart that changed health, warfare and history. Developed by Florence Nightingale for her report to Parliament about deaths from disease in military hospitals during the Crimean War, the Polar Area is a dramatic alternative to a bar chart.

The Polar Area shows the data by sizing equally angled portions of a circle by area to create a kind of fan. It is extremely eye-catching and effective, at least partially because it is hard to make. Which makes being able to create one in PowerPoint an extraordinary treat.

[This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine]

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, click here for the dataset we used to make the example at the top, and a PowerPoint deck with that slide in.

What you’ll need

• A Filled Radar chart

• Data manipulation

• Design tweaks

• Custom labelling

How you do it

To make a Polar Area, we’re going to use a Filled Radar chart as a way of drawing with numbers. By repeating the same value in a Filled Radar we can effectively get it to draw curves. By using separate data series we can divide those curves up into segments. This, however, means that we have to calculate our values as areas not as the raw data, because otherwise the Radar would be sizing the segments by radius, which would be deceptive.

The details

Insert the chart

We’re going to start by inserting a Filled Radar chart. They’re buried away under the ‘Waterfall’ group of charts in the PowerPoint insert chart menu, right at the bottom, which is where they should be. Filled Radar charts are one of the most misused charts in the library, largely because they shouldn’t be used at all. Ghastly things.

However, they do give us a tool with which to draw shapes using data, which is going to turn out to be useful.

As ever PowerPoint creates us a default chart and opens a spreadsheet to show us the data behind that chart.

In order to make a Polar Area, we’re going to have to do a lot of data manipulation.

Arrange the data

As usual in these examples, we’re going to use the default data to make our chart. We’re just going to use the first column of values, although we’re going to change them a lot in the process.

First we’re going to convert the axis column from dates into numbers. This isn’t absolutely necessary, but it is going to help us keep track of what we’re doing with the data.

The Cell Format is, by default, set to Date (select the cells, right click and choose ‘Format Cells’ and you should see the cell format options). Change that to General and the dates should all change to five digit numbers. Don’t worry about why (it’s a long story).

We’re going to swap them out for just integers: 1, 2, 3, 4, etc.

You should now have five rows of data, numbered 1–5. Now insert 11 blank rows between the first data point and the second. You can do this by either selecting the Row 3 in the spreadsheet, right-clicking and selecting ‘Insert Row Above’ eleven times, or by selecting Rows 3–6 and simply pulling them down to create new blank rows.

Make sure the blue frame that designates which cells are being visualised updates to show these changes.

Now select the first data point and copy it down into the new rows (you can do this by simply grabbing the little square that appears in the bottom right corner of the selection and pulling it down). You can do the same thing with the ‘1’ in Column 1.

What you should now see in the chart is a rough polygon with a weird last quarter, where all the other values are.

Now move all the other values over one column (Column C in the spreadsheet) to create a new data series. Move them up one row so that the last row of the first column and the first row of the new column overlap.

Now do the same thing — add in 11 empty columns under that new first data point in the second column.

Then drag down the data point to copy it to all those new rows.

Now we have a polygon with more sides and two colours (where PowerPoint is automatically colouring the data series differently).

And repeat: move the remaining data points over one column, overlap the data by one row, insert 9 blank rows and copy the data point down.

Now we have a polygon with even more sides and three colours.

Repeat for all the data points. Each data point in a separate column, repeated 12 times, slightly overlapping the data series before, by one row.

On that last point: to make sure everything is neat, only repeat the last data point 11 times, and take the twelfth repetition and move it back up to the very first row, so that the first data series now overlaps with it.

What you should now see is a polygon with so many sides it appears, basically, circular. This is why we’ve copied each data point 12 times — as it draws a Filled Radar chart with 60 data points, PowerPoint effectively ends up drawing a circle divided up into equal segments.

Edit the data

So we have our data arranged into segments, but, because we’re using the actual figures and because a Filled Radar chart is drawn along an axis spreading out from the centre, each of those segments is sized by radius, not area.

This is bad. In our example, look at the yellow ‘12’ segment compared to the green ‘28’. It should look just slightly under half the size. It instead looks much smaller. To represent the values correctly, we need to size them by area.

We could do this by square rooting and dividing by π (area of a circle is πr2, remember), but since we’re only really interested in the accurate proportion rather than the value (we’re going to be labelling by hand anyway, not drawing the labels from the spreadsheet, we can just square root everything.

We can do this with an Excel formula: “=sqrt(#value)”.

Apply this to all the data points and now the 12 looks a lot more like half the 28.

We can now finally close the spreadsheet and edit the chart itself.

Edit the chart

Basically, since we’re manipulating the chart with all kinds of jiggery pokery with the data, none of the chart attributes like axes and gridlines are useful. We can delete them all.

The legend can still work, but generally it’s better to label the chart segments directly anyway. We might want to hang on to the chart title, at least.

Colour the segments

Because we’ve entered the data as separate series, PowerPoint will colour them all differently by default. If we wanted to change those colours we can do it easily by just selecting a segment and using the Shape Fill menu to pick a colour, just as we would with any shape.

Add the labels

Because we’re torturing a Radar chart and because we’re having to use values in the spreadsheet to do that that aren’t our actual data points, the easiest way to label the chart is by hand.

Just add a Text Box to the slide (Insert/Text Box) and type in the label. If there’s room it’s often neater to place the label over the segment (make sure the text colour is effectively contrasted with the segment colour), otherwise position outside but next to the relevant segment.

Obviously you can style the text how you like.

So that’s how we make that in PowerPoint

Making a Polar Area in PowerPoint is a complex business, and not entirely successful — those shapes are still not perfect segments of a circle, the outline is still somewhat jaggy.

On the other hand, they are extraordinarily effective charts, giving us a really striking alternative to bar charts (which is what they work best at).

A couple of further notes. They work best when ordered by value — from large to small or small to large — and some audiences can mistake them for pie charts and get confused at first. They’re best used with care, basically.

But then that’s true of all data visualisations, really.

Pictograms

Pictograms are charts that visualise by repeating icons. Icons that represent the subject of the data are repeated in some kind of relationship to the values. It might be one icon per data point, or an icon might stand for 10, or 1,000 or a million.

They are extraordinarily useful charts, combining a great deal of the visual legibility of bar charts with the visual appeal of iconography with the simple ability to understand the data by counting icons.

[This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine]

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, click here for the dataset we used to make the example at the top, and a PowerPoint deck with that slide in.

What you’ll need

• A standard bar chart

• Iconography

• Custom fills

How you do it

For once PowerPoint is here to help us out. The charting engine actually offers us the ability to repeat fills in relation to the data in the chart and there is now a stock library of iconography in PowerPoint. The two together means we can find an icon and then add it as a custom fill in a bar chart.

The details

Insert a chart

Add a standard clustered bar to the slide. You can find this under the Column chart options in the Insert Chart tools.

By default PowerPoint adds a chart filled with example data and also opens Excel to show that data in a spreadsheet.

We only need one data series for this chart, so we can delete the other columns.

That’s all we need to do with the data, so we can close that spreadsheet now.

Add an icon

PowerPoint now comes with an icon library included. Under the Insert menu you will find a Stock Images option, or there is an Icons button in the Insert tab. Either will open the Stock Images panel.

There are actually multiple stock libraries now included: photography, video, illustration, even individual people and stickers. We want icons, however.

The icons are classified under general descriptions (Business, Nature, that sort of thing) and are available as solid and outlined versions. You can also just search for key terms.

The pioneers of the pictogram, the Isotype designers used beautiful, multi-coloured little illustrations in their charts, but we’re better off erring on the side of caution. Because we’re repeating icons, complex images can quickly get confusing and visually busy, distracting from the data.

Make sure the chart object on the slide is not selected (otherwise PowerPoint will insert the icon inside the chart object), find the icon you want to use, select it and choose ‘Insert’.

We can now close the icon library.

Use the icon in the chart

First, let’s get that icon out of the way. We can use the spaces outside of the slide to put things that we don’t want to be visible in presentation but which we still might want to keep around (designers will be used to this from the way that Artboards work in, say, Illustrator).

We can put the icon off the side of the slide because we’re just going to use it to fill the bars.

Set the fill colour of the icon if you need to.

Then copy it to the clipboard.

Now select the bars in the chart and open the Format pane (you can do this by right-clicking and selecting Format Data Series from the pop up menu).

In the Fill and Line options (the paint pot icon), set the Border to No line.

Then, under the Fill options select Picture of texture fill.

Under Picture options select Clipboard. This will fill each of the bars with the icon you just copied, only stretched out to fill the whole width of the bar.

Under the stretch options, select the ‘Stack and Scale with’ option. We can then set how many units in the data each icon represents. In this version we want a Units/Picture scale of 1, giving us a 1:1 relationship between icons and data.

Tweak chart layout

Everything still looks a little distorted, so we need to make some tweaks to the chart layout.

Under Series Options in the Format pane (right-click on the chart, select Format Data Series and then choose the little chart icon), bring the Gap Width down. You usually want to leave a little but it doesn’t have to be much.

We can also just adjust the width of the chart itself to get the icons to a state in which they look presentable.

Finish the chart

Once we have the icons in, we can treat our chart just like any other bar chart.

We can use the Add Chart Element options to add Data Labels.

We can also use it to remove elements we don’t need like Axes, Gridlines and the Legend.

So that’s how we make that in PowerPoint

We can also use multiple data series to create bars with different icons or, by setting the Serries Overlap, ‘total’ fills, showing unfilled or fainter icons.

Pictograms are great, but they have to be used with restraint — they’re very hard to pull off with multiple data series or large values — what they are invaluable for is replacing sized icons, which are awful and should never be used. NEVER. Hear me?

I’m serious.

Never.

Overlaid columns

Overlaid columns are a great alternative to clustered bars. Overlaying columns is a useful way of comparing one main data series to a secondary one — an average, previous time period, contextual comparison etc.

They allow us to foreground one data set over another, making them easier to read than clustered columns and helping us show the information hierarchy clearly: one data set before another.

[This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine]

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, click here for the dataset we used to make the example at the top, and a PowerPoint deck with that slide in.

What you’ll need

• Clustered column chart

• Custom shape fills

• Chart setting adjustments

How you do it

Short version: we’re going to make a standard clustered columns with two data series, then use a custom fill to make the key column narrower than the comparison. This means we can overlay the columns using the Series Overlap settings and so set one column in front of the other while keeping both visible.

The details

Create the chart

We’re going to insert a standard column chart. PowerPoint creates clustered columns by default, which is fine, because that’s what we want.

We only want two data series, though, so we’ll adjust the spreadsheet so there’s only two columns of data.

The one thing we want to make sure is that the key data — the series we want to highlight — is the final column in the spreadsheet. PowerPoint orders the layering of chart elements by placement in the spreadsheet — the last column of data will be on top. For this chart to work, we’re going to want the key data to be on top of the secondary series.

We can close the spreadsheet now as we’re not going to be doing any more with that data.

Style the columns

Now we’re going to give the main column a custom fill so we can overlay it and still have the column behind visible.

Add a square shape to the slide. Make sure it’s properly square by setting the width and height in the Shape Format tools. 6cm x 6cm allows us to divide by halves and thirds easily, so that’s something.

Set the Fill colour of the square to ‘No Fill’. This makes the Fill transparent. You can leave the border on for the time being though — it makes things easier to find.

Duplicate the square.

Set the duplicate to be of a narrower width than the square. Half width is a good start.

Give this new rectangle a fill colour that will stand out against the other column in the chart.

Now select both the rectangle and the square (shift-click will allow you to select them both) and centre the rectangle against the square both vertically and horizontally.

With them both selected set their outlines to No Outline. This will make the square entirely transparent, but still there as a notional shape, with the rectangle as a filled strip down the middle of it.

Now copy them both (Ctrl-C with them both selected will work).

TIP

To be able to see what you’re doing, try moving the shapes off the edge of the slide.

The shapes will still be there but they’ll be out of the way of your chart and they won’t show up in present mode.

(The slide works like an artboard in Illustrator, if you’re used to such things)

/TIP

Now select the main data series in the chart.

You now either open the Format Pane or right-click and select Format Data Series. Either way you want to open the Format Data Series tools and find the Fill options in the panel.

Select Picture or Texture Fill and for Picture Source select Clipboard. This will paste in as a Fill in all of the columns the combined square and rectangle we just copied.

Make sure that the Picture options are set to Stretch (PowerPoint should do this by default).

You can see, hopefully, that this gives columns that are transparent at the sides with a filled strip up the middle — a thinner column, effectively.

Overlay the columns

Now we’re going to overlay this thinner column on top of the full width one.

In the Format Data Series pane, got to the Series Options tab (it has a little chart as an icon).

Set the Series Overlap to 100%. This puts the main data set over the top of the secondary one, because we ensured that it was the last column in the spreadsheet, and so ‘at the front’ in the layer order.

We might want to make the secondary data series a little lighter, just to make sure it isn’t overwhelming our main column.

Style the chart

Let’s tidy up a bit.

We can put the legend at the top, so it’s nice and obvious.

For this chart, since we don’t have many columns, we could label them directly.

Select the main data series and then, under Chart Design > Add Chart Element, select Data Labels to be Inside End.

Set the label colour to be appropriately contrasting against the column fill.

Do the same for the secondary data set.

Some labels we’re going to have to edit individually, because of the overlay , which might mean they’re going to end up against different coloured background with the overlay, or be uncomfortably close together.

You can select a single label by selecting the whole set of labels (clicking any of them will do that) and then clicking on the individual label you want to edit.

With the labels we probably don’t need a y-axis or gridlines, so we can delete them. You can just select them and hit delete or use the Add Chart Element tools to Remove Chart Elements, too.

I’m also going to remove the x-axis line because I don’t think we need that with column charts either. We can see where the axis is perfectly well. You can do this by finding the Format Axis tools (right click on the axis to see the option) and just selecting ‘No Line’ in the Fill & Line options.

As ever the secret is a useful chart is as much removing what is unnecessary as it is showing what is necessary.

So that’s how we make that in PowerPoint

Overlaid columns are far more legible and clear than clustered bars when it comes to that act of comparison, but they still contain a density of information that means they have to be used with restraint.

Obviously they only work with two data series and those data series aren’t presented equally — one is of a different quality to the other — so we need to be definite about information hierarchy and what’s most important in our data set.

We also need to be careful with the amount of other visual elements: labels, axes, etc and keep the visuals as simple and direct as possible.

But then, that’s what we’re always doing, isn’t it?

ISN’T IT?

Lollipop charts

As the name suggests, Lollipop charts show data using circles on the ends of stems to show a single data series against a y-axis.

They are a useful alternative to columns or bars (we’re going to be making columns here). They concentrate on using the position against the y-axis as the key way of showing the data, but they still use the height of the bar too.

They’re less useful than bar charts for small, granular differences in value, but they’re still pretty accurate and easy to read.

[This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine]

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, click here for the dataset we used to make the example at the top, and a PowerPoint deck with that slide in.

What you’ll need

• A connected scatter plot

• Customized data

• Specific chart settings

• Design tweaking

• Custom axis labels

How you do it

We’re going to use a PowerPoint connected scatter plot to make the circle/stem appearance, but this will need some editing. Some connecting lines will have to be deleted and then, by setting the minimum and maximum bounds of the y-axis, we will cut off one end of the plot, hiding one set of the markers and creating the ‘lollipop’ appearance.

The details

Insert the chart

Add a ‘Scatter with Straight lines and Markers’ chart - you can find this under the X Y (Scatter) options in the PowerPoint charts.

As usual, PowerPoint will insert a demo chart and open a spreadsheet to show the data behind that chart.

You’ll notice that the scatter is dots joined by lines, effectively making it a line chart. We’re going to use this to make the lines vertical, from the x-axis up to the dots. To do this, though, we’re going to have to tweak the data first.

Edit the data

First, we’re going to want to arrange our dots into regular columns, to mimic the layout of a column chart.

We’re going to do this by evenly spacing the values for the x-axis. The x-axis, after all, doesn’t show data here, we’re just using it for positioning. Just entering integers (1, 2, 3) will work perfectly.

Now we want to make vertical lines from the bottom up to each data point. To do this we’re going to add extra, low y values for each x value, so that each dot will have another dot directly below it.

We’ll do this by adding a new row in the spreadsheet between the first and second data points.

In that new row we’re going to repeat the x-value of ‘1’ but add a new y-value of ‘0’ to draw a line from our data point down to the x-axis.

We’re then going to repeat that process for all the values in our chart to get vertical lines for all the data points.

Add data labels

While we have the spreadsheet open, we’re going to create a column for our data labels — this is so we can use a hack revealed to me by Jane Kendall of the Bank of New Zealand, who’s come up with a brilliant way of labelling custom charts like these using the ‘Value from Cells’ functionality in the charting library label options.

Pick a blank column outside of the data — outside, that is, of the blue frame that indicates what part of the spreadsheet PowerPoint is visualising.

In the second cell of that column — parallel with the first of your data points — copy that data point.

In the row beneath that, add the name for this category — whatever you would want to show below the x-axis for this data point.

Repeat this process for all the data points, so you end up with a column with all the data points, each followed by their category labels.

Now select the markers in the chart and add data labels to them.

Then select the data labels and open the Format Pane (you can do this in multiple ways, including by double-clicking or by right-clicking and choosing ‘Format Data Series’ from the menu).

In the ‘Label Contains’ list in the Format Pane, select ‘Value From Cells’. This will pop up a dialogue box telling you to select a data range from your spreadsheet. Select the column you just made with the values and the labels.

Once you’ve done this, you can deselect the other options under ‘Label Contains’, turning off the Y Values and the Leader Lines.

We can now close the spreadsheet and concentrate on styling up the chart.

Style the Markers

First let’s style the Markers. Select the Markers (just clicking on one should select them all by default). Open the Format Pane and under Marker Options (you may have to click the Marker button at the top to switch to this, and also click to open the Marker Options panel) choose a Marker shape and size. Pick a size big enough to fit the labels inside.

While you’ve got the Marker panel open, switch off the Borders on the Markers, setting them to ‘No Line’.

Label the Markers

Now we’ll do the labels. Select the first label (this may mean clicking it twice — the first click will probably select all the labels, then you’ll have to click again to select just that one).

In the Format Pane, under Label Position, set it to ‘Centre’ over the Marker. Set the font colour to contrast with the Marker fill colour.

Repeat this process for all the value labels.

Make the Lollipops

Select the Marker for the second data point (again, you may have to click twice to do this, once to select the Markers, once more to select just this one).

In the Format Pane, under the Line Options, select ‘No Line’, or, in the ribbon, under the Format tab, simply select ‘No Outline’.

This will delete the line coming from the previous data point, making this data point an element on its own.

Repeat this for the following data points.

TIP

This can also be achieved by making each data point a different data series.

PowerPoint assumes each column of y values is a different data series. By putting each pair of y values (the -1 & the value) in a new column in the spreadsheet, PowerPoint will plot them as separate data series.

This will mean that they’re no longer connected. However it will also mean that they are all different colours and will all have distinct settings for Marker size, line thickness, etc. When we come to edit these elements we will have to repeat any adjustments for each data series and for a big dataset that can get wearisome.

It depends on where you want your pain points to be, basically.

/TIP

Layout the x-axis

Now select the first of the category labels and in the Format Pane, under Label Position, select ‘Below’. Repeat this for all the category labels so that they now sit over the top of the x-axis labels.

Then select the first of the baseline Markers — the ones set to a y-value of ‘0’ and in the Marker Options in the Format Pane, set the Fill to ‘No Fill’.

Repeat this for all the baseline Markers.

We also need to even up that x-axis. Select the axis and open the Format Pane.

Under the Axis Options, set the Bounds so that they’re evenly spaced around the x-values. Since our example x-values are ‘1,2,3’, we’ll set the axis Minimum to be ‘0’ and the axis Maximum to be ‘4’.

While we’ve got the Axis Options open, set the Labels to ‘None’, as we don’t actually need them.

Style the chart

A few finishing touches. We don’t necessarily need a y-axis either, so you can delete that, if you like.

You can do this by selecting the axis and just hitting the ‘Delete’ key, or by turning it off in the Add Chart Element menu.

We can also get rid of all those gridlines.

The x-axis is still a bit cramped. We can give it more room by selecting the Plot Area of the chart (just click anywhere in the background of the chart, between the data points) and then clicking and dragging to make a little more space at the bottom.

So that’s how we make that in PowerPoint

Lollipop charts take a little extra work and finessing to get right in PowerPoint but they do give us a really useful alternative to column charts. They’re also really useful for showing change, given the impression of rising or falling that they give — like balloons. You can also always swap out the marker shape for an arrow for extra motion.

And, after all, William Cleveland, one of the most influential researchers in data vis, thought they were close to the perfect chart, so that’s got to be a recommendation, right?

Bullet chart

Bullet charts (originally developed by Stephen Few) are bar chart replacements for gauges and meters. They allow us to measure values both against a range and in comparison to a target.

This means we get to combine the clarity and ease of understanding of a bar chart with a lot of contextual detail and they are especially useful in measuring progress and success.

[This tutorial assumes you’re reasonably au fait with PowerPoint and how it makes charts. If you feel you need a more indepth introduction, click here to find out more about the basics of the PowerPoint charting engine]

So, how do we make this in PowerPoint?

In this how-to, we’ll be using the default sample data PowerPoint gives you, so you can follow along without needing to download anything, but if you want to, click here for the dataset we used to make the example at the top, and a PowerPoint deck with that slide in.

What you’ll need

• Clustered column chart

• Custom shape fills

• Visual tweaks

• Chart setting adjustments

How you do it

Short version: we’re going to use a clustered bar with three data series: the total range, the target and the actual value. By giving the target an outline without a fill, we can display it as just a line and by using custom fills we can make the measured value a thinner column, so we can then use the Series Overlap settings to layer them to create a Bullet chart.

The details

Create the chart

We’re going to insert a standard column chart. For some reason PowerPoint creates clustered columns by default, but for once that’s not as irritating as it usually is, because that’s what we want.

Also by default PowerPoint creates a clustered bar with three data series and again, that’s perfect for our purposes. Basically we want a data series for each of the three parts of the chart - the range, the target and the value. And we need those series in that order. PowerPoint layers up the columns in the order they’re in in the spreadsheet, with the last column uppermost in the layer order. When we layer up the columns we’re going to want the range on the bottom, then the target, then the value, so that’s the order we want the columns in.

The main thing we need to do with the data is to set that first column - the range - to the maximum value for the range, in every row (often 100%, but whatever is right for your range). This will give us a full height column behind the other elements in the chart.

Then we can enter our data for our particular targets and values in the other two columns.

Then we can close the spreadsheet - the rest of what we’re doing is in PowerPoint.

Style the range

First we’re going to set the style for the range columns.

Traditionally in a Bullet chart the range background shows some qualitative gradations - indicating quarter marks, or some transition from low to high, to give some context for the value in the foreground.

We’re going to make a traditional range that indicates the lower 50%, then 75%, then the upper quarter of the range.

We’re going to do this with a custom fill.

Add a square shape to the slide. Make sure it’s properly square by setting the width and height in the Shape Format tools. 6cm x 6cm allows us to divide by halves and thirds easily, so that’s something.

TIP

To be able to see what you’re doing, try moving the shapes off the edge of the slide.

The shapes will still be there but they’ll be out of the way of your chart and they won’t show up in present mode.

(The slide works like an artboard in Illustrator, if you’re used to such things)

/TIP

Set the fill of that shape to a mid-shade - we’re going to want it to be darker than the other sections, but not so dark that it’s going to distract from the foreground value. Make sure it has no border (set the outline to ‘No Outline’).

Duplicate that shape and then set the height of the new shape to half that of the original square.

Fill the new shape with a slightly lighter shade than the square and then stack it directly above the dark square.

Duplicate the new rectangle, fill that shape with a slightly lighter shade again and stack that rectangle directly above the first, so we now have a column with a bottom half of one colour and the upper two quarters getting progressively lighter.

Select all three shapes (drag round or shift-click all three) and copy them.

Now select the main data series in the chart (click once and PowerPoint should by default select all the columns in the data series). You now either open the Format Pane or right-click and select Format Data Series. Either way you want to open the Format Data Series tools and find the Fill options in the panel.

Select Picture or Texture Fill and for Picture Source select Clipboard. This will paste in as a Fill in all of the columns the combined square and rectangle we just copied.

Make sure that the Picture options are set to Stretch (PowerPoint should do this by default).

Under Format set the Outline of the columns to match your background colour (this chart type only works on slides with a single background colour - you can’t use it on gradients or photographs - at least, not easily)

Those columns should now all be filled with the half/quarter/quarter colour gradations.

Style the target

Select the second, target data series in the chart (click once and PowerPoint should by default select all the columns in the data series).

Under Format set the Shape Fill to ‘No Fill’. This will make the fill of the column entirely transparent.

Set the Outline to match your background colour (this chart type only works on slides with a single background colour - you can’t use it on gradients or photographs - at least, not easily). Make sure the border width is the same as the width on the background column.

This should give us a column with just an outline and no fill. At the moment this column should be entirely invisible against the background, but don’t worry about that, it’ll be visible once we overlay all the columns.

Style the value

In order to make the range visible behind the value, we’re going to use a custom fill to make the value column slightly thinner than the range.

Add a square shape to the slide. Make sure it’s properly square by setting the width and height in the Shape Format tools. As before 6cm x 6cm is a good choice.

Set the Fill colour of the square to ‘No Fill’. This makes the Fill transparent. You can leave the border on for the time being though - it makes things easier to find.

Duplicate the square.

Set the duplicate to be of a narrower width than the square. Half width is a good start.

Give this new rectangle a fill colour that will stand out against the range column.

Now select both the rectangle and the square (shift-click will allow you to select them both) and centre the rectangle against the square both vertically and horizontally.

With them both selected set their outlines to No Outline. This will make the square entirely transparent, but still there as a notional shape, with the rectangle as a filled strip down the middle of it.

Now copy them both (Ctrl-C with them both selected will work).

Now select the third, value series columns in the chart.

You now either open the Format Pane or right-click and select Format Data Series. Either way you want to open the Format Data Series tools and find the Fill options in the panel.

Select Picture or Texture Fill and for Picture Source select Clipboard. This will paste in as a Fill in all of the columns the combined square and rectangle we just copied.

Make sure that the Picture options are set to Stretch (PowerPoint should do this by default).

You can see, hopefully, that this gives columns that are transparent at the sides with a filled strip up the middle - a thinner column, effectively.

Overlay the columns

Now we’re going to overlay the three columns..

In the Format Data Series pane, go to the Series Options tab (it has a little chart as an icon).

Set the Series Overlap to 100%. This puts all the columns in each category over the top of each other.

Because we put the data columns in the spreadsheet in the order range, target, value, the columns will now appear with the range at the back, target on top of that and the value in the foreground.

And because we set the target column to just have an outline, it should now appear as just a line across the range.

Style the chart

Traditionally the only data labels on a Bullet chart are on the value column. We can add these easily. Select the value data series and then, under Chart Design > Add Chart Element, select Data Labels to be Inside End.

Set the label colour to be appropriately contrasting against the column fill.

We should also have a y-axis to explain the range gradations. Select the y-axis and open the Format Axis tools in the Format Panel (you can do this by right-clicking and selecting ‘Format Axis…’).

Under the Axis Options, set the Minimum and Maximum Bounds to match the minimum and maximum of the range (the maximum would therefore match the number you’ve used for the range column in the spreadsheet).

Then we’re going to set the Major Units to match the gradations in the range fill - in this case ‘2.5’ to match the fill changes on the quarters.

But we don’t need anything else, so we can get rid of the gridlines and x-axis line - as well as the Legend, as it doesn’t really help understand the chart.

So that’s how we make that in PowerPoint

Gauges in particular are hard to make in PowerPoint and Bullet charts provide an editable, and - more importantly - superior alternative. They combine the accuracy and ease of column charts with the contextual data of a gauge or thermometer, packing a lot of information into a simple and legible visual.

They are still quite complex charts, though, and probably best used for knowledgeable - or patient - audiences.