Microsoft Excel 2016 Step by Step

Curtis Frye is the author of more than 30 books, including  Microsoft Excel 2013 Step by Step for Microsoft Press and  Brilliant Excel VBA Programming for Pearson, UK. He has also created and recorded more than three dozen courses for lynda.com, including  Excel for Mac 2016 Essential Training and  Excel 2013: PivotTables in Depth. In addition to his work as a writer, Curt is a popular conference speaker and performer, both as a solo presenter and as part of the Portland, Oregon ComedySportz improvisational comedy troupe. He lives in Portland with his wife and three cats.

• Introduction     xiii Who this book is for     xiii The Step by Step approach     xiii Download the practice files     xiv Sidebar: Adapt exercise steps     xviii Ebook edition     xx Get support and give feedback     xx Errata and support     xx We want to hear from you     xx Stay in touch     xx Part 1: Create and format workbooks Chapter 1: Set up a workbook     3 Explore the editions of Excel 2016     4 Excel 2016     4 Excel Online     4 Excel Mobile Apps     5 Become familiar with new features in Excel 2016     6 Create workbooks     7 Modify workbooks     13 Modify worksheets     18 Merge and unmerge cells     23 Customize the Excel 2016 app window     26 Zoom in on a worksheet     26 Arrange multiple workbook windows     28 Add buttons to the Quick Access Toolbar     30 Customize the ribbon     33 Skills review     39 Practice tasks     40 Chapter 2: Work with data and Excel tables     43 Enter and revise data     44 Manage data by using Flash Fill     48 Move data within a workbook     51 Find and replace data     56 Correct and expand upon data     60 Define Excel tables     65 Skills review     69 Practice tasks     70 Chapter 3: Perform calculations on data     73 Name groups of data     74 Create formulas to calculate values     77 Sidebar: Operators and Precedence     88 Summarize data that meets specific conditions     90 Set iterative calculation options and enable or disable automatic calculation     96 Use array formulas     98 Find and correct errors in calculations     100 Skills review     106 Practice tasks     107 Chapter 4: Change workbook appearance     111 Format cells     112 Define styles     118 Apply workbook themes and Excel table styles     121 Make numbers easier to read     127 Change the appearance of data based on its value     130 Add images to worksheets     138 Skills review     142 Practice tasks     143 Part 2: Analyze and present dataChapter 5: Manage worksheet data     147 Limit data that appears on your screen     148 Manipulate worksheet data     153 Sidebar: Select list rows at random     154 Summarize data in worksheets that have hidden and filtered rows     155 Find unique values within a data set     161 Define valid sets of values for ranges of cells     163 Skills review     165 Practice tasks     166 Chapter 6: Reorder and summarize data     169 Sort worksheet data     170 Sort data by using custom lists     176 Organize data into levels     179 Look up information in a worksheet     184 Skills review     187 Practice tasks     188 Chapter 7: Combine data from multiple sources     191 Use workbooks as templates for other workbooks     192 Link to data in other worksheets and workbooks     197 Consolidate multiple sets of data into a single workbook     202 Skills review     205 Practice tasks     206 Chapter 8: Analyze alternative data sets     209 Examine data by using the Quick Analysis Lens     210 Define an alternative data set     212 Define multiple alternative data sets     216 Analyze data by using data tables     218 Vary your data to get a specific result by using Goal Seek     221 Find optimal solutions by using Solver     223 Analyze data by using descriptive statistics     229 Skills review     231 Practice tasks     232 Chapter 9: Create charts and graphics     237 Create charts     238 Create new types of charts     248 Customize chart appearance     252 Find trends in your data     260 Create dual-axis charts     263 Summarize your data by using sparklines     264 Create diagrams by using SmartArt     266 Create shapes and mathematical equations     271 Skills review     277 Practice tasks     278 Chapter 10: Create dynamic worksheets by using PivotTables     281 Analyze data dynamically by using PivotTables     282 Filter, show, and hide PivotTable data     290 Edit PivotTables     301 Format PivotTables     305 Create PivotTables from external data     309 Create dynamic charts by using PivotCharts     313 Skills review     316 Practice tasks     317 Part 3: Collaborate and share in Excel Chapter 11: Print worksheets and charts     323 Add headers and footers to printed pages     324 Prepare worksheets for printing     330 Fit your worksheet contents to the printed page     331 Change page breaks in a worksheet     334 Change the page printing order for worksheets     335 Print worksheets     337 Print parts of worksheets     339 Print charts     343 Skills review     345 Practice tasks     346 Chapter 12: Automate repetitive tasks by using macros     349 Enable and examine macros     350 Set macro security levels in Excel 2016     350 Examine macros     353 Create and modify macros     356 Run macros when you click a button     357 Run a macro when you open a workbook     361 Insert form controls into a worksheet     362 Skills review     369 Practice tasks     370 Chapter 13: Work with other Microsoft Office apps     373 Include Office documents in workbooks and other files     374 Link Office documents to Excel workbooks     375 Embed files in Excel and other Office apps     377 Create hyperlinks     379 Paste charts into documents     384 Skills review     385 Practice tasks     386 Chapter 14: Collaborate with colleagues     389 Share workbooks     390 Save workbooks for electronic distribution     394 Manage comments     395 Track and manage colleagues’ changes     398 Add protection to workbooks and worksheets     401 Finalize workbooks     408 Authenticate workbooks     410 Save workbooks for the web     412 Import and export XML data     414 Work with OneDrive and Excel Online     418 Skills review     424 Practice tasks     425 Part 4: Perform advanced analysis Chapter 15: Perform business intelligence analysis     431 Enable the Data Analysis add-ins     432 Define relationships between tables     434 Analyze data by using Power Pivot     438 View data by using timelines     442 Bring in external data by using Power Query     446 Skills review     451 Practice tasks     452 Chapter 16: Create forecasts and visualizations     455 Create Forecast Worksheets     456 Define and manage measures     461 Define and display Key Performance Indicators     464 Create 3D maps     468 Skills review     475 Practice tasks     476 Keyboard shortcuts     479 Glossary     89 Index     95 About the author     521