INTRODUCTION 1
Who Should Read This Book 3
How This Book Is Organized 3
Downloading the Example Files 3
Conventions Used In This Book 4
Special Elements 4
1 Understanding the Microsoft Excel Interface 5
Taking a Closer Look at the Excel Window 6
Customizing the Excel Window 12
Moving Around and Making Selections on a Sheet 19
Installing Optional Components 21
2 Working with Workbooks, Sheets, Rows, Columns, and Cells 27
Managing Workbooks 28
Using Templates to Quickly Create New Workbooks 33
Working with Sheets and Tabs 36
Working with Rows and Columns 40
Working with Cells 43
3 Getting Data onto a Sheet 47
Types of Data You Enter into Excel 48
Entering Different Types of Data into a Cell 48
Controlling the Next Cell Selection 50
Using Copy, Cut, Paste, Paste Special to Enter Data 52
Using Text to Columns to Separate Data in a Single Column 54
Inserting Symbols and Equations into a Cell 58
Using Web Queries to Get Data onto a Sheet 59
Using Series to Quickly Fill a Range 60
Editing Data 62
Working with Tables 64
Fixing Numbers Stored as Text 67
Spellchecking Your Sheet 69
Finding Data on Your Sheet 69
Using Data Validation to Limit Data Entry in a Cell 71
4 Formatting Sheets and Cells 75
Adjusting Row Heights and Column Widths 76
Changing the Font Settings of a Cell 77
Aligning Text in a Cell 79
Reflowing Text in a Paragraph 83
Applying Number Formats with Format Cells 85
Creating a Custom Format 88
When Cell Formatting Doesn't Seem to Be Working Right 95
The Number Group on the Ribbon 95
Adding a Border Around a Range 96
Coloring the Inside of a Cell 98
Creating Hyperlinks 99
Quick Formatting with the Format Painter 101
Dynamic Cell Formatting with Conditional Formatting 101
Using Cell Styles to Quickly Apply Formatting 109
Using Themes to Ensure Uniformity in Design 109
5 Using Formulas 113
The Importance of Laying Out Data Properly 114
Adjusting Calculation Settings 114
Viewing Formulas Versus Values 115
Entering a Formula into a Cell 116
Three Ways of Entering a Formula's Cell References 118
Relative Versus Absolute Formulas 118
Using a Cell on Another Sheet in a Formula 119
Using R1C1 Notation to Reference Cells 119
Using F4 to Change the Cell Referencing 121
Mathematical Operators 122
Copying a Formula to Another Cell 123
Using Names to Simplify References 125
Inserting Formulas into Tables 128
Using Table Names in Table Formulas 129
Writing Table Formulas Outside the Table 130
Using Array Formulas 132
Converting Formulas to Values 136
Troubleshooting Formulas 137
6 Using Functions 143
Breaking Down a Function 144
Finding Functions 144
Entering Functions Using the Function Arguments Dialog Box 146
Entering Functions Using In-Cell Tips 147
Using the AutoSum Button 148
Using Lookup Functions to Match a Value and Return Another 152
SUMIFS 159
SUMPRODUCT 160
Logical Functions 162
Nested IF Statements 164
IFERROR 165
Date and Time Functions 166
Troubleshooting Dates and Times Stored as Strings 172
Goal Seek 173
Using the Function Arguments Dialog Box to Troubleshoot Formulas 174
7 Sorting Data 177
Preparing Data 178
Opening the Sort Dialog Box 178
Sorting by Values 178
Sorting by Color or Icon 180
Using the Quick Sort Buttons 182
Quick Sorting Multiple Columns 182
Randomly Sorting Data 183
Sorting with a Custom Sequence 184
Rearranging Columns Using the Sort Dialog Box 186
Rearranging Columns Using the Mouse 186
Fixing Sort Problems 187
8 Filtering and Consolidating Data 189
Preparing Data 190
Applying a Filter to a Data Set 190
Clearing a Filter 191
Reapplying a Filter 192
Turning Filtering On for One Column 192
Filtering for Listed Items 192
Filtering the Grouped Dates Listing 193
Using the Search Function to Filter for or Exclude Items 195
Using the Search Function on Grouped Dates 196
Using Text, Number, and Date Special Filters 198
Filtering by Color or Icon 200
Filtering by Selection 200
Allowing Filtering on a Protected Sheet 201
Using the Advanced Filter Option 202
Removing Duplicates from a Data Set 207
Consolidating Data 208
9 Distributing and Printing a Workbook 213
Using Cell Comments to Add Notes to Cells 214
Allowing Multiple Users to Edit a Workbook at the Same Time 216
Hiding and Unhiding Sheets 217
Locking Rows or Columns in Place 218
Creating Custom Views of Your Data 219
Configuring the Page Setup .220
Creating a Custom Header or Footer 222
Using Page Break Preview to Set Page Breaks 225
Printing Sheets 226
Protecting Your Workbook from Unwanted Changes 227
Protecting the Data on a Sheet 228
Preventing Changes by Marking a File as Final 230
Restricting Access Using IRM 230
Certifying a Workbook with a Digital Signature 230
Sharing Files Between Excel Versions 231
Removing Hidden or Confidential Information 231
Recovering Lost Changes 232
Sending an Excel File as an Attachment 233
Sharing a File Online 234
10 Subtotals and Grouping 235
Using the SUBTOTAL Function 236
Summarizing Data Using the Subtotal Tool 237
Copying the Subtotals to a New Location 240
Formatting the Subtotals 241
Applying Different Subtotal Function Types 241
Combining Multiple Subtotal Results to One Row 243
Subtotaling by Multiple Columns 245
Sorting Subtotals 246
Adding Space Between Subtotaled Groups 246
Grouping and Outlining Rows and Columns 249
11 Creating Charts and Sparklines 253
Preparing Data 254
Elements of a Chart 254
Types of Charts 256
Adding a Chart to a Sheet 258
Adding, Removing, and Formatting Chart Elements 262
Editing and Formatting a Chart Title 262
Changing the Display Units in an Axis 263
Applying Chart Styles and Colors 264
Applying Chart Layouts 265
Moving or Resizing a Chart 265
Switching Rows and Columns 266
Changing an Existing Chart's Type 266
Creating a Chart with Multiple Chart Types 266
Updating Chart Data 268
Creating Stock Charts 269
Creating Bubble Charts 269
Pie Chart Issue: Small Slices 270
Adding Sparklines to Data 272
Creating a Chart Using a User-Created Template 275
12 PivotTables and Slicers 277
Preparing Data for Use in a PivotTable 278
PivotTable Limitations 279
PivotTable Compatibility 279
PivotTable Field List 280
Creating a PivotTable 281
Changing How a PivotTable Appears on a Sheet 286
PivotTable Sorting 287
Expanding and Collapsing Fields 289
Viewing the Records Used to Calculate a Value 290
Grouping Dates 290
Filtering Data in a PivotTable 292
Creating a Calculated Field 296
Hiding Totals 298
Formatting Values 298
Slicers 299
13 Using Power View to Create Reports 301
Power View Requirements 302
Creating Reports 302
Inserting and Formatting a Report Title 305
Changing Data Visualizations 305
Combining Multiple Visualizations 309
Changing Colors 310
Sorting 311
Filtering 312
Sharing Power View Reports 315
14 Inserting SmartArt, WordArt, and Pictures 317
Working with SmartArt 318
Inserting WordArt 325
Inserting Pictures 326
15 An Introduction to Using Macros and UDFs 333
Enabling VBA Security 334
Developer Tab 335
Introduction to the Visual Basic Editor 336
Understanding How the Macro Recorder Works 338
Recording a Macro 342
Running a Macro 343
User-Defined Functions 347
16 Introducing the Excel Web App 355
Requirements 356
Acquiring a Microsoft Account 356
Uploading and Downloading Workbooks 358
Opening a Workbook 360
Creating a New Workbook Online 361
Saving a File to Your Local Drive 362
Sharing a Folder or Workbook 362
Interacting with a Sheet Online 366
Configuring Browser View Options 367
Setting Up a Survey 369
Index 373
