Microsoft Excel Course

Cut Excel down to size with our tailored online course. Achieve certificated proficiency in hours. Available in all major PC and Mac versions.

We reviewed Excel's 500 functions, ruthlessly stripping out all non-essentials, to arrive at our highly refined syllabus. Our diagnostic tool then focuses your time on those parts of that syllabus that will be most valuable to you. Our regularly updated course consists of a carefully balanced combination of text, illustrations, videos and end-of-unit tests to measure your progress.

 

Our CPD UK certified online course covers what-you-need-to-know-in-business rather than what-Excel-can-do.

 

Highlights

  • Consists of 38 business focused Excel units from our distilled syllabus
  • Contains 100 hours of learning materials but can be completed in as little as 12 hours of dedicated study
  • Provides 24/7 access to all learning materials from the date you register
  • Is available in all the main versions of Excel (PC: 2003, 2007, 2010 and 2013 / 365; Mac: 2011)
  • Generates a Certificate of Attainment and a Test Certificate (useful for CPD fulfilment)
  • Is taught through a blend of text-based articles, practice spreadsheets, video tutorials and podcasts
  • Comes with email support from our experts.

Preview

Course Content

Course Diagnostic

User Dashboard

Syllabus Explorer

The Test

Course Certificate

Syllabus

Section 1: Foundation

directright

Unit 1.1 - Excel Introduction

A description of the purpose and application of Excel – what it can do, why it’s useful.

directright

Unit 1.2 - The Excel Interface

Understanding and working with the Excel interface – getting to know your way around the application, how to access functionality and how to customize it. How to create, save and open Workbooks. Under Save As, a brief explanation of backwards compatibility and other formats (e.g. PDF) save options. Skydrive, alternative online storage, and ‘local’ saving. Basic printing.

directright

Unit 1.3 - Basic Navigation and Editing

Selection and navigation. Moving around a spreadsheet efficiently. Entering information into cells, types of data (text, numbers, dates). Basic formatting. Working with multiple Worksheets.

directright

Unit 1.4 - Getting Going

Takes a quick look at a first ‘real’ spreadsheet, in order to introduce the concept of functions and formulas, and give a feel for how Excel can be useful.

Section 2: Orientation & efficiency

directright

Unit 2.1 - Editing

Copying, pasting, inserting and deleting cells, ranges of cells, rows, columns and Worksheets. ‘Special’ pasting – for example to copy just formulas, or just values. Common problems with pasting. The differences between copying and ‘cutting’.  Excel aids to copying – Fill Handle and Flash Fill.

directright

Unit 2.2 - Viewing

The various ‘Views’ or modes of spreadsheet display available in Excel, and how to choose the right View for the task in hand.  Techniques for making working with a spreadsheet easier – Freezing Panes, splitting windows, and using multiple windows.

directright

Unit 2.3 - Spreadsheet Structure

Changing the structure of your spreadsheet to make it clearer. Grouping rows and columns; changing cells size and shape.

directright

Unit 2.4 - Cell References

How (and why) to connect (or link) cells and ranges, and how connected cells behave. Fixed or absolute references (dollar signs) for cells; partially fixed references. The "F4" shortcut.

directright

Unit 2.5 - Named Ranges

Making formulas clearer and more easily understood by replacing cell references with meaningful names.

directright

Unit 2.6 - Basic Macros

Using Macros to automate repetitive tasks in Excel. (This Unit covers recorded Macros – the later unit 5.8 covers writing and editing Macros using VBA in detail.)

directright

Unit 2.7 - Design

Why spreadsheet design important in increasing efficiency, reducing errors and aiding transparency. Principles of good spreadsheet design.

Section 3: Administration

directright

Unit 3.1 - Customising Excel

Changing Excel settings to suit the way you use Excel. Includes: font face, number of worksheets, frequency of autorecovery, default view, Autocorrect and Ribbon alterations. Excel Add-Ins – Solver, Analysis TookPak. PowerPivot and Inquire.

directright

Unit 3.2 - Housekeeping

Naming and storing Excel files. Version control. Making backups.

directright

Unit 3.3 - Connecting Workbooks

Setting up links between Workbooks. The dangers and pitfalls of doing so. Managing links between Workbooks and updating external references. Troubleshooting – finding ‘missing links’ and using auditing tools.

directright

Unit 3.4 - Documentation

What to document, and how, in order to produce clear and robust spreadsheets.

directright

Unit 3.5 - Protecting and Sharing

Protecting Workbooks and when it’s useful or necessary locking cells, Worksheet protection, Workbook protections, allowing users to edit ranges. Permissions and granting access to folders & files, how to work with others on the same files and different files (both in process terms and using Excel functionality; when not to use it, avoiding conflicts). Sharing via the Cloud.

directright

Unit 3.6 - Google Docs

What is Googledocs, and why use Google Spreadsheets for spreadsheeting?

directright

Unit 3.7 - Excel Troubleshooting

Common problems and how to avoid or overcome them. For example building in checks into spreadsheets, techniques for finding errors.  Advanced techniques for diagnosing more complex problems.

Section 4: Data Handling

directright

Unit 4.1 - Sorting and Filtering

Techniques for sorting and filtering data, including controlling the order of precedence in a sort, advanced filters, and an introduction to PivotTables.  Using sorting and filtering to check and ‘clean’ data.

directright

Unit 4.2 - Controlling User Input

Controlling the way users can enter data into a spreadsheet to reduce risk of error and increase efficiency.  Covers Data Validation and using IS- functions to trap errors.

directright

Unit 4.3 - Working with Dates & Times

Date and time formatting, performing basic maths with dates and times, and date and time functions.

directright

Unit 4.4 - Working with Text

Entering and formatting text, extracting or combining parts of text. ‘Cleaning’ data for typos and bugs.

directright

Unit 4.5 - Lookup and Reference

Looking up information in a basic table, and more flexibly. VLOOKUP() and the more flexible INDEX()/MATCH() combination. Advanced lookups.

directright

Unit 4.6 - Logical Functions

Automatic decision making and error handling.  Advanced logical tests.  Matrix user of IF as an array function.

Section 5: Data Analysis

directright

Unit 5.1 - Working with Numbers

Maths: basic addition, subtraction, multiplication and division. How to use brackets to get the correct result. Powers.  Rounding.

directright

Unit 5.2 - Summarising Data

Understanding a data set by summarizing it. Totals and counts – SUM(), COUNT() and COUNTA(). Other statistics. Conditional totals and counts. Advanced conditional sums, SUMPRODUCT and array formulas.

directright

Unit 5.3 - PivotTables 1 - Simple Summaries

Uses of PivotTables. Summarising data (presenting database in terms of different subcategories). Understanding data (understanding categories, spotting typos, understanding range of values). Interpreting data (analysing to work out trends, areas of growth / profit / loss etc). Filtering. Sorting and grouping

directright

Unit 5.4 - PivotTables 2 - Manipulating Data

Changing the layout and format of data in a PivotTable. Changing Value Field Settings to show counted rather than ‘summed’ data. Setting up references to a PivotTable.

directright

Unit 5.5 - PivotTables 3 - Interpreting Data

Using PivotTables to understand and interpret data. Includes Slicers and Timelines, PivotCharts and Conditional Formatting of PivotTables.

directright

Unit 5.6 - PowerPivot: Handling Big Data

Why PowerPivot is different to a normal PivotTable: size, data model (inclusion in 2013 standard Excel), DAX, KPIs. PowerPivot data tools. Connecting to data, managing multiple tables and connections, including data from within the Excel spreadsheet

directright

Unit 5.7 - Formula Auditing

Understanding and debugging spreadsheets. Tracing calculations and monitoring the effect of changes. Formula evaluator, Watch window and the INQUIRE Add-In.

directright

Unit 5.8 - Advanced Macros and VBA

Troubleshooting macros. Writing code directly. User defined functions.  Program structures. Using Macros to interact with users.

directright

Unit 5.9 - Modelling

Principles. What modelling is; modelling broken down into distinct stages; important principles - simplification, built-in checks; including a presentation-ready sheet Techniques. Calculating growth with data points over several years; extrapolating that into the future; scenario modelling and optimisation; prioritisation (RANK); build process.

Section 6: Presentation

directright

Unit 6.1 - Cell Formatting

Basic font formatting, alignment – Including horizontal and vertical alignment, wrapping, merging, orientation, Alt-Return; consistent, sensible row heights & column Styles – to make spreadsheets more and consistent. Formatting tools

directright

Unit 6.2 - Number Formatting

Types of number (E.g., currency, %, decimal, negative numbers) – and Excel formats best employed. Custom number formats and how to maximise impact and clarity.

directright

Unit 6.3 - Conditional Formatting

Changing the format of cells depending on their value. Graphical conditional formats. Writing conditional format formulas.

directright

Unit 6.4 - Graphs and Charts

Creating simple charts and editing them to control and improve formatting. Choosing the right chart Principles and guidelines for communicating well with charts. Sparklines and Maps (when and how to use).

directright

Unit 6.5 - Page and print setup

Displaying spreadsheets as pages. Page layout view, page breaks, print area,. Printing from multiple Worksheets (and that default will be to print only from active Worksheet). Headers and footers. Adjusting page setup. Printing very large sheets of data.

Section 1: Foundation

directright

Unit 1.1 - Excel Introduction

A description of the purpose and application of Excel – what it can do, why it’s useful.

directright

Unit 1.2 - The Excel Interface

Understanding and working with the Excel interface – getting to know your way around the application, how to access functionality and how to customize it. How to create, save and open Workbooks. Under Save As, a brief explanation of backwards compatibility and other formats (e.g. PDF) save options. Skydrive, alternative online storage, and ‘local’ saving. Basic printing.

directright

Unit 1.3 - Basic Navigation and Editing

Selection and navigation. Moving around a spreadsheet efficiently. Entering information into cells, types of data (text, numbers, dates). Basic formatting. Working with multiple Worksheets.

directright

Unit 1.4 - Getting Going

Takes a quick look at a first ‘real’ spreadsheet, in order to introduce the concept of functions and formulas, and give a feel for how Excel can be useful.

Section 2: Orientation & efficiency

directright

Unit 2.1 - Editing

Copying, pasting, inserting and deleting cells, ranges of cells, rows, columns and Worksheets. ‘Special’ pasting – for example to copy just formulas, or just values. Common problems with pasting. The differences between copying and ‘cutting’.  Excel aids to copying – Fill Handle and Flash Fill.

directright

Unit 2.2 - Viewing

The various ‘Views’ or modes of spreadsheet display available in Excel, and how to choose the right View for the task in hand.  Techniques for making working with a spreadsheet easier – Freezing Panes, splitting windows, and using multiple windows.

directright

Unit 2.3 - Spreadsheet Structure

Changing the structure of your spreadsheet to make it clearer. Grouping rows and columns; changing cells size and shape.

directright

Unit 2.4 - Cell References

How (and why) to connect (or link) cells and ranges, and how connected cells behave. Fixed or absolute references (dollar signs) for cells; partially fixed references. The "F4" shortcut.

directright

Unit 2.5 - Named Ranges

Making formulas clearer and more easily understood by replacing cell references with meaningful names.

directright

Unit 2.6 - Basic Macros

Using Macros to automate repetitive tasks in Excel. (This Unit covers recorded Macros – the later unit 5.8 covers writing and editing Macros using VBA in detail.)

directright

Unit 2.7 - Design

Why spreadsheet design important in increasing efficiency, reducing errors and aiding transparency. Principles of good spreadsheet design.

Section 3: Administration

directright

Unit 3.1 - Customising Excel

Changing Excel settings to suit the way you use Excel. Includes: font face, number of worksheets, frequency of autorecovery, default view, Autocorrect and Ribbon alterations. Excel Add-Ins – Solver, Analysis TookPak. PowerPivot and Inquire.

directright

Unit 3.2 - Housekeeping

Naming and storing Excel files. Version control. Making backups.

directright

Unit 3.3 - Connecting Workbooks

Setting up links between Workbooks. The dangers and pitfalls of doing so.Managing links between Workbooks and updating external references.Troubleshooting – finding ‘missing links’ and using auditing tools.

directright

Unit 3.4 - Documentation

What to document, and how, in order to produce clear and robust spreadsheets.

directright

Unit 3.5 - Protecting and Sharing

Protecting Workbooks and when it’s useful or necessary locking cells, Worksheet protection, Workbook protections, allowing users to edit ranges.Permissions and granting access to folders & files, how to work with others on the same files and different files (both in process terms and using Excel functionality; when not to use it, avoiding conflicts). Sharing via the Cloud.

directright

Unit 3.6 - Google Docs

What is Googledocs, and why use Google Spreadsheets for spreadsheeting?

directright

Unit 3.7 - Excel Troubleshooting

Common problems and how to avoid or overcome them. For example building in checks into spreadsheets, techniques for finding errors.  Advanced techniques for diagnosing more complex problems.

Section 4: Data Handling

directright

Unit 4.1 - Sorting and Filtering

Techniques for sorting and filtering data, including controlling the order of precedence in a sort, advanced filters, and an introduction to PivotTables.  Using sorting and filtering to check and ‘clean’ data.

directright

Unit 4.2 - Controlling User Input

Controlling the way users can enter data into a spreadsheet to reduce risk of error and increase efficiency.  Covers Data Validation and using IS- functions to trap errors.

directright

Unit 4.3 - Working with Dates & Times

Date and time formatting, performing basic maths with dates and times, and date and time functions.

directright

Unit 4.4 - Working with Text

Entering and formatting text, extracting or combining parts of text. ‘Cleaning’ data for typos and bugs.

directright

Unit 4.5 - Lookup and Reference

Looking up information in a basic table, and more flexibly. VLOOKUP() and the more flexible INDEX()/MATCH() combination. Advanced lookups.

directright

Unit 4.6 - Logical Functions

Automatic decision making and error handling.  Advanced logical tests.  Matrix user of IF as an array function.

Section 5: Data Analysis

directright

Unit 5.1 - Working With Numbers

Maths: basic addition, subtraction, multiplication and division. How to use brackets to get the correct result. Powers.  Rounding.

directright

Unit 5.2 - Summarising Data

Understanding a data set by summarizing it. Totals and counts – SUM(), COUNT() and COUNTA(). Other statistics. Conditional totals and counts. Advanced conditional sums, SUMPRODUCT and array formulas.

directright

Unit 5.3 - PivotTables 1 - Simple Summaries

Uses of PivotTables. Summarising data (presenting database in terms of different subcategories). Understanding data (understanding categories, spotting typos, understanding range of values). Interpreting data (analysing to work out trends, areas of growth / profit / loss etc). Filtering. Sorting and grouping

directright

Unit 5.4 - PivotTables 2 - Manipulating Data

Changing the layout and format of data in a PivotTable. Changing Value Field Settings to show counted rather than ‘summed’ data. Setting up references to a PivotTable.

directright

Unit 5.5 - PivotTables 3 - Interpreting Data

Using PivotTables to understand and interpret data. Includes Slicers and Timelines, PivotCharts and Conditional Formatting of PivotTables.

directright

Unit 5.6 - PowerPivot: Handling Big Data

Why PowerPivot is different to a normal PivotTable: size, data model (inclusion in 2013 standard Excel), DAX, KPIs. PowerPivot data tools. Connecting to data, managing multiple tables and connections, including data from within the Excel spreadsheet

directright

Unit 5.7 - Formula Auditing

Understanding and debugging spreadsheets. Tracing calculations and monitoring the effect of changes. Formula evaluator, Watch window and the INQUIRE Add-In.

directright

Unit 5.8 - Advanced Macros and VBA

Troubleshooting macros. Writing code directly. User defined functions.  Program structures. Using Macros to interact with users.

directright

Unit 5.9 - Modelling

Principles. What modelling is; modelling broken down into distinct stages; important principles - simplification, built-in checks; including a presentation-ready sheetTechniques. Calculating growth with data points over several years; extrapolating that into the future; scenario modelling and optimisation; prioritisation (RANK); build process.

Section 6: Presentation

directright

Unit 6.1 - Cell Formatting

Basic font formatting, alignment – Including horizontal and vertical alignment, wrapping, merging, orientation, Alt-Return; consistent, sensible row heights & columnStyles – to make spreadsheets more and consistent. Formatting tools

directright

Unit 6.2 - Number Formatting

Types of number (E.g., currency, %, decimal, negative numbers) – and Excel formats best employed. Custom number formats and how to maximise impact and clarity.

directright

Unit 6.3 - Conditional Formatting

Changing the format of cells depending on their value. Graphical conditional formats. Writing conditional format formulas.

directright

Unit 6.4 - Graphs and Charts

Creating simple charts and editing them to control and improve formatting. Choosing the right chart Principles and guidelines for communicating well with charts. Sparklines and Maps (when and how to use).

directright

Unit 6.5 - Page and print setup

Displaying spreadsheets as pages. Page layout view, page breaks, print area,. Printing from multiple Worksheets (and that default will be to print only from active Worksheet). Headers and footers. Adjusting page setup. Printing very large sheets of data

Section 1: Foundation

directright

Unit 1.1 - Excel Introduction

A description of the purpose and application of Excel – what it can do, why it’s useful.

directright

Unit 1.2 - The Excel Interface

Understanding and working with the Excel interface – getting to know your way around the application, how to access functionality and how to customize it. How to create, save and open Workbooks. Under Save As, a brief explanation of backwards compatibility and other formats (e.g. PDF) save options. Skydrive, alternative online storage, and ‘local’ saving. Basic printing.

directright

Unit 1.3 - Basic Navigation and Editing

Selection and navigation. Moving around a spreadsheet efficiently. Entering information into cells, types of data (text, numbers, dates). Basic formatting. Working with multiple Worksheets.

directright

Unit 1.4 - Getting Going

Takes a quick look at a first ‘real’ spreadsheet, in order to introduce the concept of functions and formulas, and give a feel for how Excel can be useful.

Section 2: Orientation & efficiency

directright

Unit 2.1 - Editing

Copying, pasting, inserting and deleting cells, ranges of cells, rows, columns and Worksheets. ‘Special’ pasting – for example to copy just formulas, or just values. Common problems with pasting. The differences between copying and ‘cutting’.  Excel aids to copying – Fill Handle and Flash Fill.

directright

Unit 2.2 - Viewing

The various ‘Views’ or modes of spreadsheet display available in Excel, and how to choose the right View for the task in hand.  Techniques for making working with a spreadsheet easier – Freezing Panes, splitting windows, and using multiple windows.

directright

Unit 2.3 - Spreadsheet Structure

Changing the structure of your spreadsheet to make it clearer. Grouping rows and columns; changing cells size and shape.

directright

Unit 2.4 - Cell References

How (and why) to connect (or link) cells and ranges, and how connected cells behave. Fixed or absolute references (dollar signs) for cells; partially fixed references. The "F4" shortcut.

directright

Unit 2.5 - Named Ranges

Making formulas clearer and more easily understood by replacing cell references with meaningful names.

directright

Unit 2.6 - Basic Macros

Making formulas clearer and more easily understood by replacing cell references with meaningful names.

directright

Unit 2.7 - Design

Why spreadsheet design important in increasing efficiency, reducing errors and aiding transparency. Principles of good spreadsheet design.

Section 3: Administration

directright

Unit 3.1 - Customising Excel

Changing Excel settings to suit the way you use Excel. Includes: font face, number of worksheets, frequency of autorecovery, default view, Autocorrect and Ribbon alterations. Excel Add-Ins – Solver, Analysis TookPak. PowerPivot and Inquire.

directright

Unit 3.2 - Housekeeping

Naming and storing Excel files. Version control. Making backups.

directright

Unit 3.3 - Connecting Workbooks

Setting up links between Workbooks. The dangers and pitfalls of doing so.Managing links between Workbooks and updating external references.Troubleshooting – finding ‘missing links’ and using auditing tools.

directright

Unit 3.4 - Documentation

What to document, and how, in order to produce clear and robust spreadsheets.

directright

Unit 3.5 - Protecting and Sharing

Protecting Workbooks and when it’s useful or necessary locking cells, Worksheet protection, Workbook protections, allowing users to edit ranges.Permissions and granting access to folders & files, how to work with others on the same files and different files (both in process terms and using Excel functionality; when not to use it, avoiding conflicts). Sharing via the Cloud.

directright

Unit 3.6 - Excel Troubleshooting

What is Googledocs, and why use Google Spreadsheets for spreadsheeting?

Section 4: Data Handling

directright

Unit 4.1 - Sorting and Filtering

Techniques for sorting and filtering data, including controlling the order of precedence in a sort, advanced filters, and an introduction to PivotTables.  Using sorting and filtering to check and ‘clean’ data.

directright

Unit 4.2 - Controlling User Input

Controlling the way users can enter data into a spreadsheet to reduce risk of error and increase efficiency.  Covers Data Validation and using IS- functions to trap errors.

directright

Unit 4.3 - Working with Dates & Times

Date and time formatting, performing basic maths with dates and times, and date and time functions.

directright

Unit 4.4 - Working with Text

Entering and formatting text, extracting or combining parts of text. ‘Cleaning’ data for typos and bugs.

directright

Unit 4.5 - Lookup and Reference

Looking up information in a basic table, and more flexibly. VLOOKUP() and the more flexible INDEX()/MATCH() combination. Advanced lookups.

directright

Unit 4.6 - Logical Functions

Automatic decision making and error handling.  Advanced logical tests.  Matrix user of IF as an array function.

Section 5: Data Analysis

directright

Unit 5.1 - Working With Numbers

Maths: basic addition, subtraction, multiplication and division. How to use brackets to get the correct result. Powers.  Rounding.

directright

Unit 5.2 - Summarising Data

Understanding a data set by summarizing it. Totals and counts – SUM(), COUNT() and COUNTA(). Other statistics. Conditional totals and counts. Advanced conditional sums, SUMPRODUCT and array formulas.

directright

Unit 5.3 - PivotTables 1 - Simple Summaries

Uses of PivotTables. Summarising data (presenting database in terms of different subcategories). Understanding data (understanding categories, spotting typos, understanding range of values). Interpreting data (analysing to work out trends, areas of growth / profit / loss etc). Filtering. Sorting and grouping

directright

Unit 5.4 - PivotTables 2 - Manipulating Data

Changing the layout and format of data in a PivotTable. Changing Value Field Settings to show counted rather than ‘summed’ data. Setting up references to a PivotTable.

directright

Unit 5.5 - PivotTables 3 - Interpreting Data

Using PivotTables to understand and interpret data. Includes Slicers and Timelines, PivotCharts and Conditional Formatting of PivotTables.

directright

Unit 5.6 - Formula Auditing

Understanding and debugging spreadsheets. Tracing calculations and monitoring the effect of changes. Formula evaluator, Watch window and the INQUIRE Add-In.

directright

Unit 5.7 - Advanced Macros and VBA

Troubleshooting macros. Writing code directly. User defined functions.  Program structures. Using Macros to interact with users.

directright

Unit 5.8 - Modelling

Principles. What modelling is; modelling broken down into distinct stages; important principles - simplification, built-in checks; including a presentation-ready sheetTechniques. Calculating growth with data points over several years; extrapolating that into the future; scenario modelling and optimisation; prioritisation (RANK); build process.

Section 6: Presentation

directright

Unit 6.1 - Cell Formatting

Basic font formatting, alignment – Including horizontal and vertical alignment, wrapping, merging, orientation, Alt-Return; consistent, sensible row heights & columnStyles – to make spreadsheets more and consistent. Formatting tools

directright

Unit 6.2 - Number Formatting

Types of number (E.g., currency, %, decimal, negative numbers) – and Excel formats best employed. Custom number formats and how to maximise impact and clarity.

directright

Unit 6.3 - Conditional Formatting

Changing the format of cells depending on their value. Graphical conditional formats. Writing conditional format formulas.

directright

Unit 6.4 - Graphs and Charts

Creating simple charts and editing them to control and improve formatting. Choosing the right chart Principles and guidelines for communicating well with charts. Sparklines and Maps (when and how to use).

directright

Unit 6.5 - Page and Print setup

Displaying spreadsheets as pages. Page layout view, page breaks, print area,. Printing from multiple Worksheets (and that default will be to print only from active Worksheet). Headers and footers. Adjusting page setup. Printing very large sheets of data

Section 1: Pre-Syllabus

directright

Unit 1.1 - The Excel Dashboard

Understanding and working with the Excel interface: tabs, the Ribbon, the spreadsheet, Quick Access toolbar, and the Formula and Status bars

directright

Unit 1.2 - Basic Navigation and Editing

Moving, finding and selecting in a spreadsheet; making changes to the look of cells (e.g. borders and fill) and cell contents (e.g. font colors and styles)

Section 2: Spreadsheet Administration

directright

Unit 2.1 - Customising Excel

Changing permanent and default Excel settings including: font face, number of worksheets, frequency of Autorecovery, Autocorrect and Ribbon alterations

directright

Unit 2.2 - Housekeeping

Designing a system of file and folder naming; folder hierarchies; version control; backing up; choosing a password; and other types of spreadsheets

directright

Unit 2.3 - Connecting Workbooks

Referring from one workbook to another Excel workbook; updating external references when opening a workbook; and warnings about losing data through moving files and losing control through reliance on other workbooks

directright

Unit 2.4 - Sharing and Protecting

How to share a file to collaborate with other users on different machines; avoiding and resolving conflicts; protecting cells, sheets and workbooks

directright

Unit 2.5 - Excel Crashes

Avoiding Excel crashes in the first place by monitoring and reducing file size and complexity; troubleshooting; minimizing fallout when an official crash occurs 

Section 3: Orientation and Efficiency

directright

Unit 3.1 - Editing

Copying cells, rows, columns and worksheets; simple and 'special' pasting with associated issues; inserting cells; rows, columns and the effect of using Shift to do so

directright

Unit 3.2 - Viewing

The two main views - Normal and Page Break Preview; freezing and unfreezing panes; tiling and arranging windows; new windows. Direct relevance to Unit 32

directright

Unit 3.3 - Outline

Grouping columns and rows (preferred to hiding); group layers and examples of good usage; subtotalling across categories and warnings about how this can go wrong

directright

Unit 3.4 - Cell References

Connecting (linking) cells and how such cells behave; manipulating these cells; absolute references (dollar signs) for cells and columns; 'F4' shortcut

Section 4: Data Handling

directright

Unit 4.1 - Data Validation

Dropdowns to improve data integrity; consideration of permissible values in a custom list; implementation; three levels of severity

directright

Unit 4.2 - Sorting and Filtering

Sorting data in various ways - quickly, 'properly', horizontally and vertically; Auto-, Multiple- and Advanced filters; alternative techniques

directright

Unit 4.3 - Date and Time Functions

Adding, subtracting and manipulating dates; NOW, TODAY; the Serial Number; SECOND through to YEAR functions; calculating NETWORKING days

directright

Unit 4.4 - Text Functions

FIND, LEN, LEFT, MID and RIGHT functions; CLEANing and TRIMming; & and CONCATENATE; taught with a single, overarching example

directright

Unit 4.5 - Lookup and Reference Functions

VLOOKUPs (and HLOOKUPs); MATCHing and INDEXing data separately; the extremely powerful INDEX-MATCH combo with warnings

directright

Unit 4.6 - Logical and Informational Functions

TRUE, FALSE and comparison operators (<, >, =); AND, OR, ==; ISNUMBER, ISERROR, IF and nested functions; the IF-ISERROR combo

directright

Unit 4.7 - Named Ranges

The benefits over normal cell references; how to set up a Named Range using the Name Box; using Named Ranges to navigate and as Print Areas

directright

Unit 4.8 - Macros

Start - add the Developer tab, enabling macros, using Relative References; recording a macro with a formatting e.g. explained step-by-step; running macros; editing and developing macro VBA

Section 5: Data Analysis

directright

Unit 5.1 - Mathematical Functions

Arithmetic operations, use of brackets; various ways to sum; counting numbers or anything at all; SUMPRODUCT and its use in weighted averages

directright

Unit 5.2 - Summarising Data

Data characteristics; Status Bar features such as AVERAGE, MAX and MIN; summing and counting conditionally over categories (SUMIF and COUNTIF)

directright

Unit 5.3 - Pivot Tables

Quick, efficient yet powerful analysis of large datasets; preparing source data; selecting the right data; changing the layout; advanced tips and pitfalls

directright

Unit 5.4 - Formula Auditing

Checking and understanding a spreadsheet (possibly someone else's); trace precedents and dependents; annotating in-cell as an alternative to tracing

directright

Unit 5.5 - What-If Analysis

Excel's modelling tool - limitations in flexibility and sophistication; scenario modelling via Scenario Manager; optimization of outcome via Goal Seek

directright

Unit 5.6 - Modelling Principles

What modelling is; modelling in distinct stages; important principles - simplification, built-in checks; including a presentation-ready sheet

directright

Unit 5.7 - Modelling Techniques

Calculating growth with data points over several years; extrapolating into the future; scenario modelling and optimisation; prioritisation (RANK)

Section 6: Presentation

directright

Unit 6.1 - Cell Formatting

Good vs. bad formatting; format painter; horizontal and vertical alignment, wrapping, merging and orientation; sensible row heights & column widths

directright

Unit 6.2 - Number Formatting

Main formats - General, Percentage, Accounting, Comma Style; creating custom formats from our recomendation; decimal places

directright

Unit 6.3 - Conditional Formatting

How to apply conditional formatting for Greater than, Equal to, etc; limitations; new-to-2007 relative conditions; icons, databars, etc.

directright

Unit 6.4 - Graphs and Charts

Examples of when to use each of the main chart types; how to create them; formatting to requirement; converting charts to output in Excel or other apps

directright

Unit 6.5 - Review

Proofing spreadsheets, e.g. through Spellcheck; Comments - when to use them, limitations, showing and hiding, printing, alternatives e.g. Notes field, track changes

directright

Unit 6.6 - Page and Print Setup

Using the various Views; Page Setup - margins, orientation, scaling-to-fit a page; printing very large sheets of data; repeating titles on each page

Pricing

Pricing is for 12 months access.

Requirements

Software:

  • Required: Microsoft Excel
  • Suggested: None

Completion Time: 32 hours (average)