Searching for a free Excel online course? Check out my collection of Excel tutorials that will help you go from zero to ninja in Excel.
Best I could find
As there are lots of free tutorials about Excel on YouTube what I did in this article was to put the best together into a framework of most important topics. I did all the search for you, you just have to hit play. π
My experience
My name is Radu, I am based in Romania and I teach Excel for a living. As I am publishing this article I have more than 800 classroom training hours on Excel only.
I made the list bellow based on my experience.
Section 1: Working with tables in Excel
Let’s start with the basics. If you’re some kind of Excel ninja and tables have no secret for you, you can skip to the next section. There are however things to learn here also.
Cell formatting
If you’re just getting started with Excel you will want to start with the basics, formatting cells. You will find out in the video bellow how to change the font size color, background, etc.
Excel data types
The building block of an Excel spreadsheet is the cell. You will see in the video bellow that there are multiple data types of cells in Excel.
I know that the audio is not great … best that I could find.
Working with filters + sorting
Filters are an important feature in Excel because they allow you to see only what matters to you in a given table.
This first video will help you get the basics.
Filters are great and easy to use in most cases. There are times however when you need to get a bit advanced, and use advanced filters. See bellow:
Excel tables
Contrary to what you may think, your Excel tables are not actually tables. π
Wait, what?
Just changing the background and adding some borders does not create a table in Excel. Check out the video bellow for more info.
I use tables all the time in my spreadsheets. What I like most is the fact that formulas are copied automatically in the new rows.
Printing in Excel
If you ever wanted to print a large spreadsheet you probably found out that there are some challenges.
Conditional formatting
Conditional formatting in Excel gives you the ability to change the cell formatting (font, background, border) based on the value of that cell.
You can use it for lots of scenarios, in the video bellow you will find lots (if not all) of them.
And, because 1 hour of content may be insufficient π here is another video. π
Text to columns
Ever needed to split the contents of a certain column onto 2 or more? Yes, you can use text formulas for this, however text to columns is much easier to understand.
Flash fill
If you have Excel 2013 or newer you will find a very interesting option called Flash Fill. What it does is that it basically reads your mind understands the pattern if you’re doing something repetitive and does it for you.
See the video bellow to understand.
Data validation
Ever wanted to define rules for filling in a table? Things like, only numbers in a columns, only dates this year on another, and so on. You need Excel data validation for this, see bellow.
Data protection
If data validation can allow us to define rules for data entry, data protection takes things one step further. We can use it to set a password to open a document, make a sheet read only, etc.
Here is how to add a password to open a spreadsheet.
And here is how you work with making individual cells read only.
Section 2: Functions and formulas
Excel without formulas is … I don’t know, I can’t imagine Excel without formulas, they’re pretty important. π
Getting started + basic math functions
We’ll start with the basics, the video bellow explains how to insert a simple formula by making a simple sum.
https://www.youtube.com/watch?v=DsZ7lsTaAYo
Text functions
Sometimes you want to merge the content of 2 or more cells, or maybe extract something from another cell, using a function. You need text functions for that.
Date functions
Ever wondered:
- How to group dates by day of the week, or by week number?
- How to calculate a payment term from a start date?
- How to calculate number of working days between 2 dates?
Yes, it’s this guy again, he’s awesome. π
Logical functions (IF, AND, OR)
These types of functions can help you set conditions in Excel. Things like, if the cell value is negative then I will show this in another cell. AND, OR functions can help you define more complex conditions.
Lookup functions (VLOOKUP, INDEX + MATCH)
VLOOKUP (vertical lookup) is one of those functions that you just need to master in Excel. From importing data from another table to comparing tables, it’s just one of those very important functions in Excel.
VLOOKUP has some limitations nevertheless, this is why, if you want to take things to the next level, you have INDEX + MATCH.
Math-conditional functions (SUMIF, COUNTIFS, etc.)
We have math functions like SUM, COUNT, AVERAGE. We also have logical functions like IF, AND, OR. The math-conditional functions are a mix of these 2, will allow us to do basic math stuff, while defining some conditions.
Section 3: Data analysis
In this section we will talk about analysing tables in order to make some sense out of all the data.
Consolidate
We’ll start with the consolidate option. Suppose you have a monthly report with each month on a separate sheet and you want to analyse the past 3 months, find out more about this Excel option.
Subtotal
Honestly I don’t know how to summarize the subtotal. You know, it’s a … subtotal. π
Pivot Table
There are some important moments in life. You know, when you’re getting married, the first child, and when you discover Excel Pivot Tables. π
I may be exaggerating, however this is one of those Excel features that you must understand.
Solver
I wasn’t sure if I should put the Solver here as it’s not like subtotals and pivot tables in the way that they help you analyse a table.
It does however help in analysis as it enables you to calculate and make projections within some restraints. I will let this guy explain further.
Section 4: Charting
I don’t need to tell you why charts are important in Excel, you probably know already. Let’s get into in then.
Getting started with Excel charts
Lots of my students struggle with Excel charts because they don’t understand the fundamentals. You can learn the basics here.
Charts on 2 axis
If you got the basics, let’s get into more advanced stuff, like working with the secondary axis in Excel.
Pivot charts
Radu, what are pivot charts? How are they different from regular charts?
To put it briefly, they are associated to a certain Pivot Table and they have some cool filtering options built-in. See more here.
Sparklines
Radu what is a sparkline?
It’s a chart within a cell. Very simple to create, without many options, but ideal to illustrate trends while taking very little space.
Section 5: Others
I wasn’t sure where to put the following topics, this is why I created this section. π
Macros in Excel
If you are doing repetitive tasks in Excel, like this:
- Export data from an ERP system
- Transform it from CSV to a table using text to columns
- Changing the formatting
- Insert task here π
If you need to do something like that on a weekly basis, for example, it might be useful to turn the actions above into a macro and do all that at a press of a button.
Learn how in this video.
Radu, this way too advanced for me …
Macros might seem complicated and hard to understand, but they shouldn’t. The fact that you can press record and then run a recording should make them simpler.
Data import
For more complex models you might want to import data from other tables. You can do this using the import options found in Excel.
Bonus: an Excel test
Here is an Excel test that can help you evaluate your skills.
Did I forget something?
Think I missed something? let me know in the comments bellow, video recommendations are most welcome.
Have a better video?
Think your VLOOKUP video is much better that the one I posted in the list above? Send yours in the comments bellow and I look into it.