You are previewing Microsoft Excel 2013 Plain & Simple.
O'Reilly logo
Microsoft Excel 2013 Plain & Simple

Book Description

Get the guide that makes learning Microsoft Excel plain and simple! This full color, no-nonsense book shows you the quickest ways to solve a problem or learn a skill, using easy-to-follow steps and concise, straightforward language. You'll analyze, manage, and share information in more ways than ever before.

Here’s WHAT You’ll Learn

  • Manage your data quickly and efficiently

  • Filter, sort, summarize, and crunch your numbers

  • Use formulas and functions to do the heavy lifting

  • Bring data to life with charts and graphics

  • Share data between Microsoft Office documents

  • Work as a team, online and in the cloud

  • Here’s HOW You’ll Learn It

  • Jump in wherever you need answers

  • Follow easy STEPS and SCREENSHOTS to see exactly what to do

  • Get handy TIPS for new techniques and shortcuts

  • Use TRY THIS! exercises to apply what you learn right away

  • Table of Contents

    1. Dedication
    2. Special Upgrade Offer
    3. 1. About this book
      1. No computerese!
      2. Useful tasks...
      3. ...And the easiest way to do them
      4. A quick overview
      5. A few assumptions
      6. Adapting task procedures for touchscreens
      7. A final word (or two)
    4. 2. What’s new and improved in Excel 2013
      1. Using Excel 2013 in Windows 8
        1. Launch Excel 2013 in Windows 8
      2. Analyzing data instantly by using the Quick Analysis tool
        1. Summarize data by using Quick Analysis
      3. Entering data quickly by using Flash Fill
        1. Separate data by using Flash Fill
      4. Creating the right chart by using chart recommendations
        1. Create a recommended chart
      5. Filtering Excel tables by using slicers
        1. Add a slicer
        2. Define a filter by using a slicer
      6. Creating a recommended PivotTable
        1. Create a recommended PivotTable
      7. Editing a workbook in SkyDrive and the Excel Web App
        1. Edit a file in the Excel Web App
      8. Formatting charts by using the new tools interface
        1. Change a chart’s style
    5. 3. Getting started with Excel 2013
      1. Surveying the Excel program window
        1. Working with the user interface
        2. Working with galleries
      2. Starting Excel
        1. Start Excel 2013 in Windows 8
        2. Pin Excel 2013 to the taskbar
      3. Adding Excel 2013 to the Start screen
        1. Add Excel 2013 to the Start screen
      4. Starting Excel 2013 in Windows 7
        1. Start Excel 2013 in Windows 7
      5. Opening existing workbooks
        1. Open a workbook
        2. Open a recently used workbook
      6. Using file properties
        1. Set file properties
        2. Define custom properties
      7. Creating a new workbook
        1. Create a new workbook
      8. Working with multiple workbooks
        1. Switch between open workbooks
        2. Show more than one workbook
      9. Sizing and viewing windows
        1. Resize a window
      10. Zooming in or out on a worksheet
        1. Zoom in or out
        2. Zoom in or out to a custom zoom level
      11. Saving Excel workbooks
        1. Save a workbook
        2. Save a workbook with a new name
      12. Changing the default file folder
        1. Change the default file folder
      13. Closing workbooks and exiting Excel
        1. Close a workbook
        2. Exit Excel
      14. Using the Excel Help system
        1. Get Microsoft Excel Help
        2. Get suggested commands from shortcut menus
      15. Finding Excel Help on the web
        1. Get help on the web
      16. Searching for a workbook
        1. Search for a workbook
    6. 4. Building a workbook
      1. Selecting cells
        1. Select a contiguous group of cells
        2. Select a noncontiguous group of cells
        3. Select rows or columns
        4. Select noncontiguous rows or columns
      2. Entering text in cells
        1. Enter text as one line
        2. Enter text with forced line breaks
      3. Entering numbers in cells
        1. Enter numbers
        2. Enter numbers using scientific notation and exponents
      4. Entering dates and times in cells
        1. Enter a date
        2. Enter a time
        3. Enter a date and time
        4. Enter the current date and time
      5. Entering data using fills
        1. Fill data using AutoFill
        2. Use AutoFill to enter a series of values
      6. Entering data by using Flash Fill
        1. Combine data by using Flash Fill
        2. Separate data by using Flash Fill
      7. Entering data with other shortcuts
        1. Enter data with AutoComplete
        2. Pick data from a list
      8. Creating an Excel table
        1. Create an Excel table
        2. Add data to an Excel table
      9. Editing an Excel Table
        1. Rename an Excel table
        2. Resize an Excel table
        3. Select an Excel table column
      10. Editing cell contents
        1. Edit cell contents in the formula bar
        2. Edit cell contents directly in the cell
      11. Inserting a symbol in a cell
        1. Add a symbol to a cell
      12. Creating hyperlinks
        1. Add a hyperlink to files
        2. Add a hyperlink to another file
      13. Creating hyperlinks to web and email resources
        1. Add a hyperlink to a webpage
        2. Add a mailto hyperlink
      14. Cutting, copying, and pasting cell values
        1. Cut a cell value
        2. Copy a cell value
      15. Undoing or redoing an action
        1. Undo or redo an action
      16. Pasting values with more control
        1. Paste values with more control
      17. Clearing cell contents
        1. Clear a cell
      18. Using the Office Clipboard
        1. Display the contents of the Office Clipboard
        2. Paste an item from the Office Clipboard
        3. Clear an item from the Office Clipboard
      19. Finding and replacing text
        1. Find a word or value
        2. Replace a word or value
      20. Checking the spelling in your worksheet
        1. Check spelling
    7. 5. Managing and viewing worksheets
      1. Viewing and selecting worksheets
        1. Select multiple worksheets
      2. Renaming worksheets
        1. Change the name of a worksheet
      3. Moving worksheets
        1. Move worksheets within the workbook
        2. Move worksheets to another workbook
      4. Copying worksheets
        1. Copy worksheets within the workbook
        2. Copy worksheets to another workbook
      5. Inserting and deleting worksheets
        1. Insert a blank worksheet
        2. Delete one or more worksheets
      6. Hiding or showing a worksheet
        1. Hide a worksheet
        2. Unhide a hidden worksheet
      7. Changing worksheet tab colors
        1. Color a sheet tab
      8. Inserting, moving, and deleting cells
        1. Inserting cells in a worksheet
        2. Move cells in a worksheet
        3. Delete cells in a worksheet
      9. Inserting columns and rows
        1. Insert a row in a worksheet
        2. Insert a column in a worksheet
      10. Setting insert options
        1. Set insert options
      11. Deleting rows or columns
        1. Delete rows or columns
      12. Moving rows or columns
        1. Move one or more rows
        2. Move one or more columns
      13. Hiding and unhiding columns and rows
        1. Hide rows or columns
        2. Unhide rows or columns
      14. Entering data and formatting on many worksheets at the same time
        1. Enter and format data on several worksheets at one time
        2. Copy cells from one worksheet to a group of worksheets
      15. Changing how you look at Excel workbooks
        1. View different parts of one worksheet at the same time
        2. View multiple workbooks at the same time
        3. View multiple parts of a worksheet by freezing panes
      16. Naming and using worksheet views
        1. Name the current view of the worksheet
        2. Switch to another view of the worksheet
    8. 6. Using formulas and functions
      1. Creating simple cell formulas
        1. Build a formula
        2. Edit a formula
      2. Assigning names to groups of cells
        1. Create a named range
        2. Go to a named range
        3. Delete a named range
        4. Rename a named range
      3. Using names in formulas
        1. Create a formula with a named range
      4. Creating a formula that references values in an Excel table
        1. Create a formula with an Excel table reference
      5. Creating formulas that reference cells in other workbooks
        1. Use cells from other workbooks in a formula
        2. Break links to other workbooks and convert to values
        3. Refresh links
      6. Changing links to different workbooks
        1. Change links to different workbooks
      7. Analyzing data by using the Quick Analysis lens
        1. Summarize data by using Quick Analysis
      8. Summing a group of cells without using a formula
        1. Summarize data in a group of cells
        2. Find the total, average, or other values of cell data
      9. Creating a summary formula
        1. Create an AutoSum formula
      10. Summing with subtotals and grand totals
        1. Create a subtotal
        2. Remove a subtotal
      11. Exploring the Excel function library
        1. List functions available from the Excel library
        2. Use function ScreenTips
      12. Using the IF function
        1. Create an IF function
      13. Checking formula references
        1. Find cell precedents and dependents
        2. Remove tracer arrows
      14. Debugging your formulas
        1. Monitor a formula for changes
        2. Delete a watch
        3. Evaluate parts of a formula
    9. 7. Formatting the cell
      1. Formatting cell contents
        1. Change font and font size
        2. Change text appearance
      2. Formatting part of a cell’s contents
        1. Format part of a cell’s contents
      3. Formatting cells containing dates
        1. Set a date format
      4. Formatting cells containing numbers
        1. Display numerical values as currency and percentages
        2. Set the number of decimal places
      5. Adding cell backgrounds and shading
        1. Add background color
        2. Change background shading
      6. Formatting cell borders
        1. Draw borders
        2. Format cell borders
      7. Defining cell styles
        1. Apply a style
        2. Create a style
      8. Modifying and deleting cell styles
        1. Modify a style
        2. Delete a style
      9. Aligning and orienting cell contents
        1. Change text alignment
        2. Set text orientation and wrapping
      10. Formatting a cell based on conditions
        1. Change the format of a cell based on its value
        2. Change the format of a cell based on the results of a formula
      11. Editing and deleting conditional formats
        1. Edit a conditional formatting rule
        2. Delete a conditional formatting rule
      12. Changing how conditional formatting rules are applied
        1. Stop when a condition is met
        2. Change the order of conditions
      13. Displaying data bar and icon set formats
        1. Display data bars
        2. Display icon sets
      14. Displaying color scales based on cell values
        1. Display color scales
      15. Deleting conditional formats
        1. Delete conditional formats
      16. Merging or splitting cells or data
        1. Merge several cells into one
        2. Split a merged cell
      17. Copying formats with Format Painter
        1. Copy styles with Format Painter
    10. 8. Formatting the worksheet
      1. Applying workbook themes
        1. Apply a workbook theme
        2. Change colors within a theme
      2. Changing theme fonts and effects
        1. Change fonts within a theme
        2. Change effects within a theme
      3. Creating new workbook themes
        1. Create a new workbook theme
      4. Coloring sheet tabs
        1. Color a sheet tab
      5. Changing a worksheet’s gridlines
        1. Change the color of cell gridlines
        2. Show or hide cell gridlines
      6. Changing row heights and column widths
        1. Resize a row
        2. Resize a column
      7. Resizing multiple rows or columns
        1. Resize multiple rows or columns
      8. Inserting rows or columns
        1. Insert a row in a worksheet
        2. Insert a column in a worksheet
      9. Setting insert options
        1. Set insert options
      10. Moving rows and columns
        1. Move one or more rows
        2. Move one or more columns
      11. Deleting rows and columns
        1. Delete a row or column
      12. Grouping and ungrouping worksheet rows
        1. Group worksheet rows
        2. Ungroup worksheet rows
      13. Hiding rows and columns
        1. Hide rows or columns
        2. Unhide rows or columns
      14. Outlining to hide and show rows and columns
        1. Hide grouped rows and columns
        2. Show grouped rows and columns
      15. Protecting worksheets from changes
        1. Protect a worksheet
      16. Locking cells to prevent changes
        1. Lock cells
    11. 9. Printing worksheets
      1. Previewing worksheets before printing
        1. Display a worksheet in Page Layout view
        2. View and zoom worksheets in Backstage view
        3. Change column widths and row heights in Page Layout view
      2. Printing worksheets with current options
        1. Print multiple worksheets from the same workbook
      3. Choosing whether to print gridlines and headings
        1. Choose to print gridlines
        2. Choose to print headings
      4. Choosing printers and paper options
        1. Choose a printer
        2. Choose the paper
      5. Printing part of a worksheet
        1. Set a print area
        2. Remove a print area
      6. Printing row and column headings on each page
        1. Identify the rows and columns to repeat
      7. Setting and changing print margins
        1. Set page margins
        2. Adjust page margins in Backstage view
      8. Setting page orientation and scale
        1. Set page orientation
        2. Scale the printout to a fixed number of pages
      9. Creating headers and footers
        1. Add a premade header and footer
        2. Add predefined text to the header or footer
        3. Adjust header and footer height
      10. Adding graphics to a header or a footer
        1. Include a graphic in a header or footer
        2. Format a graphic in a header or footer
      11. Setting and viewing page breaks
        1. View current page breaks
        2. Set manual page breaks
        3. Change manual page breaks
    12. 10. Customizing Excel to the way you work
      1. Opening ready-to-use workbook templates
        1. Create a workbook from a template
      2. Saving a workbook as a template
        1. Save a workbook as a template
        2. Modify a template
      3. Adding commands to the Quick Access toolbar
        1. Add a command to the Quick Access toolbar
        2. Remove a command from the Quick Access toolbar
      4. Moving the Quick Access toolbar
        1. Move the Quick Access toolbar
      5. Removing a ribbon element
        1. Remove a ribbon element
      6. Adding and reordering ribbon elements
        1. Add a command to a ribbon tab
        2. Reorder commands on a ribbon tab
      7. Creating new ribbon tabs and groups
        1. Create a custom ribbon tab
        2. Add a new group to a ribbon tab
      8. Renaming a ribbon element
        1. Rename a ribbon element
      9. Choosing the color Excel uses to display errors
        1. Select the color that Excel uses to display errors
      10. Hiding and displaying ribbon tabs
        1. Hide a ribbon tab
        2. Redisplay a hidden ribbon element
      11. Controlling which error messages appear
        1. Choose which error messages appear
        2. Reset ignored errors
      12. Defining AutoCorrect entries
        1. Create an AutoCorrect entry
        2. Delete an AutoCorrect entry
      13. Controlling AutoFormat rules
        1. Control AutoFormat rules
    13. 11. Sorting and filtering worksheet data
      1. Sorting worksheet data
        1. Sort data in ascending or descending order
        2. Create a multicolumn sort
      2. Creating a custom sort list
        1. Define a custom list of values
        2. Sort using a custom list
      3. Filtering data quickly with AutoFilter
        1. Create a selection filter
        2. Create a filtering rule
      4. Filtering data with a search filter
        1. Create a search filter
      5. Clearing a filter
        1. Clear a filter from a column
        2. Clear all active filters
      6. Creating an advanced filter
        1. Build an advanced filter
        2. Remove an advanced filter
      7. Filtering Excel tables visually by using slicers
        1. Add a slicer
        2. Define a filter using a slicer
      8. Clearing and removing slicers
        1. Clear a slicer filter
        2. Remove a slicer
      9. Validating data for correctness during entry
        1. Create a validation rule
      10. Validating data using a list
        1. Validate data according to a list in a worksheet range
      11. Creating a recommended PivotTable
        1. Create a recommended PivotTable
        2. Pivot a PivotTable
    14. 12. Summarizing data visually using charts
      1. Creating a chart
        1. Create a chart
        2. Create a recommended chart
      2. Changing a chart’s layout and style
        1. Change a chart’s layout
        2. Change a chart’s style
      3. Changing a chart’s appearance
        1. Change a chart’s type
        2. Change the formatting of a chart element
      4. Formatting chart legends and titles
        1. Show or hide a chart legend
        2. Add titles
      5. Adding and removing data labels and grid lines
        1. Add and remove data labels
        2. Show or hide chart grid lines
      6. Formatting chart axes
        1. Change the scale on the value axis
        2. Change the scale on the category (X) axis
      7. Changing a chart’s data source
        1. Change the source data for your chart
      8. Adding and deleting data series
        1. Add a new series
        2. Remove a series
      9. Filtering charts
        1. Filter a chart
        2. Remove a chart filter
      10. Manipulating pie charts
        1. Pull a slice out of a pie chart
        2. Create a 3-D pie chart
      11. Creating a stock chart
        1. Create a stock chart
      12. Adding a trendline to a chart
        1. Add a trendline to a data series
      13. Summarizing data using sparklines
        1. Create a line or column sparkline
        2. Create a win/loss sparkline
      14. Formatting and deleting sparklines
        1. Format a sparkline
        2. Delete a sparkline
    15. 13. Enhancing your worksheets with graphics
      1. Adding drawing objects to a worksheet
        1. Add a simple shape
      2. Adding graphics to worksheets
        1. Add a picture
        2. Delete a picture
      3. Adding text to a shape
        1. Add text to any shape
        2. Format text in a shape
      4. Applying shape styles
        1. Apply a shape style
      5. Changing a shape’s fill color or image
        1. Apply a fill
        2. Fill an object with a picture
      6. Adding effects to drawing objects
        1. Add or edit an object’s shadow
        2. Rotate an object in three dimensions
      7. Resizing and rotating pictures and objects
        1. Resize a picture or object
        2. Rotate a picture or object
      8. Removing the background from an image
        1. Remove the background from a picture
      9. Aligning and grouping drawing objects
        1. Align objects
        2. Group or ungroup objects
      10. Using WordArt to create text effects in Excel
        1. Add WordArt text
        2. Change WordArt text colors
      11. Inserting clip art into a worksheet
        1. Add clip art
      12. Inserting and changing a diagram
        1. Insert a diagram
        2. Change the style of a diagram
      13. Creating an organization chart
        1. Create an organization chart
        2. Add a shape
      14. Changing the layout and design of a SmartArt graphic
        1. Alter the layout of your organization chart
        2. Change the design of your organization chart
      15. Adding an equation to a shape
        1. Add a model equation
        2. Add a custom equation
      16. Reordering objects
        1. Change the order of objects
    16. 14. Sharing Excel data with other programs
      1. Linking and embedding other files
        1. Embed a file in a worksheet
        2. Link to a file
      2. Exchanging table data between Excel and Word
        1. Paste Word data into Excel
        2. Copy Excel data to Word
      3. Copying Excel charts and data into PowerPoint
        1. Move Excel data to PowerPoint
        2. Copy an Excel chart to PowerPoint
      4. Exchanging data between Access and Excel
        1. Paste Access table data into an Excel worksheet
        2. Send Excel data to Access
      5. Importing a text file
        1. Paste text into Excel
    17. 15. Using Excel in a group environment
      1. Sharing workbooks in Excel
        1. Turn on workbook sharing
      2. Adding and viewing cell comments
        1. Add a comment
        2. View a comment
      3. Editing and deleting comments
        1. Edit a comment
        2. Delete a comment
      4. Tracking changes in workbooks
        1. Turn on Track Changes
      5. Accepting or rejecting changes
        1. View a change
        2. Review changes
      6. Maintaining a change history
        1. Create a change history
      7. Saving worksheets to the web
        1. Save a workbook to the web
      8. Dynamically updating worksheets published to the web
        1. Update worksheets published to the web
      9. Retrieving web data using Excel
        1. Retrieve data from web pages
      10. Copying web data to Excel
        1. Copy data from the web to Excel
      11. Modifying web queries
        1. Schedule web query data refreshes
      12. Saving data to the cloud using SkyDrive
        1. Save a file to SkyDrive
      13. Interacting over the web using XML
        1. Save a workbook as a Strict Open XML Spreadsheet file
        2. Import an XML spreadsheet file
      14. Editing a workbook in the Excel Web App
        1. Edit a file in the Excel Web App
        2. Open a file in the Excel desktop application
      15. Sharing Excel workbooks on the web
        1. Embed a workbook in a webpage
      16. Making workbooks available on the web
        1. Make a workbook available on the web
    18. A. About the Author
    19. Index
    20. About the Author
    21. Special Upgrade Offer
    22. Copyright