Advanced Microsoft Excel

Uses of Excel

Set validation rules to specify what type of data can be entered

Create drop down lists for data entry

Connect drop down lists to data within a spreadsheet

Use input messages to help users enter correct data

Display error alerts when invalid data is entered

The use of keyboard for frequent commands

Customizing the Quick Access Toolbar

Using the Auto Fill tool to fill cells with data

Copy and paste content using the clipboard

Clearly structure and present worksheet content

User various tools to format text

Change number formats

Align text and numbers

Modify the size of rows and columns

Apply borders for clarity

Hide gridlines on a worksheet

Merge multiple cells into one cell

Wrap text within a cell

Apply styles to cells for quick formatting

Use the Format Painter tools for speed and consistency in appearance

Clear formatting and tidy up content

Check the spelling of text

Using print preview

Amend page setup options

Print worksheets

Use page layout view and page break preview

Add headers and footers

Zoom in and out on a spreadsheet

Freeze panes to simplify working with large spreadsheets

View different sheets and workbooks side by side on the same screen

Highlighting records that meet specific criteria

Draw attention to high and low figures

Use colors and icons to represent values

Use formulas to set advanced rules and highlighting

Modify conditional formatting rules

Manage conditional formatting steps

Using relative references within formulas

Using absolute references within formulas

Using mixed references within formulas

Create meaningful names for individual cells and ranges of cells

Use the name manager to edit named references

Use named references within formulas

Use named ranges within drop-down lists

Write formulas to add, subtract, multiply and divide

Edit formulas within a cell

Add and edit formulas within the formula bar

Copy a formula to other cells

View results of calculations in the status Bar

Use brackets to change the order of a calculation

Calculate the percentage of a given value

Calculate the percentage difference between values

Display percentages correctly

Use of SUM, COUNT, COUNTA, COUNTBLANK, MIN, MAX, AVERAGE, PRODUCT and SUMPRODUCT functions

Text Functions - PROPER, UPPER and LOWER, CONCAT, LEFT RIGHT

Logical Functions - IF, AND, OR, IFS

Calculation Functions Based On Criteria - Sumif, Countif, Averageif

Lookup Functions - Vlookup, Hlookup, Xlookup, Match, Index

Is Functions To Test Data - isblank, istext, iserror

Rounding Up And Down Functions - roundup, rounddown, mround

Array Functions - sort, filter

Financial Functions - pmt, rate, pv ,fv

Working With Dates And Times - today, days, month

Manually create groups of data to simplify working with large list

Automatically create an outline of data with groups

Use the subtotal tool to perform calculations on groups of data

Sort records into alphabetical or numerical order

Sort records by date

Sort records by color

Use drop-down filters to find specific records

Create customized filters to find records that meet criteria

Filter data using the advanced filter

Set multiple criteria

Copy filtered data to other cells

Filter data to display unique records

Use Goal seek to identify target figures

Create Scenario Manager reports to compares possible outcomes

Create Data Tables with one or two Variables

Hide rows and columns

Hide worksheets

Protect formulas within cells

Password protect worksheets and workbooks

Protect ranges of cells

Copying and pasting hidden data

Create PivotTables

Change the layout of PivotTables

Sort data vertically and horizontally

Filter data within a PivotTable

Search for data within a PivotTable

Customize field names

Format data displayed within Pivot Tables

Drill down to display data

Create Pivot Charts

Set PivotTable options

Change the calculations performed

Work with subtotals within a PivotTable

Create top 10 reports

Group data into date periods

Group data into number ranges

Display values as percentages of total figures

Create running totals

Create calculated fields

Use slicers to filter multiple PivotTables

Apply conditional formatting to data within PivotTables

Use formulas to extract data from PivotTables

Create column, line, pie, area, scatter and radar chars

Change the chart type

Use the quick layout options

Draw attention to chart content with effective formatting

Apply styles to charts

Add trendlines to charts

Add, edit and format labels

Explode pie charts for visual effects

Customize charts in various ways

Dashboard creation

Link one cell to another

Link data across sheets in the same Excel workbook

Link data across two Excel files

Update and manage linked data

Work with the Developer tab

Record relative reference and absolute reference macros

Run macros using the keyboard

Create a new tab to hold macros

Edit VBA script to correct macros

Project involving all that has been taught in class from formatting workbook, to functions, to macros