My Excel 2016

My Excel 2016

by Tracy Syrstad
Released October 2015
Publisher(s): Que
ISBN: 9780134217192

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

My Excel 2016 is your must-have companion for getting most out of Excel 2016. This friendly, quick, full-color, 100% practical tutorial walks you through every task you'll want to do with Excel 2016.

  • Get productive fast with Excel 2016’s updated interface

  • Save time and make your data easier to work with

  • Efficiently enter, insert, move, and manage data

  • Use templates to reuse work and get a jumpstart on new projects

  • Format worksheets and charts to make them clearer and more useful

  • Use intuitive sparkline charts that fit in a single cell

  • Build custom formulas using powerful built-in functions

  • Sort, filter, and consolidate data and eliminate duplication

  • Instantly group data and generate subtotals

  • Quickly summarize huge data sets with PivotTables

  • Find, fix, and avoid errors that break spreadsheets or cause inaccuracies

  • Communicate more powerfully using visual tools such as SmartArt and WordArt

  • Precisely control what you print and how it looks

  • Securely share and distribute your workbooks

  • Use Excel on the Web wherever you have Internet access

    • Every task is presented step-by-step, using carefully annotated, colorful screenshots, all numbered so there's no chance of getting lost or confused. Everything's clearly organized in modular, self-contained chapters designed to help you get started quickly. Throughout, the book is packed with helpful tips, lists, and quick solutions to the problems you're most likely to encounter.

    Table of contents

    1. About This eBook
    2. Title Page
    3. Copyright Page
    4. Contents at a Glance
    5. Table of Contents
    6. About the Author
    7. Dedication
    8. Acknowledgments
    9. We Want to Hear from You!
    10. Reader Services
    11. Introduction
      1. What’s in This Book
      2. Guidance for Beginners
    12. 1. Understanding the Microsoft Excel Interface
      1. Identifying Parts of the Excel Window
      2. Using the Built-in Help
        1. Perform a Search
      3. Making Selections from the Ribbon
      4. Customizing the Ribbon
        1. Minimize the Ribbon Size
        2. Add More Commands to the Ribbon
      5. Customizing the QAT
        1. Move the QAT to a New Location
        2. Add More Commands to the QAT
      6. Viewing Multiple Sheets at the Same Time
        1. Arrange Multiple Sheets
        2. Scroll Two Sheets Side by Side
      7. Changing the Zoom on a Sheet
        1. Use Excel’s Zoom Controls
      8. Moving Around on a Sheet
        1. Keyboard Shortcuts for Quicker Navigation
      9. Selecting a Range of Cells
        1. Select a Range Using the Mouse
    13. 2. Working with Workbooks and Templates
      1. Managing Workbooks
        1. Create a New Workbook
        2. Open an Existing Workbook
        3. Use the Recent Workbooks List
        4. Save a Workbook
        5. Close a Workbook
      2. Using Templates to Quickly Create New Workbooks
        1. Use Microsoft’s Online Templates
        2. Save a Template
        3. Open a Locally Saved Template to Enter Data
        4. Edit the Design of a Locally Saved Template
        5. Change Personal Templates Location
    14. 3. Working with Sheets
      1. Adding and Deleting Sheets
        1. Add a New Sheet
        2. Delete a Sheet
      2. Navigating and Selecting Sheets
        1. Activate Another Sheet
        2. Select Multiple Sheets
      3. Moving or Copying Sheets
        1. Move or Copy a Sheet in the Same Workbook
        2. Move or Copy a Sheet Between Workbooks
      4. Renaming a Sheet
        1. Change a Sheet’s Name
    15. 4. Getting Data onto a Sheet
      1. Entering Different Types of Data into a Cell
        1. Type Numbers or Text into a Cell
        2. Enter Numbers as Text
        3. Type Dates and Times into a Cell
        4. Undo an Entry
      2. Using Lists to Quickly Fill a Range
        1. Extend a Series Containing Text
        2. Extend a Numerical Series
        3. Create Your Own List
      3. Using Paste Special
        1. Paste Values Only
        2. Combine Multiple Paste Special Options
        3. Multiply the Range by a Specific Value
        4. Use Paste to Merge a Noncontiguous Selection
      4. Using Text to Columns to Separate Data in a Single Column
        1. Work with Delimited Text
      5. Using Data Validation to Limit Data Entry in a Cell
        1. Limit User Entry to a Selection from a List
      6. Using Web Queries to Get Data onto a Sheet
        1. Insert a Web Query
      7. Editing Data
        1. Modify Cell Data
      8. Clearing the Contents of a Cell
        1. Clear Only Data from a Cell
      9. Clearing an Entire Sheet
        1. Clear an Entire Sheet
      10. Working with Tables
        1. Define a Table
        2. Add a Total Row to a Table
        3. Change the Total Row Function
        4. Expand a Table
      11. Fixing Numbers Stored as Text
        1. Use Convert to Number on Multiple Cells
        2. Use Paste Special to Force a Number
      12. Spell Checking a Sheet
      13. Finding Data on a Sheet
        1. Perform a Search
        2. Perform a Wildcard Search
        3. Replace Data on a Sheet
    16. 5. Selecting and Moving Data on a Sheet
      1. Working with Rows and Columns
        1. Select a Row or Column
        2. Insert a New Row or Column
        3. Delete a Row or Column
        4. Move Rows or Columns by Dragging
        5. Move Rows or Columns by Cutting
        6. Copy Rows or Columns
      2. Working with Cells
        1. Select a Cell Using the Name Box
        2. Select Noncontiguous Cells and Ranges
        3. Insert Cells
        4. Delete Cells
        5. Move Cells
    17. 6. Formatting Sheets and Cells
      1. Changing the Font Settings of a Cell
        1. Select a New Font Typeface
        2. Increase and Decrease the Font Size
        3. Apply Bold, Italic, and Underline to Text
        4. Apply Strikethrough, Superscript, and Subscript
        5. Change the Font Color
        6. Format a Character or Word in a Cell
        7. Format Quickly with the Format Painter
      2. Adjusting the Row Height
        1. Modify the Row Height by Dragging
        2. Modify the Row Height by Entering a Value
        3. Use Font Size to Automatically Adjust the Row Height
      3. Adjusting the Column Width
        1. Modify the Column Width by Dragging
        2. Modify the Column Width by Entering a Value
      4. Aligning Text in a Cell
        1. Change Text Alignment
      5. Merging Two or More Cells
        1. Merge and Center Data
        2. Merge Across Columns
        3. Unmerge Cells
      6. Centering Text Across Multiple Cells
        1. Center Text Without Merging
      7. Wrapping Text in a Cell to the Next Line
        1. Wrap Text in a Cell
      8. Reflowing Text in a Paragraph
        1. Fit Text to a Specific Range
      9. Indenting Cell Contents
        1. Indent Data
      10. Applying Number Formats
        1. Modify the Number Format
        2. Change the Format of Negative Numbers
        3. Apply a Currency Symbol
        4. Format Dates and Times
        5. Format as Percentage
        6. Format as Text
        7. Apply the Special Number Format
      11. Adding a Border Around a Range
        1. Format a Range with a Thick Outer Border and Thin Inner Lines
        2. Add a Colored Border
      12. Coloring the Inside of a Cell
        1. Apply a Two-Color Gradient to a Cell
    18. 7. Advanced Formatting
      1. Creating Custom Number Formats
        1. The Four Sections of a Custom Number Format
        2. Optional Versus Required Digits
        3. Use the Thousands Separator, Color Codes, and Text
        4. Line Up Decimals
        5. Fill Leading and Trailing Spaces
        6. Show More Than 24 Hours in a Time Format
      2. Creating Hyperlinks
        1. Create a Hyperlink to Another Sheet
        2. Link to a Web Page
      3. Dynamic Cell Formatting with Conditional Formatting
        1. Use Icons to Mark Data
        2. Highlight the Top 10
        3. Highlight Duplicate or Unique Values
        4. Create a Custom Rule
        5. Clear Conditional Formatting
        6. Edit Conditional Formatting
      4. Using Cell Styles to Apply Cell Formatting
        1. Apply a Style
        2. Create a Custom Style
      5. Using Themes to Ensure Uniformity in Design
        1. Apply a New Theme
        2. Create a New Theme
        3. Share a Theme
    19. 8. Using Formulas
      1. Entering a Formula into a Cell
        1. Calculate a Formula
        2. View All Formulas on a Sheet
      2. Relative Versus Absolute Referencing
        1. Lock the Row When Copying a Formula Down
      3. Copying Formulas
        1. Copy and Paste Formulas
        2. Copy by Dragging the Fill Handle
        3. Copy Rapidly Down a Column
        4. Copy Between Workbooks Without Creating a Link
      4. Converting Formulas to Values
        1. Paste as Values
        2. Select and Drag
      5. Using Names to Simplify References
        1. Create a Named Cell
        2. Use a Name in a Formula
      6. Inserting Formulas into Tables
        1. Write a Formula in a Table
        2. Write Table Formulas Outside the Table
      7. Using Array Formulas
        1. Enter an Array Formula
        2. Delete a Multicell Array Formula
      8. Working with Links
        1. Control the Prompt
        2. Refresh Data
        3. Change the Source Workbook
        4. Break the Link
      9. Troubleshooting Formulas
        1. Fix ###### in a Cell
        2. Understand a Formula Error
        3. Use Trace Precedents and Dependents
        4. Track Formulas on Other Sheets with Watch Window
        5. Use the Evaluate Formula Dialog Box
        6. Evaluate with F9
      10. Adjusting Calculation Settings
        1. Set Calculations to Manual
    20. 9. Using Functions
      1. Understanding Functions
        1. Look Up Functions
        2. Use the Function Arguments Dialog Box
        3. Enter Functions Using Formula Tips
      2. Using the AutoSum Button
        1. Calculate a Single Range
        2. Sum Rows and Columns at the Same Time
      3. Quick Calculations
        1. Calculate Results Quickly
        2. Using Quick Analysis Functions
      4. Using Lookup Functions
        1. Use CHOOSE to Return the nth Value from a List
        2. Use VLOOKUP to Return a Value from a Table
        3. Use INDEX and MATCH to Return a Value from the Left
      5. Using SUMIFS to Sum Based on Multiple Criteria
        1. Sum a Column Based on Two Criteria
      6. Using IF Statements
        1. Compare Two Values
      7. Hiding Errors with IFERROR
        1. Hide a #DIV/0! Error
      8. Understanding Dates and Times
        1. Return a New Date X Workdays from Date
        2. Calculate the Number of Days Between Dates
      9. Using Goal Seek
        1. Calculate the Best Payment
      10. Using the Function Arguments Dialog Box to Troubleshoot Formulas
        1. Narrow Down a Formula Error
    21. 10. Sorting Data
      1. Using the Sort Dialog Box
        1. Sort by Values
        2. Sort by Color or Icon
      2. Doing Quick Sorts
        1. Quick Sort a Single Column
        2. Quick Sort Multiple Columns
      3. Performing Custom Sorts
        1. Perform a Random Sort
        2. Sort with a Custom Sequence
      4. Rearranging Columns
        1. Sort Columns with the Sort Dialog Box
      5. Fixing Sort Problems
    22. 11. Filtering and Consolidating Data
      1. Using the Filter Tool
        1. Apply a Filter
        2. Clear a Filter
        3. Reapply a Filter
        4. Turn the Filter On for One Column
      2. Filtering Grouped Dates
        1. Turn On Grouped Dates
        2. Filter by Date
      3. Using Special Filters
        1. Filter for Items that Include a Specific Term
        2. Filter for Values Within a Range
        3. Filter for the Top 25 Items
        4. Filter Dates by Quarter
        5. Filtering by Color or Icon
        6. Filtering by Selection
      4. Allowing Users to Filter a Protected Sheet
        1. Filter a Protected Sheet
      5. Using the Advanced Filter
        1. Reorganize Columns
        2. Create a List of Unique Items
        3. Filter Records Using Criteria
        4. Use Formulas as Criteria
      6. Removing Duplicates
        1. Delete Duplicate Rows
      7. Consolidating Data
        1. Merge Values from Two Datasets
        2. Merge Data Based on Matching Labels
    23. 12. Distributing and Printing a Workbook
      1. Using Cell Comments to Add Notes to Cells
        1. Insert a New Cell Comment
        2. Edit a Cell Comment
        3. Format a Cell Comment
        4. Insert an Image into a Cell Comment
        5. Resize a Cell Comment
        6. Show and Hide Cell Comments
        7. Delete a Cell Comment
      2. Allowing Multiple Users to Edit a Workbook at the Same Time
        1. Share a Workbook
      3. Hiding and Unhiding Sheets
        1. Hide a Sheet
        2. Unhide a Sheet
      4. Using Freeze Panes
        1. Lock the Top Row
        2. Lock Multiple Rows and Columns
      5. Configuring the Page Setup
        1. Set Paper Size, Margins, and Orientation
        2. Set the Print Area
        3. Set Page Breaks
        4. Scale the Data to Fit a Printed Page
        5. Repeat Specific Rows on Each Printed Page
      6. Creating a Custom Header or Footer
        1. Add an Image to the Header or Footer
        2. Add Page Numbering to the Header and Footer
      7. Printing Sheets
        1. Configure Print Options
      8. Protecting a Workbook from Unwanted Changes
        1. Set File-Level Protection
        2. Set Workbook-Level Protection
      9. Protecting the Data on a Sheet
        1. Protect a Sheet
        2. Unlock Cells
        3. Allow Users to Edit Specific Ranges
      10. Preventing Changes by Marking a File as Final
        1. Mark a Workbook as Final
      11. Sharing Files Between Excel Versions
        1. Check Version Compatibility
      12. Recovering Lost Changes
        1. Configure Backups
        2. Recover a Backup
        3. Recover Unsaved Files
      13. Sending an Excel File as an Attachment
        1. Email a Workbook
      14. Sharing a File Online
        1. Save to OneDrive
    24. 13. Inserting Subtotals and Grouping Data
      1. Using the SUBTOTAL Function
        1. Calculate Visible Rows
      2. Summarizing Data Using the Subtotal Tool
        1. Apply a Subtotal
        2. Expand and Collapse Subtotals
        3. Remove Subtotals or Groups
        4. Sort Subtotals
      3. Copying the Subtotals to a New Location
        1. Copy Subtotals
      4. Applying Different Subtotal Function Types
        1. Create Multiple Subtotal Results on Multiple Rows
        2. Combine Multiple Subtotal Results to One Row
      5. Adding Space Between Subtotaled Groups
        1. Separate Subtotaled Groups for Print
        2. Separate Subtotaled Groups for Distributed Files
      6. Grouping and Outlining Rows and Columns
        1. Apply Auto Outline
        2. Group Data Manually
    25. 14. Creating Charts and Sparklines
      1. Adding a Chart
        1. Add a Chart with the Quick Analysis Tool
        2. Preview All Charts
        3. Switch Rows and Columns
        4. Apply Chart Styles or Colors
        5. Apply Chart Layouts
      2. Resizing or Moving a Chart
        1. Resize a Chart
        2. Move to a New Location on the Same Sheet
        3. Relocate to Another Sheet
      3. Editing Chart Elements
        1. Use the Format Task Pane
        2. Edit the Chart or Axis Titles
        3. Change the Display Units in an Axis
        4. Customize a Series Color
      4. Changing an Existing Chart’s Type
        1. Change the Chart Type
      5. Creating a Chart with Multiple Chart Types
        1. Insert a Multiple Type Chart
        2. Add a Secondary Axis
      6. Updating Chart Data
        1. Change the Data Source
      7. Adding Special Charts
        1. Create a Stock Chart
        2. Create a Bubble Chart
      8. Pie Chart Issue: Small Slices
        1. Rotate the Pie
        2. Create a Bar of Pie Chart
      9. Using a User-Created Template
        1. Save a Chart Template
        2. Use a Chart Template
      10. Adding Sparklines to Data
        1. Insert a Sparkline
        2. Emphasize Points on a Sparkline
        3. Space Markers by Date
        4. Delete Sparklines
    26. 15. Summarizing Data with PivotTables
      1. Creating a PivotTable
        1. Use the Quick Analysis Tool
        2. Create a PivotTable from Scratch
        3. Change the Calculation Type of a Field Value
        4. Format Values
      2. Changing the PivotTable Layout
        1. Choose a New Layout
      3. PivotTable Sorting
        1. Click and Drag
        2. Use Quick Sort
      4. Expanding and Collapsing Fields
        1. Expand and Collapse a Field
      5. Grouping Dates
        1. Group by Week
        2. Group by Month and Year
      6. Filtering Data in a PivotTable
        1. Filter for Listed Items
        2. Clear a Filter
      7. Creating a Calculated Field
        1. Add a Calculated Field
      8. Hiding Totals
        1. Hide Totals
        2. Hide Subtotals
      9. Viewing the Records Used to Calculate a Value
      10. Unlinking PivotTables
        1. Unlink a PivotTable Report
      11. Refreshing the PivotTable
        1. Refresh on Open
        2. Refresh After Adding New Data
        3. Refresh After Editing the Data Source
      12. Working with Slicers
        1. Create a Slicer
        2. Use a Slicer
    27. 16. Inserting SmartArt, WordArt, and Pictures
      1. Working with SmartArt
        1. Insert a SmartArt Graphic
        2. Insert Images into SmartArt
        3. Move and Resize SmartArt
        4. Reorder Placeholders
        5. Change the Layout
        6. Change an Individual Shape
      2. Working with WordArt
        1. Insert WordArt
      3. Inserting Pictures
        1. Insert a Picture
        2. Resize and Crop a Picture
        3. Apply Corrections, Color, and Artistic Effects
        4. Reduce a File’s Size
    28. 17. Introducing the Excel Web App
      1. Acquiring a Microsoft Account
        1. Create an Account
      2. Uploading a Workbook
        1. Upload Through OneDrive
        2. Save from Excel
        3. Delete a File from OneDrive
      3. Opening a Workbook Online or Locally
        1. Open a Workbook
        2. Download a Workbook
      4. Creating a New Workbook Online
        1. Create a Workbook
        2. Rename the New Workbook
      5. Sharing a Folder or Workbook
        1. Create a View-Only Folder
        2. Remove Sharing
        3. Edit Simultaneously
      6. Configuring Browser View Options
        1. Create an Online Form
      7. Designing a Survey Through the Web App
        1. Create a Survey
    29. Index

    Product information

    • Title: My Excel 2016
    • Author(s): Tracy Syrstad
    • Release date: October 2015
    • Publisher(s): Que
    • ISBN: 9780134217192