Microsoft Excel 2016 Advanced Quick Reference - CustomGuide
elect the desired formatting options from the PivotTable Options group and the PivotTable Styles
Excel 2016 Advanced
016 Advanced TABLE OF CONTENTS 1 2 5 Window options 20 Locking titles 20 Splitting
Excel 2016 - Advanced
resourcePDF
Excel 2016: Advanced Excel Tools - Kennesaw State University
Microsoft Office: Excel 2016 for Windows Figure 7 - Customize the Ribbon Options
Excel 2016 Advanced - BMCC
uploadsPDF
Microsoft Excel 2016 Advanced 2 day COURSE OUTCOMES
Excel options • protect data in worksheets and workbooks • import data into Excel and export
Microsoft Excel 2016 Advanced - Academy IT
2020/10PDF
Advanced Excel 2016: - Twinsburg Public Library
Rows command in the PivotTable Style Options command group Page 5 5 Adding a field You
Excel 2016 Advanced Topics - CPE Store
CC2694 PDF
[PDF] excel erp francais methode
[PDF] excel etudiant
[PDF] exchange rate pdf
[PDF] excursii 2015 paralela 45
[PDF] exemplaire d'un contrat de travail
[PDF] exemplaire de demande d'emploi pdf
[PDF] exemplaire de demande d'emploi word
[PDF] exemplaire de lettre de motivation pour une demande d'emploi
[PDF] exemplaire de lettre de motivation pour une demande d'emploi pdf
[PDF] exemple appréciation bulletin scolaire
[PDF] exemple appréciation copie
[PDF] exemple appreciation prof
[PDF] exemple baze de date access
[PDF] exemple bibliographie
Excel 2016 Advanced Topics
Greg Harvey, PhD
iTable of Contents
Introduction ........................................................................................................................................... 1
About This Book .................................................................................................................................. 1
Foolish Assumptions ............................................................................................................................ 1
How This Book Is Organized ................................................................................................................ 2
Conventions Used in This Book ........................................................................................................... 3
Icons Used in This Book ...................................................................................................................... 5
Where to Go from Here ........................................................................................................................ 5
Chapter 1 - Protecting Workbooks and Worksheet Data .................................................................... 7
Learning Objectives ............................................................................................................................. 7
Introduction .......................................................................................................................................... 7
Password-Protecting the File ............................................................................................................... 7
Protecting the workbook when saving the file ................................................................................... 7
Assigning a password to open from the Info screen ........................................................................ 10
Entering the password to gain access ............................................................................................ 10
Entering the password to make changes ........................................................................................ 11
Changing or deleting a password ................................................................................................... 12
Protecting the Worksheet ................................................................................................................... 12
Changing a cell's Locked and Hidden Protection formatting ........................................................... 13
Protecting the worksheet................................................................................................................ 14
Enabling cell range editing by certain users .................................................................................... 17
Doing data entry in the unlocked cells of a protected worksheet ..................................................... 20
Protecting the workbook................................................................................................................. 22
Protecting a shared workbook ........................................................................................................ 22
Review Questions .............................................................................................................................. 24
Review Answers ................................................................................................................................ 25
Chapter 2 - Using Hyperlinks ............................................................................................................. 27
Learning Objectives ........................................................................................................................... 27
Introduction ........................................................................................................................................ 27
Hyperlinks 101 ................................................................................................................................... 27
Adding hyperlinks .......................................................................................................................... 28
Follow that link! .............................................................................................................................. 30
Editing hyperlinks........................................................................................................................... 30
Using the HYPERLINK Function ........................................................................................................ 31
Review Questions .............................................................................................................................. 33
Review Answers ................................................................................................................................ 34
Chapter 3 - Sending Workbooks Out for Review ............................................................................... 35
Learning Objectives ........................................................................................................................... 35
Introduction ........................................................................................................................................ 35
Preparing a Workbook for Distribution ................................................................................................ 35
Adding properties to a workbook .................................................................................................... 36
Digitally signing a document........................................................................................................... 37
Workbook Sharing 101 ...................................................................................................................... 40
Turning on file sharing ................................................................................................................... 42
Modifying the Share Workbook options .......................................................................................... 43
Turning on change tracking ............................................................................................................ 45
Merging changes from different users ............................................................................................ 48
Workbooks on Review ....................................................................................................................... 50
Adding comments .......................................................................................................................... 51
Marking up a worksheet with digital ink .......................................................................................... 53
Review Questions .............................................................................................................................. 55
Review Answers ................................................................................................................................ 57
Table of Contents
iiChapter 4 - Sharing Workbooks and Worksheet Data....................................................................... 59
Learning Objectives ........................................................................................................................... 59
Introduction ........................................................................................................................................ 59
Sharing Your Workbooks Online ........................................................................................................ 59
Sharing workbooks saved on your OneDrive .................................................................................. 60
E-mailing workbooks ...................................................................................................................... 62
Sharing workbooks with Instant Message ....................................................................................... 63
Presenting worksheets online ........................................................................................................ 63
Editing worksheets online .............................................................................................................. 64
Excel 2016 Data Sharing Basics ........................................................................................................ 66
Excel and Word 2016 ..................................................................................................................... 67
Excel and PowerPoint 2016 ........................................................................................................... 72
Exporting Workbooks to Other Usable File Formats ........................................................................... 73
Saving and exporting worksheets as PDF files ............................................................................... 73
Saving worksheets as XPS files ..................................................................................................... 74
Saving worksheets as ODS files .................................................................................................... 74
Saving worksheets as HTML files .................................................................................................. 75
Review Questions .............................................................................................................................. 77
Review Answers ................................................................................................................................ 78
Chapter 5 - Building and Maintaining Data Lists ............................................................................... 81
Learning Objectives ........................................................................................................................... 81
Introduction ........................................................................................................................................ 81
Data List Basics ................................................................................................................................. 81
Designing the basic data list........................................................................................................... 81
Add new records to a data list ........................................................................................................ 84
Eliminating records with duplicate fields ......................................................................................... 88
Sorting Data ...................................................................................................................................... 89
Sorting records on a single field ..................................................................................................... 89
Sorting records on multiple fields ................................................................................................... 90
Sorting the columns of a data list ................................................................................................... 93
Sorting a data list on font and fill colors and cell icons .................................................................... 96
Subtotaling Data ................................................................................................................................ 96
Review Questions .............................................................................................................................. 99
Review Answers .............................................................................................................................. 100
Chapter 6 - Filtering and Querying a Data List ................................................................................ 103
Learning Objectives ......................................................................................................................... 103
Introduction ...................................................................................................................................... 103
Data List Filtering 101 ...................................................................................................................... 103
Filtering Data ................................................................................................................................... 104
Using AutoFilter ........................................................................................................................... 104
Using the Advanced Filter ............................................................................................................ 111
Using the Database Functions ......................................................................................................... 117
External Data Query ........................................................................................................................ 119
Retrieving data from Access database tables ............................................................................... 120
Retrieving data from the web ....................................................................................................... 123
Retrieving data from text files ....................................................................................................... 124
Querying data from other data sources ........................................................................................ 127
Retrieving external data with Microsoft Query............................................................................... 128
Review Questions ............................................................................................................................ 135
Review Answers .............................................................................................................................. 137
Chapter 7 - Performing What-If Scenarios ....................................................................................... 141
Learning Objectives ......................................................................................................................... 141
Introduction ...................................................................................................................................... 141
Using Data Tables ........................................................................................................................... 142
Table of Contents
iiiCreating a one-variable data table ............................................................................................... 142
Creating a two-variable data table ................................................................................................ 144
Exploring Different Scenarios ........................................................................................................... 146
Creating new scenarios................................................................................................................ 147
Producing a summary report ........................................................................................................ 150
Hide and Goal Seeking .................................................................................................................... 151
Using the Solver .............................................................................................................................. 153
Setting up and defining the problem ............................................................................................. 153
Solving the problem ..................................................................................................................... 155
Changing the Solver options ........................................................................................................ 156
Saving and loading a model problem ........................................................................................... 158
Creating Solver reports ................................................................................................................ 158
Review Questions ............................................................................................................................ 159
Review Answers .............................................................................................................................. 160
Chapter 8 - Performing Large-Scale Data Analysis ......................................................................... 163
Learning Objectives ......................................................................................................................... 163
Introduction ...................................................................................................................................... 163
Creating Pivot Tables ....................................................................................................................... 163
Pivot tables with the Quick Analysis tool ....................................................................................... 164
Recommended pivot tables .......................................................................................................... 166
Manually created pivot tables ....................................................................................................... 167
Formatting a Pivot Table .................................................................................................................. 170
Refining the pivot table layout and style ....................................................................................... 171
Formatting the parts of the pivot table .......................................................................................... 172
Sorting and Filtering the Pivot Table Data ........................................................................................ 174
Filtering the report ........................................................................................................................ 174
Filtering individual Column and Row fields ................................................................................... 175
Slicing the pivot table data ........................................................................................................... 176
Using timeline filters ..................................................................................................................... 177
Sorting the pivot table .................................................................................................................. 178
Modifying the Pivot Table ................................................................................................................. 178
Changing the summary functions ................................................................................................. 180
Adding Calculated Fields ............................................................................................................. 181
Changing the pivot table options .................................................................................................. 182
Creating Pivot Charts ....................................................................................................................... 183
Moving a pivot chart to its own sheet ............................................................................................ 183
Filtering a pivot chart.................................................................................................................... 184
Formatting a pivot chart ............................................................................................................... 185
Using the PowerPivot and Power View Add-Ins ................................................................................ 185
Data modeling with PowerPivot .................................................................................................... 186
Switching between the Data View and Diagram View ................................................................... 188
Adding calculated columns courtesy of DAX ................................................................................ 189
Creating visual reports with Power View ....................................................................................... 191
Using the Power Map feature ........................................................................................................... 193
Creating Forecast Worksheets ......................................................................................................... 196
Review Questions ............................................................................................................................ 200
Review Answers .............................................................................................................................. 202
Chapter 9 - Recording and Running Macros ................................................................................... 205
Learning Objectives ......................................................................................................................... 205
Introduction ...................................................................................................................................... 205
Macro Basics ................................................................................................................................... 205
Recording macros ........................................................................................................................ 205
Running a macro ......................................................................................................................... 209
Assigning Macros to the Ribbon and the Quick Access Toolbar........................................................ 210
Adding your macros to a custom tab on the Ribbon ...................................................................... 210
Table of Contents
ivAdding your macros to custom buttons on the Quick Access toolbar ............................................ 211
Macro Security ................................................................................................................................. 211
Review Questions ............................................................................................................................ 213
Review Answers .............................................................................................................................. 214
Chapter 10 - VBA Programming ....................................................................................................... 217
Learning Objectives ......................................................................................................................... 217
Introduction ...................................................................................................................................... 217
Using the Visual Basic Editor ........................................................................................................... 217
Editing recorded macros .............................................................................................................. 219
Writing new macros in the Visual Basic Editor .............................................................................. 227
Creating Custom Excel Functions .................................................................................................... 228
Adding a description to a user-defined function ............................................................................ 230
Using a custom function in your spreadsheet ............................................................................... 231
Saving custom functions in add-in files ......................................................................................... 233
Review Questions ............................................................................................................................ 236
Review Answers .............................................................................................................................. 237
Glossary ............................................................................................................................................. 239
Index .................................................................................................................................................. 243
quotesdbs_dbs14.pdfusesText_20