Excel
Jan-
June
Life
’
s
A
nimal Shelter
–June
Expenses
January
June
$
2
,8
5
0 $
3
,750 $
4
,235
$
1
,275
$
6
,195
$780
$1,050 $1,050 $1,050 $1,050 $1,050
Apr Cash
Life’
s Animal Shelter
1
2
3
4
5
6
A
B
Expense Categories
January
Pet Nutrition
Veterninary Supplies
$825
Life
’
s Animal Shelter
January
–June
Expenses
Excel – Chapter 3 – Creating and Editing Charts
Guided Project 3-1
Life’s Ani
m
al Shelter (LAS) is an animal care and adoption agency that accepts unwanted and
abandoned domestic animals. For this project, you create expense charts and insert sparklines to help
the agency track expenses for the first six months of the year. You also build a waterfall chart to graph
cash flow for a month.
[Student Learning Outcomes 3.1, 3.2, 3.3, 3.4, 3.5, 3.6, 3.7, 3.8]
DO NOT use the files below if you are completing an autograded SIMnet Project. Using
the linked files below will prevent you from uploading and submitting your work in
SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class
assignment.
File Needed: LAS-03.xlsx (/content/assets/InPractice2016/ex16_ip/documents/LAS-03.xlsx)
[Student data files are available in the Library of your SIMnet account.]
Completed Project File Name: [your initials] Excel 3-1.xlsx
Skills Covered in This Project
Create a chart object.
Create a chart sheet.
Apply Quick Layout and chart styles.
Add and format chart elements.
Change the chart type.
Filter the data series.
Insert a shape in a chart.
Create and format a combination chart.
Insert and format sparklines in a worksheet.
Create and format a waterfall chart.
1. Open the LAS-03 workbook from your student data files and save it as [your initials] Excel 3-1.
2. Create a pie chart object.
a. Select the Jan-June sheet and select cells A5:A10. Press Ctrl and select cells I5:I10.
b. Click the Insert Pie or Doughnut Chart button [Insert tab, Charts group].
c. Select the 2D-Pie chart subtype.
3. Create a chart sheet.
a. Select the pie chart object.
b. Click the Move Chart button [Chart Tools Design tab, Location group].
c. Click the New sheet button.
d. Type Pie Chart in the text box.
+
+
+
+
+
+
+
+
+
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
Th
is
stu
dy
re
so
ur
ce
w
as
sh
are
d v
ia
Co
ur
se
He
ro
.co
m
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
e. Click OK.
4. Apply a chart style.
a. Click the More button [Chart Tools Design tab, Chart Styles group].
b. Select Style 12.
5. Edit and format chart elements.
a. Click the chart title element.
b. Click the Formula bar, type January through June Expenses, and press Enter.
c. Triple-click the new label in the chart and change the font size to 28 pt.
d. Click the legend element to select it and to deselect the title element.
e. Click the Font Size arrow [Home tab, Font group] and change the legend size to 12 pt.
f. Select the Chart Area element, the chart background.
g. Click the Shape Outline button [Chart Tools Format tab, Shape Styles group].
h. Choose Black, Text 1 as the color.
i. Click the Shape Outline button again.
3-57 Pie Chart sheet with format changes
j. Choose Weight and select 1 pt.
k. Click outside the chart background to see the
outline (Figure 3-57).
6. Create a bar chart object.
a. Click the Jan-June sheet tab.
b. Select cells A4:G9 as source data, omitting
the Rent expense.
c. Click the Quick Analysis button and select
Charts.
d. Select Clustered Bar.
e. Point to the chart border to display a move pointer.
f. Drag the chart object so that its top left corner is at cell A13.
g. Point to the bottom right selection handle to display a resize arrow.
h. Drag the pointer to reach cell I30 (Figure 3-58).
3-58 Bar chart object sized and positioned
7. Change the chart type.
a. Click the bar chart object.
b. Click the Change Chart Type button
[Chart Tools Design tab, Type group].
c. Click the Recommended Charts tab.
d. Choose Clustered Column in the left pane.
e. Click OK.
8. Filter the source data.
a. Click the column chart object.
b. Click the Chart Filters button in the top right corner of the chart.
c. Deselect the January, February, and March boxes.
d. Click Apply in the Chart Filters pane (Figure 3-59).
e. Click the Chart Filters button to close the pane.
9. Edit, format, and add chart elements.
a. Click the chart title object.
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
Th
is
stu
dy
re
so
ur
ce
w
as
sh
are
d v
ia
Co
ur
se
He
ro
.co
m
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
3-59 Chart filters applied
b. Click the Formula bar.
c. Type Second Quarter Variable Expenses and
press Enter.
d. Click the Chart Elements arrow [Chart
Tools Format tab, Current Selection group]
and choose Chart Area.
e. Click the Shape Outline button [Chart
Tools Format tab, Shape Styles group].
f. Choose Black, Text 1 as the color.
g. Click the Shape Outline button again and choose Weight and 1 pt.
h. Click the Change Colors button [Chart Tools Design tab, Chart Styles group].
i. Choose Color 3 in the Colorful group.
j. Click the Chart Elements arrow [Chart Tools Format tab, Current Selection group] and choose
Plot Area.
k. Click the Shape Fill button [Chart Tools Format tab, Shape Styles group].
l. Choose Gold, Accent 4, Lighter 80% as the color.
m. Click a worksheet cell.
10. Create a combination chart sheet.
3-60 Combo chart and a secondary axis
a. Select cells A9:G9. Press Ctrl and select
cells A11:G11.
b. Click the Insert Combo Chart button
[Insert tab, Charts group].
c. Select the Clustered Column-Line chart
subtype.
d. Click the Move Chart button [Chart Tools Design tab, Location group].
e. Click the New sheet button, type Combo Chart as the name, and click OK.
f. Double-click the line marker in the chart.
g. Click the Series Options button in the Format Data Series task pane.
h. Select the Secondary Axis box in the task pane (Figure 3-60).
i. Close the task pane.
11. Edit and format chart elements.
a. Edit the chart title placeholder to Utilities Expense and Total Expenses.
b. Select the chart area.
c. Apply a 1 pt. Black, Text 1 outline to the chart area.
d. Click one of the column markers to select the data series.
e. Click the More button [Chart Tools Format tab, Shape Styles group].
f. Select Moderate Effect – Black, Dark 1
in the Theme Styles group.
g. Click the line marker.
h. Click the Shape Outline button [Chart
Tools Format tab, Shape Styles group].
i. Choose Gold, Accent 4 as the color.
j. Click the Shape Outline button again and
choose Weight and 3 pt (Figure 3-61).
12. Insert sparklines in the worksheet.
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
Th
is
stu
dy
re
so
ur
ce
w
as
sh
are
d v
ia
Co
ur
se
He
ro
.co
m
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
3-61 Line and column formatted in combo charta. Click the Jan-June sheet tab.
b. Select cells B5:G9 as the data range.
c. Click the Column Sparkline button [Insert tab, Sparklines group].
d. Select cells H5:H9 for the Location Range and click OK.
e. Click the Sparkline Color button [Sparkline Tools Design tab, Style group].
f. Choose White, Background 1, Darker 50%.
3-62 Text box shape inserted in chart
13. Insert a shape in a chart.
a. Select the column chart object.
b. Click the More button [Chart Tools
Format tab, Insert Shapes group].
c. Select the Text Box shape [Basic Shapes
group].
d. Draw a text box like the one shown in
Figure 3-62.
e. Type Utilities are to be removed from an updated chart per management request.
f. Drag a selection handle to size the text box shape if necessary.
g. Point to a border of the Text Box shape to display a four-pointed arrow and position it if needed.
14. Click a worksheet cell and press Ctrl+Home.
15. Create a waterfall chart object.
a. Select the Apr Cash sheet tab.
b. Select cells A5:B14. This data represents cash coming in and going out for April.
c. Click the Insert Waterfall or Stock Chart button [Insert tab, Charts group].
d. Select Waterfall.
16. Size and position a chart object.
a. Point to the chart border to display the move pointer.
b. Drag the chart object so that its top left corner is at cell C1.
c. Point to the bottom right selection handle to display the resize arrow.
d. Drag the pointer to cell L20.
17. Format chart elements.
a. Double-click the Net Cash column marker and then click the marker again to open the Format
Data Point pane.
b. Click the Series Options button in the task pane and expand the Series Options group.
c. Select the Set as total box.
d. Triple-click the chart title placeholder text and type April Cash Flow.
e. Select the chart area and apply a 1 pt. black outline.
f. Click a worksheet cell and press Ctrl+Home.
18. Save and close the workbook (Figure 3-63).
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
Th
is
stu
dy
re
so
ur
ce
w
as
sh
are
d v
ia
Co
ur
se
He
ro
.co
m
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
Page(s)
ID ex16_ip_03_gp_3-1
3-63 Excel 3-1 completed worksheets and
charts
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
Th
is
stu
dy
re
so
ur
ce
w
as
sh
are
d v
ia
Co
ur
se
He
ro
.co
m
Powered by TCPDF (www.tcpdf.org)
https://www.coursehero.com/file/24469479/Guided-Project-3-1pdf/
http://www.tcpdf.org