Excel® 2013 All-in-One For Dummies®
Visit www.dummies.com/cheatsheet/excel2013aio to view this book's cheat sheet.
Table of Contents
Book III: Formulas and Functions
Book IV: Worksheet Collaboration and Review
Chapter 1: The Excel 2013 User Experience
Excel 2013’s New Look and Feel
Going behind the scenes to Excel’s Backstage view
Adjusting to the Quick Access toolbar
Fooling around with the Formula bar
What’s up with the Worksheet area?
Taking a tour of the Status bar
Starting Excel from the Windows 8 Start screen
Starting Excel from the Windows 7 Start menu
Chapter 2: Customizing Excel 2013
Tailoring the Quick Access Toolbar to Your Tastes
Adding Ribbon commands to the Quick Access toolbar
Adding non-Ribbon commands to the Quick Access toolbar
Adding macros to the Quick Access toolbar
Changing some of the more universal settings on the General tab
Changing common calculation options on the Formulas tab
Changing correction options on the Proofing tab
Changing various save options on the Save tab
Changing a whole lot of other common options on the Advanced tab
Customizing the Excel 2013 Ribbon
Purchasing third-party add-ins
Chapter 1: Building Worksheets
Designing a workbook from scratch
It Takes All Kinds (Of Cell Entries)
Doing data entry with the Touch keyboard
You AutoCorrect this right now!
Constraining data entry to a cell range
Getting Excel to put in the decimal point
Saving workbooks in other commonly used file formats
Changing the default file location
Saving a new workbook in the old file format
Document Recovery to the Rescue
Chapter 2: Formatting Worksheets
Selecting cells with the mouse
Selecting cells with the keyboard
You AutoFit the column to its contents
Adjusting columns the old fashioned way
Formatting Tables from the Ribbon
Formatting Tables with the Quick Analysis Tool
Formatting Cells from the Ribbon
Formatting Cell Ranges with the Mini-Toolbar
Using the Format Cells Dialog Box
Basic borders, fills, and patterns
Using the Number Format cell styles
Defining a custom cell style by example
Creating a new cell style from scratch
Merging styles into other workbooks
Graphical conditional formatting
Formatting with the Quick Analysis tool
Identifying particular values or text entries in a cell range
Highlighting duplicate values in a cell range
Creating your own conditional formatting rules
Managing conditional formatting rules
Chapter 3: Editing and Proofing Worksheets
Using the Open screen in the Backstage view
Opening more than one workbook at a time
Can I just squeeze this in here?
Copying and Moving Stuff Around
Carried away with cut-and-paste
Find and Replace This Disgrace!
Adding words to the custom dictionary
Looking Up and Translating Stuff
Eliminating Errors with Text to Speech
Chapter 4: Managing Worksheets
Inserting and deleting columns and rows
Splitting the worksheet into panes
“Now you see them; now you don’t”
Opening windows on different sheets
Working with Multiple Workbooks
Comparing windows on different workbooks
Transferring data between open windows
Transferring sheets from one workbook to another
Chapter 5: Printing Worksheets
Printing from the Excel 2013 Backstage View
Checking the paging in Page Layout view
Previewing the pages of the report
Working with the Page Setup Options
Using the buttons in the Page Setup group
Using the buttons in the Scale to Fit group
Using the Print buttons in the Sheet Options group
Adding a ready-made header or footer
Creating a custom header or footer
Printing the Formulas in a Report
Book III: Formulas and Functions
Chapter 1: Building Basic Formulas
When you AutoSum numbers in a spreadsheet
Totals and sums with the Quick Analysis tool
Building formulas with operators
Using the Insert Function button
Using names in building formulas
Creating names from column and row headings
Applying names to existing formulas
Controlling Formula Recalculation
Chapter 2: Logical Functions and Error Trapping
Whiting-Out Errors with Conditional Formatting
Changing the Error Checking options
Removing Errors from the Printout
Chapter 3: Date and Time Formulas
Changing the Regional date settings
Building formulas that calculate elapsed dates
Building formulas that calculate elapsed times
Calculating the Net Present Value
Analysis ToolPak Financial Functions
Chapter 5: Math and Statistical Formulas
Using specialized statistical functions
Chapter 6: Lookup, Information, and Text Formulas
Looking up a single value with VLOOKUP and HLOOKUP
Getting specific information about a cell
Book IV: Worksheet Collaboration and Review
Chapter 1: Protecting Workbooks and Worksheet Data
Protecting the workbook when saving the file
Assigning a password to open from the Info screen
Entering the password to gain access
Entering the password to make changes
Changing or deleting a password
Changing the Locked and Hidden cell formatting
Enabling cell range editing by certain users
Doing data entry in the unlocked cells of a protected worksheet
Chapter 3: Sending Workbooks Out for Review
Preparing a Workbook for Distribution
Adding properties to a workbook
Modifying the Share Workbook options
Merging changes from different users
Marking up a worksheet with digital ink
Chapter 4: Sharing Workbooks and Worksheet Data
Sharing workbooks saved on your SkyDrive
Sharing workbooks with Instant Message
Excel 2013 Data Sharing Basics
Exporting Workbooks to Other Usable File Formats
Saving and exporting worksheets as PDF files
Saving worksheets as XPS files
Saving worksheets as HTML files
Chapter 1: Charting Worksheet Data
Embedded charts versus charts on separate chart sheets
Inserting specific chart types from the Ribbon
Inserting charts with the Quick Analysis tool
Creating a chart on a separate chart sheet
Refining the chart from the Design tab
Customizing chart elements from the Format tab
Customizing the elements of a chart
Formatting elements of a chart
Saving a customized chart as a template
Adding Sparkline Graphics to a Worksheet
Chapter 2: Adding Graphic Objects
Moving graphic objects to new layers
Managing graphic objects in the Selection task pane
Inserting Different Types of Graphics
Adding Screenshots of the Windows Desktop
Chapter 1: Building and Maintaining Data Lists
Add new records to a data list
Eliminating records with duplicate fields
Sorting records on a single field
Sorting records on multiple fields
Sorting the columns of a data list
Sorting a data list on font and fill colors and cell icons
Chapter 2: Filtering and Querying a Data List
Retrieving data from Access database tables
Retrieving data from text files
Querying data from other data sources
Retrieving external data with Microsoft Query
Chapter 1: Performing What-If Scenarios
Creating a one-variable data table
Creating a two-variable data table
Setting up and defining the problem
Saving and loading a model problem
Chapter 2: Generating Pivot Tables
Pivot tables with the Quick Analysis tool
Refining the pivot table layout and style
Formatting the parts of the pivot table
Sorting and Filtering the Pivot Table Data
Filtering individual Column and Row fields
Changing the summary functions
Changing the pivot table options
Moving a pivot chart to its own sheet
Using the PowerPivot and Power View Add-Ins
Switching between the Data View and Diagram View
Adding calculated columns courtesy of DAX
Creating visual reports with Power View
Chapter 1: Building and Running Macros
Assigning Macros to the Ribbon and the Quick Access Toolbar
Adding your macros to a custom tab on the Ribbon
Adding your macros to custom buttons on the Quick Access toolbar
Writing new macros in the Visual Basic Editor
Creating Custom Excel Functions
Adding a description to a user-defined function