By

The role of the help desk is shifting from fixing what’s broken to teaching users how to avoid problems in the first place.

In a feature in yesterday’s Computerworld (titled “The New Help Desk: Agile, Educational, Efficient”), writer John Brandon highlighted three IT departments and what they are doing to bring the help desk from where it’s stuck – the 1960s – to the present. One of the organizations featured, the University of Georgia, has put an emphasis on using calls to the help desk to educate users. We like that idea.

Creating charts in Excel

Click to see a video on creating charts in Excel.

The old way of working is myopic. If you keep fixing an issue that, with a little instruction, can be avoided, where is the long-term value? And, if you cannot – or do not — track where problem spots are, how can you plan for the future?

At Vitalyst, we fix stuff too; we’re a help desk, after all. But we also teach customers how to resolve issues on their own, and how to avoid having them crop up again in the future.

In that spirit, today’s post offers tips for creating Excel charts, a topic we receive many calls about. Happy charting.

Creating Charts

By Vitalyst Staff

Data (n.) – raw, unorganized facts.
Information (n.) – organized and processed data that can be useful in some way.

When working with a large amount of data, it often can become an overwhelming task to extract information from the data. Excel provides a great tool to facilitate converting data to visual information through the use of charts.

Follow these steps to create a chart:

Excel 2003 and older:

  • Select any cell within the data you want to chart.
  • Click on the Insert menu, then select Chart to bring up the Chart Wizard.
  • Choose the desired chart type, then click Next.
  • Excel will show you a preview of the chart using what it has detected as plot data.
  • Verify that the data is correctly organized by viewing the Series tab.
  • Click Next to further modify your chart options. You can add titles to the chart or axes, move your legend, add data labels, etc.
  • Click Next to set the location of your chart.
  • Click Finish.

Excel 2007 and 2010:

  • Select any cell within the data you want to chart.
  • Click on the Insert Tab, then click on the desired chart type in the Charts group. Excel will generate the chart as soon as you click on the desired chart type, using what it has detected as plot data.

NOTE: Different chart types sometimes need different types of data. For instance, a pie chart is best for displaying a single series of data, but a column chart is capable of plotting several series.

Watch a video on this tip for Excel 2003.

Watch a video on this tip for Excel 2007.

Watch a video on this tip for Excel 2010.

Reordering Data Series in a Chart

By Vitalyst Staff

You may find at times that you need to rearrange the order of your data series in a chart without changing your source data. This can be accomplished easily using the Format Data Series dialog.

Excel 2003 and older: 
Click on a series in the chart to select it. Right-click the series and choose “Format Data Series.” On the Series Order tab, use the Move Up and Move Down buttons to re-order the selected series.

Excel 2007 and 2010:
 Click on a series in the chart to select it. Right-click the series and choose Select Data. Use the arrow buttons to re-order the selected series.

Watch a video on this tip for Excel 2003.

Watch a video on this tip for Excel 2007.

Watch a video on this tip for Excel 2010.

Creating a Dynamic Chart in Excel

By David McQueary

If you regularly update the data ranges for your charts, a dynamic chart will help you because it automatically shows data added to the end of a column.

First thing we need to do is create a dynamic named range.

Excel 2003 or older:

  • Click on the Insert menu, select Name, and choose Define.
  • Name the range, we will want two named ranges: one for our labels and one for our data. We will define the names as Label and Data, respectively.
  • From here we will enter formulas to create the dynamic named range. The formula to do this, if you are starting in cell A1, is: =OFFSET($A$1,0,0,COUNTA($A:$A),1) 
Adjusting the column letter and number in the first part of the formula will allow us to change where the named range starts. For example =OFFSET($B$2,0,0,COUNTA($B:$B),1) would start the range in cell B2.
  • Click OK.
  • Repeat steps 2 – 4 to create the second range.
  • Click Close.

Excel 2007 and 2010:

  • Click on the Formulas tab, click Name Manager, and select New.
  • Name the range, we will want two named ranges: one for our labels and one for our data. We will define the names as Label and Data, respectively.
  • From here we will enter formulas to create the dynamic named range. The formula to do this, if you are starting in cell A1, is: =OFFSET($A$1,0,0,COUNTA($A:$A),1)
Adjusting the column letter and number in the first part of the formula will allow us to change where the named range starts. For example =OFFSET($B$2,0,0,COUNTA($B:$B),1) would start the range in cell B2.
  • Click OK.
  • Repeat steps 2 – 4 to create the second range.
  • Click Close.

Now we create our chart.

Excel 2003 or older:

  • Highlight our data and go to Insert menu and choose to insert a chart. Make all the settings that you want to the chart, and place it on the worksheet.
  • Now we apply the dynamic settings to the chart. Click on one of the series in the chart and look at the formula bar.
  • In the formula bar there should be something that looks like =SERIES(Sheet1!$A$1:$A$16,Sheet1!$B$1:$B$16,1). In the spot where it shows Sheet1!$A$1:$A$16 we are going to change that to Sheet1!Label.
  • In the spot where it shows Sheet1!$B$1:$B$16 we are going to change that to Sheet1!Data.
  • You now have a dynamic chart. If you add more data to the bottom of the column, it will automatically add that data to the chart.

Excel 2007 and 2010:

  • Highlight our data and go to Insert tab and choose a chart. Make all the settings that you want to the chart, and place it on the worksheet.
  • Now we apply the dynamic settings to the chart. Click on one of the series in the chart and look at the formula bar.
  • In the formula bar there should be something that looks like =SERIES(Sheet1!$A$1:$A$16,Sheet1!$B$1:$B$16,1). In the spot where it shows Sheet1!$A$1:$A$16 we are going to change that to Sheet1!Label.
  • In the spot where it shows Sheet1!$B$1:$B$16 we are going to change that to Sheet1!Data.
  • You now have a dynamic chart. If you add more data to the bottom of the column, it will automatically add that data to the chart.

Watch a video on this tip for Excel 2003.

Watch a video on this tip for Excel 2007.

Watch a video on this tip for Excel 2010.

Leave a Comment

Your email address will not be published. Required fields are marked *