book

Microsoft Excel 2016 Step by Step

Name: Microsoft Excel 2016 Step by Step
Author: Curtis Frye
ISBN: 9780735697478

by Curtis Frye

October 2015

Beginner

544 pages

10h 18m

English

Microsoft Press

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
Dedication
Contents
I. Introduction
Who this book is forThe Step by Step approachDownload the practice filesEbook editionGet support and give feedbackErrata and supportWe want to hear from youStay in touch
Part 1: Create and format workbooks
1. Set up a workbook
Explore the editions of Excel 2016Excel 2016Excel OnlineExcel Mobile AppsBecome familiar with new features in Excel 2016Create workbooksModify workbooksModify worksheetsMerge and unmerge cellsCustomize the Excel 2016 app windowZoom in on a worksheetArrange multiple workbook windowsAdd buttons to the Quick Access ToolbarCustomize the ribbonSkills reviewPractice tasksCreate workbooksModify workbooksModify worksheetsMerge and unmerge cellsCustomize the Excel 2016 app window
2. Work with data and Excel tables
Enter and revise dataManage data by using Flash FillMove data within a workbookFind and replace dataCorrect and expand upon dataDefine Excel tablesSkills reviewPractice tasksEnter and revise dataManage data by using Flash FillMove data within a workbookFind and replace dataCorrect and expand upon dataDefine Excel tables
3. Perform calculations on data
Name groups of dataCreate formulas to calculate valuesSummarize data that meets specific conditionsSet iterative calculation options and enable or disable automatic calculationUse array formulasFind and correct errors in calculationsSkills reviewPractice tasksName groups of dataCreate formulas to calculate valuesSummarize data that meets specific conditionsSet iterative calculation options and enable or disable automatic calculationUse array formulasFind and correct errors in calculations

4. Change workbook appearance
Format cellsDefine stylesApply workbook themes and Excel table stylesMake numbers easier to readChange the appearance of data based on its valueAdd images to worksheetsSkills reviewPractice tasksFormat cellsDefine stylesApply workbook themes and Excel table stylesMake numbers easier to readChange the appearance of data based on its valueAdd images to worksheets
Part 2: Analyze and present data
5. Manage worksheet data
Limit data that appears on your screenManipulate worksheet dataSummarize data in worksheets that have hidden and filtered rowsFind unique values within a data setDefine valid sets of values for ranges of cellsSkills reviewPractice tasksLimit data that appears on your screenManipulate worksheet dataDefine valid sets of values for ranges of cells
6. Reorder and summarize data
Sort worksheet dataSort data by using custom listsOrganize data into levelsLook up information in a worksheetSkills reviewPractice tasksSort worksheet dataSort data by using custom listsOrganize data into levelsLook up information in a worksheet
7. Combine data from multiple sources
Use workbooks as templates for other workbooksLink to data in other worksheets and workbooksConsolidate multiple sets of data into a single workbookSkills reviewPractice tasksUse workbooks as templates for other workbooksLink to data in other worksheets and workbooksConsolidate multiple sets of data into a single workbook
8. Analyze alternative data sets
Examine data by using the Quick Analysis LensDefine an alternative data setDefine multiple alternative data setsAnalyze data by using data tablesVary your data to get a specific result by using Goal SeekFind optimal solutions by using SolverAnalyze data by using descriptive statisticsSkills reviewPractice tasksExamine data by using the Quick Analysis LensDefine an alternative data setDefine multiple alternative data setsAnalyze data by using data tablesVary your data to get a specific result by using Goal SeekFind optimal solutions by using SolverAnalyze data by using descriptive statistics
9. Create charts and graphics
Create chartsCreate new types of chartsCustomize chart appearanceFind trends in your dataCreate dual-axis chartsSummarize your data by using sparklinesCreate diagrams by using SmartArtCreate shapes and mathematical equationsSkills reviewPractice tasksCreate chartsCreate new types of chartsCustomize chart appearanceFind trends in your dataCreate dual-axis chartsSummarize your data by using sparklinesCreate diagrams by using SmartArtCreate shapes and mathematical equations
10. Create dynamic worksheets by using PivotTables
Analyze data dynamically by using PivotTablesFilter, show, and hide PivotTable dataEdit PivotTablesFormat PivotTablesCreate PivotTables from external dataCreate dynamic charts by using PivotChartsSkills reviewPractice tasksAnalyze data dynamically by using PivotTablesFilter, show, and hide PivotTable dataEdit PivotTablesFormat PivotTablesCreate PivotTables from external dataCreate dynamic charts by using PivotCharts
Part 3: Collaborate and share in Excel
11. Print worksheets and charts
Add headers and footers to printed pagesPrepare worksheets for printingFit your worksheet contents to the printed pageChange page breaks in a worksheetChange the page printing order for worksheetsPrint worksheetsPrint parts of worksheetsPrint chartsSkills reviewPractice tasksAdd headers and footers to printed pagesPrepare worksheets for printingPrint worksheetsPrint parts of worksheetsPrint charts
12. Automate repetitive tasks by using macros
Enable and examine macrosSet macro security levels in Excel 2016Examine macrosCreate and modify macrosRun macros when you click a buttonRun a macro when you open a workbookInsert form controls into a worksheetSkills reviewPractice tasksEnable and examine macrosCreate and modify macrosRun macros when you click a buttonRun a macro when you open a workbookInsert form controls into a worksheet
13. Work with other Microsoft Office apps
Include Office documents in workbooks and other filesLink Office documents to Excel workbooksEmbed files in Excel and other Office appsCreate hyperlinksPaste charts into documentsSkills reviewPractice tasksInclude Office documents in workbooks and other filesCreate hyperlinksPaste charts into documents
14. Collaborate with colleagues
Share workbooksSave workbooks for electronic distributionManage commentsTrack and manage colleagues’ changesAdd protection to workbooks and worksheetsFinalize workbooksAuthenticate workbooksSave workbooks for the webImport and export XML dataWork with OneDrive and Excel OnlineSkills reviewPractice tasksShare workbooksSave workbooks for electronic distributionManage commentsTrack and manage colleagues’ changesAdd protection to workbooks and worksheetsFinalize workbooksAuthenticate workbooksSave workbooks for the webImport and export XML dataWork with OneDrive and Excel Online
Part 4: Perform advanced analysis
15. Perform business intelligence analysis
Enable the Data Analysis add-insDefine relationships between tablesAnalyze data by using Power PivotView data by using timelinesBring in external data by using Power QuerySkills reviewPractice tasksEnable the Data Analysis add-insDefine relationships between tablesAnalyze data by using Power PivotView data by using timelinesBring in external data by using Power Query
16. Create forecasts and visualizations
Create Forecast WorksheetsDefine and manage measuresDefine and display Key Performance IndicatorsCreate 3D mapsSkills reviewPractice tasksCreate Forecast WorksheetsDefine and manage measuresDefine and display Key Performance IndicatorsCreate 3D maps
Appendix: Keyboard shortcuts
Ctrl-combination shortcut keysFunction keysOther useful shortcut keys
Glossary
Index
About the author
Back Cover
Code Snippets

Content preview from Microsoft Excel 2016 Step by Step

7. Combine data from multiple sources

In this chapter

Use workbooks as templates for other workbooks

Link to data in other worksheets and workbooks

Consolidate multiple sets of data into a single workbook

Practice files

For this chapter, use the practice files from the Excel2016SBS\Ch07 folder. For practice file download instructions, see the introduction.

Excel 2016 gives you a wide range of tools with which to format, summarize, and present ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780735697478Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft Excel 2016 Step by Step

by Curtis Frye

7. Combine data from multiple sources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.