Improve staff productivity with a Microsoft Excel course. Our programmes are expert lead face-to-face sessions delivered in-house in London and across the South and East of the UK.
Increase the efficiency of your teams with our comprehensive range of Excel courses. With all of our programmes we include the following:
This one-day Introduction to Excel course equips delegates with the fundamental skills required to use Microsoft Excel productively.
Delegates will develop the skills required to build a worksheet including the principals of formulae & functions, the manipulation of data, printing and graphically representing data through charts.
Delegates will gain an appreciation of how Microsoft Excel can be used in typical business scenarios through the creation of several worksheets and may include budgeting, sales analysis, entry-level bookkeeping and staff scheduling.
Emphasis will be placed on keyboard and mouse shortcuts to ensure all delegates work as efficiently as possible.
Basic Excel formulae will be used to summarise data in a worksheet. Various number & layout formatting techniques will be applied to ensure the worksheets have a professional feel about them. Several different chart types will be used to explore ways of analysing data through graphical representation.
Screen Familiarity
• Workbook principles
• Screen layout
Working with Cells
• Use undo and redo
• Selecting cells
• Go to a specific cell
• Find and replace
• Cut, copy, paste and move cells
Autofill
• Copying cells
• Work with series (AutoFill)
Working with Data
• Enter text, formulae, and numbers
• Edit cell content
• Clear cell content
Foundation Formulas and Functions
• Mathematical Operators (+. -, *, /)
• BIDMAS (order of operations)
• Enter and revise formulas
• Use AutoSum
• Use basic functions (AVERAGE, SUM, COUNT, MIN, and MAX)
• Use paste to insert a function
• Relative and absolute cell referencing
Formatting Worksheets
• Apply font styles (typeface, size, colour, and styles)
• Modify row and column size
• Modify alignment of cell content
• Use the format painter
• Apply cell borders and shading
• Merge cells
• Wrap text
• Clear cell formats
• Apply predefined number formats
• Insert and delete rows and columns
• Insert and delete selected cells
Charting
• Keyboard Shortcut (F11)
• Types of chart
• Column, line and pie charts
• Use the chart wizard
• Preview and print charts
• Simple formatting
• Chart options
Printing
• Preview and print a selection, worksheets, workbooks
• Change page orientation and scaling
• Set page margins and centering
• Insert and remove a page break
• Set print and clear a print area
• Set up headers and footers
• Set print titles and options
Working with Lists (Time Permitting – Overview)
• Setting up a list in Excel
• Field headings
• Sorting
• Searching with Autofilter
I have learned skills I did not have before this training, I have gained knowledge on formulas and how to use them. The course was clear and easy to understand, Mark explained everything in a way that was easy to digest.
This Intermediate Microsoft Excel course is aimed at delegates who are using Excel to maintain lists of data and carry out some form of analysis on them. It is designed for people who need to know how to fully understand and manipulate lists to maximum effect. This will be done by covering data manipulation and filters, the more advanced mathematical and statistical analysis of data and ultimately the use of Pivot Tables within Excel.
– For delegates who use Excel to store and analyse data
– To create and maintain a list of data that makes lists work for you
– Use filters to quickly select sets of data within lists
– Manipulate data into useful elements e.g. separate data from a single cell
– See the magic of Pivot Tables and the speed of which they analyse and present statistical information.
List management
• Excel data list rules
• Use data forms
• Custom lists
Print options
• Set print titles and options
• Headers and footers
• Page breaks
Worksheet management
• Freeze / unfreeze rows and columns
• Arrange windows of one or more workbooks
• Inserting, moving, naming, hiding and deleting worksheets
• Copying sheets to other workbooks
Working with named ranges
• What are named ranges
• Create, define and delete named ranges
Sorting, filters and outlines
• Perform single and multilevel sorts
• AutoFilter and custom filters
• Use grouping and outlines
Subtotalling
• Use subtotalling
• Data subtotals versus =subtotal function
Data validation
• Applying data validation criteria
Conditional formatting
• Apply conditional formatting
• Cell value
• Formula is
Pivot tables
• Creating a pivot table with the Wizard
• Adding / removing items from the row and column areas
• Adding filters with pages
• Summarising numbers in the data area
• Pivot the row and column headings
• Refreshing the pivot table
• Reducing disk space
• Selecting different functions
• Format with autoformat
• Display grand totals
• Drill down into the data behind a number
Beyond basic functions (optional and time dependant)
• Standard date functions
• If functions
• Example text function (left, right, mid and concatenate – &)
• VLookup and HLookup
This Advanced Microsoft Excel course is designed to take delegates through performing complex data analysis, using complex formulae, importing and exporting of data and introducing them to recording macros and editing in the Visual Basic environment.
It is recommended that participants should have attended or be confident with the foundation and intermediate level subjects.
– For delegates who use Excel to store and analyse data
– To create and maintain a list of data that makes lists work for you
– Use filters to quickly select sets of data within lists
– Manipulate data into useful elements e.g. separate data from a single cell
– See the magic of Pivot Tables and the speed of which they analyse and present statistical information.
Consolidate Basics
• Overview of basic excel functions
• Formulae and BIDMAS rules
• Data entry and conditional format
Data Validation
• Types of validation
• Setup rules for data entry
• Display help and error messages
• Create drop down menus
Lookup Function
• Horizontal
• Vertical
• Across worksheets
• Automating lookup attributes
If Statements
• Basic If attributes
• Nested If statements
• ‘AND’ and ‘OR’ logic
Protection
• Protect worksheet or workbook
• Unlocking variable cells
• Allow formatting while locked
Beyond Basic Functions
• Overview range names
• Date functions and formats
• Index() & Match()
• Sumif, Countif functions
• Text functions – Left(), Mid(), Len(), Upper(), Proper(), Lower()
Views and Reports
• Add scenarios
• Grouping and outlining
Goal Seeker
• Set up options
• Implementing a goal seek
Importing Non-Excel Data
• De-limited data
• Fixed length data
Combine Formula
• Concatenation
• Extract data with find()
Shared Workbooks
• Overview sharing options
• Track changes overview (dependant on networking permissions)
Introduction to Macros
• Overview of macros and VBA
• Record a macro
• Relative and absolute cells
• Editing macros using Visual Basic (VBA)
• Assigning macros to buttons
This course is aimed at those who are using Excel to maintain lists of data and carry out some form of analysis on them. It is designed for people who need to know how to fully understand and manipulate lists to maximum effect. This will be done by covering data manipulation and filters, the more advanced mathematical and statistical analysis of data and ultimately the use of Pivot Tables, Macros and Charts within Excel.
• For delegates who use Microsoft Excel to store and analyse data
• To create and maintain a list of data that makes lists work for you
• Use Filters to quickly select sets of data within lists
• Manipulate data into useful elements e.g. separate data from a single cell
• See the magic of Pivot Tables and the speed of which they analyse and present statistical information.
Delegates are required to have a good working knowledge of Excel with the ability to easily create professional looking workbooks from scratch.
List management
• Data forms
• AutoFilter
• Sorting data
• Creating sub-totals
Pivot Tables
• Creating a Pivot Table with the Wizard
• Adding and removing items from rows and columns
• Adding filters with pages
• Summarising numbers in the data area
• Pivot the row and column headings
• Refreshing a Pivot Table
• Reducing disk space
• Selecting different functions
• Format the data and autoformat
• Show data options
• Display grand totals
• Change the sort order of the ‘Rows’, ‘Columns’ or data
• Grouping
• Multiple row and column items
• Drill down into the data behind a number
• Use hide and show detail
Macros (recorded)
• Record macros
• Personal macro workbooks
• Run macros
• Deleting macros
• Assign a macro to a command button or a menu item
• View VBA code
• Edit a recorded macro
Creating charts
• Chart wizard
• Chart types
• Customising a chart
• Formatting
Advanced charts
• Error bars and trend lines
• Embedding
• Dual Y-axis
Beyond basic functions (optional and time dependent)
• Basic If functions
• Example text function (Left, Right, Mid and Concatenate – &)
• HLookup and VLookup
This Formulae and Functions Microsoft Excel course will provide a thorough grounding in the use of calculations in Excel. To do this effectively, you will learn the fundamentals of calculations: creating simple formulae and the use of Excel’s in-built function formulae. This will lead onto the use of statistical and date functions and ultimately the use of logical “IF” statements. Calculations will be used in single worksheets and across multiple worksheets and workbooks. The delegate will also gain a greater understanding of how spreadsheets can and are being used in in a variety of industries.
Delegates will:
• Focus on the calculation (formulae & function) capabilities of Excel
• Speed up your productivity and become familiar with the Excel function library
• Using named ranges to make it easier to refer to cell references
• Follow calculations others have created and subsequently track and resolve errors
• Introducing conditional statements in formulae.
Foundation formulas (recap)
• BIDMAS (order of operations)
• Enter formulas in a cell and use the formula bar
• Revise formulas
• Use AutoSum
• Use basic functions (AVERAGE, SUM, COUNT, MIN, and MAX)
• Use paste to insert a function
Autofill
• Copying cells
• Create a custom list
• Copying formats
Number formats
• Apply predefined number formats incl. decimal places
• Create custom number formats
• Date formats
• Relative, absolute and partial cell references
Linking formulae and worksheets
• Inserting, moving, naming, hiding, deleting worksheets
• Link worksheets and workbooks
• Using 3-D references
• Edit links
Working with windows
• Working with and arranging multiple windows
• Freezing panes
• Splitting windows
Working with named ranges
• What are named ranges
• Create, define and delete named ranges
• Use a named range in a formula
• Paste and apply named ranges
Auditing a worksheet
• Trace precedents (find cells referred to in a specific formula)
• Trace dependents (find formulas that refer to a specific cell)
Creating a template
• Protecting cells
• Protecting sheets and workbooks
• Saving a template
Beyond basic functions – date
• TODAY(), NOW()
Beyond basic functions – logical
• Use logical functions (IF)
• Using nested IF statements
• Applying AND / OR
Annotating your work with comments
• Create, edit, and remove a comment
• Display and print comments
Printing
• Print titles
• Page breaks
• Headers and footers
This Microsoft Excel VBA course is aimed at those who need to develop integrated spreadsheet solutions using Microsoft Excel. Typically, these may be senior I.T. staff or “super users” who need to be able to produce in-house “turnkey” systems.
Prior attendance on either the Advanced Excel or Formula and Functions programmes, or a thorough knowledge of Microsoft Excel, is recommended. The course is 100% hands-on and, although no previous programming experience is required, programming in the VBA environment forms much of the course so an understanding of basic programming techniques would also be desirable.
Excel Macro’s
• Recording visual basic “macros”
• The visual basic environment
• Understanding the visual basic language
• Assigning code to buttons, menu’s and toolbars
Writing visual basic code
• Procedures sub and function statements
• Private procedures
• Named arguments
Variables
• What is a variable?
• Dim, public, private, static and constant statements
• Variable scope
• Naming conflicts
User defined functions
• Creating a function
• Function arguments
• Return values
Visual basic objects
• Properties
• Methods
• Object and common collections
• Object containers
• The object browser
• Performing multiple actions on an object
Control structures
• If…then…else
• Select case
• For…next
• Do…loop
User interface objects
• Dialog boxes
• Placing controls on a worksheet
• The forms toolbar
The Excel menu system under VBA control
• Testing and debugging applications
• Starting, halting and tracing execution
• Resolving compile and run-time errors
• Working with breakpoints
• Using the debug window
Error handling and trapping
• On error statement
• Excel built in error values
I’ve learned everything I needed to know. I’ve achieved so much confidence in using pivot tables. Mark was an amazing trainer and the course was really well structured.
Where are we?
Headquarters
Acacia Learning Ltd
26 Havelock Walk
London
SE23 3HG
United Kingdom
Tel: +44 (0) 208 239 1323
FREE COURSE BROCHURE
“As an Acacia student please login with the username and password provided to you in your confirmation pack to access all the information:
Please click below for Acacia Learning CIPD Student Hub (access by invitation only)”