10 essential things you should learn about Microsoft Excel

If you want to learn Excel, this lesson covers ten important things that you need to know if you are going to use Excel effectively. Even if you've been using Excel for a while, check out each of the individual lessons below to make sure you have the basics covered - and maybe learn something new along the way. If you have any questions about any of the lessons, you can leave a comment at the bottom of this page, or at the bottom of the individual lesson.

Note that each of these lessons will open in a new tab so you can come back to this page easily when you've finished one of the lessons.

1. How to create a Pivot Table in Excel

Pivot Tables are considered by most advanced Excel users to be the most powerful feature in Excel. Pivot Tables allow you to tabulate and report on data in your spreadsheets in ways that would take hours to achieve without using a Pivot Table.

This lesson introduces you to Pivot Tables in Excel, and will show you the basics of how to use them. You can download our worked example spreadsheet so you can work through the lesson using the same data that we used to create the lesson.

Read more

2. How to enter basic formulas and calculations in Excel

If you're learning Excel, formulas are where the real magic begins. Formulas allow you to perform calculations on data in your spreadsheet. Simple Excel formulas allow you to add up, subtract, multiply, divide and average one or more numbers in your spreadsheet. Complicated formulas allow you to calculate just about anything you like.

This lesson introduces you to formulas in Excel, and teaches you what you'll need to know if you are to use Excel successfully.

Read more

3. Use the SUM function to add up a column or row of cells in Excel

Adding up numbers is something most Excel users do every day. There are a number of ways to do this in Excel, and some of them are more efficient than others.

In this lesson you'll learn how to use the SUM function, as well as some handy tips for adding up columns, rows and tables of numbers lightning fast - which means you'll save time.

Read more

4. Absolute and relative references in Excel

Once you get the hang of creating simple formulas and adding up numbers in Excel, the next big thing to learn is the difference between relative and absolute references in Excel formulas. It can take a bit of time to get your head around, but understanding how relative and absolute references work in Excel, and when to use them, is essential to becoming an Excel ninja.

This lesson will show you what absolute and relative references look like, how to create formulas that use them, and when you should (and shouldn't) use absolute references.

Read more

5. Rounding numbers in Excel

It's very important to understand how rounding works in Excel. A common example is rounding prices to two decimal places in a sales spreadsheet. If your formulas don't round price calculations to the correct number of decimal places, then you can end up with sales figures that are incorrect - and hunting down the differences can be a nightmare. And rounding errors can cause havoc with your spreadsheets without you even realising it. A common mistake occurs when you change the display format of a number to show fewer digits after the decimal point and assume that the number has been rounded for use in other calculations.

This lesson explains how rounding in Excel works, and shows you how to avoid common rounding errors in Excel.

Read more

6. Scale your spreadsheet to fit on one page when printing from Excel

Every Excel user has stories to tell about printing their spreadsheet and finding that what should have been a simple one-page spreadsheet has somehow spilled over onto 2 or more pages when they print it out. The likelihood of this happening is usually related directly to how important the document is, and how soon you need it.

In this lesson, you'll learn how to scale your spreadsheet so that it fits on just one page. You'll also learn the failsafe trick for printing your spreadsheet so that it is only one page wide, but is as many pages long as it needs to be.

Read more

7. Print an Excel 2010 spreadsheet with page numbers

There are no two ways about it - page numbers on spreadsheets are important. You'll know what I mean if you've ever sat in a meeting where someone handed out a 20 page spreadsheet without page numbers and then proceeded to spend the meeting making you flip back and forth between pages. Which page are we on now?

In this lesson, you will learn several ways to print your Excel spreadsheets with page numbers on each page, so you never have to be that person in meetings. You'll learn how to print the page number in the same place on every page. You'll also learn advanced tricks such as printing your page numbers in different places on odd- and even-numbered pages, and printing page numbers differently (or not at all) on the first page of your documents.

Read more

8. Freeze or lock rows and columns in an Excel worksheet

When you are working on a big spreadsheet it's easy to get lost as you scroll through your data. For that reason, it can be handy to keep one or more rows and/or columns locked so they don't disappear when you scroll down or across in the spreadsheet.

In this lesson, you'll learn now to use Excel's Freeze Panes feature to lock rows and columns in place in your spreadsheet.

Read more

9. How to use the IF function in Excel to calculate values based on different criteria

The IF function is one of the major building blocks of a successful Excel spreadsheet. As you're learning Excel, it won't be long before you want to write a formula that returns one result if something is true (e.g. pay 5% sales commission if sales are greater than $1000) and another result if it is false (pay 3% sales commission if sales are less than or equal to $1000).

This lesson will show you how to use the IF function, and includes a number of example scenarios in which you might use the IF function as part of your spreadsheets.

Read more

10. How to use Autofilter in Excel

If you have a table of data arranged in columns with multiple rows of information, Autofilter is a very useful tool to know. Autofilter lets you treat a range of cells as a table and then filter out certain rows based on different criteria. For example, you might filter a table of sales data to show only rows where a certain product was sold. Of you might filter the same sales table to show only sales made between two dates, or sales over a certain value.

This lesson covers the basics of setting up a data table in Excel that will be compatible with Autofilter, and then shows you how to enable Autofilter and use it for basic filtering.

Read more