# Microsoft Excel For Real Beginners

Premium course

Easy

Premium course

Easy

Video/Text

Microsoft Excel

105 Lessons

0% Not started

Everything you need to take your Excel skills to the next level!

If you're new to Excel, this is the place to be. It's your chance to gain in confidence and really get to grips with the foundations to make the most of Excel. Learn how to create calculations and formulas, and use time-saving shortcuts to work like a pro!

Premium course

This is the start of your journey into managing data with Excel. I'm delighted to have you here - so let's get started!

Premium course

In this lesson, we're going to do a guided tour. We'll cover some of the terminology you'll be using throughout this course, look at the user interface, find some of the most useful buttons and tools, and take a look at what your options are for customizing the Excel interface to work the way you want it

Premium course

Before we start with the meat of this course, I want to introduce you to some really useful shortcuts. Why? Because I'll be using these shortcuts throughout the course - and you should too. The quicker you get on board with these shortcuts, the more efficient you can be when it comes to your life with Excel - and there's no time like the present to get started with this.

Premium course

Let's take a look at what we're going to cover in this chapter, and what you'll be able to do by the end of it.

Premium course

When you start with Excel, you've got two choices. You can either work with a spreadsheet that already exists, or you can create a new file from scratch. In this quick lesson, we'll look at both options.

Premium course

When you're putting together a new spreadsheet, there are some basics to consider. Where is the best place to put your headings? Do they need special formatting? And which order do you do things in your spreadsheet? In this lesson, we'll answer these basic questions

Premium course

Throughout this course, I'll be setting you exercises, and then reviewing them with you. In this lesson, I'll talk you through how the exercises are structured so that you know what to expect.

Premium course

Now it's your turn. Put the skills that you've learned into practice as you set up a spreadsheet from scratch.

Premium course

Let's take a look at how you've done in that last exercise. We'll look at a couple of ways in which you could have tackled that last challenge.

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

Excel's default appearance, the buttons you see, the size of text - even the colour of gridlines - all of this can be customised to work the way you want. In this lesson, I'll show you how to get Excel looking and working the way you want.

Premium course

Now you know how to open an existing file and how to create a new one, it's time to get Excel to do some work! We're going to start by looking at how Excel can do take the strain out of creating calculations.

Premium course

For the first time, we begin to add some really simple calculations to our spreadsheet. How do you create a calculation? And if there's more than one way (there is!) which is the better way, and why? All the answers are revealed in this lesson!

Premium course

This lesson is a bit of a diversion, in that it doesn't really have anything to do with Excel. But there are rules in maths about which order things get done. If you've got a calculation like 3 + 4 x 5, it matters whether you do the 3 + 4 first, or the 4 x 5 first! In this lesson, we explore which is the right way, and how that's reflected in Excel.

Premium course

There's a tiny square dot in the corner of a cell. It allows you to access the "AutoFill" tool, which is one of the simplest, yet most powerful tools in the Excel toolkit. In this lesson, we'll explore what it can do for us.

Premium course

Now it's your turn. I'll give you a couple of spreadsheet with the data already there. But it's down to you to add the appropriate calculations to them...

Premium course

Let's see how you did! We're going to run through the exercise together so that you can see how I'd approach these calculations, and see if there are any alternatives which might be better.

Premium course

Excel knows all about dates, and how to calculate with them. In this lesson, we'll see just how easy it is to calculate how far apart any two dates are - and to find out how old you are (in days)!

Premium course

Let's take a look back at what we've learned in this section of the course.

Premium course

So, you've got your headings in place, and perhaps a little data too. Now you need to do some very basic formatting - to make your headings look like headings, and your data look like... well - financial information or dates or whatever else your data is.

Premium course

Cells are made up of three parts - the background, the contents and the borders. So far, we've looked at the contents, but in this lesson we'll dive into formatting the background and borders of your cells.

Premium course

You've seen what's involved in formatting your cells - and that you can spend a long time getting things to look "just so". Now we'll take a look at how you can make all this a bit simpler and quicker...

Premium course

We've seen how easy it is to format your data to look like dates, or currency or whatever else you want. Now we'll take a deep dive into some formatting tools that give you real power - and which most people don't even know exist!

Premium course

It's your turn now. Given some real-world spreadsheets, it's your job to format them to become clearer and more user-friendly.

Premium course

Let's take a look at how you tackled that last exercise - and at some alternative strategies for formatting spreadsheets.

Premium course

Let's take a look back at what we've learned in this section of the course.

Premium course

What exactly is a function? What do they look like, and how do they work? In this lesson, we'll take a look at the anatomy of a function so that you recognise one when you see it, and know the basics of using any function in Excel.

Premium course

Probably the single most common thing to do in Excel is "adding stuff up". That's what the SUM function does for you. In this lesson, you'll learn how to use SUM, including ways of adding up more than one set of cells. We'll also see a quick shortcut to use SUM - and some other functions - without even typing a thing!

Premium course

Now that you know how to use one of Excel's functions (SUM) it's time to get to know some of the other essential functions. In this lesson, we'll look at three more - MAX, MIN and AVERAGE.

Premium course

You'll often see the dollar symbol within calculations in Excel. But what exactly does it mean, and why would you want to use it? All will be revealed as we explore one of the most important topics we've come to so far!

Premium course

The COUNT function is an interesting one. It seems very simple (and indeed it is) but there are some questions that I'm often asked. One is "Yes, but when would I ever actually use it in the real world" and another is "But what about this other function that looks just like it?" In this lesson we'll tackle how, when and why you use COUNT and its cousin COUNTA.

Premium course

In this exercise, you'll put your new skills with the SUM, MAX, MIN and AVERAGE functions to the test in a real-world example.

Premium course

Let's have a look at how you did. I'll show you how I would tackle the exercise I set you, and we can compare results!

Premium course

Excel has fantastic built-in help available for you, right when you need it. In this lesson, we'll look at where the built-in library of Excel functions lives, and how to get the help you need from it

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

In this unit of the course, we're going to look at all things to do with testing. But why is this necessary? Let's see where we're heading in this section.

Premium course

IF is one of the most powerful and useful functions in Excel. With IF, you can say "Do this IF some other condition is met" - such as "Do this calculation IF we have data" or "Calculate shipping IF it's a UK order". So, in this lesson, we'll take a look at the absolute basics of how the IF function is put together.

Premium course

Now you know the essentials of how to write an IF function, it's time to look at when and how you're likely to want to use it. So, in this lesson, we'll work on some real-world examples of using IF to make our spreadsheets more intelligent, easier to read and more powerful.

Premium course

Now it's your chance to put what you've learned about IF to the test in a real-world example. Can you solve the challenge?!

Premium course

Let's compare notes and see how you did with that exercise.

Premium course

Let's take a breath and look over what we've covered in this session.

Premium course

IF is one of the most useful functions in Excel. But so common is it to want to say "Do this if..." that those nice people at Microsoft have given us some more functions which combine their own job with the power of an IF test. Let's investigate...

Premium course

As well as IF, we have a couple of other functions that allow us to do some testing. We've seen that COUNT allows us to count cells. But what if we want to COUNT cells only IF they meet our requirements? COUNTIF to the rescue!

Premium course

Just as COUNTIF can be used to count cells that meet your requirements, so we can use SUMIF to add values that meet your requirements. SUMIF behaves just like COUNTIF, but with an additional feature - in this lesson we explore that in detail.

Premium course

How well can you put what you've learned into practice? Let's see with another exercise based on a real-world example.

Premium course

Let's compare notes again. I'll show you how I'd tackle the exercise you've just been set.

Premium course

So far, we've been working mostly with calculations, formulas and functions. Along the way, we've seen some lists of data, but it's time we tackled this topic in more detail. So let's do it!

Premium course

When you need to create a block of data or a list, there are some things you can do which will help you to be able to analyse that data later - and things you may do which will make your life difficult. In this lesson, we'll take a look at the best way to structure your lists - and at why these ways work best.

Premium course

If you've got a large block of data, one of the things that can make your life harder is that once you start scrolling, the headings all disappear! In this lesson, we'll take a look at how to resolve that problem in a couple of different ways.

Premium course

A nice gentle start to this unit.... You may already know how to sort data in Excel, but in this lesson, I'll provide you with a few tips to help you sort in the most efficient way possible - and a couple of words of warning about ways to get it spectacularly wrong and screw up all your data!

Premium course

More on sorting. What if you want to sort your data in a way that isn't alphabetical? What if you need to sort your data from left to right, rather than top to bottom. In this lesson, we'll learn how to do that.

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

There are various tools that Excel provides for filtering your data. In this part of the course, we'll take a look at how to use them, and how to get the most from them.

Premium course

Let's say that someone sends you a file which contains an output from a database. Perhaps it's a list of all the staff in your organisation, or transactions from the past 6 months. There could be a hundred thousand rows of data. Now we want to deal with a subset - just data for your local region, or just transactions with a value over 100. In this lesson we'll take a look at how we achieve this by using Excel's powerful - yet simple - filtering tools.

Premium course

Conditional formatting is a tool which can be used in any scenario - but it really comes into its own when you're looking at a large block of data, and wanting to make sure you're not missing some key information. What if a person claims to have worked not 40 but a 400 hour week? Or an order has a negative value? Conditional formatting helps to highlight this.

Premium course

Now it's your turn. I'll give you a file containing lots of data - it's your job to filter and format it to show just the information you've been asked to find.

Premium course

How did you get on? Let's look at one or two ways in which you could complete the exercise you've just been set.

Premium course

In this bonus lesson, we'll take a sneek peek at one of Excel's new functions that allows you to sort data

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

You've seen how to use some really great functions. And we've even applied those functions to lists of data. But there's an easier way - by naming our cells and our ranges, we can make our formulas both easier to write - and to read.

Premium course

Let's take a look at how we name our data and our ranges, and how we then use those names in our calculations. Once you master these simple tools, your life (in Excel) will never be the same again...

Premium course

In the last lesson, we looked at creating names - and why you should always do so. In this lesson we'll build on that to use a great tool that creates names automatically for you.

Premium course

Now is your chance to apply what you've learned, and create a set of names within an existing spreadsheet. Then you'll update some existing formulas to use these new names.

Premium course

Let's look at how best to tackle the exercise you've just done, as we put into practice what we've learned about naming cells and ranges.

Premium course

What if you get a name wrong? Or name the wrong cells? Fortunately, Excel has a tool to help put that right. And it has another trick up its sleeve too.....

Premium course

Creating names is good... using those names in your formulas is better! Let's have a look at how names can be used within your calculations.

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

In this unit, we'll look at a great tool that Excel provides to allow easy management of lists of data - the "data table". We'll look at how you create them, at what they do for you, and at how you remove them once you're done.

Premium course

It's not actually as simple as you might think to create a data table - simply because Excel seems to "hide the button" in plain sight! But once you know where the button is, the rest is straightforward. So, let's create a couple of data tables!

Premium course

When you create a data table, you don't have to stay with what you're given. There are lots of options to control how your table looks and behaves, so let's dive in and explore!

Premium course

There's an "interesting" syntax you need to use when referring to cells in a data table. In this lesson, we'll take a look at what happens when you refer to a cell within your table in a calculation somewhere else.

Premium course

Like most tools, data tables are great in the right situation, but not perfect for every situation. So, in this lesson, we'll take a look at how you get rid of a data table if you decide that you no longer want to use it.

Premium course

It's time for you to have a go. In this exercise, you'll take a block of "normal" data and convert it to a data table. You'll set some options to get it looking just right, and then you'll get rid of the table again.

Premium course

How did you get on? Let's look together at the task you were set, and find the most efficient way of accomplishing it.

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

This module of the course is all about using Excel workbooks which contain multiple worksheets. So the first question we tackle is "Why do it?"

Premium course

Let's get the basics under our belt. In this lesson, we'll look at the basic tools you need to use worksheets - creating, moving, copying, renaming and deleting them

Premium course

When you have multiple worksheets in your workbook, it often makes sense to work with some of them as a group - perhaps sheets for one particular department or month. In this lesson, we'll look at how to group sheets, how to show that they're a group, and point out the pitfalls of working with grouped sheets.

Premium course

Now it's your turn to have a go. I'll provide you with a basic file with one sheet - and you get to turn that into a multi-sheet file, colour-code the sheets and format them all in one go.

Premium course

How did you do? We'll go through the exercise together now and you can see if there are any steps you missed, or any tricks you could have used to be more efficient.

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

Let's see where we're going in this unit, and why understanding how to calculate across multiple worksheets is essential.

Premium course

Let's start with the good news - it's really simple to create a calculation on one worksheet that refers to another worksheet. In this lesson, we'll see just how easy it can be.

Premium course

So now you know how to refer to other sheets in the same file, what about creating a calculation that refers to data in a whole different file? Also simple, as you'll see!

Premium course

So far, we've seen that referring to other sheets, and even other files, in your calculations can be really simple. But there are one or two "gotchas" to be aware of. So, in this lesson, we'll take a look at potential issues with referring to cells in other workbooks.

Premium course

Now it's your chance to have a go at creating calculation which refer to another workbook. You'll do a basic reference to another file - then you'll break that reference by renaming the other file. Finally, you'll find the cause of the resultant errors, and you'll fix that problem!

Premium course

How did you get on? That was an exercise with several parts, each building on the last, so it may have been slightly trickier. As we go through the exercise together, you can see whether you came up with the same solution I did, or whether there was more than one way to achieve the result you wanted.

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

I've been deliberately flippant with the name of this chapter - charts really are seen by some as just pretty pictures made by those who don't like looking at numbers. But it's true that sometimes a picture can paint a thousand words - and a chart can instantly highlight information which would be hard to spot by just looking at raw data. So, you need to get some solid charting skills under your belt, and in this chapter we'll give you the lowdown on how to create great looking and functional charts.

Premium course

In this lesson, I'll introduce you to the "magic" keyboard shortcut which gives you a chart with a single keypress. I'll also walk you through the information that Excel needs in order to create a chart, so that you know how to alter a chart to get exactly what you need.

Premium course

In this lesson, we'll take a look at some of the common settings that you may want to change for your chart, so that you get closer to what you really wanted. We'll also look at a way of getting several of these settings applied with a single click, to speed up the process.

Premium course

Pie charts are essentially the same as other chart types in Excel, except that you have to be a little careful when selecting the data to plot. Here we investigate what works and what doesn't.

Premium course

The sheer quantity of charts available in Excel can be overwhelming. In this short lesson, we'll take a look at which types of chart are best used for which kinds of information.

Premium course

In this exercise, I'm going to give you two different types of data, and you can take a shot at producing the best type of chart for each sample.

Premium course

Let's tackle the exercise together, and see which chart types we like and which work less well.

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

I've been deliberately flippant with the name of this chapter - charts really are seen by some as just pretty pictures made by those who don't like looking at numbers. But it's true that sometimes a picture can paint a thousand words - and a chart can instantly highlight information which would be hard to spot by just looking at raw data. So, you need to get some solid charting skills under your belt, and in this chapter we'll give you the lowdown on how to create great looking and functional charts.

Premium course

There are lots of ways in which you can get something OTHER than what you want when you print. But if you follow these top 3 principles, your chances of a good result first time go up significantly.

Premium course

If you want to get the best results when printing your data, you need to tell Excel exactly what you want to print - and how big you want to print it. In this lesson, you'll see how to do that.

Premium course

Do you want to print the gridlines that Excel shows? Do you want big margins or small? Portrait or landscape layout? All these settings and more are found as we set up the page ready to print

Premium course

Do you want to display the name and location of the file at the bottom of each page? Or the page number in the lower left corner? Then you'll be wanting to set up headers and footers. In this lesson, I'll show you how.

Premium course

There are a couple of extra points to consider when printing large amounts of data. How will you know which column is which on page 25? And how annoying will it be to have a single column print on its own page? These problems and more are resolved in this lesson.

Premium course

Let's take a look back at the topics we've covered in this part of the course.

Premium course

Well done - you've achieved a huge amount in this course, and have come a long way. Let's take a look at what you've learned.

Premium course

It's not the end! There's loads more to Excel, and now's a great time to make a start. Let's take a look at what else you might want to learn as you become a real Excel guru!

Follow

Andrew Richards has consulted and provided training for the world's leading companies - Marks and Spencer, Imperial College London, the Institute for Government and Net A Porter are among his clients. He's been running training courses for over 20 years for companies large and small as well as for government bodies, charities and healthcare providers.