Microsoft Excel 2016 – Intermediate

Code INF1616

Table of Contents

Filling Data

Understanding Filling

Filling a Series

Filling a Growth Series

Filling a Series Backwards

Filling Using Options

Creating a Custom Fill List

Modifying a Custom Fill List

Deleting a Custom Fill List

Extracting With Flash Fill

More Complex Flash Fill Extractions

Extracting Dates and Numbers

Worksheet Techniques

Inserting and Deleting Worksheets

Copying a Worksheet

Renaming a Worksheet

Moving a Worksheet

Hiding a Worksheet

Unhiding a Worksheet

Copying a Sheet to Another Workbook

Changing Worksheet Tab Colours

Grouping Worksheets

Hiding Rows and Columns

Unhiding Rows and Columns

Freezing Rows and Columns

Splitting Windows

Page Setup

Strategies for Printing Worksheets

Understanding Page Layout

Using Built in Margins

Setting Custom Margins

Changing Margins by Dragging

Centring on a Page

Changing Orientation

Specifying the Paper Size

Setting the Print Area

Clearing the Print Area

Inserting Page Breaks

Using Page Break Preview

Removing Page Breaks

Setting a Background

Clearing the Background

Settings Rows as Repeating Print Titles

Clearing Print Titles

Printing Gridlines

Printing Headings

Scaling to a Percentage

Fit to a Specific Number of Pages

Applying Borders

Understanding Borders

Applying a Border to a Cell

Applying a Border to a Range

Applying a Bottom Border

Applying Top and Bottom Borders

Removing Borders

The More Borders Command

Using the More Borders Command

Drawing Borders

Changing Column Colour Schemes

Changing the Colour of a Series

Changing Line Chart Colours

Using Shape Effects

Colouring the Chart Background

Understanding the Format Pane

Using the Format Pane

Exploding Pie Slices

Changing Individual Bar Colours

Formatting Text

Formatting With WordArt

Changing WordArt Fill

Changing WordArt Effects

Drawing a Border Grid

Erasing Borders

Formatting the Drawing Pencil

Essential Functions

Key Worksheet Functions

Using IF With Text

Using IF With Numbers

Nesting IF Functions

The CHOOSE Function

The LOOKUP 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

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

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


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

Working With Icon Sets

Understanding Sparklines

Creating Sparklines

Editing Sparklines

Goal Seeking

Understanding Goal Seeking

Using Goal Seek

The Quick Analysis Tools

Understanding Quick Analysis

Quick Formatting

Quick Charting

Quick Totals

Quick Sparklines

Quick Tables

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

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

Chart Object Formatting

Understanding Chart Formatting

Selecting Chart Objects

Using Shape Styles

Download this outline as a PDF.

Intermediate Microsoft Excel 2016