Microsoft Excel 2013 – Intermediate

Code INF 1316

Table of Contents

Fill Techniques

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

Moving 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

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

Financial Functions

Understanding Financial Functions

Using PMT

Using FV

Using NPV

Using PV

Using RATE

Using EFFECT

Using NOMINAL

Date and Time Functions

Understanding Date And Time Functions

Using NOW

Using HOUR And MINUTE

Using TODAY

Calculating Future Dates

Using DATE

Using Calendar Functions

Using WEEKDAY

Using WEEKNUM

Using WORKDAY

Using EOMONTH

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

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

Controls

Adding a Combo Box Control

Changing Control Properties

Using the Cell Link to Display the Selection

Adding a List Box Control

Adding a Scroll Bar Control

Adding a Spin Button Control

Adding Option Button Controls

Adding a Group Box Control

Adding a Check Box Control

Protecting a Worksheet With Controls

Download this outline as a PDF.

Advanced Microsoft Excel 2016