You are previewing Excel® 2013 Absolute Beginner’s Guide.
O'Reilly logo
Excel® 2013 Absolute Beginner’s Guide

Book Description

Make the most of Excel 2013–without becoming a technical expert! This book is the fastest way to master Excel…use it to build powerful, trustworthy, easy-to-understand workbooks…discover hidden patterns and make better decisions…visualize data so everyone understands it…share and present workbooks on the web, tablets, anywhere! Even if you’ve never used Excel before, you’ll learn how to do what you want, one incredibly clear and easy step at a time. Excel has never, ever been this simple!

Who knew how simple Excel 2013 could be?

This is the easiest, most practical beginner’s guide to using Microsoft’s incredibly powerful new Excel 2013 spreadsheet program…simple, reliable instructions for doing everything you really want to do! Here’s a small sample of what you’ll learn:

  • Quickly create, edit, and format workbooks

  • Enter formulas and use functions effectively

  • Enter data more rapidly, easily, consistently, and accurately

  • Master powerful ways to sort, filter, consolidate, or group data

  • Visualize information through great charts, PivotTables, and Sparklines

  • Illustrate workbooks with images, shapes, and SmartArt

  • Explore and analyze data more easily with Excel 2013’s amazing new Power View

  • Share, print, present, and secure your workbooks

  • Store your workbooks online with SkyDrive

  • Work with macros

  • Build immense power into worksheet cells with UDFs

  • Table of Contents

    1. Title Page
    2. Copyright Page
    3. Contents
    4. About the Author
    5. Dedication
    6. Acknowledgments
    7. We Want to Hear from You!
    8. Reader Services
    9. Introduction
      1. Who Should Read This Book
      2. How This Book Is Organized
      3. Downloading the Example Files
      4. Conventions Used In This Book
      5. Special Elements
    10. 1. Understanding the Microsoft Excel Interface
      1. Taking a Closer Look at the Excel Window
      2. Customizing the Excel Window
      3. Moving Around and Making Selections on a Sheet
      4. Installing Optional Components
    11. 2. Working with Workbooks, Sheets, Rows, Columns, and Cells
      1. Managing Workbooks
      2. Using Templates to Quickly Create New Workbooks
      3. Working with Sheets and Tabs
      4. Working with Rows and Columns
      5. Working with Cells
    12. 3. Getting Data onto a Sheet
      1. Types of Data You Enter into Excel
      2. Entering Different Types of Data into a Cell
      3. Controlling the Next Cell Selection
      4. Using Copy, Cut, Paste, Paste Special to Enter Data
      5. Using Text to Columns to Separate Data in a Single Column
      6. Inserting Symbols and Equations into a Cell
      7. Using Web Queries to Get Data onto a Sheet
      8. Using Series to Quickly Fill a Range
      9. Editing Data
      10. Working with Tables
      11. Fixing Numbers Stored as Text
      12. Spellchecking Your Sheet
      13. Finding Data on Your Sheet
      14. Using Data Validation to Limit Data Entry in a Cell
    13. 4. Formatting Sheets and Cells
      1. Adjusting Row Heights and Column Widths
      2. Changing the Font Settings of a Cell
      3. Aligning Text in a Cell
      4. Reflowing Text in a Paragraph
      5. Applying Number Formats with Format Cells
      6. Creating a Custom Format
      7. When Cell Formatting Doesn’t Seem to Be Working Right
      8. The Number Group on the Ribbon
      9. Adding a Border Around a Range
      10. Coloring the Inside of a Cell
      11. Creating Hyperlinks
      12. Quick Formatting with the Format Painter
      13. Dynamic Cell Formatting with Conditional Formatting
      14. Using Cell Styles to Quickly Apply Formatting
      15. Using Themes to Ensure Uniformity in Design
    14. 5. Using Formulas
      1. The Importance of Laying Out Data Properly
      2. Adjusting Calculation Settings
      3. Viewing Formulas Versus Values
      4. Entering a Formula into a Cell
      5. Three Ways of Entering a Formula’s Cell References
      6. Relative Versus Absolute Formulas
      7. Using a Cell on Another Sheet in a Formula
      8. Using R1C1 Notation to Reference Cells
      9. Using F4 to Change the Cell Referencing
      10. Mathematical Operators
      11. Copying a Formula to Another Cell
      12. Using Names to Simplify References
      13. Inserting Formulas into Tables
      14. Using Table Names in Table Formulas
      15. Writing Table Formulas Outside the Table
      16. Using Array Formulas
      17. Converting Formulas to Values
      18. Troubleshooting Formulas
    15. 6. Using Functions
      1. Breaking Down a Function
      2. Finding Functions
      3. Entering Functions Using the Function Arguments Dialog Box
      4. Entering Functions Using In-Cell Tips
      5. Using the AutoSum Button
      6. Using Lookup Functions to Match a Value and Return Another
      7. SUMIFS
      9. Logical Functions
      10. Nested IF Statements
      11. IFERROR
      12. Date and Time Functions
      13. Troubleshooting Dates and Times Stored as Strings
      14. Goal Seek
      15. Using the Function Arguments Dialog Box to Troubleshoot Formulas
    16. 7. Sorting Data
      1. Preparing Data
      2. Opening the Sort Dialog Box
      3. Sorting by Values
      4. Sorting by Color or Icon
      5. Using the Quick Sort Buttons
      6. Quick Sorting Multiple Columns
      7. Randomly Sorting Data
      8. Sorting with a Custom Sequence
      9. Rearranging Columns Using the Sort Dialog Box
      10. Rearranging Columns Using the Mouse
      11. Fixing Sort Problems
    17. 8. Filtering and Consolidating Data
      1. Preparing Data
      2. Applying a Filter to a Data Set
      3. Clearing a Filter
      4. Reapplying a Filter
      5. Turning Filtering On for One Column
      6. Filtering for Listed Items
      7. Filtering the Grouped Dates Listing
      8. Using the Search Function to Filter for or Exclude Items
      9. Using the Search Function on Grouped Dates
      10. Using Text, Number, and Date Special Filters
      11. Filtering by Color or Icon
      12. Filtering by Selection
      13. Allowing Filtering on a Protected Sheet
      14. Using the Advanced Filter Option
      15. Removing Duplicates from a Data Set
      16. Consolidating Data
    18. 9. Distributing and Printing a Workbook
      1. Using Cell Comments to Add Notes to Cells
      2. Allowing Multiple Users to Edit a Workbook at the Same Time
      3. Hiding and Unhiding Sheets
      4. Locking Rows or Columns in Place
      5. Creating Custom Views of Your Data
      6. Configuring the Page Setup
      7. Creating a Custom Header or Footer
      8. Using Page Break Preview to Set Page Breaks
      9. Printing Sheets
      10. Protecting Your Workbook from Unwanted Changes
      11. Protecting the Data on a Sheet
      12. Preventing Changes by Marking a File as Final
      13. Restricting Access Using IRM
      14. Certifying a Workbook with a Digital Signature
      15. Sharing Files Between Excel Versions
      16. Removing Hidden or Confidential Information
      17. Recovering Lost Changes
      18. Sending an Excel File as an Attachment
      19. Sharing a File Online
    19. 10. Subtotals and Grouping
      1. Using the SUBTOTAL Function
      2. Summarizing Data Using the Subtotal Tool
      3. Copying the Subtotals to a New Location
      4. Formatting the Subtotals
      5. Applying Different Subtotal Function Types
      6. Combining Multiple Subtotal Results to One Row
      7. Subtotaling by Multiple Columns
      8. Sorting Subtotals
      9. Adding Space Between Subtotaled Groups
      10. Grouping and Outlining Rows and Columns
    20. 11. Creating Charts and Sparklines
      1. Preparing Data
      2. Elements of a Chart
      3. Types of Charts
      4. Adding a Chart to a Sheet
      5. Adding, Removing, and Formatting Chart Elements
      6. Editing and Formatting a Chart Title
      7. Changing the Display Units in an Axis
      8. Applying Chart Styles and Colors
      9. Applying Chart Layouts
      10. Moving or Resizing a Chart
      11. Switching Rows and Columns
      12. Changing an Existing Chart’s Type
      13. Creating a Chart with Multiple Chart Types
      14. Updating Chart Data
      15. Creating Stock Charts
      16. Creating Bubble Charts
      17. Pie Chart Issue: Small Slices
      18. Adding Sparklines to Data
      19. Creating a Chart Using a User-Created Template
    21. 12. PivotTables and Slicers
      1. Preparing Data for Use in a PivotTable
      2. PivotTable Limitations
      3. PivotTable Compatibility
      4. PivotTable Field List
      5. Creating a PivotTable
      6. Changing How a PivotTable Appears on a Sheet
      7. PivotTable Sorting
      8. Expanding and Collapsing Fields
      9. Viewing the Records Used to Calculate a Value
      10. Grouping Dates
      11. Filtering Data in a PivotTable
      12. Creating a Calculated Field
      13. Hiding Totals
      14. Formatting Values
      15. Slicers
    22. 13. Using Power View to Create Reports
      1. Power View Requirements
      2. Creating Reports
      3. Inserting and Formatting a Report Title
      4. Changing Data Visualizations
      5. Combining Multiple Visualizations
      6. Changing Colors
      7. Sorting
      8. Filtering
      9. Sharing Power View Reports
    23. 14. Inserting SmartArt, WordArt, and Pictures
      1. Working with SmartArt
      2. Inserting WordArt
      3. Inserting Pictures
    24. 15. An Introduction to Using Macros and UDFs
      1. Enabling VBA Security
      2. Developer Tab
      3. Introduction to the Visual Basic Editor
      4. Understanding How the Macro Recorder Works
      5. Recording a Macro
      6. Running a Macro
      7. User-Defined Functions
    25. 16. Introducing the Excel Web App
      1. Requirements
      2. Acquiring a Microsoft Account
      3. Uploading and Downloading Workbooks
      4. Opening a Workbook
      5. Creating a New Workbook Online
      6. Saving a File to Your Local Drive
      7. Sharing a Folder or Workbook
      8. Interacting with a Sheet Online
      9. Configuring Browser View Options
      10. Setting Up a Survey
    26. Index