Excel 2016 In Depth

Excel 2016 In Depth

by Bill Jelen
Released December 2015
Publisher(s): Que
ISBN: 9780134386898

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

This is the Rough Cut version of the printed book.

Does your life play out in a spreadsheet? Do numbers in columns and rows make or break you in the work world? Tired of having numbers kicked in your face by other Excel power users who make your modest spreadsheets look paltry compared to their fancy charts and pivot tables? If you answered yes to any of these questions, Excel 2016 In Depth is the book that will make it all better. Learn quickly and efficiently from a true Excel master using his tried and true formula for success. Here, you'll find information that's undocumented elsewhere--even in Microsoft's own Help systems. 

The world's most popular spreadsheet application, Microsoft Excel 2016, has many new features. Excel 2016 In Depth will ease the upgrade path. Bill Jelen "Mr. Excel," introduces the reader to the new interface allowing them to quickly get back up-to-speed in performing their job and from there will then introduce the powerful new features available in Excel 2016.

There's a lot new (especially Business Intelligence) in Office 2016, and Jelen covers it all:

• Power Query is now a native feature of Excel 2016

• Power View works on SSAS Multidimensional (this is only going to work on the versions of SSAS Multidimensional that support DAX queries, i.e. SSAS 2014 or SSAS 2012 SP2)

• New Excel forecasting functions

• Time grouping functionality in PivotTables and more

Table of contents

  1. About This E-Book
  2. Title Page
  3. Copyright Page
  4. Contents at a Glance
  5. Contents
  6. About the Author
  7. Dedication
  8. Acknowledgments
  9. We Want to Hear from You!
  10. Reader Services
  11. Introduction
    1. How This Book Is Organized
    2. Conventions Used in This Book
      1. Text Conventions
      2. Special Elements
  12. I: The Excel Interface
    1. 1. What’s New in Excel 2016 (and 2013)
      1. Color Returns to the Excel Interface
      2. The Data Model from Excel 2013 Is the Most Important Feature in 2016
      3. Clean Your Data with Power Query
      4. Pivot Your Data on a Map with 3D Maps
      5. View Your Data Using Six New Chart Types
      6. Forecast the Future Using a Forecast Sheet
      7. Important Features from Excel 2013
        1. Displaying Two Workbooks on Two Monitors
        2. Dismissing the Start Screen Permanently
        3. Using the Subscription Model of Office 365
        4. Using the Cloud for Storage and More
        5. Relying on the Cloud
      8. Oddities Added to Excel 2016
        1. Handwriting Equations
        2. Touchable Slicers
        3. Search the Internet from Excel
        4. Find a Ribbon Command if You Know the Exact Name of the Command
    2. 2. Using the Excel Interface
      1. Using the Ribbon
        1. Using Fly-out Menus and Galleries
        2. Rolling Through the Ribbon Tabs
        3. Revealing More Commands Using Dialog Launchers, Task Panes, and “More” Commands
        4. Resizing Excel Changes the Ribbon
        5. Activating the Developer Tab
        6. Activating Contextual Ribbon Tabs
        7. Finding Lost Commands on the Ribbon
        8. Shrinking the Ribbon
      2. Using the Quick Access Toolbar
        1. Adding Icons to the QAT
        2. Removing Commands from the QAT
        3. Customizing the QAT
      3. Using the Full-Screen File Menu
        1. Pressing the Esc Key to Close Backstage View
        2. Recovering Unsaved Workbooks
        3. Clearing the Recent Workbooks List
        4. Getting Information About the Current Workbook
        5. Marking a Workbook as Final to Prevent Editing
        6. Finding Hidden Content Using the Document Inspector
        7. Adding Whitespace Around Icons Using Touch Mode
        8. Previewing Paste Using the Paste Options Gallery
        9. Accessing the Gallery After Performing a Paste Operation
        10. Accessing the Paste Options Gallery from the Right-Click Menu
      4. Using the New Sheet Icon to Add Worksheets
      5. Navigating Through Many Worksheets Using the Controls in the Lower Left
      6. Using the Mini Toolbar to Format Selected Text
      7. Expanding the Formula Bar
      8. Zooming In and Out on a Worksheet
      9. Using the Status Bar to Add Numbers
      10. Switching Between Normal View, Page Break Preview, and Page Layout View Modes
    3. 3. Customizing Excel
      1. Performing a Simple Ribbon Modification
      2. Adding a New Ribbon Tab
      3. Sharing Customizations with Others
      4. Questions About Ribbon Customization
      5. Introducing the Excel Options Dialog
        1. Getting Help with a Setting
        2. New Options in Excel 2016
        3. Using AutoRecover Options
        4. Controlling Image Sizes
        5. Working with Protected View for Files Originating from the Internet
        6. Working with Trusted Document Settings
      6. Options to Consider
      7. Five Excel Oddities
    4. 4. Keyboard Shortcuts
      1. Using Keyboard Accelerators
        1. Selecting Icons on the Ribbon
        2. Selecting Options from a Gallery
        3. Navigating Within Drop-Down Lists
        4. Backing Up One Level Through a Menu
        5. Dealing with Keyboard Accelerator Confusion
        6. Selecting from Legacy Dialog Boxes
      2. Using the Shortcut Keys
      3. Using My Favorite Shortcut Keys
        1. Quickly Move Between Worksheets
        2. Jumping to the Bottom of Data with Ctrl+Arrow
        3. Selecting the Current Region with Ctrl+*
        4. Jumping to the Next Corner of a Selection
        5. Pop Open the Right-Click Menu Using Shift+F10
        6. Crossing Tasks Off Your List with Ctrl+5
        7. Date Stamp or Time Stamp Using Ctrl+; or Ctrl+:
        8. Repeating the Last Task with F4
        9. Adding Dollar Signs to a Reference with F4
        10. Choosing Items from a Slicer
        11. Finding the One Thing That Takes You Too Much Time
      4. Using Excel 2003 Keyboard Accelerators
        1. Invoking an Excel 2003 Alt Shortcut
        2. Determining Which Commands Work in Legacy Mode
  13. II: Calculating with Excel
    1. 5. Understanding Formulas
      1. Getting the Most from This Chapter
      2. Introduction to Formulas
        1. Formulas Versus Values
      3. Entering Your First Formula
        1. Building a Formula
        2. The Relative Nature of Formulas
        3. Overriding Relative Behavior: Absolute Cell References
        4. Using Mixed References to Combine Features of Relative and Absolute References
        5. Using the F4 Key to Simplify Dollar Sign Entry
        6. Using F4 After a Formula Is Entered
        7. Using F4 on a Rectangular Range
      4. Three Methods of Entering Formulas
        1. Enter Formulas Using the Mouse Method
        2. Entering Formulas Using the Arrow Key Method
      5. Entering the Same Formula in Many Cells
        1. Copying a Formula by Using Ctrl+Enter
        2. Copying a Formula by Dragging the Fill Handle
        3. Double-Click the Fill Handle to Copy a Formula
      6. Use the Table Tool to Copy a Formula
    2. 6. Controlling Formulas
      1. Formula Operators
        1. Order of Operations
        2. Unary Minus Example
        3. Addition and Multiplication Example
        4. Stacking Multiple Parentheses
      2. Understanding Error Messages in Formulas
      3. Using Formulas to Join Text
        1. Joining Text and a Number
      4. Copying Versus Cutting a Formula
      5. Automatically Formatting Formula Cells
      6. Using Date Math
      7. Troubleshooting Formulas
        1. Seeing All Formulas
        2. Highlighting All Formula Cells
        3. Editing a Single Formula to Show Direct Precedents
        4. Using Formula Auditing Arrows
        5. Tracing Dependents
        6. Using the Watch Window
        7. Evaluate a Formula in Slow Motion
        8. Evaluating Part of a Formula
    3. 7. Understanding Functions
      1. Working with Functions
        1. The Formulas Tab in Excel 2016
        2. Finding the Function You Need
        3. Using Tab to AutoComplete Functions
        4. Using the Insert Function Dialog to Find Functions
      2. Getting Help with Excel Functions
        1. Using On-Grid ToolTips
        2. Using the Function Arguments Dialog
        3. Using Excel Help
      3. Using AutoSum
        1. Potential Problems with AutoSum
        2. Special Tricks with AutoSum
        3. Using AutoAverage or AutoCount
        4. Function Reference Chapters
    4. 8. Using Everyday Functions: Math, Date and Time, and Text Functions
      1. Math Functions
      2. Date and Time Functions
      3. Text Functions
      4. Examples of Math Functions
        1. Using SUM to Add Numbers
        2. Using AGGREGATE to Ignore Error Cells or Filtered Rows
        3. Rounding Numbers
        4. Using SUBTOTAL Instead of SUM with Multiple Levels of Totals
        5. Totaling Visible Cells Using SUBTOTAL
        6. Using RAND and RANDBETWEEN to Generate Random Numbers and Data
        7. Using =ROMAN() to Finish Movie Credits and =ARABIC() to Convert Back to Digits
        8. Using ABS() to Figure Out the Magnitude of Error
        9. Using GCD and LCM to Perform Seventh-Grade Math
        10. Using MOD to Find the Remainder Portion of a Division Problem
        11. Using SQRT and POWER to Calculate Square Roots and Exponents
        12. Using COUNTIF, AVERAGEIF, and SUMIF to Conditionally Count, Average, or Sum Data
        13. Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS()
        14. Dates and Times in Excel
        15. Understanding Excel Date and Time Formats
      5. Examples of Date and Time Functions
        1. Using NOW and TODAY to Calculate the Current Date and Time or Current Date
        2. Using YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND to Break a Date/Time Apart
        3. Using DATE to Calculate a Date from Year, Month, and Day
        4. Using TIME to Calculate a Time
        5. Using DATEVALUE to Convert Text Dates to Real Dates
        6. Using TIMEVALUE to Convert Text Times to Real Times
        7. Using WEEKDAY to Group Dates by Day of the Week
        8. Using WEEKNUM or ISOWEEKNUM to Group Dates into Weeks
        9. Calculating Elapsed Time
        10. Using EOMONTH to Calculate the End of the Month
        11. Using WORKDAY or NETWORKDAYS or Their International Equivalents to Calculate Workdays
        12. Using International Versions of WORKDAY or NETWORKDAYS
      6. Examples of Text Functions
        1. Joining Text with the Ampersand (&) Operator
        2. Using LOWER, UPPER, or PROPER to Convert Text Case
        3. Using TRIM to Remove Leading and Trailing Spaces
        4. Using the CHAR or UNICHAR Function to Generate Any Character
        5. Using the CODE or UNICODE Function to Learn the Character Number for Any Character
        6. Using LEFT, MID, or RIGHT to Split Text
        7. Using LEN to Find the Number of Characters in a Text Cell
        8. Using SEARCH or FIND to Locate Characters in a Particular Cell
        9. Using SUBSTITUTE to Replace Characters
        10. Using REPT to Repeat Text Multiple Times
        11. Using EXACT to Test Case
        12. Using TEXT to Format a Number as Text
        13. Using the T and VALUE Functions
    5. 9. Using Powerful Functions: Logical, Lookup, Web, and Database Functions
      1. Examples of Logical Functions
        1. Using the IF Function to Make a Decision
        2. Using the AND Function to Check for Two or More Conditions
        3. Using OR to Check Whether One or More Conditions Are Met
        4. Using the NOT Function to Simplify the Use of AND and OR
        5. Using the IFERROR or IFNA Function to Simplify Error Checking
      2. Examples of Information Functions
        1. Using the ISFORMULA Function with Conditional Formatting to Mark Formula Cells
        2. Using IS Functions to Test for Types of Values
        3. Using the N Function to Add a Comment to a Formula
        4. Using the NA Function to Force Charts to Not Plot Missing Data
        5. Using the CELL Function to Return the Worksheet Name
      3. Examples of Lookup and Reference Functions
        1. Using the CHOOSE Function for Simple Lookups
        2. Using VLOOKUP with TRUE to Find a Value Based on a Range
        3. Using the MATCH Function to Locate the Position of a Matching Value
        4. Using INDEX and MATCH for a Left Lookup
        5. Using MATCH and INDEX to Fill a Wide Table
        6. Performing Many Lookups with LOOKUP
        7. Using FORMULATEXT to Document a Worksheet
        8. Using Numbers with OFFSET to Describe a Range
        9. Using INDIRECT to Build and Evaluate Cell References On the Fly
        10. Using the HYPERLINK Function to Quickly Add Hyperlinks
        11. Using the TRANSPOSE Function to Formulaically Turn Data
        12. Using GETPIVOTDATA to Retrieve One Cell from a Pivot Table
      4. Examples of Database Functions
        1. Using DSUM to Conditionally Sum Records from a Database
        2. Using the DGET Function
    6. 10. Other Functions
      1. Web Functions
      2. Financial Functions
      3. Statistical Functions
      4. Trigonometry Functions
      5. Matrix Functions
      6. Engineering Functions
    7. 11. Connecting Worksheets and Workbooks
      1. Connecting Two Worksheets
        1. Creating Links Using the Paste Options Menu
        2. Creating Links Using the Right-Drag Menu
        3. Building a Link by Using the Mouse
        4. Links to External Workbooks Default to Absolute References
        5. Building a Formula by Typing
        6. Creating Links to Unsaved Workbooks
        7. Using the Links Tab on the Trust Center
        8. Opening Workbooks with Links to Closed Workbooks
        9. Dealing with Missing Linked Workbooks
        10. Preventing the Update Links Dialog from Appearing
    8. 12. Array Formulas and Names in Excel
      1. Advantages of Using Names
      2. Naming a Cell by Using the Name Dialog
      3. Using the Name Box for Quick Navigation
      4. Avoiding Problems by Using Worksheet-Level Scope
        1. Defining a Worksheet-Level Name
        2. Referring to Worksheet-Level Names
      5. Using Named Ranges to Simplify Formulas
      6. Retroactively Applying Names to Formulas
      7. Using Names to Refer to Ranges
      8. Adding Many Names at Once from Existing Labels and Headings
      9. Using Intersection to Do a Two-Way Lookup
      10. Using Implicit Intersection
      11. Using a Name to Avoid an Absolute Reference
      12. Using a Name to Hold a Value
      13. Assigning a Formula to a Name
      14. Using Power Formula Techniques
        1. Using 3D Formulas to Spear Through Many Worksheets
        2. Referring to the Previous Worksheet
      15. Combining Multiple Formulas into One Formula
        1. Turning a Range of Formulas on Its Side
        2. Coercing a Range of Dates Using an Array Formula
  14. III: Data Analysis with Excel
    1. 13. Transforming Data
      1. Using Power Query
        1. Specifying the Data Source
        2. Transforming Data in Power Query
        3. Unpivoting Data in Power Query
        4. Correcting a Mistake in Power Query
        5. Adding Columns in Power Query
        6. Reviewing the Query
        7. Loading and Refreshing the Data
      2. Cleaning Data with Flash Fill
        1. Coaching Flash Fill with a Second Example
        2. Flash Fill Will Not Automatically Fill In Numbers
        3. Using Formatting with Dates
        4. Troubleshooting Flash Fill
      3. Sorting Data
        1. Sorting by Color or Icon
        2. Factoring Case into a Sort
        3. Reordering Columns with a Left-to-Right Sort
        4. Sorting into a Unique Sequence by Using Custom Lists
        5. One-Click Sorting
        6. Fixing Sort Problems
      4. Discovering Interesting Things in Your Data Using the Quick Analysis
    2. 14. Summarizing Data Using Subtotals or Filter
      1. Adding Automatic Subtotals
      2. Working with the Subtotals
        1. Showing a One-Page Summary with Only the Subtotals
        2. Sorting the Collapsed Subtotal View So the Largest Customers Are on Top
        3. Copying Only the Subtotal Rows
        4. Formatting the Subtotal Rows
        5. Removing Subtotals
      3. Subtotaling Multiple Fields
      4. Filtering Records
        1. Using a Filter
        2. Selecting One or Multiple Items from the Filter Drop-Down
        3. Identifying Which Columns Have Filters Applied
        4. Combining Filters
        5. Clearing Filters
        6. Refreshing Filters
        7. Resizing the Filter Drop-Down
        8. Filtering by Selection—Hard Way
        9. Filtering by Selection—Easy Way
        10. Filtering by Color or Icon
        11. Handling Date Filters
        12. Using Special Filters for Dates, Text, and Numbers
        13. Totaling Filtered Results
        14. Formatting and Copying Filtered Results
      5. Using the Advanced Filter Command
        1. Advanced Filter Criteria
      6. Using Remove Duplicates to Find Unique Values
      7. Combining Duplicates and Adding Values
    3. 15. Using Pivot Tables to Analyze Data
      1. Creating Your First Pivot Table
        1. Browsing Ten “Recommended” Pivot Tables
        2. Starting with a Blank Pivot Table
        3. Adding Fields to Your Pivot Table Using the Field List
        4. Changing the Pivot Table Report by Using the Field List
      2. Dealing with the Compact Layout
      3. Rearranging a Pivot Table
      4. Finishing Touches: Numeric Formatting and Removing Blanks
      5. Four Things You Have to Know When Using Pivot Tables
        1. Your Pivot Table Is in Manual Calculation Mode Until You Click Refresh!
        2. One Blank Cell in a Value Column Causes Excel to Count Instead of Sum
        3. If You Click Outside the Pivot Table, All the Pivot Table Tools Disappear
        4. You Cannot Change, Move a Part of, or Insert Cells in a Pivot Table
      6. Calculating and Roll-ups with Pivot Tables
        1. Grouping Daily Dates to Months and Years
        2. Adding Calculations Outside the Pivot Table
        3. Changing the Calculation of a Field
        4. Showing Percentage of Total Using Show Value As Settings
        5. Showing Running Totals and Rank
        6. Using a Formula to Add a Field to a Pivot Table
      7. Formatting a Pivot Table
      8. Finding More Information on Pivot Tables
    4. 16. Using Slicers and Filtering a Pivot Table
      1. Filtering Using the Row Label Filter
        1. Clearing a Filter
        2. Filtering Using the Check Boxes
        3. Filtering Using the Label Filter Fly-Out
        4. Filtering Using the Date Filters
        5. Filtering to the Top 10
      2. Filtering Using Slicers
        1. Adding Slicers
        2. Arranging the Slicers
        3. Using the Slicers in Excel 2016
      3. Filtering Dates
      4. Filtering Oddities
        1. AutoFiltering a Pivot Table
        2. Replicating a Pivot Table for Every Customer
      5. Sorting a Pivot Table
    5. 17. Mashing Up Data with PowerPivot
      1. Joining Multiple Tables Using the Data Model
        1. Preparing Data for Use in the Data Model
        2. Building a Pivot Table from the Data Model
        3. Adding the Second Table and Defining a Relationship
        4. Understanding the Limitations of the Data Model
      2. Benefits of Moving to PowerPivot
        1. Count Distinct Using DAX
        2. Date Intelligence Using DAX
      3. Interactive Dashboards with Power View
    6. 18. Using What-If, Scenario Manager, Goal Seek, and Solver
      1. Using What-If
        1. Creating a Two-Variable What-If Table
        2. Modeling a Random Scenario Using a Data Table
      2. Using Scenario Manager
        1. Creating a Scenario Summary Report
        2. Adding Multiple Scenarios
      3. Using Goal Seek
      4. Using Solver
        1. Installing Solver
        2. Solving a Model Using Solver
    7. 19. Automating Repetitive Functions Using VBA Macros
      1. Checking Security Settings Before Using Macros
      2. Recording a Macro
      3. Case Study: Macro for Formatting for a Mail Merge
        1. How Not to Record a Macro: The Default State of the Macro Recorder
        2. Relative References in Macro Recording
        3. Starting the Macro Recorder
        4. Running a Macro
      4. Everyday-Use Macro Example: Formatting an Invoice Register
        1. Using the Ctrl+Down-Arrow Key to Handle a Variable Number of Rows
        2. Making Sure You Find the Last Record
        3. Recording the Macro in a Blank Workbook
        4. Editing a Macro
      5. Understanding VBA Code—An Analogy
        1. Comparing Object.Method to Nouns and Verbs
        2. Comparing Collections to Plural Nouns
        3. Comparing Parameters to Adverbs
        4. Comparing Adjectives to Properties
        5. Using the Analogy While Examining Recorded Code
      6. Using Simple Variables and Object Variables
        1. Using R1C1-Style Formulas
        2. Fixing AutoSum Errors in Macros
      7. Customizing the Everyday-Use Macro Example: GETOPENFILENAME and GETSAVEASFILENAME
      8. From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges
        1. Finding the Last Row with Data
        2. Looping Through All Rows
        3. Referring to Ranges
        4. Combining a Loop with FinalRow
        5. Making Decisions by Using Flow Control
        6. Putting Together the From-Scratch Example: Testing Each Record in a Loop
        7. A Special Case: Deleting Some Records
      9. Combination Macro Example: Creating a Report for Each Customer
        1. Using the Advanced Filter for Unique Records
        2. Using AutoFilter
        3. Selecting Visible Cells Only
        4. Combination Macro Example: Putting It All Together
    8. 20. More Tips and Tricks for Excel 2016
      1. Watching the Results of a Distant Cell
      2. Comparing Documents Side by Side with Synchronous Scrolling
      3. Calculating a Formula in Slow Motion
      4. Inserting a Symbol in a Cell
      5. Editing an Equation
      6. Protecting a Worksheet
      7. Separating Text Based on a Delimiter
      8. Auditing Worksheets Using Inquire
  15. IV: Excel Visuals
    1. 21. Formatting Worksheets
      1. Why Format Worksheets?
      2. Using Traditional Formatting
        1. Changing Numeric Formats by Using the Home Tab
        2. Changing Numeric Formats by Using Built-in Formats in the Format Cells Dialog
        3. Using Numeric Formatting with Thousands Separators
        4. Displaying Currency
        5. Displaying Dates and Times
        6. Displaying Fractions
        7. Displaying ZIP Codes, Telephone Numbers, and Social Security Numbers
        8. Changing Numeric Formats Using Custom Formats
        9. Using the Four Zones of a Custom Number Format
        10. Controlling Text and Spacing in a Custom Number Format
        11. Controlling Decimal Places in a Custom Number Format
        12. Using Conditions and Color in a Custom Number Format
        13. Using Dates and Times in a Custom Number Format
        14. Displaying Scientific Notation in Custom Number Formats
        15. Aligning Cells
        16. Changing Font Size
        17. Changing Font Typeface
        18. Applying Bold, Italic, and Underline
        19. Using Borders
        20. Coloring Cells
        21. Adjusting Column Widths and Row Heights
        22. Using Merge and Center
        23. Rotating Text
      3. Formatting with Styles
      4. Understanding Themes
        1. Choosing a New Theme
        2. Creating a New Theme
        3. Reusing Another Theme’s Effects
        4. Saving a Custom Theme
        5. Using a Theme on a New Document
        6. Sharing a Theme with Others
      5. Other Formatting Techniques
        1. Formatting Individual Characters
        2. Changing the Default Font
        3. Wrapping Text in a Cell
        4. Justifying Text in a Range
        5. Adding Cell Comments
      6. Copying Formats
        1. Pasting Formats
        2. Pasting Conditional Formats
        3. Using the Format Painter
        4. Copying Formats to a New Worksheet
    2. 22. Using Data Visualizations and Conditional Formatting
      1. Using Data Bars to Create In-Cell Bar Charts
        1. Creating Data Bars
        2. Customizing Data Bars
        3. Showing Data Bars for a Subset of Cells
      2. Using Color Scales to Highlight Extremes
      3. Using Icon Sets to Segregate Data
        1. Setting Up an Icon Set
        2. Moving Numbers Closer to Icons
        3. Mixing Icons or Hiding Icons
      4. Using the Top/Bottom Rules
      5. Using the Highlight Cells Rules
        1. Highlighting Cells by Using Greater Than and Similar Rules
        2. Comparing Dates by Using Conditional Formatting
        3. Identifying Duplicate or Unique Values by Using Conditional Formatting
        4. Using Conditional Formatting for Text Containing a Value
      6. Tweaking Rules with Advanced Formatting
        1. Using a Formula for Rules
        2. Getting to the Formula Box
        3. Working with the Formula Box
        4. Finding Cells Within Three Days of Today
        5. Finding Cells Containing Data from the Past 30 Days
        6. Highlighting Data from Specific Days of the Week
        7. Highlighting an Entire Row
        8. Highlighting Every Other Row Without Using a Table
      7. Combining Rules
      8. Extending the Reach of Conditional Formats
      9. Special Considerations for Pivot Tables
    3. 23. Graphing Data Using Excel Charts
      1. Choosing from Recommended Charts
        1. Using the Paintbrush Icon for Styles
        2. Deleting Extraneous Data Using the Funnel
        3. Changing Chart Options Using the Plus Icon
      2. Easy Combo Charts
      3. Using the New Hierarchy Charts
      4. Creating a Frequency Distribution with a Histogram Chart
      5. Describe the Statistics of a Data Set with a Box and Whisker Chart
      6. Showing Financial Data with a Waterfall Chart
      7. Saving Time with Charting Tricks
        1. Adding New Data to a Chart by Pasting
        2. Dealing with Small Pie Slices
        3. Saving a Favorite Chart Style As a Template
    4. 24. Using 3D Maps
      1. Examples of 3D Maps
        1. Adding Color Information for Categories
        2. Zooming In
        3. Animating Over Time
        4. Going Ultra-Local
      2. Getting Your Data into 3D Map
      3. 3D Map Techniques
        1. Tipping, Rotating, and Zooming the Map
        2. Adding a Photo to a Point
        3. Combining Layers
        4. Changing Column Size or Color
        5. Resizing the Various Panes
        6. Adding a Satellite Photograph
        7. Showing the Whole Earth
        8. Understanding the Time Choices
        9. Animating a Line Between Two Points
        10. Controlling Map Labels
      4. Building a Tour and Creating a Video
      5. Using an Alternate Map
        1. Preparing the Store Image
        2. Specifying a Custom Map
    5. 25. Using Sparklines
      1. Fitting a Chart into the Size of a Cell with Sparklines
      2. Understanding How Excel Maps Data to Sparklines
        1. Creating a Group of Sparklines
        2. Built-in Choices for Customizing Sparklines
        3. Controlling Axis Values for Sparklines
        4. Setting Up Win/Loss Sparklines
        5. Showing Detail by Enlarging the Sparkline and Adding Labels
        6. Other Sparkline Options
    6. 26. Decorating Spreadsheets
      1. Using SmartArt
        1. Elements Common in Most SmartArt
        2. Inserting SmartArt
        3. Changing Existing SmartArt to a New Style
        4. Adding Images to SmartArt
        5. Special Considerations for Organizational Charts and Hierarchical SmartArt
      2. Using Shapes to Display Cell Contents
      3. Working with Shapes
      4. Using WordArt for Interesting Titles and Headlines
        1. Using Text Boxes to Flow Long Text Passages
      5. Using Pictures and Clip Art
        1. Getting Your Picture into Excel
        2. Inserting a Picture from Your Computer
        3. Inserting Multiple Pictures at Once
        4. Inserting a Picture or Clip Art from Online
      6. Adjusting the Picture Using the Ribbon Tab
        1. Resizing the Picture to Fit
        2. Adjusting the Brightness and Contrast
        3. Adding Interesting Effects Using the Picture Styles Gallery
        4. Applying Artistic Effects
        5. Removing the Background
        6. Reducing a Picture’s File Size
      7. Inserting Screen Clippings
      8. Selecting and Arranging Pictures
    7. 27. Printing
      1. Printing in One Click
      2. Finding Print Settings
      3. Previewing the Printed Report
        1. Using the Print Preview on the Print Panel
        2. Using Full Screen Print Preview
        3. Making the Report Fit on the Page
        4. Setting Worksheet Paper Size
        5. Adjusting Worksheet Orientation
        6. Adjusting Worksheet Margins
        7. Repeating the Headings on Each Page
        8. Excluding Part of Your Worksheet from the Print Range
        9. Forcing More Data to Fit on a Page
      4. Working with Page Breaks
        1. Manually Adding Page Breaks
        2. Manual Versus Automatic Page Breaks
        3. Using Page Break Preview to Make Changes
        4. Removing Manual Page Breaks
      5. Adding Headers or Footers to the Printed Report
        1. Adding an Automatic Header
        2. Adding a Custom Header
        3. Inserting a Picture or a Watermark in a Header
        4. Using Different Headers and Footers in the Same Document
        5. Scaling Headers and Footers
      6. Printing from the File Menu
        1. Choosing a Printer
      7. Choosing What to Print
        1. Changing Printer Properties
        2. Changing Some of the Page Setup Settings
      8. Using Page Layout View
      9. Exploring Other Page Setup Options
        1. Printing Gridlines and Headings
        2. Centering a Small Report on a Page
        3. Replacing Error Values When Printing
        4. Printing Comments
        5. Controlling the First Page Number
    8. 28. Excel Online
      1. Accessing Your OneDrive Workbooks from Anywhere
        1. Understanding the Limitations of Excel Online
        2. Group Editing Using Excel Online
      2. Designing a Workbook as an Interactive Web Page
        1. Sharing a Link to Your Web Workbook
      3. Collecting Survey Data in Excel Online
      4. Creating a PDF from a Worksheet
  16. Index
  17. Code Snippets

Product information

  • Title: Excel 2016 In Depth
  • Author(s): Bill Jelen
  • Release date: December 2015
  • Publisher(s): Que
  • ISBN: 9780134386898