Microsoft Excel 2016 (Advanced)

Course duration

Two days: 9.30am - 4.30pm on both days

Course 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 of course

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?

Course agenda

Goals and objectives of course

Goals

1. Enable the user to pass advanced ECDL
2. To familiarise the user with the more advanced ECDL features including functions, charting and databases.

Objectives

After completing this course you will be able to:

1. use:
   1. Date and time functions
      • TODAY DAY MONTH YEAR
   2. Mathematical and statistical functions
      • SUMIF ROUND COUNT COUNTA COUNTIF
   3. Text functions
      • PROPER UPPER LOWER CONCATENATE
   4. Financial functions
      • FV NPV PMT PV RATE
   5. Lookup functions
      • VLOOKUP HLOOKUP
   6. Logical functions
      • IF
   7. Nested functions
      • AND OR with IF
2. use Excel as a database
3. use advanced filters with lists
4. use functions with filtered lists such as DSUM DCOUNT DMAX DMIN
5. name cells
6. apply conditioning formatting to cells
7. apply an autoformat to a table
8. hide rows columns or worksheets
9. create cell formats
10. import text files into Excel
11. use the sub totalling feature with lists
12. work with one-input and two input data tables
13. protect and secure a spreadsheet or cells within a spreadsheet
14. link data and charts between worksheets and workbooks
15. link Excel charts or data to a MS Word document
16. create templates
17. create and format charts, including formatting the charts as well as any labels or axes
18. create and pivot table
19. create scenarios
20. use the auditing facility
21. add validation to a spreadsheet
22. create a macro
23. freeze or split windows
24. track changes in an Excel document
25. compare and merge spreadsheets.

You will complete all of these using Microsoft Excel.

Cost of course

External attendees: £170 plus VAT per candidate.