3r’eate a Spreadsheet
Stan Excel and create a blank workbook.
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.
Anv Town
Eleimentary School
School Supplies
Distribution
Classroom Supplies
Copy Paper
2 Pencils
Pens
Highlighters
Markers
Crayons
Colored Pencils
Staples
Tissues
Hand Sanitizer
Sept. Sept. Units Cost
4A
29
22
12
35
15
60
12
22
16
Oct. % of Grand Cost Total Cost/Unit
3.99
0.75
4
3.29
4.79
3.1
2.29
1.89
1.49
A
Z
“/” ot
Grand
Total
Oct,
Units
32
12
8
10
22
7
28
4
I
7
I
I
3.
4.
5.
Iv’lerge and center cells 41 through Hi. Format the school name as
Title style.
Merge and center cells A2 through H2. Format the label as
Heading 1 style.
Bold the labels in row 4 and right align the labers above the
numeric values.
- Format the Cost/Unit values as Currency with 2 decimal places.
Your spreadsheet should look like the figure below.
5 r. l:, * * i 5 r r I : ;: i i r: -.; li i : i r: i; i: i I r-: :i
,i Cla:crpom 5uppli*s f o5tfunit 5€p! Unirj 5cpt,fo;t % of Grand Toul ifct fost ?i oI tlrand Totsl
‘r ( al:1’1’-‘.ii:..,
i’ :i::… ,
: r,,,.. |.”‘1,:.
1 : r. i:::tjr-a’a: fl,. i,il
-: \i’r;:i.:’r
- Fi l..i :lL
i I lcl
\ j .,:
i{i
::)
::1
L-:
,.:!
i.l
ort. Lleit,
🙂
!
:r*1
]_j
.:
[An image of the school supplies excel workbook with formatted data.]
- ln 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. - ln cell A16, enter the label “Grand rotal” and format it as bold.
- ln cell D16, enter a formula that uses a function to calculate the
total of the column.
ln 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 0 decimal places.
ln cell E16, enter a formula that uses a function to calculate the
total of the column (it should total 100%).
Repeat the process you used in steps 7-11to calculate values for
October.
Sort the school supplies alphabetically. (Be sure to select cells
AS:H14 to sort the entire row of data.) Your spreadsheet should
look similar to the figure below.
Sr lir i ! lr :: ir : ir -. D i.- t ir i:i:t ii::-r Clisrroorn -iuppliet C6!t/Urrit Sept. Unrts Sepr. Corl 1′; of 6r:nd Tot:i O(i UrritB O.a CDn ;”i of 6and Torai
z7:t..ii;|, il.l_., /,i: ;i,ri.r: 1., :j :i,l- :,:
i”i’,’f!,i ii–t.:i! :;.2’l il ::-rl.l: l;-:; : It iit: 1.’ ii ,
l1:;:.,. iat;i’: :1. -.’l ::i-, !15. rlr l: , l;’ ,i’.17.r-l ,]ii,.,
{.i,.i:-:-i !: :i’ ::. j’ii’ :-i i . :: ij’.. j.-‘ ‘-;
,
iii.r:1.::.i:j.t: !; a:a: li: !11 .r:’ 1 I i:,:.ar-‘ . ..
.. !:,. ,rlflrr ‘:r J,.j ‘,) ‘-irli :a: t, . 1.r il.l .,r,‘ l’, ‘i’ ‘.’ :,.i3,,.;i,.1 ii. !9 –:1, -.’,i1:;’ i:1 ,l;’ i;: ‘!,ii:,.:+ ii’: ;’i:a-r :.i iri.l ij ‘-!iii ::i:r i i’: :.: i:.,t.i i:i
tir:r “.., lii.r–i+ :.r .i.7,r i:ijj : ; ,.i i 1:. arrl “irrl.t,; ii-,’,+ ).1 }ij.fi) .l:: i: ::;.,i1 i::
i-‘1.:j il: :i1a:’:
11.
12.
13.
Grand Toial
[An image of the School Supplies excel workbook with the data sorted in alphabetical
order.l - Save the modified spreadsheet.
: i t3 2.Ctr
Create a Chart - Create a chart by first selecting the cell ranges for the chart. Drag
from cell 44 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.
i+
Coit/Unit spt (> ){,i -11.–l !.: i::r i:i |; i:.:! ..:_ S: -:! S.Ef 6$rndT€id {}rt ! 3?& :8
?lx
6H
4*
5S
t2t6
1!t(
3x
4*
!#&.ndT#td \lt i5s 3?6
3?{,
3X
8g
*5?6
??d
1u2F
}E
‘r Grrnd Totrl
[An image of selected cell ranges in the School Supplies excel workbook.] - lnsert a 3-D Clustered Column chart.
- Change the chart title to read “Cost of Supplies.”
- 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.
.(
l1
-‘i: il;:, .’r
”!j. l”j,:…r!
1i: Grend Total
‘1 ,’
i$
1ti
i;’
lf
l
.l ,’
-:
l1SS.C+
s16&fl*
t1{.{e $r2o.*
3IGS,O $fi.15 569-0, 5,40″F srs.e s+so i. 1:: a.: )i ii],{ 7 i 1 !’rl il ‘;’i ( C:t *f Suppties
l a,:
:i””
1[] ..
I
#
iffir ffir
-f “.”-“. *.S- :sd dp-o
os& +rF
liefl{.ttf }f,J{r-{4J!
[An image of a chart made with the selected cell ranges in the School Supplies Excel
workbook.l - Put the following information into cell A4’1 :
o Your name
o Your email address
o Your student number
” The course name and number
a - Save and close the workbook.
Scoring Guidelines
,
I
,s
,1
“o”.”d
“
Sample Solution
