1 Using multiple axes
When you have a mix of data to use, such as very large values and some small ones like percentages, you’ll find that the percentages disappear when they’re plotted against the larger data values.
You can solve the problem by adding a second axis for the smaller values. To do this, click on the chart and then choose Chart Tools > Layout tab. Select the chart series that you can’t see clearly on the chart from the dropdown list. Then click the Format Selection icon which is directly below this.
In the dialog box, click Series Options and then Secondary Axis to plot this series on a new axis that has a different scale applied to it. Now, with the series still selected, select Chart Tools > Design tab and choose Change Chart Type. Because only part of the chart is selected, whatever option you choose now will be applied to only the selected data series. Choose a contrasting chart type for this series to draw attention to the fact that it’s plotted against a different axis. A good combination where you are using a column chart for the large values is to use a line chart for the very small values.
To finish, from the Chart Tools > Layout tab, select Axes and name your primary and secondary vertical axes to indicate what’s being displayed on those axes. Formatting the axis titles so they match the colour of the series plotted on them will help the reader understand your chart.
2 Pictures in charts
Make it immediately obvious what the chart is about by adding a picture. To do this, click the chart to select it and, from the Chart Tools > Format tab, click the dropdown list in the top left corner and choose Plot Area to place the image behind the chart. Alternatively, choose Chart Area to fill the entire chart box with the image.
Choose Format Selection, which is immediately under the dropdown list. Select Fill and then click Picture or Texture Fill. Select File to open an image on your disk, or choose Clip Art to use a clip art image. When you insert the image it will appear behind the chart. To make the picture partially transparent, adjust the transparency slider. If you place your image on the Chart Area, you may want to select and set the Plot Area Fill to No Fill or make it partially transparent so you can see the image more clearly.
3 Re-size chart bars
By default, bar and column charts are created with the bars and columns in a preset width. If you’d prefer the gaps to be smaller and the bars wider, this is easy to change. First, choose Chart Tools > Layout tab and select one of your chart series from the list. Then, choose the Format Selection option immediately under this and select Series Options from the Format Data Series dialog. Adjust the Gap Width slider to make the space between bars or columns wider or narrower – if you make the gap narrower, the bars become wider and vice versa. If you’ve plotted multiple series on your chart, you can overlap the bars or columns by dragging the Series Overlap slider towards Overlapped. This overlaps the bars or columns so they’ll be wider as well as being overlapped.
4 Add new data
In Excel 2003 and earlier versions, you could add data to a chart by selecting the data and dragging and dropping it onto the chart. This feature was discontinued in Excel 2007 and 2010, making it more difficult than it was previously to add data to a chart.
One way to add data to a chart in Excel 2007 and 2010 is to select the new data (including the column heading if you previously included column headings in your chart data selection) and click the Copy button on the Home tab of the ribbon. Now, click on a data series in the chart and press Ctrl + V to paste the data into the chart.
The other way to add data is to click the chart to select it and choose Chart Tools > Design tab > Select Data. In this dialog you’ll see the data that is already plotted in your chart. The series names appear in the panel on the left and the category or horizontal axis data appears in the right-hand panel. To add data, click the Add button then click on the series name to add – this is the cell or cells which contain the heading that should appear in the legend for this series. Then click in the Series Value box and select the data to include in the chart (but don’t include the headings you just selected). When you do this, the chart will automatically expand to show the data you’ve just added to it so you can check the results. Click OK twice to complete the process.
5 Control missing data
Where your data is incomplete – such as when some of it’s missing – you may find your chart looks strange because of the way Excel plots missing data. You can change the way this missing data is handled by clicking on the chart to select it and choose Chart Tools > Design tab > Select Data and click the Hidden and Empty Cells button.
A dialog box appears with three options for plotting the empty cells: Gaps, Zero and Connect data points with line. Select the option that’s best for your data and click OK twice. All the data missing from your charted range will then be dealt with accordingly.