Blog Archives

Excel – Applying Conditional Formatting to Charts

Hopefully you are familiar with applying conditional formatting to cells, and creating rules to apply more complex formatting rules. If not, check out my blog to see how you can apply conditional formats to cells.

However, when it comes to charts there is no in-built conditional formatting functionality where you can create some sort of rule and   apply it to a series.

In order to create the “illusion” of conditional formatting on charts we have to create some dummy data based on our base data.

Simple sales table

When converted into a chart it gives us this.

Standard chart

As you can see we have a range of products being sold and have different sales figures against each one. To make things stand out a bit more it would be nice to apply some colour coding to the bars of the chart to show which sales are good, bad or indifferent or simply to show on a colour scale how sales are performing.

For this example, let’s say I just want to show a RAG (Red/Amber/Green) system for each product.

First, decide what your break points are going to be i.e. what determines if something should be red, amber or green;

Red <=1500

Amber <= 3500

Green > 3500

Once we have our break points we can set up our table to create the dummy data.

Add a couple of new lines above your table and if you want, label them something like Lower and Upper to represent the lower and upper bounds of your range of values e.g. 0 to 1500 for red.

It’s worth also adding some labels so that these appear in your legend when you create the chart so people understand the meaning of the colours. In this example, I used the formula = C2&” – “&C3 to build up the label for the red values.

Modified table to create dummy data

Now we build up some IF statements to determine which category the actual value from the base data falls into i.e. Product A should be in the amber section. We will start by creating a nested IF statement in cell C5 (in this example) that will test if the sales value for Product A is between 0 and 1500. If it is, then display the number, otherwise show #NA. We need to have #NA as this prevents anything from being plotted in the chart. By fixing certain elements of the formula (see my blog on absolute vs. relative referencing if you are unsure about what I have done) so that the formula can be copied across the columns and then down the rows ensuring it works in all cases.

=IF(\$B5>=C\$2,IF(\$B5<=C\$3,\$B5,NA()),NA())

Once applied to all the cells in the modified table, each sales value for each product should only appear once in the new columns.

Completed dummy data table

Now it’s time to replot the chart but this time using the dummy data.

Highlight all the data apart from the original values.

Chart data selected

Use a stacked column chart to display your data and then apply whatever formatting you want to get your end chart.

Conditionally Formatted Chart

Because of the way the data has been set up, if any of the values change, the formatting you have applied will automatically change based on the values.

Updated numbers

Excel – How to Create a Trellis Chart

There are many ways of displaying data within Excel, and the basic charts that come as standard are perfectly adequate for most jobs. A line chart or clustered column chart is often the best way to display data over time. They are both clear and simple to interpret and at a glance can show you a trend or pattern.

There are times, though, when you might want to do comparisons of data. Having multiple lines or columns can sometimes work, but there may be times when you want to compare two set of data side by side.

This is where a Trellis chart may be useful.

A Trellis chart allows you to place to data points on the same line showing a direct comparison between the two data points. Let’s say we want to compare sales of a number of products over a two year period.

We could use either a line or column/bar chart. It works, but perhaps having the data for each customer on a separate line might be better.

Using standard fare column and line charts

First of all we need to set up our data correctly in order to create the Trellis chart.

In a column to the left of your data add a series of zeros. This will make more sense a few steps further on.

Our zero values t help create data points for labels

Add two more columns to the right of your data and label these the same as your two original data columns – in this example 2014 and 2015.

Setting a rank for each row

Next add one more column and let’s call it LABELS. Add the same descending rank numbers again to this one too.

Our data table is now set up.

Select all the data from column C to F and click on INSERT, and from there select SCATTER chart, which will give you this;

Initial chart appearance

Click on the chart to activate it, and go to the FORMAT tab. From the CURRENT SELECTION group on the far left, use the drop down to select elements from the chart.

The drop down to select an element of the chart

First, select LABELS. You should now see this;

Currently selected data for labels

Grab the PURPLE box that is currently around the 2015 series, and drag it over to the column made up of zeros in column A, so you should end up looking like this;

…after the change has been completed

Now select 2014 series and this time drag the PURPLE box over the data in column B. Your chart should now be taking shape;

Starting to take shape

Remove any vertical grid lines, as these are distracting as we want to be able to compare two data points along a line rather than on a grid.

No vertical grid lines

At the moment the LABELS on the Y axis don’t tell us anything useful, they are merely markers that have helped us to align the data points. We need to attach some labels that tell what each line represents i.e. the customer name.

Create some custom labels in some blank cells. If you have 2013 (or later, assuming the same functionality is carried over) click on the chart, then click on the plus symbol and click the arrow to the right of DATA LABELS and select MORE OPTIONS. Untick the Y VALUE option to remove the current labels and tick VALUE FROM CELLS instead.

Then highlight the cells containing the custom labels you created a moment ago. Make sure you select LEFT as the position. You may need to adjust the PLOT AREA to make sure the labels are clear of the chart. Format the Y axis so that so LABELS are set to NONE. Finally make the markers “invisible” as we don’t need to see them, but they do need to remain in place, and remove LABELS from the LEGEND. Your chart should now look like this;

With added custom labels so it makes more sense

Feel free then to customise your chart as you see fit to make sure people understand what you are showing them.

The completed Trellis chart with a bit of customisation

If you don’t have Excel 2013, you can still do all this, but you will need the X Y CHART LABELER add-in by Rob Bovey. For more information on this useful little add-in see my blog  https://excelmate.wordpress.com/wp-admin/post.php?post=897&action=edit

So that’s how you can build a Trellis chart. It’s an alternative chart to a column or line chart to compare values. Like all custom charts (or even just charts in general), it’s about using the most appropriate chart for the job, rather than just using it because you know how to, but this one does have its uses and can help to tell a visual story.

Excel – Using the “X Y Chart Labeler”

If you have ever looked around for tutorials on charts or read through books on charting techniques, one thing that pops up regularly is the X Y Chart Labeler by Rob Bovey.

This is a great little add-in, and best of all it’s free. You can download it from http://www.appspro.com/Utilities/Utilities.htm

So what does it do, and why is it so great?

When you create a chart in versions up to 2010, you can add labels to your series with one of the following;

• Series name
• Category name
• Value

Label options 2010

In most cases this is ample, but what if you wanted to put something different? You could re-label everything in your data table but then it probably won’t make sense to anyone else. If you use scatter diagrams or bubble charts you can’t even do that as you need both x and y coordinates to place your markers and these act as labels. Identifying the individual scatter points or bubbles becomes quite difficult. You could add text boxes manually to everything but if the data point moves the text box doesn’t, so you have to move everything again by hand. All quite painful really.

So this is where the X Y Chart Labeler comes in.

On the Ribbon

Let’s take a simple example using a table showing sales by customer. What might be of interest is to show the name of the account manager against the customer sales. I could show sales for each customer or sales for each account manager but not both at the same time. Not easily, anyway.

Basic table showing customers and sales

You create your chart as normal, but then you create a set of custom labels, or as in this case the names of the account managers. You will need one label per data point/series in order for it to work, but there is functionality within the tool to show/hide individual labels.

List of account managers

Assuming you have installed and turned on the add-in, click on the X Y CHART LABELS tab and click on ADD LABELS.

Tab detail of the Chart Labeler

Pick the series you want to label from the drop down list, then select the cells that contain your new custom labels. Select where you want them to appear (top/bottom/centre) and click on OK.

Completed chart with custom labels

Your new labels will now appear on the chart. Job done!

If the position is not quite right, click on MOVE LABELS.

Move labels form

Set the number of points you want your labels to move with each click of the arrows, and using the arrows, relocate your labels. You can apply the move to all labels in one go, or select individual labels to apply the movement to.

Manual labeller: allows you to manually label individual data points using either already existing values in cells or by typing a value directly into the form.

Manual labels form

Delete labels: as the name suggests, you can delete labels for a whole series or all labels in the chart.

Delete labels form

Help: it comes with a searchable help file, although to be fair, you can’t really go wrong using this add-in.

Help file

If you have Excel 2013 (and I’m guessing that this will continue to apply to later versions too) you do have the option to attach custom labels, using cells from your worksheet.

Click on the chart to activate it and click on the plus symbol that you see top right hand corner of the chart area. Select CHART LABELS then click on MORE OPTIONS.  Near the top of the window that appears on the right hand side, you will see the option VALUE FROM CELLS. Tick the option and then select the cells containing your custom labels.

Custom label option in Excel 2013

Knowing that this functionality exists in 2013+, is there a need for the add-in? Probably yes! You still need the add-in to move, show/hide labels etc. more easily than doing it all manually, so from point of view it’s worth having. Perhaps later versions will include this sort of extra functionality, but until then use the add-in.

Now…some of you will be looking at this and thinking “wow, this is brilliant….but they’ll never let me download it at work”. We all have over zealous IT security police and if they don’t know what it is or can’t support it (even though it needs no support) they block it. The add-in runs off VBA code and Rob Bovey, the developer, has not locked the code down, so you, or any despotic IT jobs worth can look at the code quite easily and read all the helpful notes and comments that are in the modules and forms.

Forms & modules in the add-in

Sample code & comments visible to all

The add-in is perfectly safe. Rob Bovey is a recognised MVP or Microsoft Most Valued Professional, so he’s not some random programmer who is dumping dodgy code on the internet. The add-in is also mentioned by the likes of Mr Excel, Jon Peltier and other leading lights in the Excel world as the go to tool for this job. So the only reason the IT bod will refuse to load it is because he/she knows nothing about VBA, not because the code is seen as a justifiable threat!

So if you need to add custom labels to a chart, get this add-in. Once you have it you’ll be able to apply it to all sorts of custom charts. It’s quick and easy to use.

Excel – Creating a Basic Waterfall Chart

Waterfall charts are used to show how you got from one figure, let’s say at the start of the year, to how you ended up at the figure at the end of the year, but instead of showing a total value for each period, it will show you the change in value from one period to another.

Let’s take a simple example…

Standard column chart

Above we can see the cash amount left over at the end of each month. If we chart this with a basic column chart it shows those values as solid columns. We can see that there is a change from month to month but we can’t see by how much unless we work it out in our heads.

The waterfall chart (sometimes called a flying bricks, bridge or Mario chart) will display the change in value whether positive or negative as a floating bar. This type of chart is used in a number of ways but examples might include showing cash flow over time, or changes to inventory or staff levels.

Instead of a standard column chart we want to create this;

Waterfall chart

There are add-ins you can buy to create a variety of waterfall charts and I will mention this again later. If all you want though is a basic waterfall chart then it can be achieved relatively simply using a handful of formulas and a bit of formatting.

First we need to modify our data table.

Insert four new columns. You can call them anything you like. In this example I have named them;

• Invisible
• Increase
• Decrease
• Variance

We also need to add a couple of new rows to the table. We need an opening balance (i.e. how much had in the bank at the end of December 2014) and a closing balance at the end of the year.

This will now give us the following layout;

Modified table required to create the waterfall data

Next, calculate the change from one period to another.

Simple bit of maths, one cell minus another

=F3 – F2

…and copy down.

Variance calculation

Now we need to create what will become the floating bars in our waterfall chart. We need one calculation to identify positive values and one for negative values.

In D3 I need the following formula;

=IF(E3>0,E3,0)

This tests the value in the VARIANCE column. As we are looking for positive variances we test for values greater than 0. If the value is negative display a 0 (zero).

Increases calculated

Now for the negative values. In cell E3 enter the following formula;

=IF(E3<=0,ABS(E3),0)

This time show which values are negative, but as we need these values to be displayed as floating bars, the values need to be converted to positive ones, hence the use of the ABS (absolute) function.

Decreases calculated

As a quick check, in each pair of cells representing an INCREASE and a DECREASE, you should find one cell with the value 0 and the other with a positive value in it.

The final calculation we have to do now is work out the INVISIBLE value. We need this to create a value to calculate the start value of our DECREASE/INCREASE bars.. The close up below of the bars in the final waterfall chart shows what we are trying to calculate.

Calculating where bars have to start from

To work these out we need to add the INVISBLE value to the INCREASE minus the DECREASE. Copy across the OPEN and CLOSE values into your INCREASE column and enter the formula as shown below.

Calculating the height of the supporting invisible series

Looking at the numbers, the sum of the INVISBLE and the DECREASE values in one line should add up to the INVISBLE value in the line above.

Quick check to ensure values are correct

Copy your formula down and we now have a full set of data from which to build the chart.

Select cells A1 to the bottom of your INCREASE column.

Selecting your data for the chart

Go to the INSERT tab and select the 2-D STACKED COLUMN chart.

All we need now is a bit of formatting to get the final effect.

Select the INVISIBLE series and format the bars to have NO FILL. Then remove INVISBLE from the LEGEND as it is no longer required.

If you want, colour code the INCREASE/DECREASE bars. In this example I will make INCREASE bars green and DECREASE bars red.

To differentiate your OPEN and CLOSE balances from the variance bars, colour these differently. You may have to click twice on the end bars to select them separately from the rest of the series. Pick any colour you like.

As a finishing touch, you can add the variance values to the floating bars using Rob Bovey’s free add-in – X Y CHART LABELER from www.appspro.com

Completed waterfall chart

And there we have our final chart.

If you don’t fancy doing all those calculations then you might want to consider buying Jon Peltier’s PELTIER TECH CHART UTILITY add-in. The full advanced edition sells currently (April 2015) for \$99 (http://peltiertech.com/Utility20/). At that price it may not be for everyone, but if you have to build complex waterfall charts (as well as some other types included in the add-in) on a regular basis then it’s worth investing in.

Excel – Dynamic Range 12 Month Rolling Horizontal Chart

After publishing my blog on dynamic ranges to create a 12 month rolling chart (http://wp.me/p2EAVc-ah), I have received a number of enquiries about how to apply this to a horizontal table of data.

Personally, I prefer to have my data tables set vertically as it gives me far more options to slice and dice my data, apply filters, pivots etc. Based on the number of questions I have had there seem to be quite a number of businesses out there using a horizontal data layout.

So here is how it’s done;

The formula you will need will be as follows (bearing in mind you may need to adapt this to your own data table);

=OFFSET(Sheet1!\$B\$2,0,COUNT(Sheet1!\$2:\$2)-1,1,-12)

Our anchor point is \$B\$2, and always starting in the same row (0). The COUNT function counts the number of cells in row 2 and takes away 1 to give the correct end cell i.e. the last cell in the row to contain a numerical value. The height of the range is 1 row but the width is 12 columns back from the last cell that was identified by the COUNT function.

As we are applying this to a chart, we also need to create a dynamic range to pick out the correct labels;

=OFFSET(DynHorizData,-1,0)

As explained in the earlier blog, this dynamic range is based on the dynamic data range we created above. “DynHorizData2 is just the name I assigned to the range.

And there you have it…a 12 month rolling chart based on a horizontal data table.

To see an example using the formulas above follow the link below to open a workbook (Excel 2013) called “Horizontal Dynamic Chart.xlsx”. The workbook is read only, so click on FILE and Save As to download the file and then you can edit the data and see what happens.

https://onedrive.live.com/redir?resid=2C036E200F2C8BCF!242&authkey=!AKh4mjBK2AWbTnw&ithint=file%2cxlsx

Excel – Create a Dynamic 12 Month Rolling Chart

If like many people producing reports at work you report on a rolling yearly basis, you are probably manually changing the data range of your chart every month too either by changing the cell references or even worse, deleting the first month of data and adding the most recent data to the end of your table. All time consuming, and ultimately unnecessary.

It is possible to create a dynamic 12 month rolling chart that automatically displays the last 12 months of data (or any other time frame in fact). All you have to do is add data to the end of your data table and let Excel do the rest!

For this you will need to use the OFFSET function. If you are not familiar with this function, then go to my “Creating Dynamic Ranges” blog ( http://wp.me/p2EAVc-9Y ) to understand how this function works before tackling this…otherwise this will be a complete mystery and quite unfathomable.

Let’s start with some basic data – one year of sales figures.

Basic data table

To create a dynamic chart using this simple table we will need two named dynamic ranges – one for the data itself and one for the labels. Note that when working with charts you will need to create a separate dynamic range for each series as charts treat each series separately so you cannot create a single dynamic named range that includes all rows and columns.

Personally, I like to start by creating the dynamic range that handles the data.

Go to NAME MANAGER and select NEW, or go to DEFINE NAME. Give the range a name of some sort. In this example I will use ChtData. Don’t use the word Chart in your name, apparently it won’t work (Mr Excel).

In the refers to box enter the following formula;

Dynamic range formula to select 12 months of data

We use -12 in the HEIGHT argument as we always want to count back 12 rows from the last cell containing data.

Then we need a dynamic named range to pick out the correct labels (in this case dates) to match the dynamically selected data. In this example let’s call it ChtLabels.

Following the same steps as above enter the following formula;

Dynamic range formula to select the correct date labels

Rather than work everything out from another reference cell, we can use our ChtData range as the reference point or in this case reference range. This will work out what to select based on the first dynamic range, and select values from one column to the left (WIDTH argument = -1).

Check that both ranges work properly by adding some new data at the bottom of the chart and click into the refers to box of the named range to see which cells it selects.

Now time to create our chart. Just follow the usual steps to create a chart…INSERT, CHART etc.

Once our “static” chart is set up, now comes the clever bit.

Select Data icon on the ribbon

Make sure you have clicked on your chart and then click on SELECT DATA.

Edit data source window

In the SELECT DATA SOURCE window, click on the series you want to turn into a dynamic one. If you have multiple series, then you will need to do the following steps for each one.

Click on EDIT.

Original and updated range reference

Currently you should see the cell references relating to the series in the SERIES VALUES box. Remove any cell references but leave the sheet name and exclamation mark. Replace the cell references with the dynamic range name for the data – in this example ChtData. Click on OK. Now do the same for the labels.

Click on any one of the labels under HORIZONTAL (CATEGORY) AXIS LABELS and then click on EDIT.

As before, remove any cell references from AXIS LABEL RANGE, leaving the sheet name and exclamation mark exactly as before. So in this example we should now have Book1!ChtLabels.

Amended label range reference

All ready to go! Now just add new data and watch your chart automatically update to always show the last 12 months of data.

Completed chart with extra data but still only showing most recent 12 months

Basic Charting – How to Create a Chart

On any given day of the week, there are at least a couple of pleas on Twitter for help on how to create charts in Excel. So to assist all of you who have asked, and no doubt for the many who will follow, here are the basics of creating charts in Excel.

First of all make sure your data table is workable. By that I mean make sure that it is one continuous table of data; NO subtotals, NO blank rows/columns within the data, leave a gap of at least 1 row and 1 column around your table, NO double row headings and ideally leave the top left hand corner cell EMPTY. By leaving this cell empty you will avoid a host of potential problems. For basic trouble free charting a simple, basic layout is best.

Or…

Or….

You can have pretty much as many rows and columns as you like but bear in mind that too much data makes it very hard to read the chart.

So limit the data you want to convert to a chart…

Far more sensible and actually easy to view and understand.

Once you have a usable table click somewhere in it. You don’t have to highlight the entire table. As long as you have clicked in the table Excel will work out how many rows and columns make up the data table. Basically it looks around the cell you have clicked in until it finds empty rows and columns which tells Excel it has found the outer boundaries of the data table. That’s why you leave at least 1 row and 1 column between your data table and any other data in your worksheet to avoid including data that is not part of the table you want to base your chart on.

That’s the hard bit out of the way!

You have 2 keyboard shortcuts available to you to instantly create a chart;

• F11 creates a chart in a new chart sheet
• Alt + F1 creates a chart on the same worksheet as your data table (v2007 onwards)

There you are…one chart done and dusted. Using these shortcuts will use whichever chart type is set as the default on your PC. You may well find that the default is a column chart. In fact this is a very good default type to have. Plain, but it works, and usually shows the data in an easy to understand layout.

If you want to change the chart type, make sure you have clicked on the chart to activate it. Once selected you will see 3 new tabs top right hand corner of Excel; DESIGN, LAYOUT and FORMAT.

Make sure you are on the Design tab and over on the far left you will see CHANGE CHART TYPE. Click on that and you will get the full list of available char types. Not all of them are recommended (for a variety of reasons). If you are interested in which charts you should or should not use, check out  this blog by Jon Peltier http://peltiertech.com/WordPress/excel-chart-types/

If you want to have a little more input into how your chart is created then start off exactly the same way as above i.e. click somewhere in your data table. Then click on the INSERT tab (if you are using 2003 or earlier, you have the chart wizard to help you. Just click on the chart wizard icon and follow the prompts), and from the Charts group click on one of the chart options, then choose from one of the sub-options that appear. To see the full range of charts click on the launch button in the bottom right hand corner of the group icons.

Now it is simply down to editing the overall appearance of your chart. Once again make sure the chart is activated by clicking on it, then check out the three CHART TOOLS tabs;

• Design: change chart type, pick from a range of preset colour schemes, switch the rows and columns over
• Layout (this is where you’ll do most stuff): add titles, axis labels, position your legend, add series labels, show/hide data/axes/gridlines
• Format: change shape and text styles. Chances are you won’t do much here.

The DESIGN ribbon;

The LAYOUT ribbon;

The FORMAT ribbon;

The only other thing you might want to do is change the appearance of the series and the chart background etc. All you have to do is RIGHT CLICK on any part of the chart and you will get the option to format whichever part of the chart you have clicked on. Choose from the multitude of options available to you. I could go through each one but it’s more fun trying things out for yourself. If it all goes pear shaped just do Ctrl + Z to undo your last change.

And there you have basic charting.