Generating Graphs and Charts With Excel

1. Start up the Excel Program (available on computers in Hartnell College Library). Open your annual.cgi file by clicking on File, Open, then selecting the location of the file and changing the Files of Type to All Files.

1. Highlight the name of your file and click Open. This will activate the Excel Text Import Wizard. You will see the following screen:

2. Click the Finish button to import the tab delimited data into a spreadsheet:

3. You will notice some of the numbers have been transformed into a series of pound signs. To fix that, you will need to adjust the width of columns A – G. Start by clicking on the A label for the first column.

4. Click the line between the top A label and B label. You will see a black line with two arrows, one pointing left and the other pointing right. Hold down the left mouse button and drag the line to the right until you can see all of the words United States and California.

5. Now do the same thing to widen columns B, C, C, E, F, and G. When you finish, your workbook should look like this:

6. We are now ready to select our data and generate a chart. Select the data for years 2001-2005 for both the United States and California.

7. Once you have the data selected as shown above, click the Insert tab. This will bring up a display that allows you to choose what type of chart you wish to create.

8. Click the Column chart icon. This will provide a drop down menu of all the different types of column charts you can generate. Choose the first 3-D Column chart icon.

9. Once you have selected the 3-D Column chart icon, Excel will reformat the selected data into a column chart and display it on the screen.

10. Notice how the chart legend says Series 1 and Series 2 instead of U.S. and California. Also, the labels along the x axis display the numbers 1-5 rather than the names of the years. We will fix that and add a title and additional labels to our chart. To do this, click the Layout tab.

11. Once you have clicked the Layout tab, you now have several options for reformatting your chart. Start by clicking the Chart Title icon. Select Above Chart. This will create a text box above your new chart.

12. If you click the Chart Title text, you can now replace these words with your own title. Title your chart Personal Income Comparison.

13. Click the Chart Tools tab on the upper right hand side of the display.

14. Once you do this, you will see more formatting options:

15. Click the Select Data icon.

16. This will bring up a display that allows you to change the words Series 1 and Series 2 to United States and California. To do so, highlight Series 1 and click Edit.

17. This opens up another display window which will allow you to either type in what you want the Series name to read for Series 1 in the legend.

18. Click the little red arrow in the box next to the Series name field as shown above. Once you do this, it will bring up a box for you to enter the text you wish to see displayed.

19. Type United States in the box and then click the red x in the right hand corner of the box as shown above.

20. Once you see the new series name appearing, click O.K. Do the same thing for Series 2, renaming it California.

21. Now you need to replace the numbers 1 -5 on the X axis with the names of the years. To do this, highlight the first number under Horizontal (Category) Axis Labels and click the Edit button. Replace 1 with 2001,2002,2003,2004,2005.

22. Click OK. You will see the following display.

23. Click OK.

24. Notice how your chart now has a title and correct information in both the legend and along the X axis. If you wish to move your chart off the data page onto its own workbook page, select the chart by clicking on it. Click on Chart Tools and then click the Move Chart icon. The program will pop up a display window that looks like this:

25. Click the radio button next to New Sheet and rename it Personal Income Comparison, then click OK.

26. Save your Excel workbook file and submit it through e-College.