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.