Microsoft Excel 2016 Step by Step

Microsoft Excel 2016 Step by Step

by Curtis Frye
Released October 2015
Publisher(s): Microsoft Press
ISBN: 9780735697478

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Now in full color!

The quick way to learn Microsoft Excel 2016!

This is learning made easy. Get more done quickly with Excel 2016. Jump in wherever you need answers--brisk lessons and full-color screen shots show you exactly what to do, step by step.

  • Quickly set up workbooks, enter data, and format it for easier viewing

  • Perform calculations and find and correct errors

  • Filter, sort, summarize, and combine data

  • Analyze data by using PivotTables, PivotCharts, scenarios, data tables, and Solver

  • Visualize data with charts and graphs, including new sunbursts, waterfalls, and treemaps

  • Build data models and use them in business intelligence analyses

  • Create timelines, forecasts, and visualizations, including KPIs and PowerMap data maps

  • Look up just the tasks and lessons you need

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication
  5. Contents
  6. I. Introduction
    1. Who this book is for
    2. The Step by Step approach
    3. Download the practice files
    4. Ebook edition
    5. Get support and give feedback
      1. Errata and support
      2. We want to hear from you
      3. Stay in touch
  7. Part 1: Create and format workbooks
    1. 1. Set up a workbook
      1. Explore the editions of Excel 2016
        1. Excel 2016
        2. Excel Online
        3. Excel Mobile Apps
      2. Become familiar with new features in Excel 2016
      3. Create workbooks
      4. Modify workbooks
      5. Modify worksheets
      6. Merge and unmerge cells
      7. Customize the Excel 2016 app window
        1. Zoom in on a worksheet
        2. Arrange multiple workbook windows
        3. Add buttons to the Quick Access Toolbar
        4. Customize the ribbon
      8. Skills review
      9. Practice tasks
        1. Create workbooks
        2. Modify workbooks
        3. Modify worksheets
        4. Merge and unmerge cells
        5. Customize the Excel 2016 app window
    2. 2. Work with data and Excel tables
      1. Enter and revise data
      2. Manage data by using Flash Fill
      3. Move data within a workbook
      4. Find and replace data
      5. Correct and expand upon data
      6. Define Excel tables
      7. Skills review
      8. Practice tasks
        1. Enter and revise data
        2. Manage data by using Flash Fill
        3. Move data within a workbook
        4. Find and replace data
        5. Correct and expand upon data
        6. Define Excel tables
    3. 3. Perform calculations on data
      1. Name groups of data
      2. Create formulas to calculate values
      3. Summarize data that meets specific conditions
      4. Set iterative calculation options and enable or disable automatic calculation
      5. Use array formulas
      6. Find and correct errors in calculations
      7. Skills review
      8. Practice tasks
        1. Name groups of data
        2. Create formulas to calculate values
        3. Summarize data that meets specific conditions
        4. Set iterative calculation options and enable or disable automatic calculation
        5. Use array formulas
        6. Find and correct errors in calculations
    4. 4. Change workbook appearance
      1. Format cells
      2. Define styles
      3. Apply workbook themes and Excel table styles
      4. Make numbers easier to read
      5. Change the appearance of data based on its value
      6. Add images to worksheets
      7. Skills review
      8. Practice tasks
        1. Format cells
        2. Define styles
        3. Apply workbook themes and Excel table styles
        4. Make numbers easier to read
        5. Change the appearance of data based on its value
        6. Add images to worksheets
  8. Part 2: Analyze and present data
    1. 5. Manage worksheet data
      1. Limit data that appears on your screen
      2. Manipulate worksheet data
        1. Summarize data in worksheets that have hidden and filtered rows
        2. Find unique values within a data set
      3. Define valid sets of values for ranges of cells
      4. Skills review
      5. Practice tasks
        1. Limit data that appears on your screen
        2. Manipulate worksheet data
        3. Define valid sets of values for ranges of cells
    2. 6. Reorder and summarize data
      1. Sort worksheet data
      2. Sort data by using custom lists
      3. Organize data into levels
      4. Look up information in a worksheet
      5. Skills review
      6. Practice tasks
        1. Sort worksheet data
        2. Sort data by using custom lists
        3. Organize data into levels
        4. Look up information in a worksheet
    3. 7. Combine data from multiple sources
      1. Use workbooks as templates for other workbooks
      2. Link to data in other worksheets and workbooks
      3. Consolidate multiple sets of data into a single workbook
      4. Skills review
      5. Practice tasks
        1. Use workbooks as templates for other workbooks
        2. Link to data in other worksheets and workbooks
        3. Consolidate multiple sets of data into a single workbook
    4. 8. Analyze alternative data sets
      1. Examine data by using the Quick Analysis Lens
      2. Define an alternative data set
      3. Define multiple alternative data sets
      4. Analyze data by using data tables
      5. Vary your data to get a specific result by using Goal Seek
      6. Find optimal solutions by using Solver
      7. Analyze data by using descriptive statistics
      8. Skills review
      9. Practice tasks
        1. Examine data by using the Quick Analysis Lens
        2. Define an alternative data set
        3. Define multiple alternative data sets
        4. Analyze data by using data tables
        5. Vary your data to get a specific result by using Goal Seek
        6. Find optimal solutions by using Solver
        7. Analyze data by using descriptive statistics
    5. 9. Create charts and graphics
      1. Create charts
      2. Create new types of charts
      3. Customize chart appearance
      4. Find trends in your data
      5. Create dual-axis charts
      6. Summarize your data by using sparklines
      7. Create diagrams by using SmartArt
      8. Create shapes and mathematical equations
      9. Skills review
      10. Practice tasks
        1. Create charts
        2. Create new types of charts
        3. Customize chart appearance
        4. Find trends in your data
        5. Create dual-axis charts
        6. Summarize your data by using sparklines
        7. Create diagrams by using SmartArt
        8. Create shapes and mathematical equations
    6. 10. Create dynamic worksheets by using PivotTables
      1. Analyze data dynamically by using PivotTables
      2. Filter, show, and hide PivotTable data
      3. Edit PivotTables
      4. Format PivotTables
      5. Create PivotTables from external data
      6. Create dynamic charts by using PivotCharts
      7. Skills review
      8. Practice tasks
        1. Analyze data dynamically by using PivotTables
        2. Filter, show, and hide PivotTable data
        3. Edit PivotTables
        4. Format PivotTables
        5. Create PivotTables from external data
        6. Create dynamic charts by using PivotCharts
  9. Part 3: Collaborate and share in Excel
    1. 11. Print worksheets and charts
      1. Add headers and footers to printed pages
      2. Prepare worksheets for printing
        1. Fit your worksheet contents to the printed page
        2. Change page breaks in a worksheet
        3. Change the page printing order for worksheets
      3. Print worksheets
      4. Print parts of worksheets
      5. Print charts
      6. Skills review
      7. Practice tasks
        1. Add headers and footers to printed pages
        2. Prepare worksheets for printing
        3. Print worksheets
        4. Print parts of worksheets
        5. Print charts
    2. 12. Automate repetitive tasks by using macros
      1. Enable and examine macros
        1. Set macro security levels in Excel 2016
        2. Examine macros
      2. Create and modify macros
      3. Run macros when you click a button
      4. Run a macro when you open a workbook
      5. Insert form controls into a worksheet
      6. Skills review
      7. Practice tasks
        1. Enable and examine macros
        2. Create and modify macros
        3. Run macros when you click a button
        4. Run a macro when you open a workbook
        5. Insert form controls into a worksheet
    3. 13. Work with other Microsoft Office apps
      1. Include Office documents in workbooks and other files
        1. Link Office documents to Excel workbooks
        2. Embed files in Excel and other Office apps
      2. Create hyperlinks
      3. Paste charts into documents
      4. Skills review
      5. Practice tasks
        1. Include Office documents in workbooks and other files
        2. Create hyperlinks
        3. Paste charts into documents
    4. 14. Collaborate with colleagues
      1. Share workbooks
      2. Save workbooks for electronic distribution
      3. Manage comments
      4. Track and manage colleagues’ changes
      5. Add protection to workbooks and worksheets
      6. Finalize workbooks
      7. Authenticate workbooks
      8. Save workbooks for the web
      9. Import and export XML data
      10. Work with OneDrive and Excel Online
      11. Skills review
      12. Practice tasks
        1. Share workbooks
        2. Save workbooks for electronic distribution
        3. Manage comments
        4. Track and manage colleagues’ changes
        5. Add protection to workbooks and worksheets
        6. Finalize workbooks
        7. Authenticate workbooks
        8. Save workbooks for the web
        9. Import and export XML data
        10. Work with OneDrive and Excel Online
  10. Part 4: Perform advanced analysis
    1. 15. Perform business intelligence analysis
      1. Enable the Data Analysis add-ins
      2. Define relationships between tables
      3. Analyze data by using Power Pivot
      4. View data by using timelines
      5. Bring in external data by using Power Query
      6. Skills review
      7. Practice tasks
        1. Enable the Data Analysis add-ins
        2. Define relationships between tables
        3. Analyze data by using Power Pivot
        4. View data by using timelines
        5. Bring in external data by using Power Query
    2. 16. Create forecasts and visualizations
      1. Create Forecast Worksheets
      2. Define and manage measures
      3. Define and display Key Performance Indicators
      4. Create 3D maps
      5. Skills review
      6. Practice tasks
        1. Create Forecast Worksheets
        2. Define and manage measures
        3. Define and display Key Performance Indicators
        4. Create 3D maps
  11. Appendix: Keyboard shortcuts
    1. Ctrl-combination shortcut keys
    2. Function keys
    3. Other useful shortcut keys
  12. Glossary
  13. Index
  14. About the author
  15. Back Cover
  16. Code Snippets

Product information

  • Title: Microsoft Excel 2016 Step by Step
  • Author(s): Curtis Frye
  • Release date: October 2015
  • Publisher(s): Microsoft Press
  • ISBN: 9780735697478