Excel 2013 · the Missing Manual by MacDonald, Matthew -- Read -- Imperial Library of Trantor

Index

Excel 2013: The Missing Manual The Missing Credits

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

Introduction

What You Can Do with Excel The New Features in Excel 2013

The Office 365 Subscription Service Office RT: Office for Tablets The Office Web Apps

About This Book

About the Outline About→These→Arrows

Contextual Tabs Buttons with Menus Dialog Box Launchers Backstage View Ordinary Menus

About Shortcut Keys

Online Resources

Registration Feedback Errata Examples Safari© Books Online

1. Worksheet Basics

1. Creating Your First Spreadsheet

Starting a Workbook Adding Information to a Worksheet

Adding Column Titles Adding Data Editing Data Editing Cells with the Formula Bar

Using the Ribbon

The Tabs of the Ribbon Collapsing the Ribbon Using the Ribbon with the Keyboard The Quick Access Toolbar

Using the Status Bar Going Backstage Saving Files

The Excel File Format Sharing Your Spreadsheet with Older Versions of Excel Saving Your Spreadsheet for Excel 2003 Saving Your Spreadsheet As a PDF Password-Protecting Your Spreadsheet Disaster Recovery

AutoRecover Settings

Opening Files

Opening Files in Other Formats 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

Moving Around the Grid with Shortcut Keys The Go To Feature AutoComplete AutoCorrect AutoFit AutoFill

Custom AutoFill Lists

Flash Fill

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 Inserting Rows Inserting Copied or Cut Cells Deleting Columns and Rows

4. Managing Worksheets

Worksheets and Workbooks

Adding and Removing Worksheets Moving Between 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

2. 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 Window

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 BASE() and DECIMAL(): Converting Numbers to Different Bases ROMAN() and ARABIC(): Using Roman Numerals

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 Need to Pay Off a Loan or Meet an Investment Target RATE(): Calculating the Interest Rate You Need to Achieve a Target RRI(): Calculating the Interest Rate Your Investments Achieve 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 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 IFNA(): Dealing with Failed 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 FORMULATEXT(): Getting the Text of a Formula Other Reference and Lookup Functions INDIRECT() and ADDRESS(): Working with Cell References Stored As Text TRANSPOSE(): Changing Rows to 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

Dealing with Blank Values Dealing with Incorrect Values

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

3. Organizing Your Information

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 a Table

Applying a Simple Sort Order Sorting with Multiple Criteria Sorting by Color

Filtering a Table

Hiding Specific Values Filtering Dates Filtering Numbers Filtering Text Applying Filters with Slicers

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

Using the Office Online Templates

Creating a New Workbook from an Office Online Template Make Your Favorite Templates Stick Around Downloading Templates from the Office Online Website

Rolling Your Own Templates

Setting Up a Personal Template Folder Building a Custom Template

4. Charts and Graphics

17. Creating Basic Charts

Charting 101

Embedded and Standalone Charts Adding a Recommended Chart Picking from the Full Range of Charts Selecting a Chart

Basic Tasks with Charts

Moving and Resizing a Chart Creating a Standalone Chart Editing and Adding to Chart Data Filtering 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 Scatter (XY) Stock Surface Donut Bubble Radar

18. Formatting and Perfecting Charts

Chart Styles and Layouts

Chart Styles Chart Colors Chart Layouts

Adding Chart Elements

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

Formatting Chart Elements

Selecting a Chart Element Coloring the Background Fancy Fills

Gradient Fills Texture Fills Picture Fills

Fancy Borders and Lines Formatting Data Series and Data Points

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 from a File Positioning and Resizing a Picture Picture Touch-Up Removing the Background Behind a Picture Cropping and Shaping a Picture Picture Borders, Effects, and Styles Compressing Pictures

Excel’s Clip Art Library Drawing Shapes

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

SmartArt

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

5. Sharing Data with the Rest of the World

21. 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 an Entire Workbook Protecting Cell Ranges (with More Passwords) Allowing Specific Windows Users to Edit a Range

22. Worksheet Collaboration

Your Excel Identity Preparing Your Workbook

Workbook Protection Checking for Issues Document Properties

Distributing Your Workbook

Sending by Email

Adding Comments

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

Tracking Changes

Turning 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

Reviewing Workbooks with Inquire

Turning on the Inquire Add-In Generating a Spreadsheet Report Comparing two Workbooks Following the Formula Trail

23. Using Excel on the Web

Putting Your Files Online

Introducing SkyDrive Using SkyDrive Your Web Workflow Uploading a File to SkyDrive Using Your Browser Uploading a Workbook to SkyDrive Using Excel

Using the Excel Web App

Supported Features Unsupported Features Partially Supported Features Saving Files Taking a Workbook Back to Desktop Excel

Sharing Your Files

Inviting Specific People Creating a Sharing Link Spreading the Word on Social Networks Collaboration: The Excel Web App’s Specialty Creating a Survey

24. Exchanging Data with Other Programs

Sharing Information in Windows Embedding and Linking Objects

Embedding an Excel Chart in Another Program Creating a Linked Chart Object Importing Objects into Excel

Transferring Data

Exporting Tables of Data Importing Tables of Data Importing Text Files

6. Advanced Data Analysis

25. 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

26. 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 Timelines Group Filtering

Pivot Charts

Creating a Pivot Chart Manipulating a Pivot Chart

27. Analyzing Databases

Excel and Databases Creating a Data Connection

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

The Data Model: Boosting Pivot Tables

Dealing with Big Tables Understanding Relationships Defining a Relationship Importing Relationships Going Behind the Scenes with PowerPivot Creating a Calculated Field with PowerPivot

28. Analyzing XML and Web Data

Understanding XML

What Is XML, Really? Three Rules of XML

The Prolog Elements Nesting

XML Files and Schemas

Retrieving Information from XML

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

Creating Web Queries

The Limitations of Web Queries Creating a Web Query

Connecting to Online Data Services with OData

Creating an OData Query

7. Programming Excel

29. 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

30. 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

8. 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 Exporting Your Custom Ribbon

Index About the Author Copyright

← Prev
Back
Next →

← Prev
Back
Next →