Skip to content

Excel Charts - give them a try

Excel has great charting features and they get better in Excel 2016.  Sadly many long time Office users have been scared off charting because of the difficulties in making and editing charts in years gone by.

Rest assured, Slowly but surely, Excel charts have improved.  They are more robust; sometimes it seemed that making one wrong click would ruin a chart with no undo.  The options are clearer and more useful.

So here’s a quick guide to making charts in Excel.  For Excel novices and anyone who’s been scared off by a bad experience.

Select some cells

First, select some cells to make a chart.  For this simple example, a series of pairs with a text label and a value.

The total isn’t been selected because that high number would distort the chart badly.

Choose a chart

Then go to Insert | Charts. Excel 2013 added a Recommended Charts feature which does a fair job of suggesting charts based on your data selection.

You’re not limited to those recommendations.  Click on All Charts and look at other options. Click on the various charts and hover over a preview to see a larger version.

Back in Excel 2007 there’s many of the same chart options but without the live preview that makes chart selection a lot faster and easier.

Whichever version of Excel you use, the fundamentals are the same.  Select the cells, then select a chart.

All charts are normally ‘live’ – meaning they are linked to the cells you chose.  Any change in the cells will be reflected in the chart automatically.   Recent Excel versions are smart enough to extend the chart when you add rows to the original worksheet.  Failing that you should use named ranges to define the chart axis.

Tweaking your chart

Once you have the right chart, it’s time to customize it to suit your needs.

Each part of the chart is selectable, for example here we’ve right-clicked on the vertical axis then selected Format Axis to show the many, many formatting options available.  In Excel 2013:

There’s similar options in Excel 2007 but without the side pane and seeing changes immediately reflected in the chart.

Excel will automatically choose the scale of the chart based on the values and the automatic scales are usually pretty right.  However you can change them if you wish.

Individual parts of the chart can be selected and formatted. For example here we’ve selected single column and changed their colours (the NSW and QLD state colours). For Victoria we’ve used a picture of the state flower to fill the column.

Where available, you don’t have to select from the chart.  Pull-down the Series options list to select a part of the chart to format.

Moving

Chart elements, including the main chart, can be moved around the chart area.  Click on the edge of an element and look for the familiar ‘four arrows’ icon to appear.  Hold and drag the element around.  Here we’ve moved the title into the empty chart area to save space overall.

Labels and Callouts

Excel has had the ability to add ‘Data Labels’ to individual chart elements.    They are perfect for highlighting important parts of the change.   Right click and choose Data Label to make a label for one or more chart elements.

In Excel 2013 this was expanded to include Data Callouts, shown on right above.   Both Data Labels and Callouts have more flexibility that most people realize.  You can change the text in the box, even use a formula to pull in data from other parts of the worksheet.  Also the formatting can be changed.

To do that, click twice in the label/callout so you can type in the box or use the ribbon to do formatting.  Type in the formula bar if you want to make a formula.

Placing the chart

Excel will normally dump a new chart onto the same worksheet as the data.  Sometimes the chart goes over the cells or down the worksheet out of sight!  Never fear, the whole chart is moveable, click on the edge and drag it to another location.  Or use Chart Tools | Design | Move Chart to move it to another sheet or a new sheet.

Sparklines

Sparklines were new in Excel 2010.  Think of them as mini-charts than fit into a worksheet cell.

Here’s an example of sparklines.  Column sparklines at the right plus line sparklines underneath each column.

You’ll find sparklines in Excel 2010 and later on the Insert menu.

And there’s many options on the Sparklines ribbon.

Be brave … give charts a chance

Don’t be scared of Excel charts.  Give them a try.  Save your work often and remember that Undo is your friend when the chart gets screwy.

A common problem with charts is errors in the data cells – check out Excel charts – it’s all in the data.

About this author

Office-Watch.com

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.