Advanced excel formula and functions Excel has its immense purposeful applications. 95% of the users apply the basic form. There are functions and advanced excel formula that can be used for complex calculations. Excel users both inexperienced and experienced who don't feel confident about Microsoft Excel Advanced formulas and Functions Anyone who uses Excel for work or personal needs such as Sales Manager, Students, Individual, Accountants,Auditor, Financial Analyst, Information Clerk. Mastering excel. Advanced Excel skills are a high level of proficiency with the Microsoft Excel spreadsheet software program. Whereas basic users of Excel can use it to create spreadsheets, perform mathematical operations, and make simple tables and charts, advanced users can manage and analyze complex datasets, derive meaningful insights from them, and create persuasive data visualizations for.

This practical, 3 day Microsoft Excel Advanced Training Course focuses on the MS Excel skills that the typical business user would require.

It is run on a monthly basis in Midrand to facilitate delegates from Pretoria and Johannesburg.

Next course : 10-12 May 2021
Duration – 3 Days
Skill Level >3.5

It is suitable for Intermediate and Advanced Excel users, but the best way to check if this is the best MS Excel course in Johannesburg for you is to do the Free MS Excel Skills Assessment first.

The next available course date is 10-12 May 2021. For more dates have a look at the training calendar for the various MS Excel courses we offer. We also offer virtual MS Excel courses.

Advanced Ms Excel Pivot Table

Some of the key outcomes of the 3 Day Advanced MS Excel public course will include:

Using the new tools that Microsoft has introduced
Easily cleaning up data received to report on
Understanding and making use of Pivot Tables, to turn data into reports
Reporting findings graphically via charts and other visualisation techniques
Ability to create an automated reporting pack that changes month on month with no effort
Perform partial lookups where the words aren’t exactly the same
Get dates to work how you expect them to.
For a full listing see the contents below

Advanced Ms Excel Formulas

Delivery Methods- Public Microsoft Excel Advanced Training Course

Live MS Excel Training options

Public courses for Johannesburg and Pretoria
Onsite- We can provide the training onsite to corporate groups- for more information click the request for information button below.
Virtual training to your staff (see more)

Online MS Excel Intermediate Training

If you are not in South Africa, or prefer to do the course online, have a look at combining the the Online MS Excel Intermediate Course and the Online MS Excel Advanced Course

Course Outline

Ultimate Excel Tool

Flash Fill- Your new best friend?

MS Excel Tips and Tricks

Viewing many parts of the spreadsheet (at the same time)
Don’t hide rows- group them rather
See sheet names in portrait style
Selecting ranges with mouse and arrow keys- short cuts
Enter date and time stamps- unchanging
Doing Quick Calculations in a spreadsheet
The Quick Access Toolbar

Filtering and Sorting

Data sorting-Revision, colour and icon sorting
Sorting the columns instead of the rows
Create a custom sorting list
Data Filtering
Remove rows with the Filter
Remove duplicate rows
Use Advanced Filter for repeating filters

The Specials- GOTO and Paste

GOTO Special- find comments, values, formulas and more
Find errors, conditional formatting and data validation
Copy Visible Cells Only
Fill in all the blanks
Paste Special- Values, Formulas with/without formatting
Paste Special- convert negatives to positives
Switch rows to columns and columns to rows
Paste without overwriting blank cells
Re Use the Format Painter

Tools for text

Find Replace- standard uses
Find Replace- Change formatting on many cells
Remove between brackets or characters
Single Column to Multiple Columns
Import CSV’s into the correct columns
Find partial names or between characters (fuzzy logic)
Change the $ signs on multiple cells with FIND/ REPLACE
Import CSV but correct for ‘numbers’ that start with zero e.g. credit card numbers

Pivot Tables

Required data format for Pivot tables
Create a Pivot Table- Step by Step
Inserting Values into a Pivot Table
Enhancing the values e.g. percentage of another column
Show the details of a number in the Pivot Table
Laying out the Pivot Report in completely separate columns
Working with the rows in a Pivot Table- dropdown
Working with the rows in a Pivot Table- field settings
Normalize Pivot Reports to use elsewhere as a copy paste
Working with the Columns in a Pivot Table
Working with the Filter in a Pivot Table
Automatically duplicate your Pivot Tables with different selections
Slicers in a Pivot Table
Control all Pivot Tables and Charts at once
Timelines in a Pivot Table
Grouping Dates into days, months, quarters and years in a Pivot Table
Grouping Dates into weeks in a Pivot Table
Grouping Text in a Pivot Table
Grouping Numbers in a Pivot Table
Pivot Charts in Excel
Problems with grouping in Pivot Tables
Protect the source data of a pivot table
Add a calculation INSIDE a Pivot Table
Conditional Formatting within a Pivot
Get information out of a Pivot into a cell
Switching off the GETPIVOTDATA
Add up ITEMs within a Pivot Table Field

Conditional Formatting

Change cell colour based on value
Change color based on benchmark in another cell
Highlight all above average cells
Add comparative bars into cells
Create heat maps in Excel
Add risk robots, milestone, up and down icons to Excel cells
Control the heat maps, icons and data bars in conditional formatting
Conditional Formatting- Linking to different cells
Control what is entered in an Excel cell- Data Validation
Control the inputs in a cell based on another cell

Function and Formula Basics

Tip- Opening Frequent files
Enter formula into many cells (at the same time)
Function Wizard
Auditing Toolbar
Referring to fixed cells
Using the F4 shortcut for $ signs
Dollar Sign ($A$1) Examples
The basic “IF” function
How to (safely) build a nested IF formula
IF this AND this AND this is True
See the numbers behind a formula
Change formula across many sheets at the same time
Update the calculations on only one sheet (for large spreadsheets)

Text Functions

Fitting text inside a cell
Quick entry of rows of data
Quick Drop Down List
Printing Tips
Get Excel to read out the text and numbers
Join text from many cells
Extract parts of the text from cells
Force Excel to see the value (instead of text)
Change text to lower, UPPER or Proper case
Remove unnecessary spaces
Create a find/ replace formula
Find where a certain character is in a cell
Force excel to treat number as text

Key Lookup Functions

VLOOKUP explained in simple terms
Build your first VLOOKUP
The many uses of VLOOKUP
Vlookup approximate match (True instead of False)
Vlookup partial text from single cell- fuzzy lookup
Automatic Sorting in Excel
Handling Error Messages- Remove the impact of #NA, #DIV/0! and more

SUM, COUNT, AVERAGE IFS

Sum, count or average if certain cells match
Sum, coun