Microsoft Excel 2013 Level 2 Course for Teaching

Full excel level 2 Course that can teach mpst advanced excel abilities

1. Copyright © 2015 30 Bird Media LLC
Excel 2013
Level 2

2. Copyright © 2015 30 Bird Media LLC
Course Objectives
After you complete this course, you will know:
How to manage workbooks and customize the Excel environment.
How to use named ranges in formulas.
About Excel tables and how to sort data, use filtering features, validate data,
and transpose rows and columns.
How to consolidate data from more than one range or workbook, and how to
use subtotals.
How to use PivotTables to summarize and rearrange large amounts of data in a
list, and how to use PivotCharts to present such data.
How to use conditional formatting to format data when it meets a condition,
and how to insert and manipulate graphics.
How to use special chart features, and how to insert sparklines to give
snapshots of data and how to use Quick Access features to conveniently
analyze data.
How to use permissions to control access to and prevent changes in your
workbooks and about sharing features such as comments and change tracking.

3. Copyright © 2015 30 Bird Media LLC
Chapter 1: Managing Workbooks
You will learn how to:
Manage worksheets in a workbook that
contains many
Customize the Quick Access toolbar and
the ribbon, and create a custom view

4. Copyright © 2015 30 Bird Media LLC
Module A: Managing Worksheets
You will learn how to:
Insert and delete one or more worksheets
Rearrange worksheets by moving or
copying them either within or between
workbooks
Rename worksheets, change their tab
color, and hide them

5. Copyright © 2015 30 Bird Media LLC
Inserting Worksheets
Insert Worksheet tab: Insert Sheet command:

6. Copyright © 2015 30 Bird Media LLC
Moving or Copying Worksheets

7. Copyright © 2015 30 Bird Media LLC
Worksheet Names
Must be unique within workbook
Must have 31 or fewer characters
Cannot contain:
/ \ * ? ; [ ]

8. Copyright © 2015 30 Bird Media LLC
Inserting and Modifying Hyperlinks

9. Copyright © 2015 30 Bird Media LLC
Hyperlinks in the final worksheet

10. Copyright © 2015 30 Bird Media LLC
Assessment: Managing Worksheets
You use the Insert tab on the ribbon to
insert new worksheets. True or False?
A.True
B.False
That’s false. You actually use either the Cells
group of the Home tab (on the ribbon), or
the context menus for the worksheet tabs.

11. Copyright © 2015 30 Bird Media LLC
Assessment: Managing Worksheets
Which of the following are options when moving or
copying a worksheet? Choose all that apply.
A.Moving or copying to a new workbook
B.Moving or copying within the current workbook
C.Moving or copying to Microsoft Word
D.Moving or copying to a workbook that is not
currently open
A and B are correct. You can't move a worksheet as a
whole to Microsoft Word, nor move a worksheet to a
workbook that is not open.

12. Copyright © 2015 30 Bird Media LLC
Assessment: Managing Worksheets
Which of the following is not a valid
worksheet name?
A.Sales 2014
B.Sales/Rep
C.2014 Sales
B is not valid, because you cannot use the
slash (/) character in a worksheet name.

13. Copyright © 2015 30 Bird Media LLC
Assessment: Managing Worksheets
You cannot hide worksheets. True or false?
A.True
B.False
That's false. You certainly can hide
worksheets.

14. Copyright © 2015 30 Bird Media LLC
Assessment: Managing Worksheets
How do you create a worksheet group?
A.By clicking the Group command on the
ribbon
B.By clicking Group in the context menu for
each worksheet tab
C.By holding down Ctrl to select multiple
worksheets
C is correct.

15. Copyright © 2015 30 Bird Media LLC
Module B: Customizing Excel
You will learn how to:
Customize the Quick Access Toolbar
Customize the ribbon, including adding
tabs and groups

16. Copyright © 2015 30 Bird Media LLC
Customizing the Quick Access Toolbar

17. Copyright © 2015 30 Bird Media LLC
Collapsing the Ribbon

18. Copyright © 2015 30 Bird Media LLC
Customizing the Ribbon

19. Copyright © 2015 30 Bird Media LLC
Custom Tab on the Ribbon

20. Copyright © 2015 30 Bird Media LLC
Assessment: Customizing Excel
You must use the Excel Options window to
customize the Quick Access toolbar. True or
false?
A.True
B.False
That’s false. Although you can use the Excel
Options window, you can also add or remove
several commands directly from the menu on the
right of the toolbar.

21. Copyright © 2015 30 Bird Media LLC
Assessment: Customizing Excel
You can add buttons only to a custom ribbon
group. True or False?
A.True
B.False
That's true. To add custom buttons, you
must first create either a custom group on
an existing tab, or create a custom tab.

22. Copyright © 2015 30 Bird Media LLC
Assessment: Customizing Excel
How do your rearrange commands or groups on the
ribbon?
A.Drag them where you want them.
B.Right-click the ribbon, and click Rearrange.
C.Use the Move Up and Move Down buttons in the
Customize Ribbon options of the Excel Options
window.
C is correct. No, you have to use the Move Up and
Move Down buttons in the Customize Ribbon options
of the Excel Options window.

23. Copyright © 2015 30 Bird Media LLC
Summary: Managing Workbooks
You should now know how to:
Insert, delete, move, copy, rename, and
group worksheets, as well as change their
tab color and hide or unhide them
Customize the Quick Access toolbar;
collapse the ribbon or add custom tabs,
groups, and commands to it

24. Copyright © 2015 30 Bird Media LLC
My Managing Workbooks Synthesis

25. Copyright © 2015 30 Bird Media LLC
Chapter 2: Named Ranges
You will learn how to:
Assign names to values, cells, and ranges,
and use names in formulas

26. Copyright © 2015 30 Bird Media LLC
Module A: Using Names in Formulas
You will learn how to:
Assign a name to a range
Use a name instead of a reference in a
formula
Use a name for a value in a formula
Create names from the labels in a selected
range
Apply names to references in selected
formulas

27. Copyright © 2015 30 Bird Media LLC
A Named Range

28. Copyright © 2015 30 Bird Media LLC
Formulas Using Named Ranges

29. Copyright © 2015 30 Bird Media LLC
Name Manager

30. Copyright © 2015 30 Bird Media LLC
My Named Ranges

31. Copyright © 2015 30 Bird Media LLC
Assessment: Using Names in Formulas
Which of the following can Excel names refer
to? Choose all that apply.
A.Ranges
B.Values
C.Formats
D.Formulas
A, B, and D are correct. Names can refer to
ranges, values, or formulas, but not to formats.

32. Copyright © 2015 30 Bird Media LLC
Assessment: Using Names in Formulas
Which of the following is not a valid Excel
name?
A.Commission Rate
B.Commission
C.Commission_for_Sales_in_Pacific_Region
A, "Commission Rate," cannot be an Excel
name because it contains a space.

33. Copyright © 2015 30 Bird Media LLC
Assessment: Using Names in Formulas
Which of the following are ways to define names? Choose
all that apply.
A.Select a range, and then type a name in the Name
box.
B.Click Define Name.
C.Use the Insert tab of the ribbon.
D.Select a range, and click Create from Selection.
A, B, and D are correct. There are many ways to define
names, but there aren't any commands for defining names
on the Insert tab. The commands are on the Formula tab.

34. Copyright © 2015 30 Bird Media LLC
Assessment: Using Names in Formulas
The only way to use a name for a reference
in a formula you've already created is to edit
the formula. True or false?
A.True
B.False
That's false. You can use the Apply Names
command to substitute appropriate names
for references in selected formulas.

35. Copyright © 2015 30 Bird Media LLC
Assessment: Using Names in Formulas
You use the Name Manager to make
changes to a name, such as changing the
reference to which it refers or its name. True
or false?
A.True
B.False
That's true.

36. Copyright © 2015 30 Bird Media LLC
Summary: Named Ranges
You should now know how to:
Define a name to refer to a cell, a range, or
a value
Apply names to selected formulas
Use names in formulas
Create many names in a single step using
the Create from Selection command
Use the Name Manager to edit existing
names

37. Copyright © 2015 30 Bird Media LLC
My Names Synthesis

38. Copyright © 2015 30 Bird Media LLC
Chapter 3: Tables
You will learn how to:
How to sort data in a range by the values in
one or more columns
About Excel tables, and how to filter data in
a table to show only what you want to see
How to use validation to ensure that the
data entered in a worksheet is appropriate
How to transpose rows and columns of data

39. Copyright © 2015 30 Bird Media LLC
Module A: Sorting
You will learn how to:
Sort the rows in a range by putting a single
column in alphabetical, numeric, or
chronological order
Sort the rows in a range by more than one
column of values

40. Copyright © 2015 30 Bird Media LLC
Sort Order
Alphabetical Order Descending Values

41. Copyright © 2015 30 Bird Media LLC
Sorting Commands

42. Copyright © 2015 30 Bird Media LLC
Employees by Date of Hire

43. Copyright © 2015 30 Bird Media LLC
Sort Window

44. Copyright © 2015 30 Bird Media LLC
Assessment: Sorting
Which of the following are ways you can sort
data?
A.By numerical values
B.By dates
C.By number formats
D.Alphabetical order
A, B, and D are correct. You can't sort by
number formats.

45. Copyright © 2015 30 Bird Media LLC
Assessment: Sorting
You always need to use the Sort window to
sort a range. True or false?
A.True
B.False
That's false. You can sort by a single column
by using a command in the Sort & Filter
menu.

46. Copyright © 2015 30 Bird Media LLC
Assessment: Sorting
You can sort data by font color. True or
false?
A.True
B.False
That's true.

47. Copyright © 2015 30 Bird Media LLC
Module B: Filtering Tables
You will learn how to:
Create an Excel table and describe what its
features are
Use AutoFilter to quickly show only
certain data in a table
Create advanced criteria for filtering tables
in a very focused way
Remove duplicate values from a table

48. Copyright © 2015 30 Bird Media LLC
Table Features
Header row
Banded rows
Calculated columns
Totals row

49. Copyright © 2015 30 Bird Media LLC
Using AutoFilter

50. Copyright © 2015 30 Bird Media LLC
Filter Commands

51. Copyright © 2015 30 Bird Media LLC
Assessment: Filtering Tables
An Excel table must have a header row. True
or false?
A.True
B.False
That's false, but you will get much more
functionality out of your tables if they have
header rows.

52. Copyright © 2015 30 Bird Media LLC
Assessment: Filtering Tables
Which of the following is NOT a type of data by
which you can filter a table? Select the one correct
answer.
A.Text
B.Formulas
C.Numbers
D.Dates
B is correct. You can't filter by formulas, but you
can filter by text, numbers, and dates.

53. Copyright © 2015 30 Bird Media LLC
Assessment: Filtering Tables
Does adding a condition using AND make
the criteria more or less restrictive?
A.More restrictive
B.Less Restrictive
A is correct. Adding a condition using AND
makes the criteria more restrictive.

54. Copyright © 2015 30 Bird Media LLC
Assessment: Filtering Tables
How would you create criteria to filter for
numbers below a minimum value or above a
maximum value?
A.Use the Between number filter command.
B.Use an OR condition.
C.Use the Not Between filter command.
B is correct. You'd need to create a Less Than
condition for one of the criteria, then click OR,
then add a Greater Than condition for the other.

55. Copyright © 2015 30 Bird Media LLC
Module C: Validation
You will learn how to:
Set up simple data validation rules
Create a validation list

56. Copyright © 2015 30 Bird Media LLC
Validation Messages

57. Copyright © 2015 30 Bird Media LLC
Department Validation List

58. Copyright © 2015 30 Bird Media LLC
Assessment: Validation
Which of the following are types of validation
you can apply to a cell or range without
creating a custom formula? Choose all the
correct answers.
A.Text of a particular length
B.Whole numbers
C.Lists of values
All of these are possible types of validation.

59. Copyright © 2015 30 Bird Media LLC
Assessment: Validation
You must create an input message and an
error alert for all validation rules. True or
false?
A.True
B.False
That's false. Input messages and error alerts
are options.

60. Copyright © 2015 30 Bird Media LLC
Assessment: Validation
For list validation, you can enter a list of values
directly in the Data Validation window. True or
false?
A.True
B.False
That's true. But your list will be easier to
maintain if you it on a worksheet, and then enter
a reference to it in the Data Validation window.

61. Copyright © 2015 30 Bird Media LLC
Module D: Transposing Data
You will learn:
Use a pasting option to transpose rows
and columns in a range

62. Copyright © 2015 30 Bird Media LLC
Transposed Data

63. Copyright © 2015 30 Bird Media LLC
Assessment: Transposing Data
You can use both the Copy and the Cut
commands to transpose data. True or false?
A.True
B.False
That's false. You can use only the Copy
command.

64. Copyright © 2015 30 Bird Media LLC
Assessment: Transposing Data
Excel updates references in transposed
formulas. True or false?
A.True
B.False
That's true.

65. Copyright © 2015 30 Bird Media LLC
Summary: Tables
You should now know:
How to sort data by text, number, and date values; how
to sort by more than one column; and how to sort by
other cell attributes, such as cell or font color
About Excel tables and how to create them, how to filter
table data by various kinds of values, how to clear a
filter, and how to use more complex criteria to show
only what you want to see
How to use validation to ensure that the data entered in
a worksheet is appropriate, and how to create list-based
validation
How to transpose rows and columns of data

66. Copyright © 2015 30 Bird Media LLC
My Tables Synthesis

67. Copyright © 2015 30 Bird Media LLC
Chapter 4: Summarizing Data
You will learn how to:
Consolidate similar data on multiple
worksheets onto a single worksheet
Use subtotals to summarize information
based on values in columns

68. Copyright © 2015 30 Bird Media LLC
Module A: Consolidation
You will learn how to:
Consolidate date from several ranges into
a single list
Consolidate with links back to the source
data

69. Copyright © 2015 30 Bird Media LLC
Consolidated Sales Data

70. Copyright © 2015 30 Bird Media LLC
All References Box

71. Copyright © 2015 30 Bird Media LLC
Consolidated Data

72. Copyright © 2015 30 Bird Media LLC
Outlining Features
Outline level buttons
Expand buttons
Collapse buttons
Links

73. Copyright © 2015 30 Bird Media LLC
Consolidating with Links
Category Using SUM Link to Source

74. Copyright © 2015 30 Bird Media LLC
Assessment: Consolidation
To consolidate data, the source ranges must
be structured identically. True or false?
A.True
B.False
That's false. If the source ranges are
structured identically, you can consolidate
by position. If not, you can consolidate by
category.

75. Copyright © 2015 30 Bird Media LLC
Assessment: Consolidation
Which of the following are functions that you can use
when consolidating? Choose all that apply.
A.STDDEV
B.LOOKUP
C.SUM
D.MATCH
E.COUNT
A, C, and E are correct. STDDEV, SUM, and COUNT are
all functions you can use. LOOKUP and MATCH are not.

76. Copyright © 2015 30 Bird Media LLC
Assessment: Consolidation
You must create an outline manually in
Excel. True or false?
A.True
B.False
That's false. Although you can create an
outline manually, Excel creates on
automatically when you use certain
features, including consolidating with links.

77. Copyright © 2015 30 Bird Media LLC
Module B: Subtotals
You will learn how to:
Insert subtotals in a list of data
Manage and copy subtotal data

78. Copyright © 2015 30 Bird Media LLC
Subtotal Features
Average discount per rep
Average discount per region
Outline structure

79. Copyright © 2015 30 Bird Media LLC
Regional Subtotals

80. Copyright © 2015 30 Bird Media LLC
Two Levels of Subtotals

81. Copyright © 2015 30 Bird Media LLC
Go To Special Window

82. Copyright © 2015 30 Bird Media LLC
Pasted Subtotals Report

83. Copyright © 2015 30 Bird Media LLC
Assessment: Subtotals
Subtotals are only possible in a range
formatted as an Excel table. True or false?
A.True
B.False
That's false. In fact, you cannot insert
subtotals in an Excel table.

84. Copyright © 2015 30 Bird Media LLC
Assessment: Subtotals
How would you add two levels of subtotals to a
list? Select the best answer.
A.Insert both levels in a single step.
B.Insert the lower level first, then insert the
higher level.
C.Insert the higher level first, then insert the
lower level.
C is correct. You have to insert the higher level
first, and each level in a separate step.

85. Copyright © 2015 30 Bird Media LLC
Assessment: Subtotals
Which command do you use to select visible cells
only? Choose the best response.
A.Find
B.Replace
C.Go To Special
D.Go To
C is correct. Although you can get to the Go To
Special window by first clicking the Go To
command, that's not the best way to do so.

86. Copyright © 2015 30 Bird Media LLC
Summary: Summarizing Data
You should now know how to:
Consolidate similar data on multiple worksheets
onto a single worksheet, consolidate with links,
and work with Excel's outline features to display
different levels of detail
Use subtotals to summarize information based
on values in one column, add levels of subtotal
detail, copy subtotal levels by using the select
visible cells feature, and remove subtotals from
a list

87. Copyright © 2015 30 Bird Media LLC
My Summarizing Synthesis

88. Copyright © 2015 30 Bird Media LLC
Chapter 5: PivotTables
You will learn:
About PivotTables and how to create and
format them
How to manipulate PivotTables to show
exactly what you want
How to create and manipulate PivotCharts
How to create relationships between
tables and create PivotTables based on
related tables

89. Copyright © 2015 30 Bird Media LLC
Module A: Creating and Formatting PivotTables
You will learn how to:
Create a PivotTable and view its
underlying data
Use external data
Control PivotTable options and value field
formats
Examine underlying PivotTable data

90. Copyright © 2015 30 Bird Media LLC
PivotTable Elements
Row field values
Column field values
Values
Slicer

91. Copyright © 2015 30 Bird Media LLC
Sales by Rep and Item

92. Copyright © 2015 30 Bird Media LLC
PivotTable Tools Design Tab

93. Copyright © 2015 30 Bird Media LLC
A Formatted PivotTable

94. Copyright © 2015 30 Bird Media LLC
Invoices for Daniels, Indus Tea

95. Copyright © 2015 30 Bird Media LLC
Assessment: Creating and Formatting PivotTables
Which of the following are elements of a PivotTable? Choose
all that apply.
A.Category fields
B.Row labels
C.Values
D.AutoFilter
E.Column labels
Row labels, column labels, and values are all elements of
PivotTables. There isn't an element called "categories" in a
PivotTable, and although you can filter a PivotTable, the
AutoFilter feature is not available.

96. Copyright © 2015 30 Bird Media LLC
Assessment: Creating and Formatting PivotTables
Which of the following is the best way to format the
values in PivotTable?
A.Formatting tools on the Home tab of the ribbon
B.Value field settings
C.Format Painter
B, Value field settings, is correct. Both the formatting
tools on the Home tab and the Format Painter will
apply formatting to the cells, which might move. By
using value field settings, the formatting will apply to all
the value cells, even if you rearrange the PivotTable.

97. Copyright © 2015 30 Bird Media LLC
Module B: Manipulating PivotTables
You will learn how to:
Filter and slice PivotTables to show
subsets of their data
Manipulate field setting to use different
functions
Group data within a field

98. Copyright © 2015 30 Bird Media LLC
Sort and Filter Options

99. Copyright © 2015 30 Bird Media LLC
Report Filter

100. Copyright © 2015 30 Bird Media LLC
Slicers
A Slicer Inserting a Slicer

101. Copyright © 2015 30 Bird Media LLC
Using Slicers
To display data for a particular value in the slicer
field, simply click that value in the slicer. To display
results for more than one value, hold down Ctrl
while clicking. Selected values are highlighted.
To resize a slicer, drag one of its borders or corners.
To move a slicer, drag it by the top.
Use options on the Slicer Tools tab to control how
the slicer looks and behaves.
To remove a slicer, simply click its title once, then
press Delete.

102. Copyright © 2015 30 Bird Media LLC
Values as Percentages

103. Copyright © 2015 30 Bird Media LLC
Showing Min and Max Values

104. Copyright © 2015 30 Bird Media LLC
Grouping by Quarter

105. Copyright © 2015 30 Bird Media LLC
Assessment: Manipulating PivotTables
You can filter a PivotTable based on row and
column label values. True or false?
True
False
That's true. Simply use the filter arrows by
the heading.

106. Copyright © 2015 30 Bird Media LLC
Assessment: Manipulating PivotTables
Which of the following are ways to view subsets of
data in a PivotTable? Choose all that apply.
A.Slicers
B.Subtotals
C.Report filters
D.AutoFilter
A and C are correct, you can use slicers and report
filters in this way. You can't use AutoFilter on a
PivotTable, and Subtotals don't show a subset of data.

107. Copyright © 2015 30 Bird Media LLC
Assessment: Manipulating PivotTables
How do you control how values are being
summarized in a PivotTable? Select the best answer.
A.Grouping
B.The Summarize Values By tab of the Field
Settings window
C.The Show Values As tab of the Field Settings
window
B is correct. You would use the Summarize Values By
tab of the Field Settings window.

108. Copyright © 2015 30 Bird Media LLC
Assessment: Manipulating PivotTables
You can show values as percentages only of
the grand totals. True or false?
True
False
That's false. You can show values as
percentages of grand totals, rows totals, or
column totals.

109. Copyright © 2015 30 Bird Media LLC
Module C: PivotCharts
You will learn how to:
Create PivotCharts
Manipulate PivotCharts to analyze data

110. Copyright © 2015 30 Bird Media LLC
PivotChart Elements
Series filter
Category filter

111. Copyright © 2015 30 Bird Media LLC
Filtering a PivotChart

112. Copyright © 2015 30 Bird Media LLC
PivotChart with Slicer

113. Copyright © 2015 30 Bird Media LLC
Assessment: PivotCharts
You can create a PivotChart without first
creating a PivotTable. True or false?
A.True
B.False
That's true.

114. Copyright © 2015 30 Bird Media LLC
Assessment: PivotCharts
Which of the following statements is most accurate?
A.PivotCharts have the features of PivotTables, but
not of normal Excel charts.
B.PivotCharts have the features of normal Excel
charts, but not of PivotTables.
C.PivotCharts have the features of both PivotTables
and normal Excel charts.
C is correct. PivotCharts have the features of both
PivotTables and normal Excel charts.

115. Copyright © 2015 30 Bird Media LLC
Summary: PivotTables
You should now know:
Create and format PivotTables, view their underlying
data, and create PivotTables based on external data
Manipulate PivotTables to show exactly what you want
by filtering or by using a slicer, change field settings for
summary and display, and group a field to summarize
dates by month or quarter
Create PivotCharts either directly from a list or from an
existing PivotTable, and manipulate PivotCharts to
analyze data
Create and observe relationships between tables, and
create PivotTables based on related tables

116. Copyright © 2015 30 Bird Media LLC
Synthesis: Cost Min and Max

117. Copyright © 2015 30 Bird Media LLC
Synthesis: PivotChart with Slicers

118. Copyright © 2015 30 Bird Media LLC
Chapter 6: Presentation Features
You will learn how to:
Format cells and ranges based upon
conditions you choose
Create custom number formats
Insert and manage various kinds of
graphics

119. Copyright © 2015 30 Bird Media LLC
Module A: Conditional Formats
You will learn how to:
Use built-in rules to conditionally format
cells and ranges
Use data bars and icon sets to graphically
represent conditional formatting
Use the rule manager to create
conditional formats
Create conditional formatting based on
formulas

120. Copyright © 2015 30 Bird Media LLC
Conditional Format Examples
Values Greater Than 100 Rows Colored by Region

121. Copyright © 2015 30 Bird Media LLC
Conditional Format Rules
Highlight Cells Top/Bottom

122. Copyright © 2015 30 Bird Media LLC
Between Rule Settings

123. Copyright © 2015 30 Bird Media LLC
Values Above $18,000

124. Copyright © 2015 30 Bird Media LLC
Sales Data with Conditional Formats

125. Copyright © 2015 30 Bird Media LLC
Data Bars and Icon Sets
Data Bars Icon Sets

126. Copyright © 2015 30 Bird Media LLC
Data Bar and Icon Set Options
Data Bar Options Icon Set Options

127. Copyright © 2015 30 Bird Media LLC
Sales Data with Icon Set

128. Copyright © 2015 30 Bird Media LLC
Custom Rule Types
Value-based
Containing
Top/bottom
Above/below average
Unique or duplicate
Formula-based

129. Copyright © 2015 30 Bird Media LLC
One Standard Dev Above

130. Copyright © 2015 30 Bird Media LLC
Formatting Rows With a Formula

131. Copyright © 2015 30 Bird Media LLC
Assessment: Conditional Formats
You can use conditional formatting on both
numbers and text values. True or false?
A.True
B.False
That's true.

132. Copyright © 2015 30 Bird Media LLC
Assessment: Conditional Formats
You must always use formulas to apply
conditional formatting. True or false?
A.True
B.False
That's false. Many rules are directly
available without writing formulas.

133. Copyright © 2015 30 Bird Media LLC
Assessment: Conditional Formats
When creating rules, you are limited by the
options available in the built-in rule sets.
True or false?
A.True
B.False
That's false. You can create new rules that
have much more fine control over
conditions.

134. Copyright © 2015 30 Bird Media LLC
Assessment: Conditional Formats
Which of the following statements is true of using formulas
for conditional formatting? Choose the one correct answer.
A.You can use any type of formula, regardless of its
result.
B.Formulas must be in relation to the active cell of the
range you selected to format.
C.If a formula results in FALSE for a particular cell, that
cell will be formatted.
B is correct, formulas must be in relation to the active cell.
Formulas must result in logical values, TRUE or FALSE. And
if they result in FALSE, the cell will not be formatted.

135. Copyright © 2015 30 Bird Media LLC
Module B: Graphics
You will learn how to:
Insert a picture, clip art, shape, or word
art in a workbook
Adjust graphic properties
Move, size, or rotate graphics
Use SmartArt to create functional,
dynamic illustrations, such as
organizational or process charts

136. Copyright © 2015 30 Bird Media LLC
Types of Graphics
Pictures
Clip Art
Shapes
SmartArt
Screenshots

137. Copyright © 2015 30 Bird Media LLC
Sizing Graphics

138. Copyright © 2015 30 Bird Media LLC
SmartArt Example

139. Copyright © 2015 30 Bird Media LLC
Completed Org Chart

140. Copyright © 2015 30 Bird Media LLC
Assessment: Graphics
You cannot edit pictures from within Excel.
True or false?
A.True
B.False
That's false. After you insert a picture, you
can use many of Excel's tools to edit it.

141. Copyright © 2015 30 Bird Media LLC
Assessment: Graphics
Which of the following can you accomplish by dragging
a graphic's handles? Choose all the correct responses.
A.Moving
B.Sizing
C.Rotating
D.Changing contrast
B and C are correct, You can size or rotate a graphic in
this way. You move a graphic by dragging, but not using
handles. And you can't change it's contrast by dragging
its handles.

142. Copyright © 2015 30 Bird Media LLC
Assessment: Graphics
Which of the following is a good use of SmartArt?
Choose the best answer.
A.A process diagram.
B.A graphic of a person using a phone.
C.A graphic of a window in another open program.
A is correct. You want to use SmartArt when the
graphic needs to show a relationship between
elements, such as in a process diagram or an flow
chart.

143. Copyright © 2015 30 Bird Media LLC
Assessment: Graphics
You can adjust picture contrast and
brightness from within Excel. True or false?
A.True
B.False
That's true, you can.

144. Copyright © 2015 30 Bird Media LLC
Summary: Presentation Features
You should now know how to:
Format cells and ranges using built-in rules for
conditional formatting, by using data bars and icon sets
to graphically represent where values fall in a range, by
creating new rules with more complex conditions, and by
creating rules that use formulas to evaluate conditions
Create custom number formats to show numbers and
dates precisely how you want them to appear
Insert, move, resize, and change properties for pictures,
online pictures, and shapes; insert WordArt and text
boxes; and use SmartArt to create functional graphics
that illustrate relationships among shapes

145. Copyright © 2015 30 Bird Media LLC
Synthesis: Formatted

146. Copyright © 2015 30 Bird Media LLC
Chapter 7: Advanced Charts
You will learn how to:
Create charts that include trendlines and
dual axes, and create and use a chart
template
Use and format sparklines, which show
visual snapshots of data
Use Quick Analysis features to
conveniently analyze selected data

147. Copyright © 2015 30 Bird Media LLC
Module A: Special Chart Types
You will learn how to:
Apply a trendline to a chart
Combine two different sets of data in a
single chart by using a trendline
Create and use chart templates to quickly
format charts

148. Copyright © 2015 30 Bird Media LLC
Trendline Types
Linear Exponential

149. Copyright © 2015 30 Bird Media LLC
A Polynomial Trendline

150. Copyright © 2015 30 Bird Media LLC
Using a Secondary Axis

151. Copyright © 2015 30 Bird Media LLC
Applying a Chart Template

152. Copyright © 2015 30 Bird Media LLC
Assessment: Special Chart Types
Which of the following are regression types for a
trendline? Choose all that apply.
A.Linear
B.Curved
C.Exponential
D.Polynomial
E.Static
A, C, and D are correct. The types of regression are
Linear, Logarithmic, Polynomial, Power Exponential, and
Moving Average.

153. Copyright © 2015 30 Bird Media LLC
Assessment: Special Chart Types
And R-squared value of close to 0 shows the
best fit for a trendline. True or false?
A.True
B.False
That's false. A fit is better the closer the R-
squared value is to 1.

154. Copyright © 2015 30 Bird Media LLC
Assessment: Special Chart Types
You add a secondary axis to a chart by using
the Format Axis command for the primary
value axis. True or False?
A.True
B.False
That's false. You would use the Format Data
Series command for the series you want to
plot on a secondary axis.

155. Copyright © 2015 30 Bird Media LLC
Assessment: Special Chart Types
You apply a chart template in the Change
Chart Type window. True or false?
A.True
B.False
That's true. You use the Template category.

156. Copyright © 2015 30 Bird Media LLC
Module B: Sparklines
You will learn how to:
Insert and format a sparkline

157. Copyright © 2015 30 Bird Media LLC
Sample Column Sparklines

158. Copyright © 2015 30 Bird Media LLC
Grouped Sparklines

159. Copyright © 2015 30 Bird Media LLC
Sparklines with High Points

160. Copyright © 2015 30 Bird Media LLC
Assessment: Sparklines
You can create sparklines in all the same
types as normal Excel charts. True or false?
A.True
B.False
That's false. The available sparkline types
are line, column, and win/loss.

161. Copyright © 2015 30 Bird Media LLC
Assessment: Sparklines
Sparklines always reside in a single cell. True
or false?
A.True
B.False
That's true.

162. Copyright © 2015 30 Bird Media LLC
Module C: Quick Analysis
You will learn how to:
Use Quick Analysis options to apply
conditional formats, create charts, and
analyze data in a selected range.

163. Copyright © 2015 30 Bird Media LLC
Quick Analysis options
1.The Quick Analysis button appears by the
bottom-right corner of a selected range.
Click it to see tabbed galleries of options
for making quick work of the selection.

164. Copyright © 2015 30 Bird Media LLC
Assessment: Quick Analysis
How do you access Quick Analysis features?
Choose the one correct answer.
A.The Data tab of the ribbon
B.The Quick Analysis button on the Home
tab
C.The Quick Analysis button for a selected
range
D

165. Copyright © 2015 30 Bird Media LLC
Assessment: Quick Analysis
Which of the following is NOT a main Quick
Analysis option? Choose the one correct
answer.
A.Conditional formatting
B.Number formatting
C.Tables
D.Charts
B

166. Copyright © 2015 30 Bird Media LLC
Summary: Advanced Charts
You should now know:
About types of trendlines and how to insert them
on charts; how to create combination charts and
use a secondary axis; and how to create and then
use chart templates to quickly format a chart
About sparklines, how to insert them into cells to
show snapshots of data, and how to format and
customize them
How to access and use Quick Analysis features to
apply conditional formatting and table formats, or
to insert charts, totals, or sparklines

167. Copyright © 2015 30 Bird Media LLC
Combination Chart with Trendlines

168. Copyright © 2015 30 Bird Media LLC
Sparklines on Budget Data

169. Copyright © 2015 30 Bird Media LLC
Chapter 8: Collaboration
You will learn:
About permissions, and how to use them
to restrict access to a workbook or to
protect certain parts of it from being
changed
How to share workbooks, track changes to
them, merge multiple versions into one,
and mark a workbook as final

170. Copyright © 2015 30 Bird Media LLC
Module A: Permissions
You will learn how to:
Protect a workbook so only those users
with a password may open it
Lock cells in a workbook, and protect a
workbook to prevent certain cells and
ranges from being edited
Protect a workbook’s structure

171. Copyright © 2015 30 Bird Media LLC
Protect Workbook Menu

172. Copyright © 2015 30 Bird Media LLC
Protecting Workbook Structure

173. Copyright © 2015 30 Bird Media LLC
Assessment: Permissions
Which of the following are type of protection in Excel? Choose all
that apply.
A.Cell protection
B.Workbook encryption
C.Sheet protection
D.Workbook structure
E.Ribbon protection
B, C, and D are correct. Workbook encryption, sheet protection,
and workbook structure protection are all things you can do. Cell
protection really happens by locking or unlocking cells before
protecting a worksheet, and ribbon protection doesn't exist.

174. Copyright © 2015 30 Bird Media LLC
Assessment: Permissions
When you protect a worksheet, you can
control which cells are editable. True or
false?
A.True
B.False
That's true. You do this by unlocking the
cells that you want users to be able to edit
before you protect the worksheet.

175. Copyright © 2015 30 Bird Media LLC
Assessment: Permissions
Protecting workbook structure always
includes preventing changes to windows.
True or false?
A.True
B.False
That's false. You have the option of
preventing window changes, but you don't
have to do it that way.

176. Copyright © 2015 30 Bird Media LLC
Module B: Shared Workbooks
You will learn how to:
Insert, view, edit, and delete comments in a
workbook
Restrict who can edit a workbook and what
they can edit
Turn on the track changes feature to see how a
particular user has changed a workbook
Merge copies of a shared workbook to
consolidate changes
Mark a workbook as final

177. Copyright © 2015 30 Bird Media LLC
Comments
Comment Indicator Viewing a Comment

178. Copyright © 2015 30 Bird Media LLC
Comments Group

179. Copyright © 2015 30 Bird Media LLC
Editing a Comment

180. Copyright © 2015 30 Bird Media LLC
Assessment: Shared Workbooks
You use the Insert tab to insert and manage
comments in a workbook. True or false?
A.True
B.False
That's false. You use the Comments group
on the Review tab.

181. Copyright © 2015 30 Bird Media LLC
Assessment: Shared Workbooks
You can highlight tracked changes only in
shared workbooks. True or false?
A.True
B.False
That’s true. If your workbook isn't already
shared, turning on highlighting of changes
will automatically create a shared
workbook.

182. Copyright © 2015 30 Bird Media LLC
Assessment: Shared Workbooks
Which of the following statements is true? Choose the
one correct answer.
A.You can merge any two workbooks.
B.You can merge only shared versions of the same
workbook.
C.You cannot merge more than two versions of a
shared workbook at a time.
B is correct. You can merge only shared versions of the
same workbook, but you can merge two or more
versions at once.

183. Copyright © 2015 30 Bird Media LLC
Summary: Collaboration
You should now know:
About permissions, and how to use them to
encrypt a workbook with a password, control
access to data and various workbook features, and
protect a workbook's structure or windows from
being changed
How to insert, modify, and delete comments; about
shared workbooks; how to use Track Changes to
mark changes by multiple users; how to compare
and merge multiple versions of the same shared
workbook; and how to mark a workbook as final