1.)Start Excel and create a blank workbook.
2. Enter the data shown in the following table and then
format, sizing columns as necessary and right-aligning
all number values. Save your file, naming it School Supplies.
3. Merge and center cells A1 through H1. Format the
school name as Title style.
4. Merge and center cells A2 through H2. Format the label
as Heading 1 style.
5. Bold the labels in row 4 and right align the labels above
the numeric values.
6. Format the Cost/Unit values as Currency with 2 decimal place
7. In column D, enter appropriate formulas to calculate the
September costs for each school supply. Your formulas
must use cell references. Format the calculated values
as Currency with 2 decimal places
8. In cell A16, enter the label "Grand Total" and format it as
bold.
9. In cell D16, enter a formula that uses a function to
calculate the total of the column.
10. In column E, enter formulas that calculate the
percentage of the grand total for each type of school
supply (total cost of each item divided by the grand
total). Your formulas must use appropriate absolute cell
references. Format the calculated values as Percentage
with O decimal places.
11. In cell E16, enter a formula that uses a function to
calculate the total of the column (it should total 100%).
12. Repeat the process you used in steps 7-11 to calculate
values for October.
13. Sort the school supplies alphabetically. (Be sure to
select cells A5:H14 to sort the entire row of data.) Your
spreadsheet should look similar to the figure below 10. In column E, enter formulas that calculate the
percentage of the grand total for each type of school
supply (total cost of each item divided by the grand
total). Your formulas must use appropriate absolute cell
references. Format the calculated values as Percentage
with O decimal places.
11. In cell E16, enter a formula that uses a function to
calculate the total of the column (it should total 100%).
12. Repeat the process you used in steps 7-11 to calculate
values for October.
13. Sort the school supplies alphabetically. (Be sure to
select cells A5:H14 to sort the entire row of data.) Your
spreadsheet should look similar to the figure below
E
Any Town Elementary School
School Supplies Distribution
Classroom Supplies Cost/Unit Sept. Units Sept. Cost % of Grand Total Oct. Units
Oct. Cost % of Grand Total
#2 Pencils
$0.75
29
$21.75
3%
6 Colored Pencils
$2.29
60
$137.40
18%
Copy Paper
$3.99
40
$159.60
21%
12
28
32
$9.00
2%
$64.12
15%
$127.68
30%
Crayons
$3.10
9
Hand Sanitizer
$2.00
10 Highlighters
$3.29
11
Markers
$4.79
12 Pens
$4.00
13 Staples
$1.89
14 Tissues
$1.49
15
16
12
35
22
12
22
$46.50
6%
$21.70
5%
$32.00
4%
$14.00
3%
$39.48
5%
$167.65
22%
10
22
$32.90
8%
$105.38
25%
$88.00
12%
$32.00
7%
$22.68
3%
$7.56
2%
$32.78
4%
$13.41
3%
15
16 Grand Total
$747.84
100%
$427.75
100%
14. Save the modified spreadsheet.
Create a Chart
1. Create a chart by first selecting the cell ranges for the
chart. Drag from cell A4 to A14 to select the range and
then press and hold the Ctrl key while you drag from
cell D4 to D14. Continue to press and hold the Ctr
while dragging from cell G4 to G14. Your selected. chart. Drag from cell A4 to A14 to select the range and
then press and hold the Ctrl key while you drag from
cell D4 to D14. Continue to press and hold the Ctrl key
while dragging from cell G4 to G14. Your selected cells
should look like the figure below.
Any Town Elementary School
School Supplies Distribution
Classroom Supplies
Cost/Unit Sept. Units Sept. Cost % of Grand Total Oct. Units Oct. Cost % of Grand Total
#2 Pencils
50.75
Colored Pencils
$2.29
Copy Paper
$3.99
Crayons
$3.10
Hand Sanitizer
$2.00
10 Highlighters
$3.29
11 Markers
$4.79
12 Pens
$4.00
13 Staples
$1.89
14 Tissues
$1.49
29
60
40
15
16
12
35
22
12
22
$21.75
3%
12
$9.00
S137.40
18%
28
$64.12
$159.60
21%
32
$127.68
$46.50
6%
$21.70
$32.00
4%
$14.00
$39.48
5%
$167.65
22%
10
22
$32.90
$105.38
$88.00
12%
$32.00
$22.68
3%
$7.56
$32.78
4%
9
$13.41
2%
15%
30%
5%
3%
8%
25%
7%
2%
3%
15
16 Grand Total
$747.84
100%
5427.75
100%
2. Insert a 3-D Clustered Column chart.
3. Change the chart title to read "Cost of Supplies."
4. Position the chart with the upper-left corner in cell A18
and then size the chart until it extends to column H, as
shown in the figure below.
C
13 Staples
14 Tissues
15
16 Grand Total
18
19
20
21
22
23
24
25
$180.00
$160.00
$140.00
$120.00
26
27
28
29
30
31
32
33
34
35
36
37
38
39
An
$1.89
$1.49
12
22
$22.68
$32.78
3%
4%
$7.56
$13.41
2%
3%
$747.84
100%
$427.75
100%