My Excel 2016 by Syrstad, Tracy -- Read -- Imperial Library of Trantor

Select a Row or Column Insert a New Row or Column Delete a Row or Column Move Rows or Columns by Dragging Move Rows or Columns by Cutting Copy Rows or Columns

Working with Cells

Select a Cell Using the Name Box Select Noncontiguous Cells and Ranges Insert Cells Delete Cells Move Cells

6 Formatting Sheets and Cells

Changing the Font Settings of a Cell

Select a New Font Typeface Increase and Decrease the Font Size Apply Bold, Italic, and Underline to Text Apply Strikethrough, Superscript, and Subscript Change the Font Color Format a Character or Word in a Cell Format Quickly with the Format Painter

Adjusting the Row Height

Modify the Row Height by Dragging Modify the Row Height by Entering a Value Use Font Size to Automatically Adjust the Row Height

Adjusting the Column Width

Modify the Column Width by Dragging Modify the Column Width by Entering a Value

Aligning Text in a Cell

Change Text Alignment

Merging Two or More Cells

Merge and Center Data Merge Across Columns Unmerge Cells

Centering Text Across Multiple Cells

Center Text Without Merging

Wrapping Text in a Cell to the Next Line

Wrap Text in a Cell

Reflowing Text in a Paragraph

Fit Text to a Specific Range

Indenting Cell Contents

Indent Data

Applying Number Formats

Modify the Number Format Change the Format of Negative Numbers Apply a Currency Symbol Format Dates and Times Format as Percentage Format as Text Apply the Special Number Format

Adding a Border Around a Range

Format a Range with a Thick Outer Border and Thin Inner Lines Add a Colored Border

Coloring the Inside of a Cell

Apply a Two-Color Gradient to a Cell

7 Advanced Formatting

Creating Custom Number Formats

The Four Sections of a Custom Number Format Optional Versus Required Digits Use the Thousands Separator, Color Codes, and Text Line Up Decimals Fill Leading and Trailing Spaces Show More Than 24 Hours in a Time Format

Creating Hyperlinks

Create a Hyperlink to Another Sheet Link to a Web Page

Dynamic Cell Formatting with Conditional Formatting

Use Icons to Mark Data Highlight the Top 10 Highlight Duplicate or Unique Values Create a Custom Rule Clear Conditional Formatting Edit Conditional Formatting

Using Cell Styles to Apply Cell Formatting

Apply a Style Create a Custom Style

Using Themes to Ensure Uniformity in Design

Apply a New Theme Create a New Theme Share a Theme

8 Using Formulas

Entering a Formula into a Cell

Calculate a Formula View All Formulas on a Sheet

Relative Versus Absolute Referencing

Lock the Row When Copying a Formula Down

Copying Formulas

Copy and Paste Formulas Copy by Dragging the Fill Handle Copy Rapidly Down a Column Copy Between Workbooks Without Creating a Link

Converting Formulas to Values

Paste as Values Select and Drag

Using Names to Simplify References

Create a Named Cell Use a Name in a Formula

Inserting Formulas into Tables

Write a Formula in a Table Write Table Formulas Outside the Table

Using Array Formulas

Enter an Array Formula Delete a Multicell Array Formula

Working with Links

Control the Prompt Refresh Data Change the Source Workbook Break the Link

Troubleshooting Formulas

Fix ###### in a Cell Understand a Formula Error Use Trace Precedents and Dependents Track Formulas on Other Sheets with Watch Window Use the Evaluate Formula Dialog Box Evaluate with F9

Adjusting Calculation Settings

Set Calculations to Manual

9 Using Functions

Understanding Functions

Look Up Functions Use the Function Arguments Dialog Box Enter Functions Using Formula Tips

Using the AutoSum Button

Calculate a Single Range Sum Rows and Columns at the Same Time

Quick Calculations

Calculate Results Quickly Using Quick Analysis Functions

Using Lookup Functions

Use CHOOSE to Return the nth Value from a List Use VLOOKUP to Return a Value from a Table Use INDEX and MATCH to Return a Value from the Left

Using SUMIFS to Sum Based on Multiple Criteria

Sum a Column Based on Two Criteria

Using IF Statements

Compare Two Values

Hiding Errors with IFERROR

Hide a #DIV/0! Error

Understanding Dates and Times

Return a New Date X Workdays from Date Calculate the Number of Days Between Dates

Using Goal Seek

Calculate the Best Payment

Using the Function Arguments Dialog Box to Troubleshoot Formulas

Narrow Down a Formula Error

Using the Sort Dialog Box

Sort by Values Sort by Color or Icon

Doing Quick Sorts

Quick Sort a Single Column Quick Sort Multiple Columns

Performing Custom Sorts

Perform a Random Sort Sort with a Custom Sequence

Rearranging Columns

Sort Columns with the Sort Dialog Box

Fixing Sort Problems Using the Filter Tool

Apply a Filter Clear a Filter Reapply a Filter Turn the Filter On for One Column

Filtering Grouped Dates

Turn On Grouped Dates Filter by Date

Using Special Filters

Filter for Items that Include a Specific Term Filter for Values Within a Range Filter for the Top 25 Items Filter Dates by Quarter Filtering by Color or Icon Filtering by Selection

Allowing Users to Filter a Protected Sheet

Filter a Protected Sheet

Using the Advanced Filter

Reorganize Columns Create a List of Unique Items Filter Records Using Criteria Use Formulas as Criteria

Removing Duplicates

Delete Duplicate Rows

Consolidating Data

Merge Values from Two Datasets Merge Data Based on Matching Labels

Using Cell Comments to Add Notes to Cells

Insert a New Cell Comment Edit a Cell Comment Format a Cell Comment Insert an Image into a Cell Comment Resize a Cell Comment Show and Hide Cell Comments Delete a Cell Comment

Allowing Multiple Users to Edit a Workbook at the Same Time

Share a Workbook

Hiding and Unhiding Sheets

Hide a Sheet Unhide a Sheet

Using Freeze Panes

Lock the Top Row Lock Multiple Rows and Columns

Configuring the Page Setup

Set Paper Size, Margins, and Orientation Set the Print Area Set Page Breaks Scale the Data to Fit a Printed Page Repeat Specific Rows on Each Printed Page

Creating a Custom Header or Footer

Add an Image to the Header or Footer Add Page Numbering to the Header and Footer

Printing Sheets

Configure Print Options

Protecting a Workbook from Unwanted Changes

Set File-Level Protection Set Workbook-Level Protection

Protecting the Data on a Sheet

Protect a Sheet Unlock Cells Allow Users to Edit Specific Ranges

Preventing Changes by Marking a File as Final

Mark a Workbook as Final

Sharing Files Between Excel Versions

Check Version Compatibility

Recovering Lost Changes

Configure Backups Recover a Backup Recover Unsaved Files

Sending an Excel File as an Attachment

Email a Workbook

Sharing a File Online

Save to OneDrive

Using the SUBTOTAL Function

Calculate Visible Rows

Summarizing Data Using the Subtotal Tool

Apply a Subtotal Expand and Collapse Subtotals Remove Subtotals or Groups Sort Subtotals

Copying the Subtotals to a New Location

Copy Subtotals

Applying Different Subtotal Function Types

Create Multiple Subtotal Results on Multiple Rows Combine Multiple Subtotal Results to One Row

Adding Space Between Subtotaled Groups

Separate Subtotaled Groups for Print Separate Subtotaled Groups for Distributed Files

Grouping and Outlining Rows and Columns

Apply Auto Outline Group Data Manually

Adding a Chart

Add a Chart with the Quick Analysis Tool Preview All Charts Switch Rows and Columns Apply Chart Styles or Colors Apply Chart Layouts

Resizing or Moving a Chart

Resize a Chart Move to a New Location on the Same Sheet Relocate to Another Sheet

Editing Chart Elements

Use the Format Task Pane Edit the Chart or Axis Titles Change the Display Units in an Axis Customize a Series Color

Changing an Existing Chart’s Type

Change the Chart Type

Creating a Chart with Multiple Chart Types

Insert a Multiple Type Chart Add a Secondary Axis

Updating Chart Data

Change the Data Source

Adding Special Charts

Create a Stock Chart Create a Bubble Chart

Pie Chart Issue: Small Slices

Rotate the Pie Create a Bar of Pie Chart

Using a User-Created Template

Save a Chart Template Use a Chart Template

Adding Sparklines to Data

Insert a Sparkline Emphasize Points on a Sparkline Space Markers by Date Delete Sparklines

Creating a PivotTable

Use the Quick Analysis Tool Create a PivotTable from Scratch Change the Calculation Type of a Field Value Format Values

Changing the PivotTable Layout

Choose a New Layout

PivotTable Sorting

Click and Drag Use Quick Sort

Expanding and Collapsing Fields

Expand and Collapse a Field

Grouping Dates

Group by Week Group by Month and Year

Filtering Data in a PivotTable

Filter for Listed Items Clear a Filter

Creating a Calculated Field

Add a Calculated Field

Hiding Totals

Hide Totals Hide Subtotals

Viewing the Records Used to Calculate a Value Unlinking PivotTables

Unlink a PivotTable Report

Refreshing the PivotTable

Refresh on Open Refresh After Adding New Data Refresh After Editing the Data Source

Working with Slicers

Create a Slicer Use a Slicer

Working with SmartArt

Insert a SmartArt Graphic Insert Images into SmartArt Move and Resize SmartArt Reorder Placeholders Change the Layout Change an Individual Shape

Working with WordArt

Insert WordArt

Inserting Pictures

Insert a Picture Resize and Crop a Picture Apply Corrections, Color, and Artistic Effects Reduce a File’s Size

Acquiring a Microsoft Account

Create an Account

Uploading a Workbook

Upload Through OneDrive Save from Excel Delete a File from OneDrive

Opening a Workbook Online or Locally

Open a Workbook Download a Workbook

Creating a New Workbook Online

Create a Workbook Rename the New Workbook

Sharing a Folder or Workbook

Create a View-Only Folder Remove Sharing Edit Simultaneously

Configuring Browser View Options

Create an Online Form

Designing a Survey Through the Web App

Create a Survey

10 Sorting Data 11 Filtering and Consolidating Data 12 Distributing and Printing a Workbook 13 Inserting Subtotals and Grouping Data 14 Creating Charts and Sparklines 15 Summarizing Data with PivotTables 16 Inserting SmartArt, WordArt, and Pictures 17 Introducing the Excel Web App Index

← Prev
Back
Next →

← Prev
Back
Next →