## 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