This exercise covers the material in Excel Projects 2 and 5 in Dozer's Quintessential Guide to Computer Literacy.
Part 1 Functions and Charting
1. Start Excel and open the worksheet from last week for Conner Construction
Company. If you don't have it available, you can download it from Resources in
Etudes.
2. Insert a new row after the Paint row.
3. Type the label Total
4. Click in cell B7 and click the AutoSum button in the Home tab.
5. Make sure the range is B3:B6 and press Enter or click the Enter button.
6. Use the fill handle to copy the SUM function across the columns.
7. Click in cell F3 under the AVERAGE label.
8. Click the Insert Function button (the fx)
9. Select the AVERAGE function and click OK
10. Make sure the Number1 box contains the range B3:E3 and click OK
11. Point to the fill handle and copy the formula down the rows.
12. Select the labels and data for each month and item (not the title, average
or totals)
13. Click Column on the Insert Tab
14. Choose a column chart you like.
15. Drag the chart under the data and resize it so it looks nice.
16. Practice changing the Chart Styles from the Design tab.
17. Click the Layout tab. Click Axis titles and then Primary Vertical Axis
Title. Choose Vertical Title.
18. Modify the default axis title to say Thousands.
19. Change the font of the new axis label.
20. Click in a blank area to select the background of the chart box.
21. Click
the Format tab, Format Selection. Practice with the options to add a gradient fill, shadow, etc.
to the chart.
22. Drag to select the row labels (item names) in the data grid. Then hold down the Ctrl key on the
keyboard and select the averages.
23. On the Insert tab and choose a Pie chart.
24. Click the Move Chart button and select New Sheet.
25. On the Chart Tools Layout tab, click Chart Title. Type a title describing the data in
the pie chart.
26. Click the Data Labels button and make selections to display the numbers
with the wedges.
27. Click the tab to return to your data sheet.
28. Click the Office button and then move to Print, then click Print Preview.
29. Click the Page Setup button. On the Header/Footer tab, click Custom Footer.
Insert the date in the center section and click OK.
30. Exit Print Preview.
31. Save the worksheet as May14charts. Upload to Assignments, Tests and Surveys.
1. Open the Furniture worksheet from Resources in Etudes.
2. Use AutoSum to add up the hours in cell B14.
3. Use the fill handle to copy the formula across columns C through E.
4. Enter a formula in cell B15 to calculate the gross pay by multiplying the
rate in B2 (absolute reference) by the total hours in B14. Press the F4 key to make B2
absolute or type $B$2.
5. Copy the formula for gross pay across the columns.
6. Enter a formula in cell B16 to calculate the taxes to be withheld.
Multiply the tax rate by the gross pay. Make sure
you use an absolute reference for the tax rate.
7. Copy the formula across the columns.
8. Enter a formula in cell B17 to calculate the net pay.
9. Copy the formula across the columns.
10. Format the numbers in B2 and B15 through
E17 as Currency with 2 decimal
places.
11. Format the tax rate in cell B3 as percent with no decimal places.
12. Make cell B18 the active cell. Click the Insert Function button. Change
the Category box to say All.
13. Choose the COUNTIF function and click OK.
14. Drag to select B7 through B13 (Kevin's hours). Drag the Function Arguments
dialog box out of the way if necessary.
15. In the criteria box, type 0 and click OK.
16. Copy the formula across through column E.
17. View the formulas for the worksheet by pressing Ctrl+~ (tilde). Then turn
formula view off again by pressing the same key combination.
18. Format the worksheet to look more attractive by merging and centering the
title and changing alignment, fonts, colors and borders.
19. Save the worksheet as May14formulas. Upload to Assignments, 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 parenthesis 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 * .0825 for sales tax, you can enter =G4 * 8.25%