Excel 2016 Tips and Tricks

Removing Data Validation ......................................................................................62 Exercise...................................................................................................................64 USING ADVANCED FUNCTIONS.....................................................67 Using Lookup Functions..........................................................................................68 Using the VLOOKUP Function.................................................................................68 Using the HLOOKUP Function.................................................................................72 Using the IF Function..............................................................................................75 Using Nested IF Functions ......................................................................................79 Using the ISERROR Function...................................................................................82 Using an AND Condition with IF .............................................................................85 Using an OR Condition with IF................................................................................87 Using the ROUND Function ....................................................................................90 Limiting the Precision of Numbers .........................................................................92 Exercise...................................................................................................................95 CREATING/REVISING PIVOTTABLES...............................................97 Recommended PivotTables ....................................................................................98 Creating a PivotTable Report..................................................................................99 Adding PivotTable Report Fields ..........................................................................101 Search for PivotTable Report Field .......................................................................104 Selecting a Report Filter Field Item ......................................................................105 Refreshing a PivotTable Report ............................................................................107 Changing the Summary Function .........................................................................108 Adding New Fields to a PivotTable Report ...........................................................110 Moving PivotTable Report Fields..........................................................................111 Using Expand and Collapse Buttons .....................................................................113 Hiding/Unhiding PivotTable Report Items............................................................115 Deleting PivotTable Report Fields ........................................................................116 Creating Report Filter Pages.................................................................................117 Formatting a PivotTable Report ...........................................................................119 Creating Slicers .....................................................................................................121 Filtering with Slicers .............................................................................................123 Editing a Slicer ......................................................................................................125

LESSON 3 -

LESSON 4 -

Page iv

OFFICEPRO, Inc.