Content and objectives

By the end of the course you will be able to create simple spreadsheets with calculations and sums and be able to format, print and save them.

Basic Skills - working with cells, numbers, texts and calculations

· Selections - using both the mouse and keyboard

· Simple Formulae - add, subtract, multiply and divide

· Formatting - font, alignment and numbers

· Moving and Copying - keyboard and mouse shortcuts.

Formulae - use formulae that automatically update their results when values change

· Addition and Multiplication

· SUM - sum a range of cells

· Absolute references - using $

· Count, average, max and min - simple aggregate functions.

Getting help - new Excel feature to help you

· Help (F1) – documentation

·  Search

· Function Wizard (fx) - learning new functions

· Count, average, max and min - simple aggregate functions.

Formatting - present your spreadsheet clearly and efficiently

· Fonts - size, typeface and colour

· Colour - cells, borders and text in cells

· Borders - underline or outline a range of cells.

Sheets and workbooks - organise various kinds of related information on separate sheets in a workbook

· New sheets and workbooks - adding and deleting worksheets · Moving and copying sheets - using keyboard and mouse commands · Linking - referring to cells in other sheets.

Saving & Printing - file operations and making sure you get the right printed results · Files, folders and metadata - navigation and making sure your documents are correctly identified

· Print Area - print just the area of the worksheet or worksheets needed · Charts - display your results visually.

EXCEL INTERMEDIATE- 6h 2 half days

Content and objectives

By the end of the course you will be able to create spreadsheets with filtered tables of data and pivot tables. You will be able to bring data together from several tables with vlookup and if functions

Review - ensure your basic skills are complete and you are using the best methods and shortcuts for the current version

• Selecting cells and ranges - multiple selections and shortcuts
• Moving and copying - mouse and keyboard shortcuts
• Formatting - font, alignment and numbers
• Formulae and absolute ($) references •   Quick access toolbar - easy and efficient

Database - using filters and PivotTables to generate ad hoc or structured reports from tables of data

• AutoFilter - show a subset of the data
• Sorting
• Designing and creating PivotTables - diagrams
• Modifying and formatting PivotTables
• Slicers - filters and timelines.

Functions - learn functions that allow you to bring in information from other tables, make simple decisions and create simple stats

• VLOOKUP and XLOOKUP - looking up data in another table
• IF - putting two formulae in the same cell
• COUNTIF - count up cells which match a criterion.

Formatting - programme your spreadsheet to control data entry and format automatically •            Conditional formatting

• Data validation.

Macros - automate a set of simple actions such as copying, formatting, printing etc. •       Recording

• Buttons.

EXCEL ADVANCED 6h 2 half day

Content and objectives

By the end of the course you will be able to create advanced spreadsheets that will require low maintenance. You will be able to create financial or budge models to help in decision making. You will be able to do more powerful data summaries and analysis.

Review 

• Of content from Intro and advanced sessions

Dynamic Array Functions

• SORT, SORTBY, FILTER, UNIQUE
• Uses and benefits

Modelling - running a model several times with different inputs and comparing the outputs

• Data tables - iterating the spreadsheet
• Scenario switching - using indirect
• Sensitivity analysis - using data tables

Data analysis - summarising information in either database or cross tabulated structures

• 3D spreadsheets
• New functions – IFS, COUNTIFS, SUMIFS etc
• Logical operators (AND, OR) - complex conditions with Ifs.

Tables and Power Query - get and transform data

• Database manipulations - remove duplicates
• Append - combining two queries.

Advanced lookups -

• Using XLOOKUP for2 way lookups - looking up data in crosstab tables
• Reconciliations - comparing two lists.

Text functions

• Left, right, mid - extracting part of some text in a cell.
• More flexible extraction using new functions – TEXTBEFORE, TEXTAFTER