Excel 2016 Tips and Tricks

First page Table of contents Next page Last page

Copyright & Trademarks	6
Disclaimer	6
Lesson 1 - Using Conditional and Custom Formats	11
Applying Conditional Formats	12
 Discussion	12
 Procedures	13
 Step-by-Step	13
Changing a Conditional Format	14
 Discussion	14
 Procedures	15
 Step-by-Step	16
Adding a Conditional Format	17
 Discussion	17
 Procedures	17
 Step-by-Step	18
Creating a Custom Conditional Format	19
 Discussion	19
 Procedures	19
 Step-by-Step	19
Using Data Bars	21
 Discussion	21
 Procedures	21
 Step-by-Step	22
Deleting a Conditional Format	23
 Discussion	23
 Procedures	23
 Step-by-Step	23
Creating a Custom Number Format	24
 Discussion	24
 Procedures	25
 Step-by-Step	26
Exercise	28
Using Conditional and Custom Formats	28
 Task	28
Lesson 2 - Working with Tables	29
Using Tables	30
Creating a Table from Existing Data	30
 Discussion	30
 Procedures	32
 Step-by-Step	32
Changing the Table Name	33
 Discussion	33
 Procedures	34
 Step-by-Step	34
Changing the Table Style	35
 Discussion	35
 Procedures	36
 Step-by-Step	36
Changing Table Style Options	36
 Discussion	36
 Procedures	37
 Step-by-Step	38
Creating a Total Row	39
 Discussion	39
 Procedures	41
 Step-by-Step	41
Adding Table Rows and Columns	42
 Discussion	42
 Procedures	44
 Step-by-Step	44
Inserting/Deleting Table Rows/Columns	45
 Discussion	45
 Procedures	46
 Step-by-Step	46
Creating a Calculated Column	47
 Discussion	47
 Procedures	47
 Step-by-Step	47
Selecting Parts of a Table	48
 Discussion	48
 Procedures	50
 Step-by-Step	50
Moving a Table	51
 Discussion	51
 Procedures	52
 Step-by-Step	52
Sorting Data by Multiple Levels	53
 Discussion	53
 Procedures	54
 Step-by-Step	55
Using Text Filters	57
 Discussion	57
 Procedures	58
 Step-by-Step	59
Using Number Filters	60
 Discussion	60
 Procedures	61
 Step-by-Step	62
Using Data Validation	63
 Discussion	63
 Procedures	64
 Step-by-Step	65
Validating Data using a List	66
 Discussion	66
 Procedures	67
 Step-by-Step	68
Creating a Custom Error Message	69
 Discussion	69
 Procedures	70
 Step-by-Step	70
Removing Data Validation	72
 Discussion	72
 Procedures	72
 Step-by-Step	72
Exercise	74
Working with Tables	74
 Task	74
Lesson 3 - Using Advanced Functions	77
Using Lookup Functions	78
 Discussion	78
Using the VLOOKUP Function	78
 Discussion	78
 Procedures	80
 Step-by-Step	80
Using the HLOOKUP Function	82
 Discussion	82
 Procedures	83
 Step-by-Step	84
Using the IF Function	85
 Discussion	85
 Procedures	87
 Step-by-Step	87
Using Nested IF Functions	89
 Discussion	89
 Procedures	90
 Step-by-Step	90
Using the ISERROR Function	92
 Discussion	92
 Procedures	93
 Step-by-Step	93
Using an AND Condition with IF	95
 Discussion	95
 Procedures	95
 Step-by-Step	96
Using an OR Condition with IF	97
 Discussion	97
 Procedures	98
 Step-by-Step	98
Using the ROUND Function	100
 Discussion	100
 Procedures	101
 Step-by-Step	101
Limiting the Precision of Numbers	102
 Discussion	102
 Procedures	103
 Step-by-Step	103
Exercise	105
Using Advanced Functions	105
 Task	105
Lesson 4 - Creating/Revising PivotTables	107
Recommended PivotTables	108
 Discussion	108
 Procedures	108
 Step-by-Step	109
Creating a PivotTable Report	109
 Discussion	109
 Procedures	110
 Step-by-Step	110
Adding PivotTable Report Fields	111
 Discussion	111
 Procedures	112
 Step-by-Step	113
Search for PivotTable Report Field	114
 Discussion	114
 Procedures	115
 Step-by-Step	115
Selecting a Report Filter Field Item	115
 Discussion	115
 Procedures	116
 Step-by-Step	116
Refreshing a PivotTable Report	117
 Discussion	117
 Procedures	117
 Step-by-Step	117
Changing the Summary Function	118
 Discussion	118
 Procedures	119
 Step-by-Step	119
Adding New Fields to a PivotTable Report	120
 Discussion	120
 Procedures	120
 Step-by-Step	120
Moving PivotTable Report Fields	121
 Discussion	121
 Procedures	122
 Step-by-Step	122
Using Expand and Collapse Buttons	123
 Discussion	123
 Procedures	124
 Step-by-Step	124
Hiding/Unhiding PivotTable Report Items	125
 Discussion	125
 Procedures	125
 Step-by-Step	125
Deleting PivotTable Report Fields	126
 Discussion	126
 Procedures	126
 Step-by-Step	127
Creating Report Filter Pages	127
 Discussion	127
 Procedures	128
 Step-by-Step	128
Formatting a PivotTable Report	129
 Discussion	129
 Procedures	130
 Step-by-Step	130
Creating Slicers	131
 Discussion	131
 Procedures	132
 Step-by-Step	132
Filtering with Slicers	133
 Discussion	133
 Procedures	134
 Step-by-Step	134
Editing a Slicer	135
 Discussion	135
 Procedures	135
 Step-by-Step	136
Using Multi-Select Slicers	137
 Discussion	137
 Procedures	137
 Step-by-Step	137
Creating a PivotChart Report	138
 Discussion	138
 Procedures	139
 Step-by-Step	139
Exercise	141
Creating/Revising PivotTables	141
 Task	141

Made with FlippingBook flipbook maker