Not sure which Excel course or level to take? Fill in our Training Needs Analysis (TNA) and we'll tell you which course(s) are most suitable for your level of expertise.
Excel Introduction Module 1: Getting started with Excel
During this hands-on session, you will learn how to create, format, save and print a simple Excel worksheet. You will become familiar with the screen layout and learn how to give commands within the program. You will also learn how to browse Help files to learn more about Excel.
1.5 Hours
This course is suitable for total beginners in the use of Microsoft Excel and assumes no prior knowledge. Ideally, learners be familiar with using a mouse, and working in a Windows environment.
- Understanding the screen: learn about the most important features on-screen; the ribbon, worksheet, scroll bars
- Workbook structure: investigate the essential elements of a workbook; worksheet tabs, columns, rows, cells, the active cell and the name box
- Adding text and data: add some simple text and figures and then use a simple calculation to total the figures; change some of the data and see the total change automatically!
- Formatting text and figures: use options on the Home tab to add basic formatting to enhance the appearance of your worksheet; use format painter
- Formatting cells: apply background fills to cells and add borders to make the worksheet easier to read
- Moving and copying data in cells: Use the mouse, keyboard and clipboard to move and copy data within worksheets and between cells
- Sorting and filtering: sort data effectively; use AutoFilter tools
- Useful tips: mouse pointer - learn what the different shapes are used for; learn how to select cells, rows and columns; use the scroll bars, mouse and keyboard to navigate a large workbook; learn how to undo an action
- Printing: explore the options in the Print backstage view
- Help: browse Excel Help to find out more about the program, or search for a specific topic
Excel Introduction Module 2: Intro to formulas and functions
Excel Introduction Module 3: Workbook management
During this hands-on session, you will learn how to add basic formula and functions to a worksheet to perform the most frequently used calculations. You will be introduced to the concept of ‘relative’ and ‘absolute’ referencing, and learn about the ‘order of calculation’ – an understanding of these concepts will provide a good foundation for working with more complex formulae in the future.
1.5 Hours
This course is suitable for beginners; no prior knowledge of Excel is required but would be beneficial if the learner had a basic understanding of creating and editing workbooks. You should be familiar with using a mouse, working in a Windows environment and ideally should have attended the Level 1 Module 1 session (Getting started).
- Data types: enter/edit text, numeric and date values appropriately in cells and via the formula bar
- Order of calculation: when multiple operators appear in a formula, which is calculated first, why is it so important to be aware of this, and how to control it
- Creating simple formula: use arithmetical operations to add, subtract and multiply figures; Use the fill handle tool to copy formulas; use Paste Special to paste values only without the calculation
- Relative vs absolute referencing: discover why sometimes formula must contain absolute references so that cell references do not change when copying; learn about mixed absolute referencing
- Functions: learn how to use functions to perform calculations, e.g. find the average of a list of numbers, or find the lowest figure in a long list
- Errors: learn about common error messages and circular references
During this hands-on session you will learn how to manipulate the structure of the workbook itself to better suit your requirements e.g. adding and renaming new worksheets, inserting new columns or rows and changing the width/height of those columns/rows. You will learn how to prepare a workbook prior to sharing it online with other users. Finally, you will see how to set up your workbook ready for printing.
1.5 Hours
This course assumes basic understanding in the use of Excel and of creating and editing workbooks. For new users to Excel, prior attendance on the other Level 1 courses would be beneficial.
- File tab: what are all those other options for, and do I need to use them? Your trainer will tell you a little about the other options, but we will focus on the essentials i.e. Info and Options
- Workbooks: Opening multiple workbooks and switching between them; split and freeze worksheet windows
- Worksheets: add, move, copy, rename and remove worksheets; move a worksheet to a new workbook; change tab colour; grouping worksheets and formatting as a group
- Rows and columns: learn how to insert/delete cells, rows and columns in a worksheet; resize rows and columns
- Conditional formatting: apply basic conditional formatting to text or data that meets your set conditions
- AutoFill: create lists of headings or dates quickly using this fantastic feature, you can even create your own custom lists for use again and again; learn how to use Flash fill
- Data: search for text or other content in a worksheet and learn how to replace it with alternative text e.g. change all ‘Qtr1’ to ‘Qtr2’.
- Preparing your workbook for sharing with others: learn how to spell check your workbook; protect formulae from accidental changes; add Comments to worksheet cells; add Headers and Footers to a worksheet; worksheet and workbook protection: adding a password
Excel Intermediate Module 1: Charts, Sparklines and Themes
During this hands-on session, you will learn how to represent your data visually using charts and sparklines (mini charts) in a workbook. You will also learn how to quickly and easily apply professionally designed themes to your charts and data to ensure that colours are co-ordinated, and fonts used consistently throughout the workbook.
1.5 Hours
This course assumes basic understanding in the use of Excel and of creating and editing workbooks. For new users to Excel, prior attendance on the Level 1 Modules is desirable.
- Inserting a chart: select chart headings and data; choose a suitable chart type; explore chart type variations; insert chart on same worksheet as data
- Chart type: learn how to change the ‘chart type’ of an existing chart
- Chart data: add/remove data to/from an existing chart
- Chart elements: identify, format and modify chart elements; format a chart axis; add text labels and other drawing objects to a chart
- Customised charts: add a Trendline to a chart; create a combination chart
- Printing charts: print a chart as part of a worksheet or on its own; print options
- Quick tip: learn how to add a new worksheet and place a chart on it, in seconds!
- Sparklines: learn how to add a mini line, column or win/loss chart next to data to represent its value
- Themes: colour match your data and charts by selecting a theme for the entire workbook; each theme is a co-ordinated combination of colours, fonts and effects which give your work a professional look, with minimum effort
Excel Intermediate Module 2: Formulas and advancing Functions
During this hands-on session, you will learn how to use named ranges to reference data as well as work with logical functions in Excel as well as begin to nest logical functions together and work with lookup functions too.
2 Hours
This course assumes a good knowledge of the use of Microsoft Excel and of creating and editing workbooks. It is desirable although not essential for learners to have already attended the Level 1 Modules, in particular Module 2 - Introduction to Formulae or to have a good understanding of how formulas and functions work in Excel.
- Review of relative and absolute cell references: examples of each; how to use absolute referencing in a formula
- Displaying and tracing formulas: learn how to trace formula precedents and dependents to help when analysing calculations
- Names: apply and define cell and range names; use names in formulae
- Functions: use functions to manipulate, tidy and represent text or numeric data accurately, use Date & Time functions
- Logical functions: Use logical functions to test and summarize data
- Nesting logical functions: Use multiple logical functions together
- Lookup functions: Find out how to use to lookup functions to reference and extract data
Excel Intermediate Module 3: Tables and Data Validation
During this hands-on session, you will learn how to create and utilise the many features of a table as well as learning to create and manipulate custom conditional formatting rules and explore and learn to use data validation and begin to set custom validation criteria.
2 Hours
This course assumes a good knowledge of the use of Microsoft Excel and of using functions. It is desirable to have attended all other Level 2 modules.
- Tables: Create Tables from data sets and interact with data using slicers, work with filters and total row
- Custom Conditional Formatting: Creating custom Conditional Formatting rules, using functions in as conditional formatting criteria
- Basic Validation: Using Data validation, see how to restrict entry to a list; see how to include a drop-down list; understand input messages; understand error alerts and the differences between – stop, warning & information and use validation circles
- Using formula in validation: As this is important for custom validation, we’ll now have a look at some of the functions that can be useful.
- Multiple Validation Lists: How to drive one list by the result of another; and you will learn how to cope with issues that may arise.
Excel Advanced Module 1: Data manipulation and analysis
During this hands-on session, you will learn how to create links between worksheets, between workbooks and even between Excel and external data. We will look at different ways of summarizing large tables of data using a vast array of features and tools. We will also explore Excel’s specialist analysis tools to solve problems and work with creating and summarizing scenarios.
2 Hours
You should have a very good knowledge of the Excel program and ideally should have attended all Level 2 modules.
- INDEX/MATCH: Learn to use INDEX and MATCH functions to overcome VLOOKUP limitations
- Data Consolidation: Bring together sets of data using consolidation tools and create links between data sets
- Working with external data: learn how to import and export data; convert text files to a manageable format; use hyperlinks to link from a workbook to another workbook or to a web page
- Advanced Filter: Filter and extract data on multiple criteria
- Database Functions: Summarize a large data set using database functions, build an interactive mini-dashboard using database functions and other excel features
- Subtotals: Summarize large data sets using subtotals
- Data analysis: use Goal Seek; use Solver; create Scenarios and summary reports
Excel Advanced Module 2: PivotTable & Charts & Dashboards
During this hands-on session, you will learn how to manipulate, analyse and present your data using a PivotTable report and a PivotChart. You will also learn how to use Slicers to filter your PivotTable data and create a dashboard using PivotTables.
1.5 Hours
You should have a good knowledge of the Excel program and should have experience using the program in a work environment for some time and ideally should have attended Level 2 modules.
- Creating a PivotTable: create a PivotTable and use it to analyse data; modify the data source for a PivotTable.
- Changing a PivotTable: format a PivotTable and change options; add calculated fields to a PivotTable.
- Creating a PivotChart: create a PivotChart based on your PivotTable and explore the options available.
- Slicers: add a slicer and format it; use the slicer to filter the PivotTable data.
- Dashboard: Create an interactive Dashboard for your data using PivotTables
Excel Advanced Module 3: Introduction to Macros and VBA
During this hands-on session, you will learn how to use macros to repeat actions and expand upon this with an introduction to VBA (Visual Basic for Applications). This will give you an introduction to programming within Excel to explore the full potential of the software.
1.5 Hours
You should have a very good knowledge of and experience using the Excel program and ideally should have attended all other Level 3 Modules.
- Recording Macros: Learn how to record macros; understand the difference between absolute and relative referencing within macros; understand macro security; and understand the importance of where you store your macros.
- Playing Macros using Buttons: Learn how to play your macros using – the Ribbon, the Quick Access Toolbar and shortcuts; learn how to apply macros to images; learn how to use a button from the form control group; learn how to add commands to the ribbon for later use.
- Overview of the Microsoft Visual Basic for Applications Environment: Learn why VBA utilises a different screen; learn how to navigate through the UI; learn how to set preferences; learn about some of the important tools available.
- Combining Macros using ‘Call’: Learn how to combine macros that you have already created through the Call command; see how the code is run.
- Tidying up Code: Learn how to understand elements of code; understand the difference between recording code and writing it from scratch; learn the structure of objects and procedures within VBA.
- Creating your own Functions: Learn how to create your own functions for use within Excel.
Excel Extras Module 2: PowerPivot Tables and Data modelling
During this hands-on session, you will learn how to you will learn how to access the potential of PowerPivot.
1.5 Hours
You should have a very good knowledge of and experience using the Excel program and it is essential that you have attended the ‘Level 3 Module 2 – PivotTables, PivotCharts and Dashboards’ course as understanding PivotTables is necessary.
- Control Multiple Tables of Data: Learn the difference between PivotTables and the power of using multiple tables within PowerPivot.
- Understanding PowerPivot: You will learn the Data Terminology used; and gain a better understanding of tables, data, and relationships.
- Data Model Layouts: Appreciate data model layouts.
- Using Dates Effectively: Learn how dates impact on the reports created through PowerPivots.
- DAX Functions: Learn what DAX functions are used; why they are important in PowerPivots; and how they can be used..
