Category Archives: Charts

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.

Let’s start with a simple table.

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

 

Advertisements

Excel/PowerPoint – Distortion Free Image Column Fill (Infographic style)

In an earlier blog, I showed you how to covert a standard stacked column chart into something that looked more like a battery infographic, to create a more visually interesting slide. This time I’ll show you how you can replace columns in a chart with an image but set it up so that you don’t get image distortion which is created by images being stretched or compressed by the different values in your table.

Let’s start with the basic chart.

Default Excel issue chart

Default Excel issue chart

As with all charts….it’s done its job but that is where it ends.

You can then format the chart to contain an image going through the normal FORMAT DATA SERIES > FILL > PICTURE OR TEXTURE FILL and select an image from your computer. Unfortunately, although it “works”, the image becomes distorted – higher values stretch the image and smaller values compress it, giving you this;

distorted-pencil-chart

You could argue it doesn’t make much difference, but people will invariably read more into the image than is necessary – “oooh….I wonder what a blunt tip means?”, “does the size of the rubber tell us anything?”

So you need to find a way of keeping the proportions.

To achieve this we need to create three separate images;

  1. Rubber tip
  2. Main body of the pencil
  3. Pencil tip

In case you are wondering, I used Photoshop to cut out the pencil and then split it into 3 sections. Not everyone has access to Photoshop, so you may need to use some more basic cropping tools. By using Photoshop I am able to control size and blank space around my cut-out shapes more easily.

Splitting out the pencil

Splitting out the pencil

It also means we need to split out the data. At the moment we have a single series – one value per month in our table.

original-data

Original, basic data table

This now needs to be split into three to match the number of images we have. Let’s give a value of 10 to represent the tip and 10 to represent the rubber end. So our new data table now looks like this:

Modified data table

Modified data table

Now re-create the table, but this time use a STACKED COLUMN chart. You should now get this:

Stacked column chart

Stacked column chart

As before, you now need to format each series by inserting the appropriate image into each series i.e. image of the tip in the top series, the main body of the pencil in the middle series and finally the rubber tip into the bottom series. Once complete you will get an undistorted image in your columns, with each tip and rubber end the same size.

Take away lines and axes, add some values above the pencils and you end up with what appears to be an infographic of some sort, ideal for a PowerPoint presentation:  a nice clean image free of clutter and unnecessary detail which hopefully will be a bit more memorable than the default offering in Excel.

Final chart/infographic

Final chart/infographic

Here is another example using exactly the same principles but applied to a bar chart;

petrol-shipments-chart

PowerPoint – Make a Chart Look like an Infographic

blog-header

Far too often, people will add charts to their presentations without even attempting to make them vaguely interesting or memorable. At the end of the day, data is data and is not necessarily the most interesting thing on earth, but there is no need to bore people to death showing an endless procession of slides made up of bullets and standard charts.

Infographics are one way to make your slides more interesting and hopefully more memorable. You’ll see all sorts of infographics in the press and on the web, but many of these are created by graphic designers using specialist software which most of us will not have access to.

However, you can use in-built formatting options, and drawing tools within PowerPoint to create infographic-type shapes and images. In earlier blogs, I showed you how to create shiny spheres  and transparent cylinders using only the tools and options you find in PowerPoint.

At the top of the page, you have a standard 100% chart and next to it is the same chart but with three added shapes and some formatting to make it look like a battery. Hopefully, people will remember the battery and what parts of the business give it a “full charge” compared to the rather dull standard Excel chart.

Working directly in PowerPoint, insert a chart into a slide.

insert-chart

Select 100% STACKED COLUMN chart type.

100-stacked-column

Click on OK.

Edit the data in the worksheet that opens.

chart-data

You may need to SWITCH ROW/COLUMN to get the values stacked up properly, but once done you will get the standard Excel chart with whatever colours it selects for you by default.

basic-default-chart

It’s done its job. you could add some labels but it’s hardly memorable.

So let’s convert it into something more akin to an infographic.

First of all, get rid of anything you won’t need in the final “image” i.e. x-axis, grid lines etc. You can always remove or add elements depending on the final look you want to create.

Now to add the shiny effect to the chart to make it look like it might be a cylinder and has some depth to it:Click on one of the segments in the chart to select it.

If you are using Excel 2010 or earlier you will find a number of “interesting” preset gradients. Some of these are actually quite good for what we want to do.

old-gradient-presets

If you are using 2013 or later then these preset options have disappeared and become a selection of standard colours which admittedly are less gaudy but you will have more work to do to get the final desired effect.

new-gradient-presets

Whichever version you have, you will need to adjust the GRADIENT STOPS, adding light and shade sections to the chart segment. If you need to add GRADIENT STOPS just click on plus button next to the GRADIENTS STOPS . Move the marker to the appropriate position and then select a colour. This is entirely down to personal choice but for reference these are the settings I chose to achieve the look in this blog (use whichever colour you want to set up your marker positions);

  1. Stop 1: 0%, blue-gray, Accent 1, darker 50%
  2. Stop 2: 13%, light blue, Accent 1, lighter 40%
  3. Stop 3: 30%, blue-gray, Accent 1, darker 50%
  4. Stop 4: 78%, light blue, Accent 1, lighter 40%
  5. Stop 5: 82%, white, background 1, darker 5%
  6. Stop 6: 100%, blue-gray, Accent 1, darker 50%

gradient-stops-bar

Apply the same gradient settings to each segment picking a different colour each time. This is not as bad as it sounds. When you select GRADIENT for each segment the settings you applied are still there. All you will have to do is change the colours.

Once complete, you should have something like this;

completed-battery

All you need to do now is add some shapes top and bottom of the chart to create the battery effect.

Using the standard shapes, use the RECTANGLE: ROUNDED CORNERS and create two blocks to represent the top and bottom of the battery, and then a smaller third block to represent the positive or cathode end of the battery. Line them up and send them to the back so they don’t hide any of the chart. Apply the same gradient to each rectangle….and done.

There you have an editable chart that looks like a battery!

To edit your data, right click on the battery and select EDIT DATA.

edit-option

Change your numbers (better still…get the data to update itself) and close the workbook to see your new updated chart/battery.

update-your-chart

 

Excel – Create a Custom Matrix Background

If you have ever been on any sort of management course, you will have been shown a variety of matrixes to represent data, strategy etc. These can be quite visual and can help to identify products or people who fit into different areas allowing you to target specific areas rather than try and tackle everything. Most of the time, you will see these in PowerPoint or Word, and have been manually created and are not particularly accurate and are not dynamic in any way.

So how can we create a useful matrix on which we can plot data?

First of all we have to decide on the type of chart that will best display our raw data. In the case of matrix displays a scatter chart is best, plotting results based on two set of values, effectively giving us x and y co-ordinates.

Using recent enquiries I have had, some examples of this might be:

  • strategic importance of work compared to the revenue it will generate
  • knowledge transfer from long serving staff due to retire – level of critical knowledge vs. the amount of time before retirement

In both of these examples, the business wants to identify work or people that might have a critical impact on the business so that they can be prioritised ahead of less critical work or where very specific and unique knowledge sits with one person, and ensure that an individual’s knowledge is recorded or transferred before they leave.

Let’s take the critical knowledge transfer and create a chart to track that.

One piece of information I will need is the amount of time between today and the day the person is scheduled to retire, and I might break that down into three segments;

  • less than 3 months
  • 3-6 months
  • 6 months to one year

The other piece of information I will need is the level of knowledge an individual might have and assign scores or levels (these are likely to be subjective);

  • Low
  • Medium
  • High

So we may find ourselves with data like this;

Basic starting data

Basic starting data

Because we can’t plot “words” in a scatter chart I need to change the Low-Medium-High ratings to numerical values. Depending on how you measure this you can use a simple 1-2-3 or have something with a few more breakpoints – choice is yours.

So now our new table might look like this (I’ve used a basic data validation list for the rating);

Data with added scores/rating values

Data with added scores/rating values

We can now create a simple scatter chart using the due retirement and rating columns.

Standard scatter chart

Standard scatter chart

The data has plotted OK but nothing jumps out at us to start dealing with those who will be shortly retiring and have critical knowledge level of high. What we need is some sort of background with clear segments to help identify target employees.

We could create or find an image that contains the number of segments we need but the proportions may not be that accurate, so the image will have to be stretched or simply won’t fit in with our data. So we are going to create the background manually using dummy data.

As we have three levels of criticality and 3 time periods we will need a 3 x 3 grid pattern.

Away from your real data, type the following values;

Dummy data to create bandings

Dummy data to create bandings

Using these values create a stacked column chart…

Base chart

Base chart

…and get what you see above. At this point we need to do a few minor adjustments;

  • Fix the maximum value of the Y-axis to 3
  • Set the gap between series to no gap or 0%
Change axis maximum value

Change axis maximum value

Change settings on the chart - gap width

Change settings on the chart – gap width

Our chart will now look like this;

ScreenHunter_214 Aug. 09 11.23

Before we start applying different colours to the various segments that make up the chart, we have to move the axes out of the way. The axes need to be moved because we need to copy the “real” data onto the chart, and in order for the data points to look as they did in the first basic chart (with no background) we need to use secondary x and y axes. If this is not done, the real data looks back to front and upside down as you need to read the values off the secondary axes rather than off the normal x and y axes. It’s not a major issue but best to sort out at this stage.

Right click on either axis. If you select the x axis first then select the axis option VERTICAL AXIS CROSSES AT MAXIMUM CATEGORY.

Right click on the y axis and from the options select HORIZONTAL AXIS CROSSES AT MAXIMUM AXIS VALUE.

The chart should now look like this;

ScreenHunter_214 Aug. 09 11.24

Apply formatting of some sort to your segments. Use any of the fill options available to you at this stage – solid colours, gradients, images or patterns…more options than you can shake a stick at.

The path is now clear to add the real data to the chart.

Highlight the due retirement and rating columns as we did in the original plain scatter chart and copy them.

Depending on how much data you have, you can create a separate column using a formula to only identify people who have a leave date within the next year…no need to plot people who are not going for another 10 years!

Click on the chart that is being used to represent the 3 x 3 grid of our matrix. On the HOME tab click on the arrow below PASTE and select PASTE SPECIAL. Instead of the normal options you see if you do this (paste values, paste link etc.) you will see this dialog box;

Paste special window for chart data

Paste special window for chart data

Select all the options as in the picture above and click on OK. The chart that appears looks very odd as it is trying to plot your copied data as stacked columns – the same chart type as the base chart with our grid.

Our chart with the added "real" data

Our chart with the added “real” data

If you’re using 2010 right click on the new series you just added and select CHANGE SERIES CHART TYPE, and select SCATTER WITH ONLY MARKERS type chart. If you’re using 2013 or later then it will open the COMBO chart dialog window. Select the correct series and select SCATTER.

Set the correct combination charts

Set the correct combination charts

ScreenHunter_214 Aug. 09 11.28

New series changed over to the correct scatter type plot

Now, it’s purely down to formatting. Depending on what you are presenting and how you want to highlight data points will be entirely up to you. In my version here, you will see I have added shading to differentiate between the high/mid/low ratings and some extra labels to identify the criticality levels, as well as some arrows to highlight the timescale – the latter having been added manually.

Completed chart with custom gradients and additional labels/arrows

Completed chart with custom gradients and additional labels/arrows

The only other thing I have added are some custom labels. As we are working with a scatter chart we can’t attach any useful labels to the data points. I have added a new column with a formula to pick out the names of people who have a criticality level of high and their leave date is within the next three months. I then used Rob Bovey’s xy chart labeller which is a free add-in that is perfect for this sort of job. If you are not familiar with this add-in, read my blog http://wp.me/p2EAVc-et which explains how it works and where you can get it from – an absolute must for these sorts of jobs.

So that’s one way of creating a custom background to make a matrix type display using Excel. Once you have your basic set up for the background, i.e. number of segments you want to measure against, it’s down to your raw data. Depending on your own Excel skills, use data validation, dynamic ranges, formulas etc. and whether you are working with static or continuously evolving data, how much interaction and dynamism you want to build into your matrix. With a little thought and preparation you can construct a pretty useful matrix type chart that will highlight data points, be they people or activities that need attention.

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 columns and line charts

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

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.

In your new columns rank your rows in descending order.

Setting a rank for each row

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.

Adding a ranked labels column

Adding a ranked labels column

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

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

The drop down to select an element of the chart

First, select LABELS. You should now see this;

Re-selecting our data

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 competed

…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;

ScreenHunter_204 Aug. 31 11.49

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 gridlines

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

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

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

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

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

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

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

Tab detail of the Chart Labeler

The add labels form

The add labels form

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

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

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.

Other functionality within this add-in;

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

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

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

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

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

Forms & modules in the add-in

Sample code & comments visible to all

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

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

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

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

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

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

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

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

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

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

Selecting your data for the chart

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

Ready to apply final formatting

Ready to apply final formatting

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

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

Horizontal 12 Month Rolling 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 Scrolling Chart

In a couple of my other blogs, I have shown you how to create dynamic ranges ( http://wp.me/p2EAVc-9Y ) and also how to use these to set up a 12 month rolling chart ( http://wp.me/p2EAVc-ah ). This time I am going to show you how to create a dynamic scrolling chart.

By that, I mean a chart that can cover many (100s, 1000s) of data points but only show a limited number at any one time and the user uses a scroll bar to select which data is displayed. This type of chart allows you to scroll through years’ worth of data but only show a set number of data points but only using a single chart.

Here is what we are going to create…

To achieve this we are going to have to combine a number of techniques. So…if you are not familiar with the OFFSET function to create dynamic ranges, then check out this blog first ( http://wp.me/p2EAVc-9Y). If you are not familiar with creating a dynamic chart then check out this blog ( http://wp.me/p2EAVc-ah ). Without this prior knowledge, setting up this scrolling chart will not be obvious…to say the least.

Let’s say I have 8 years of data. If I plot all of it on one chart it’s going to be messy, and not easy to read – too many points and not enough labels!

All data points showing

All data points showing

It would be so much easier to read if there were only 12 months of data visible for example, but I need to look at all my data over the full 8 year period and beyond as I add more data.

Create your chart, including all your data…we will make changes later to convert it into the scrolling dynamic chart we want.

As always, I am going to create my dynamic named range to cover the data points first but to start with I am going to add in my scroll bar as I will need its control cell as part of my dynamic range formula. Place it wherever you want…I quite like to put it directly above or below my chart area.

To add a scroll bar, make sure your developer tab is turned on. From the FORM CONTROLS, select the SCROLL BAR.

Form Controls window

Form Controls window

Click and drag your mouse pointer to draw the scroll bar.

Chart with scroll bar underneath

Chart with scroll bar underneath

ScreenHunter_166 Jul. 15 14.10

 

Right click on your SCROLL BAR and select FORMAT CONTROL.

Format control window for the scroll bar

Format control window for the scroll bar

In the CONTROL tab of the FORMAT CONTROL window, set the start value to 0. This may vary slightly depending on where you start your dynamic range from, but in this example, the starting point of my dynamic range is the first data cell in the column so I will leave the default value 0.

Your maximum value will depend on how many data points you have all together. I have left it here at the default 100, but change this according to your own data.

Now assign a cell to link to your SCROLL BAR. This cell is important as this stores the value generated by moving the bar to the left or right. When you close the window you should see 0 in the cell you selected. In this example, I have chosen $G$4

In another cell (again, does not matter where) enter the number of data points you want to see in your chart. For this example I have chosen 12 but you can set any number you like, and in fact this can be changed as you use the chart.

Now to create the dynamic ranges. As always I prefer to set the data range first and base my labels and any subsequent data series off that one.

For the data points, my formula will be as follows;

Dynamic range formula for the data

Dynamic range formula for the data

The start row for my dynamic range is set by the value in the linked cell. Like that, as I move the SCROLL BAR right or left, it sets the first row to start that many rows away from B2. The HEIGHT of my data set to use in the chart is set by the value I have manually entered in G4.

Now for the dynamic range to calculate which labels to use;

Dynamic range formula for the labels

Dynamic range formula for the labels

Again if you are unsure as to why the formula is set up like this refer to my other blog “Excel – Create a 12 Month Rolling Chart” ( http://wp.me/p2EAVc-9Y).

The final step is to edit all the series and label range references, replacing them with the dynamic range names just like we did in the 12 month rolling chart.

Edited data cell references

Edited data cell references

Edited label cell references

Edited label cell references

You should now have a fully dynamic and interactive chart. All you have to do now is use the scroll button to view all your data.

The final chart with only 12 data points

The final chart with only 12 data points

 

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

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

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

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

Select Data icon on the ribbon

 

 

 

 

 

 

 

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

 

Edit data source window

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

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

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

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