Tutorial on graphics
The purposes for this tutorial are the following:
• discover and efficiently organize technical information
• use appropriate software (Word and Excel) to produce tech docs • apply principles of doc design to create data displays
Creating tables and graphics in Excel
Open the file widgets.xlsx; then, save your file as widgets_y3i.xlsx, replacing the y3i with your three initials. As you continue through the instructions, save your document frequently.
Modify the material in the file to produce a table, a pie graph, and a line graph.
Creating a table in Excel
Change the typeface from Arial 10 point to Calibri 10 point: 1. Select all type (highlight with mouse or press Ctrl-A). 2. In the font section of the ribbon, select Calibri 10 point.
Adjust the width of the columns so that the first column is 18 pixels wide and the other columns are 10 pixels wide.
1. Right click the column head(s) or select the columns and on the Home tab ribbon, click Format > Column width.
2. Enter the value in the Column width dialog box.
Use Find and Replace (Ctrl-H) to replace all instances of quarter with qtr. Format the column heads:
1. To bold the column heads, select all the column heads (A4–F4), click Ctrl-B.
2. To right align the column heads, select column heads B4–F4, click the right align icon in the Alignment area of the Home tab ribbon.
Add total sales for each salesperson:
1. Click cell F5; then, in the Editing area of the Home tab ribbon, click Σ (sum). Press Enter (to accept the formula (=SUM(B5:E5).
2. To copy the formula down to the cells below, click cell F5 and highlight down to F8; then, press Ctrl-D.
Add total sales for each quarter and for the entire year.
1. Click cell B9; then in the Editing area of the Home tab ribbon, click Σ (sum). Press Enter (to accept the formula [=SUM(B5:B8)].
2. To copy the formula across to the cells to the right, click cell B9 and highlight across to F9; then, press Ctrl-R.
Add the average (mean) sales for each quarter:
1. Click cell B10; then, in the Editing area of the Home tab ribbon, click Σ drop-down menu and select Average. Change the formula from =AVERAGE(B5:B9) to =AVERAGE(B5:B8); then, press Enter.
2. Format cell B10 so that it displays integers (not decimals) by clicking the Decrease Decimal icon twice in the Number section of the Home Tab ribbon.
3. To copy the formula across to the cells to the right, click cell B10 and highlight across to F10; then, press Ctrl-R.
Insert horizontal rules in your table:
1. Highlight cells A4–F4; then, in the Font area of Home tab ribbon, select the Borders icon and click first the Top border and then the bottom border.
2. Highlight cells A10–F10; then, in the Font area of Home tab ribbon, select the Borders icon and click the bottom border.
3. Highlight cells A8–F8; then, in the Font area of Home tab ribbon, select the Borders icon and click the bottom border.
Use white space to increase contrast (space between rows) in your table:
1. Right-click row 9; from the quick menu, select row height, and in the Row height dialog box, type 18 and click OK.
2. Right-click row 3; from the quick menu, select row height, and in the Row height dialog box, type 18 and click OK.
Modify the chart title:
1. Click cell A3; then, to bold the title, press Ctrl-B.
2. Click cell A3; then, to increase the type size, in the font area of the Home tab ribbon, type 12 for the font size.
Creating a pie graph in Excel
Create a pie graph that shows the percent of total widgets sold by salesperson.
Begin by modifying (sorting) your table so that the sections (pieces of pie) in the chart will move from largest (at the 12 o’clock position) to smallest.
1. Highlight the data to sort, in this case cells A5 to F8 (dragging from top left to lower right). 2. In the Editing section of the Home tab ribbon, click the Sort & filter drop-down menu. Select
Custom sort. (Note that the My data has headers check box is selected.)
3. From the Sort by menu, select Total sales, and from the Order menu select Largest to smallest; then click OK.
Select the data for the (pie) graph and the kind of graph:
1. Highlight the total sales form each salesperson (cells F5–F8).
2. Press Ctrl and highlight the name of each salesperson (cells A5–A8) so that 8 cells are now highlighted.
3. Click the Insert tab; then, from the graphs section, select Pie graph, and from the drop-down menu, select 2-D Pie (the first option).
4. Drag your graph to an open area of your spreadsheet (in this case, to the right of your table). Modify your entire pie graph:
1. From the Quick Layout drop-down menu, select Layout 1, which shows the name and % of sales. 2. Remove the chart title by clicking it and pressing delete.
Modify individual the placement, color, and pattern of individual slices of your pie graph: 1. Click on the pie graph.
The handles appear around the edges of the graph. 2. Click on a piece of the pie graph.
The handles appear around that piece of the graph. 2.1 Drag the piece of pie out to highlight it.
2.2 Right click the piece of pie; then, select Format data point. In the Format data point window, click the Fill & line button to apply a pattern, to change the background color (behind the pattern), and to add a border to the slice.
Creating a line graph in Excel
Select the data for the (line) graph and the kind of graph:
1. Highlight the names of the salespeople and all of the data for the four quarters (cells A5–E8). 2. Click the Insert tab; then, from the graphs section, select Line or area chart, and from the
drop-down menu, select 2-D Line (the first option).
3. Drag your graph to an open area of your spreadsheet (in this case, below your table). Modify your line graph:
1. From the Quick Layout drop-down menu, select Layout 6, which shows the name and value for the last data point (the fourth quarter).
2. Click each salesperson’s name 3x; then, delete the comma and data value. 3. Remove the chart title by clicking it and pressing delete.
4. Click the Y axis label; then, modify the text to read Number of widgets sold (without the italics). 5. Click the X axis values (1–4) so that a text box appears around the quarter numbers; then, in the Chart tools > Designs ribbon, click Select data, highlight B4–E4 (so that those cells appear in the Axis labels dialog box), click OK, and click OK again.
Modify individual the placement, color, and pattern of individual slices of your line graph: 1. Click on a line.
The data points appear on the line and the Format data series window appears.
2. In the Format data series window, click Dash type to select the type of line (using Round dot for one line and Dashed for another) and click color to change the color of the lines.
Save your file.
Creating a Word document using our memo template
Use your memo template (Memo_y3i.dotx) to create a new memo. 1. Open your template.
2. Add the subject line Widgets exercise and in parentheses the filename widgets_y3i.docx, replacing the y3i with your initials (without the italics).
3. Insert the following text (without quotation marks) in the first paragraph of your memo: “The year 2020 was an excellent year for widget sales. All of our salespersons had banner years.”
4. Press Enter (so that you are now using Memo text style); then, insert Greeked (dummy) text to fill out the rest of your document:
4.1 Open a browser and type greeked text (without the italics) in the search box. 4.2 Select Lorem Ipsum – Generator, Origins and Meaning.
4.3 Generate 3 paragraphs, click generate; then, click copy.
5. Return to your memo, place your cursor on the line (paragraph) following your introduction; then, from the Home tab select Paste > Merge formatting to insert your three paragraphs of Greeked text (all formatted as Memo text style).
Your text will flow onto a second page. 6. Create a new style:
6.1 Name the style Memo text greeked. 6.2 Click Format; then, select Language.
6.3 Click (select) the Do not check spelling or grammar check box; then click OK, and, again, click OK.
Save your file.
Inserting a table and graphs (from Excel) into the memo
Copy the table and graphs from your Excel document into your Word document by using three different methods of copying and pasting.
Copying a table (using the clipboard)
Move to your Excel document, and copy the table: 1. Highlight cells A3–F10.
2. Press Ctrl-C to copy the table (to the clipboard). Move to your Word document, and insert the table:
1. Insert your cursor at the beginning of the second paragraph (which is the first paragraph of Greeked text).
2. Press Ctrl-V to paste in the table.
The table will appear flush with the left margin (not the left margin of your Memo text). 3. Align the table with the text of your memo:
3.1 Right-click the plus sign/4-directional arrow to the upper left of your table. 3.2 Select Table properties.
3.3 Indent the table 1” from the left margin, and click OK.
Copying a pie graph (using Paste > Keep source formatting & embed workbook)
Move to your Excel document, and copy the pie graph: 1. Press Esc to deselect the table.
2. Click on the white space within the pie graph to activate the handles around the pie graph; then, press Ctrl-C to copy the graph.
Move to your Word document to insert the graph:
1. Insert your cursor at the beginning of the second paragraph (which is the first paragraph of Greeked text).
2. On the Home tab, Click Paste > Keep source formatting & embed workbook. 3. Above the tabs, click Chart tools > Format.
4. In the Arrange section of the ribbon, click Wrap text > Tight. 5. In the Size section of the ribbon, click the Diagonal arrow.
The Layout dialog box appears.
6. Click the Lock aspect ratio check box, and in the (Absolute) Width box, type 3 (inches); then, click OK.
7. Drag your graphic down so that one line of text appears above the graphic and to the right so that it is flush with the right margin.
8. Delete text so that only one full line in the second paragraph extends below the pie graph.
Copying a line graph (using Paste > Keep source formatting & embed workbook)
1. Press Esc to deselect the pie graph.
2. Click on the white space within the line graph to activate the handles around the graph; then, press Ctrl-C to copy the graph.
Move to your Word document to insert the graph:
1. Insert your cursor at the beginning of the third paragraph (which is the second paragraph of Greeked text).
2. On the Home tab, Click Paste > Keep source formatting & embed workbook. 3. Above the tabs, click Chart tools > Format.
4. In the Arrange section of the ribbon, click Wrap text > Tight. 5. In the Size section of the ribbon, click the Diagonal arrow.
The Layout dialog box appears.
6. Click the Lock aspect ratio check box, and in the (Absolute) Width box, type 3.5 (inches); then, click OK.
7. Drag your graphic up so that it fits on the first page and so that it is flush with the left margin. 8. Delete text so that your document consists of a single page.
Modifying the layout of graphics (in Word) and inserting a caption
Modify the size of the pie graph and add a caption.1. Click the pie graph so that the graphic handles appear. 2. Click Chart tools > Format.
3. In the Size area of the ribbon, type 2.8 for Width.
4. Drag the graphic up to that it replaces a portion of the first line of the paragraph. 5. Right-click the pie graph; then select Insert caption:
5.1 In the Caption text box, type in % of total sales by salesperson; then click OK. 5.2 Edit the caption by changing the style (in style window).
5.3 Edit the caption by modifying the text in the caption text box.
Delete any lines which appear on a second page so that your document is only one page; then, save your file.
Modifying data in Excel and Word
Modify the table in your spreadsheet:1. In the table, add 10 to each of Ole’s quarterly sales.
The pie and line graphs in Excel change to show the changed data. 2. Save your Excel document.
View the changes in the Word document:
1. Note that the table and pie graph did not change; however, the line graph which contains a link to the Excel document does change.
2. Save your Word document.
Final instructions
Submit both files (widgets_y3i.docx and widgets_y3i.xlsx) to the appropriate folder to D2L Assessments > Assignments.
