See how to tame unruly data by:
- using multiple axes in Excel
- adding pictures to charts
- resize chart bars
- add data to charts
-deal with missing data
Creating a basic chart in Excel is simple but when your data requires just a little bit extra attention to look its best, you’ll need to delve into the depths of the Excel charting tools. Here I’ll show you five ways of taming unruly data in Microsoft Excel.
Using Multiple Axes
When you have a mix of data to use such as very large values and some very small ones like percentages you’ll find that the percentages disappear when they are 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 and from the selector in the top left of the Ribbon select the chart series that you cannot see clearly on the chart. Then click the Format Selection icon which is directly below this.
When the dialog appears click Series Options and click Secondary Axis to plot this series on a new axis which has a different scale applied to it.
Now, with the series still selected, select Chart Tools > Design tab and choose Change Chart Type. Because you have only part of the chart 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 is 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 is 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.
Pictures in Charts
Make your charts illustrate what their contents are all about by adding a picture to the chart. 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 or choose Chart Area to fill the entire chart box with the image.
Choose either 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 clipart 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.
Re-size Chart Bars
By default, Bar and Column charts are created with the bars and columns a preset width. If you would prefer the gaps to be smaller and the bars wider, you can do so. 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.
Overlap data in charts
If you have 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 will be wider still as well as being overlapped.
Add New Data to a chart
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 behaviour was discontinued in Excel 2007 and 2010 making it more difficult than previously to add data to a chart.
One method of adding 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 method of adding data is to click the chart to select it and choose Chart Tools > Design tab > Select Data. In this dialog you will 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 and 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 that you have just added to it so you can check the results. Click Ok twice to finish.
Control Missing Data
When your data is incomplete such as where some of it is missing you may find your chart looks strange because of the way Excel plots missing data. You can change the way that 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 appears with three options for plotting the empty cells: Gaps, Zero and Connect data points with line. Select the option that is best for your data and click Ok twice. All the data that is missing from your charted range will be dealt according to the option you selected.