Excel 2016 Data and List Management

Table of Contents

ABOUT OFFICEPRO – WE CHANGE LIVES! ...............................................................I

WHAT TO EXPECT FROM OFFICEPRO?...................................................................II

TABLE OF CONTENTS ...........................................................................................III

LESSON 1 -

USING ADVANCED FUNCTIONS.......................................................1 Using Lookup Functions............................................................................................2 Using the VLOOKUP Function...................................................................................2 Using the HLOOKUP Function...................................................................................6 Using the IF Function................................................................................................9 Using Nested IF Functions ......................................................................................13 Using the ISERROR Function...................................................................................16 Using an AND Condition with IF .............................................................................19 Using an OR Condition with IF................................................................................21 Using the ROUND Function ....................................................................................24 Limiting the Precision of Numbers .........................................................................26 Exercise...................................................................................................................29 CREATING/REVISING PIVOTTABLES...............................................31 Recommended PivotTables....................................................................................32 Creating a PivotTable Report..................................................................................33 Adding PivotTable Report Fields ............................................................................35 Search for PivotTable Report Field.........................................................................38 Selecting a Report Filter Field Item ........................................................................39 Refreshing a PivotTable Report ..............................................................................41 Changing the Summary Function ...........................................................................42 Adding New Fields to a PivotTable Report .............................................................44 Moving PivotTable Report Fields............................................................................45 Using Expand and Collapse Buttons .......................................................................47 Hiding/Unhiding PivotTable Report Items..............................................................49 Deleting PivotTable Report Fields ..........................................................................50 Creating Report Filter Pages...................................................................................51 Formatting a PivotTable Report .............................................................................53

LESSON 2 -

OFFICEPRO, Inc.

Page iii