About Microsoft Excel Advanced Plus • Level 4
About Excel’s Array course
The greater part of this course will focus on formula arrays. This type of formula is different from what you may understand about Excel. Using relatively simple array formulas you can carry out calculations that would be either complex or impossible using your usual Excel formulas. This course will show you how to get the most out of array formulas, and employ techniques not available within front end Excel. The course is structured but informal and you will be encouraged to ask and answer questions throughout.
Is this course for you?
You must be using Excel on a regular basis and want to use formula Arrays to extend your data manipulation capabilities.
Join this scheduled course at our training centre in Whiteley, near Fareham, Portsmouth and Southampton.
Or, consider onsite training using our computers at no extra cost. £100/delegate or £650 for up to eight delegates on the same course. Minimum charge: £400 plus vat.
- Lunch and Refreshments – Lunch at Caffe Dallucci (Whiteley only)
- Microsoft Training Manual
- Post course training downloads
- Certificate on completion
- Post course support relating to course content
More discounted courses
Aim & Objectives
To create formulas that will solve both simple and complex business problems.
By the end of the course you will be able to:
- Create and edit business formulas
- Create simple array formulas to solve business problems
- Create advanced array formulas to solve challenging business problems
- Troubleshoot arrays
PowerPivot (Analyse large sets of data)
Slicers – PowerPivot
Sparklines (Present your data)
Array and array formulas (Understand the theory)
Multi-cell array formulas – Single-cell array formulas – Array formula Syntax – Rules for entering and changing array formulas
Using array formulas (Some disadvantages)
Array constants (Tools within tools)
One dimensional and two dimensional constants – Horizontal and Vertical constants – Using Constants in Formulas – Array Constant Syntax – Elements in Constants – Naming Array Constants – Troubleshooting Array Constants
Simple array formulas (Simple analytical tools)
Creating Array and Array Constants from existing values – Count characters in a range of cells – Find the N smallest values in a range – Find the N largest values in a range – Find the longest text string in a range of cells
Complex array formulas (Advanced analytical tools)
Sum a range that contains error values – Count the number of error values in a range – Sum values based on conditions – Compute and average that excludes zeros – Count the number of differences between two ranges of cells – Find the location of the Maximum value in a range.