Microsoft Excel 2013 Inside Out

Microsoft Excel 2013 Inside Out

Released April 2013
Publisher(s): Microsoft Press
ISBN: 9780735674264

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

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, what-if, 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

    1. Dedication
    2. A Note Regarding Supplemental Files
    3. Introduction
      1. Who this book is for
        1. Assumptions about you
      2. How this book is organized
      3. About the companion content
      4. Acknowledgments
      5. Support and feedback
        1. Errata & support
        2. We want to hear from you
        3. Stay in touch
    4. I. Examining the Excel environment
      1. 1. What’s new in Microsoft Excel 2013
        1. New and improved for 2013
          1. New templates
          2. Quick analysis
          3. Flash Fill
          4. Recommended charts
          5. New chart-formatting controls
          6. Timeline
          7. Other improvements
        2. Retired in 2013
        3. If you missed the last upgrade
        4. If you missed the last two upgrades
        5. Moving onward with Office 2013
      2. 2. Exploring Excel fundamentals
        1. Examining the Excel 2013 workspace
          1. Facts about worksheets
          2. Using the workbook window
            1. Getting around in the workbook
            2. Workbook navigation tips
            3. Working with workbook windows
          3. Exploring the ribbon
            1. The ribbon speaks
            2. Drop-down lists and dialog box launchers
            3. Galleries and live preview
            4. Understanding contextual tool sets
          4. Using the Quick Access Toolbar
          5. Accessing commands with the keyboard
          6. The joy of shortcut menus
          7. Using the Quick Analysis tool
          8. Meet the formula bar
          9. Facts about the status bar
          10. Introducing Backstage view
        2. Exploring file-management fundamentals
          1. Creating workbooks
            1. Installing your own templates
          2. Saving files
            1. Rules for file naming
            2. File formats
            3. Ensuring file compatibility with previous versions of Excel
            4. Understanding the “XL” formats
            5. Specifying the default file format
            6. Creating automatic backup files
            7. Protecting files
            8. Adding summary information to files
          3. Opening files
            1. Special ways to open files
            2. Opening files when you start Excel
            3. Recovering corrupted files
        3. Importing and exporting files
          1. Importing and exporting files
          2. Sharing data with Excel for the Macintosh
          3. Sharing data beyond Excel
            1. Office Web Apps
            2. Using web file formats
            3. Importing and exporting text files
            4. Other file formats
        4. Using the Help system
          1. Help on the surface
          2. Help in depth
        5. Recovering from crashes
          1. Understanding AutoRecover
          2. Managing versions
      3. 3. Custom-tailoring the Excel workspace
        1. Customizing the ribbon
          1. Identifying items in the Customize Ribbon and Quick Access Toolbar categories
          2. Creating a custom ribbon tab
          3. Creating a custom command group
          4. Adding existing command groups to custom tabs
          5. Resetting and recycling your customizations
        2. Customizing the Quick Access Toolbar
          1. Positioning the toolbar
          2. Adding tools to the toolbar
            1. Adding tools as you work
            2. Removing tools
            3. Adding and organizing tools
            4. Too many tools?
          3. Creating your own buttons
          4. Saving and resetting your custom Quick Access Toolbar
          5. Restoring the toolbar
          6. Exploring other toolbar and ribbon options
        3. Controlling other elements of the Excel 2010 interface
          1. Displaying underlying formulas
          2. Hiding zeros
        4. Enhancing accessibility
        5. Installing apps
      4. 4. Security and privacy
        1. The Trust Center
          1. Trusted publishers, locations, and documents
          2. App catalogs, add-ins, ActiveX settings, and macro settings
          3. Message bar
          4. External content
          5. File block settings
          6. Privacy options
        2. File security
          1. Removing personal information from your workbooks
          2. Using digital signatures
        3. Other security features
    5. II. Building worksheets
      1. 5. Planning your worksheet design
        1. Which data should be in rows, and which in columns?
        2. Will you need to print the worksheet?
        3. Who is the audience?
        4. Would your worksheet survive without you?
        5. Does the worksheet rely on imported data?
        6. Do you need more than one worksheet?
        7. Have you allowed room for new data?
      2. 6. How to work a worksheet
        1. Moving around regions
          1. Navigating regions with the keyboard
          2. Navigating regions with the mouse
          3. Navigating with special keys
        2. Understanding selection
          1. Selecting with the mouse
            1. Zooming to select large worksheet areas
            2. Selecting columns, rows, and nonadjacent ranges
            3. Selecting regions
          2. Using the find and select commands
          3. Selecting with Go To Special
            1. Selecting precedents and dependents
            2. Selecting row or column differences
        3. Techniques for entering data
          1. Making entries in cells and in the formula bar
            1. Entering simple numeric and text values
            2. Using special characters
            3. Understanding the difference between displayed values and underlying values
            4. Creating long text values
            5. Using text wrapping
            6. Understanding numeric text entries
            7. Entering symbols
          2. Making entries in ranges
          3. Editing and undoing entries
        4. Managing worksheets
          1. Inserting and deleting worksheets
          2. Naming and renaming worksheets
          3. Moving and copying worksheets
        5. Viewing worksheets
          1. Splitting worksheets into panes
          2. Freezing panes
          3. Zooming worksheets
          4. Using custom views
        6. Protecting worksheets
          1. Unlocking individual cells
          2. Protecting the workbook
          3. Allowing password access to specific cell ranges
          4. Hiding cells and worksheets
          5. Using passwords
      3. 7. How to work a workbook
        1. Managing multiple workbooks
          1. Navigating between open workbooks
          2. Arranging workbook windows
          3. Comparing worksheets side by side
        2. Opening multiple windows for the same workbook
          1. Useful inconsistencies of new windows
        3. Hiding and protecting workbooks
          1. Hiding workbooks
          2. Protecting workbooks
          3. Encrypting workbooks
          4. Saving workbooks or windows as hidden
          5. Hiding worksheets
          6. Marking as final
    6. III. Formatting and editing worksheets
      1. 8. Worksheet editing techniques
        1. Copying, cutting, and pasting
          1. Copying and pasting
            1. Collecting multiple items on the Clipboard
            2. Pasting multiples
            3. Using the Paste Options button
          2. Cutting and pasting
            1. Pasting selectively using Paste Special
            2. Pasting using math operators
            3. Pasting links
            4. Skipping blank cells
            5. Transposing entries
          3. Pasting hyperlinks
          4. Moving and copying with the mouse
        2. Inserting and deleting
          1. Inserting columns and rows
          2. Inserting cells
            1. Inserting copied or cut cells
          3. Deleting cells, columns, and rows
          4. Clearing cells
          5. Inserting, deleting, and clearing cells with the mouse
          6. Dragging with the right mouse button
        3. Undoing previous actions
          1. Redoing what you’ve undone
          2. Repeating your last action
        4. Editing cell contents
          1. Editing in cells or in the formula bar
          2. Editing options
          3. Filling cells and creating data series
            1. Extending with Auto Fill
            2. Dragging the fill handle with the right mouse button
            3. Using the Series command
            4. Using the Fill menu commands
            5. Distributing long entries using the Justify command
            6. Creating custom lists
            7. Importing custom lists
            8. Automatic parsing and concatenation using Flash Fill
          4. Controlling automatic formatting
        5. Finding and replacing stuff
          1. Finding formatting
          2. Specifying variables using wildcard characters
          3. Replacing what you find
        6. Getting the words right
          1. Fixing errors as you type
            1. Typing Internet and network addresses
            2. Using custom AutoCorrect actions
          2. Letting Excel help with typing chores
          3. Cheking yer speling
          4. Research resources
        7. Editing multiple worksheets
          1. Grouping worksheets for editing
            1. What you can do in group-editing mode
          2. Filling a group
        8. Auditing and documenting worksheets
          1. Checking for errors
          2. Evaluating and auditing formulas
          3. Watching formulas
          4. Tracing cell references
            1. Tracing dependent cells
            2. Clearing tracer arrows
            3. Tracing precedent cells
            4. Tracing errors
            5. Tracing references to other worksheets
          5. Adding comments to cells
            1. Tweaking your comments
            2. Printing comments
        9. Outlining worksheets
          1. Outlining a worksheet with nonstandard layout
          2. Extending the outline to new worksheet areas
          3. Hiding an outline
          4. Collapsing and expanding outline levels
          5. Displaying a specific outline level
          6. Ungrouping and grouping columns and rows
        10. Consolidating worksheets
          1. Consolidating by position
          2. Consolidating by category
            1. Creating links to the source worksheets
      2. 9. Worksheet formatting techniques
        1. Formatting fundamentals
          1. Formatting tables
            1. Options for applying table formats
            2. Creating custom table formats
            3. Removing the automatic table features
          2. Painting formats
        2. Using themes and cell styles
          1. Formatting with themes
            1. Creating custom themes
          2. Formatting with cell styles
            1. Creating custom cell styles
              1. Creating cell styles by example
              2. Modifying and duplicating cell styles
              3. Merging cell styles from different workbooks
              4. Deleting a cell style
        3. Formatting conditionally
          1. Creating conditional formatting rules
          2. Managing conditional formatting rules
            1. Copying, clearing, and finding conditional formats
          3. Creating conditional formatting formulas
        4. Formatting in depth
          1. Formatting individual characters
          2. Formatting as you type
          3. Understanding the General format
          4. Formatting numbers
            1. Using Currency formats
            2. Using Accounting formats
            3. Formatting percentages
            4. Formatting fractions
            5. Formatting Scientific (exponential) values
            6. Understanding the Text format
            7. Using the Special formats
            8. Creating Custom number formats
              1. Creating new number formats
              2. Creating four-part formats
              3. Adding color to formats
              4. Using custom format conditional operators
          5. Aligning data in cells
            1. Aligning text horizontally
              1. Indenting cell contents
              2. Distributing cell contents
              3. Centering text across columns
              4. Filling cells with characters
              5. Wrapping text in cells
              6. Justifying text in cells
          6. Aligning text vertically
            1. Controlling text orientation
            2. Shrinking text to fit in cells
          7. Using fonts
          8. Customizing borders
          9. Applying colors and patterns
            1. Adding graphic backgrounds to worksheets
          10. Controlling the size of cells
            1. Changing column widths
            2. Changing row heights
            3. Merging and unmerging cells
        5. Using template files to store formatting
    7. IV. Adding graphics and printing
      1. 10. Creating and formatting graphics
        1. Using the Shapes tools
          1. Drawing constrained objects
          2. Drawing freehand lines and polygons
            1. Adjusting freehand shapes with the Edit Points command
            2. Working with curves
          3. Working with text boxes
            1. Adding text to other shapes
          4. Working with shapes
            1. Using connectors and callouts
        2. Creating WordArt
        3. Creating SmartArt
        4. Inserting other graphics
          1. Inserting online pictures and clip art
          2. Inserting pictures
          3. Inserting other objects
        5. Formatting graphics
          1. Using picture tools
            1. Making adjustments to your images
          2. Using drawing tools
          3. Using SmartArt tools
          4. Formatting text in graphics
          5. Applying compression to pictures
          6. Using advanced object formatting effects
            1. Formatting fills and lines
            2. Applying shadows
            3. Applying 3-D effects
            4. Formatting objects that contain text
          7. Formatting embedded objects
        6. Working with graphic objects
          1. Selecting and grouping objects
          2. Positioning objects
            1. Tools to help you position objects on the worksheet
          3. Protecting objects
        7. More tricks with graphic objects
          1. Assigning macros to objects
          2. Creating screenshots or pictures of your worksheets
            1. Taking screenshots
            2. Copying cells as a picture
            3. Pasting cells as a picture
            4. Creating linked images of cells using the Camera button
      2. 11. Printing and presenting
        1. Controlling the appearance of your pages
          1. Setting page options
            1. Printing wide or tall
            2. Specifying paper size and print quality
            3. Setting a reduction (scaling) ratio
            4. Setting the first page number
          2. Working in Page Layout view
          3. Setting margins
          4. Creating a header and footer
            1. Using the header/footer controls
            2. Adding pictures to headers and footers
          5. Setting worksheet options
            1. Specifying the area to be printed
            2. Specifying rows and columns to print on every page
            3. Printing gridlines and headings
            4. Printing comments and errors
            5. Printing drafts
            6. Translating screen colors to black and white
            7. Setting the printing order of large print ranges
        2. Controlling what and where to print
        3. Adjusting page breaks
          1. Using Page Break Preview
          2. Inserting and removing manual page breaks
        4. Using Print Preview
        5. Creating portable documents
    8. V. Creating formulas and performing data analysis
      1. 12. Building formulas
        1. Formula fundamentals
          1. Understanding the precedence of operators
          2. Using cell references in formulas
            1. Entering cell references by clicking
            2. Understanding relative, absolute, and mixed references
            3. Creating references to other worksheets in the same workbook
            4. Creating references to worksheets in other workbooks
            5. How copying affects cell references
              1. Copying relative references
              2. Copying absolute references
              3. Copying mixed references
          3. Editing formulas
            1. Understanding reference syntax
            2. Using numeric text in formulas
            3. About text values
          4. Understanding error values
        2. Using functions: A preview
          1. Using the Sum button
          2. Inserting a function
          3. Using Formula AutoComplete
        3. Working with formulas
          1. Naming cells and cell ranges
            1. Using names in formulas
            2. Defining and managing names
            3. Editing names
            4. Workbook-wide vs. worksheet-only names
            5. Creating names semiautomatically
            6. Naming constants and formulas
            7. Using relative references in named formulas
            8. Creating three-dimensional names
            9. Inserting names in formulas
            10. Creating a list of names
            11. Replacing references with names
            12. Using Go To with names
          2. Getting explicit about intersections
          3. Creating three-dimensional formulas
          4. Formula-bar formatting
          5. Using structured references
            1. Understanding structured reference syntax
            2. Using operators with column specifiers
            3. About the special item specifiers
            4. Using Formula AutoComplete with structured references
            5. Filling and copying structured references
        4. Worksheet calculation
          1. Recalculating manually
          2. Calculating part of a formula
          3. Working with circular references
          4. Understanding the precision of numeric values
        5. Using arrays
          1. One-dimensional arrays
          2. Array formula rules
          3. Two-dimensional arrays
          4. Single-cell array formulas
          5. Using array constants
          6. Understanding array expansion
        6. Linking workbooks
          1. Saving linked workbooks
          2. Opening a dependent workbook
          3. Editing links
          4. Copying, cutting, and pasting in linked workbooks
            1. Copying and pasting between workbooks
            2. Cutting and pasting between workbooks
        7. Creating conditional tests
          1. Using conditional functions
          2. Using lookup functions
      2. 13. Using functions
        1. Using the built-in function reference in Excel
        2. Exploring the syntax of functions
          1. Expressions as arguments
          2. Types of arguments
            1. Numeric values
            2. Text values
            3. Logical values
            4. Named references
            5. Arrays
            6. Mixed argument types
        3. Inserting functions
          1. Inserting references and names
      3. 14. Everyday functions
        1. Understanding mathematical functions
          1. Using the SUM function
            1. The Sum button
            2. The SUMIF, SUMIFS, and COUNTIF functions
          2. Using selected mathematical functions
            1. The AGGREGATE function
            2. The PRODUCT and SUMPRODUCT functions
            3. The MOD function
            4. The COMBIN function
            5. The RAND and RANDBETWEEN functions
          3. Using the rounding functions
            1. The ROUND, ROUNDDOWN, and ROUNDUP functions
            2. The EVEN and ODD functions
            3. The FLOOR and CEILING functions
            4. The INT function
            5. The TRUNC function
        2. Understanding text functions
          1. Using selected text functions
            1. The TEXT function
            2. The DOLLAR function
            3. The LEN function
            4. The ASCII functions: CHAR and CODE
            5. The cleanup functions: TRIM and CLEAN
            6. The EXACT function
            7. The case functions: UPPER, LOWER, and PROPER
          2. Using the substring text functions
            1. The FIND and SEARCH functions
            2. The RIGHT and LEFT functions
            3. The MID function
            4. The REPLACE and SUBSTITUTE functions
            5. The CONCATENATE function
        3. Understanding logical functions
          1. Using selected logical functions
            1. The IF function
            2. The AND, OR, and NOT functions
            3. Nested IF functions
            4. Other uses for conditional functions
        4. Understanding information functions
          1. Using selected information functions
            1. The TYPE and ERROR.TYPE functions
            2. The COUNTBLANK function
          2. Using the IS information functions
        5. Understanding lookup and reference functions
          1. Using selected lookup and reference functions
            1. The VLOOKUP and HLOOKUP functions
            2. The LOOKUP function
            3. The ADDRESS function
            4. The CHOOSE function
            5. The MATCH function
            6. The INDEX function
            7. The INDIRECT function
            8. The ROW and COLUMN functions
            9. The ROWS and COLUMNS functions
            10. The AREAS function
            11. The TRANSPOSE function
      4. 15. Formatting and calculating date and time
        1. Understanding how Excel records dates and times
        2. Entering dates and times
          1. Entering a series of dates
            1. Extending an existing date series
        3. Formatting dates and times
          1. Creating your own date and time formats
            1. Measuring elapsed time
        4. Calculating with date and time
          1. Working with date and time functions
            1. Using the TODAY and NOW functions
            2. Using the WEEKDAY function
            3. Using the YEAR, MONTH, and DAY functions
            4. Using the HOUR, MINUTE, and SECOND functions
            5. Using the DATEVALUE and TIMEVALUE functions
          2. Working with specialized date functions
            1. Using the EDATE and EOMONTH functions
            2. Using the YEARFRAC function
            3. Using the WORKDAY, NETWORKDAYS, WORKDAY.INTL, and NETWORKDAYS.INTL functions
      5. 16. Functions for financial analysis
        1. Calculating investments
          1. The PV function
          2. The NPV function
          3. The FV function
          4. The PMT function
          5. The IPMT function
          6. The PPMT function
          7. The NPER function
          8. The RATE function
          9. The IRR function
          10. The MIRR function
        2. Calculating depreciation
          1. The SLN function
          2. The DDB and DB functions
          3. The VDB function
          4. The SYD function
        3. Analyzing securities
          1. The DOLLARDE and DOLLARFR functions
          2. The ACCRINT and ACCRINTM functions
          3. The INTRATE and RECEIVED functions
          4. The PRICE, PRICEDISC, and PRICEMAT functions
          5. The DISC function
          6. The YIELD, YIELDDISC, and YIELDMAT functions
          7. The TBILLEQ, TBILLPRICE, and TBILLYIELD functions
          8. The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD functions
          9. The DURATION and MDURATION functions
        4. Using the Euro Currency Tools add-in
      6. 17. Functions for analyzing statistics
        1. Analyzing distributions of data
          1. Using built-in statistical functions
            1. The AVERAGE functions
            2. The MEDIAN, MODE.SNGL, MODE.MULT, MAX, MIN, and COUNT functions
          2. Using functions that analyze rank and percentile
            1. The PERCENTRANK functions
            2. The PERCENTILE and QUARTILE functions
            3. The SMALL and LARGE functions
            4. The RANK functions
          3. Using sample and population statistical functions
            1. Calculating sample statistics: VAR.S and STDEV.S
            2. Calculating total population statistics: VAR.P and STDEV.P
        2. Understanding linear and exponential regression
          1. Calculating linear regression
            1. The LINEST function
            2. The TREND function
            3. The FORECAST function
            4. The SLOPE function
            5. The STEYX function
          2. Calculating exponential regression
            1. The LOGEST function
            2. The GROWTH function
        3. Using the Analysis Toolpak data analysis tools
          1. Installing the Analysis Toolpak
          2. Using the Descriptive Statistics tool
          3. Creating histograms
            1. Analyzing distribution with the FREQUENCY function
          4. Using the Rank And Percentile tool
          5. Generating random numbers
            1. Distributing random numbers uniformly
            2. Distributing random numbers normally
            3. Generating random numbers using Bernoulli distribution
            4. Generating random numbers using binomial distribution
            5. Generating random numbers using Poisson distribution
            6. Generating random numbers using discrete distribution
            7. Generating semi-random numbers using patterned distribution
          6. Sampling a population of numbers
          7. Calculating moving averages
      7. 18. Performing a what-if analysis
        1. Using data tables
          1. Data tables based on one input variable
          2. Single-variable tables with more than one formula
          3. Data tables based on two input variables
          4. Editing tables
        2. Using the Scenario Manager
          1. Defining scenarios
          2. Browsing your scenarios
          3. Adding, editing, and deleting scenarios
            1. Tracking changes
          4. Routing and merging scenarios
          5. Creating scenario reports
            1. The Scenario Summary report
            2. The Scenario PivotTable report
        3. Using the Goal Seek command
          1. Precision and multiple solutions
        4. Using the Solver
          1. Stating the objective
          2. Specifying variable cells
          3. Specifying constraints
            1. Specifying integer constraints
          4. Other Solver options
            1. Linear models
            2. Viewing iteration results
          5. Saving and reusing the Solver parameters
          6. Assigning the Solver results to named scenarios
          7. Generating reports
            1. The Sensitivity report
            2. The Answer report
            3. The Limits report
    9. VI. Creating charts
      1. 19. Designing charts
        1. Selecting data for your chart
        2. Creating a new chart
        3. Changing the chart type
          1. Using a combo chart type
        4. Assigning a series to a secondary axis
        5. Switching rows and columns
        6. Choosing a chart layout
        7. Choosing a chart style
        8. Moving the chart to a separate chart sheet
        9. Adding, editing, and removing a chart title
        10. Adding, editing, and removing a legend
        11. Adding and positioning data labels
        12. Adding a data table
        13. Adding and removing gridlines
          1. Adding and editing axis titles
        14. Working with axes
          1. Changing the scale of a value axis
            1. Changing the positions of tick marks and gridlines
            2. Changing the point where axes intersect
            3. Reversing the value-axis scale
            4. Using logarithmic scaling
            5. Applying a scaling factor
            6. Changing the display format of a value axis
          2. Changing the scale of a category axis
            1. Changing the beginning and end points on a date axis
            2. Changing the base unit
        15. Modifying a chart’s data
          1. Using the mouse to add data points
          2. Filtering chart data
          3. Plotting noncontiguous source ranges
            1. Changing the way Excel plots empty and hidden cells
        16. Adding moving averages and other trendlines
        17. Adding error bars
      2. 20. Formatting charts
        1. Formatting charts with the Chart Styles gallery
        2. Selecting chart elements
        3. Repositioning chart elements with the mouse
        4. Formatting lines and borders
        5. Formatting areas
          1. Choosing a solid fill
          2. Filling an area with a color gradient
          3. Filling an area with a texture or picture
          4. Making areas transparent
        6. Formatting text
          1. Using WordArt
          2. Changing the rotation of axis labels
          3. Changing the interval between axis labels
          4. Changing the position of axis labels
          5. Changing the numeric format used by axis labels
        7. Adding arrows, shapes, and text
      3. 21. Using sparklines
        1. Creating sparklines
          1. Creating groups of sparklines
          2. Expanding a set of sparklines
        2. Customizing sparklines
          1. Changing the sparkline color and weight
          2. Emphasizing particular points
          3. Customizing axes
            1. Setting minimum and maximum values for the vertical axis
            2. Plotting a group of sparklines against a common vertical axis
            3. Using a time-scaled horizontal axis
          4. Adding text to sparklines
        3. Removing sparklines
    10. VII. Managing databases and tables
      1. 22. Managing information in tables
        1. How to organize a table
        2. Creating a table
          1. Overwriting default headers
          2. Turning a table back into an ordinary range
          3. Naming a table
          4. Expanding a table
        3. Adding totals to a table
        4. Sorting tables and other ranges
          1. Sorting on a single column
          2. Sorting on more than one column
          3. Sorting only part of a list
          4. Sorting by column
          5. Sorting cells that contain formulas
          6. Sorting months, weekdays, or custom lists
          7. Performing a case-sensitive sort
        5. Filtering a list or table
          1. Using filters
            1. Determining how many rows pass the filter
            2. Removing a filter
            3. Using filter criteria in more than one column
            4. Using a filter to find the top or bottom n items
            5. Using a filter to display blank entries
            6. Using filters to select dates
            7. Using filters to specify more complex criteria
            8. Using custom filters to specify complex relationships
              1. Finding an alphabetical range of text values
              2. Using wildcards in custom criteria
          2. Using the Advanced Filter command
            1. Specifying a criteria range
            2. An example using two columns joined by OR
            3. An example using three ORs on a column
            4. An example using both OR and AND
            5. Applying multiple criteria to the same column
            6. Using computed criteria
              1. Referencing cells within the list
              2. Referencing a cell outside the list
              3. Referencing all rows in a column
            7. Extracting filtered rows
            8. The Unique Records Only check box
          3. Removing duplicate records
        6. Using formulas with tables
          1. Referencing the total row
          2. Explicitly referencing the current row
          3. Referencing parts of a table
        7. Formatting tables
          1. Using themes to change style appearance
          2. Customizing table styles
      2. 23. Analyzing data with PivotTable reports
        1. Introducing PivotTables
        2. Creating a PivotTable
        3. Rearranging PivotTable fields
        4. Refreshing a PivotTable
        5. Changing the numeric format of PivotTable data
        6. Choosing report layout options
        7. Formatting a PivotTable
          1. Customizing the display of empty or error cells
          2. Merging and centering field labels
          3. Hiding outline controls
          4. Hiding the Row Labels and Column Labels headings
        8. Displaying totals and subtotals
          1. Customizing subtotals
        9. Sorting PivotTable fields
        10. Filtering PivotTable fields
          1. Filtering with the Report Filter axis
          2. Filtering with slicers
            1. Connecting a slicer to multiple PivotTables
          3. Formatting slicers
          4. Filtering in the PivotTable Fields pane
        11. Changing PivotTable calculations
          1. Using a different summary function
          2. Applying multiple summary functions to the same field
          3. Using custom calculations
          4. Using calculated fields and items
            1. Creating a calculated field
            2. Creating a calculated item
            3. Displaying a list of calculated fields and items
        12. Grouping and ungrouping data
          1. Creating ad hoc item groupings
          2. Grouping items in date or time ranges
        13. Displaying the details behind a data value
        14. Creating PivotCharts
        15. Moving beyond PivotTables
      3. 24. An introduction to PowerPivot
        1. Using a PivotTable on an Excel table
        2. Using PowerPivot in Office 2013
          1. Adding information to the Excel table
          2. Creating a data model with many tables
          3. Understanding relationships
        3. Understanding the data model
          1. Querying the data model
        4. The PowerPivot add-in
        5. Creating a Power View report
        6. Loading data from external sources
        7. Using the DAX language
          1. Creating a calculated column
          2. Creating a calculated field
        8. Refreshing the PowerPivot data model
      4. 25. Working with external data
        1. Using and reusing Office Data Connections
          1. Setting refresh options
          2. Requiring or not requiring a password to refresh
          3. Refreshing on demand
        2. Opening an entire Access table in Excel
        3. Working with data in text files
          1. Using the Text Import Wizard
          2. Parsing Clipboard text
        4. Working with XML files
          1. Creating an ad hoc mapping of XML elements to table columns
          2. Importing XML data using an existing XML structure
        5. Using Microsoft Query to import data
          1. Choosing tables and fields (columns)
          2. Filtering records
          3. Sorting records
            1. Saving the query or moving to Microsoft Query
          4. Working directly with Microsoft Query
            1. Getting to Query
            2. Adding and removing tables
            3. Working with joins
            4. Adding, removing, and moving fields
            5. Renaming fields
            6. Sorting the result set
            7. Filtering the result set
              1. Creating exact-match criteria
              2. Using multiple exact-match criteria
              3. Using menu commands to specify exact-match criteria
              4. Filtering on fields that are not in the result set
              5. Limiting the result set to unique entries
              6. Comparing fields
            8. Performing aggregate calculations
              1. Cycling through the totals
              2. Cycling through the functions
              3. Using menu commands
              4. Aggregating groups of records
              5. Using more than one aggregate field
              6. Filtering on calculated fields
            9. Creating a parameter-based query
            10. Saving a query
            11. Returning the result set to Excel
        6. Using a web query to return Internet data
          1. Using an existing web query
          2. Creating your own web query
            1. Using the From Web command
            2. Copying and pasting from the web browser
            3. Exporting from Internet Explorer to Excel
    11. VIII. Using Excel collaboratively
      1. 26. Collaborating on a network or by email
        1. Saving and retrieving files over a network
        2. Sharing workbooks on a network
          1. Using advanced sharing options
          2. Tracking changes
          3. Protecting the change history
          4. Reviewing changes
          5. Canceling the shared workbook session
        3. Combining changes made to multiple workbooks
        4. Distributing workbooks and worksheets by email
          1. Sending an entire workbook as an email attachment
          2. Sending a worksheet, chart, or range by email
          3. Sending a workbook for review
      2. 27. Working in the cloud
        1. Using SkyDrive
          1. Working with the SkyDrive application
            1. Using the SkyDrive notification icon
            2. Specifying which folders to synchronize
          2. Saving workbooks to and retrieving them from SkyDrive
          3. Working with your cloud-based SkyDrive
            1. Uploading, downloading, and managing files and folders
            2. Opening documents
            3. Sharing folders and documents
            4. Opening recent documents in SkyDrive
            5. Seeing and accessing items others have shared with you
            6. Fetching files from remote computers
        2. Using Microsoft Office Web Apps
          1. How the Web App handles unsupported features
    12. IX. Automating Excel
      1. 28. Recording macros
        1. Configuring macro security
        2. Using the macro recorder
          1. Recording with relative references
          2. What to do when the macro recorder does not give you what you expect
        3. Introducing the Visual Basic Editor
        4. Learning the basics of Visual Basic
          1. Objects, methods, and properties
          2. The Object Browser
          3. Collections of objects
          4. Manipulating an object’s properties without selecting the object
          5. Naming arguments to methods
        5. Adding code to or editing recorded macros
        6. Using subroutines in macros
        7. Using the Personal Macro Workbook
        8. Going on from here
      2. 29. Creating custom functions
        1. Creating a simple custom function
        2. Using custom functions
        3. Understanding custom function rules
        4. Using VBA keywords in custom functions
        5. Documenting macros and custom functions
        6. Creating custom functions with optional arguments
        7. Making your custom functions available anywhere
      3. 30. Debugging macros and custom functions
        1. Using design-time tools
          1. Catching syntax errors
          2. Catching misspelled variable names
          3. Stepping through code
          4. Setting breakpoints with the Toggle Breakpoint command
          5. Setting conditional breakpoints using Debug.Assert
          6. Using the Watch Window to monitor variable values and object properties
            1. Setting conditional breakpoints with the Watch Window
            2. Using Quick Watch to monitor a variable or add a watch item
          7. Using the Immediate window
        2. Dealing with run-time errors
    13. X. Integrating and extending Excel
      1. 31. Linking, hyperlinking, and embedding
        1. Linking and embedding with OLE technology
          1. Embedding vs. linking
          2. Embedding vs. static pasting
          3. Embedding and linking from the Clipboard
          4. Embedding and linking with the Object command
          5. Managing links
            1. Choosing automatic or manual update
            2. Fixing broken links
        2. Linking with hyperlinks
          1. Creating a hyperlink in a cell
            1. Turning ordinary text into a hyperlink
          2. Linking to a website or local file
            1. Linking to a location in the current document
            2. Linking to a new file
            3. Linking to an email message
          3. Assigning a hyperlink to a shape, image, or chart
          4. Selecting a cell or an object without triggering the hyperlink
            1. Editing or deleting a hyperlink
          5. Using formulas to create hyperlinks
      2. 32. Using Excel data in Word documents
        1. Using Excel tables in Word documents
          1. Pasting an Excel table from the Clipboard
          2. Using Paste Special to control the format of your table
            1. Using the Microsoft Excel Worksheet Object format
            2. Using RTF and HTML formats
            3. Using Unformatted Text and Unformatted Unicode Text
            4. Using Bitmap and Picture (Enhanced Metafile)
            5. Paste-linking an Excel table into Word
            6. Linking with hyperlinks
          3. Using the Object command
        2. Using Excel charts in Word documents
        3. Using Excel to supply mail-merge data to Word
    14. XI. Appendixes
      1. A. Menu-to-ribbon command reference
        1. Excel 2003 File menu
        2. Excel 2003 Edit menu
        3. Excel 2003 View menu
        4. Excel 2003 Insert menu
        5. Excel 2003 Format menu
        6. Excel 2003 Tools menu
        7. Excel 2003 Data menu
        8. Excel 2003 Chart menu
        9. Excel 2003 Window menu
        10. Excel 2003 Help menu
        11. Excel 2003 Standard toolbar
        12. Excel 2003 Formatting toolbar
      2. B. Keyboard shortcuts
        1. Shortcuts by key: Function keys
        2. Shortcuts by key: Control and navigation keys
        3. Shortcuts by key: Numeric keys
        4. Shortcuts by key: Symbol keys and keypad
        5. Shortcuts by key: Letter keys
        6. Shortcuts by task: Insert charts
        7. Shortcuts by task: Work in dialog box text boxes
        8. Shortcuts by task: Work in dialog boxes
        9. Shortcuts by task: Edit data
        10. Shortcuts by task: Work with formulas
        11. Shortcuts by task: Enter data
        12. Shortcuts by task: Select cells
        13. Shortcuts by task: Filter tables
        14. Shortcuts by task: Work with borders
        15. Shortcuts by task: Format data
        16. Shortcuts by task: Work with Help
        17. Shortcuts by task: Insert, delete, and copy cells
        18. Shortcuts by task: Work with macros
        19. Shortcuts by task: Work with the ribbon
        20. Shortcuts by task: Move and scroll in End mode
        21. Shortcuts by task: Move and scroll in worksheets
        22. Shortcuts by task: Move within a selected range
        23. Shortcuts by task: Print
        24. Shortcuts by task: Work in Backstage view
        25. Shortcuts by task: Select cells, rows, columns, and objects
        26. Shortcuts by task: Select cells with special characteristics
        27. Shortcuts by task: Work with outlines
        28. Shortcuts by task: Work with options menus
        29. Shortcuts by task: Work with task panes
        30. Shortcuts by task: Navigate Windows
        31. Shortcuts by task: Work with worksheets
      3. C. Function reference
        1. Alphabetical list of Excel 2013 functions
        2. Alphabetical list of Excel 2013 functions (continued)
    15. D. Index to troubleshooting topics
    16. E. About the authors
    17. Index
    18. About the Authors
    19. Copyright

    Product information

    • Title: Microsoft Excel 2013 Inside Out
    • Author(s):
    • Release date: April 2013
    • Publisher(s): Microsoft Press
    • ISBN: 9780735674264