Minimum and Maximum Markers

Last week I posted a series of 5 line charts in PowerPoint that were visual. I promised at the time that I would post this week the how to for each of the charts.  This post is the first of these: “Minimum & Maximum Markers.”  You download the sample worksheet here.

Step 1: Setup the data table

The original data table could look like this:

The first thing we need to do is calculate the minimum and maximum values.  You can do this using calculations like the ones below where column B is where the Data column is in the spreadsheet:

=MAX(Sheet1!$B$2:$B$10)

=MIN(Sheet1!$B$2:$B$10)

In my data the maximum is 9 and the minimum is 2.

Once you’ve calculated the minimum and maximum, you will need to add two new columns.   I’ve put them before my Data column and labelled them “Max” and “Min”.

In cell B2 I’ve added the calculation:

=IF(Sheet1!$D2=$G$2,Sheet1!$D2,NA())

Note: Cell G2 is the cell where my Maximum value is stored.

In cell C2 I’ve added the calculation:

=IF(Sheet1!$D2=$G$3,Sheet1!$D2,NA())

Note: Cell G3 is the cell where my Minimum value is stored.

I’ve then copied the values down the table to fill all of the table:

Notice that in columns B and C almost all of the rows show #N/A (except for the minimum and maximum values).  This is very important!

Step 2: Create the chart

Highlight the whole table and click on Insert > Chart > Line > Line with markers.

A chart like the one below will appear:

Delete the legend and gridlines by clicking on them and pressing the delete button.  Then, right click on the graph and choose select data.

Click on the Data series and then press the Up arrow button to move it to the first line as shown in the picture above and then press the OK button.

Step 3: Adjust min / max markers

Create a new marker for your minimum and maximum values.  This can be as simple as inserting a picture, or as complicated as creating a group using the shapes provided by Excel. Once you’ve created your markers, copy the Maximum marker by pressing Ctrl+C.

Then click on the Format tab in the Chart tools section of the ribbon. Choose Series “Max” from the drop down selection box.

With the series selected, press Ctrl+V.  The marker should update with your chosen marker.  Repeat the process for the minimum series and marker.  You can then format the Data series as you wish.  Your chart should now look something like the picture below:

Notes

Incidentally, you can use the same technique to have different markers for when the line is in and out of specification by altering the calculation for the Mix and / or Max columns.  E.g.:

=IF(Sheet1!$B2>=$G$2,Sheet1!$B2,NA())

This calculation will show the value whenever it is larger or equal to the value in Cell G2.

Hope you find this useful!

+Alesandra Blakeston

Advertisements