Excel 2010: The Missing Manual by MacDonald, Matthew -- Read -- Imperial Library of Trantor

Index

Excel 2010: The Missing Manual

SPECIAL OFFER: Upgrade this ebook with O’Reilly A Note Regarding Supplemental Files The Missing Credits

About the Author About the Creative Team Acknowledgements The Missing Manual Series

Introduction

What You Can Do with Excel The Modern Face of Excel

The Ribbon Using the Ribbon with the Keyboard Backstage View The Quick Access Toolbar

Excel's New Features About This Book

About the Outline About→These→Arrows

Contextual tabs Buttons with menus Dialog box launchers Backstage view Ordinary menus

About Shortcut Keys Examples About MissingManuals.com Safari® Books Online

One. Worksheet Basics

1. Creating Your First Spreadsheet

Creating a Basic Worksheet

Starting a New Workbook Adding the Column Titles Adding Data Editing Data

Moving Around the Grid

Shortcut Keys The Go To Feature

A Tour of the Excel Window

The Tabs of the Ribbon The Formula Bar The Status Bar Going Backstage Excel Options

Saving Files

The Excel File Format Sharing Your Spreadsheet with Excel 2007 Saving Your Spreadsheet for Excel 2003 Saving a Spreadsheet in Other Formats Saving Your Spreadsheet As a PDF Saving Your Spreadsheet As an HTML File Saving Your Spreadsheet with a Password Disaster Recovery

AutoRecover settings

Opening Files

Opening Recent Documents Protected View Opening Files—with a Twist Working with Multiple Open Spreadsheets

2. Adding Information to Worksheets

Adding Different Types of Data

How Excel Identifies Text How Excel Identifies Numbers How Excel Identifies Dates and Times Regional Dating

Handy Timesavers

AutoComplete AutoCorrect AutoFill

Custom AutoFill lists

AutoFit

Dealing with Change: Undo, Redo, and AutoRecover

Undo and Redo AutoRecover

Spell Check

Spell Checking Options

Adding Hyperlinks

Creating a Link to a Web Page or Document Creating a Link to a Worksheet Location

3. Moving Data

Selecting Cells Making Continuous Range Selections Making Noncontiguous Selections Automatically Selecting Your Data Making Selections with the Keyboard Moving Cells Around A Simple Cut-and-Paste or Copy-and-Paste A Quicker Cut-and-Paste or Copy-and-Paste Fancy Pasting Tricks Paste Special Copying Multiple Items with the Clipboard Adding and Moving Columns or Rows Inserting Columns and Rows Inserting Rows Inserting Copied or Cut Cells Deleting Columns and Rows

4. Managing Worksheets

Worksheets and Workbooks

Adding and Removing Worksheets Hiding Worksheets Naming and Rearranging Worksheets Moving Worksheets from One Workbook to Another Grouping Worksheets

Managing grouped sheets Formatting grouped sheets Entering data in grouped sheets

Find and Replace

The Basic Find Find All More Advanced Searches Finding Formatted Cells Finding and Replacing Values

5. Formatting Cells

Formatting Cell Values

Changing the Cell Value Format Formatting Numbers

General Number Currency Accounting Percentage Fraction Scientific Text

Formatting Dates and Times Special Formats for Special Numbers Custom Formats

Creating a custom format Custom format string codes Date and time format strings Number format strings Text format strings

Formatting Cell Appearance

Alignment and Orientation Fonts and Color

Special characters

Borders and Fills Drawing Borders by Hand

6. Smart Formatting Tricks

The Format Painter Styles and Themes

Custom Styles Modifying Styles Transferring Styles Between Workbooks Themes: A Package of Styles

Fonts Colors Effects

Modifying Themes

Conditional Formatting

The Basics of Conditional Formatting Highlighting Specific Values Using Multiple Rules

7. Viewing and Printing Worksheets

Controlling Your View

Zooming Viewing Distant Parts of a Spreadsheet at Once Freezing Columns or Rows Hiding Data Saving View Settings Viewing Multiple Workbooks at Once

Printing

How to Print an Excel File Page Layout View: A Better Print Preview Creating Headers and Footers

Controlling Pagination

Page Breaks Scaling Page Break Preview: A Bird's-Eye View of Your Worksheet

Two. Formulas and Functions

8. Building Basic Formulas

Creating a Basic Formula

Excel's Order of Operations Cell References How Excel Formats Cells That Contain Cell References

Functions

Using a Function in a Formula Using Cell References with a Function Using Cell Ranges with a Function Excel Compatibility Functions

Formula Errors Logical Operators Formula Shortcuts

Point-and-Click Formula Creation Point-and-Click Formula Editing The Formulas Tab Using the Insert Function Button

Copying Formulas

Absolute Cell References Partially Fixed References Referring to Other Worksheets Referring to Other Workbooks

Updating links Troubleshooting links The Edit Links dialog box

9. Math and Statistical Functions

Rounding Numbers

ROUND(), ROUNDDOWN(), ROUNDUP(): Rounding Numbers MROUND(), CEILING(), and FLOOR(): More Rounding Functions INT() and TRUNC(): Chopping Off Non-Whole Numbers EVEN() and ODD(): Rounding Up to Even or Odd Values

Groups of Numbers

SUM(): Summing Up Numbers COUNT(), COUNTA(), and COUNTBLANK(): Counting Items in a List MAX() and MIN(): Finding Maximum and Minimum Values LARGE(), SMALL(), and RANK(): Ranking Numbers AVERAGE() and MEDIAN(): Finding Average or Median Values PERCENTILE() and PERCENTRANK(): Advanced Ranking Functions FREQUENCY(): Putting Numbers into Grouped Ranges MODE(): Finding Numbers That Frequently Occur in a List

General Math Functions

PRODUCT(), FACT(), POWER(), and SQRT(): Products, Factorials,Powers, and Square Roots QUOTIENT() and MOD(): Higher Division ABS() and SIGN(): Absolute Value and Determining a Number's Sign RAND() and RANDBETWEEN(): Generating Random Numbers GCD() and LCM(): Greatest and Least Common Denominator COMBIN() and PERMUT(): Figuring Combinations and Permutations

Trigonometry and Advanced Math Advanced Statistics

10. Financial Functions

The World of Finance Financial Functions

FV(): Future Value PV(): Present Value PMT(), PPMT(), and IPMT(): Calculating the Number of Payments You Need to Make NPER(): Figuring Out How Much Time You'll Need to Pay Offa Loan or Meet an Investment Target RATE(): Figuring the Interest Rate You Need to Achieve Future Value NPV() and IRR(): Net Present Value and Internal Rate of Return

Depreciation Other Financial Functions

11. Manipulating Dates, Times, and Text

Manipulating Text

CONCATENATE(): Joining Strings of Text Together LEFT(), MID(), and RIGHT(): Copying Portions of a Text String LEN(), FIND(), and SEARCH(): Counting Characters in a String UPPER(), LOWER(), and PROPER(): Changing Capitalization TRIM() and CLEAN(): Removing Unwanted Spaces and Non-Printing Characters SUBSTITUTE(): Replacing One Sequence of Characters with Another TEXT(), VALUE(), FIXED(), and DOLLAR(): Converting Text to Numbers and Vice Versa Other Text Functions

Manipulating Dates and Times Math with Dates and Times

Using Dates and Times with Ordinary Functions

Date and Time Functions

TODAY() and NOW(): Inserting the Current Date and Time DATE() and TIME(): Dates and Times in Calculations DAY(), MONTH(), and YEAR(): More Date Calculations HOUR(), MINUTE(), SECOND(): More Time Calculations WEEKDAY(): Determining the Day of the Week DATEDIF(): Calculating the Difference Between Dates DATEVALUE() and TIMEVALUE(): Converting Dates and Times into Serial Numbers DAYS360(): Finding Out the Number of Days Between Two Dates EDATE(): Calculating Future Dates YEARFRAC(): Calculating the Percentage of a Year Between Two Dates EOMONTH(): Finding the Last Day of Any Month NETWORKDAYS(): Counting the Number of Business Days WORKDAY(): Figuring Out When Days Will Fall in the Future WEEKNUM(): Figuring Out in Which Week a Date Falls

12. Lookup, Reference, and Information Functions

The Basic Lookup

VLOOKUP(): Vertical Lookups HLOOKUP(): Horizontal Lookups

Advanced Lookups

MATCH(): Finding the Position of Items in a Range INDEX(): Retrieving the Value from a Cell Performing a "Left Lookup" Performing a Double Lookup OFFSET(): Shifting Cell References Other Reference and Lookup Functions INDIRECT() and ADDRESS(): Working with Cell References Stored As Text TRANSPOSE(): Changing Rows into Columns and Vice Versa The HYPERLINK() Function: Creating a Dynamic Link

Information Functions

The "IS" Functions: Checking the Value Inside a Cell TYPE() and ERROR.TYPE(): Finding a Value's Data Type or Error Type INFO() and CELL(): Gathering Info About Your Computer and Your Worksheet's Cells

Tutorial: Generating Invoices from a Product Catalog

13. Advanced Formula Writing and Troubleshooting

Conditions in Formulas

IF(): Building Conditional Formulas COUNTIF(): Counting Only the Cells You Specify SUMIF(): Adding Only the Cells You Specify COUNTIFS() and SUMIFS(): Counting and Summing Using Multiple Criteria

Descriptive Names for Cell References

Creating and Using a Named Range Creating Slightly Smarter Named Ranges Naming Formulas and Constants Managing Named Ranges Automatically Creating Named Ranges Applying Names to Existing Formulas

Variable Data Tables

Creating a One-Variable Data Table Creating a Two-Variable Data Table

Controlling Recalculation Solving Formula Errors

Step-by-Step Evaluation Tracing Precedents and Dependents Error Checking

Three. Organizing Worksheets

14. Tables: List Management Made Easy

The Basics of Tables

Creating a Table Formatting a Table Editing a Table Selecting Parts of a Table

Sorting and Filtering a Table

Applying a Simple Sort Order Sorting with Multiple Criteria Sorting by Color Filtering with the List of Values Creating Smarter Filters

Filtering dates Filtering numbers Filtering text

Dealing with Duplicate Rows

Highlighting Duplicates Removing Duplicates Automatically

Performing Table Calculations

Dynamic Calculations Column Names Table Names The Total Row The SUBTOTAL() Function The Database Functions

15. Grouping and Outlining Data

Basic Data Grouping

Creating a Group Nesting Groups Within Groups Summarizing Your Data Combining Data from Multiple Tables

Grouping Timesavers

Auto Outline Automatic Subtotaling

16. Templates

Understanding Templates Creating a New Workbook from a Template

Downloading Templates (Method 1: From Backstage View) Downloading Templates (Method 2: The Office Online Website)

Creating Templates

Understanding Custom Templates Building a Custom Template Sharing Templates with Others

Four. Charts and Graphics

17. Creating Basic Charts

Charting 101

Embedded and Standalone Charts Creating a Chart with the Ribbon The Chart Tools Ribbon Tabs

Basic Tasks with Charts

Moving and Resizing a Chart Creating a Standalone Chart Editing and Adding to Chart Data Changing the Chart Type Printing Charts

Embedded charts Standalone charts

Practical Charting

Charts with Multiple Series of Numbers Controlling the Data Excel Plots on the X-Axis Data That Uses a Date or Time Scale Noncontiguous Chart Ranges Changing the Order of Your Data Series Changing the Way Excel Plots Blank Values

Chart Types

Column Bar Line Pie Area XY (Scatter) Stock Surface Donut Bubble Radar

18. Formatting and Perfecting Charts

Chart Styles and Layouts

Chart Styles Chart Layouts

Adding Chart Elements

Adding Titles Adding a Legend Adding Data Labels to a Series Adding Individual Data Labels Adding a Data Table

Selecting Chart Elements Formatting Chart Elements

Coloring the Background Fancy Fills

Gradient fills Texture fills Picture fills

Fancy Borders and Lines Formatting Data Series and Data Points Reusing Your Favorite Charts with Templates

Improving Your Charts

Controlling a Chart's Scale Adding a Trendline Adding Error Bars to Scientific Data Formatting 3-D Charts Changing the Shape of a 3-D Column

Advanced Charting

Exploding Slices in a Pie Grouping Slices in a Pie Gaps, Widths, and Overlays in a Column Chart Creating Combination Charts

19. Inserting Graphics

Adding Pictures to a Worksheet

Inserting a Picture Positioning and Resizing a Picture Picture Touch-Up Compressing Pictures Cropping and Shaping a Picture Picture Borders, Effects, and Styles

Excel's Clip Art Library Drawing Shapes

Drawing a Shape Adding Text to a Shape Selecting and Arranging Shapes Connecting Shapes SmartArt

Five. Advanced Data Analysis

20. Visualizing Your Data

Data Bars

Editing a Formatting Rule Fine-Tuning Data Bars

Putting data bars in a separate column Changing the data bar scale

Color Scales

Fine-Tuning Color Scales

Icon Sets

Fine-Tuning Icon Sets

Giving your icons added significance Hiding icons for some values

Sparklines

Creating a Sparkline Changing the Axis Markers

21. Scenarios and Goal Seeking

Using Scenarios

Creating a New Scenario Managing Scenarios Creating a Summary Report

Using Goal Seek

Goal Seeking with Complex Equations

Solver

Understanding Solver Defining a Problem in Solver More Advanced Solver Problems Saving Solver Models Configuring Solver

22. Pivot Tables

Summary Tables Revisited

Life Without Pivot Tables Life with Pivot Tables

Building Pivot Tables

Preparing a Pivot Table Pivot Table Regions Laying Out a Pivot Table Formatting a Pivot Table Rearranging a Pivot Table Getting to the Source

Multi-Layered Pivot Tables

Hiding and Showing Details

Fine-Tuning Pivot Table Calculations

Changing the Type of Calculation Adding a Calculated Field

Filtering a Pivot Table

Report Filtering Slicers Group Filtering

Pivot Charts

Creating a Pivot Chart Manipulating a Pivot Chart

23. Analyzing Databases, XML, and Web Pages

Excel and Databases

Connecting to an Access Database Refreshing Data Data Source Security Connecting to a SQL Server Database Reusing Your Database Connection

Understanding XML

What Is XML, Really? Three Rules of XML

The prolog Elements Nesting

XML Files and Schemas

Excel and XML

Mapping a Simple Document Importing and Exporting XML Mapping Lists Gaining the Benefits of XML Mapping

Web Queries

The Limitations of Web Queries Creating a Web Query The Research Pane—A Web Query Alternative

Six. Sharing Data with the Rest of the World

24. Protecting Your Workbooks

Understanding Excel's Safeguards Data Validation

Settings Input Message Error Alert Data Validation with Formulas and Cell References Data Validation with Lists

Locked and Hidden Cells

Protecting a Worksheet Protecting the Entire Workbook Protecting Cell Ranges (with More Passwords) Allowing Specific Windows Users to Edit a Range

25. Worksheet Collaboration

Your Excel Identity Preparing Your Workbook

Workbook Protection Checking for Issues Document Properties

Distributing a Document

Sending by Email Uploading to the Web

Adding Comments

Inserting a Comment Showing and Hiding Comments Fine-Tuning Comments Reviewing Comments Printing Comments

Tracking Changes

Switching On Change Tracking Understanding the Change Log Highlighting Changes Examining the Change Log Accepting and Rejecting Changes Merging Multiple Revisions into One Workbook

Sharing Your Workbook

Multiple Users Without Workbook Sharing Turning On Workbook Sharing Workbook Sharing in Action

26. Using Excel on the Web

Putting Your Files Online

Introducing SkyDrive Your Web Workflow Uploading a File to SkyDrive (Using Your Browser) Uploading a Workbook to SkyDrive (Using Excel) Uploading a Workbook to a SharePoint Server Viewing a File in Your SkyDrive Account Viewing a File in Someone Else's SkyDrive Account

Using the Excel Web App

Supported Features Saving Files Collaboration: The Excel Web App's Specialty Taking a Workbook Back to Desktop Excel Unsupported Features Partially Supported Features

27. Exchanging Data with Other Programs

Sharing Information in Windows Embedding and Linking Objects

Exporting Charts Out of Excel Editing a Linked Object Editing an Embedded Object Importing Objects into Excel

Transferring Data

Exporting Tables of Data Importing Tables of Data Importing Text Files

Seven. Programming Excel

28. Automating Tasks with Macros

Macros 101

Macro-Free and Macro-Enabled Workbooks

The Macro Recorder

Relative and Absolute Recording Where Macros Live Recording a Macro Playing a Macro

Macro Security

Trusted Documents Temporary Trust The Trust Center Setting Up a Trusted Location

Creating Practical Macros

Inserting a Header Alternating Row Formatting A Combined Task Placing a Macro on the Quick Access Toolbar Attaching a Macro to a Button Inside a Worksheet

29. Programming Spreadsheets with VBA

The Visual Basic Editor

The Project Window Modules and Macros Finding and Moving Macros Debugging a Macro

Understanding Macro Code

The Anatomy of a Macro Objects 101 Using Properties and Methods Hunting for Objects

Exploring the VBA Language

Entering Text in the Current Cell Interacting with Other Cells Editing Specific Cells Formatting Cells Using Variables Making Decisions Repeating Actions with a Loop Creating Custom Functions

Eight. Appendix

A. Customizing the Ribbon

Adding Your Favorites to the QAT

Adding Buttons Customizing Specific Workbooks

Personalizing the Ribbon

Changing Existing Tabs Creating Your Own Tab Saving and Reusing Your Custom Ribbon

Index About the Author SPECIAL OFFER: Upgrade this ebook with O’Reilly

← Prev
Back
Next →

← Prev
Back
Next →