Microsoft Excel Essentials

Excel Workbook, Worksheet, Cell Structure Preview 08:43

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

Quick Tour of Excel - Ribbon & Quick Access Toolbar Preview 03:45

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

Customizing the Excel Interface Preview 06:00

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

Summary: Important Points to Remember (Terminology & Shortcuts) Preview 00:58

Quiz: Excel Fundamentals

Overview - Data Input & Hidden Excel Features Preview 01:40

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.

Data Entry and Editing Excel Data Preview 07:14

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

Basic Excel Formulas Preview 09:26

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.

Text to Speech (Hidden Excel Feature) Preview 03:09

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

Correctly Input Dates and Time Stamps in Excel Preview 04:25

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.

Use Excel AutoFill To Do Your Work Preview 03:56

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

Excel Flash Fill for Major Time Saving (Become Pro) Preview 05:00

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

Custom Lists With AutoFill (Hidden Excel Feature) Preview 02:30

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.

Inputting & Editing Comments & Notes in Excel Preview 08:13

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

Finding Excel Formulas & Input Cells with ONE Click (Become Pro) Preview 03:59

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

Excel Data Validation to Reduce Time Checking Data Preview 11:04

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.

Adding Tool Tips (Screen Tips in Excel) Preview 02:18

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.

Learn to Work with Pictures, Shapes & Other Objects in Excel Preview 05:31

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.

Quiz: Data Input in Excel

Challenge: Data Manipulation & Preparation in Excel Preview 00:22

Solution: Data Manipulation & Preparation in Excel Preview 05:41

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.

Summary: Key Excel Features to Impress & Save Time Preview 00:58

Overview - Worksheet Navigation in Excel Preview 01:07

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

Useful Excel Shortcuts for Navigation (Become Pro) Preview 11:01

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

Copying, Moving & Hiding Worksheets Preview 03:12

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

Freeze Panes (Lock Rows, Columns) & Split Screen for Long Excel Reports Preview 03:01

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.

Hide & Unhide, Insert, Adjust Columns & Rows in Excel Preview 06:32

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.

Copying and Pasting Data (Hidden Excel Paste Features) Preview 05:55

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.

Protecting Excel Worksheets & Specific Ranges Preview 08:44

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.

Challenge: Update & Protect Excel Report Preview 00:16

Solution: Update & Protect Excel Report Preview 03:57

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

Quiz: Worksheet Navigation

Summary: Important Worksheet Features & Excel Shortcuts Preview 01:40

Overview - Excel Formulas & What to be Aware of Preview 02:26

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

How Excel Calculates - Order of Precedence Preview 05:19

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.

Simple but Useful Excel Formulas Preview 05:18

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

Essential Excel Rule (Don't Skip!) Preview 02:31

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

Absolute and Relative Cell Referencing in Excel Preview 06:19

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.

Improve Clarity with Range Names Preview 05:09

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

Referencing other Workbooks or Worksheets Preview 03:54

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

Circular References & How to Locate Them Preview 01:40

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

Combine Values From Two or More Cells to ONE Cell Preview 03:44

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.

Challenge: Allocate Yearly Value Based on Percentages Preview 00:21

Solution: Allocate Yearly Value Based on Percentages Preview 03:42

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

Quiz: Excel Formulas

Summary: Key Points when Writing Excel Formulas Preview 00:57

Overview & Finding Excel Functions from Insert Function Preview 05:07

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

Important! Working with Excel Functions Preview 00:43

COUNT, COUNTA, COUNTBLANK Preview 04:12

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

COUNT based on a Condition (COUNTIFS) Preview 09:29

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.

SUM & AVERAGE Functions in Excel Preview 03:10

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

SUM by Criteria (SUMIFS, AVERAGEIFS) Preview 06:17

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

MAX & MIN (MAXIFS & MINIFS) Preview 04:06

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.

Rounding Values (ROUND, ROUNDUP & ROUNDOWN) Preview 05:31

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

Essential Excel Date Functions Preview 09:21

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.

Time Calculations in Excel - Total Time Worked Preview 06:00

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

Handling Excel Formula Errors Preview 05:19

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.

IF (Then, Else) Excel Function - Conditional Formulas Preview 13:22

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.

VLOOKUP Function - Lookup Values from Another Place Preview 07:48

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.

Challenge: How Good Are you with Excel Functions? Preview 00:30

Solution: How Good Are you with Excel Functions? Preview 07:11

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

NEW 365 Functions: FILTER, SORT, UNIQUE, XLOOKUP & More Preview 00:54

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.

Summary: Important Excel Functions Preview 01:14

Overview - Data Cleaning, Analysis & Management Tools in Excel Preview 02:27

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.

Sort Data (Organize & Create Order) in Excel Preview 07:05

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.

Unsort Data to Revert to Original Order Preview 01:49

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

Add Subtotals to Sorted Data Preview 06:27

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

Filter Data to Extract What you Need Preview 06:00

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

Delete Blank or Empty Rows in Excel Preview 03:52

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

Fill Empty Cells in One Go (Become Pro) Preview 03:26

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.

Remove Duplicates to Get a Unique List in Excel Preview 03:01

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

Excel Find Feature, Select & Emphasize Preview 07:10

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

Find and Replace Values & Formatting (Hidden Excel Feature) Preview 05:09

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.

Why Excel Tables Can be a Time Saver (Become Pro) Preview 10:21

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.

Challenge: Data Management in Excel Preview 00:24

Solution: Data Management Challenge in Excel Preview 05:31

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

Summary: Tools for Organizing, Cleaning & Managing Data in Excel Preview 01:01

Special Message - You've Got This! Preview 02:14

Overview - All About Formatting Preview 01:29

In this section we’ll take a look at some useful formatting options in Excel which we haven’t covered in the course yet.

Excel Formatting Tips - Alignment, Borders & More Preview 05:27

We’ll cover the formatting options that are going to come in handy for your when you create Excel reports. We’ll talk about Alignment options and borders.

Better Than Merge (Become Pro) Preview 03:24

Merge and Center cells can become a problem in Excel when you add them in areas with formulas. I’ll show you a better alternative.

Justify Text Length (Hidden Excel Feature) Preview 02:37

I’ll show you one of my favorite Excel features when it comes to working with text in Excel.

Excel Number Formatting Options (& Special Formatting) Preview 03:39

In this lecture we’ll take a look at some number formatting options we haven’t looked at yet.

Excel Conditional Formatting (Top Bottom Analysis & More) Preview 06:20

We’ll learn how we can influence the cell formatting based on a condition. Excel has a lot of inbuilt, easy-to-use conditions.

Icons & Data Bars with Conditional Formatting in Excel Preview 06:39

In this lecture we’ll learn how to use icons and data bars with conditional formatting. This is going to help you to bring attention to specific areas of your report.

Quiz: Excel Formatting

Challenge: Format Excel Report Preview 00:23

Solution: Format Excel Report Challenge Preview 06:45

In this report we’ll format a report so it’s in a nice, readable format.

Summary: Useful Excel Formatting Tools Preview 00:36

Checklist Before Printing Preview 02:10

I’ll give you a checklist to make sure your printed file looks great whether it’s in print or in electronic format.

Useful Printing Options in Excel Preview 04:22

In this lecture we’ll cover some useful printing options like margins, orientation, and scaling in Excel.

Adding Page Breaks & Printing Parts of a Sheet Preview 04:22

We’ll take a look at how you can control page breaks and how you can print just parts of an Excel worksheet by using the print area feature.

Printing Large Datasets (Make sure you include this!) Preview 03:47

We’ll learn how we can fit the data of large datasets to fit properly on the printed pages including any required header information.

Header & Footer in Excel (Logo, Page Numbers, etc.) Preview 04:00

We’ll expand on the header and footer options in Excel. I’ll also show you a trick you can use to apply the header and footer of an existing sheet to many sheets in one go.

Saving Excel Files PDF & Printing Many Sheets Preview 03:42

In this lecture we’ll look at how you can print many sheets or an entire Excel workbook in one go. We’ll also look into how you can save these sheets as a PDF document.

Challenge: Prepare Excel Report for Printing Preview 00:22

Solution: Prepare Excel Report for Printing Preview 03:20

In this challenge we prepare a document for proper printing.

Summary: Excel Printing Checklist Preview 00:46

Good Spreadsheet Design Principles (with Examples) Preview 05:24

We’ll cover some Dos and Don’ts when it comes to creating your next Excel workbook. We’ll talk about the 2 main aspects when designing an Excel spreadsheet: The workbook structure and the visual design of the worksheets.

Adding Hyperlinks (Plus a Smart Workaround) in Excel Preview 06:47

The advantage of hyperlinks in a workbook is that you can quickly jump to different locations. We’ll cover the different ways how you can use hyperlinks in your files.

Define Cell Styles & Set Defaults (e.g. for Excel Tables) Preview 05:36

If you’re designing an Excel workbook that has many tabs and you want these tabs to be similar in their setup you can use cell styles. I’ll show you how you can setup a style for your input cells, calculated cells, hyperlinks, and so on.

Excel Workbook Theme & Color to Save Time Preview 05:22

In this lecture we’ll take defining styles a step further and we’ll change the theme of the entire workbook.

Use Your Own Excel Templates Preview 04:05

We’ll learn how to create your own Excel template so anytime you create a new workbook you can use this template as your starting point.

Quiz: Workbook Design

Summary: Excel Workbook Design Principles Preview 00:58

Overview - Create Helpful Excel Charts Preview 02:38

Charts visually display quantitative data. In Excel there are various chart types available. Each chart type serves a purpose. We’ll cover the most important ones in this section and when to use which type.

Excel Chart Basics for a Quick Start Preview 13:38

In this lecture we’ll cover some chart basics to get started like selecting and changing an Excel chart type and formatting options.

Adding More Series to a Chart (Comparison Charts - Method 1) Preview 07:00

We’ll take a look at how we can add more series to an existing Excel chart for comparison charts, where we’re comparing one category with another.

Comparison Excel Charts - Actual & Variance (Method 2) Preview 06:33

In this lecture we’ll create a comparison chart by combining 2 Excel charts to show actual results and the deviation to previous year.

Combination Charts (Plus a Hidden Excel Feature) Preview 07:01

We’ll learn how to create combination charts. These charts in Excel include different series types in one graph.

Dynamic Chart Ranges with Excel Tables Preview 02:33

In this lecture we’ll create dynamic chart ranges with Excel tables. This will automatically update your chart when new data is added to the source data.

Pareto & Histogram Excel Charts Preview 07:08

I’ll show you how you can make Pareto and Histogram charts in Excel.

Treemap Chart in Excel Preview 07:28

In this lecture we learn how to create a Treemap chart in Excel. This is a chart that displays hierarchical data in the form of rectangles.

Sunburst, Pie & Doughnut Excel Charts Preview 06:46

We’ll learn how to do Sunburst, Pie and Doughnut charts in Excel.

Sparklines - Mini Excel Charts Inside Cells Preview 06:04

In this lecture we’ll learn how to create Sparklines in Excel. This is a mini-chart inside a single cell.

Quiz: Excel Charts

Challenge: Treemap for Management Report Preview 00:22

Solution: Treemap for Management Report Preview 06:39

In this challenge we’ll create some charts to provide a few options to visualize data.

Summary: Working with Excel Charts Preview 00:49

Benefits of Excel Pivot Tables & Starting Checklist Preview 01:07

In this section we’ll take a look at PivotTables to get quick insights from data in Excel.

Inserting a Pivot Table in Excel Preview 10:27

We’ll learn how we can insert PivotTables in Excel, select fields, and how to drill down on selected fields.

Why You Should Use Tables as Pivot Source (Become Pro) Preview 02:35

We’ll cover the benefits of using Excel Tables as source for PivotTables.

Sorting Pivot Data & Analyzing Multiple Items Preview 07:36

You’ll learn how to analyze multiple items and how to get your Pivot Data sorted automatically in Excel.

Adding Calculations & Multiple Reports with Pivot Tables Preview 05:40

In this lecture we’ll take a look at you can add calculations and multiple PivotTable reports to your Excel spreadsheets.

Using Slicers & Timeline (Working with Dates) in Excel Preview 08:30

We’ll learn how we can improve our PivotTable reports by adding Slicers to them. This will give us the ability to filter our data in Excel by using buttons.

Creating Pivot Charts Preview 08:16

We’ll cover advantages and disadvantages of PivotCharts over standard Excel charts.

Pivot Tables or Formulas? When to Use What Preview 02:13

We’ll answer the question when to use PivotTables and when to use Excel formulas instead.

Challenge: Excel Data Analysis with Pivot Tables Preview 00:22

Solution: Excel Data Analysis with Pivot Tables Preview 06:11

In this challenge we’ll do data analysis with PivotTables.

Summary: Important Pivot Table Features Preview 01:05

Power Query, Power Pivot & Power BI - When to use which? Preview 04:00

Learn how to use Excel "Get and Transform" - also known as Power Query.

Power Query is a part of Excel since Excel 2016. You'll find it in the Data tab. This section provides you with an introduction to Power Query and introduces you to different scenarios where Power Query is better and easier to use than any other Excel feature when it comes to getting the right data structure so you can prepare meaningful Excel reports / Excel dashboards from the data.

Excel has other Power Tools like Power Pivot and Power BI. When should you use what? - and do you need to learn Power Pivot and Power BI? In this overview Excel tutorial, I'll introduce you to the Excel's Power Tools and take you through when you can use each of these.

Availability of Power Query / Get & Transform Preview 00:22

Import Text File with Power Query Preview 12:31

We’ll learn how to import a text file with Excel’s Power Query.

Merge data in Power Query (for Pivot Table) Preview 07:49

We’ll cover how to merge data from different tables into one report in Power Query.

Clean up Messy Excel Data (multiple headers, unpivot etc.) Preview 10:38

A little bit of Power Query knowledge can take you a long way. This lesson shows you a few of the most useful features of Power Query and how you can use these together to transform very messy Excel data to a clean tabular structure that can be used to create reports on. You'll learn:

1. How to bring specific Excel ranges to Power Query

2. Use Power Query Fill Down

3. Transpose Data

4. Merge Columns in Power Query

5. UnPivot Columns

6. Split Columns by Delimiter

   
   

Challenge: Data Cleanup with Power Query Preview 00:29

Solution: Data Cleanup with Power Query Preview 06:56

In this lecture we’ll perform a data cleanup with Power Query.

Summary: Key Takeaways Excel Power Query Preview 00:26

Excel Dynamic Arrays & New Functions in Microsoft 365 Preview 03:22

Get a quick overview of how Excel's calculation engine has changed in the new Excel for Microsoft 365 version. It will show you the new power of Excel to create better spreadsheets with simple functions like Unique, Sort and Filter. Complex data analysis is now accessible to everyone and not just to advanced Excel users.

Availability of the New Excel Functions Preview 00:14

Excel's New Calculation Engine: Spill Error and @(at) sign Preview 09:36

Find out what spilled ranges mean in the new Excel. What is the purpose of the hash sign (#) and what the #spill error is and how you can fix it. You'll also learn how you can work with Excel tables and spilled ranges. What the at (@) sign means when you open old files in the new Excel and whether or not you can  still work and save files in the old Excel versions and open then in the new 365 version. We also talk about performance in the new Excel and whether using spilled ranges makes your workbooks slower or not.

Excel UNIQUE Function: Get List of Distinct Values Preview 06:06

Learn how to use the new Excel Unique function. You'll also learn:

1. How to get the unique values for two or more columns

2. How to get unique values inside the data validation drop-down list (how to reference spilled ranges in data validation)

3. How to get a list of items that occurred only once

4. How to get a unique list of values across columns

Excel SORT & SORTBY Functions Preview 08:43

Find out how to use the new Excel SORT and SORTBY functions. Here we cover:

1. How to get a dynamic unique sorted list in Excel

2. How to sort based on another column

3. How to sort based on a column not inside the final result

4. How to use sort and unique with excel drop-down lists

Excel FILTER Function: Return Multiple Match Results Preview 08:32

Learn to use the new Excel FILTER function properly. The Filter function is one of the Excel's new powerful lookup functions. It can return multiple match results. You can also use AND as well as OR conditions in FILTER. Sometimes you might end up with a #CALC error. Learn what that means and how you can handle that in this lecture.

Excel XLOOKUP Function: The New Power Lookup Function Preview 09:11

The advantage of the new Excel XLOOKUP function over VLOOKUP function is that XLOOKUP is much more flexible that VLOOKUP. For example:

1. The lookup value can be on the left OR the right hand side of the data you want returned

2. You can look for the first OR the last match

3. No need to use Excel IFNA or IFERROR function if a match isn't found. You can account for this inside the XLOOKUP formula

4. You can use Wildcards in XLOOKUP

5. You can do approximate match lookups just like you could with VLOOKUP but your lookup range no longer needs to be sorted

Challenge: Get Total Salary by Division Preview 00:29

Solution: Get Total Salary by Division Preview 06:39

Your Next Steps & More Learning Preview 02:00

** Next Steps - BONUS Preview 01:47