The Advanced Excel course covers the various tools and formula you should know in your daily Excel work. The Advanced course focuses a bit more on the use of formula in Excel.
Advanced Excel Course Details
141 lessons
240 video clips
68 example exercises with detailed video solutions explaining exactly how you should have got to the answer.
After the lessons and exercises you will be able to:
Lookup partial matches like a surname in a list of full names
View sheets from the SAME WORKBOOK side by side
Aggregate sheets by moving them around. Include or exclude divisions by moving the sheets to the appropriate area
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
Add up all similar items even if they are not in order.
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
(Easily) reconcile lists. What items are missing, what is matching and what appears more than it should?
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)?
Create a 2 way lookup. Find the exact matching numbers in a matrix
Lost the leading zeros? Put them back in
Nested IF’s a problem. Learn how to create them in the easiest, safest way.
Learn complex data cleanups. Remove brackets and whatever is in between (even if it is not consistent)
Create intelligent dropdown lists that check whether the user is sure about their entry
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.
Categorise data WITHOUT using complex Nested If’s.
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.
Tired of connecting your summary sheets to each sheet individually. Use a formula to refer to whatever sheet you type.
See the numbers behind a formula without scrambling through the spreadsheet. No need to pull out your calculator to ‘check’ Excel.
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
Make spreadsheets easier to read by changing cell colours where there is a significant change in data.
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)
Show where the business is trending towards and forecast out based on the data you have.
Get Excel to grow its charts and formula when you add more data. You don’t have to worry any more if the chart/ pivot/ vlookup/ etc takes all the new data into account. Excel will just adjust it for you
Make a line chart change colour at a significant point e.g. actuals in blue and budget in red.
