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.

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

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 – 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

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.

ScreenHunter_87 Jan. 09 10.01

Or…

ScreenHunter_89 Jan. 09 10.08

Or….

ScreenHunter_90 Jan. 09 10.08

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.

ScreenHunter_91 Jan. 09 10.11

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

ScreenHunter_96 Jan. 09 10.21

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.

ScreenHunter_92 Jan. 09 10.15

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;

ScreenHunter_93 Jan. 09 10.16

The LAYOUT ribbon;

ScreenHunter_94 Jan. 09 10.18

The FORMAT ribbon;

ScreenHunter_95 Jan. 09 10.19

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.

If you want to learn more about charting techniques try out these great websites:

http://peltiertech.com/

http://www.andypope.info/

http://chandoo.org/wp/category/visualization/

Follow me https://twitter.com/excelmate on Twitter

Or check out other articles/blogs on http://excelmate.tumblr.com/