- 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
Dedication
To Robert K. Jelen, my favorite brother
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.