When you are working with a large spreadsheet in Microsoft Excel, it's easy to find yourself scrolling down or across and losing track of where you are. This lesson explains how to freeze rows and columns (officially known as "Freeze Panes") in Excel 2010 for Windows and Excel 2011 for Mac.
Learn Microsoft Excel
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.
If you have a column of numbers and you want to calculate a running total of the numbers alongside, you can use the SUM() formula combined with a clever use of absolute and relative references.
31 comments
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.
2 comments
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.
10 comments
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.
17 comments
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.
13 comments
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.
1 comment
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.
28 comments
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.
322 comments
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.
9 comments
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.
12 comments
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.
3 comments
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.
187 comments
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.
1 comment
If you're using Excel to calculate dates, it is useful to know how to add (or subtract) a certain number of working or business days to a date. This lesson introduces the WORKDAY() function and shows you how to use it.
Pages |
Excel Formulas
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.
Click here to see the full list of Excel Formulas and Functions