An often overlooked area of Excel is its power to derive answers from the data entered. This takes Excel out of the realm of a mere spreadsheet and into the realm of a “modeller” – the ability to build analytical solutions and conclusions.
Course Benefits
Upon completion of this workshop, participants will be able to;
- This course aims to empower existing Excel users who wish to take their skills further and venture into the areas of Data Modelling, Financial Expositions, Statistical Summaries and Scenario Management.
Target Audience
This course would benefit anyone who needs to apply advanced analysis techniques to more complex data sets.
Course Outline
Module 1: Custom Formatting
- Creating new styles
- Applying & modifying styles
- Merging styles
- Complex Number Formatting
- Number format codes
- Creating custom number formats
- Displaying leading or trailing zeros
- Customize currency, date, time, scientific & text formats
Module 2: Advanced Data Manipulation
- Rounding Numbers
- Rounding to significant figures
- Rounding to nearest multiple
- Rounding dollar values
- Extracting integer values
- Rounding to even or odd integer
- Manipulating Text
- Matching cell contents
- Joining cells
- Changing cases
- Extracting character
- Text length
- Working with Dates & Times
- Calculate the number of days. months or years between dates
- Offsetting a date
- Calculate the time difference
- Rounding time values
- Counting, Summing & Averaging Techniques
- Conditional counting & multiple criteria
- Counting frequent entries
- Conditional summing & multiple criteria
- Conditional averaging & multiple criteria
Module 3: Specialized Lookups
- Two Way Lookup with INDEX & MATCH
- Multiple Column Lookup
Module 4: Financial Functions
- Loan Calculations
- Investment Calculations
- Depreciations
Module 5: Statistical & Array Functions
- Constructing Array Formulas
- Ranking
- Frequency Distribution
- Histograms
Module 6: Advanced Data Analysis
- Analysis ToolPak
- Regression
- Sampling
- Working with Trend Lines
Module 7: Data Modelling
- Building Spreadsheet Models
- Optimising Spreadsheet using Solver
Module 8: Introduction to Macros
- Recording a Macro
- Running Macros
- Assigning Macros
Module 9: Forms in Excel
- Understanding Excel’s Forms & Form Controls
- Using the Form Controls
- Text boxes & labels
- Checkboxes & option buttons
- Spinner & combo boxes
- Drop-down lists
- Buttons
- Form Calculations
Available Course Sessions
Please click here to stay updated on upcoming sessions.
Trainer Profile
Microsoft Certified Trainer
All of our Associate Trainers offer our high level of service therefore are subject to regular peer assessments and interview prior to joining our Institute. They must all be qualified and have relevant experience to an agreed level and are subjected to continual audit and evaluation. Strict internal assessment is implemented as an integral part of our quality control mechanism.
