-
Duration
Two days: 9.30am - 4.30pm on both days
-
Description
The Advanced Excel course introduces many of the more advanced tools available in Microsoft Excel. It has been designed for users who are proficient in the functionality of Microsoft Excel to help them realise the full potential of the spreadsheet tool.
The Course covers various new topics including the use of sub totals, data tables, charting, linking multiple worksheets and pivot tables. It also covers many of the functions available within Excel.
-
Prerequisites
Attendance at an Intermediate Excel course or extensive knowledge of Excel.
Check points:
- Can you create and format charts?
- Have you used a few of the more common functions such as SUM, COUNT, AVERAGE?
- Have you knowledge of the database facilities within Excel, such as advanced filters?
-
Agenda
Exercise Details Exercise 1 Date and time functions Exercise 2 Mathematical functions
- Sumif
- Sumif (2)
- Round
- Roundup
- Rounddown
Exercise 3 Statistical functions - Count
- Counta
- Countif
- Countblank
- Countblank (2)
Exercise 4 Text functions - Proper
- Upper
- Lower
- Concatenate
- Left
- Right
- Mid
- Trim
Exercise 5 Financial functions
Exercise 6a Lookup functions - vlookup
Exercise 6b Lookup functions - hlookup Exercise 7 Naming cells
Exercise 8 Lookup functions
Exercise 9 Logical functions
​Exercise 10 Nested functions
- And
- Or
- If
Exercise 11a Advanced filters Exercise 11b Using functions with filtered lists - Dsum
- Dcount
- Dmax
- Dmin
- Daverage
Exercise 12 Naming cells Exercise 13 Conditional formatting Exercise 14 Applying autoformats Exercise 15 Hiding rows, columns, worksheets Exercise 16 Formatting cells Exercise 17 Importing text files Exercise 18 Sub totals Exercise 19a Data input tables Exercise 19b More data input tables Exercise 20a Spreadsheet protection Exercise 20b Cell protection Exercise 21 Custom sorts Exercise 22 Linking cells between Excel worksheets Exercise 23a Exercise 23b Linking worksheets - 3d reference Exercise 24 Linking your Excel spreadsheet to Word Exercise 25 Using hyperlinks in excel Exercise 26 Paste special Exercise 27 Templates Exercise 28 Creating charts Exercise 29a Charts
Exercise 29b Adding data to charts Exercise 30a Secondary axis
Exercise 30b Combined chart type Exercise 31 Formatting chart axis Exercise 32 Pivot tables Exercise 33 Scenarios Exercise 34 Auditing Exercise 35 Adding a comment to a cell Exercise 36 Validation Exercise 37 Macros Exercise 38 Freezing panes Exercise 39 Split window in Excel Exercise 40a
Track changes in Excel - Turn on track changes
- Accept and reject track changes
Exercise 40b Change track changes display view Exercise 40c
Remove track changes Exercise 41 Compare and merge spreadsheets -
Goals and objectives
Goals- Enable the user to pass advanced ECDL
- To familiarise the user with the more advanced ECDL features including functions, charting and databases.
ObjectivesAfter completing this course you will be able to:
- use:
- Date and time functions
- TODAY DAY MONTH YEAR
- Mathematical and statistical functions
- SUMIF ROUND COUNT COUNTA COUNTIF
- Text functions
- PROPER UPPER LOWER CONCATENATE
- Financial functions
- FV NPV PMT PV RATE
- Lookup functions
- VLOOKUP HLOOKUP
- Logical functions
- IF
- Nested functions
- AND OR with IF
- Date and time functions
- use Excel as a database
- use advanced filters with lists
- use functions with filtered lists such as DSUM DCOUNT DMAX DMIN
- name cells
- apply conditioning formatting to cells
- apply an autoformat to a table
- hide rows columns or worksheets
- create cell formats
- import text files into Excel
- use the sub totalling feature with lists
- work with one-input and two input data tables
- protect and secure a spreadsheet or cells within a spreadsheet
- link data and charts between worksheets and workbooks
- link Excel charts or data to a MS Word document
- create templates
- create and format charts, including formatting the charts as well as any labels or axes
- create and pivot table
- create scenarios
- use the auditing facility
- add validation to a spreadsheet
- create a macro
- freeze or split windows
- track changes in an Excel document
- compare and merge spreadsheets.
You will complete all of these using Microsoft Excel.
-
Costs
External attendees: £170 plus VAT per candidate.