Excel 2016 Resources

Updated 2017.10.15

Spreadsheet objectives

Database objectives

Spreadsheet Design Guidelines

Web

Videos Watch videos in full screen and HD (1040)

1. Basics

Changing Excel's defaults

Changing Excel's Defaults

Cursor movement

Cursor Movement

Cursor Movement & Shapes (4:55)

Data entry and editing

Data Entry

Data Entry and Editing (6:22)

Editing

Editing

Font formatting

Formatting Fonts

Formatting Fonts (8:38)

Alignment

Formatting Alignment

Formatting Alignment (7:48)

Formatting numbers

Formatting Numbers

Formatting Numbers (6:22)

File commands

File/Print/Layout Commands

File/Print/Layout Commands (11:34)

2. Relative Cell References and Auto-fill

Autofill

Autofill (5:50)

Toolbar functions:

Toolbar functions

Toolbar functions, relative cell references (7:52)

Relative cell references

Relative references

3. Absolute and Mixed Cell References

Absolute cell references

Absolute cell references

Absolute cell references (6:42)

Mixed cell references

Mixed cell references

Mixed cell references (12.23),

Mixed AND absolute cell references

4. Database 1: Sorting and Auto-filtering

Tables

Tables

Creating a table

Sorting

Sorting

Single-key sort (4:47)

Multiple-key sort (5:40)

AutoFiltering

Autofiltering

Filtering on text fields (6:17)

Filtering on numeric fields (6:51)

5. Charts

Bad Examples

Bad chart examples

Bad examples (8:39)

Good Examples

Good chart examples

Good examples (5:50)

Creating a chart

Charts overview

Creating a chart (6:18)

Formatting a chart

Formatting a chart (7:44)

Line charts

Line charts (6:57)

Pie charts

Pie charts, (6:08)

XY (Scatter) charts

XY charts (4:57)

Sparklines

Sparklines

6. Making Decisions, part 1: Logical Tests and Logical Functions

Logical tests

Logical Tests (6:24)

IF

IF

IF, part 1     IF, part 2

AND

AND

AND (9:15)

OR

OR

OR (7:57)

Nested IF

Nested IF (9:05)

7. Database 2: Pivot Tables

Pivot Tables

Pivot Tables

Lists

Lists (9:57)

Tables

Tables (4:57)

Sorting

Sorting (4:23)

Filtering

Filtering (2:47)

Field settings

Field Settings (3:46)

8. 3D Workbooks

3D workbooks

3D Workbooks

Formatting 3D workbooks

Formatting 3D workbooks (6:33)

3D formulas

3D Formulas (5:49)

9. Making Decisions, part 2: VLOOKUP and Conditional Formatting

Conditional formatting

Conditional formatting

Conditional formatting (6:47)

VLOOKUP

VLOOKUP

VLOOKUP, Part 1 (12:09)

VLOOKUP, Part 2 (6:53)

COUNTIF, SUMIF, Nested IF

10. Database 3: Advanced Filters and Functions

Advanced Filters

Advanced filters

Advanced Filters, part 1 (3:32)

Advanced Filters, part 2 (8:14)

Database functions

Database functions

DSUM

DSUM (11:48)

DAVERAGE

DAVERAGE (4:32)

DCOUNT, DCOUNTA

DCOUNT and DCOUNTA (5:22)

DMIN

DMIN (5:24)

DMAX

 

DMAX

11. Dates and Times

Dates

Date Serial Numbers (9:17)

Date functions

Date functions

Date Functions: TODAY, NOW (9:17)

MONTH, DAY, YEAR, WEEKDAY, DATE

DATEDIF

Time functions

Time Functions

Time Serial Numbers (10:48)

12. Financial Functions

Overview

Overview

Future Value (FV)

Future Value (FV)

FV (7:29)

Loan Payment

Loan Payment (PMT)

PMT (Loan) (4:05)

Savings Payment

Savings Payment (PMT)

PMT (Savings) (3:38)

Loan PV

Loan PV

PV (Loan) (3:23)

Savings PV

Savings PV

PV (Savings) (4:06)

Lottery PV

Lottery PV

PV (Lottery) (4:30)

Loan Rate

Loan Rate (RATE)

Rate (Loan) (2:44)

Savings Rate

Savings Rate (RATE)

Rate (Savings) ( 3:04)

Loan NPER

Loan time to pay off (NPER)

NPER (Loan) (2:36)

Savings NPER

Savings time to goal (NPER)

NPER (Savings) ( 2:46)

13. Macros and Visual Basic

Customizing Excel

Customizing Excel

 

Macros

Macros

 

Visual Basic

Visual Basic

 

 

Miscellaneous

·        Solver

·  Office Home Page: starting place for info about Excel

·  Microsoft Office Specialist (MOS) Home Page: get your Excel skills certified