Loading...
 

 

Microsoft Office 2007 Excel Graphics Tutorial,

 

Level I

 


Other Microsoft Excel 2007 tutorials: Other spreadsheet tutorials:
Microsoft Excel 2007 Calculating Marks Microsoft Excel 2010 Calculating Marks
  Microsoft Excel 2010 Graphics

 

Microsoft Excel 2007, among many other functions, allows you to manage numerical data, do calculations, and present the results in ways that are easily integrated into documents and presentations. These include both tables and graphics. Since translators very often work with these kinds of documents, they may often need to open and edit these graphics.

 

 

I. Introduction


Excel is a spreadsheet program that allows you to use your data to produce a variety of types of graphics for presentations, articles, theses, and other publications. It can help you to manage a large amount of data effectively and present it so that it can be understood quickly and clearly. Using Excel, you can create cross-references within and between sheets to do calculations based on data, and also group and present data in different ways. To learn more about these functions, see the Excel Calculating Marks, Level I tutorial.

 

Excel also offers advantages for managing data for graphics: if you edit data in an Excel spreadsheet after you have generated a graphic from it, the graphic will be updated automatically to reflect the changes. As you can imagine, this can be a huge time-saver!

 

You can learn more about Excel in the Help files, which you can access by clicking the Help button that appears in the top right-hand corner of the Excel screen, or on the Microsoft site at http://office.microsoft.com/en-us/help/default.aspx

 

II. Getting ready


  1. Open Excel (Start > Microsoft Office Excel 2007). A blank spreadsheet file opens automatically. This file contains three worksheets that you can access through the tabs along the bottom of the screen. You can enter data on one or all of these sheets and save them all together in one file. (See note 1)

 

III. Adding data to a spreadsheet


  1. Enter the data from the table below (which lists fictional numbers of graduates from the various STI programs in recent years and the projections for the future) into your spreadsheet, exploring three ways of entering the data. 

 

Table 1:  Past graduates from STI programs and projections for future years
Year B.A. graduates

Accelerated

B.A. graduates

M.A. graduates PhD graduates
1990 50 15 10 2
2000 55 25 7 3
2010 62 25 15 4
2020 100 35 20 8

 

  1. Start by typing the title Year from the first cell in the table into the first cell of the spreadsheet.
    1. Click on the cell in the top left-hand corner of the Excel spreadsheet and type Year. To move on to the next cell, press the Tab key near the top left-hand corner of the keyboard.
    2. To make the title bold, as it appeared in the Word table, click on the cell to select it, and press Ctrl + B on the keyboard.
  2. Now try copying and pasting data from multiple cells into the spreadsheet:
    1. Use your mouse to select the rest of the column titles from the table, and then press Ctrl + C on the keyboard to copy the contents.
    2. Place your cursor in the next cell of the first line of the spreadsheet, and press Ctrl + V on the keyboard to paste the contents of the cells into the spreadsheet. The data is automatically entered in separate cells, as it appeared in the table.
    3. To adjust the width of the columns to display the titles properly, simply place your cursor over the dividing line between columns in the top (shaded) edge of the spreadsheet, which displays the column labels (A, B, C, …), and click and drag the borders until you are happy with the width of the columns.
  3. Finally, copy and paste the remaining data into the spreadsheet all at once, by copying all the cells, placing your cursor in the top left-hand corner of the block of empty cells, and pasting the data you copied. You’ll notice that once again, the data is automatically separated into separate rows and columns.

You now have all of the data from the table copied into a spreadsheet.

  1. Add one more column to the spreadsheet, to display the total number of students in the STI programs in a given year.
    1. In the first line of the spreadsheet, place your cursor in the cell to the right of the PhD graduates column, and type Total graduates. You’ll notice that Excel automatically makes the cell bold to match the formatting of the other cells beside it.
    2. Place your cursor in the cell under this new column heading, in the row with the data from 1990. From the Home tab in Excel, click the AutoSum button that appears near the right-hand side.
    3. Excel automatically “guesses” which cells contain the numbers you want to add. Click and drag with your mouse to select all of the cells in the row for 1990 that contain numbers of graduates, but not the year (which was automatically included by Excel). Then press the Enter key on the keyboard to select this new block of cells. The total number of graduates in all programs in 1990 is automatically displayed in the cell. (See note 2)
    4. Copy and paste this formula into the remaining cells in this column, to calculate the numbers of graduates for 2000, 2010 and 2020.
      1. Click on the cell that contains the total for 1990 to select it.
      2. Press Ctrl + C on your keyboard to copy the formula.
      3. Select all three of the remaining cells in this column by clicking and dragging with your mouse.
      4. Press Ctrl + V on your keyboard to paste the formula into each of these cells all at once.

 

IV. Generate a graph from spreadsheet data


 

  1. You first need to decide what data and how much of it you want to display in your graph. You can choose to create a graphic that will display all of the data, or just a selection of it. For the first graph, you will use just the data for 1990, excluding the total number of graduates.
    1. Click and drag with your mouse to select all of the cells containing numbers of graduates for 1990.
  2. You next need to decide what you want to represent in your graph, and how you want to display it. Excel offers a number of possibilities depending on what you want to represent in your graph:
    1. A pie chart allows you to represent the parts of a whole or the proportions of different types of members in a group. For example, this type of chart would be appropriate for identifying what proportions of the STI graduates in a given year came from each program.
    2.  A line graph is particularly useful for graphing changes over time to reveal overall trends. For example, this type of graph would be appropriate for tracking the variation in the numbers of students in the various STI programs between 1990 and 2020. In Excel, you can graph a single series of values (e.g. the number of graduates from a single program), or multiple series (e.g. the number of graduates from all of the programs).
    3. A column graph is a relatively general representation that allows you to compare values in different categories. As with a line graph, you can graph a single series of values (e.g. the numbers of graduates from each program in a single year) or a number of series at once (e.g. the numbers of graduates from each program for each of the years). (A bar graph produces a similar display, but on a horizontal rather than vertical axis.)

In this case, we will begin by creating a pie chart to represent the proportions of graduates from each of the programs.

  1. With the cells containing the 1990 data still selected, click the Pie icon on the Insert tab.
    1. A range of options for formatting the pie chart appears in a drop-down menu. Select the type of chart that you wish to create (e.g. in 2D or 3D).
    2. The Design tab is automatically displayed at the top of the Excel screen, and a pie chart automatically appears in the spreadsheet. Since this is less convenient than having the chart on a separate sheet, click the Move chart icon that appears at the right-hand side of the Design tab.
    3. From the dialogue box that appears, click the New sheet radio button to select it, and Type 1990 grads in the field that appears to its right.
    4. Click the OK button to close the dialogue box. The chart is now displayed on a new worksheet labeled 1990 grads. You’ll see this name at the bottom of the Excel screen, beside the other worksheets.
  1. You’ll notice that in the key that is automatically created for the graph, each of the values is indicated by default using a number. Obviously, this isn’t as clear as having the data labeled using the column headings. Add the headings to the key of the graph.
    1. On the Design tab, click the Select data icon. The Select Data Source dialogue box icon appears.
    2. At the right-hand side of the dialogue box, you will see the Horizontal (Category) Axis Labels pane. Click the Edit button to adjust the data labels.
    3. You will be directed back to your worksheet, with new dialogue box appearing over it. This box will allow you to select the data labels you want to use from the spreadsheet.
    4. Click the tab for the worksheet where you stored your data, and click and drag with your mouse to select the column headings from the table that correspond to the cells you selected to display in the graph.
    5. Once the cells have been selected, press the Enter key on the keyboard to confirm the selection.
    6. Back in the chart, you will see that the column headings now appear in the key. Compare the graph to the data in the table quickly to confirm that the labeling is correct.
  1. Now try creating a line graph that will allow you to track the changes in the numbers of students in each program from 1990 to 2020.
    1. Repeat the steps you used to create the pie chart above, but this time select all of the values for each program (except the totals) from 1990 to 2020 and click the Line icon on the Insert tab. Choose the format you want for your line graph (e.g. with or without points on the lines, in 2D or 3D).
    2.  As above, move the new graph that appears to a new worksheet, naming it Trends.
    3. You’ll note that the graph shows a steady decrease from left to right, while the data in the table shows that the numbers of graduates increase through the years. By default, Excel has created a line for each of the years, with the number of students represented along the vertical axis and the programs along the horizontal axis (with the B.A. on the left and the PhD on the right). (You can see the scale automatically generated along the vertical axis to display the number of students.) To properly display the evolution of the numbers of graduates over time, you will need to reverse this presentation, so that there is a line for each program and the years are displayed along the horizontal axis.
    4. Click the Switch row/column button on the Design tab. The graph now displays a steady increase in the numbers of graduates from each program over the years (with 1990 on the left and 2020 on the right)
    5. As above, add the data labels from the table to the graph. Start by adding the labels for the years as you added the labels for the programs in the pie chart above, this time selecting the cells that contain the years as your data sources. The years now appear along the horizontal axis of the graph.
    6. Now you are ready to add the labels for each of the programs. In the left-hand side of the Source data dialogue box, select the data for the first program, Series 1. Click the Edit button to edit its properties.
    7. In the Edit series dialogue box that appears, the Series name field is empty. Beside it appears a Select range button that will allow you to identify the label for the series. Click the Select range button and then click on the tab for the worksheet where you stored your data, and find the label for the first data series, B.A. graduates.
    8. Click on the cell to select it, press the Enter button on the keyboard to confirm your selection, and then click the OK button to confirm again and close the Edit selection dialogue box. The label appears in the series list and in the chart’s key. (See note 4)
    9. Repeat the steps above to label the lines for the remaining programs.
  2. Add another line to the graph to display the variation in the total number of graduates in each year.
    1. With the Trends graph displayed, click the Select data icon on the Design tab.
    2. In the dialogue box that appears, click the Add button.
    3. In the Edit source dialogue box that appears, click the Select range button beside the Series values field. Select the cells that display the total number of graduates in each year and press the Enter key on the keyboard.
    4. Click the Select range button beside the Series name field. Select the cell that displays the heading Total graduates and press the Enter key on the keyboard.
    5. Click the OK button to confirm your selection. A new line appears in the trends graph, and it is labeled Total graduates in the graph’s key.
  3. Now create a column graph that displays the numbers of graduates in each program in each year, to allow all of these data to be compared.
    1. Repeat the steps you used to create the line graph above, but this time click the Column icon on the Insert tab. Choose the format you want for your column graph (e.g. in 2D or 3D).
    2. As above, move the new graph that appears to a new sheet, naming it Grad comparison.
    3.  Add the labels to the axes as you did above for the line graph. (In this case, you don’t need to adjust the layout of the graph.)
    4. Check the graph against the table to ensure that the labels are correct. 

 

IV. Adjust the appearance of your graph


 

Excel 2007 makes it easy to adjust the layout, colour scheme and other characteristics of your graphs. The functions used are found on the Design and Layout tabs.

  1. Change the colour scheme of your column graph.
    1. Click on the Columns tab to display the column graph.
    2. On the Design tab, find the Chart styles section and click the button in the bottom right-hand corner of the section to display all of the available options.
    3. Choose a colour scheme that you like and click on the corresponding button to apply it to your graph.
  2. Change the layout of your graph so that a title appears at the top of the graph, and the key appears underneath the graph.
    1. On the Design tab, find the Chart layouts section and click the button in the bottom right-hand corner of the section to display all of the available options.
    2. Find the option that matches the format described above, and click on the corresponding button to apply it to your graph.
    3. Update the chart title: double-click on Chart title where it appears at the top of the graph and type STI graduates 1990-2020.
  3. Add labels to the axes of the column graph.
    1. On the Layout tab, find the Labels section.
    2. Click the Axis titles button and choose the Primary Horizontal Axis Title option from the menu that appears.
    3. Choose the Title below axis option. The label Axis title appears below the axis.
    4. Double-click on this label and replace it with the label Years.
    5. Repeat these steps to add a label to the vertical axis, choosing the Vertical title option and labeling it Number of graduates.
  4. Add labels for the values represented by each column.
    1. On the Layout tab, find the Labels section.
    2. Click the Data labels button.
    3. Choose the Show option. The number of graduates appears at the top of each of the columns. (See note 5)

 

IV. Copy your graph into a document


 

  1. Open Microsoft Word (Start > Microsoft Office Word 2007).
  2. A blank document opens automatically. (You can also open a existing document (Office button > Open) if you prefer.)
  3. In Excel, select your graph for copying by clicking on it.
  4. Press Ctrl + C on the keyboard.
  5. Back in Word, place the cursor at the place in the document where you’d like to insert the graph.
  6. Press Ctrl + V on the keyboard. The graph is pasted into the document. 

 

VII. Wrapping up


  1. To make a copy of your files as a backup or to transfer them to another computer, copy them to a USB key, or if it they are less than 2 MB in size, send a copy as an attachment to your e-mail. 

 

 NOTE 1: If you want to create more sheets, you can do so by clicking the New Worksheet button that appears to the right of the third sheet that appears. If you want to delete an existing sheet, you can right-click on the tab for the sheet you want to delete and choose the Delete option from the contextual menu that appears.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Note 2: You may notice a green triangle that appears in the top left-hand corner of the cell containing the total. This is a warning that the formula entered does not include the cell next to the ones you included, which appears to Excel to contain similar data. Since in this case that cell contains the year and not a number of students, this warning can be safely ignored.
 
NOTE 3: You’ll note that the totals are not the same in all cells. This is because it is not the number displayed in the first cell that was copied, but rather the formula used to calculate it. Excel automatically updates this formula to include the cells in each of the rows below, so that you are counting the right numbers of students in the total for each year. If you update the numbers of students for a given year, the total will also be updated automatically. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

NOTE 4: An alternative to selecting labels from the spreadsheet is to simply type the name you want to give the series in the Series name dialogue box. The advantage of linking the name to the worksheet is that if you update this information in the data, it will also be updated automatically in the graph; you won’t have to correct the graph (and any others you have created using the same data) manually. However, if the labels that appear in the table are too long or otherwise unsuitable for use in the graph, you can type your preferred version in the field and update them manually as needed. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

NOTE 5: The features on this tab also allow you to insert, edit or remove chart titles, adjust the placement of the graph’s key and make a number of other types of changes to the format of the graph. You can explore these independently to learn more about other options in changing the appearance of Excel graphs. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

VI. Questions for reflection


  • As you did these exercises, what did you notice about how Excel works?
  • What could Excel help a translator to do? In what kind of situation?
  • What criteria can be used to evaluate spreadsheet programs and their graphics functions?
  • How does Excel compare to others in the same class?
  • What are some of the advantages and disadvantages of using Excel to manage data and create graphics? Compared to a manual approach? Compared to using another tool?

 

 

Tutorial developed by Elizabeth Marshman. (2011-02-28)