Book Description
You’re beyond the basics—so dive in and really put your spreadsheet skills to work! This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Learn how the experts tackle Excel 2013—and challenge yourself to new levels of mastery. Includes companion eBook and sample files.
Topics include:
Customizing the Excel workspace
Best practices for designing and managing worksheets
Creating formulas and functions
Performing statistical, whatif, and other data analysis
Core to advanced charting techniques
Using graphics and sparklines
Managing databases and tables
Automating Excel with macros and custom functions
Collaborating in Excel online, in the cloud, and more
Extending Excel
Table of Contents
 Dedication
 A Note Regarding Supplemental Files
 Introduction

I. Examining the Excel environment
 1. What’s new in Microsoft Excel 2013

2. Exploring Excel fundamentals
 Examining the Excel 2013 workspace
 Exploring filemanagement fundamentals
 Importing and exporting files
 Using the Help system
 Recovering from crashes
 3. Customtailoring the Excel workspace
 4. Security and privacy

II. Building worksheets
 5. Planning your worksheet design
 6. How to work a worksheet
 7. How to work a workbook

III. Formatting and editing worksheets

8. Worksheet editing techniques
 Copying, cutting, and pasting
 Inserting and deleting
 Undoing previous actions
 Editing cell contents
 Finding and replacing stuff
 Getting the words right
 Editing multiple worksheets
 Auditing and documenting worksheets
 Outlining worksheets
 Consolidating worksheets

9. Worksheet formatting techniques
 Formatting fundamentals
 Using themes and cell styles
 Formatting conditionally

Formatting in depth
 Formatting individual characters
 Formatting as you type
 Understanding the General format
 Formatting numbers
 Aligning data in cells
 Aligning text vertically
 Using fonts
 Customizing borders
 Applying colors and patterns
 Controlling the size of cells
 Using template files to store formatting

8. Worksheet editing techniques

IV. Adding graphics and printing

10. Creating and formatting graphics
 Using the Shapes tools
 Creating WordArt
 Creating SmartArt
 Inserting other graphics
 Formatting graphics
 Working with graphic objects
 More tricks with graphic objects
 11. Printing and presenting

10. Creating and formatting graphics

V. Creating formulas and performing data analysis

12. Building formulas

Formula fundamentals
 Understanding the precedence of operators
 Using cell references in formulas
 Editing formulas
 Understanding error values
 Using functions: A preview

Working with formulas

Naming cells and cell ranges
 Using names in formulas
 Defining and managing names
 Editing names
 Workbookwide vs. worksheetonly names
 Creating names semiautomatically
 Naming constants and formulas
 Using relative references in named formulas
 Creating threedimensional names
 Inserting names in formulas
 Creating a list of names
 Replacing references with names
 Using Go To with names
 Getting explicit about intersections
 Creating threedimensional formulas
 Formulabar formatting
 Using structured references

Naming cells and cell ranges
 Worksheet calculation
 Using arrays
 Linking workbooks
 Creating conditional tests

Formula fundamentals
 13. Using functions

14. Everyday functions
 Understanding mathematical functions
 Understanding text functions
 Understanding logical functions
 Understanding information functions
 Understanding lookup and reference functions
 15. Formatting and calculating date and time

16. Functions for financial analysis
 Calculating investments
 Calculating depreciation

Analyzing securities
 The DOLLARDE and DOLLARFR functions
 The ACCRINT and ACCRINTM functions
 The INTRATE and RECEIVED functions
 The PRICE, PRICEDISC, and PRICEMAT functions
 The DISC function
 The YIELD, YIELDDISC, and YIELDMAT functions
 The TBILLEQ, TBILLPRICE, and TBILLYIELD functions
 The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD functions
 The DURATION and MDURATION functions
 Using the Euro Currency Tools addin

17. Functions for analyzing statistics
 Analyzing distributions of data
 Understanding linear and exponential regression

Using the Analysis Toolpak data analysis tools
 Installing the Analysis Toolpak
 Using the Descriptive Statistics tool
 Creating histograms
 Using the Rank And Percentile tool

Generating random numbers
 Distributing random numbers uniformly
 Distributing random numbers normally
 Generating random numbers using Bernoulli distribution
 Generating random numbers using binomial distribution
 Generating random numbers using Poisson distribution
 Generating random numbers using discrete distribution
 Generating semirandom numbers using patterned distribution
 Sampling a population of numbers
 Calculating moving averages
 18. Performing a whatif analysis

12. Building formulas

VI. Creating charts

19. Designing charts
 Selecting data for your chart
 Creating a new chart
 Changing the chart type
 Assigning a series to a secondary axis
 Switching rows and columns
 Choosing a chart layout
 Choosing a chart style
 Moving the chart to a separate chart sheet
 Adding, editing, and removing a chart title
 Adding, editing, and removing a legend
 Adding and positioning data labels
 Adding a data table
 Adding and removing gridlines
 Working with axes
 Modifying a chart’s data
 Adding moving averages and other trendlines
 Adding error bars
 20. Formatting charts
 21. Using sparklines

19. Designing charts

VII. Managing databases and tables

22. Managing information in tables
 How to organize a table
 Creating a table
 Adding totals to a table
 Sorting tables and other ranges

Filtering a list or table

Using filters
 Determining how many rows pass the filter
 Removing a filter
 Using filter criteria in more than one column
 Using a filter to find the top or bottom n items
 Using a filter to display blank entries
 Using filters to select dates
 Using filters to specify more complex criteria
 Using custom filters to specify complex relationships
 Using the Advanced Filter command
 Removing duplicate records

Using filters
 Using formulas with tables
 Formatting tables

23. Analyzing data with PivotTable reports
 Introducing PivotTables
 Creating a PivotTable
 Rearranging PivotTable fields
 Refreshing a PivotTable
 Changing the numeric format of PivotTable data
 Choosing report layout options
 Formatting a PivotTable
 Displaying totals and subtotals
 Sorting PivotTable fields
 Filtering PivotTable fields
 Changing PivotTable calculations
 Grouping and ungrouping data
 Displaying the details behind a data value
 Creating PivotCharts
 Moving beyond PivotTables
 24. An introduction to PowerPivot

25. Working with external data
 Using and reusing Office Data Connections
 Opening an entire Access table in Excel
 Working with data in text files
 Working with XML files

Using Microsoft Query to import data
 Choosing tables and fields (columns)
 Filtering records
 Sorting records
 Working directly with Microsoft Query
 Using a web query to return Internet data

22. Managing information in tables

VIII. Using Excel collaboratively
 26. Collaborating on a network or by email
 27. Working in the cloud

IX. Automating Excel
 28. Recording macros
 29. Creating custom functions

30. Debugging macros and custom functions
 Using designtime tools
 Dealing with runtime errors

X. Integrating and extending Excel
 31. Linking, hyperlinking, and embedding
 32. Using Excel data in Word documents

XI. Appendixes
 A. Menutoribbon command reference

B. Keyboard shortcuts
 Shortcuts by key: Function keys
 Shortcuts by key: Control and navigation keys
 Shortcuts by key: Numeric keys
 Shortcuts by key: Symbol keys and keypad
 Shortcuts by key: Letter keys
 Shortcuts by task: Insert charts
 Shortcuts by task: Work in dialog box text boxes
 Shortcuts by task: Work in dialog boxes
 Shortcuts by task: Edit data
 Shortcuts by task: Work with formulas
 Shortcuts by task: Enter data
 Shortcuts by task: Select cells
 Shortcuts by task: Filter tables
 Shortcuts by task: Work with borders
 Shortcuts by task: Format data
 Shortcuts by task: Work with Help
 Shortcuts by task: Insert, delete, and copy cells
 Shortcuts by task: Work with macros
 Shortcuts by task: Work with the ribbon
 Shortcuts by task: Move and scroll in End mode
 Shortcuts by task: Move and scroll in worksheets
 Shortcuts by task: Move within a selected range
 Shortcuts by task: Print
 Shortcuts by task: Work in Backstage view
 Shortcuts by task: Select cells, rows, columns, and objects
 Shortcuts by task: Select cells with special characteristics
 Shortcuts by task: Work with outlines
 Shortcuts by task: Work with options menus
 Shortcuts by task: Work with task panes
 Shortcuts by task: Navigate Windows
 Shortcuts by task: Work with worksheets
 C. Function reference
 D. Index to troubleshooting topics
 E. About the authors
 Index
 About the Authors
 Copyright
Product Information
 Title: Microsoft Excel 2013 Inside Out
 Author(s):
 Release date: April 2013
 Publisher(s): Microsoft Press
 ISBN: 9780735674264