Management Tool Best Practices: 3 Excel Tips that Promise Charting Greatness

Be Sociable, Share!

• 

If you are managing the IT infrastructure, senior-level projects or are the IT leader in charge of maintaining and analyzing the majority of IT’s data points, you’re likely using Microsoft Excel every day. And if the application’s charts are intended to help you make large amounts of data more easily digestible, why, then, are they often so complicated and frustrating to create?

We don’t have a pat answer to that question, but we do have tips to make charting a little easier – whether you’re a recent hire or a veteran IT manager.

Learn to Love the Secondary Axis
At times, you may find that one of your data series varies greatly from the others, making your chart difficult to interpret. Enter the secondary axis. It’s especially useful of one series’ data far exceeds the others.

There’s nothing more unsightly than an Excel chart with data gaps.

Here’s how you create one:

1. Right-click on your data series and choose Format Data Series.
2. Click on the Axis tab (in Excel 2007: Series Options).
3. Change it to Plot Series on Secondary Axis.
4. Format your axis as desired.

Note: Secondary axes can cause plotting issues in column charts due to the way Excel plots data series in those charts. It centers all series together on each category per axis.

To counter this, shift your data over by adding blanks to the chart.

1. Create a series of 0 values to match the series that lies on the secondary axis. You will need the same number of data points as your original data.
2. Select the series using the mouse.
3. Copy the data using Ctrl+C or by clicking on the Edit menu and selecting Copy (Excel 2007: Ctrl+C or click on the Home tab, then Copy).
4. Click on the chart and paste the data using Ctrl+V or by clicking on the Edit menu and selecting Paste (Excel 2007: Ctrl+V or click on the Home tab and select Paste).
5. Your new series should be added to the secondary axis and your visible series should have shifted over. Repeat as necessary.
6. In the legend, click on the entries for the “dummy” sets of data and press the Delete key to remove them from the box. (Steven Mak)

Mind the Gap
There’s nothing more unsightly than an Excel chart with data gaps (well maybe an annual report in Comic Sans, but that’s another post). There are three ways of dealing with this issue, depending on the chart you are using.

Let us say that you had a set of data for a month, but one of the data points was missing due to an empty cell in the source data. To fix this issue in a two-dimensional line chart, you have the following options: plot the data point as a zero, so that the line dips down to zero but has no gap; continue the line if the gap falls between two data points by connecting points on both sides; or do nothing and leave the gap as is. Here’s how it’s done:

Excel 2000, 2002, 2003:

1. Select the chart by left-clicking on it.
2. Click on the Tools menu and select Options.
3. Click on the Chart tab.
4. Select one of the “Plot empty cells as” options that are described above. For three-dimensional graphs, the second option will not be available.

Excel 2007:

1. Select the chart by left-clicking on it. Excel will add three Chart Tools tabs to the Ribbon: Design, Layout and Format.
2. Choose the Design tab.
3. Click on the Select Data button on the Design tab in the Data group.
4. Click on the Hidden and Empty Cells button at the bottom left of the dialog box. In this dialog, you can select from the three options described above. For three-dimensional graphs, the second option will not be available.

Then there’s the case of hidden rows, which can be troublesome. If a data series you expect to see is not showing on the graph, the row or column may be hidden, or the data may be filtered. To fix this, go to the Chart tab of the Options menu (Excel 2007: in the Hidden and Empty Cells dialog).

In Excel 2000, 2002, 2003: Check the “Plot visible cells only” checkbox if you do not wish to have hidden rows or columns plotted on the chart.

In Excel 2007: Check the “Show data in hidden rows and columns” checkbox to plot hidden rows and columns on the chart. (Jorg Freiberg)

Go Green: Recycle Your Charts

If you’ve made a standout diagram in the past, one that you lovingly tweaked and polished till you had it just right, your efforts were not for naught. Instead of recreating a stellar chart each time, just reuse it by simply changing the source data.

In Excel 2000, 2002, or 2003:

1. Select your chart.
2. On the Chart menu, select Source Data.
3. In the Data Range dialog box, either manually update your cell references or click the Collapse Dialog Box button on the far right.
4. If you clicked the button, use your mouse to select the cells you want to include in the chart, and then press the Enter key on your keyboard.
5. Click OK.

Your chart should reflect the data you selected.

In Excel 2007:

1. Select your chart.
2. On the Chart Tools contextual tab at the top of Excel, select the Design tab.
3. Click the Select Data button.
4. In the Chart data range dialog box, either manually update your cell references or click the Collapse Dialog Box button on the far right.
5. If you clicked the button, use your mouse to select the cells you want to include in the chart, and then press the Enter key on your keyboard.
6. Click OK.

Your chart should now reflect the data you selected. (Michelle Fiske)

For more software management tips and tricks, sign up for our free e-newsletter.

MORE INFO IN: Desktop Application Support | PC Helps eTraining | Contact PC Helps

Be Sociable, Share!

•