Excel

Excel 2016 Tutorial

Font

Font Color

Font Size

Background Color

Format Text

Border

Wrap Text

Align Top

Center Across

Merge cells

Merge Wrap

Strikethrough

Subscript

Superscript

Rotate Text

Format Painter

Comment Indicator

Hide Formulas

Protect Cell

Validate Max Length

Validate Numbers

Validate Exact Length

Insert Row

Delete Row

Row Height

Hide Row

Unhide Row

Unhide Row 1

Insert Column

Delete Column

Column Width

Hide Column

Unhide Column

Unhide Column A

Unhide Right Columns

Insert Sheet

Delete Sheet

Rename Sheet

Hide Sheet

Unhide Sheet

Create Hyperlink 

Delete Hyperlink

Copy Range

Move Range

Add Named Range

Charts

Create Bar Chart

Create Column Chart

Create Pie Chart

Create Line Chart

Create Column/Line Chart with 8 Columns and 1 Line

Create Chart with Two Y-axes and One Shared X-axis

2013 Pivot Tables

Create Pivot Table

Change Pivot Source

Refresh Pivot

Remove Column Grand Totals

Remove Row Grand Totals

Show Top 10

Show Bottom 10

Handle Errors in Pivot

Handle Empty Cells in Pivot

Data Columns in Pivot

Data Column in Pivot

Unhide Value in Pivot

2013 Macros/BA

VBA Environment Introduction
Open VBA
Create Line Chart
Project Explorer
Properties Window

Code Window

Immediate Window

Array Formulas

Sum Values in Column A
Sum Values in Column AB
Sum All
Count Ranges of Times
Count

Count Rows

Count Occurrences

Count Dates
Calculate Monthly Attendance
Count Matches
Count Rows
Average Values

Miscellaneous

Filters

Filter Single Column
Filter 2 Columns

Apply Filter

Copy Filtered Data

Sort Data on 1 Column

Sort Data on 2 Columns

String/Text Functions

ASC (VBA)

CHR (VBA)

CONCATENATE with & (WS,VBA)

FORMAT STRINGS (VBA)

INSTR(VBA)

INSTRREV (VBA)

LCASE (VBA)

LEFT (WS,VBA)

LEN (WS,VBA)

LTRIM (VBA)

MID (WS, VBA)

REPLACE (VBA)

REPLACE (WS)

RIGHT (WS,VBA)

RTRIM (VBA)

SPACE (VBA)

SPLIT (VBA)

STR (VBA)

STRCOMP (VBA)

STRCONV (VBA)

STRREVERSE (VBA)

TRIM (WS,VBA)

LTRIM (VBA)

UCASE (VBA)

VAL (VBA)

REPLACE (WS)

Date/Time Functions

DATE (VBA)

DATEADD (VBA)

DATEDIFF (VBA)

DATEPART (VBA)

DATESERIAL (VBA)

DATEVALUE (WS,VBA)

DAY (WS,VBA)

FORMAT DATES (VBA)

HOUR (WS,VBA)

MINUTE (WS,VBA)

MONTH (WS,VBA)

MONTHNAME (VBA)

NOW (WS,VBA)

TIMESERIAL (VBA)

TIMEVALUE (WS,VBA)

WEEKDAY (WS,VBA)

WEEKDAYNAME (VBA)

YEAR (WS,VBA)

Math/Tri Functions

ABS (WS, VBA)

ATN (VBA)

COS Function (WS,VBA)

EXP (WS,VBA)

FIX (VBA)

FORMAT NUMBERS (VBA)

INT (WS,VBA)

LOG (WS,VBA)

RANDOMIZE (VBA)

RND (VBA)

ROUND (VBA)

SGN (VBA)

SIN (WS,VBA)

SQR (VBA)

TAN (WS,VBA)

Logical Functions

AND (VBA)

AND (WS)

CASE (VBA)

FOR...NEXT (VBA)

IF Function (WS)

IFS Function (WS)

FOR...NEXT (VBA)

IF-THEN-ELSE (VBA)
Nested IF Functions (WS)

OR (VBA)
OR (WS)

WHILE...WEND (VBA)

Information Functions

ENVIRON (VBA)

ISBLANK (WS)
ISDATE (VBA)

ISEMPTY (VBA)

ISERROR (WS,VBA)

ISNULL (VBA)

ISNUMERIC (VBA)

Data Type Conversion Functions

CBOOL (VBA)

CBYTE (VBA)

CCUR (VBA)

CDATE (VBA)

CDBL (VBA)

CDEC (VBA)

CINT (VBA)

CLNG (VBA)

CSNG (VBA)

CSTR (VBA)

CVAR (VBA)

Financial Functions

DDB (WS,VBA)

FV (WS,VBA)

IPMT (WS, VBA)
ISPMT Function (WS)

IRR (WS,VBA)

MIRR (WS,VBA)

NPER (WS,VBA)

NPV (WS,VBA)

PMT (WS,VBA)

PPMT (WS,VBA)

PV (WS,VBA)

RATE (WS,VBA)

SLN (WS,VBA)

SYD (WS, VBA)

File/Directory Functions

CHDIR (VBA)

CHDRIVE (VBA)

DIR (VBA)

FILEDATETIME (VBA)

FILELEN (VBA)

GETATTR (VBA)

MKDIR (VBA)

SETATTR (VBA)

Lookup/Reference Functions

CHOOSE (WS,VBA)

HLOOKUP Function(WS)

SWITCH (VBA)

VLOOKUP function

More Functions

AGGREGATE Function(WS)
AVERAGE Function (WS)
AVERAGEA Function (WS)
AVERAGEIF Function (WS)
CELL Function (WS)

CHAR Function

CLEAN Function

CODE Function

CONCAT Function

CONCATENATE Function

DAVERAGE Function (WS)
DOLLAR Function

EXACT Function

FIND Function

FORMAT Function with Strings

HLOOKUP Function(WS)

INDEX Function(WS)

LOWER Function

MATCH Function (WS)

MAX Function (WS)

MIN Function(WS)
MINA Function(WS)

PROPER Function (WS)

REPT Function

RIGHT Function

RTRIM Function

SEARCH Function

SUBSTITUTE Function
SUBTOTAL Function (WS)
SUMPRODUCT Function (WS)

T Function

TEXT Function

TEXTJOIN Function

TRANSPOSE Function(WS)

UPPER Function

VALUE Function

References

Microsoft Office Training