Using Microsoft Excel 2010

Description

Practical, approachable coverage completely flattens the Microsoft Excel 2010 learning curve in this handbook that is tightly integrated with online video, screencast tutorials, podcasts, and more: the total learning experience for new Excel 2010 users.

Author description

Tracy Syrstad is the project manager for the MrExcel consulting team and also handles many of the tech support phone calls. If you have a problem with Excel or another Microsoft Office product, she's there to listen and provide a solution. Bill Jelen is the host of MrExcel.com. You will frequently find Bill on the road, entertaining people with his Power Excel seminars. He is the author of many books about Microsoft Excel and is the voice of the Learn Excel from MrExcel podcast.

Table of contents

Introduction ...1 1 Getting to Know the Excel Interface ...7 Customizing the Ribbon ...7 Removing Default Tabs and Groups ...7 Creating a Custom Tab ...8 Adding a New Group ...9 Adding a New Button ...9 Minimizing the Ribbon ...10 Customizing the Quick Access Toolbar ...11 Move the Quick Access Toolbar ...11 Adding Commands to the Quick Access Toolbar ...11 Customizing the Quick Access Toolbar for the Current Workbook ...14 Removing Commands from the Quick Access Toolbar ...15 Dialog Launchers ...15 Zoom ...16 Page Break Preview ...17 Changing a Page's Column Break ...17 Page Layout ...18 Inserting the Filename and Date into the Header ...18 Page Numbering ...19 Inserting Page of Pages Page Numbering ...19 Repeating Rows or Columns When Printing ...20 Repeating a Header Row ...20 Freezing Panes ...20 Freezing Columns and Rows at the Same Time ...21 Working with Custom Views ...22 Creating a Custom View of Filtered Data ...22 Arranging Windows ...23 Inserting Rows & Columns ...23 Inserting a Row ...24 Renaming a Sheet ...24 Moving or Copying Sheets ...24 Within the Workbook ...25 Between Workbooks ...25 Change Multiple Worksheets at One Time ...25 Changing Multiple Sheets ...26 Setting Workbook and Sheet Protection ...26 Setting Workbook Protection ...26 Setting Sheet Protection ...27 Unlocking Cells ...27 Recent Documents ...27 Saving as PDF ...28 Opening Templates ...28 Troubleshooting Excel Options ...29 2 Introducing the Excel Web App ...31 Requirements ...31 Acquiring a Windows Live ID ...31 Creating a Windows Live ID ...32 Uploading and Downloading Workbooks ...33 Accessing the SkyDrive ...34 Uploading with Windows Live ...34 Saving to SkyDrive from Excel ...35 Creating a New Workbook ...36 Opening a Workbook ...37 Saving a File to Your Local Drive ...37 Sharing a Workbook ...38 Creating a New Folder ...38 Setting Folder Permissions ...39 Simultaneous Editing ...40 Interacting with a Sheet Online ...41 3 Entering Data in Excel ...43 Dragging the Fill Handle to Extend a Series ...43 Extending a Series Containing Text ...44 Extending a Numerical Series ...44 Creating Your Own Series ...44 Creating a Custom List ...45 Joining Text ...45 Joining a First Name and Last Name ...46 Joining Dates and Text ...46 Fixing Numbers as Text ...47 Using Convert to Number on a Range ...48 Using Paste Special to Force a Number ...48 Convert Text Case ...48 Copying Formulas Rapidly ...49 Filling a Formula Down a Column ...49 Converting Formulas to Values ...50 Quickly Replacing Formulas with Values ...50 Using Text to Columns ...51 Working with Delimited Text ...51 Working with Fixed Width Text ...53 Using Text to Columns to Convert Text to Numbers ...55 Controlling the Next Cell Selection ...55 Enter Versus Tab ...55 Moving Cell Pointer Direction ...56 Preselecting the Data Range ...56 Entering Data in a Circle ...56 Working with Tables ...57 Defining a Table ...57 Expanding a Table ...59 Adding a Total Row to a Table ...60 Data Validation ...61 Limiting User Entry to a Selection from a List ...62 4 Data Formatting ...65 Cell Formatting ...65 Font Group ...65 Alignment Group ...69 Justifying Text in a Range ...72 Reflowing Text in a Paragraph ...73 Cell Styles ...74 Creating a Custom Style ...74 Using Number Formats in Excel and Excel Starter ...74 General ...75 Number ...75 Currency ...75 Accounting ...76 Date ...76 Time ...76 Percentage ...76 Fraction ...77 Scientific ...77 Text ...77 Special ...78 Custom Formats ...78 Dealing with Formatting Issues ...84 Using Number Formats in Excel Web Apps ...84 Using the Number Format Drop-down ...84 Using Increase and Decrease Decimal ...84 Using Format Painter ...85 Copying the Formatting of a Range ...86 Adjusting Row Heights and Column Widths ...87 Applying One Column's Width to Other Columns ...88 Using Themes ...88 Applying a New Theme ...89 Creating a New Theme ...89 Sharing a Theme ...90 Using Conditional Formatting ...91 Applying a Custom Icon Set ...92 Using Rules ...93 Clearing Conditional Formatting ...98 5 Using Formulas ...99 The Importance of Laying Data Out Properly ...99 Adjusting Calculation Settings ...100 Formulas Versus Values ...100 Entering a Formula ...101 Enter a Formula ...101 Relative Versus Absolute Formulas ...101 R1C1 Notation ...102 Using F4 to Change the Cell Referencing ...103 Entering Formulas ...104 Copying a Formula ...104 Copying a Formula by Using Ctrl+Enter ...105 Copying a Formula by Dragging the Fill Handle ...105 Formula Operators ...106 Order of Operations ...107 Using Names To Simplify References ...107 Applying and Using a Name in a Formula ...108 Inserting Formulas into Tables ...109 Entering a Formula in a Table ...109 Table References in Formulas ...110 Writing Formulas That Refer to Tables ...111 Using Array Formulas ...111 Example 1 ...112 Example 2 ...112 Example 3 ...113 Editing Array Formulas ...114 Deleting Array Formulas ...115 Troubleshooting Formulas ...115 Error Messages ...116 Trace Precedents and Dependents ...116 Watch Window ...118 Evaluating Formulas ...119 6 Using Functions ...121 Breaking Down a Function ...121 Finding Functions ...121 Entering Functions ...122 The Formula Wizard ...122 Using In-Cell Tips ...124 AutoSum ...125 Other Auto Functions ...126 Lookup Functions ...127 CHOOSE ...127 VLOOKUP ...127 MATCH and INDEX ...129 INDIRECT ...130 SUMIFS ...131 SUMPRODUCT ...132 Logical Functions ...133 IF/AND/OR/NOT ...134 Nested IF Statements ...135 IFERROR ...135 Date and Time Functions ...136 Convert and Breakdown Dates ...136 Convert and Breakdown Times ...138 Date Calculation Functions ...138 Troubleshooting: Calculating with Dates ...140 Data Analysis Tools ...142 Goal Seek ...144 Using Goal Seek ...145 Using the Wizard to Troubleshoot Formulas ...145 Troubleshooting a Formula ...145 7 Sorting ...147 Preparing Data ...147 Sorting in the Web App ...147 The Sort Dialog ...147 Sorting by Values ...148 Sorting by Color or Icon ...149 Doing a Case-Sensitive Sort ...151 Using the Quick Sort Buttons ...151 Quick Sorting Multiple Columns ...151 Random Sort ...152 Randomizing Data ...152 Sorting with a Custom Sequence ...153 Using a Custom List ...153 Rearranging Columns ...155 Using the Sort Dialog ...155 Using the Mouse ...156 Fixing Sort Problems ...157 8 Filtering and Consolidating Data ...159 Preparing Data ...159 Applying a Filter to a Dataset ...159 Clearing a Filter ...160 Reapplying a Filter ...161 Filtering in the Web App ...161 Turn Filtering on for One Column ...161 Filtering Options ...162 Filter Listing for Listed Items ...162 Grouped Dates Filter Listing ...163 Searching Functions for Listed Items ...165 Using the Search Function for Grouped Dates ...167 Using Text, Number and Date Special Filters ...169 Filtering by Color or Icon ...171 Filtering By Selection ...172 Using Filter Selection ...172 Allow Filtering on a Protected Sheet ...172 Using the Advanced Filter ...173 Using the Criteria Range ...174 Filtering for Unique Items ...176 Removing Duplicates ...177 Removing Duplicates from a Dataset ...177 Consolidating Data ...178 Consolidating Duplicate Data by Category ...179 9 Subtotals and Grouping ...181 SUBTOTAL Function ...181 Subtotal Tool ...182 Summarizing Data Using the Subtotal Command ...183 Placing Subtotals Above Data ...184 Remove Subtotals or Groups ...184 Expanding and Collapsing Subtotals ...184 Copying Subtotals ...185 Copying Only the Totals to a New Location ...185 Formatting Subtotals ...186 Applying Formatting to Only the Totals ...186 Applying Multiple Subtotal Function Types ...187 Applying Multiple Subtotal Function Types ...187 Combining Multiple Subtotals to One Row ...188 Subtotaling by Multiple Columns ...190 Subtotaling by Multiple Columns ...191 Sorting Subtotals ...192 Sorting a Subtotaled Column ...192 Inserting Blank Rows ...192 Separating Subtotaled Rows for Print ...193 Separating Subtotaled Rows for Distributed Files ...194 Grouping and Outlining ...196 Manually Grouping Rows ...198 10 Pivot Tables ...199 Data Preparation ...199 Pivot Table Limitations ...200 Pivot Table Compatibility ...201 PivotTable Field List ...201 Creating a Pivot Table ...202 Creating a Pivot Table ...203 Moving Fields in a Pivot Table ...205 Remove a Field ...205 Rename a Field ...205 Change Calculation Type ...206 Change the Calculation Type of a Field Value ...206 Show Values Based on Other Items ...206 Pivot Table Sorting ...208 Pivot Table Quick Sort ...208 Pivot Table Sort (Fieldname) Dialog ...208 Expanding and Collapsing Fields ...211 Drill Down ...211 Grouping Dates ...212 Group a Date Field into Months and Years ...213 Summarize Weeks ...213 Filtering Options ...214 Filter Listing for Listed Items ...214 Search Function for Listed Items ...215 Special Filters ...218 Filter By Selection ...221 Filter by Manual Selection ...221 Clearing Filters ...221 Calculated Fields ...222 Create a Calculated Field ...223 Adding Color and Lines to a Pivot Table ...223 Hiding Totals ...223 Formatting Values ...224 Pivot Table Views ...225 Slicers ...226 Add a Slicer to a Pivot Table ...227 Making Data Suitable for Pivot Tables ...227 11 Creating Charts ...231 Components of a Chart ...231 Preparing Data ...232 Types of Charts ...233 Creating a Chart ...234 Creating a Chart ...235 Chart Styles ...236 Chart Layouts ...237 Adding a Chart Title ...237 Adding an Axis Title ...238 Adding or Moving the Chart Legend ...238 Moving or Resizing a Chart ...239 Changing a Chart's Type ...239 Mixing Chart Types ...240 Creating a Chart with Multiple Chart Types ...240 Showing Numbers of Different Scale ...241 Chart Data of Vastly Different Scales ...242 Updating Chart Data ...242 Pasting New Data onto Existing Series ...243 Switching Rows and Columns ...243 Trendlines ...244 Adding a Forecasting Trendline ...245 Stock Charts ...246 Creating a Stock Chart ...246 Bubble Charts ...247 Pie Chart Issue: Small Slices ...247 Rotating the Pie ...248 Create a Bar of Pie Chart ...248 Sparklines ...250 Creating Sparklines ...250 Adding Points to a Sparkline ...250 Spacing Markers in a Sparkline ...251 Delete Sparklines ...252 Saving a Chart as a Template ...252 Creating a Chart Using a User-Created Template ...253 12 SmartArt, WordArt, and Pictures ...255 SmartArt ...255 Inserting SmartArt ...256 Inserting SmartArt Images ...257 Selecting SmartArt ...259 Adding and Deleting Shapes ...259 Reorder Components ...260 Formatting the Selected Layout ...261 Changing the Selected Layout ...261 Changing an Individual Component ...261 Linking a Cell to Smart Art ...262 Inserting WordArt ...264 Inserting Pictures ...265 Inserting a Picture ...265 Resizing and Cropping Pictures ...265 Corrections, Color, and Artistic Effects ...267 Arranging Pictures ...268 Reducing a Picture's File Size ...270 13 Macros and UDFs ...271 Enabling VBA Security ...271 Developer Tab ...273 Introduction to the Visual Basic Editor ...273 Project Explorer ...274 Understanding How the Macro Recorder Works ...275 Navigating While Recording ...275 Relative References in Macro Recording ...276 Avoid the AutoSum Button ...278 Recording a Macro ...279 Filling in the Record Macro Dialog ...279 Running a Macro ...281 Running a Macro from the Ribbon ...281 Running a Macro from the Quick Access Toolbar ...282 Running a Macro from a Form Control, Text Box, or Shape ...283 User-Defined Functions ...284 Structure of a UDF ...285 How to Use a UDF ...287 Sharing UDFs ...287 Using Select Case to Replace Nested IF ...288 TOC, 9780789742902, 7/13/10