B00b8v7ma6 Ebok by Jelen, Bill -- Read -- Imperial Library of Trantor

Index

Title Page Copyright Page Contents at a Glance Table of Contents About the Author Dedication Acknowledgments We Want to Hear from You! Reader Services Introduction

How This Book Is Organized Conventions Used in This Book

I: Mastering the New User Interface

1. Staying Connected Using Excel 2013

Displaying Two Workbooks on Two Monitors Signing In to Excel 2013 Introducing the Excel 2013 Start Screen Using the Cloud for Storage and More

2. Introducing Flash Fill and Quick Analysis

Cleaning Data with Flash Fill Discovering Interesting Things in Your Data Using the Quick Analysis

3. Using the Excel Interface

Using the Ribbon Using the Quick Access Toolbar Using the Full-Screen File Menu Using Other Excel Interface Improvements 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

4. Customizing Excel

Performing a Simple Ribbon Modification Adding a New Ribbon Tab Sharing Customizations with Others Questions About Ribbon Customization Introducing the Excel Options Dialog Ten Options to Consider Five Excel Oddities

5. Extending Excel with Excel Apps and Add-Ins

Using Apps for Office Using Traditional Add-Ins General-Purpose Utility Suites Utilities for Data Analysis Tasks

6. Keyboard Shortcuts

Using New Keyboard Accelerators Using the Shortcut Keys Using My Favorite Shortcut Keys Using Excel 2003 Keyboard Accelerators

7. The Big Grid and File Formats

Excel Grid Limits Why Are There Only 65,536 Rows in My Excel 2013 Spreadsheet? Other Limits in Excel 2013 Tips for Navigating the Big Grid Understanding the File Formats Version Compatibility Opening Excel 2013 Files in Excel 2002 or 2003 Creating Excel 2013 File Formats in Excel 2003 Opening Excel 2013 Files in Excel 2007

II: Calculating with Excel

8. Understanding Formulas

Getting the Most from This Chapter Introduction to Formulas Entering Your First Formula Three Methods of Entering Formulas Entering the Same Formula in Many Cells Use the Table Tool to Copy a Formula

9. Controlling Formulas

Formula Operators Understanding Error Messages in Formulas Using Formulas to Join Text Copying Versus Cutting a Formula Automatically Formatting Formula Cells Using Date Math Troubleshooting Formulas

10. Understanding Functions

Working with Functions Getting Help with Excel Functions Using AutoSum

11. Using Everyday Functions: Math, Date and Time, and Text Functions

Examples of Math Functions Dates and Times in Excel Examples of Date and Time Functions Examples of Text Functions

12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions

Examples of Logical Functions Examples of Information Functions Examples of Lookup and Reference Functions Cube Functions Introduced in Excel 2007 Examples of Web Functions Examples of Database Functions

13. Using Financial Functions

Examples of Common Household Loan and Investment Functions Examples of Functions for Financial Professionals Examples of Depreciation Functions Functions for Investment Analysis Examples of Functions for Bond Investors Examples of Miscellaneous Financial Functions

14. Using Statistical Functions

Functions That Have Been Renamed Examples of Functions for Descriptive Statistics Examples of Functions for Regression and Forecasting Examples of Functions for Inferential Statistics Using the Analysis ToolPak to Perform Statistical Analysis

15. Using Trig, Matrix, and Engineering Functions

A Brief Review of Trigonometry Basics Examples of Logarithm Functions Working with Imaginary Numbers Solving Simultaneous Linear Equations with Matrix Functions Examples of Engineering Functions Using the Analysis Toolpack to Perform Fast Fourier Transforms (FFTs)

16. Connecting Worksheets, Workbooks, and External Data

Connecting Two Worksheets Connecting to Data on a Web Page Setting Up a Connection to a Text File Setting Up a Connection to an Access Database Setting Up SQL Server, XML, OLE DB, and ODBC Connections Managing Connections

17. Using Super Formulas in Excel

Using 3D Formulas to Spear Through Many Worksheets Combining Multiple Formulas into One Formula Calculating a Cell Reference in the Formula by Using the INDIRECT Function Assigning a Formula to a Name Replacing Multiple Formulas with One Array Formula

18. Using 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 Managing Names Using a Name to Simplify an Absolute Reference Using a Name to Hold a Value Assigning a Formula to a Name

19. Fabulous Table Intelligence

Defining Suitable Data for Excel Tables Adding a Total Row to a Table Toggling Totals Expanding a Table Adding New Formulas to Tables Selecting Only the Data in the Column Using Table Data for Charts to Ensure Stickiness Replacing Named Ranges with Table References Creating Banded Rows and Columns with Table Styles Dealing with the Filter Drop-Downs

III: Business Intelligence

20. Sorting Data

Introducing the Sort Dialog Using Specialized Sorting One-Click Sorting Sorting Randomly

21. Removing Duplicates and Filtering

Filtering Records Sorting Filtered Results Using the Advanced Filter Command Using Remove Duplicates to Find Unique Values

22. Using Automatic Subtotals

Adding Automatic Subtotals Working with the Subtotals Using Specialty Subtotal Techniques

23. 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

24. Using Slicers and Filtering a Pivot Table

Filtering Using the Row Label Filter Filtering Using Filter Fields Filtering Using Slicers Filtering Using Timelines Filtering Oddities Sorting a Pivot Table

25. Mashing Up Data with PowerPivot

Joining Multiple Tables Using the Data Model in Regular Excel 2013 Benefits of Moving to PowerPivot Enabling PowerPivot Case Study: Building a PowerPivot Report Some Things Are Different Two Kinds of DAX Calculations Using DAX to Create a Calculated Field in the Pivot Table Defining KPIs with PowerPivot Using QuickExplore Other Notes

26. Creating Interactive Dashboards with Power View or GeoFlow

Preparing Your Data for Power View Creating a Power View Worksheet Replicating Charts Using Multiples Showing Data on a Map Using Table or Card View with Images Animating a Scatter Chart Over Time Using Drill-Down Some Closing Tips on Power View Creating a Map in GeoFlow

27. Using What-If, Scenario Manager, Goal Seek, and Solver

Using What-If Using Scenario Manager Using Goal Seek Using Solver

28. 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

29. More Tips and Tricks for Excel 2013

Speeding Up Calculation Watching the Results of a Distant Cell Opening the Same Files Every Day Comparing Documents Side by Side with Synchronous Scrolling Calculating a Formula in Slow Motion Inserting a Symbol in a Cell Edit an Equation Adding a Digital Signature Line to a Workbook Protecting a Worksheet Sharing a Workbook Separating Text Based on a Delimiter Translating Text Auditing Worksheets Using Inquire

IV: Visual Presentation

30. Formatting Worksheets

Why Format Worksheets? Using Traditional Formatting Formatting with Styles Understanding Themes Other Formatting Techniques Copying Formats

31. 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 Combining Rules Clearing Conditional Formats Extending the Reach of Conditional Formats Special Considerations for Pivot Tables

32. Graphing Data Using Excel Charts

Choosing from Recommended Charts Using Paintbrush Icon for Styles Deleting Extraneous Data Using the Funnel Changing Chart Options Using the Plus Icon Showing Two Orders of Magnitude Using a Combo Chart Labeling Charts Controlling the Axis Range Seeing the Relationship Between Two Variables in a Scatter Chart Summarizing Data Using the Quick Analysis Icon Saving Time with Charting Tricks

33. Using Sparklines

Fitting a Chart into the Size of a Cell with Sparklines Understanding How Excel Maps Data to Sparklines

34. Using SmartArt, Shapes, WordArt, and Text Boxes

Using SmartArt Using Shapes to Display Cell Contents Using WordArt for Interesting Titles and Headlines

35. Using Pictures and Clip Art

Getting Your Picture into Excel Adjusting the Picture Using the Ribbon Tab Using the Format Picture Task Pane Inserting Screen Clippings Selecting and Arranging Pictures

V: Sharing Information

36. Printing

Printing in One Click Finding Print Settings Previewing the Printed Report 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

37. Excel Web App and Other Ways to Share Workbooks

Viewing Your SkyDrive Workbooks from Anywhere Designing a Workbook as an Interactive Web Page Collecting Survey Data in the Excel Web App Make Any Web Table Interactive with Excel Everywhere Creating a PDF from a Worksheet Interacting with Other Office Applications

38. Saving Time Using the Easy-XL Program

Downloading and Installing Easy-XL Easy-XL Works Best with Tabular Data Doing Away with VLOOKUP Using a Fuzzy Match Text to Columns on Steroids Sorting Columns Left to Right Summarizing Data Cleansing Data Without Using TRIM(), PROPER(), or CLEAN() There’s More

Index

← Prev
Back
Next →

← Prev
Back
Next →