Excel

Module 1 Excel Essentials

One Day Course Code: EXM01

Pre-requisites:

Some Windows knowledge and ability to open, close and save files. Ideally a basic knowledge of another application such as Word.

Aims:

To provide a basic understanding of Excel for new users and to enable them to produce basic but functional spreadsheets.

Content:

• Create, edit and save workbooks; file management through Excel's File Save As…/Open dialogs
• Data types & their entry; navigation; moving & copying data/worksheets
• Using the Task Pane views
• Introduction to SmartTags
• Formatting numbers and text; formatting cells, borders, columns & rows; adjusting column widths, row heights; layout & presentation of spreadsheets
• Printing worksheets small work sheets, adding headers/footers, page numbers
• Autofill and other time saving methods
• Creating formulae to perform basic mathematical operations; troubleshooting formulae; Autosum for totalling only
• Altering your View of the spreadsheet; spellcheck & find and replace
• Graphs using Quick Graph, basic editing and troubleshooting of graphs
• Preview and printing
  
  

Module 2 Excel Improvers

One Day Course Code: EXM02

Pre-requisites:

Some experience of basic Excel. Ideal for self-taught users.

Aims:

To provide a deeper understanding of Excel's arithmetic functions.
To enable users to handle spreadsheets larger than A4

Content:

• Recap on Module One Items if needed
• Printing worksheets larger than A4; controlling what is printed, including repeated row and column headings, and fitting spreadsheets to a specified number of pages
• Autofill and other time saving methods such as custom lists
• Working with SmartTags
• Further work with formulae to perform basic mathematical operations; troubleshooting formulae; recap on Autosum for totalling and looking at its extra features; introduction to other functions such as Average, Count, Minimum and Maximum
• Saving Views; alternative toolbars; splitting and freezing panes; hiding data
• Graphs using Chart Wizard, basic editing and troubleshooting of same
• Preview and printing
• Introduction to naming cells and ranges
  
  

Module 3 Formulae & Functions

One Day Course Code: EXM03

Pre-requisites:

To gain the most from this module delegates will ideally have completed Module 1 or Module 2 training or at least be very familiar with all the topics in them. Module 3 and Module 4 are deemed to be at the same level but with different emphases.

Aims:

To provide an understanding of further Excel functions and Formulae and to greatly improve the power and productivity of spreadsheets.

Content:

• Short re-cap and consolidation of introductory level Excel.
• Layout and planning of spreadsheet construction to speed up production.
• Deeper look at naming cells & ranges, using these within formulae, Paste function and Formula Palette.
• Absolute and semi-absolute cell references.
• Conditional functions: IF, SUMIF and COUNTIF
• Use of the following functions VLOOKUP, HLOOKUP, COUNT, OR, AND, and various text functions.
• Dealing with Rounding errors
• Introduction to Text functions
  

Module 4 Data Management & Presentation

One Day Course Code: EXM04

Pre-requisites:

To gain the most from this module delegates will ideally have completed Module 1 or Module 2 training or at least be very familiar with all the topics in them. Module 3 and Module 4 are deemed to be at the same level but with different emphases.

Aims:

To provide an understanding of Excel Data Management and to greatly improve the handling, manipulation and summarising of data within spreadsheets.

Content:

• Short re-cap and consolidation of introductory level Excel.
• Links to other files and other applications.
• Importing data from Access and other applications.
• Adding refreshable Web Links
• Further formatting, Autoformats and Conditional Formatting; styles.
• Save as HTML documents.
• Charts based on summarised or non-contiguous data; further formatting of charts.
• Introduction to Drawing tools
• Summarise data lists - sorting, subtotalling, outlining, filtering; validation of data and how to ensure correct data is entered.
• Introduction to Pivot Tables
  

Module 5 Data Analysis

Pre-requisites:

To gain the most from this module delegates will ideally have completed Modules 1-4 training or at least be very familiar with all the topics in them. Module 5 and Module 6 are deemed to be at the same level but with different emphases.

Aims:

To provide an understanding of further data management and to greatly improve the management, control and analysis of data within spreadsheets.

Content:

• Short re-cap and consolidation of intermediate level Excel
• Managing multiple worksheets and multiple workbooks
• Linking cell data across worksheets and workbooks
• Combined LOOKUP functions
• Using auditing tools
• Sharing workbooks on the network; merging workbooks & tracking changes
• Database functions - DCOUNT, DCOUNTA, DAVERAGE, DMIN, DMAX, DSUM
• Links and Queries - to other files and other applications (writing in MS Query; OLAP Cubes)
• Complex/nested Text functions
  

Module 6 Excel Productivity

One Day Course Code: EXM06

Pre-requisites:

To gain the most from this module delegates will ideally have completed Modules 1-4 training or at least be very familiar with all the topics in them. Module 5 and Module 6 are deemed to be at the same level but with different emphases.

Aims:

To provide an understanding of advanced Excel tools and to greatly improve the speed of productivity and efficiency within spreadsheets.

Content:

• Short re-cap and consolidation of intermediate level Excel
• Pivot tables - using, formatting, grouping & creating charts from Pivot Tables
• Automating tasks with macros, introduction to debugging and editing macros
• Assigning Macros to buttons
• Projections and "what if" analyses using Goal Seek, Scenarios and Solver
• Introduction to Data Analysis tools
  

Please note: IPS offers: a follow-on course 2 day Excel/VBA; customised workshops dedicated to helping you complete your own projects. We can also develop databases for you. Please ask for details of any of these services.