Chart Tips

Chart  Toolbar       Effective Charting     Dynamic Chart Range Names

Chart Toolbar

The chart toolbar appears when you select a chart. Getting familiar with this menu has saved me a lot of time. Do you find yourself trying to change X axis font size of grid line format by double clicking the chart element and going to the menu for that item? The Chart Menu gives you a direct method to the format menu for the chart element you want.

By pressing the down arrow next to the Chart Element  name box, you get a list of all chart elements in your selected chart. Which element do you want to select?  Let's select the Y Axis. Now press the Format Menu icon just to the right of the Chart Element box.

Excel brings you directly to the Y Axis Format menu where you can adjust the axis patterns, scale, font, number format and alignment.

Effective Charting

Excel has great charting capabilities However, the default settings need work. The XY chart on right shows defaults:
o Legend
o Chart Fill In
o Solid Grid Lines
o X and Y Axis Scales
o Chart & Axis Titles

Do we really need a legend for a chart with one data series? - No. Do we really want a background for our plot area? Do we need so much blank space between title and plot area?  

Let's see how we can clean up this chart with a little formatting. To me, this chart is much cleaner, clearer and effective than the default chart.

Now that we have an XY Chart format that we like, we can save it as one of our Custom Types so that we can reuse the format next time.

Dynamic Chart Range Names

If you have charts that you regularly update with new data,  dynamic range names can help. Let's look at a simple data set where we have flow data for each week.

We want to setup our chart so that it automatically updates as we add additional weeks of flow data. We do this by creating dynamic range names that expand as we add data.

Step 1 - Define X Value Range Name: To create a range name, go to Insert  > Name > Define.

The Define  Name window appears. We add "Week" in Name box and enter an Offset formula in the Refers to box to define our dynamic range name. The general offset formula is:

   = Offset(start cell, rows offset, cols offset, number of rows, number of cols)

 Step 2 - Define Y Value Range Name: Now that we have defined our Week range name, we can define of the Y value (flow)  range with an Offset formula in the Refers to box.

We have a choice for our Y value. We can use a formula similar to the Week range formula or we can make the Flow range a direct offset to the Week range because the Flow range is one column to the right of the week range.

The Offset formula for a new range offset from an existing range is:

    = Offset(book_name!range_name,0,C)

where C is column Offset from existing range.

To define the existing range, notice that we have to give both the workbook name followed by an ! and the range name.

Excel now has two dynamic range names.

Step 3 - Edit Chart Series formula to show dynamic range names. We do this by selecting our chart and the data series. We than want to replace the standard X and Y value cell references with our dynamic range names.

In this example the dynamic range names are

PDA_4_Charting.XLS!week and

PDA_4_charting.XLS!Flow.

Press enter and we are done. Our chart will now automatically update when we add weekly data.