Using Microsoft Excel 2010

Get comfortable with the latest versions of Microsoft Excel, Microsoft Excel Starter, and the Excel Web App. Don’t just read about it: see it, hear it, with step-by-step video tutorials and valuable audio sidebars delivered through the Free Web Edition that comes with every USING book. For the price of the book, you get online access anywhere with a web connection–no books to carry, updated content, and the benefit of video and audio learning. Way more than just a book, this is all the help you’ll ever need...where you want, when you want! learn fast, learn easy, using web, video, and audio **Show Me** video walks through tasks you’ve just got to see–including bonus advanced techniques **Tell Me More** audio delivers practical insights straight from the experts UNLOCK THE FREE WEB EDITION–To register your USING book, visit quepublishing.com/using. Table of Contents 8 Introduction 21 1 Getting to Know the Excel Interface 27 Customizing the Ribbon 27 Removing Default Tabs and Groups 27 Creating a Custom Tab 28 Adding a New Group 29 Adding a New Button 29 Minimizing the Ribbon 30 Customizing the Quick Access Toolbar 31 Move the Quick Access Toolbar 31 Adding Commands to the Quick Access Toolbar 31 Customizing the Quick Access Toolbar for the Current Workbook 34 Removing Commands from the Quick Access Toolbar 35 Dialog Launchers 35 Zoom 36 Page Break Preview 37 Changing a Page's Column Break 37 Page Layout 38 Inserting the Filename and Date into the Header 38 Page Numbering 39 Inserting Page of Pages Page Numbering 39 Repeating Rows or Columns When Printing 40 Repeating a Header Row 40 Freezing Panes 40 Freezing Columns and Rows at the Same Time 41 Working with Custom Views 42 Creating a Custom View of Filtered Data 42 Arranging Windows 43 Inserting Rows & Columns 43 Inserting a Row 44 Renaming a Sheet 44 Moving or Copying Sheets 44 Within the Workbook 45 Between Workbooks 45 Change Multiple Worksheets at One Time 45 Changing Multiple Sheets 46 Setting Workbook and Sheet Protection 46 Setting Workbook Protection 46 Setting Sheet Protection 47 Unlocking Cells 47 Recent Documents 47 Saving as PDF 48 Opening Templates 48 Troubleshooting Excel Options 49 2 Introducing the Excel Web App 51 Requirements 51 Acquiring a Windows Live ID 51 Creating a Windows Live ID 52 Uploading and Downloading Workbooks 53 Accessing the SkyDrive 54 Uploading with Windows Live 54 Saving to SkyDrive from Excel 55 Creating a New Workbook 56 Opening a Workbook 57 Saving a File to Your Local Drive 57 Sharing a Workbook 58 Creating a New Folder 58 Setting Folder Permissions 59 Simultaneous Editing 60 Interacting with a Sheet Online 61 3 Entering Data in Excel 63 Dragging the Fill Handle to Extend a Series 63 Extending a Series Containing Text 64 Extending a Numerical Series 64 Creating Your Own Series 64 Creating a Custom List 65 Joining Text 65 Joining a First Name and Last Name 66 Joining Dates and Text 66 Fixing Numbers as Text 67 Using Convert to Number on a Range 68 Using Paste Special to Force a Number 68 Convert Text Case 68 Copying Formulas Rapidly 69 Filling a Formula Down a Column 69 Converting Formulas to Values 70 Quickly Replacing Formulas with Values 70 Using Text to Columns 71 Working with Delimited Text 71 Working with Fixed Width Text 73 Using Text to Columns to Convert Text to Numbers 75 Controlling the Next Cell Selection 75 Enter Versus Tab 75 Moving Cell Pointer Direction 76 Preselecting the Data Range 76 Entering Data in a Circle 76 Working with Tables 77 Defining a Table 77 Expanding a Table 79 Adding a Total Row to a Table 80 Data Validation 81 Limiting User Entry to a Selection from a List 82 4 Data Formatting 85 Cell Formatting 85 Font Group 85 Alignment Group 89 Justifying Text in a Range 92 Reflowing Text in a Paragraph 93 Cell Styles 94 Creating a Custom Style 94 Using Number Formats in Excel and Excel Starter 94 General 95 Number 95 Currency 95 Accounting 96 Date 96 Time 96 Percentage 96 Fraction 97 Scientific 97 Text 97 Special 98 Custom Formats 98 Dealing with Formatting Issues 104 Using Number Formats in Excel Web Apps 104 Using the Number Format Drop-down 104 Using Increase and Decrease Decimal 104 Using Format Painter 105 Copying the Formatting of a Range 106 Adjusting Row Heights and Column Widths 107 Applying One Column's Width to Other Columns 108 Using Themes 108 Applying a New Theme 109 Creating a New Theme 109 Sharing a Theme 110 Using Conditional Formatting 111 Applying a Custom Icon Set 112 Using Rules 113 Clearing Conditional Formatting 118 5 Using Formulas 119 The Importance of Laying Data Out Properly 119 Adjusting Calculation Settings 120 Formulas Versus Values 120 Entering a Formula 121 Enter a Formula 121 Relative Versus Absolute Formulas 121 R1C1 Notation 122 Using F4 to Change the Cell Referencing 123 Entering Formulas 124 Copying a Formula 124 Copying a Formula by Using Ctrl+Enter 125 Copying a Formula by Dragging the Fill Handle 125 Formula Operators 126 Order of Operations 127 Using Names To Simplify References 127 Applying and Using a Name in a Formula 128 Inserting Formulas into Tables 129 Entering a Formula in a Table 129 Table References in Formulas 130 Writing Formulas That Refer to Tables 131 Using Array Formulas 131 Example 1 132 Example 2 132 Example 3 133 Editing Array Formulas 134 Deleting Array Formulas 135 Troubleshooting Formulas 135 Error Messages 136 Trace Precedents and Dependents 136 Watch Window 138 Evaluating Formulas 139 6 Using Functions 141 Breaking Down a Function 141 Finding Functions 141 Entering Functions 142 The Formula Wizard 142 Using In-Cell Tips 144 AutoSum 145 Other Auto Functions 146 Lookup Functions 147 CHOOSE 147 VLOOKUP 147 MATCH and INDEX 149 INDIRECT 150 SUMIFS 151 SUMPRODUCT 152 Logical Functions 153 IF/AND/OR/NOT 154 Nested IF Statements 155 IFERROR 155 Date and Time Functions 156 Convert and Breakdown Dates 156 Convert and Breakdown Times 158 Date Calculation Functions 158 Troubleshooting: Calculating with Dates 160 Data Analysis Tools 162 Goal Seek 164 Using Goal Seek 165 Using the Wizard to Troubleshoot Formulas 165 Troubleshooting a Formula 165 7 Sorting 167 Preparing Data 167 Sorting in the Web App 167 The Sort Dialog 167 Sorting by Values 168 Sorting by Color or Icon 169 Doing a Case-Sensitive Sort 171 Using the Quick Sort Buttons 171 Quick Sorting Multiple Columns 171 Random Sort 172 Randomizing Data 172 Sorting with a Custom Sequence 173 Using a Custom List 173 Rearranging Columns 175 Using the Sort Dialog 175 Using the Mouse 176 Fixing Sort Problems 177 8 Filtering and Consolidating Data 179 Preparing Data 179 Applying a Filter to a Dataset 179 Clearing a Filter 180 Reapplying a Filter 181 Filtering in the Web App 181 Turn Filtering on for One Column 181 Filtering Options 182 Filter Listing for Listed Items 182 Grouped Dates Filter Listing 183 Searching Functions for Listed Items 185 Using the Search Function for Grouped Dates 187 Using Text, Number and Date Special Filters 189 Filtering by Color or Icon 191 Filtering By Selection 192 Using Filter Selection 192 Allow Filtering on a Protected Sheet 192 Using the Advanced Filter 193 Using the Criteria Range 194 Filtering for Unique Items 196 Removing Duplicates 197 Removing Duplicates from a Dataset 197 Consolidating Data 198 Consolidating Duplicate Data by Category 199 9 Subtotals and Grouping 201 SUBTOTAL Function 201 Subtotal Tool 202 Summarizing Data Using the Subtotal Command 203 Placing Subtotals Above Data 204 Remove Subtotals or Groups 204 Expanding and Collapsing Subtotals 204 Copying Subtotals 205 Copying Only the Totals to a New Location 205 Formatting Subtotals 206 Applying Formatting to Only the Totals 206 Applying Multiple Subtotal Function Types 207 Applying Multiple Subtotal Function Types 207 Combining Multiple Subtotals to One Row 208 Subtotaling by Multiple Columns 210 Subtotaling by Multiple Columns 211 Sorting Subtotals 212 Sorting a Subtotaled Column 212 Inserting Blank Rows 212 Separating Subtotaled Rows for Print 213 Separating Subtotaled Rows for Distributed Files 214 Grouping and Outlining 216 Manually Grouping Rows 218 10 Pivot Tables 219 Data Preparation 219 Pivot Table Limitations 220 Pivot Table Compatibility 221 PivotTable Field List 221 Creating a Pivot Table 222 Creating a Pivot Table 223 Moving Fields in a Pivot Table 225 Remove a Field 225 Rename a Field 225 Change Calculation Type 226 Change the Calculation Type of a Field Value 226 Show Values Based on Other Items 226 Pivot Table Sorting 228 Pivot Table Quick Sort 228 Pivot Table Sort (Fieldname) Dialog 228 Expanding and Collapsing Fields 231 Drill Down 231 Grouping Dates 232 Group a Date Field into Months and Years 233 Summarize Weeks 233 Filtering Options 234 Filter Listing for Listed Items 234 Search Function for Listed Items 235 Special Filters 238 Filter By Selection 241 Filter by Manual Selection 241 Clearing Filters 241 Calculated Fields 242 Create a Calculated Field 243 Adding Color and Lines to a Pivot Table 243 Hiding Totals 243 Formatting Values 244 Pivot Table Views 245 Slicers 246 Add a Slicer to a Pivot Table 247 Making Data Suitable for Pivot Tables 247 11 Creating Charts 251 Components of a Chart 251 Preparing Data 252 Types of Charts 253 Creating a Chart 254 Creating a Chart 255 Chart Styles 256 Chart Layouts 257 Adding a Chart Title 257 Adding an Axis Title 258 Adding or Moving the Chart Legend 258 Moving or Resizing a Chart 259 Changing a Chart's Type 259 Mixing Chart Types 260 Creating a Chart with Multiple Chart Types 260 Showing Numbers of Different Scale 261 Chart Data of Vastly Different Scales 262 Updating Chart Data 262 Pasting New Data onto Existing Series 263 Switching Rows and Columns 263 Trendlines 264 Adding a Forecasting Trendline 265 Stock Charts 266 Creating a Stock Chart 266 Bubble Charts 267 Pie Chart Issue: Small Slices 267 Rotating the Pie 268 Create a Bar of Pie Chart 268 Sparklines 270 Creating Sparklines 270 Adding Points to a Sparkline 270 Spacing Markers in a Sparkline 271 Delete Sparklines 272 Saving a Chart as a Template 272 Creating a Chart Using a User-Created Template 273 12 SmartArt, WordArt, and Pictures 275 SmartArt 275 Inserting SmartArt 276 Inserting SmartArt Images 277 Selecting SmartArt 279 Adding and Deleting Shapes 279 Reorder Components 280 Formatting the Selected Layout 281 Changing the Selected Layout 281 Changing an Individual Component 281 Linking a Cell to Smart Art 282 Inserting WordArt 284 Inserting Pictures 285 Inserting a Picture 285 Resizing and Cropping Pictures 285 Corrections, Color, and Artistic Effects 287 Arranging Pictures 288 Reducing a Picture's File Size 290 13 Macros and UDFs 291 Enabling VBA Security 291 Developer Tab 293 Introduction to the Visual Basic Editor 293 Project Explorer 294 Understanding How the Macro Recorder Works 295 Navigating While Recording 295 Relative References in Macro Recording 296 Avoid the AutoSum Button 298 Recording a Macro 299 Filling in the Record Macro Dialog 299 Running a Macro 301 Running a Macro from the Ribbon 301 Running a Macro from the Quick Access Toolbar 302 Running a Macro from a Form Control, Text Box, or Shape 303 User-Defined Functions 304 Structure of a UDF 305 How to Use a UDF 307 Sharing UDFs 307 Using Select Case to Replace Nested IF 308 Index 311 A 311 B 311 C 311 D 313 E 314 F 314 G 316 H 316 I 316 J-K 316 L 316 M 317 N 317 O 317 P 318 Q 318 R 318 S 319 T 320 U 321 V 321 W 321 X-Y 322 Z 322 Table of Contents......Page 8 Introduction......Page 21 Removing Default Tabs and Groups......Page 27 Creating a Custom Tab......Page 28 Adding a New Button......Page 29 Minimizing the Ribbon......Page 30 Adding Commands to the Quick Access Toolbar......Page 31 Customizing the Quick Access Toolbar for the Current Workbook......Page 34 Dialog Launchers......Page 35 Zoom......Page 36 Changing a Page's Column Break......Page 37 Inserting the Filename and Date into the Header......Page 38 Inserting Page of Pages Page Numbering......Page 39 Freezing Panes......Page 40 Freezing Columns and Rows at the Same Time......Page 41 Creating a Custom View of Filtered Data......Page 42 Inserting Rows & Columns......Page 43 Moving or Copying Sheets......Page 44 Change Multiple Worksheets at One Time......Page 45 Setting Workbook Protection......Page 46 Recent Documents......Page 47 Opening Templates......Page 48 Troubleshooting Excel Options......Page 49 Acquiring a Windows Live ID......Page 51 Creating a Windows Live ID......Page 52 Uploading and Downloading Workbooks......Page 53 Uploading with Windows Live......Page 54 Saving to SkyDrive from Excel......Page 55 Creating a New Workbook......Page 56 Saving a File to Your Local Drive......Page 57 Creating a New Folder......Page 58 Setting Folder Permissions......Page 59 Simultaneous Editing......Page 60 Interacting with a Sheet Online......Page 61 Dragging the Fill Handle to Extend a Series......Page 63 Creating Your Own Series......Page 64 Joining Text......Page 65 Joining Dates and Text......Page 66 Fixing Numbers as Text......Page 67 Convert Text Case......Page 68 Filling a Formula Down a Column......Page 69 Quickly Replacing Formulas with Values......Page 70 Working with Delimited Text......Page 71 Working with Fixed Width Text......Page 73 Enter Versus Tab......Page 75 Entering Data in a Circle......Page 76 Defining a Table......Page 77 Expanding a Table......Page 79 Adding a Total Row to a Table......Page 80 Data Validation......Page 81 Limiting User Entry to a Selection from a List......Page 82 Font Group......Page 85 Alignment Group......Page 89 Justifying Text in a Range......Page 92 Reflowing Text in a Paragraph......Page 93 Using Number Formats in Excel and Excel Starter......Page 94 Currency......Page 95 Percentage......Page 96 Text......Page 97 Custom Formats......Page 98 Using Increase and Decrease Decimal......Page 104 Using Format Painter......Page 105 Copying the Formatting of a Range......Page 106 Adjusting Row Heights and Column Widths......Page 107 Using Themes......Page 108 Creating a New Theme......Page 109 Sharing a Theme......Page 110 Using Conditional Formatting......Page 111 Applying a Custom Icon Set......Page 112 Using Rules......Page 113 Clearing Conditional Formatting......Page 118 The Importance of Laying Data Out Properly......Page 119 Formulas Versus Values......Page 120 Relative Versus Absolute Formulas......Page 121 R1C1 Notation......Page 122 Using F4 to Change the Cell Referencing......Page 123 Copying a Formula......Page 124 Copying a Formula by Dragging the Fill Handle......Page 125 Formula Operators......Page 126 Using Names To Simplify References......Page 127 Applying and Using a Name in a Formula......Page 128 Entering a Formula in a Table......Page 129 Table References in Formulas......Page 130 Using Array Formulas......Page 131 Example 2......Page 132 Example 3......Page 133 Editing Array Formulas......Page 134 Troubleshooting Formulas......Page 135 Trace Precedents and Dependents......Page 136 Watch Window......Page 138 Evaluating Formulas......Page 139 Finding Functions......Page 141 The Formula Wizard......Page 142 Using In-Cell Tips......Page 144 AutoSum......Page 145 Other Auto Functions......Page 146 VLOOKUP......Page 147 MATCH and INDEX......Page 149 INDIRECT......Page 150 SUMIFS......Page 151 SUMPRODUCT......Page 152 Logical Functions......Page 153 IF/AND/OR/NOT......Page 154 IFERROR......Page 155 Convert and Breakdown Dates......Page 156 Date Calculation Functions......Page 158 Troubleshooting: Calculating with Dates......Page 160 Data Analysis Tools......Page 162 Goal Seek......Page 164 Troubleshooting a Formula......Page 165 The Sort Dialog......Page 167 Sorting by Values......Page 168 Sorting by Color or Icon......Page 169 Quick Sorting Multiple Columns......Page 171 Randomizing Data......Page 172 Using a Custom List......Page 173 Using the Sort Dialog......Page 175 Using the Mouse......Page 176 Fixing Sort Problems......Page 177 Applying a Filter to a Dataset......Page 179 Clearing a Filter......Page 180 Turn Filtering on for One Column......Page 181 Filter Listing for Listed Items......Page 182 Grouped Dates Filter Listing......Page 183 Searching Functions for Listed Items......Page 185 Using the Search Function for Grouped Dates......Page 187 Using Text, Number and Date Special Filters......Page 189 Filtering by Color or Icon......Page 191 Allow Filtering on a Protected Sheet......Page 192 Using the Advanced Filter......Page 193 Using the Criteria Range......Page 194 Filtering for Unique Items......Page 196 Removing Duplicates from a Dataset......Page 197 Consolidating Data......Page 198 Consolidating Duplicate Data by Category......Page 199 SUBTOTAL Function......Page 201 Subtotal Tool......Page 202 Summarizing Data Using the Subtotal Command......Page 203 Expanding and Collapsing Subtotals......Page 204 Copying Only the Totals to a New Location......Page 205 Applying Formatting to Only the Totals......Page 206 Applying Multiple Subtotal Function Types......Page 207 Combining Multiple Subtotals to One Row......Page 208 Subtotaling by Multiple Columns......Page 210 Subtotaling by Multiple Columns......Page 211 Inserting Blank Rows......Page 212 Separating Subtotaled Rows for Print......Page 213 Separating Subtotaled Rows for Distributed Files......Page 214 Grouping and Outlining......Page 216 Manually Grouping Rows......Page 218 Data Preparation......Page 219 Pivot Table Limitations......Page 220 PivotTable Field List......Page 221 Creating a Pivot Table......Page 222 Creating a Pivot Table......Page 223 Rename a Field......Page 225 Show Values Based on Other Items......Page 226 Pivot Table Sort (Fieldname) Dialog......Page 228 Drill Down......Page 231 Grouping Dates......Page 232 Summarize Weeks......Page 233 Filter Listing for Listed Items......Page 234 Search Function for Listed Items......Page 235 Special Filters......Page 238 Clearing Filters......Page 241 Calculated Fields......Page 242 Hiding Totals......Page 243 Formatting Values......Page 244 Pivot Table Views......Page 245 Slicers......Page 246 Making Data Suitable for Pivot Tables......Page 247 Components of a Chart......Page 251 Preparing Data......Page 252 Types of Charts......Page 253 Creating a Chart......Page 254 Creating a Chart......Page 255 Chart Styles......Page 256 Adding a Chart Title......Page 257 Adding or Moving the Chart Legend......Page 258 Changing a Chart's Type......Page 259 Creating a Chart with Multiple Chart Types......Page 260 Showing Numbers of Different Scale......Page 261 Updating Chart Data......Page 262 Switching Rows and Columns......Page 263 Trendlines......Page 264 Adding a Forecasting Trendline......Page 265 Creating a Stock Chart......Page 266 Pie Chart Issue: Small Slices......Page 267 Create a Bar of Pie Chart......Page 268 Adding Points to a Sparkline......Page 270 Spacing Markers in a Sparkline......Page 271 Saving a Chart as a Template......Page 272 Creating a Chart Using a User-Created Template......Page 273 SmartArt......Page 275 Inserting SmartArt......Page 276 Inserting SmartArt Images......Page 277 Adding and Deleting Shapes......Page 279 Reorder Components......Page 280 Changing an Individual Component......Page 281 Linking a Cell to Smart Art......Page 282 Inserting WordArt......Page 284 Resizing and Cropping Pictures......Page 285 Corrections, Color, and Artistic Effects......Page 287 Arranging Pictures......Page 288 Reducing a Picture's File Size......Page 290 Enabling VBA Security......Page 291 Introduction to the Visual Basic Editor......Page 293 Project Explorer......Page 294 Navigating While Recording......Page 295 Relative References in Macro Recording......Page 296 Avoid the AutoSum Button......Page 298 Filling in the Record Macro Dialog......Page 299 Running a Macro from the Ribbon......Page 301 Running a Macro from the Quick Access Toolbar......Page 302 Running a Macro from a Form Control, Text Box, or Shape......Page 303 User-Defined Functions......Page 304 Structure of a UDF......Page 305 Sharing UDFs......Page 307 Using Select Case to Replace Nested IF......Page 308 C......Page 311 D......Page 313 F......Page 314 L......Page 316 O......Page 317 R......Page 318 S......Page 319 T......Page 320 W......Page 321 Z......Page 322