My Excel 2016

Content: Introduction   1What's in This Book   2Guidance for Beginners   2 Chapter 1: Understanding the Microsoft Excel Interface   5Identifying Parts of the Excel Window   6Using the Built-in Help   8Perform a Search   9Making Selections from the Ribbon   10Customizing the Ribbon   10Minimize the Ribbon Size   11Add More Commands to the Ribbon   12Customizing the QAT   15Move the QAT to a New Location   15Add More Commands to the QAT   15Viewing Multiple Sheets at the Same Time   16Arrange Multiple Sheets   17Scroll Two Sheets Side by Side   18Changing the Zoom on a Sheet   19Use Excel's Zoom Controls   19Moving Around on a Sheet   20Keyboard Shortcuts for Quicker Navigation   20Selecting a Range of Cells   21Select a Range Using the Mouse   21 Chapter 2: Working with Workbooks and Templates   25Managing Workbooks   25Create a New Workbook   26Open an Existing Workbook   26Use the Recent Workbooks List   27Save a Workbook   28Close a Workbook   30Using Templates to Quickly Create New Workbooks   30Use Microsoft's Online Templates   31Save a Template   32Open a Locally Saved Template to Enter Data   33Edit the Design of a Locally Saved Template   34Change Personal Templates Location   35Chapter 3: Working with Sheets   37 Adding and Deleting Sheets   37 Add a New Sheet   38 Delete a Sheet   38 Navigating and Selecting Sheets   39 Activate Another Sheet   39 Select Multiple Sheets   40 Moving or Copying Sheets   40 Move or Copy a Sheet in the Same Workbook   41 Move or Copy a Sheet Between Workbooks   42 Renaming a Sheet   43 Change a Sheet's Name   43 Chapter 4: Getting Data onto a Sheet   45 Entering Different Types of Data into a Cell   46 Type Numbers or Text into a Cell   46 Enter Numbers as Text   47 Type Dates and Times into a Cell   48 Undo an Entry   49 Using Lists to Quickly Fill a Range   50 Extend a Series Containing Text   50 Extend a Numerical Series   51 Create Your Own List   53 Using Paste Special   55 Paste Values Only   55 Combine Multiple Paste Special Options   57 Multiply the Range by a Specific Value   60 Use Paste to Merge a Noncontiguous Selection   63 Using Text to Columns to Separate Data in a Single Column   64 Work with Delimited Text   64 Using Data Validation to Limit Data Entry in a Cell   67 Limit User Entry to a Selection from a List   67 Using Web Queries to Get Data onto a Sheet   70 Insert a Web Query   70 Editing Data   72 Modify Cell Data   72 Clearing the Contents of a Cell   73 Clear Only Data from a Cell   73 Clearing an Entire Sheet   74 Clear an Entire Sheet   74Working with Tables   75 Define a Table   75 Add a Total Row to a Table   77 Change the Total Row Function   77 Expand a Table   78 Fixing Numbers Stored as Text   79 Use Convert to Number on Multiple Cells   80 Use Paste Special to Force a Number   81 Spell Checking a Sheet   83 Finding Data on a Sheet   83 Perform a Search   84 Perform a Wildcard Search   86 Replace Data on a Sheet    87 Chapter 5: Selecting and Moving Data on a Sheet   91 Working with Rows and Columns   91 Select a Row or Column   92 Insert a New Row or Column   93 Delete a Row or Column   95 Move Rows or Columns by Dragging   96 Move Rows or Columns by Cutting   97 Copy Rows or Columns   99 Working with Cells   101 Select a Cell Using the Name Box   101 Select Noncontiguous Cells and Ranges   102 Insert Cells   103 Delete Cells   104 Move Cells   105 Chapter 6: Formatting Sheets and Cells   107 Changing the Font Settings of a Cell   108 Select a New Font Typeface   109 Increase and Decrease the Font Size   110 Apply Bold, Italic, and Underline to Text   111 Apply Strikethrough, Superscript, and Subscript   111 Change the Font Color   113 Format a Character or Word in a Cell   114 Format Quickly with the Format Painter   114 Adjusting the Row Height   115 Modify the Row Height by Dragging   116 Modify the Row Height by Entering a Value   117 Use Font Size to Automatically Adjust the Row Height   118Adjusting the Column Width   119 Modify the Column Width by Dragging   119 Modify the Column Width by Entering a Value   120 Aligning Text in a Cell   121 Change Text Alignment   121 Merging Two or More Cells   121 Merge and Center Data   122 Merge Across Columns   123 Unmerge Cells   123 Centering Text Across Multiple Cells   124 Center Text Without Merging   124 Wrapping Text in a Cell to the Next Line   125 Wrap Text in a Cell   125 Reflowing Text in a Paragraph   126 Fit Text to a Specific Range   126 Indenting Cell Contents   127 Indent Data   127 Applying Number Formats   128 Modify the Number Format   128 Change the Format of Negative Numbers   129 Apply a Currency Symbol   130 Format Dates and Times   131 Format as Percentage   132 Format as Text   133 Apply the Special Number Format   134 Adding a Border Around a Range   135 Format a Range with a Thick Outer Border and Thin Inner Lines   136 Add a Colored Border   137 Coloring the Inside of a Cell   138 Apply a Two-Color Gradient to a Cell   139 Chapter 7: Advanced Formatting   143 Creating Custom Number Formats   143 The Four Sections of a Custom Number Format   144 Optional Versus Required Digits   145 Use the Thousands Separator, Color Codes, and Text   146 Line Up Decimals   148 Fill Leading and Trailing Spaces   149 Show More Than 24 Hours in a Time Format   150 Creating Hyperlinks   152 Create a Hyperlink to Another Sheet   152 Link to a Web Page   154Dynamic Cell Formatting with Conditional Formatting   154 Use Icons to Mark Data   155 Highlight the Top 10   157 Highlight Duplicate or Unique Values   159 Create a Custom Rule   161 Clear Conditional Formatting   163 Edit Conditional Formatting   164 Using Cell Styles to Apply Cell Formatting   166 Apply a Style   166 Create a Custom Style   167 Using Themes to Ensure Uniformity in Design   169 Apply a New Theme   169 Create a New Theme   170 Share a Theme   173 Chapter 8: Using Formulas   175 Entering a Formula into a Cell   175 Calculate a Formula   176 View All Formulas on a Sheet   177 Relative Versus Absolute Referencing   178 Lock the Row When Copying a Formula Down   179 Copying Formulas   180 Copy and Paste Formulas   180 Copy by Dragging the Fill Handle   181 Copy Rapidly Down a Column   182 Copy Between Workbooks Without Creating a Link   183 Converting Formulas to Values   184 Paste as Values   184 Select and Drag   185 Using Names to Simplify References   186 Create a Named Cell   186 Use a Name in a Formula   187 Inserting Formulas into Tables   189 Write a Formula in a Table   189 Write Table Formulas Outside the Table    191 Using Array Formulas   193 Enter an Array Formula   193 Delete a Multicell Array Formula   194 Working with Links   195 Control the Prompt   196 Refresh Data   196Change the Source Workbook   197 Break the Link   198 Troubleshooting Formulas   198 Fix ###### in a Cell   199 Understand a Formula Error   199 Use Trace Precedents and Dependents   201 Track Formulas on Other Sheets with Watch Window   203 Use the Evaluate Formula Dialog Box   204 Evaluate with F9   206 Adjusting Calculation Settings   207 Set Calculations to Manual   207 Chapter 9: Using Functions   209 Understanding Functions   209 Look Up Functions   210 Use the Function Arguments Dialog Box   211 Enter Functions Using Formula Tips   214 Using the AutoSum Button   214 Calculate a Single Range   215 Sum Rows and Columns at the Same Time   216 Quick Calculations   217 Calculate Results Quickly   217 Using Quick Analysis Functions   219 Using Lookup Functions   219 Use CHOOSE to Return the nth Value from a List   219 Use VLOOKUP to Return a Value from a Table   221 Use INDEX and MATCH to Return a Value from the Left   223 Using SUMIFS to Sum Based on Multiple Criteria   225 Sum a Column Based on Two Criteria   225 Using IF Statements   228 Compare Two Values   228 Hiding Errors with IFERROR   229 Hide a #DIV/0! Error   230 Understanding Dates and Times   231 Return a New Date X Workdays from Date   231 Calculate the Number of Days Between Dates   233 Using Goal Seek   234 Calculate the Best Payment   234 Using the Function Arguments Dialog Box to Troubleshoot Formulas   235 Narrow Down a Formula Error   236Chapter 10: Sorting Data   239 Using the Sort Dialog Box   239 Sort by Values   240 Sort by Color or Icon   242 Doing Quick Sorts   244 Quick Sort a Single Column   244 Quick Sort Multiple Columns   245 Performing Custom Sorts   246 Perform a Random Sort   246 Sort with a Custom Sequence   247 Rearranging Columns   249 Sort Columns with the Sort Dialog Box   249 Fixing Sort Problems   251 Chapter 11: Filtering and Consolidating Data   253 Using the Filter Tool   253 Apply a Filter   254 Clear a Filter   256 Reapply a Filter   257 Turn the Filter On for One Column   257 Filtering Grouped Dates   258 Turn On Grouped Dates   258 Filter by Date   259 Using Special Filters   260 Filter for Items that Include a Specific Term   260 Filter for Values Within a Range   261 Filter for the Top 25 Items   262 Filter Dates by Quarter   263 Filtering by Color or Icon   263 Filtering by Selection    264 Allowing Users to Filter a Protected Sheet   264 Filter a Protected Sheet   265 Using the Advanced Filter   266 Reorganize Columns   266 Create a List of Unique Items   268 Filter Records Using Criteria   269 Use Formulas as Criteria   272 Removing Duplicates   273 Delete Duplicate Rows   273 Consolidating Data   274 Merge Values from Two Datasets   274 Merge Data Based on Matching Labels   276Chapter 12: Distributing and Printing a Workbook   279 Using Cell Comments to Add Notes to Cells   280 Insert a New Cell Comment   280 Edit a Cell Comment    281 Format a Cell Comment   282 Insert an Image into a Cell Comment   284 Resize a Cell Comment   287 Show and Hide Cell Comments   287 Delete a Cell Comment   288 Allowing Multiple Users to Edit a Workbook at the Same Time   289 Share a Workbook   289 Hiding and Unhiding Sheets   291 Hide a Sheet   291 Unhide a Sheet   292 Using Freeze Panes   292 Lock the Top Row   293 Lock Multiple Rows and Columns   294 Configuring the Page Setup   295 Set Paper Size, Margins, and Orientation   295 Set the Print Area   296 Set Page Breaks   .297 Scale the Data to Fit a Printed Page   299 Repeat Specific Rows on Each Printed Page   300 Creating a Custom Header or Footer   300 Add an Image to the Header or Footer   301 Add Page Numbering to the Header and Footer    303 Printing Sheets   304 Configure Print Options   304 Protecting a Workbook from Unwanted Changes   306 Set File-Level Protection   306 Set Workbook-Level Protection   307 Protecting the Data on a Sheet   308 Protect a Sheet   308 Unlock Cells   309 Allow Users to Edit Specific Ranges   310 Preventing Changes by Marking a File as Final   312 Mark a Workbook as Final   312 Sharing Files Between Excel Versions   313 Check Version Compatibility   313Recovering Lost Changes    314 Configure Backups   314 Recover a Backup   315 Recover Unsaved Files   316 Sending an Excel File as an Attachment   316 Email a Workbook   317 Sharing a File Online   318 Save to OneDrive   318 Chapter 13: Inserting Subtotals and Grouping Data   321 Using the SUBTOTAL Function   321 Calculate Visible Rows   322 Summarizing Data Using the Subtotal Tool   323 Apply a Subtotal   323 Expand and Collapse Subtotals   325 Remove Subtotals or Groups   325 Sort Subtotals   326 Copying the Subtotals to a New Location   327 Copy Subtotals   328 Applying Different Subtotal Function Types   329 Create Multiple Subtotal Results on Multiple Rows   329 Combine Multiple Subtotal Results to One Row   331 Adding Space Between Subtotaled Groups   332 Separate Subtotaled Groups for Print   333 Separate Subtotaled Groups for Distributed Files   334 Grouping and Outlining Rows and Columns   336 Apply Auto Outline   336 Group Data Manually   337 Chapter 14 :Creating Charts and Sparklines   341 Adding a Chart   341 Add a Chart with the Quick Analysis Tool   342 Preview All Charts   343 Switch Rows and Columns   344 Apply Chart Styles or Colors   345 Apply Chart Layouts   346 Resizing or Moving a Chart   346 Resize a Chart   346 Move to a New Location on the Same Sheet   347 Relocate to Another Sheet   347Editing Chart Elements   348 Use the Format Task Pane   348 Edit the Chart or Axis Titles   349 Change the Display Units in an Axis   351 Customize a Series Color    352 Changing an Existing Chart's Type   353 Change the Chart Type   353 Creating a Chart with Multiple Chart Types   354 Insert a Multiple Type Chart   354 Add a Secondary Axis   356 Updating Chart Data   356 Change the Data Source   357 Adding Special Charts   358 Create a Stock Chart    358 Create a Bubble Chart   359 Pie Chart Issue: Small Slices   360 Rotate the Pie   360 Create a Bar of Pie Chart   361 Using a User-Created Template   363 Save a Chart Template   363 Use a Chart Template   364 Adding Sparklines to Data   364 Insert a Sparkline   365 Emphasize Points on a Sparkline   365 Space Markers by Date   366 Delete Sparklines   367 Chapter 15: Summarizing Data with PivotTables   369 Creating a PivotTable   370 Use the Quick Analysis Tool   371 Create a PivotTable from Scratch   372 Change the Calculation Type of a Field Value   375 Format Values   376 Changing the PivotTable Layout   377 Choose a New Layout   378 PivotTable Sorting   378 Click and Drag   378 Use Quick Sort   379 Expanding and Collapsing Fields   380 Expand and Collapse a Field   380Grouping Dates   381 Group by Week   381 Group by Month and Year   382 Filtering Data in a PivotTable   383 Filter for Listed Items   383 Clear a Filter   384 Creating a Calculated Field   384 Add a Calculated Field   384 Hiding Totals   385 Hide Totals   386 Hide Subtotals   386 Viewing the Records Used to Calculate a Value   387 Unlinking PivotTables   388 Unlink a PivotTable Report   388 Refreshing the PivotTable    389 Refresh on Open   390 Refresh After Adding New Data   390 Refresh After Editing the Data Source   391 Working with Slicers   392 Create a Slicer    392 Use a Slicer   93 Chapter 16: Inserting SmartArt, WordArt, and Pictures   395 Working with SmartArt   395 Insert a SmartArt Graphic   396 Insert Images into SmartArt   398 Move and Resize SmartArt   399 Reorder Placeholders   401 Change the Layout   402 Change an Individual Shape   402 Working with WordArt   403 Insert WordArt   403 Inserting Pictures   404 Insert a Picture   404 Resize and Crop a Picture   405 Apply Corrections, Color, and Artistic Effects   407 Reduce a File's Size   409Chapter 17: Introducing the Excel Web App   411 Acquiring a Microsoft Account   411 Create an Account   412 Uploading a Workbook   413 Upload Through OneDrive   413 Save from Excel   414 Delete a File from OneDrive   415 Opening a Workbook Online or Locally   416 Open a Workbook   417 Download a Workbook   417 Creating a New Workbook Online   418 Create a Workbook   419 Rename the New Workbook   419 Sharing a Folder or Workbook   420 Create a View-Only Folder   420 Remove Sharing   422 Edit Simultaneously   424 Configuring Browser View Options   425 Create an Online Form   425 Designing a Survey Through the Web App   428 Create a Survey   428 Index   430