Syrstad: Excel 2013 Absolute Beginner's Guide

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