Excel Chart and Charting Tips and Tricks

Increase Excel's charting capabilities With These Charting Software Downloads

One of Excels most popular features would arguably be its ability to chart data . Below are some of the tips and tricks I have found (mostly by accident) over the years.

Custom Charts

You have just spent hours creating your chart and its a looking exactly how you want it, the only problem is you cannot stand the thought of having to do the same thing all over again for your next chart(s). Below are three ways that will save you many hours.

Short and Sweet
Simply click on your chart to select it and then right click and select Copy. Now all you have to do is paste your new chart where you want it and change its Data range and Series.
If your chart is on a Chart sheet then click the sheet name tab and hold down your Ctrl key and drag the sheet to another location.

User Defined
Right click on your chart and select Chart type then click the Custom Types tab. At the bottom of this page you will see the Select from box. From in here select User defined, now click Add. Type in a name for your chart and a description if needed, then click OK.

Default to User Defined
Do exactly as above in User Defined but after clicking Add and typing in a name and description click the Set as default chart button. Now each time you create a chart it will default to your custom type.

Not Plotting Empty Cells

A very common problem with charts is that if your data range contains empty cells Excel will try to plot them. This makes your chart drop off suddenly and leaves you looking at a chart with a lot of missing data. Below are two ways this can be overcome. The second method would probably suit most needs.

Hiding Rows or Columns
Let's assume you have a chart that is plotting sales figures by month. The month names are in cells A1:A12 with their associated sales figures in B1:B12. This would mean if the current month was April then your chart would be plotting 8 months of empty cells, as May:Dec figures are not yet available. To avoid this, you could simply hide rows 5:12 (May:Dec). Excel will not plot hidden rows. To hide these rows select them and go to Format>Rows>Hide.

Return #N/A
Using the same example as above in Hiding Rows or Columns follow these steps:

Place The Month Names Jan:Dec In Cells C1:C12
In Cell B1 Type =NA() And Copy It Down To B12
In Cell A1 Type =IF(ISERROR(B1),B1,D1) And Copy It Down To A12

As you enter the sales figure for the month you will type over the =NA().
This will mean that if the sales figure has not been entered for any month both the month column (Column A) and the sales figure column (Column B) will return #N/A. Excel will not plot #N/A.

Changing the chart data range - quickly!

Let's assume you have a chart that is plotting dollar values in the range A1:A20. Now let's say sometimes you need to add a new series eg; some comparison Pound values in range B1:B20

Drag and Drop
Highlight the range B1:B20, place your mouse pointer over any border of the highlighted range until it changes to an arrow. Now click and drag the range onto the chart and drop it. You should now see the chartPaste Special dialog box. You can also do this by copying your data then choosing Paste Special from the Edit menu.

Scrolling Charts

Give your charts a professional look by using a Scrollbar to increase and decrease the data range it is plotting. Just follow these easy steps:

On Sheet1 In Cell A1 Type The Heading Amounts And In Cell B1 Type The Heading Months And In Cell C1 Type The Number 1.
Fill Cells A2:A13 With Some Numeric Values And In Cells B2:B13 Put The Months Of The Year I.E. Jan:Feb.
Go To Insert>Name>Define And Type The Name MyValues In The Names In Workbook Box.
In The Refers To Box Type The Formula =OFFSET($A$2,0,0,$C$1,1) And Now Click Add.
Repeat Step 3, Except Replace "MyValues" With The Name MyMonths. Repeat The Last Step Except Replace $A$2 With $B$2. Click OK.
Click The Chart Wizard From The Standard Toolbar, Select Line From The Chart Types, Choose Any Type From The Chart Sub-Type. Click Next And Then The Series Tab.
Click Add, Select Cell A1 Using The Collapse Dialog To The Left Of The Name Box Or Type Amounts. In The Values Box Type =Sheet1!MyValues And In The Category (X) Axis Labels Type=Sheet1!MyMonths.
Step Through The Rest Of The Wizard Setting Up The Chart How You Want And Finally Click Finish.
Go To View>Toolbars>Forms And From This Toolbar Click The Scrollbar Then Click On Your Chart. Position And Size The Scollbar On The Chart.
Right Click On The Scrollbar And Select Format Control Then Click The Control Tab. Change The Current Value To 1, Change The Minimum Value To 1, Change The Maximum Value To 12, Change The Incremental Change To 1.
Finally Put $C$1 In The Cell Link Box And Then Click OK.

Now use the scrollbar to change the data range in your chart.

Changing Range Chart

Here is a great little trick that will allow you to change your chart range by selecting it from a list. This one should impress the boss!

On Sheet1 In Cell A1 Type The Heading Amounts, In B1 Type Prices, In C1 Type Ages, In D1 Type Months.
Under These Headings Type Put Some Values. Under Months Put The 12 Months Of The Year.
Click In Cell F1 And Go To Data>Validation. Select List From The Allow: Box. In The Source Box Type =$A$1:$C$1. Click OK.
In Cell G1 Put This Formula: =IF(F1=A1,0,IF(F1=B1,1,IF(F1=C1,2,0))) This Will Make The Default (If F1 Is Blank) Amounts.
Go To Insert>Name>Define And Type The Name AnyColumns In The Names In Workbook Box.
In The Refers To Box Type The Formula =OFFSET($A$2,0,$G$1,COUNTA($D$2:$D$13),1) And Now Click Add.
Click The Chart Wizard From The Standard Toolbar, Select Line From The Chart Types, Choose Any Type From The Chart Sub-Type. Click Next And Then The Series Tab.
Click Add, Select Cell F1 Using The Collapse Dialog To The Left Of The Name Box Or Type =Sheet1!$F$1.
In The Values Box Type =Sheet1!AnyColumns And In The Category (X) Axis Labels Type =Sheet1!$D$2:$D$13
Step Through The Rest Of The Wizard Setting Up The Chart How You Want But For The Chart Location Choose As Object In, Finally Click Finish.
Position And Size Your Chart So You Can Select From Your List In Cell F1.

Now select a name from your list in F1 and your chart series range will change accordingly.

Two Handy Dynamic Ranges For Charts

Expand Down One Row Each Month
In the Refers to box type: =OFFSET($A$1,0,0,MONTH(TODAY()),1)

Expand Down One Row Each Week
In the Refers to box type: =OFFSET($A$1,0,0,WEEKNUM(TODAY()),1)

You can also change the Columns the dynamic range will span by simply changing the last Argument of the OFFSET function to a higher number than 1.

You could even expand across your Columns dynamically by placing another COUNT or COUNTA formula as the last argument, instead of 1. See below:

In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

This dynamic range will now also expand across Columns in Row 1. So if you add another Column to your Table the dynamic range will automatically incorporate it.

To try and give you a better understanding of the OFFSET formula, read the text below taken from the Excel help file.

OFFSET(reference,rows, cols,height,width)

Reference : is the reference from which you want to base the offset. Reference must be a reference to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.

Rows : is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the Starting reference) or negative (which means above the Starting reference).

Cols : is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the Starting reference) or negative (which means to the left of the Starting reference).
If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.

Height : is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.

Width : is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.
If height or width is omitted, it is assumed to be the same height or width as reference.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.