Mark up your charts

Okay, let's start with a table that shows us sales margins. As you can see from the table the date for determination of the figures is always the last day of the year, December 31st.
 

Display Excel tables online >> Excel Jeanie HTML 4

Now let us think about things we need and how to achieve them. We want our users to go through the chart with a user interface so we should think about using form controls. As far as I can see, the scroll bar is the best choice here.

In Excel 2003 if you don't have the forms toolbar available change that by going to View - Toolbar - Forms. Click on the scroll bar icon and - now with the cursor looking like a little crosshair - draw the bar in the size of your choice.

In Excel 2007 head over to the developer ribbon and there choose the the scroll bar from the Insert - Form Controls button. Draw a the bar in the size of your choice.

So the next thing we have to do is to determine a cell which will work as a cell link for our newly created scroll bar. As I think that it could be possible, that this table is reused over the years and therefore additional data may be entered, I chose a cell to the right of the existing content. We will need two additional columns for the next steps so I decided for cell E1. I also gave it a special format (light yellow background, small black border and centered its content through the format cell dialog box which is accessible with the keyboard shortcut CTRL+1).

 Since we want to refer to this cell later on, it makes sense to give it a name. Defining names by the way is always a good practice when working with calculations because it makes your functions and formulas so much more readable.

By the way, we will use names for cells and ranges intensively in our upcoming series on creating dashboards and yes, this is a little appetizer. 

The name I chose here is selYear so I entered it in the top left field directly besides the formula bar.  

The next thing we need to do is to determine the scroll bars minimum and maximum values as well as link it to the previously defined cell. Right-click the scroll bar and select Format control from the context menu. Head over to the Control tab and enter the following values:

• Minimum value: 1995
• Maximum value: 2009
• Cell link: =selYear

Here you can see an immediate advantage of using names: Having =selYear in the cell link text field is much clearer for future usage than having something more cryptic like $E$1. Okay, with this small table it may not be so important but imagine spreadsheet files with several sheets and several form controls.

If you use the scroll bar you can see that the value in cell E1 (our selYear cell) changes from 1995 to 2009 in steps of 1.

The next step would be to add the column for the first marker. As we will use a line graph to visualize the development of our sales margin over the years, we will use some kind of "dot" for the selected year and also to show the current value. I label this column "Marker 1". To enter the values for the whole column in one step I use this approach. I select the range C2 to C16 with C2 as my active cell. Then I enter the following formula into the formula bar:

=IF(YEAR($A2)=selYear,$B2,NA())

Do NOT press Enter on your keyboard. Instead hold down the CTRL key and then press Enter. With this little shortcut you are able to enter the formula in to the whole selected range. Now we will demystify it  ;-)

• IF (YEAR($A2)=selYear, - This tells Excel: "Hey, buddy, take a look at cell A2 and tell me if the year of the date in there is the same than the one which is adjusted in our cell which is named selYear. Oh and by the way, if you copy this formula down, don't dare to change the column, always stick to column A." (A little note for you Excel 2007 users: You have a little more comfort.Not only does Excel proposes you the formulas themselves with a little explanation but also the name selYear if you start typing it.)
• $B2, - This tells Excel: "If the condition mentioned above is TRUE, return the value from cell B2 and again, don't you change the column when copying the formula."
• NA()) - Here we tell Excel that if the condition from above is not met (or false), it should return the the value #NA.

You may ask yourself - especially for the "false" part of the formula - "Why don't we ask Excel to return 0?" Well, this is pretty simple to answer: Because we only want to show the selected data point. If we asked Excel to enter 0 in all the cells where the condition is false, we would have an increase before and a decrease after the selected cell. We don't want that, do we? 

The next marker, which should be labeled Marker 2 in cell D1 has a very similar syntax. Follow the approach mentioned above for entering all the content at once and use this formula:

=IF(YEAR($A2)=selYear,0,NA())

The only part that's different from the first formula is the TRUE part. Here I want Excel to show a 0 (zero) because we want this marker to appear at the bottom of the chart. If we enter anything else than 0, the marker would be flowing around in the chart.

So let's create our chart at last. Select the range A1 to D16 and insert a line chart (I chose the fourth option from the line chart sub-types - the line with markers displayed at each data value). Follow the assistant until you reach step 3. There we need to delete the legend. Do so by switching to the legend tab and untick the checkbox labeled "Show legend". Click the Finish button after that.

So as you can see, we now have our basic line chart. The first adjustment we want to make,  applies to our marker 1 data series. Usually - no matter if MSXL2003 or MSXL2007 - you should have a data point in a different color within your line chart. Right click that data point and select Format Data Series. Be careful to only click this data point once, because if you click it twice you will only format this special data point. This is not what we want, because it changes every time we use the scroll bar.

The settings defined as custom - which you can determine from the screen shot below - are as follows:

• Style: Choose the circle
• Foreground: Choose red as color
• Background: Choose white as color
• Size: Choose 9 pts

I didn't change anything for the line style because with the #NA for all the other data there is no line for this data series. Head over to the Data labels tab and select the third option: value.

Next is the "normal" series so I selected one of the other data points and right-clicked it (again make sure you only click once to get the complete data series).  On the patterns tab I chose None for the marker and left the line styling as it is.

The next thing we choose is the Marker 2 data series which should be on the category axis. I chose the following settings (triangle as style, black as color for foreground and background, size 7 pts, no data labels).

Well, that concludes our styling of the chart. I don't want to leave unmentioned that this approach has two (minor) disadvantages:

• This does not work with all kinds of charts, because sometimes (like with some column charts) you simply don't have a time line to show the current position.
• If you use the table later on to add additional data (like the values of 2010, 2011 etc.) you have to adjust the scroll bars  maximum value manually.

Nevertheless this is - as far as I can see - a very nice way to show the users of your Excel files "where they are". If you like it, please leave a comment and tell me, what purposes you would use that for.

Discuss this blog post in the forums (click the blue button)