The IF statement is a simple function in Excel that is one of the building blocks you need when you are working with large spreadsheets. You may not know you need it yet, but once you know how to use it, you won't want to live without it.
If you want to learn Microsoft Excel, you're in the right place. There is a lot to learn about Microsoft Excel, and not everything is in the manual. We've got a range of free online lessons on how to get the best out of Excel, starting from the basics right up to advanced subjects. We'll help you to do your job better - with the right Excel skills you could even get a raise or a better job! If you don't see what you want to learn, why not get in touch and suggest a lesson we should write.
In this lesson, we look at a specific example where you have a table of sales data, and you need to find out the name of the person who had the highest sales for the month. It's one of those things that seems like it should be easy until you actually try to do it. The solutions we present here are not the only way of achieving this, but the do have the advantage of solving the problem with a single formula. The methods here could also be used for a variety of other applications as well.
If you're getting started with Excel, creating formulas is one of the first things you should learn. In this lesson you'll learn how to create simple formulas and calculations in Excel.
If you want to combine text with the results of a formula in a cell, you can use concatenation. Suppose you have calculated the total of a range of cells using a formula in cell D2. Now, you want to have cell A2 display the text "Today's sales are $12,000", where $12,000 is the value calculated in D2. As the value in D2 changes, you want the value in A2 to update automatically.
This lesson explains how to use Excel's logical operators and logical functions (AND, OR, NOT). These boolean operators can be used in a number of ways, either on their own, or to complement other Excel functions. In many cases, you can even use one of these functions instead of a more complicated function. For example, it is often easy to rewrite an IF statement using either AND or OR. This lesson includes examples of how to do this.
The IF() function in Excel allows you to evaluate a situation which has two possible outcomes (e.g. sales are greater than $1000) and calculate a different value for each outcome. However, sometimes you need to work with situations where there are more than two possible outcomes. That's where multiple, or nested, IF functions come in handy. In this tutorial we'll cover how to use nested IF functions to calculate sales commission for a team of sales people, given a range of different commission rates.
If you have a spreadsheet with time values that have been added to the spreadsheet as text values, you need the TIMEVALUE function. This will allow you to convert the text values into valid time values. A common scenario where this might be useful is when you've been provided data to import into Excel, and the times in the imported data are not recognised by Excel as valid times.
Excel's Pivot Table feature is an incredibly powerful tool that makes it easy to tabulate and summarise data in your spreadsheets, particularly if your data changes a lot. This lesson will show you how to create a simple pivot table in Excel to summarize a set of daily sales data for a team of several sales people.
If you're using the Autofilter feature, you're probably wondering how to perform calculations on only those values that are being displayed by the filter. The SUBTOTAL() function is the answer.
INDEX is an Excel function that allows you to find a value inside a list or table of data in Excel. If you know (or can calculate) the row and column position of the value you want, INDEX is the function to retrieve that value for you. This lesson shows you how to use the INDEX function and includes some simple examples to help illustrate how you can use it.
Excel offers a couple of handy functions that you can use to calculate the smallest and largest values in a range of cells. They are simple functions that go by the names of MIN() and MAX(). This lesson shows you how to use them. It also introduces SMALL() and LARGE(), functions which duplicate what MIN and MAX do, plus more besides.
When you create a new Pivot Table, Excel either uses the source data you selected or automatically selects the data for you. But data changes often, which means you also need to be able to update your pivot tables to reflect the new or changed data. This lesson shows you how to update existing data, and add new data to an Excel pivot table.
Printing from Excel can be very frustrating, especially if your spreadsheet is too wide or too tall to fit on a single page.
You can use the Scaling option in Page Setup to set limits on how many pages wide and tall your document should be when you print it. The problem with that is that you can find your page fits onto one page, but becomes too small to read. Not only that, but Excel ignores any manual page breaks you've entered. This lesson explains how you can print your spreadsheet so it automatically scales to be one page wide without forcing the rows into a single page.
If you are working with large tables of data in Excel, you can make your spreadsheet easier to read by formatting alternate rows to be shaded a different colour. There are a number of ways you can achieve this. This lesson shows you a quick and easy way to do it on Excel 2011 for Mac.
Excel's VLOOKUP function is excellent when you want to find a value in a table based on a lookup value. But if your table includes your lookup value multiple times, you'll find that VLOOKUP can't do it. This lesson shows you how to use the INDEX function (plus some other functions) to find all matching values in a list, and return a value from another column in the same row. It also looks at how to do this when you want to return all values which are a partial match (i.e. a wildcard search) to the values in your lookup table.
Do you need help with an Excel formula or function? We have lessons on a range of different Excel functions, and the list is growing all the time.