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.

nexusstc/Using Microsoft Excel 2010/95beed302c87833974ac55a18b51a784.pdf

lgli/Pearson-Using.Microsoft.Excel.2010.RETAiL.EBook-DiGiBook 0-7897-4290-X,0-7897-4290-X 1,978-0-7897-4290-2.pdf

lgrsnf/Pearson-Using.Microsoft.Excel.2010.RETAiL.EBook-DiGiBook 0-7897-4290-X,0-7897-4290-X 1,978-0-7897-4290-2.pdf

zlib/Computers/Applications & Software/Tracy Syrstad, Bill Jelen/Using Microsoft Excel 2010_783119.pdf

Safari books online

Que Corporation; Bill Jelen

Tracy Syrstad, Bill Jelen

Syrstad, Tracy

Que Publishing

Que Pub.; Que

Alpha Books

Hayden

United States, United States of America

Indianapolis, Ind., London, 2010

Using--, Indianapolis, IN, ©2011

Indianapolis, IN, Indiana, 2011

demonoid.me

lg358324

{"edition":"1","isbns":["078974290X","9780789742902"],"last_page":323,"publisher":"Que"}

Includes bibliographical references and index.

MiU

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

2011-03-04