Excel Essentials for the Real World (Complete Excel Course)

In this course you’re going to start off learning how to input data including some hidden features in Excel that will save you lots of time. You’re going to learn how to navigate large data and how you can protect your Excel spreadsheets. We’ll cover important Excel functions and formula rules. We’ll turn messy data into a proper dataset. We’ll learn about spreadsheet design principles as well as printing and formatting to bring attention to your analysis. We’ll create interactive charts and tables and even find better ways to import and transform data with tools like Power Query.

I’ll cover the 2 options to download the accompanying Excel workbooks.

In this section I’ll give you a tour of Excel to get you feeling comfortable using the program.

We’ll cover the Excel ribbon and the different tabs that are available to you.

I’ll show you how you can customize Excel so you can easily find the files and features you use most often.

In this section we’ll be entering data and basic formulas in Excel. We’ll also do basic formatting for cleaner and more professional reports. You’re also going to be able to use important features like drop-down lists in Excel and add data validation to the cells. We’re also going to cover some key features that aren’t usually talked about in an Excel basics course. They’ll help you become more efficient and will make your workbooks look more professional.

I’ll show you how to enter data to Excel and some tips and tricks when it comes to entering, removing, or editing data.

In this lecture I’ll show you how to enter basic Excel formulas. We’ll cover some helpful shortcuts to be more efficient. We’ll also cover some basic cell formatting.

I’ll show you a fun, hidden feature on how to get Excel to read out the content of a cell for you.

We’ll cover how Excel remembers dates and how you can format them. We’ll also learn how to quickly add a time stamp to our spreadsheet.

We’ll learn how AutoFill and its options can help us when we work with different types of data in Excel.

I’ll show you how Excel's Flash Fill can become your magic tool when it comes to entering and manipulating data.

We’ll learn about another time saver that not many people know about. We’ll use Custom Lists in Excel to be much faster entering data.

I’ll show you how you can make the best use of Comments and Notes in Excel.

In this lecture I’ll show you how you can find all Excel formulas or input cells with One click only.

I’ll show you why data validation is important in Excel spreadsheets and how you can easily set it up. We’ll learn how to validate numbers and dates and how you can set up drop down lists for the user to choose from.

We’ll learn how to add Tool Tips or Screen Tips to help the user of the Excel spreadsheet know what they should input in which cell.

In this lecture I’ll show you how to work with pictures, shapes and other objects in Excel cells. I’ll show you how to prevent objects from getting stretched out when the size of the Excel cell changes and how you can hide them from view.

In this challenge we’ll tackle data manipulation and preparation. We’ll create a login name from the name in the dataset by applying a certain logic. We’ll extract the start year for our employees from their start date, add notes and screen tips to our Excel spreadsheet, and add a data validation to ensure date is input correctly.

In this section you’re going to learn all about worksheet navigation in Excel.

In this lecture we’ll take a look at the most useful Excel shortcuts for navigation. These will save you a ton of time.

I’ll show you how you can copy, move and hide worksheets in an Excel workbook.

We’ll lean how to make it easier for us to navigate Excel worksheets that have a lot of data by using freeze panes and split screen.

We’ll cover how we can hide and unhide rows and columns and how we can find which rows and columns have been hidden. We’ll also learn how to insert values in between other values without overwriting existing data. I’ll also give you a bonus tip as to how you can group rows and columns instead of hiding them.

I’ll show you the hidden features when it comes to pasting data in Excel. In addition, I’ll give a bonus tip and I’ll show you how you apply a mathematical operation with Paste Special.

In this lecture we’ll talk about protecting your work. I’ll show you how you can protect an entire workbook, the workbook structure, individual sheets, or to allow the user to edit ranges. The bonus tip is to protect a sheet but to still allow the user to perform certain tasks.

In this challenge we practice to update and to protect Excel spreadsheets.

We’ll cover the differences between an Excel function and a formula and how to turn on/off automatic calculations for an Excel spreadsheet.

It’s important to understand how Excel runs calculations and in which order it does it. I’ll also cover how Excel interprets True and False and how you can work with comparison operators inside Excel functions to become Advanced in Excel. My bonus tip is to show you can display the formula as text in a separate cell.

We’ll learn some simple but useful formulas and practice entering formulas in Excel.

I’ll show you the 2 essential rules when it comes to creating error-free reports in Excel.

We’ll cover one of the most important concepts when working with Excel. We’ll learn what the “$”-sign means and how you can apply this to your formulas.

You’ll learn about range names and how they can help you improve clarity and write easier-to-read formulas.

We’ll take a look at how you can write formulas that reference cells in other workbooks or other worksheets.

We’ll learn what a circular reference is in Excel and how to easily find the problematic formula(s).

You might come across cases where you need to combine the value of 2 or more cells into one cell. I’ll show how this is done in Excel and how you can combine text with cell references.

In this challenge we’ll use formulas to allocate a yearly value to certain months by using smart cell referencing.

We’ll cover important Excel functions in this section and I’ll also share with you how you can use Excel’s function library.

We’ll learn how we can apply the COUNT, COUNTA, and COUNTBLANK function in Excel and when to use which of these functions.

You can use the COUNTIFS function in Excel when you want to count the number of values in a range if they meet one or more conditions. I’ll show you how to use it and how you can even use operators like the greater than or smaller than signs.

We’ll cover the SUM and the AVERAGE function in Excel and how you can easily apply them to your spreadsheet.

We’ll learn how to use the SUMIFS and the AVERAGEIFS function in Excel one values that meet one or more conditions.

We’ll look at how to use the MAX and MIN function in Excel and how we can get the Max and Min value of a dataset by criteria.

I’ll show you how you can use functions in Excel to automatically round values.

How to work with dates in Excel is not that intuitive. I’ll show you how dates are entered correctly, how Excel remembers dates, and how we can use Essential Excel date functions.

We’ll cover how Excel processes time and how we can work with that.

Whenever you’re working with Excel formulas you may end up with some formula error. I’ll show you the most common ones and how you can handle them with the IFERROR function.

We’ll cover the IF function which is one of Excel’s most useful functions. I’ll also show you a quick debugging trick that you can use to see what’s behind your formula.

The VLOOKUP function is one of the most used Lookup-Functions in Excel. I’ll show you how you can lookup values from another place, another sheet, or another data table.

In this challenge we’ll use some essential Excel functions to solve some exercises.

If you have Excel for Microsoft 365 then you have the new Excel Dynamic array functions such as FILTER, SORT, UNIQUE, XLOOKUP & more. I have added a brand new section to course called NEW 365 Excel Functions: FILTER, SORT, UNIQUE, XLOOKUP & More.

We cover these in detail there.

In this section we’ll take a look at more Excel features that will help us organize and structure our data, so it makes analysis easier later on.

We’ll cover the different options we have to sort our data. I’ll also show you how you can even Sort your data by color.

We’ll learn how we can unsort the data so we can go back to our original order if we need to.

I’ll show you how you can quickly add subtotals to your dataset by using the Subtotal feature in Excel.

We’ll learn about Excel’s Filter feature to quickly extract what you need from a dataset.

In this lecture we’ll look at how we can delete rows with blank or empty cells in Excel.

In this lecture we’ll take a look at a very neat Excel feature that come in handy when you want to update your data and fill empty cells.

We’ll look at a method you can use to remove duplicates from your dataset in Excel.

Excel’s Find feature has many benefits that may come in really handy.

In this lecture we’ll take a look at how we can not just find but also replace values. And not just values but also Find and Replace formatting.

I’ll show you the advantages of Excel Tables and why they can be really helpful in your report. We’ll also learn what Structured Referencing means in Excel.

We’ll use some of the features we learned in this section and apply it to our example.