Microsoft Excel - Intermediate

Course subjects

Setting Excel Options

• Understanding Excel Options

• Personalising Excel

• Setting the Default Font

• Setting Formula Options

• Understanding Save Options

• Setting Save Options

• Setting the Default File Location

• Setting Advanced Options

Formula Referencing

• Absolute Versus Relative

• Referencing Relative Formulas

• Problems With Relative Formulas

• Creating Absolute References

• Creating Mixed References

Filling Data

• Understanding Filling

• Extracting With Flash Fill

• More Complex Flash Fill Extractions

• Extracting Dates and Numbers

Defined Names

• Understanding Defined Names

• Defining Names From Worksheet Labels

• Using Names in Typed Formulas

• Applying Names to Existing Formulas

• Creating Names Using the Name Box

• Using Names to Select Ranges

• Pasting Defined Names Into Formulas

• Defining Names for Constant Values

• Creating Names From a Selection

• Scoping Names to a Worksheet

• Using the Name Manager

• Documenting Defined Names

Essential Functions

• Worksheet Functions

• Using IF With Text

• Using IF With Numbers

• Nesting IF Functions

• The VLOOKUP Function

• Using Counting Functions

• The ROUND Function

• Rounding Up and Rounding Down

• Manipulative Functions

• The MOD Function

• The TODAY Function

• The NOW Function

• The DATE Function

• The PMT Function

Complex Formulas

• Scoping a Formula

• Long-Hand Formulas

• Preparing for Complex Formulas

• Creating the Base Formula

• Adding More Operations

• Editing a Complex Formula

• Adding More Complexity

• Copying Nested Functions

• Switching to Manual Recalculation

• Pasting Values From Formulas

• Documenting Formulas

Number Formatting Techniques

• Applying Alternate Currencies

• Applying Alternate Date Formats

• Formatting Clock Time

• Formatting Calculated Time

• Understanding Number Formatting

• Understanding Format Codes

• Creating Descriptive Custom Formats

• Custom Formatting Large Numbers

• Custom Formatting for Fractions

• Padding Numbers Using Custom Formatting

• Aligning Numbers Using Custom Formats

• Customising the Display of Negative Values

Conditional Formatting

• Understanding Conditional Formatting

• Formatting Cells Containing Values

• Clearing Conditional Formatting

• More Cell Formatting Options

• Top Ten Items

• More Top and Bottom Formatting Options

• Working With Data Bars

• Working With Colour Scales

Conditional Formatting (cont.)

• Working With Icon Sets

• Understanding Sparklines

• Creating Sparklines

• Editing Sparklines

• Creating Custom Rules

• The Conditional Formatting Rules Manager

• Managing Rules

• Clearing Rules

Validating Data

• Understanding Data Validation

• Creating a Number Range Validation

• Testing a Validation

• Creating an Input Message

• Creating an Error Message

• Creating a Drop Down List

• Using Formulas as Validation Criteria

• Circling Invalid Data

• Removing Invalid Circles

• Copying Validation Settings

Data Linking

• Understanding Data Linking

• Linking Between Worksheets

• Linking Between Workbooks

• Updating Links Between Workbooks

• Using Names to Link Between Workbooks

Goal Seeking

• Understanding Goal Seeking

• Using Goal Seek

Worksheet Tables

• Understanding Tables

• Creating a Table From Scratch

• Working With Table Styles

• Inserting Table Columns

• Removing Table Columns

• Converting a Table to a Range

• Creating a Table From Data

• Inserting or Deleting Table Records

• Removing Duplicates

• Sorting Tables

• Filtering Tables

• Renaming a Table

• Splitting a Table

• Deleting a Table

PivotTables

• Understanding Pivot Tables

• Recommended Pivot Tables

• Creating Your Own PivotTable

• Defining the PivotTable Structure

• Filtering a PivotTable

• Clearing a Report Filter

• Switching PivotTable Fields

• Formatting a PivotTable

Understanding Slicers

• Creating Slicers

• Inserting a Timeline Filter

Chart Elements

• Understanding Chart Elements

• Adding a Chart Title

• Adding Axes Titles

• Repositioning the Legend

• Showing Data Labels

• Showing Gridlines

• Formatting the Chart Area

• Adding a Trendline

• Adding Error Bars

• Adding a Data Table

GoTo Special

• Finding Constants

• Finding Formulas

• Finding Blanks

Supplementary Content

• SUMIF Functions

• Special Pasting

• Finding and Replacing

• Text Functions

• Financial Functions

• Grouping and Outlining

• Summarising and Subtotalling

• Chart Object Formatting