Benchmark Series: Microsoft Excel 365 Levels 1 and 2

666 Pages·2019·27.218 MB·English

by  Nita Rutkosky

Levels 1& 2
BENCHMARK SERIES
Microsoft® Excel®
2019 Edition
Nita Rutkosky Jan Davidson Audrey Roggenkamp Ian Rutkosky
Lambton College Pierce College Puyallup Pierce College Puyallup
Sarnia, Ontario Puyallup, Washington Puyallup, Washington
Rutkosky | Davidson | Roggenkamp | Rutkosky
B00Mi-O00ffxicvei-3B6M5-K2306159_eE_xCcverlsL_1F-Lro2n-Ft.Mind.indd d 3 1 47/2/293/1/199 1 12::2347 PPMM
Vice President, Content and Digital Solutions: Christine Hurney
Director of Content Development: Carley Fruzzetti
Developmental Editor: Jennifer Joline Anderson
Director of Production: Timothy W. Larson
Production Editor/Project Manager: Jen Weaverling
Senior Design and Production Specialist: Jack Ross
Cover and Interior Design: Valerie King
Copy Editor: Communicáto, Ltd.
Testers: Janet Blum, Traci Post
Indexer: Terry Casey
Vice President, Director of Digital Products: Chuck Bratton
Digital Projects Manager: Tom Modl
Digital Solutions Manager: Gerry Yumul
Senior Director of Digital Products and Onboarding: Christopher Johnson
Supervisor of Digital Products and Onboarding: Ryan Isdahl
Vice President, Marketing: Lara Weber McLellan
Marketing and Communications Manager: Selena Hicks
Care has been taken to verify the accuracy of information presented in this book. However, the authors, editors, and publisher
cannot accept responsibility for web, email, newsgroup, or chat room subject matter or content, or for consequences from the
application of the information in this book, and make no warranty, expressed or implied, with respect to its content.
Trademarks: Microsoft is a trademark or registered trademark of Microsoft Corporation in the United States and/or other
countries. Some of the product names and company names included in this book have been used for identification purposes
only and may be trademarks or registered trade names of their respective manufacturers and sellers. The authors, editors, and
publisher disclaim any affiliation, association, or connection with, or sponsorship or endorsement by, such owners.
Paradigm Education Solutions is independent from Microsoft Corporation and not affiliated with Microsoft in any manner.
Cover Photo Credit: © lowball-jack/GettyImages
Interior Photo Credits: Follow the Index.
We have made every effort to trace the ownership of all copyrighted material and to secure permission from copyright
holders. In the event of any question arising as to the use of any material, we will be pleased to make the necessary corrections
in future printings.
ISBN 978-0-76388-722-3 (print)
ISBN 978-0-76388-709-4 (digital)
© 2020 by Paradigm Publishing, LLC
875 Montreal Way
St. Paul, MN 55102
Email: [email protected]
Website: ParadigmEducation.com
All rights reserved. No part of this publication may be adapted, reproduced, stored in a retrieval system, or transmitted in any
form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without prior written permission from
the publisher.
Printed in the United States of America
28 27 26 25 24 23 22 21 20 19 1 2 3 4 5 6 7 8 9 10 11 12
00i-00xvi-BMK365_ExcelL1-L2-FM.indd 2 7/23/19 1:24 PM
Brief 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
Chapter 2 Inserting Formulas in a Worksheet 31
Chapter 3 Formatting a Worksheet 51
Chapter 4 Enhancing a Worksheet 79
Unit 2 Enhancing the Display of Workbooks 113
Chapter 5 Moving Data within and between Workbooks 115
Chapter 6 Maintaining Workbooks 143
Chapter 7 Creating Charts and Inserting Formulas 177
Chapter 8 Adding Visual Interest to Workbooks 207
Excel Level 1 Index 233
Microsoft Excel Level 2
Unit 1 Advanced Formatting, Formulas,
and Data Management 1
Chapter 1 Advanced Formatting Techniques 3
Chapter 2 Advanced Functions and Formulas 31
Chapter 3 Working with Tables and Data Features 59
Chapter 4 Summarizing and Consolidating Data 85
Unit 2 Managing and Integrating Data
and the Excel Environment 115
Chapter 5 Using Data Analysis Features 117
Chapter 6 Exporting, Importing, and Transforming Data 143
Chapter 7 Automating Repetitive Tasks
and Customizing Excel 173
Chapter 8 Protecting and Distributing a Workbook 209
Excel Level 2 Index 243
iii
00i-00xvi-BMK365_ExcelL1-L2-FM.indd 3 7/23/19 1:24 PM
00i-00xvi-BMK365_ExcelL1-L2-FM.indd 4 7/23/19 1:24 PM
Contents
Preface ix Chapter 3 Formatting a Worksheet 51
Getting Started GS-1 Changing Column Width 52
Microsoft Excel Level 1 Changing Column Width Using Column
Boundaries 52
Unit 1 Preparing and Formatting Worksheets 1
Changing Column Width at the Column Width
Chapter 1 Preparing an Excel Workbook 3 Dialog Box 53
Changing Row Height 54
Creating a Worksheet 4
Inserting and Deleting Cells, Rows, and Columns 55
Entering Data in a Worksheet 6
Inserting Rows 55
Saving a Workbook 7
Inserting Columns 56
Editing Data in a Cell 9
Deleting Cells, Rows, or Columns 57
Printing a Worksheet 10
Clearing Data in Cells 58
Closing a Workbook and Closing Excel 11
Applying Formatting 58
Using Automatic Entering Features 12
Applying Font Formatting 59
Using AutoComplete 12
Formatting with the Mini Toolbar 59
Using AutoCorrect 12
Applying Alignment Formatting 59
Using AutoFill 14
Applying a Theme 62
Opening a Workbook 15
Formatting Numbers 63
Opening a Workbook from the Recent Option List 15
Formatting Numbers Using Number Group Buttons 63
Pinning and Unpinning Workbooks and Folders 15
Applying Number Formatting at the
Entering Formulas 17
Format Cells Dialog Box 65
Using the AutoSum Button to Add Numbers 17
Applying Formatting Using the Format Cells Dialog Box 67
Using the AutoSum Button to Average Numbers 18
Aligning and Indenting Data 67
Using the Fill Handle to Copy a Formula 18
Changing the Font 69
Selecting Cells 19
Adding Borders to Cells 71
Selecting Cells Using the Mouse 19
Adding Fill and Shading to Cells 73
Selecting Cells Using the Keyboard 19
Repeating the Last Action 73
Selecting Data within Cells 20
Formatting with Format Painter 74
Applying Basic Formatting 20
Hiding and Unhiding Columns and Rows 75
Changing Column Width 20
Chapter Summary 77
Merging and Centering Cells 21
Formatting Numbers 22 Chapter 4 Enhancing a Worksheet 79
Using the Tell Me Feature 24
Formatting a Worksheet Page 80
Using Help 26
Changing Margins 80
Getting Help from a ScreenTip 27
Centering a Worksheet Horizontally and/or Vertically 81
Getting Help in a Dialog Box or at the
Changing Page Orientation 82
Backstage Area 28
Changing the Paper Size 82
Chapter Summary 28
Inserting and Removing Page Breaks 83
Chapter 2 Inserting Formulas in a Worksheet 31 Printing Column and Row Titles on Multiple Pages 86
Scaling Data 87
Writing Formulas with Mathematical Operators 32
Inserting a Background Picture 88
Copying a Formula with Relative Cell References 32
Printing Gridlines and Row and Column Headings 89
Checking Cell References in a Formula 33
Printing a Specific Area of a Worksheet 89
Writing a Formula by Pointing 34
Inserting Headers and Footers 91
Determining the Order of Operations 35
Customizing Print Jobs 96
Using the Trace Error Button 36
Checking Spelling 97
Identifying Common Formula Errors 36
Using Undo and Redo 97
Inserting Formulas with Functions 38
Finding and Replacing Data and Cell Formatting 99
Writing Formulas with Statistical Functions 40
Sorting Data 104
Writing Formulas with the NOW and
Completing a Custom Sort 104
TODAY Functions 44
Sorting More Than One Column 106
Displaying Formulas 44
Filtering Data 106
Using Absolute and Mixed Cell References in Formulas 45
Chapter Summary 109
Using an Absolute Cell Reference in a Formula 45
Using a Mixed Cell Reference in a Formula 47
Chapter Summary 48
v
00i-00xvi-BMK365_ExcelL1-L2-FM.indd 5 7/23/19 1:24 PM
Unit 2 Enhancing the Display of Workbooks 113 Inserting and Managing Comments 166
Inserting a New Comment 166
Chapter 5 Moving Data within
Posting a Comment 166
and between Workbooks 115
Editing and Deleting a Comment 168
Creating a Workbook with Multiple Worksheets 116 Viewing and Managing Comments at the
Inserting a New Worksheet 116 Comments Task Pane 168
Deleting a Worksheet 116 Writing Formulas with Financial Functions 170
Selecting Multiple Worksheets 116 Finding the Periodic Payments for a Loan 171
Copying, Cutting, and Pasting Cells 117 Finding the Future Value of a Series of Payments 173
Copying and Pasting Selected Cells 117 Chapter Summary 174
Using Paste Options 118
Chapter 7 Creating Charts
Moving Selected Cells 119
and Inserting Formulas 177
Copying and Pasting Using the Clipboard
Creating a Chart 178
Task Pane 120
Sizing and Moving a Chart 179
Pasting Values Only 122
Editing Data and Adding a Data Series 180
Managing Worksheets 123
Formatting with Chart Buttons 182
Hiding and Unhiding a Worksheet in a Workbook 125
Printing a Chart 184
Printing a Workbook Containing Multiple
Changing the Chart Design 185
Worksheets 126
Changing the Chart Style 186
Changing the Zoom 127
Switching Rows and Columns 186
Splitting a Worksheet and Freezing
Changing Chart Layout and Colors 187
and Unfreezing Panes 128
Changing the Chart Location 188
Naming and Using a Range 130
Adding, Moving, and Deleting Chart Elements 188
Working with Windows 131
Changing Chart Formatting 191
Opening Multiple Workbooks 132
Formatting a Selection 191
Arranging Workbooks 132
Inserting a Shape 192
Hiding and Unhiding Workbooks 134
Creating Alternative Text for an Image 193
Sizing and Moving Workbooks 135
Using the Quick Analysis Feature 194
Moving, Linking, Copying and Pasting Data
Applying Formatting at a Task Pane 195
between Workbooks 135
Changing Chart Height and Width Measurements 196
Moving and Copying Data 136
Deleting a Chart 198
Linking Data 137
Writing Formulas with the Logical IF Function 200
Copying and Pasting Data between Programs 138
Writing Formulas with an IF Function Using
Chapter Summary 139
the Function Arguments Dialog Box 201
Chapter 6 Maintaining Workbooks 143
Writing IF Statements Containing Text 203
Managing the Recent Option List 144
Chapter Summary 204
Pinning and Unpinning a Workbook 145
Chapter 8 Adding Visual Interest
Recovering an Unsaved Workbook 145
to Workbooks 207
Clearing the Recent Option List
and the Recent List 145 Inserting Symbols and Special Characters 208
Managing Worksheets 146 Inserting an Image 210
Copying a Worksheet to Another Workbook 146 Customizing and Formatting an Image 210
Moving a Worksheet to Another Workbook 148 Sizing and Moving an Image 210
Formatting with Cell Styles 150 Formatting an Image at the Format Picture
Applying a Cell Style 150 Task Pane 212
Defining a Cell Style 151 Inserting an Online Image 212
Modifying a Cell Style 155 Creating and Inserting a Screenshot 214
Copying Cell Styles to Another Workbook 156 Inserting and Formatting a Shape 215
Removing a Cell Style 157 Inserting and Modifying Text Boxes 218
Deleting a Cell Style 157 Inserting and Customizing Icons 218
Inserting Hyperlinks 158 Inserting and Customizing 3D Models 221
Linking to an Existing Web Page or File 158 Inserting a SmartArt Graphic 224
Navigating Using Hyperlinks 159 Entering Data in a SmartArt Graphic 224
Linking to a Place in the Workbook 160 Sizing, Moving, and Deleting a SmartArt Graphic 225
Linking to a New Workbook 160 Changing the SmartArt Graphic Design 226
Linking Using a Graphic 161 Changing the SmartArt Graphic Formatting 227
Linking to an Email Address 161 Creating, Sizing, and Moving WordArt 228
Modifying, Editing, and Removing a Hyperlink 162 Chapter Summary 230
Using Excel Templates 163 Excel Level 1 Index 233
vi Excel Levels 1 & 2 | Contents
00i-00xvi-BMK365_ExcelL1-L2-FM.indd 6 7/23/19 1:24 PM
Microsoft Excel Level 2 Converting a Table to a Normal Range and
Subtotaling Related Data 76
Unit 1 Advanced Formatting, Formulas,
Modifying Subtotals 79
and Data Management 1
Selecting Data from Different Outline Levels 80
Chapter 1 Advanced Formatting Techniques 3 Grouping and Ungrouping Data 81
Applying Conditional Formatting 4 Chapter Summary 82
Applying Conditional Formatting Using a New Rule 6
Chapter 4 Summarizing and
Editing and Deleting a Conditional Formatting Rule 8
Consolidating Data 85
Applying Conditional Formatting Using an Icon Set 11
Summarizing Data in Multiple Worksheets Using
Applying Conditional Formatting Using Data Bars
Range Names and 3-D References 86
and Color Scales 12
Summarizing Data by Linking to Ranges
Applying Conditional Formatting Using a Formula 13
in Other Worksheets or Workbooks 89
Applying Conditional Formatting Using
Maintaining External References 90
Quick Analysis 15
Summarizing Data Using the Consolidate Feature 93
Applying Fraction Formatting and Scientific Formatting 16
Creating PivotTables 95
Applying Special Number Formatting 18
Creating a Recommended PivotTable 96
Creating a Custom Number Format 19
Building a PivotTable 96
Filtering a Worksheet Using a Custom AutoFilter 21
Formatting and Filtering a PivotTable 100
Filtering and Sorting Data and Removing a Filter 23
Changing the PivotTable Summary Function 101
Filtering and Sorting Data Using
Filtering a PivotTable Using Slicers 102
Conditional Formatting or Cell Attributes 23
Filtering a PivotTable Using a Timeline 104
Removing a Filter 24
Creating a PivotChart 106
Defining a Custom Sort 24
Summarizing Data with Sparklines 110
Applying an Advanced Filter 26
Creating Sparklines 110
Chapter Summary 29
Customizing Sparklines 111
Chapter 2 Advanced Functions and Formulas 31 Chapter Summary 113
Managing Range Names 32
Unit 2 Managing and Integrating Data
Using Statistical Functions 33
and the Excel Environment 115
Using Statistical Functions: COUNTIF
and COUNTIFS 34 Chapter 5 Using Data Analysis Features 117
Using Statistical Functions: AVERAGIF Pasting Data Using Paste Special Options 118
and AVERAGIFS 38 Selecting Other Paste Special Options 119
Using Math and Trigonometry Functions: SUMIF Transposing Data 119
and SUMIFS 41 Performing a Mathematical Operation
Using Lookup Functions 43 While Pasting 121
Using the VLOOKUP Function 43 Using Goal Seek to Populate Cells 122
Using the HLOOKUP Function 46 Adding, Editing, and Applying Scenarios 124
Using the PPMT Financial Function 46 Applying a Scenario 126
Using and Nesting Logical Functions 49 Editing a Scenario 126
Using the Nested IF Logical Function 49 Deleting a Scenario 127
Using Logical Functions: Nested IF, AND, and OR 50 Generating a Scenario Summary Report 128
Using the ROUND Function 51 Performing What-If Analysis Using Data Tables 129
Using the IFS Logical Function 54 Creating a One-Variable Data Table 129
Viewing Long Formulas in the Formula Bar 54 Creating a Two-Variable Data Table 131
Chapter Summary 57 Using Auditing Tools 132
Tracing Precedent and Dependent Cells 133
Chapter 3 Working with Tables and
Troubleshooting Formulas 134
Data Features 59
Circling Invalid Data 137
Formatting Data as a Table 60
Watching a Formula Cell 137
Modifying a Table 61
Chapter Summary 139
Applying Table Styles, Table Style Options,
and Table Properties 63 Chapter 6 Exporting, Importing,
Sorting and Filtering a Table 65 and Transforming Data 143
Working with Data Tools 68 Exporting Data from Excel 144
Separating Data Using Text to Columns 68 Copying and Pasting Data into Word 144
Identifying and Removing Duplicate Records 69 Breaking a Link to an Excel Object 147
Validating Data Entry 71 Copying and Pasting Data into PowerPoint 147
Excel Levels 1 & 2 | Contents vii
00i-00xvi-BMK365_ExcelL1-L2-FM.indd 7 7/23/19 1:24 PM
Copying and Pasting Data into Access 150 Creating a Macro Button Form Control 197
Exporting a Worksheet as a Text File 151 Saving a Workbook as a Template 198
Importing Data into Excel 154 Using a Custom Template 199
Importing Data from Access 154 Deleting a Custom Template 200
Modifying Data with the Power Query Editor 156 Customizing Save Options 201
Importing Data from a Text File 158 Viewing Trust Center Settings 204
Refreshing, Modifying, and Deleting Queries 160 Chapter Summary 206
Editing or Removing the Source for a Query 163
Chapter 8 Protecting and Distributing
Transforming Data Using Flash Fill 164
a Workbook 209
Using Text Functions 165
Chapter Summary 170 Adding Workbook Properties 210
Protecting and Unprotecting Worksheets 213
Chapter 7 Automating Repetitive Tasks Protecting and Unprotecting the Structure
and Customizing Excel 173 of a Workbook 216
Changing Display Options 174 Adding and Removing a Password to Open
Minimizing the Ribbon 175 a Workbook 217
Customizing Ribbons and the Quick Access Toolbar 176 Preparing a Workbook for Distribution 220
Exporting and Importing Customizations 177 Checking for Accessibility Issues 220
Customizing the Ribbon 177 Inspection Results 221
Customizing the Quick Access Toolbar 181 Inspecting a Workbook and Removing
Resetting the Ribbons and the Information before Distributing It 222
Quick Access Toolbar 183 Marking a Workbook as Final 226
Creating and Applying a Custom View 184 Using the Compatibility Checker 228
Automating Tasks Using Macros 186 Distributing Workbooks 230
Creating a Macro 186 Publishing a Workbook as a PDF File 230
Saving Workbooks Containing Macros 187 Publishing a Workbook as an XPS File 232
Running a Macro 189 Publishing a Worksheet as a Web Page 233
Assigning a Macro to a Shortcut Key 190 Exporting and Importing XML Data 235
Editing a Macro 192 Creating an XML Schema 235
Managing and Deleting Macros 194 Exporting a Worksheet as an XML File 236
Inserting and Configuring Form Controls 194 Importing an XML File 239
Inserting Form Controls 195 Chapter Summary 240
Configuring Form Controls 195 Excel Level 2 Index 243
viii Excel Levels 1 & 2 | Contents
00i-00xvi-BMK365_ExcelL1-L2-FM.indd 8 7/23/19 1:24 PM
Preface
Achieving Proficiency in Excel
The Benchmark Series, Microsoft® Excel® 365, 2019 Edition, is designed for students
who want to learn how to use Microsoft’s powerful spreadsheet program to
manipulate numerical data in resolving financial and other problems requiring data
management and analysis. No prior knowledge of spreadsheets is required. After
successfully completing a course in Microsoft Excel using this courseware, students
will be able to do the following:
• Create and edit spreadsheets and worksheets of varying complexity.
• Format cells, columns, and rows as well as entire workbooks in a uniform,
attractive style.
• Analyze numerical data and project outcomes to make informed decisions.
• Plan, research, create, revise, and publish worksheets and workbooks to meet
specific needs.
• Given a workplace scenario requiring a numbers-based solution, assess the
information requirements and then prepare the materials that achieve the goal
efficiently and effectively.
Well-designed pedagogy is important, but students learn technology skills
through practice and problem solving. Technology provides opportunities for
interactive learning as well as excellent ways to quickly and accurately assess
student performance. To this end, this course is supported with Cirrus, Paradigm’s
cloud-based training and assessment learning management system. Details about
Cirrus as well as its integrated student courseware and instructor resources can be
found on page xii.
Proven Instructional Design
The Benchmark Series has long served as a MicrosofEt® xcel®
standard of excellence in software instruction. Level 1
Elements of the series function individually
and collectively to create an
inviting, comprehensive learning
Unit 1
environment that leads to
MicrosofEt® xcPrepearing anld ®Formatting Worksheets
full proficiency in computer
Chapter 1 PrLepeavriengl a2n Excel Workbook
applications. The following visual
Chapter 2 Inserting Formulas in a Worksheet
tour highlights the structure and Chapter 3 Formatting a Worksheet
features that comprise the highly Chapter 4 Enhancing a Worksheet
popular Benchmark model. Unit 1
Advanced Formatting, Formulas,
and Data Management
Chapter 1 Advanced Formatting Techniques
Chapter 2 Advanced Functions and Formulas
Chapter 3 Working with Tables and Data Features
Chapter 4 Summarizing and Consolidating Data 1
Unit Openers display the unit’s four chapter
titles. Each level of the course contains two
1
units with four chapters each.
ix
00i-00xvi-BMK365_ExcelL1-L2-FM.indd 9 7/23/19 1:24 PM
Chapter Openers Present Learning Objectives
Chapter Openers present the performance objectives and an
EMicrosxoft® cel® overview of the skills taught.
CHAPTER
2 Data Files are provided for each chapter.
Advanced Functions
and Formulas
Performance Objectives
Upon successful completion of Chapter 2, you will be able to:
1 Create and manage names for ranges of cells
2 Write formulas with the COUNTBLANK, COUNTIF, and COUNTIFS
statistical functions
3 Write formulas with the AVERAGEIF and AVERAGEIFS statistical
functions
4 Write formulas with the SUMIF and SUMIFS math and trigonometry
functions
5 Write formulas with the VLOOKUP and HLOOKUP lookup functions
6 Write formulas with the PPMT financial function
7 Write formulas with the nested IF, AND, and OR logical functions and
the IFS logical function
8 Write formulas with the ROUND math and trigonometry function
9 Expand the Formula bar to view longer formulas
To help make complex calculations easier to perform, Excel provides numerous
preset formulas called functions. These are grouped into thirteen different categories
to facilitate calculations in worksheets containing financial, logical, mathematical,
statistical, or other types of data. The Insert function dialog box provides options
for locating and building formulas with various functions. The structure of a
function formula includes an equals sign (=) followed by the name of the function
and then the function argument. Argument is the term given to the values to be
included in the calculation. The structure of the argument is dependent on the type
of function being used and can include references to values in a single cell, a range,
multiple ranges, or any combination of these.
Data Files The online
Bsteofroargee b megeidninuimng a cnhda tphteenr wmoarkke, cEoLp2yC t2h teh Ee La2cCti2v ef oflodledre rt.o your caaodnuddri tasioes nsieanslc slturmadeiennsint g
resources.
31
Activities Build Skill Mastery within Realistic Context
Multipart Activities provide a framework for instruction and
practice on software features. An activity overview identifies
tasks to accomplish and key features to use in completing the
Activity 1 Calculate Statistics and Sums Using 6 Parts
Conditional Formulas for an Insurance Claims Worksheet work.
You will manage range names in an insurance claims worksheet and use the range
names in statistical formulas that count, average, and sum based on single and
multiple criteria. Tutorials provide interactive, guided training and measured
Tutorial Managing Range Names practice.
Managing Range Recall that a range name is a name assigned to a cell or range of cells. Range
Names names provide the option of referencing a source using a descriptive label, rather
than the cell address or range address, when creating a formula, printing a
Tutorial worksheet, or navigating in a worksheet. Creating range names makes the task of Quick Steps in the margins allow fast reference and review of
Review: Naming managing complex formulas easier and helps others who may work in or edit a
and Using a Range worksheet to understand the purpose of a formula more quickly.
Name Manager ThisB my edaenfas utlhta, ta irfa tnhgee fnorammue lais ias nc oapbiseodlu otre mreofevreedn ctoe tao d ai fcfeerlel notr lroacnagteio onf, ctehlels . the steps needed to accomplish tasks.
references will not change. Later in this chapter, when creating a lookup formula,
uick Steps take advantage of the absolute referencing of a range name when including a group
Ma12n.. oCCdd lliiiRccfyekk fRFNeoaraernmmngecue elM aNsaa ntmaabge.e r oainlfs otche ilenl scf oliunrmd tehuse lat fh oaeru mwtooumrlaka stthihceaaetlt l ysrt earfeyef refeirxneencdec ewbsy ht hednee fctahouerlr tef; octrthm weruoelrfaok risseh ,ec toeytpp. iieFndog.r Atehx eraa mrnagpnelge en, aanmsasmue me e Hints offer useful tips on how to use features efficiently and
345... bCCTyulliipccttekko nnrE.aednwitg bera unnttagomen .e. ttnhhaaem tf eocder mlPl uArol3dau iccnetB lSl.. h NTeoeo tta i2dcd eh ttahhsa ebt tetwehnoe nvwaaolmurkeessdh, Petyerpto deru etcfhteAere fanoncredms cudelolal nA=o3Pt r inonde uSecdht Aeteo+t Pb3er o hidnauscc ltbuBed eiennd . effectively.
6. nbTyaopmxe.e n ienw N raamngee t ext NamTeh Me Nanaamgee rM buanttaogne ri nd itahloe gD beofixn, esdh oNwanm iens Fgirgouurpe 2on.1 ,t hise oFpoernmeudl absy t calbic okri nbgy t he
78.. atCCedllxiidcct kkrbe OCossxlKo. .aset .Refers to uttohs eicn rngea atmtheee, keodery imtb, ooaadnriddfy dsinhegloe rtttehc eur atr naCgntegr eln +aamd Fde3rse.. sTAs h aresa snNogcaeima nteea dmM wea icntahan gi etbr. e Ad ei adraliotneggd eb bonxya mccahena c nbageni n uagsl seod
be deleted, but extra caution should be used when doing so. Cells that include a
HintThe Formulas deleted range name in the formula will display the error text #NAME? Also use the
tfarobm c oSnetlaeicntsio an Cbruettaotne Name Manager dialog box to add new range names to a worksheet.
in the Defined Names
gtor oauupt otmhaatt iccaanll yb cer euasteed Figure 2.1 Name Manager Dialog Box
range names for a list
or table. Select the list
or table and click the
button. Excel uses the
names in the top row
or left-most column as
the range names.
32 Excel Level 2 | Unit 1 Chapter 2 | Advanced Functions and Formulas
x Excel Levels 1 & 2 | Preface
00i-00xvi-BMK365_ExcelL1-L2-FM.indd 10 7/23/19 1:24 PM