Category Archives: 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.
When converted into a chart it gives us this.
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;
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.
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.
Once applied to all the cells in the modified table, each sales value for each product should only appear once in the new columns.
Now it’s time to replot the chart but this time using the dummy data.
Highlight all the data apart from the original values.
Use a stacked column chart to display your data and then apply whatever formatting you want to get your end 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.
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.
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;
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;
- Rubber tip
- Main body of the pencil
- 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.
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.
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:
Now re-create the table, but this time use a STACKED COLUMN chart. You should now get this:
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.
Here is another example using exactly the same principles but applied to a bar chart;
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.
Select 100% STACKED COLUMN chart type.
Click on OK.
Edit the data in the worksheet that opens.
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.
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.
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.
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);
- Stop 1: 0%, blue-gray, Accent 1, darker 50%
- Stop 2: 13%, light blue, Accent 1, lighter 40%
- Stop 3: 30%, blue-gray, Accent 1, darker 50%
- Stop 4: 78%, light blue, Accent 1, lighter 40%
- Stop 5: 82%, white, background 1, darker 5%
- Stop 6: 100%, blue-gray, Accent 1, darker 50%
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;
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.
Change your numbers (better still…get the data to update itself) and close the workbook to see your new updated chart/battery.
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);
So we may find ourselves with data like this;
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);
We can now create a simple scatter chart using the due retirement and rating columns.
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;
Using these values create a stacked column 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%
Our chart will now look like this;
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;
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;
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.
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.
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.
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.
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.
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.
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.
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;
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.
First, select LABELS. You should now see this;
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;
Now select 2014 series and this time drag the PURPLE box over the data in column B. Your chart should now be taking 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.
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;
Feel free then to customise your chart as you see fit to make sure people understand what you are showing them.
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.
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
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.
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.
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.
Assuming you have installed and turned on the add-in, click on the X Y CHART LABELS tab and click on ADD LABELS.
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.
Your new labels will now appear on the chart. Job done!
If the position is not quite right, click on MOVE LABELS.
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.
Delete labels: as the name suggests, you can delete labels for a whole series or all labels in the chart.
Help: it comes with a searchable help file, although to be fair, you can’t really go wrong using this add-in.
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.
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.
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.
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…
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;
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;
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;
Next, calculate the change from one period to another.
Simple bit of maths, one cell minus another
=F3 – F2
…and copy down.
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;
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).
Now for the negative values. In cell E3 enter the following formula;
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.
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.
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.
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.
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.
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
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.
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);
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;
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.
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!
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.
Click and drag your mouse pointer to draw the scroll bar.
Right click on your SCROLL BAR and select FORMAT CONTROL.
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;
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;
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.
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.
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.
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;
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;
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.
Make sure you have clicked on your chart and then click on SELECT DATA.
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.
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.
All ready to go! Now just add new data and watch your chart automatically update to always show the last 12 months of data.