- About This E-Book
- Title Page
- Copyright Page
- Contents at a Glance
- Contents
- About the Author
- Dedication
- Acknowledgments
- We Want to Hear from You!
- Reader Services
- Introduction
- I: The Excel Interface
- 1. What’s New in Excel 2016 (and 2013)
- Color Returns to the Excel Interface
- The Data Model from Excel 2013 Is the Most Important Feature in 2016
- Clean Your Data with Power Query
- Pivot Your Data on a Map with 3D Maps
- View Your Data Using Six New Chart Types
- Forecast the Future Using a Forecast Sheet
- Important Features from Excel 2013
- Oddities Added to Excel 2016
- 2. Using the Excel Interface
- Using the Ribbon
- Using the Quick Access Toolbar
- Using the Full-Screen File Menu
- Pressing the Esc Key to Close Backstage View
- Recovering Unsaved Workbooks
- Clearing the Recent Workbooks List
- Getting Information About the Current Workbook
- Marking a Workbook as Final to Prevent Editing
- Finding Hidden Content Using the Document Inspector
- Adding Whitespace Around Icons Using Touch Mode
- Previewing Paste Using the Paste Options Gallery
- Accessing the Gallery After Performing a Paste Operation
- Accessing the Paste Options Gallery from the Right-Click Menu
- Using the New Sheet Icon to Add Worksheets
- Navigating Through Many Worksheets Using the Controls in the Lower Left
- Using the Mini Toolbar to Format Selected Text
- Expanding the Formula Bar
- Zooming In and Out on a Worksheet
- Using the Status Bar to Add Numbers
- Switching Between Normal View, Page Break Preview, and Page Layout View Modes
- 3. Customizing Excel
- 4. Keyboard Shortcuts
- Using Keyboard Accelerators
- Using the Shortcut Keys
- Using My Favorite Shortcut Keys
- Quickly Move Between Worksheets
- Jumping to the Bottom of Data with Ctrl+Arrow
- Selecting the Current Region with Ctrl+*
- Jumping to the Next Corner of a Selection
- Pop Open the Right-Click Menu Using Shift+F10
- Crossing Tasks Off Your List with Ctrl+5
- Date Stamp or Time Stamp Using Ctrl+; or Ctrl+:
- Repeating the Last Task with F4
- Adding Dollar Signs to a Reference with F4
- Choosing Items from a Slicer
- Finding the One Thing That Takes You Too Much Time
- Using Excel 2003 Keyboard Accelerators
- 1. What’s New in Excel 2016 (and 2013)
- II: Calculating with Excel
- 5. Understanding Formulas
- 6. Controlling Formulas
- 7. Understanding Functions
- 8. Using Everyday Functions: Math, Date and Time, and Text Functions
- Math Functions
- Date and Time Functions
- Text Functions
- Examples of Math Functions
- Using SUM to Add Numbers
- Using AGGREGATE to Ignore Error Cells or Filtered Rows
- Rounding Numbers
- Using SUBTOTAL Instead of SUM with Multiple Levels of Totals
- Totaling Visible Cells Using SUBTOTAL
- Using RAND and RANDBETWEEN to Generate Random Numbers and Data
- Using =ROMAN() to Finish Movie Credits and =ARABIC() to Convert Back to Digits
- Using ABS() to Figure Out the Magnitude of Error
- Using GCD and LCM to Perform Seventh-Grade Math
- Using MOD to Find the Remainder Portion of a Division Problem
- Using SQRT and POWER to Calculate Square Roots and Exponents
- Using COUNTIF, AVERAGEIF, and SUMIF to Conditionally Count, Average, or Sum Data
- Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS()
- Dates and Times in Excel
- Understanding Excel Date and Time Formats
- Examples of Date and Time Functions
- Using NOW and TODAY to Calculate the Current Date and Time or Current Date
- Using YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND to Break a Date/Time Apart
- Using DATE to Calculate a Date from Year, Month, and Day
- Using TIME to Calculate a Time
- Using DATEVALUE to Convert Text Dates to Real Dates
- Using TIMEVALUE to Convert Text Times to Real Times
- Using WEEKDAY to Group Dates by Day of the Week
- Using WEEKNUM or ISOWEEKNUM to Group Dates into Weeks
- Calculating Elapsed Time
- Using EOMONTH to Calculate the End of the Month
- Using WORKDAY or NETWORKDAYS or Their International Equivalents to Calculate Workdays
- Using International Versions of WORKDAY or NETWORKDAYS
- Examples of Text Functions
- Joining Text with the Ampersand (&) Operator
- Using LOWER, UPPER, or PROPER to Convert Text Case
- Using TRIM to Remove Leading and Trailing Spaces
- Using the CHAR or UNICHAR Function to Generate Any Character
- Using the CODE or UNICODE Function to Learn the Character Number for Any Character
- Using LEFT, MID, or RIGHT to Split Text
- Using LEN to Find the Number of Characters in a Text Cell
- Using SEARCH or FIND to Locate Characters in a Particular Cell
- Using SUBSTITUTE to Replace Characters
- Using REPT to Repeat Text Multiple Times
- Using EXACT to Test Case
- Using TEXT to Format a Number as Text
- Using the T and VALUE Functions
- 9. Using Powerful Functions: Logical, Lookup, Web, and Database Functions
- Examples of Logical Functions
- Examples of Information Functions
- Examples of Lookup and Reference Functions
- Using the CHOOSE Function for Simple Lookups
- Using VLOOKUP with TRUE to Find a Value Based on a Range
- Using the MATCH Function to Locate the Position of a Matching Value
- Using INDEX and MATCH for a Left Lookup
- Using MATCH and INDEX to Fill a Wide Table
- Performing Many Lookups with LOOKUP
- Using FORMULATEXT to Document a Worksheet
- Using Numbers with OFFSET to Describe a Range
- Using INDIRECT to Build and Evaluate Cell References On the Fly
- Using the HYPERLINK Function to Quickly Add Hyperlinks
- Using the TRANSPOSE Function to Formulaically Turn Data
- Using GETPIVOTDATA to Retrieve One Cell from a Pivot Table
- Examples of Database Functions
- 10. Other Functions
- 11. Connecting Worksheets and Workbooks
- Connecting Two Worksheets
- Creating Links Using the Paste Options Menu
- Creating Links Using the Right-Drag Menu
- Building a Link by Using the Mouse
- Links to External Workbooks Default to Absolute References
- Building a Formula by Typing
- Creating Links to Unsaved Workbooks
- Using the Links Tab on the Trust Center
- Opening Workbooks with Links to Closed Workbooks
- Dealing with Missing Linked Workbooks
- Preventing the Update Links Dialog from Appearing
- Connecting Two Worksheets
- 12. Array Formulas and Names in Excel
- Advantages of Using Names
- Naming a Cell by Using the Name Dialog
- Using the Name Box for Quick Navigation
- Avoiding Problems by Using Worksheet-Level Scope
- Using Named Ranges to Simplify Formulas
- Retroactively Applying Names to Formulas
- Using Names to Refer to Ranges
- Adding Many Names at Once from Existing Labels and Headings
- Using Intersection to Do a Two-Way Lookup
- Using Implicit Intersection
- Using a Name to Avoid an Absolute Reference
- Using a Name to Hold a Value
- Assigning a Formula to a Name
- Using Power Formula Techniques
- Combining Multiple Formulas into One Formula
- III: Data Analysis with Excel
- 13. Transforming Data
- 14. Summarizing Data Using Subtotals or Filter
- Adding Automatic Subtotals
- Working with the Subtotals
- Subtotaling Multiple Fields
- Filtering Records
- Using a Filter
- Selecting One or Multiple Items from the Filter Drop-Down
- Identifying Which Columns Have Filters Applied
- Combining Filters
- Clearing Filters
- Refreshing Filters
- Resizing the Filter Drop-Down
- Filtering by Selection—Hard Way
- Filtering by Selection—Easy Way
- Filtering by Color or Icon
- Handling Date Filters
- Using Special Filters for Dates, Text, and Numbers
- Totaling Filtered Results
- Formatting and Copying Filtered Results
- Using the Advanced Filter Command
- Using Remove Duplicates to Find Unique Values
- Combining Duplicates and Adding Values
- 15. Using Pivot Tables to Analyze Data
- Creating Your First Pivot Table
- Dealing with the Compact Layout
- Rearranging a Pivot Table
- Finishing Touches: Numeric Formatting and Removing Blanks
- Four Things You Have to Know When Using Pivot Tables
- Calculating and Roll-ups with Pivot Tables
- Formatting a Pivot Table
- Finding More Information on Pivot Tables
- 16. Using Slicers and Filtering a Pivot Table
- 17. Mashing Up Data with PowerPivot
- 18. Using What-If, Scenario Manager, Goal Seek, and Solver
- 19. Automating Repetitive Functions Using VBA Macros
- Checking Security Settings Before Using Macros
- Recording a Macro
- Case Study: Macro for Formatting for a Mail Merge
- Everyday-Use Macro Example: Formatting an Invoice Register
- Understanding VBA Code—An Analogy
- Using Simple Variables and Object Variables
- Customizing the Everyday-Use Macro Example: GETOPENFILENAME and GETSAVEASFILENAME
- From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges
- Combination Macro Example: Creating a Report for Each Customer
- 20. More Tips and Tricks for Excel 2016
- IV: Excel Visuals
- 21. Formatting Worksheets
- Why Format Worksheets?
- Using Traditional Formatting
- Changing Numeric Formats by Using the Home Tab
- Changing Numeric Formats by Using Built-in Formats in the Format Cells Dialog
- Using Numeric Formatting with Thousands Separators
- Displaying Currency
- Displaying Dates and Times
- Displaying Fractions
- Displaying ZIP Codes, Telephone Numbers, and Social Security Numbers
- Changing Numeric Formats Using Custom Formats
- Using the Four Zones of a Custom Number Format
- Controlling Text and Spacing in a Custom Number Format
- Controlling Decimal Places in a Custom Number Format
- Using Conditions and Color in a Custom Number Format
- Using Dates and Times in a Custom Number Format
- Displaying Scientific Notation in Custom Number Formats
- Aligning Cells
- Changing Font Size
- Changing Font Typeface
- Applying Bold, Italic, and Underline
- Using Borders
- Coloring Cells
- Adjusting Column Widths and Row Heights
- Using Merge and Center
- Rotating Text
- Formatting with Styles
- Understanding Themes
- Other Formatting Techniques
- Copying Formats
- 22. Using Data Visualizations and Conditional Formatting
- Using Data Bars to Create In-Cell Bar Charts
- Using Color Scales to Highlight Extremes
- Using Icon Sets to Segregate Data
- Using the Top/Bottom Rules
- Using the Highlight Cells Rules
- Tweaking Rules with Advanced Formatting
- Using a Formula for Rules
- Getting to the Formula Box
- Working with the Formula Box
- Finding Cells Within Three Days of Today
- Finding Cells Containing Data from the Past 30 Days
- Highlighting Data from Specific Days of the Week
- Highlighting an Entire Row
- Highlighting Every Other Row Without Using a Table
- Combining Rules
- Extending the Reach of Conditional Formats
- Special Considerations for Pivot Tables
- 23. Graphing Data Using Excel Charts
- 24. Using 3D Maps
- 25. Using Sparklines
- 26. Decorating Spreadsheets
- 27. Printing
- Printing in One Click
- Finding Print Settings
- Previewing the Printed Report
- Using the Print Preview on the Print Panel
- Using Full Screen Print Preview
- Making the Report Fit on the Page
- Setting Worksheet Paper Size
- Adjusting Worksheet Orientation
- Adjusting Worksheet Margins
- Repeating the Headings on Each Page
- Excluding Part of Your Worksheet from the Print Range
- Forcing More Data to Fit on a Page
- Working with Page Breaks
- Adding Headers or Footers to the Printed Report
- Printing from the File Menu
- Choosing What to Print
- Using Page Layout View
- Exploring Other Page Setup Options
- 28. Excel Online
- 21. Formatting Worksheets
- Index
- Code Snippets
About the Author
Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. He loves performing his half-day Power Excel seminar around the world. He has produced more than 1,900 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 45 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. Before founding MrExcel.com, Bill Jelen spent 12 years in the trenches—working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives in Merritt Island, Florida, with his wife, Mary Ellen.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.