MS Excel Advanced training course

MS Excel Advanced training courseThis practical, 2 day MS Excel Advanced training course focuses on the Excel skills that the typical Advanced user would require.

Some of the key outcomes will include:

  • 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
  • Improve your data visualisation with charts and other tricks
  • Get dates to work how you expect them to.

Delivery Methods- Advanced MS Excel

Live MS Excel Advanced Training

Online MS Excel Advanced Training

Course Outline

Ultimate Excel Tool

  • Flash Fill- You definitely need to know this
Split key text into their own columns

Tips and Tricks- Advanced

  • View multiple cells/ sheets/ spreadsheets at the same time
  • Adding cells by moving sheets around
  • Useful for the aggregation trick
  • Custom Format a word into a number cell e.g. 100 tons
  • Custom Format numbers into thousands or millions
  • Tables- Critical for Excel use going forward
View sheets from the SAME WORKBOOK side by side

 

Format your numbers in a more meaningful way. Add metrics to numbers, change the colour negative numbers turn, format numbers into thousands, millions or billions.

 

Aggregate sheets by moving them around. Include or exclude divisions by moving the sheets to the appropriate area

Filtering and Sorting

  • Filtering and Sorting- make sure you are comfortable with the lessons taught in the Intermediate MS Excel course as this is assumed knowledge.
  • Create an Automatic Filter for reuse

Tools for text

  • 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
Learn complex data cleanups. Remove brackets and whatever is in between (even if it is not consistent)

 

Replace unwanted parts of cells

 

Split columns but decide how it must handle leading zeros. Bank Account numbers/ Employee Numbers/ ID numbers- you must keep the zeros. Payments- you can lose the zeros

Pivot Tables

  • Make sure you are comfortable with the Pivot Table lessons taught in the Intermediate MS Excel course as this is assumed knowledge.
  • 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
Pull out only the parts of the Pivot Table that interest you

 

Building formula outside the Pivot Table looking into the Pivot Table? Rather build the formula directly into the Pivot Table

Function and Formula Basics

  • Function Wizard- why you should use it
  • Trace where a formula comes from and goes to (even across sheets)
  • Using Absolute and Relative Referencing ($ signs)
  • Shortcut to add $ signs to cell references
  • How to (safely) build a nested IF formula
  • IF this AND this AND this is True
Nested IF’s a problem. Learn how to create them in the easiest, safest way.

Formula Tips and Tricks

  • 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)
  • Trick Excel into Changing where Absolute references ($) look
See the numbers behind a formula without scrambling through the spreadsheet. No need to pull out your calculator to ‘check’ Excel.

Text Functions

  • Text Functions- make sure you are comfortable with the lessons taught in the Intermediate MS Excel course as this is assumed knowledge.
  • Create a find/ replace formula
  • Find where a certain character is in a cell
  • Force excel to treat number as text
Lost the leading zeros? Put them back in

Key Lookup Functions

  • Key Lookup functions- make sure you are comfortable with the lessons taught in the Intermediate MS Excel course as this is assumed knowledge.
  • Vlookup approximate match (True instead of False)
  • Vlookup partial text from single cell- fuzzy lookup
  • Automatic Sorting in Excel Exercise
  • Handling Error Messages- Remove the impact of #NA, #DIV/0! and more
  • OFFSET
  • OFFSET- Simple Example
  • OFFSET- Time Lag Example
  • OFFSET- Allow Insert Rows Example
  • OFFSET- Monthly Management Reporting Example
  • MATCH and INDEX as an alternate VLOOKUP?
  • Lookup across rows and columns (at the same time)
  • Lookup to the left instead of right (like VLOOKUP)
  • Get Excel to find the columns you want to bring back from a lookup
  • MATCH, INDEX, HLOOKUP and OFFSET Combined
  • Lookup on different sheets
  • INDIRECT Example to summarise divisions across sheets
  • Transpose rows to columns but keep them linked
Create an automated reporting pack. Type in the current month and it will pull through the actuals for the month, budget and perhaps prior year as well as (automatically) pull through the matching year to date numbers. NO OVERWRITING OF FORMULAS

 

Create a 2 way lookup. Find the exact matching numbers in a matrix

 

Tired of connecting your summary sheets to each sheet individually. Use a formula to refer to whatever sheet you type.

 

Categorise data WITHOUT using complex Nested If’s.

 

(Easily) reconcile lists. What items are missing, what is matching and what appears more than it should?

 

Lookup partial matches like a surname in a list of full names

SUM, COUNT, AVERAGE IFS

  • Sum, count or average if certain cells match
  • Sum, count or average cells e.g. bigger than
  • Sum, count or average with many conditions
  • Sum, count or average with partial matches
Add up all similar items even if they are not in order.

Date functions and issues

  • How Excel Handles Times and Dates
  • Create a VALID Excel date with a day, month and year number
  • Calculate the proper end of month
  • When is the next workday e.g. 30 days from invoice date
  • How many working days between 2 dates
  • How many months/years between two dates
  • Force Excel to format a date to your version

Conditional Formatting

  • Conditional Formatting- make sure you are comfortable with the lessons taught in the Intermediate MS Excel course as this is assumed knowledge.
  • 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
Want to easily see where the numbers change month on month? Let them automatically change colour if the previous cell is a different number to the current cell.

 

Make spreadsheets easier to read by changing cell colours where there is a significant change in data.

 

Create intelligent dropdown lists that check whether the user is sure about their entry

Charting In Excel

  • MS Excel Charting- make sure you are comfortable with the lessons taught in the Intermediate MS Excel course as this is assumed knowledge.
  • Paste a linked picture of data onto a chart (or anywhere)
  • Sparklines for trend analysis
  • Copy the format of one chart to another chart
  • Remove Zeros from chart labels
  • Combine a column with a line chart (or other combinations)
  • Stop charts stretching when the column width changes
  • Customise the series marker to your own image
  • Add linked commentary directly to the chart
  • Add commentary to the axis
  • Add commentary to labels and call outs
  • Add commentary to labels for older versions of Excel
  • Hiding a series with NA()
  • Create a waterfall chart (pre Excel 2016)
  • Create a waterfall chart (Excel 2016 and higher)
  • Understand and use XY charts e.g. Risk Register
  • Change the default colour scheme in charts
  • Create a chart template for re-use
  • Add a trendline to a chart
  • Benefit of using a table for chart data
  • Link Excel chart to PowerPoint or Word
Learn how to ‘Format Paint’ a chart. Find the chart you like and simply absorb its format

 

Build a chart series selector. The user can switch on and off any series and the chart will adapt. Great for dashboards

 

Get fancy with your charts. Trick Excel into creating some of those fancy charts you have seen (it is much easier than you think)

 

Add shading to a chart (WITHOUT manually colouring it in) to show where the good and bad areas are

 

Need to explain what happened. Learn how to create a waterfall chart both in the new version of Excel (which has waterfall charts) and the older versions (where you have to trick Excel)

 

Make a line chart change colour at a significant point e.g. actuals in blue and budget in red.

Protecting spreadsheet information and formulas

  • How to protect an Excel Spreadsheet from opening
  • Allow Spreadsheet to be viewed but not changed (read only)
  • Protect only certain cells/ formula from everything except input changes
  • Lock cells but allow colour formatting or comments
  • Lock Cells but allow insert or delete of columns/ rows
  • Lock Cells but allow column/ row size change
  • Protect cells but allow filter/ pivot table/ sort
  • Hide formulas in Excel
  • Hide and protect a sheet in a spreadsheet
  • Allow only certain values into a cell
  • Protect cells with different passwords to allow levels of authority
  • Trick: Allow user to only see their information in Excel
  • Trick: Protect rows and columns without protecting sheet
  • Some Useful Tools when protecting spreadsheets
Add various levels of protection to a spreadsheet. Can the user change the formula (probably not)? What about the inputs (probably yes)? What about the format of the cells (it depends)?

 

Feedback & Who Should Attend

  • I’m glad I invested my money and time on this course, it was worth it.I also like the fact that i will be assisted even after completing the course. I walked out a different lady, just confirming what De Wet said.- Kefiloe M.
  •  It dealt with my daily challenges at work- IS Management Accountant.
  • There was nothing I did not like about the workshop. Adrian was fantastic- Accountant.
  • This course really add significant value to my life as a data analyst- Lonmin
  • … first the assessment is a good tool to determine that the trainee is allocated to the correct training, secondly, although the training is intense the content is brilliant and very well presented. Being able to express real live occurrences during the training makes for understanding of applications. All Krones personnel that have attended this training has had their skillset enhanced. Being proficient in excel has allowed the flow of work from department to department to become more and more efficient and not to mention, look more professional. Audit Excel is my only choice for Excel Training.- Debbie L- Krones

Typical Attendees

  • All persons using Excel spreadsheets. Professions that have attended include accountants, actuaries, auditors, business analysts, chartered accountants, civil engineers, construction, consulting, corporate finance, engineers, farming, finance, hr function, landlords, lawyers, quantity surveyors, research analysts, retail, scientists and engineers

Pre Requisite (s)

In order to do this course, we recommend that all delegates have an Intermediate understanding of MS Excel, in that they are using MS Excel in their daily activities and want to work quicker, smarter and safer.

We recommend that all delegates should, using our free MS Excel Skills Assessment, have a skill score of  >4.5 with a level of Intermediate or Intermediate-to-Advanced (get your delegates to do the  free MS Excel Skills Assessment to make sure this is the correct level of training).

This is a very hands-on, non-intimidating, course – delegates work through various exercises under the supervision, and with the help, of the facilitator to get to grips with the nuances of Excel’s functions.