Skip to main content

Read our latest coronavirus updates. 

PayReportApply
PayReportApply
Read aloud icon Read aloud Accessibility icon Accessibility
IT training

Microsoft Excel 2016 (Advanced)

  • 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

    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.

  • Costs

    External attendees: £170 plus VAT per candidate.

Read aloud icon Read aloud