Details about the Intermediate MS Excel Online portion of this bundled course. With over 133 lessons, 260 video clips and 48 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course builds on the Fundamental knowledge from the beginner course and teaches you to solve problems in Excel with a particular focus on the tools available.
Details about the Online Beginner MS Excel portion of the Course. With over 60 lessons, 130 video clips, and 28 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course will give you the fundamentals to make sure it is easy to learn anything about Excel.
Add commentary to labels for older versions of Excel
Add commentary to labels and call outs
The basic “IF” function
Use Excel's inbuilt tools and functions
What can go wrong with hidden rows and columns
Don't insert a cell (or at least be very careful)
The risks with inserting columns and rows (and how to spot them)
What can go wrong with a copy/ paste
Determine what day of the week a date is
What week number is a date
Pull the day, month or year out of the date
Errors that arise from linking spreadsheets together
Build your first VLOOKUP and understand what each component means and how to use it.
Vlookup explained in simple terms so that you obtain a fundamental knowledge that you can apply to the more advanced uses of VLOOKUP
Remove unnecessary spaces
Force Excel to see the value (instead of text)
Change text to lower, UPPER or Proper case
Extract parts of the text from cells
Join text from many cells
IF this then that Examples
Dollar Sign ($A$1) Examples
Formula Auditing Examples
Enter formula into many cells (at the same time)
The problem with the Protect Sheets is that it does apply some limits and some users want everything to be open EXCEPT that they don't want users to insert or delete rows and columns (this is a very common cause of spreadsheet errors or 'templates' corrupting). This is a trick to disable the insert/ delete option without protecting the sheet and no VBA.
When you protect a sheet you typically stop users from changing your formulas. However, you can also control whether a user can see a formula on a cell. If you want the formula to be hidden you need to follow a slightly different process.
This is a trick to be able to send out one spreadsheet which contains everyone's information, but set it up so that a single person can only see their data and not the other users.
SUMIFS et al Examples
SUMIFS et al Examples- sales by area, recon lists, duplicate bank accounts
SUMIFS et al Examples- count profitable months, stop duplicate entry
SUMIFS et al Examples- multiple conditions, duplicate bank account numbers
VLOOKUP Examples- fuzzy logic
Sum, count or average if certain cells match
Sum, count or average cells e.g. bigger than
Sum, count or average with partial matches
Sum, count or average with many conditions
Replace text in excel using formula
How to use vlookup approximate match to categories data, use banding and get Excel to guesstimate results
VLOOKUP Examples- categorise, bands
Vlookup partial text from single cell- fuzzy lookup
Downloads of the Follow Along and Exercise files
Track where a formula comes from
How to use this course
Function Wizard- the Excel cheat tool
How to add a new calculated field to a pivot table so that you can create calculations directly into the Pivot Table