Part 1 Formatting and Charting
1. Start Excel and open the worksheet from last week about technology in
schools. If you don't have it available, you can download it from Resources in
Etudes.
2. Drag to select cells A1 through F1. Click the Merge and Center button in the
Home tab.
3. Change the title font and enlarge it.
4. Shade the title in a color of your choice. Change the font color as needed
to make it readable against the background color.
5. Select the column labels in row 3 and apply a border.
6. Drag to select cells D3:F9
7. On the Insert tab, click the column button (in Excel 2003, you click the
Chart Wizard button in the toolbar and answer questions in the boxes).
8. Under 3-D Column, choose the first one, 3-D Clustered Column.
9. Drag the chart under the data and resize it.
10. On the Design tab of Chart Tools, click Select Data.
11. Under Horizontal
(Category) Axis Labels, click the Edit button.
12. Drag to select the state names
and click OK. They should appear instead of 1 2 3 4. Click OK to close the
Select Data Source box.
13. Practice changing the Chart Styles from the Design tab.
14. Click the Layout tab. Click Axis title and then Primary Vertical Axis
Title. Choose Vertical Title.
15. Modify the default axis title to say Score.
16. Change the font of the new axis label.
17. Click in a blank area to select the background of the chart box.
18. Click
the Format tab, Format Selection. Practice with the options to add a gradient fill, shadow, etc.
to the chart.
19. Drag to select the state names. Then hold down the Ctrl key on the
keyboard and select the Overall Scores in column C.
20. On the Insert tab and choose a Pie chart.
21. Click the Move Chart button and select New Sheet.
22. On the Chart Tools Layout tab, click Chart Title. Type a title describing the data in
the pie chart.
23. Click the Data Labels button and make selections to display the numbers
with the wedges.
24. Click the tab to return to your data sheet.
25. Click the Office button and then move to Print, then click Print Preview.
26. Click the Page Setup button. On the Header/Footer tab, click Custom Footer.
Insert the date in the center section and click OK.
27. Exit Print Preview.
28. Save the worksheet as Nov 21Tech. Upload to Tasks, Tests and Surveys.
This exercise covers the material in Excel Project 2 in Dozer's Quintessential Guide to Computer Literacy. We will add formulas to a worksheet together and then you will complete some steps on your own.
1. Open the Furniture worksheet from the shared drive. If you are doing this on
your own, you can find in Resources in Etudes.
2. Enter Mon in cell A7. Point to the fill handle and drag down to cell A13 to
put the days of the week into the column.
3. Use Autosum to add up the hours in cell B14.
4. Use the fill handle to copy the formula across columns C through F.
5. Use Autosum to total the hours per day in cell F7.
6. Copy the formula down the column.
7. Enter a formula in cell B15 to calculate the gross pay by multiplying the
rate in B2 (absolute reference) by the total hours. Press the F4 key to make B2
absolute or type $B$2.
8. Copy the formula across the columns.
9. Enter a formula in cell B16 to calculate the taxes to be withheld. Make sure
you use an absolute reference.
10. Copy the formula across the columns.
11. Enter a formula in cell B17 to calculate the net pay.
12. Copy the formula across the columns.
13. Use the AVERAGE function to calculate the average in cell B18.
14. Copy the formula across the columns.
15. Format the numbers in B2 and B15 through F17 as Currency with 2 decimal
places.
16. Format the numbers in B18 through F18 as numbers with no decimal places.
17. Format the tax rate in cell B3 as percent with no decimal places.
18. Make cell B19 the active cell. Open the Insert Function dialog box. Change
the Category box to say All.
19. Choose the COUNTIF function and click OK.
20. Drag to select B7 through B13 (Kevin's hours). Drag the Function Arguments
dialog box out of the way if necessary.
21. In the criteria box, type 0 and click OK.
22. Copy the formula across through column E.
23. View the formulas for the worksheet by pressing Ctrl+~ (tilde). Then turn
formula view off again by pressing the same key combination.
Do the following steps on your own:
24. In column G, add a column label and formulas to calculate the gross payroll
for each day of the week.
25. Format the worksheet to look more attractive by merging and centering the
title and changing alignment, fonts, colors and borders.
26. Save the worksheet as Nov14Furniture. Upload to Tasks, Tests and Surveys.
Additional Notes: Order of Operations
When you type in a formula, Excel doesn't always perform the calculations from left to right. It applies the rules of operator precedence. Here is a list in order, highest to lowest:
| Operator | Description |
|---|---|
| – | Negation (as in –1) |
| % | Percent |
| ^ | Exponentiation |
| * and / | Multiplication and division |
| + and – | Addition and subtraction |
Example: =B3 + B4 / B5
If B3 contains 15, B4 contains 9 and B5 contains 3, you might expect the
result to be 8.
It is NOT. The division will be done first and then the addition, so the result
is 18.
If you put parens around a calculation, you force it to be done first.
=(B3+B4) / B5
The result is 8 because the addition is now done first.
The percent operator is used to make formulas more readable. Instead of typing =G4 * .08.25 for sales tax, you can enter =G4 * 8.25%