Skip to main content
PayReportApply
IT training

Microsoft Excel 2016 (Intermediate)

  • Duration

    One day; 9.30am - 4.30pm

  • Description

    Microsoft Excel provides ways to help automate the business tools you need for your data analysis, list keeping, and calculations as well as the presentation tools for reporting your results.

    This course is designed for users who are proficient in the functionality of Microsoft Excel. It is aimed at teaching the user improved ways of working with spread sheets and charts as well as introducing new topics such as working with databases and creating macros.prerequisites:

  • Prerequisites

    Attendance at an Excel course or basic knowledge of Excel topics such as chart creation and basic formulas.

    Check Points:

    • Can you create a simple spread sheet?
    • Can you change the appearance of a worksheet - fonts, borders, alignment?
    • Can you edit the worksheet - cut, copy, paste, text boxes?
    • Do you have knowledge of creating charts?
  • Agenda

    Exercise Details
    Exercise 1 Introduction and refresher exercise
    • Creating a simple spreadsheet
    • Formatting a spreadsheet
    Exercise 2 
    • Custom series
    • Autofill
    Exercise 3  Formulae
    • Simple formula
    • Common Excel functions
    • Variable cell referencing
    Exercise 4  Advanced formulae
    • Cell referencing
    • Absolute cell referencing
    Exercise 5 

    Naming cells

    • Naming a cell
    • Naming a cell range
    Exercise 6 

    Charting

    • Formatting bar chart
    Exercise 7 

    Charting

    • Creating and formatting a pie chart
    Exercise 8

    Linking charts between Excel and Word

    • Creating a link between Excel and Word
    Exercise 9 

    Charting

    • Creating and formatting a line chart
    ​Exercise 10 

    Database facilities

    • Sorting information
    • Filtering information
    • Advanced filtering
    Exercise 11 
     
    Filter by colour
    • Filtering information by colour
    Exercise 12 


    Advanced database facilities

    • Using the Advanced filter to create conditions
    Exercise 13  Functions within databases
    • DSUM DCOUNT
    Exercise 14 
     
    Pivot tables
    • Creating a PivotTable
    • Working with PivotTables
  • Goals and objectives


    Goals

    1. To introduce and develop the users understanding or some of the more complex features of Microsoft Excel.
    2. To improve the users knowledge of the various tools provided within Excel and, to enable to user to produce a professional level of output.


    Objectives

    After reading this manual you will be able to:

    1. Enter text and format cells
    2. Create formulae to add or average a range of cells
    3. Use the auto fill feature and create a custom series
    4. Use some of the more popular functions using the function wizard:
      1. AVERAGE, MAX, MIN, COUNT, ROUND and IF
    5. Understand and use variables
    6. Use Relative and Absolute cell addressing
    7. Name Cells
    8. Create charts
    9. Manipulate charts
    10. Work with databases, sort information, use auto filter to find information meeting certain criteria
    11. Use the Advanced filter facility
    12. Use database functions
    13. Create and format a Pivot Table.

    You will complete all of these using Microsoft Excel.

  • Costs

    External attendees: £110 plus VAT per candidate.

Read aloud icon Read aloud