Benchmark Series: Microsoft Excel 2019 Levels 1 and 2
By Nita Rutkosky, Jan Davidson, Audrey Roggenkamp and Ian Rutkosky
Contents:
Preface ix
Getting Started GS-1
Microsoft Excel Level 1
Unit 1 Preparing and Formatting Worksheets 1
Chapter 1 Preparing an Excel Workbook 3
Creating a Worksheet 4
Entering Data in a Worksheet 6
Saving a Workbook 7
Editing Data in a Cell 9
Printing a Worksheet 10
Closing a Workbook and Closing Excel 11
Using Automatic Entering Features 12
Using AutoComplete 12
Using AutoCorrect 12
Using AutoFill 14
Opening a Workbook 15
Opening a Workbook from the Recent Option List 15
Pinning and Unpinning Workbooks and Folders 15
Entering Formulas 17
Using the AutoSum Button to Add Numbers 17
Using the AutoSum Button to Average Numbers 18
Using the Fill Handle to Copy a Formula 18
Selecting Cells 19
Selecting Cells Using the Mouse 19
Selecting Cells Using the Keyboard 19
Selecting Data within Cells 20
Applying Basic Formatting 20
Changing Column Width 20
Merging and Centering Cells 21
Formatting Numbers 22
Using the Tell Me Feature 24
Using Help 26
Getting Help from a ScreenTip 27
Getting Help in a Dialog Box or at the
Backstage Area 28
Chapter Summary 28
Chapter 2 Inserting Formulas in a Worksheet 31
Writing Formulas with Mathematical Operators 32
Copying a Formula with Relative Cell References 32
Checking Cell References in a Formula 33
Writing a Formula by Pointing 34
Determining the Order of Operations 35
Using the Trace Error Button 36
Identifying Common Formula Errors 36
Inserting Formulas with Functions 38
Writing Formulas with Statistical Functions 40
Writing Formulas with the NOW and
TODAY Functions 44
Displaying Formulas 44
Using Absolute and Mixed Cell References in Formulas 45
Using an Absolute Cell Reference in a Formula 45
Using a Mixed Cell Reference in a Formula 47
Chapter Summary 48
Chapter 3 Formatting a Worksheet 51
Changing Column Width 52
Changing Column Width Using Column
Boundaries 52
Changing Column Width at the Column Width
Dialog Box 53
Changing Row Height 54
Inserting and Deleting Cells, Rows, and Columns 55
Inserting Rows 55
Inserting Columns 56
Deleting Cells, Rows, or Columns 57
Clearing Data in Cells 58
Applying Formatting 58
Applying Font Formatting 59
Formatting with the Mini Toolbar 59
Applying Alignment Formatting 59
Applying a Theme 62
Formatting Numbers 63
Formatting Numbers Using Number Group Buttons 63
Applying Number Formatting at the
Format Cells Dialog Box 65
Applying Formatting Using the Format Cells Dialog Box 67
Aligning and Indenting Data 67
Changing the Font 69
Adding Borders to Cells 71
Adding Fill and Shading to Cells 73
Repeating the Last Action 73
Formatting with Format Painter 74
Hiding and Unhiding Columns and Rows 75
Chapter Summary 77
Chapter 4 Enhancing a Worksheet 79
Formatting a Worksheet Page 80
Changing Margins 80
Centering a Worksheet Horizontally and/or Vertically 81
Changing Page Orientation 82
Changing the Paper Size 82
Inserting and Removing Page Breaks 83
Printing Column and Row Titles on Multiple Pages 86
Scaling Data 87
Inserting a Background Picture 88
Printing Gridlines and Row and Column Headings 89
Printing a Specific Area of a Worksheet 89
Inserting Headers and Footers 91
Customizing Print Jobs 96
Checking Spelling 97
Using Undo and Redo 97
Finding and Replacing Data and Cell Formatting 99
Sorting Data 104
Completing a Custom Sort 104
Sorting More Than One Column 106
Filtering Data 106
Chapter Summary 109
Unit 2 Enhancing the Display of Workbooks 113
Chapter 5 Moving Data within
and between Workbooks 115
Creating a Workbook with Multiple Worksheets 116
Inserting a New Worksheet 116
Deleting a Worksheet 116
Selecting Multiple Worksheets 116
Copying, Cutting, and Pasting Cells 117
Copying and Pasting Selected Cells 117
Using Paste Options 118
Moving Selected Cells 119
Copying and Pasting Using the Clipboard
Task Pane 120
Pasting Values Only 122
Managing Worksheets 123
Hiding and Unhiding a Worksheet in a Workbook 125
Printing a Workbook Containing Multiple
Worksheets 126
Changing the Zoom 127
Splitting a Worksheet and Freezing
and Unfreezing Panes 128
Naming and Using a Range 130
Working with Windows 131
Opening Multiple Workbooks 132
Arranging Workbooks 132
Hiding and Unhiding Workbooks 134
Sizing and Moving Workbooks 135
Moving, Linking, Copying and Pasting Data
between Workbooks 135
Moving and Copying Data 136
Linking Data 137
Copying and Pasting Data between Programs 138
Chapter Summary 139
Chapter 6 Maintaining Workbooks 143
Managing the Recent Option List 144
Pinning and Unpinning a Workbook 145
Recovering an Unsaved Workbook 145
Clearing the Recent Option List
and the Recent List 145
Managing Worksheets 146
Copying a Worksheet to Another Workbook 146
Moving a Worksheet to Another Workbook 148
Formatting with Cell Styles 150
Applying a Cell Style 150
Defining a Cell Style 151
Modifying a Cell Style 155
Copying Cell Styles to Another Workbook 156
Removing a Cell Style 157
Deleting a Cell Style 157
Inserting Hyperlinks 158
Linking to an Existing Web Page or File 158
Navigating Using Hyperlinks 159
Linking to a Place in the Workbook 160
Linking to a New Workbook 160
Linking Using a Graphic 161
Linking to an Email Address 161
Modifying, Editing, and Removing a Hyperlink 162
Using Excel Templates 163
Inserting and Managing Comments 166
Inserting a New Comment 166
Posting a Comment 166
Editing and Deleting a Comment 168
Viewing and Managing Comments at the
Comments Task Pane 168
Writing Formulas with Financial Functions 170
Finding the Periodic Payments for a Loan 171
Finding the Future Value of a Series of Payments 173
Chapter Summary 174
Chapter 7 Creating Charts
and Inserting Formulas 177
Creating a Chart 178
Sizing and Moving a Chart 179
Editing Data and Adding a Data Series 180
Formatting with Chart Buttons 182
Printing a Chart 184
Changing the Chart Design 185
Changing the Chart Style 186
Switching Rows and Columns 186
Changing Chart Layout and Colors 187
Changing the Chart Location 188
Adding, Moving, and Deleting Chart Elements 188
Changing Chart Formatting 191
Formatting a Selection 191
Inserting a Shape 192
Creating Alternative Text for an Image 193
Using the Quick Analysis Feature 194
Applying Formatting at a Task Pane 195
Changing Chart Height and Width Measurements 196
Deleting a Chart 198
Writing Formulas with the Logical IF Function 200
Writing Formulas with an IF Function Using
the Function Arguments Dialog Box 201
Writing IF Statements Containing Text 203
Chapter Summary 204
Chapter 8 Adding Visual Interest
to Workbooks 207
Inserting Symbols and Special Characters 208
Inserting an Image 210
Customizing and Formatting an Image 210
Sizing and Moving an Image 210
Formatting an Image at the Format Picture
Task Pane 212
Inserting an Online Image 212
Creating and Inserting a Screenshot 214
Inserting and Formatting a Shape 215
Inserting and Modifying Text Boxes 218
Inserting and Customizing Icons 218
Inserting and Customizing 3D Models 221
Inserting a SmartArt Graphic 224
Entering Data in a SmartArt Graphic 224
Sizing, Moving, and Deleting a SmartArt Graphic 225
Changing the SmartArt Graphic Design 226
Changing the SmartArt Graphic Formatting 227
Creating, Sizing, and Moving WordArt 228
Chapter Summary 230
Excel Level 1 Index 233
Microsoft Excel Level 2
Unit 1 Advanced Formatting, Formulas,
and Data Management 1
Chapter 1 Advanced Formatting Techniques 3
Applying Conditional Formatting 4
Applying Conditional Formatting Using a New Rule 6
Editing and Deleting a Conditional Formatting Rule 8
Applying Conditional Formatting Using an Icon Set 11
Applying Conditional Formatting Using Data Bars
and Color Scales 12
Applying Conditional Formatting Using a Formula 13
Applying Conditional Formatting Using
Quick Analysis 15
Applying Fraction Formatting and Scientific Formatting 16
Applying Special Number Formatting 18
Creating a Custom Number Format 19
Filtering a Worksheet Using a Custom AutoFilter 21
Filtering and Sorting Data and Removing a Filter 23
Filtering and Sorting Data Using
Conditional Formatting or Cell Attributes 23
Removing a Filter 24
Defining a Custom Sort 24
Applying an Advanced Filter 26
Chapter Summary 29
Chapter 2 Advanced Functions and Formulas 31
Managing Range Names 32
Using Statistical Functions 33
Using Statistical Functions: COUNTIF and COUNTIFS 34
Using Statistical Functions: AVERAGIF and AVERAGIFS 38
Using Math and Trigonometry Functions: SUMIF and SUMIFS 41
Using Lookup Functions 43
Using the VLOOKUP Function 43
Using the HLOOKUP Function 46
Using the PPMT Financial Function 46
Using and Nesting Logical Functions 49
Using the Nested IF Logical Function 49
Using Logical Functions: Nested IF, AND, and OR 50
Using the ROUND Function 51
Using the IFS Logical Function 54
Viewing Long Formulas in the Formula Bar 54
Chapter Summary 57
Chapter 3 Working with Tables and
Data Features 59
Formatting Data as a Table 60
Modifying a Table 61
Applying Table Styles, Table Style Options,
and Table Properties 63
Sorting and Filtering a Table 65
Working with Data Tools 68
Separating Data Using Text to Columns 68
Identifying and Removing Duplicate Records 69
Validating Data Entry 71
Converting a Table to a Normal Range and
Subtotaling Related Data 76
Modifying Subtotals 79
Selecting Data from Different Outline Levels 80
Grouping and Ungrouping Data 81
Chapter Summary 82
Chapter 4 Summarizing and Consolidating Data 85
Summarizing Data in Multiple Worksheets Using
Range Names and 3-D References 86
Summarizing Data by Linking to Ranges
in Other Worksheets or Workbooks 89
Maintaining External References 90
Summarizing Data Using the Consolidate Feature 93
Creating PivotTables 95
Creating a Recommended PivotTable 96
Building a PivotTable 96
Formatting and Filtering a PivotTable 100
Changing the PivotTable Summary Function 101
Filtering a PivotTable Using Slicers 102
Filtering a PivotTable Using a Timeline 104
Creating a PivotChart 106
Summarizing Data with Sparklines 110
Creating Sparklines 110
Customizing Sparklines 111
Chapter Summary 113
Unit 2 Managing and Integrating Data
and the Excel Environment 115
Chapter 5 Using Data Analysis Features 117
Pasting Data Using Paste Special Options 118
Selecting Other Paste Special Options 119
Transposing Data 119
Performing a Mathematical Operation While Pasting 121
Using Goal Seek to Populate Cells 122
Adding, Editing, and Applying Scenarios 124
Applying a Scenario 126
Editing a Scenario 126
Deleting a Scenario 127
Generating a Scenario Summary Report 128
Performing What-If Analysis Using Data Tables 129
Creating a One-Variable Data Table 129
Creating a Two-Variable Data Table 131
Using Auditing Tools 132
Tracing Precedent and Dependent Cells 133
Troubleshooting Formulas 134
Circling Invalid Data 137
Watching a Formula Cell 137
Chapter Summary 139
Chapter 6 Exporting, Importing,
and Transforming Data 143
Exporting Data from Excel 144
Copying and Pasting Data into Word 144
Breaking a Link to an Excel Object 147
Copying and Pasting Data into PowerPoint 147
Copying and Pasting Data into Access 150
Exporting a Worksheet as a Text File 151
Importing Data into Excel 154
Importing Data from Access 154
Modifying Data with the Power Query Editor 156
Importing Data from a Text File 158
Refreshing, Modifying, and Deleting Queries 160
Editing or Removing the Source for a Query 163
Transforming Data Using Flash Fill 164
Using Text Functions 165
Chapter Summary 170
Chapter 7 Automating Repetitive Tasks
and Customizing Excel 173
Changing Display Options 174
Minimizing the Ribbon 175
Customizing Ribbons and the Quick Access Toolbar 176
Exporting and Importing Customizations 177
Customizing the Ribbon 177
Customizing the Quick Access Toolbar 181
Resetting the Ribbons and the
Quick Access Toolbar 183
Creating and Applying a Custom View 184
Automating Tasks Using Macros 186
Creating a Macro 186
Saving Workbooks Containing Macros 187
Running a Macro 189
Assigning a Macro to a Shortcut Key 190
Editing a Macro 192
Managing and Deleting Macros 194
Inserting and Configuring Form Controls 194
Inserting Form Controls 195
Configuring Form Controls 195
Creating a Macro Button Form Control 197
Saving a Workbook as a Template 198
Using a Custom Template 199
Deleting a Custom Template 200
Customizing Save Options 201
Viewing Trust Center Settings 204
Chapter Summary 206
Chapter 8 Protecting and Distributing
a Workbook 209
Adding Workbook Properties 210
Protecting and Unprotecting Worksheets 213
Protecting and Unprotecting the Structure
of a Workbook 216
Adding and Removing a Password to Open
a Workbook 217
Preparing a Workbook for Distribution 220
Checking for Accessibility Issues 220
Inspection Results 221
Inspecting a Workbook and Removing
Information before Distributing It 222
Marking a Workbook as Final 226
Using the Compatibility Checker 228
Distributing Workbooks 230
Publishing a Workbook as a PDF File 230
Publishing a Workbook as an XPS File 232
Publishing a Worksheet as a Web Page 233
Exporting and Importing XML Data 235
Creating an XML Schema 235
Exporting a Worksheet as an XML File 236
Importing an XML File 239
Chapter Summary 240
Excel Level 2 Index 243