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 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.

At its heart, Excel is a giant calculator. In fact, a simple way to think about Excel is to consider each cell in

a worksheet like an individual calculator. An Excel spreadsheet has millions of cells, which means you have

millions of individual calculators to work with. Not only that, but you can create formulas that link different cells

together (e.g. add the value in this cell to the value in that cell). You can create formulas that link cells in

different worksheets together. And you can even create formulas that link cells in different workbooks

together. 

How to enter a formula in Excel

In Excel, each cell can contain a calculation. In Excel jargon we call this a formula. Each cell can contain one

formula. When you enter a formula in a cell, Excel calculates the result of that formula and displays the result of that

calculation to you. In fact, when you enter a formula into any cell, Excel will recalculate the result of all the cells in

the worksheet. This normally happens in the blink of an eye so you won't normally notice it, although you may find

that large and complex spreadsheets can take longer to recalculate.

When entering a formula, you have to make sure Excel knows that's what you want to do. You start by typing the =

(equals) sign, then the rest of your formula. If you don't type the equals sign first, then Excel will assume you are

typing either a number or a text. You can also start a formula with either a plus (+) or minus (-) symbol. Excel will

assume you're typing a formula and insert the equals sign for you.

Here are some examples of some simple Excel formulas and their results:  

In this example, there are four basic formulas:

Addition (+)

Subtraction (-)

Multiplication (*)

Division (/)

In each case, you would type the equals sign (=), then the formula, then press Enter to tell Excel you've finished.

Sometimes Excel will show you a warning rather than just entering your formula. This will happen if the formula

you've typed is invalid, i.e. is not in a format that Excel recognises. It will usually also give you some indication of

what you did wrong.

Other times, Excel may enter the formula you have typed correctly but then show you an error such as #VALUE.

This means that you have entered a formula that was value, but Excel could not calculate a valid result from your

formula.

Creating formulas that refer to other cells in the same worksheet

Excel's power comes from allowing you to create formulas that refer to the values in other cells.

In the example above, you'll notice the headings across the top (A, B) and down the left (1,2,3,4,5). By comining

these values, we have a unique reference each cell in a worksheet (A1, A2, A3, B1, B2, B3, and so on).

When you create a formula, you can refer to other cells using these cell references to incorporate the values in other

cells into a formula. The value in another cell might be a simple number, or another cell containing a formula. When

you create a formula that refers to another cell that also contains a formula, your formula will use the result of the

formula in that other cell. Then, if the result of the formula in that other cell changes, so too does the result in your

formula. Here are some examples of some Excel formulas that refer to other cells:

In this example, rows 6-8 build on the earlier examples to link cells together:

B6 adds the values in B2 and B3 together. If you change either of the values in B2 or B3 the result in B6 will change

too.

B7 and B8 subtract and multiply the values in other cells.

B9 goes a step further and divides B8 by B3. Note that B8 in turn multiplied B5 and B2 together. So changing the

values in either B5 or B2 will have a domino effect, where the value in B8 will change, and so the value in B9 will

change too. Note that Excel handles all of this the moment you finish entering a change in either B5 or B2.

Creating formulas that refer to cells in other worksheets When you first open Excel, you start with a single worksheet. However, Excel allows you to have more than one

worksheet inside a single spreadsheet file (known as a workbook). In fact, in earlier versions of Excel a new

workbook automatically started out with 3 worksheets inside it.

Earlier we saw how to link two cells together within a worksheet by referring to other cells using their cell reference

value. Referring to a cell inside another worksheet works in much the same way, but we need to provide more

information about the location of that cell so Excel knows which cell we're talking about.

Here are some examples of formulas that refer to cells in another worksheet inside the same workbook:

 

In this example, the formulas in B10 and B11 refer to cells in another worksheet called Data.

B10 multiples the value in B9 by the value in cell A2 in the worksheet called Data

B11 takes the value A4 in the worksheet called Data and divides it by the value in B9.

In other words, we've told Excel to go to the worksheet called Data and use values in that worksheet in our

formulas.

There are a couple of ways to create formulas like this:

Type the formula in by hand. In the above example, you would create the reference to the other worksheet by typing

the worksheet name followed by an exclamation mark (!); the exclamation mark tells Excel that you're referring to

another worksheet.

Start typing the formula by typing the equals sign (=), then click on the name of the other worksheet. Excel will

switch to the other worksheet, and you can click on the cell you want to reference in your formula. You can then

press Enter to finish entering the formula, or you can click back on the original worksheet name and finish typing

your formula before pressing Enter. 

Note that if you rename the worksheet called Data, the formulas that refer to Data will automatically update to reflect

the new name. Here's what the above examples look like if we change the name of the worksheet called Data

to Daily Data.

Note how Excel has put apostrophes around the name of the worksheet called Daily Data. This is because of the

space in the worksheet name. Excel does this to make sure that the reference still works; if you manually type the

formula without the apostrophes then Excel will not be able to validate the formula, and will not let you enter it.

Creating formulas that link to other workbooksAs you might imagine what we've already covered, it is also possible to create a formulat that refers to cells in

another workbook (i.e. another file). Once again, it's simply a matter of correctly referring to the cell in the other

workbook.

The following example shows what this looks like:

In this example, B12 contains a formula that refers to cell D6 in a worksheet called Data in a file called Excel-data-

table-xlsx.

The square brackets are used to indicate the filename, i.e. [filename]. Be aware that if the file referred to is not

currently open, the square brackets may also include the full file path to that file, so that Excel can still read the

value from the cell being referred to even though the file is not open.

The apostrophes are used to enclose the full file name and worksheet name.

Then, Excel uses absolute references to identify the cell being referred to. This means that if you move (not

copy) the contents of cell D6 in the Data worksheet, your formula will still work. The $ signs are used to denote an

absolute reference (as opposed to a relative reference). Absolute and relative references are out of scope for this

lesson, but you can read about them in this lesson. 

2. 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.

There are a variety of ways to add up the numbers found in two or more cells in Excel. This lesson shows you

several methods including the SUM function and the Autosum button.

Option One - simple addition

Let's say you have several cells, such as A2, A3 and A4. You can type the following formula into, say, A5:

=A2+A3+A4

If you type this and then press Enter, the result of this calculation will be displayed in cell A5 (the place you typed

the formula). Note that if one or more of the cells are empty or contain text rather than a number, Excel will simply

ignore those values when calculating the result.

Also note that Excel doesn't mind if you type the formula in lower case as shown below - Excel will automatically

convert each letter to uppercase when you press Enter:

=a2+a3+a4

The great thing about adding up cells in this way is that the formula will always show the current value of the sum of

these cells. Any time you change one of the values in cells A2, A3 or A4, the value in A5 will change immediately to

show the correct sum of these cells. If you prefer, you could create this formula more quickly by following these

steps:

Click on the cell where you want the result of the calculation to appear.

Type = (that's the equals sign)

Click on the first cell to be added (A2 in this example)

Type + (that's the plus sign)

Click on the second cell to be added (A3 in this example)

Typel + again, and the next cell to be added. Repeat until all cells to be added have been clicked.

Press Enter.

This will create the formula as shown in the example above without you needing to manually type the whole formula.

Of course, this approach to adding up cells becomes difficult to manage once you have a large number of cells to be

added. For that reason, you may prefer the next method of adding up cells in Excel.

Option Two - use the SUM() function

The SUM() function is a more efficient way of adding up cells since it allows you to specify the first and last cell in a

range of cells to be added up. The SUM() function then adds up all the cells from the start to the end of the range. It

is particularly powerful because it can be used to add up millions of cells in a very short, simple formula.

If we look at our earlier example, you could use SUM() as shown in the following formula, which would achieve the

same result (adding up cells A2, A3 and A4) =SUM(A2:A4)

This formula adds up all the cells from A2 to A4 inclusive. That way, if you were adding up a larger range of cells,

such as from A2 to A200, you could type: =SUM(A2:A200)

You can add up numbers from more than one column at the same time. Let's say that column B also contains

numbers that should be included in the calculation. You could write the formula as follows: =SUM(A2:B200)

You could even add up multiple ranges at once. Let's say that column A, C and E each contain a range of numbers

to be added up as part of the same calculation. Your formula might look like this:

=SUM(A2:A200,C2:C200,E2:E200)

As you can see, this formula specifies three separate ranges, each of which will be included in the same SUM()

calculation.

This brings us to the final way you could use the SUM() function - although I don't recommend it. It's

actually longer than option 1 shown above! =SUM(A2,A3,A4)

Option 3 - Autosum - a shortcutWith Autosum, Excel will automatically add up a range of cells and place the result in a nearby cell. For example,

imagine you have a range of cells from A2 to A4. To use Autosum, select the cells you want to add up, then click the

Autosum button. Excel will put automatically put the SUM() formula in cell A5:

=SUM(A2:A4)

If you wanted Excel to put the resullt in A6 instead, you would select the cells from A2 to A6 then click the Autosum

button. Note that the formula in this case would be:

=SUM(A2:A5)

In other words, it will include the blank cell, A5, in the formula. This can be very useful, although that will be covered

in another lesson.

Finally for this lesson, imagine you had a range of cells from A2 to H4. You want to calculate the sum of each

column from A to H. To do this, select the cells you want to add up, then click the Autosum button. Excel will put the

sum of each column in the row below the range (i.e. row 5 in this case). As before, if you were to select from A2 to

H6, Excel would place the sum of each column into row 6 rather than row 5

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

5 comments

3. 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.

Absolute and relative references in ExcelWhen you create a formula in Excel that refers to other cells in the worksheet, Excel will store the information about

those cells as relative references. Relative references and their counterpart, absolute references, are one of the

things that make spreadsheets such a powerful tool.

Relative referencesA relative reference allows you to describe a cell in terms of where it is relative to the current cell. If you have D14

selected, then D15 can be described as the cell one row down. G10 can be described as the cell that is 3

cells across and 4 cells up from the location of G10.

For example, suppose you put a formula in cell A1 that looks like this:

=B1+B2+B3

Excel actually thinks of these cells, B1, B2 and B3, in terms of where they are relative to A1. So B1 is actually 1 cell

to the right and 0 cells down. B2 is actually 1 cell right, 1 cell down, and so on.

Copying and pasting a formula containing relative references

When you copy the formula from A1 to A2, Excel will adjust the formula automatically to look like this:

=B2+B3+B4

As far as Excel is concerned, this formula simply says: take the value from one cell to the right and add it to the

value from one cell to the right and one cell down and add to that the value from one cell to the right and two cell

down. You can copy this formula to any other cell in the spreadsheet and it wll always add up the same set of

cells relative to the cell that contains the formula.

Absolute ReferencesSometimes you may not want to use relative references. For example, you may have a unit price for a product

entered into a specific cell, and you want to be able to write a formula that will always refer to the cell containing the

unit price wherever you copy it to. To do this, you need to identify the unit price cell in your formula as an absolute

reference.

Cutting and pasting a formula containing absolute references

For example, suppose the unit price is stored in cell A5. You want to create a formula in cell A6 that will multiply a

quantity stored in B10 by the unit price using an absolute reference. This formula would look like this:

=$A$5*B10

The $ signs tell Excel that A5 is an absolute reference. When you copy and paste this formula from A6 to A7, the

formula will change as follows:

=$A$5*B11

Because you copied the formula to the cell in the next row down, B10 has changed to B11. But the reference to A5

hasn't changed because you identified it as an absolute reference.

Here's a quick tip. When entering A5 in the formula, press F4 to convert it to $A$5. If you're using Excel on a Mac,

press Command+T

More on absolute references

Note that there are two $ signs in $A$5 for a reason.

$A means "wherever you copy and paste this formula to, this reference will always refer to column A"

Similarly, $5 means "wherever you copy and paste this formula to, this reference will always refer to row 5".

Here's an example of how this might work.

Suppose you have the following formula:

=$A5*B10

You then copy and paste this formula into the row below. The formula now looks like this:

=$A6*B11

Note that A hasn't changed because you "locked" it using $A. However, you didn't lock the row number, so it was

increased by one when you pasted it in to the row below.

Now imagine you copied and pasted the formula into the next cell across. Now the formula looks like this:

=$A5*C10

Because you pasted the formula across one column but kept it in the same row, $A5 doesn't change. However, B10

changed to C10 because the formula was pasted into the next column along.

Cutting and pasting formulasWhen you cut and paste a formula, absolute and relative references within the formula behave the same - they don't

change. For example, if you have the following formula:

=A5+A6+A7

and you cut and paste it into another cell - any other cell - in the worksheet, the formula will be unchanged.

Similarly, the following formula will not change if you cut and paste it into another cell in the worksheet:

=$A$5+$A$6+A7

4. 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. Androunding 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.

Rounding numbers in Excel

Rounding in Excel refers to reducing the number of digits in a number to make it easier to work with. A common

example is rounding a price to two decimal places. 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 use the different rounding functions

available in Excel.

Rounding versus Formatting in ExcelAs I've already mentioned, a very common mistake people make when creating Excel spreadsheets is to assume

that using the formatting options in Excel to change how numbers display also causes those numbers to be

rounding. Even seasoned Excel professionals get caught out by this one, and end up scratching their head while

they try to figure out why their calculations don't work out as expected. To see what I mean, check out these

examples.Imagine you have a spreadsheet with the following numbers which you format to only show two decimal

places:

Note how the third and fourth numbers look different when rounded down to two decimal places. The reality is that

both columns are the same - they just look different because of the formatting. Here's what happen when you

multiple the numbers in each column by 100:

In the next example, there is no difference between the numbers in the second and fourth columns - even though

the numbers in column three look different, the numbers being stored by Excel are the same. If you want Excel to

see them differently, you will need to use one of the Rounding functions to round the numbers to the number of

digits or decimal points you want to work with.

If you're not sure how to round numbers, you can read more about it here:

o http://www.factmonster.com/math/numbers/rounding.html 

o and in a little more detail here: http://math.about.com/od/arithmetic/a/Rounding.htm

The ROUND functionThe simplest way to round numbers is to use the ROUND function:

=ROUND(number,num_digits)

The values in this function are as follows:

o number is the number you want to round. This can be a number, a reference to a cell with a number in it, or the

result of another calculation inside a cell.

o num_digits is the number of digits the number should be rounded to.

You can use num_digits in several ways depending on the result you want:

o If num_digits is greater than zero, the ROUND function will round your number to that many digits after the

decimal point.

o If num_digits is zero, the ROUND function will round your number to the nearest integer (i.e. the nearest whole

number)

o If num_digits is less than zero, the ROUND function will round the digits to the left.

The following example shows how ROUND can be used to round a number to two decimal points (column C in this

example shows the formula used in column B):

This example shows ROUND with a range of values for num_digits ranging from -2 to 2. Note how the negative

numbers round the values to the nearest 1 (if set to 0), the nearest 10 (if set to -1), and the nearest hundred (if set

to -2):

You could also use the ROUND function inside another formula, as shown in this example. Note how the FOUND

function is used to round the values in column A before completing the calculation, which is to multiply the rounded

number by 10.

5. 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.

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.

Scaling your spreadsheet when printingYou 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.

If you're using Excel 2010 

1. Click the Page Layout tab.

2. Set the Scale to Fit options for Width and Height to the values you want.

3. Print your spreadsheet.

If you're using Excel 2007 and earlier for PC, or Excel for Mac 2008 or 2011

1. Open Page Layout

2. In Excel 2007, click the Page Layout tab, then click the small arrow in the bottom right corner of the Page

Setup group (this also works for Excel 2010 as an alternative to the instructions above)

3. In earlier versions of Excel, and for Excel 2008 and 2011 for Mac, click File, then Page Setup.

4. Enter the number of pages wide and tall you want your spreadsheet to be when printed. For example, you may

choose to set your spreadsheet to be 1 page wide and 2 pages tall when printed.

5. Print your spreadsheet.

The problem with this approach is that you can find your spreadsheet is scaled down too far and becomes too small

to read. Not only that, but Excel ignores any manual page breaks you've entered.

Scaling an Excel spreadsheet to a specific number of pages

Suppose you want your Excel spreadsheet to print out one page wide, but you don't mind how many pages tall the

print out is. It could also be that you have horizontal page breaksthat you want to keep when you print your

spreadsheet.

Scaling to Fit in Excel 2010 (method 1) 

1. Click the Page Layout tab.

2. Set the Scale to Fit option for Width to be 1 page.

3. Set the Scale to Fit option for Height to be Automatic.

4. Print your spreadsheet.

Scaling to Fit in Excel 2010 (method 2)

1. Choose File > Print

2. Choose Fit all columns on one page

3. Print your spreadsheet

Both methods will scale your spreadsheet so it prints out exactly one page wide. The number of pages it prints will

depend on how many pages tall the scaled down spreadsheet is. Excel will ignore any vertical page breaks you've

inserted when you do this, but will keep your manual horizontal page breaks.

Of course, if you want the spreadsheet to print out just 1 page tall, and as many pages across as it needs, you'd

reverse the values in steps 2 and 3 of method 1 above.

Scaling to fit in other versions of Excel (PC and Mac)

You can get the same result when printing from other versions of Excel for both PC and Mac. This tip works in Excel

2010 as well, but the instructions above are an easier way to do it.

1. Open Page Setup.

2. Click the Page tab.

3. Click the Fit To option.

4. Enter 1 for the number of pages wide.

5. Delete the value for the number of pages tall and leave it blank.

6. 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.

In this lesson, we will cover how to print an Excel spreadsheet with page numbers on each page. Excel has

changed a lot over the years, but one thing that hasn't changed is the way that you insert page numbers into a

spreadsheet. Unfortunately, Excel 2010 does not make it easy to find the options for doing this. This lesson covers

the basics of inserting page numbers as well as providing an overview of some more advanced options.

Printing standard Excel page numbersTo print your Excel 2010 spreadsheet with page numbers on each page, follow these steps. Note that you won't see

the results of these steps until you either print or preview the spreadsheet for printing:

Select the worksheet you want to print

Choose the Insert menu. This will display the Header and Footer Tools toolbar in the ribbon menu.

The display will change to show the spreadsheet in Page Layout mode. The Header and Footer Tools toolbar will be

displayed:

You can click the Header or Footer buttons to insert from a range of pre-set options for the top and/or bottom of the

page.

o These include page numbers as well as other standard document options such as file name, worksheet name, print

date and other.

o You can choose each in turn to see a preview of your spreadsheet with the selected options.

By default, the Header/Footer view will open to edit the Header. You can use theNavigation buttons to Go to

Header or Go to Footer.

You can use the other buttons to insert specific Header and Footer elements.

o Page Number

o Number of pages (this will change when you print to reflect the actual number of pages being printed)

o Current date (i.e. the date when the spreadsheet is printed - good for version control)

o Current time (i.e. the time when the spreadsheet is printed - also good for version control)

o File path (i.e. where on your hard drive the file is saved)

o File name (the name of the current spreadsheet file)

o Sheet name (the name of the current worksheet)

o Picture (e.g. a logo).

o Format Picture (when a picture has been inserted and selected, you can format the picture)

You can also choose some additional options, such as:

o Whether the first page in the printout should have a different header and footer to subsequent pages

o Whether odd and even pages should be formatted differently. For example, if you are printing your spreadsheet

double-sided and plan to bind it, you may want the page numbers to appear on the outside edge of each page.

o Whether to align the header/footer with the page margins on the page.

o Whether to scale the header/footer when you scale the spreadsheet for printing.

Inserting Page Numbers - alternative optionThe following steps are another way to set up page numbering. You may find this method useful if you are used to

older versions of Microsoft Excel.

Choose the Page Layout menu. This will display the Page Layout toolbar in the ribbon menu.

Click the arrow in the bottom right corner of the Page Setup section of the Page Layout toolbar (see the image

above).

This will display the Page Setup dialog box. From here, click the Header/Footer tab. Note that another way to

access this dialog box is to choose File > Print and then select Page Setup.

 You can now click either the Header or Footer option to choose from several standard options for printing page

numbers (as well as other options at the top or bottom of each sheet.

o For example, if you choose Page 1 of ?, then Excel will print the page number and the total number of pages in the

printout in the centre each page (e.g. Page 2 of 7).

o If you choose Confidential, 12/03/2012, Page 1, Excel will print Confidential on the left side of each page, the

current date in the centre of each page, and the page number on the right of each page.

o These options are available for both the Header and Footer.

Once you have chosen your header/footer options to include page numbers (as well as other things), you can now

print yoru spreadsheet to see the results. Choosing Print Preview will allow you to view your changes before you

print.

Customizing Excel page numbers - alternative option.

To customize the page numbering in your Excel spreadsheet using options not available in the standard options

described above, follow these steps. These steps are mostly the same in older versions of Excel, as well as Excel

for Mac:

Decide whether you want to print your spreadsheet with Different odd and even pages. This is useful if you are

printing your spreadsheet onto double-sided sheets for binding. Otherwise you can probably ignore it.

Decide if you want a Different first page. This is useful if the first page will be a cover sheet and you don't want to

print the page number on that page (although note that this will not change how page numbers are assigned).

Click either Customer Header or Custom Footer. The screenshot below assumes you chose both options

described in the points above.

 You can now click in each region of the header (Left, Center, Right) and enter a custom value.

You can enter text directly into each region.

You can insert variables using the buttons shown above. Float your mouse over each button to see its function.

Each button is described below in the order shown above:

o Format the text in each section. This displays the standard text formatting dialog box.

o Insert the page number - &[Page]

o Insert the number of pages in the document - &[Pages]

o Insert the date (this will be the date when the spreadsheet is printed or previewed) - &[Date]

o Insert the time (similarly, this will be the time when the spreadsheet is printed or previewed) - &[Time]

o Insert the File Path (i.e. the path on your hard drive where the file is located - &[Path]

o Insert the name of the file being printed - &[File]

o Insert the name of the worksheet being printed - &[Tab]

o Insert Picture - this could be a logo, or a watermark. Clicking this button will allow to choose a picture from your hard

drive to insert.

o Format Picture - once you have chosen a picture, you can use the standard Microsoft Office image formatting tools

to format the picture you have uploaded.

Note that if you chose the options for different odd/even pages, or different first page, you can set these values

using the additional tabs shown in the screenshot above

7. 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. 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.

Why you might need to freeze rows or columns in your spreadsheet

Imagine you have a spreadsheet that contains sales data for January. The worksheet contains daily data that

reports the sales for each person in your sales team, broken down by products sold:

This example actually has 85 rows of data (the table carries on down further than this screenshot shows):

Once you scroll down, however, the heading row disappears off the top of the screen, and you can no longer be

sure what each column contains:

  

 This is a simple example, but it's not hard to imagine that with a lot more columns and rows, the problem would get

considerably more complex,

To solve the problem, you can freeze or lock the heading rows so that they don't disappear off the top of the screen

as you scroll down the worksheet. The proces for doing this is slightly different between Excel 2010 for Windows

and Excel 2011 for Mac, so I've covered both here:

How to freeze rows and columns

You have two options for freezing panes in Excel. Note that these steps also apply to freezing columns:

o Freeze several rows and/or columns

o Freeze just the first row (or column)

To freeze just one row, click the View menu, and find the Freeze Panes button (if you're using Excel 2011 for Mac,

click the Layout menu to find the Freeze Panes button)

 

When you click the Freeze Panes button, you can choose Freeze Top Row from the expanded Freeze Panes

options. If you wanted to freeze the first column, you would then go back and choose that option. The screenshot

below is from Excel 2010 for Windows. In the Mac version of Excel the options are the same, but you don't get the

explanations of each option that you see here:

Things get slightly more complicated if you want to freeze more than one row or column. If you look at the first

screenshot in this lesson, you'll see that the first row doesn't actually contain the headings for the sales data table -

it contains the title of this worksheet.

To freeze the heading row of the table, you will have to freeze the first five rows in the worksheet. To do this, click in

the cell A6 (i.e the first row that should not be frozen) and choose the first option in the Freeze Panes drop-down

menu (it's also called Freeze Panes).

When you do this, not much will appear to change. All you'll see is a line stretching across the screen, almost like a

border along the bottom of row 5 (which is the last row to be frozen in our example). The screenshot shows what

Freeze Panes looks like if you had clicked B6 before clicking Freeze Panes (i.e. you wanted rows 1-5 and column A

to be frozen):

Here's what the sales data table looks like if you scroll down. As you can see, the first five rows have stayed put,

and the other rows have disappeared underneath them as I've scrolled down the screen:

How to unfreeze panes in Excel

Unfreezing panes is, fortunately, fairly simple:

In Excel 2010 for Windows, choose the View menu, click the Freeze Panes button. The first option, which was

Freeze Panes, is now Unfreeze Panes. Click that option and the frozen rows will be unfrozen.

In Excel 2011 for Mac, choose the Layout menu and choose Unfreeze Panes (for some reason, it's a separate

option which only becomes available once you have frozen panes).

8. 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.

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() Function SyntaxThe IF() function has the following syntax:

=IF(logical test, value if true, value if false)

The IF function works by performing a logical test that can only have one of two outcomes - TRUE or FALSE. It then

outputs a result based on the outcome of that test.

Logical Tests in Excel

You can use anything as a logical test provided Excel can determine whether the outcome is TRUE or FALSE.

Some examples of logical tests that you can use with the IF function include:

C5=C6 (compare cell C5 to C6. If they are equal, the outcome is TRUE, otherwise the outcome is FALSE).

C5>C6 (if C5 is greater then TRUE, otherwise FALSE)

C5="" (if cell C5 is empty then TRUE, otherwise FALSE)

SUM(A5:A10)>B5 (if the sum of cells A5 to A10 is greater than B5 then TRUE, otherwise FALSE).

There are a number of ways to construct more complex logical tests which we won't cover here.

Examples of the IF() function in action

To use the IF() function, follow these steps:

Click on a cell and enter the IF() function:

Enter the logical test as shown in this example:

The value in the Result column is the outcome of the IF formula

o The logical test checks to see whether the cell in the Day column (B5) = "Wednesday" (we use the speech marks to

tell Excel the value we're performing the test on is text rather than a number)

o If the value in the Day cell is "Wednesday", then the result will be Yes.

o Otherwise the result will be No, as it is in this case.

There are many ways to use the IF() function in Excel that we won't cover here, but look out for more advanced IF()

lessons coming soon.

9. 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.

How to use Autofilter in Excel

Autofilter is one of the most powerful features of Excel if you need to work with data in tabulated (table) format. It

lets you treat a range of cells as a table and then filter out certain rows based on different criteria. It is very powerful

if you need to "mine" data in a list and find out specific information about the data in that list. This tutorial covers how

to set up a data table in Excel to use with Autofilter, and also shows you how to enable Autofilter and use it for basic

filtering. This lesson is applicable for all versions of Excel (including Excel for Mac) although the visual presentation

of the options may change from version to version.

Prepare your data for AutofilterTo use Autofilter, first ensure you have a table of data to work with. Follow these simple steps to check that your

data table is ready to use with Autofilter:

Check that your data is organised in columns. Each column should have a heading that explains what sort of

data is in the column.

o Your column headings should occupy just one row of the spreadsheet, as shown in this example:

o You should avoid using column headings in two rows, as shown in the following example, because they can

confuse Excel's Autofilter feature and stop it working as it should. While you can still use Autofilter if you have

headings in two rows as shown here, it's much easier if you stick to headings in just one row:

Next, make sure there are no empty columns or rows in your data. Excel is good at sensing the start and end of

a data table by looking for empty rows and columns, and will ignore data after an empty row or column.

o A quick tip to check if your data is formatted in one contiguous range (a fancy way of saying "one block of data",

which is used a lot in the Excel world) is to click a single cell in the table then press CTRL+* (or CTRL+SHIFT+8 if

you don't have a separate number keypad). This automatically selects the whole table. You'll then see if you have

any problems with the layout of your table.

o This example shows a table that is in a contiguous range:

o This table has an empty row; after pressing CTRL+* to select the table, you can see that the rows after the empty

column have been ignored:

o Similarly, this table has an empty column, so the columns to the right of the empty column have been ignored:

o Note that empty cells within the table are OK. What isn't OK is a whole row or a whole column of empty cells.

Finally, check that you have consistent data types in all cells.

o For example, if you have a date column, make sure all the values in that column are dates (or blank).

o If you have a quantity column, make sure all the values are numbers (or blank) and not words.

o This is not a hard and fast rule, but it will make it much easier to use the features of Autofilter if you follow them

wherever possbile.

At this point, if everything is looking OK, you're ready to move on to the next step.

Enable AutofilterOnce your data range that meets the criteria above, you are ready to apply the Autofilter feature:

Select a cell in the range.

o Any cell will do, but make sure you don't select more than one cell or Excel will apply the Autofilter to the selected

cells rather than the whole table.

o You may prefer to select all of the cells in the data range if you want - this is a good idea if you have columns with

headings in two or more rows (as discussed above).

Next, click the Sort and Autofilter button on the Home tab of the Excel ribbon toolbar, then click Filter.

The first row in the table (the header row) should change, with a small drop-down arrow on each cell in the header

row, similar to the example below:

You are now ready to start using Excel's Autofilter feature.

Using Excel's Autofilter feature to filter your listYou can now filter the table based on the values in the table by clicking the buttons to the right of the column

heading. This will show a drop-down list as shown here:

In this example, we've clicked the Autofilter button for the Salesperson. Based on the selection we have made, the

list will be filtered to show only rows where Mike is the Salesperson. Other options you have here will allow the

following:

Sorting by Salesperson. In this case the list could be sorted alphabetically in ascending (A to Z) or descending (Z

to A) order.

If you are using Color formatting on the cells in the Salesperson column, you could also choose to sort by Color.

This option also hides the very useful (and powerful) Custom Sortoption which allows you to sort the list by

multiple criteria, such as sorting by Salesperson and then by Item.

You could also select more than one salesperson from the list by checking the box next to the name of each

salesperson you want to filter by.

Text Filters allow you to use various advanced text filtering options, rather than choosing specific people from the

list. One example would be to filter by all salespeople whose name starts with M.

The Search option is useful when you have a large list of unique records (in this case that would mean lots of

different salespeople), so the quickest way to find a specific person is to type their name, rather than scrolling

through the whole list. This option was introduced in Excel 2010, and is also available in Excel 2011 for Mac and

Excel 2013 for Windows.

Once you click OK with the selection above, the list will look like this:

It is important to note two things about this list now that it has been filtered:

There is a different filter icon at the top of the Salesperson column, indicating that this list has a filter applied on the

Salesperson column.

The row numbers are now shown in blue, indicating that the list has been filtered. Note how the row numbers now

skip row numbers for rows that don't match the filter.

This screenshot is just a portion of the full list of sales records for Mike. The Excel status bar at the bottom left of the

screen will display the total number of records returned by the filter, as shown here:

Using Autofilter to filter by more than one criteriaThe above example used just one criterion - Salesperson - to filter the list. However, you can filter by multiple criteria

if you want. For example:

Show only rows that include sales by Mike, where he sold Washing Marchines (filter by Salesperson=Mike and

Item=Washing Machine)

Show only rows that include sales by Mike where he sold more than 10 units (filter by Salesperson=Mike and

Quantity>10)

Show only rows that include sales of Refrigerators that were made on the 2nd, 3rd or 4th of January.

Once you start working with Autofilter, you will find it an excellent and powerful tool for for filtering and mining data

from your spreadsheets. The next lesson will cover more advanced filtering options, and will also show you how to

perform calculations that change according to criteria you have used to filter the list.

Read more

1 comment

10. 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.

How to create a Pivot Table in ExcelExcel'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. If you are finding yourself writing lots of formulas to summarise

data in Excel (using functions such as SUMIF and COUNTIF) then Pivot Tables can save you a lot of time and work

and give you insights into your data that are otherwise too hard to discover. Not only that, but they also allow you to

quickly change how your data is summarised with almost no effort at all. This lesson will show you how to create a

simple pivot table in Excel to summarise a set of daily sales data for a team of several sales people.

What you'll learn in this lessonIn this tutorial, you'll learn how to:

Set up your data in Excel so it is in a format that you can use for a pivot table.

Create a pivot table with that data

Change the pivot table report to reflect different views on the same data.

The data we'll work with in this example is an Excel table that has two months of daily sales data for a team of four

sales people, broken down by product. The first few rows are shown below:

In fact, this spreadsheet extends down for 688 rows of sales data, for all of January and February. So while you

might look at the data in the table above and think "I could summarise that quickly by hand or with a few clever

formulas", the likelihood is that it would all get too much - and would certainly take too long to do by hand. That's

where pivot tables are by far the best solution - you'll be able to convert this data in under a minute, and be able to

get different summaries with a few clicks of the mouse.

Getting started with Pivot Tables - make sure your data is readyThere are some important rules you need to follow if you want to create a pivot table from your data:

You need to have a your data organised in columns with headings. These headings will be used when you create

the pivot table, and things will get very confusing without headings.

Make sure there are no empty columns or rows in your data. Excel is good at sensing the start and end of a data

table by looking for empty rows and columns at which point it stops.

o A quick tip to check if your data is formatted in one contiguous range (a fancy term way of saying "one block of

data") is to click a single cell in the table then press SHIFT+* (or CTRL+SHIFT+8). This automatically selects the

whole table. You'll then see if you have any problems with the layout of your table.

o Note that empty cells are OK. What isn't OK is a whole row or a whole column of empty cells.

Consistent data in all cells.

o If you have a date column, make sure all the values in that column are dates (or blank).

o If you have a quantity column, make sure all the values are numbers (or blank) and not words.

At this point, if everything is looking OK, you're ready to move on to the next step.

Create a blank Pivot TableTo start your pivot table, follow these steps:

Click on a cell in the data table. Any cell will do, provided your data meets the rules outlined above. In fact, at this

point it's all or nothing - select the whole table or just one cell in the table. Don't select a few cells, because Excel

may think you are trying to create a pivot table from just those cells.

Click on the Insert menu and click the PivotTable button:

The following dialog box will appear:

Note that the Table/Range value will automatically reflect the data in your table (you can click in the field to change

the Table/Range value if Excel guessed wrong). Alternatively, you can choose an external data source such as a

database (we'll cover that another day!)

Also notice that you can choose where the new PivotTable should go. By default, Excel will suggest a New

Worksheet, which I think is the best choice unless you already know you want it on an existing worksheet.

o Be warned that if your data changes a lot, or you find yourself changing the Pivot Table layout a lot, then refreshing

the data in your Pivot Table can result in the Pivot Table changing shape and covering a larger area. If you have

data or formulas in that area, they'll disappear. Therefore, putting a Pivot Table on the same page as data or other

information can cause you real headaches later on, and thats' why New Worksheet is the recommended option.

Once you've completed your selections, click OK. Assuming you chose the New Worksheet option, Excel will create

a new worksheet in the current workbook, and place the blank PivotTable in the worksheet for you. You are now

ready to design your Pivot Table.

Designing your PivotTable layout. When you switch to the worksheet with your new Pivot Table, you'll notice three separate elements of the Pivot

Table on the screen, starting with the PivotTable report itself:

Then you'll see the Pivot Table Field List and under that the field layout area. Note that it should show the column

headings from your data table.

  

To create the layout, you need to first select the fields you want in your table, and then place them in the correct

location.

o You can check the boxes for the fields you want to include, and Excel will guess which area each field should be

placed in. However, the Pivot Table is recalculated each time you check one of the boxes which can slow you down,

especially if Excel places a field in the wrong place.

o Therefore, I recommend you drag and drop each field to the area you want it to be.

As an example, here are the Field List and the Field Layout area above with the fields in place to show a report with:

o Each day down the left, with each sales person listed separately for each day

o Items shown across the top.

o The total quantity of items sold for each cell in the Pivot Table.

Here is how to layout this report:

   

The report that this generates looks like this:

Notice how the Pivot Table has automatically created a list of the sales people for each day covered in the source

data.

Hopefully this lesson has got you started with PivotTables. If you're looking for more lessons,visit our Pivot Table

page here. This book by Bill Jelen is also well worth a look.