Advanced Spreadsheets teaches you skills that can be used to produce sophisticated reports, to perform complex mathematical and statistical calculations, and to improve productivity using a spreadsheet application.

Category

Formatting

Skill Set

Cells

Task Item

Apply conditional formatting.

Create and apply custom number formats.

Split text to columns.

Skill Set

Worksheets

Task Item

Copy, move worksheets between spreadsheets.

Split a window. Move, remove split bars.

Hide, show rows, columns, worksheets.

Save a spreadsheet as a template, modify a template.

Category

Formulas and Functions

Skill Set

Using Formulas and Functions

Task Item

Use date and time functions: today, now, day, month, year.

Use logical functions: and, or, not.

Use mathematical functions: rounddown, roundup, sumif.

Use statistical functions: countif, countblank, rank.

Use text functions: left, right, mid, trim, concatenate.

Use financial functions: fv, pv, pmt.

Use lookup functions: vlookup, hlookup.

Use database functions: dsum, dmin, dmax, dcount, daverage.

Create a two-level nested function.

Use a 3-D reference within a sum, average, minimum, maximum function.

Use mixed references in formulas.

Category

Charts

Skill Set

Creating Charts

Task Item

Create a combined chart like: column and line, column and area.

Create, change, delete a sparkline.

Add a secondary axis to a chart.

Change the chart type for a defined data series.

Add, delete a data series in a chart.

Skill Set

Formatting Charts

Task Item

Re-position chart title, legend, data labels.

Change scale of value axis: minimum, maximum number to display, major interval.

Change display units on value axis without changing data source: hundreds, thousands, millions.

Format columns, bars, pie slices, plot area, chart area to display an image.

Category

Analysis

Skill Set

Using Table

Task Item

Create, modify a pivot table/datapilot.

Modify the data source and refresh the pivot table/datapilot

Filter, sort data in a pivot table/datapilot.

Automatically, manually group data in a pivot table/datapilot and rename groups.

Use one-input, two-input data tables/multiple operations tables.

Skill Set

Sorting And Filtering

Task Item

Sort data by multiple columns at the same time.

Create a customized list and perform a custom sort.

Automatically filter a list in place.

Apply advanced filter options to a list.

Use automatic, manual outline features: group, ungroup, sub-total.

Expand, collapse outline detail levels.

Skill Set

Scenarios

Task Item

Create named scenarios.

Show, edit, delete scenarios.

Create a scenario summary report.

Category

Validating and Auditing

Skill Set

Validating

Task Item

Set, edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time.

Enter input message and error alert

Skill Set

Auditing

Task Item

Trace precedent, dependent cells. Identify cells with missing dependents.

Display all formulas in a worksheet, rather than the resulting values.

Insert, edit, delete, show, hide comments/notes in a worksheet locally, online.

Category

Enhancing Productivity

Skill Set

Naming Cells

Task Item

Name cell ranges, delete names for cell ranges.

Use named cell ranges in a function.

Activate, deactivate the group mode.

Skill Set

Paste Special

Task Item

Use paste special options: add, subtract, multiply, divide.

Use paste special options: values /numbers, transpose.

Skill Set

Linking, Embedding and Importing

Task Item

Insert, edit, remove a hyperlink.

Link data within a spreadsheet, between spreadsheets.

Update, break a link.

Import delimited data from a text file.

Skill Set

Automation

Task Item

Record a simple macro like: change page setup, apply a custom number format, apply autoformats to acell range, insert fields in worksheet header, footer.

Run a macro.

Assign a macro to a custom button.

Category

Collaborative Editing

Skill Set

Reviewing and Security

Task Item

Turn on, off track changes. Track changes in a worksheet using a specified display view.

Compare and merge spreadsheets.

Add, remove password protection for a spreadsheet: to open, to modify.

Protect, unprotect cells, worksheet with a password.

Hide, unhide formulas.