T-Time Seminar: Creating Charts in Excel

June 10, 2009

Turn Excel data into visual images by creating charts. This is a basic introduction in to how to use Excel to create charts using Excel’s “Chart Wizard”.

Create a simple spreadsheet or use an existing spreadsheet. For this example, we will use the my budget Excel spreadsheet from Lesson 2 of Using Excel. You can use the original my budget file I sent you or the revised (Your Name) my budget file you submitted for Part 2 of the assignment.
Open the Chart Wizard. Open the Insert menu (Alt+i). Then select Charts. For Excel 2007 users, the key command sequence is:
Control+F1

Chart Wizard Step 1 of 4: Chart Type

In the Chart Wizard, you will be placed on Step 1 to select the type of chart you want to create. This is a multi-page dialog box. The Page Tabs are Standard and Custom. In most cases, you will use Excel’s standard chart types. To select this Page Tab, press the Control key and press Tab until you reach the Standard Page Tab.
You are placed in a list of available standard chart types. You can move through the list of different chart types using the Up and Down Arrow keys. Once you have selected the type of chart you want to create, Tab past the series of radio buttons and select the Next button.

For this exercise, we will choose a Column chart using the labels and data from the my budget spreadsheet. Row data is transformed into chart columns, row labels (Rent, Groceries, Utilities, Savings) become the chart legend text, and column labels (January, February, March, April) become the category names at the bottom of the chart.

This is a clustered column chart. Visually, the chart that is created looks like a capital L. The vertical line of the L is the y axis and displays the chart values in increments of $50. The horizontal line of the L is the x axis and displays the chart categories. Each category includes four separate bars. So, for January, you will have a bar for Rent, a bar for Groceries, a bar for Utilities and a bar for Savings.

The bars are vertical starting at the x axis (horizontal line of the L) to the category’s corresponding amount or value on the y axis. For January, you will have four bars all starting at the x axis and moving vertically up to the corresponding amount on the y axis. The Rent bar will go up to $450, the Groceries bar will go up to $217, the Utilities bar will go up to $149 and the Savings bar will go up to $300.

Excel creates a legend, assigning a color for each item being measured in the chart. Following is the legend for our practice chart:

Rent=Blue
Groceries=Purple
Utilities=Yellow
Savings=Green

(Note: Your chart may display different colors.)

We will discuss the steps for creating the chart legend and switching the x and y axis information shortly.

Chart Wizard Step 2 of 4: Chart Source Data

You are now on Step 2 of 4 in the Excel Chart Wizard. This is a multi-page dialog box with the following Page Tabs: Data Range Tab and Series Tab. For the purposes of our exercise, we will only work with the Data Range Tab. Hold down the Control key and press Tab to switch to the Data Range Tab.
The first element in the Data Range dialog box is called Data Range. Type in the range of data you want displayed in your chart. For our example, we will type in A11:E15. You may notice that the name of the budget file appears by default in this field. Go ahead and type over with the selected range and press Tab.
You are now on a series of two radio buttons labeled Columns and Rows. Selecting the Columns radio button creates the chart using the Column labels from the selected spreadsheet as the categories that will appear across the horizontal x axis of the chart. In this example, the categories would be January, February, March and April.

Selecting the Rows radio button creates a chart with the Row labels as the categories across the horizontal x axis of the chart. In this example, the categories would be Rent, Groceries, Utilities and Savings.

Select the Columns radio button by pressing the Up or Down Arrow key until you reach Columns and then press Tab to the Next button and select the button.

Chart Wizard Step 3 of 4: Chart Options

This is the most complicated of the Chart Wizard dialog boxes. The dialog box includes the following six page tabs:

Titles Tab
Access Tab
Gridlines Tab
Legend Tab
Data Labels Tab
Data Table Tab

Each page tab is discussed. To move to a page tab, press and hold down the Control key and press the Tab key until you reach the correct page tab.

Titles Tab

Here you will assign labels to your chart. Press Tab once to move from the Page Tabs and you will be placed on Chart Title: edit box. Type in the title of the chart. For this example, type in My Budget. Press Tab to move to the next field.
The next field is Category (x axis): Type in a label for the categories that will appear across the horizontal axis of the chart. The categories for this example are January, February, March and April. A general label for these categories is Months. After typing in the Categories labels, press Tab once.
You are now on the field Value (y axis): Type in a label that best summarizes the method used for measuring the categories. In this example, the categories are being measured in dollars. Type in the Value label as Amount and press Tab.
Select the Access page tab using Control+Tab.

Access Tab

This page tab allows you to change or remove the x or y axis from your chart. Since you will probably always want to display the data from your spreadsheet, we can ignore this page and move on to the Gridlines page tab.

Gridlines Tab

This page allows you to select gridlines for the chart. Press Tab once from the Page Tabs area and you are placed in the Category (x axis) on a check box Major Gridlines. By default, this check box is not checked. If checked, the line for the horizontal part of the chart would be visible. For this exercise, check this check box by pressing the Spacebar or clicking in the check box.
Press Tab once to move to the Minor Gridlines check box. Again, the check box is not checked. In a column chart, the minor gridlines for the x axis are the vertical lines. For this exercise, check this check box by pressing the Spacebar or clicking in the box. Press Tab once.
The next two check boxes are for the Value or y axis of the chart. Check the check box for Major gridlines and Minor Gridlines in this section.
Minor gridlines can help when visually trying to interpret the chart to determine exactly where the different Categories measure against the Values. For example, in January the value for Groceries is $217. If minor gridlines are selected, it is easier to visually determine the amount because the bar for Groceries will fall somewhere between $200 and $250.
Move to the Legend Tab.

Legend Tab

The Legend for the chart identifies how the different categories are identified. For example, how each of the items for each category are displayed. In this example, colors have been assigned to each of the items in each category (Rent=Blue, Groceries=Purple, Utilities=Yellow, Savings=Green). Press Tab once to move from the Page Tabs in to the Legend page. You are placed on a check box Show Legend. Without a Legend, it would be difficult for someone to understand the table. For this exercise, select the check box to Show Legend by pressing the Spacebar or clicking in the box.
Press Tab once and you will be placed on a series of radio buttons – Top, Right, Left, Bottom and Corner. Selecting one of the radio buttons will determine where the Legend will display on the final chart page. For this exercise, select the Top radio button and then select the Data Labels page tab.

Data Labels Tab

This is a more advanced feature that will be covered in Charts 2. Move to the Data Table page tab.

Data Table Tab

Press Tab once to move in to the Data Table page. The first item is a check box Show data table. By default, this is not checked. Typically, you would not want to display the Excel spreadsheet on the same page as the chart. For this exercise, we will leave this item unchecked.
If you did want to include the Excel spreadsheet on the same page as the chart, you would check this check box by pressing the Spacebar key or clicking in the box. Checking this item causes the page to change and display a new item. Press Tab once and you will be placed on another check box Show Legend keys. If you are displaying the Excel spreadsheet on the same page as the chart, it is recommended that you show legend keys for easier interpretation. Press the Spacebar to check this box or click in the box.
Press Tab to the Next button and select the button.

Chart Wizard Step 4 of 4: Chart Location

In this step, you will determine if the chart you are creating is displayed as an object within the current spreadsheet file or on a separate sheet within the current Excel workbook. Press Tab several times through the page until you reach a series of two radio buttons (As Object and As New Sheet). Press the Up or Down Arrow key to select As New Sheet and press Tab once.
In the Name of New Sheet field, type the name you would like to call the chart. In this example, we will call the new sheet Budget Chart. Press Tab once.
The next field is Sheets in Workbook. This is a combo or drop down box. Press the Down Arrow key to open the combo or drop down box and Arrow to the sheet you want to place the chart on. For this example, we will select Sheet 2. Press Tab to the Finish button and select the button.

My Budget Chart

You are placed back in Excel but are now on a page displaying your newly created chart. If you are using a screen reader such as JAWS or Window-Eyes, following is an interpretation of what you will hear as you Down Arrow through the chart screen.

My Budget (chart title)

Description: Clustered Column. Compares values across categories.

The chart contains 4 distinct sets of vertical columns:

Category (x) axis: Months
Value (y) axis: Amount

set 1 legend: January has 4 columns
column 1 Months=Rent
Amount=450
column 2 Months=Groceries
Amount=217
column 3 Months=Utilities
Amount=149
column 4 Months=Savings
Amount=300

set 2 legend: February has 4 columns
column 1 Months=Rent
Amount=450
column 2 Months=Groceries
Amount=302
column 3 Months=Utilities
Amount=149
column 4 Months=Savings
Amount=300

set 3 legend: March has 4 columns
column 1 Months=Rent
Amount=450
column 2 Months=Groceries
Amount=290
column 3 Months=Utilities
Amount=152
column 4 Months=Savings
Amount=300

set 4 legend: April has 4 columns
column 1 Months=Rent
Amount=450
column 2 Months=Groceries
Amount=315
column 3 Months=Utilities
Amount=158
column 4 Months=Savings
Amount=300

This sheet contains only the chart.
Prior Sheet (allows you to move to the prior sheet or your original budget spreadsheet)

Congratulations! You have successfully completed a visual chart in Excel. Stay tuned for the next T-Time seminar on Creating Charts 2. You will receive an e-mail announcing the date of the next T-Time seminar.

If you have any questions regarding the above steps, please contact me by e-mail at: salmon@hadley.edu, or by telephone at (800) 323-4238.