Chapter 7 Processing Data in Excel

Data Tables in Excel

In Excel, a Data Table is a way to see different results by altering an input cell in your formula. As an example, we're going to alter the interest rate, and see how much a £10,000 loan would cost each month. The interest rate will be our input cell. By asking Excel to alter this input, we can quickly see the different monthly payments. Want to know how much we'd pay back each month if the interest was 24 percent per year. But other banks may be offering better deals. So we'll ask Excel to calculate how much we'd pay each month if the interest rate was 22 percent a year, 20 percent a year, and 18 percent a year.

The formula we need is the Payment one you met in a previous section - PMT( ). Here it is again:

PMT(rate, nper, pv, fv, type)

We only need the first three arguments. So for us, it's just this:

PMT(rate, nper, pv)

Rate means the interest rate. The second argument, nper, is how many months you've got to pay the loan back. The third argument, pv, is how much you want to borrow.

Let's make a start then. On a new spreadsheet, set up the following labels:

So we'll put our starting interest rate in cell B3 (rate), our loan length in cell B4 (nper), and our loan amount in cell B5 (pv).

Enter the following in cells B3 to B5:

So you need to enter 24.00% in cell B3, 60 in cell B4, and £10,000 in cell B5.

We'll enter our formula now. Click inside cell D2 and enter the following:

=PMT(B3 / 12, B4, -B5)

Cell B3 is the interest rate. But this is for the entire year. In the formula, we're diving whatever is in cell B3 by 12. This will get us a monthly interest rate. B4 in the formula is the number of months, which is 60 for us. B5 has a minus sign before it. It's a minus figure because it's a debt.

When you press the enter key on your keyboard, Excel should give you an answer of £287.68.

Now that we have our function in place, we can create an Excel Data Table. First, though, we need to tell Excel about those other interest rates. It will use these to work out the new monthly payments. Remember, Excel is recalculating the PMT function. So it needs some new values to calculate with.

So enter some new values in cells C3, C4, and C5. Enter the same ones as in the image below:

We have put the PMT function in cell D2 for a reason. This is one Row up, and one Column to the right of our first new interest rate of 22%. The new monthly payments are going to go in cells D3 to D5. Excel needs the table setting out this way.

So that Excel can work out the new totals, you have to highlight both the new values and the Function you're using.

So highlight the cells C2 to D5. Your spreadsheet should look like this:

As you can see, the cells C2 to D5 are now highlighted. This includes our new interest rate values in the C column, and our PMT function in cell D2. We can now create an Excel Data Table. This will work out new monthly payemnts for us. So do this:

• From the Excel menu bar, click on Data
• Locate the Data Tools panel
• Click on the "What if Analysis" item:

When you click on the "What if Analysis" item, you'll see the following menu:

Click on Data Table, and you'll see this small dialogue box:

In the dialogue box, there is only a Row input cell or a Column input cell. We want Excel to fill downwards, down a column. So we need the second text box on the dialogue box "Column input cell". If we were filling across in rows, we would use the "Row input cell" text box.

The Input Cell for us is the one that contains our original interest rate. This is the cell you want Excel to substitute.

So click inside the Column input cell box and enter B3:

Click OK. When you do, Excel will work out the new monthly payments:

So if we could get an 18 percent interest rate, our monthly payments would be £253.93.

If you click inside any of the cells D3 to D5, then look at the formula bar, you will see this:

{=TABLE(,B3)}

That's Excel's way of telling you that a Table has been created, based on the input cell B3

We'll try one more Data Table in the next part. We'll try an easier formula, this time

A Second Data Table

We'll do one more Data Table, just so that you get the hang of things. This time, we'll use a more simple formula than PMT, and we'll use Rows instead of Columns. This is the scenario:

You have 250 items that you want to sell on EBay. Your unique selling point is this - All items are only £5 each! Except, you feel £5 may be a bit expensive for the goods you're selling! What you want to know is how much profit you'll make if you reduce your prices to £4.50, how much if you reduce to £4.00, and how much for a reduction to £3.50. Assume that everything gets sold.

To start creating your Table, construct a spreadsheet like the one below. Make sure that you start on a new sheet.

In cell B1 is the number of items we want to sell (250). Cell B2 has the original price (£5.00). And the Reductions Row has our new values. Cell B3 has a 0 because there's no reduction for £5.00. Row 4 is where our Profits will go.

The formula to work out the profits is simply the Number of Items multiplied by thePrice Per Item. So click inside cell B4 and enter the following formula:

= B1 * B2

Your spreadsheet will then look like this:

So if we manage to sell all our items at £5, we'll make £1,250. We're a bit dubious, though. Realistically, all our items won't sell at this price! Let's use an Excel Data Table to work out how much profit we'd make at the other prices.

Again, we put the answer in cell B4 for a reason. This is because when you want Excel to calculate a Data Table in Rows, the formula must be inserted one Column to the Left of your first new value, and then one Row down. Our first new value is going in cell C3. So one column to the left takes us to the B column. One row down is Row 4. So the formula goes in cell B4.

Next, click inside cell B3 and highlight to cell E4. Your spreadsheet should now look like this one:

Excel is going to use our formula in cell B4. It will then look at the new values on Row 3 (not counting the zero), and then insert the new totals for us. To create a Data Table then, do the following:

• From the Excel menu bar, click on Data
• Locate the Data Tools panel
• Click on the "What if Analysis" item
• Select Data Table from the menu

Just like last time, you'll get the Data Table dialogue box. The one we want now, though, is Row Input Cell. But what is the Input Cell this time?

Ask yourself what you are trying to work out, and what you want Excel to recalculate. You want to work out the new prices. The formula you entered was:

= B1 * B2

Excel is going to be changing this formula. You only need to decide if you want Excel to alter the B1 or the B2. B1 contains the number of items; B2 contains the price of each item. Since we're trying to work out the profits we'd get if we change the price, we need Excel to change B2. So enter B2 for the Row Input Cell:

When you click OK, Excel will work out the new profits:

So setting a price of £3.50 per item, you'd make £875 profit. You'd make £1,000 at £4.00 per item, and £1,125 if you sell for £4.50.

Hopefully, Data Tables weren't too difficult! But they are a useful tool when you want to analyse values that can change.

Scenarios in Excel

Scenarios come under the heading of "What-If Analysis" in Excel. They are similar to tables in that you are changing values to get new results. For example, What if I reduce the amount I'm spending on food? How much will I have left then? Scenarios can be saved, so that you can apply them with a quick click of the mouse.

An example of a scenario you might want to create is a family budget. You can then make changes to individual amounts, like food, clothes, or fuel, and see how these changes effect your overall budget.

We'll see how they work now, as we tackle a family budget. So, create the spreadsheet below:

The figure in B12 above is just a SUM function, and is your total debts (=SUM(B3:B10). The figure in D3 is how much you have to spend each month (not a lot!). The figure in D13 is how much you have left after you deduct all your debts. In cell D13, then, enter =D3 - B12

With only 46 pounds spending money left each month, clearly some changes have to be made. We'll create a scenario to see what effect the various budgets cuts have.

• From the top of Excel click the Data menu
• On the Data menu, locate the Data Tools panel
• Click on the What if Analysis item, and select Scenario Manager from the menu:

When you click Scenario Manager, you should the following dialogue box:

We want to create a new scenario. So click the Add button. You'll then get another dialogue box popping up:

The J22 in the image is just whatever cell you had selected when you brought up the dialogue boxes. We'll change this. First, type a Name for your Scenario in theScenario Name box. Call it Original Budget.

Excel now needs you to enter which cells in your spreadsheet will be changing. In this first scenario, nothing will be changing (because it's our original). But we still need to specify which cells will be changing. Let's try to reduce the Food bill, the Clothes Bill, and the Phone bill. These are in cells B7 to B9 in our spreadsheet. So in the Changing Cells box, enter B7:B9

Don't forget to include the colon in the middle! But your Add Scenario box should look like this:

Click OK and Excel will ask you for some values:

We don't want any values to change in this first scenario, so just click OK. You will be taken back to the Scenario Manager box. It should now look like this:

Now that we have one scenario set up, we can add a second one. This is where we'll enter some new values - our savings.

Click the Add button again. You'll get the Add Scenario dialogue box back up. Type a new Name, something like Budget Two. The Changing Cells area should already say B7:B9. So just click OK.

You will be taken to the Scenario Values dialogue box again. This time, we do want to change the values. Enter the same ones as in the image below:

These are the new values for our Budget. Click OK and you'll be taken back to the Scenario Manager. This time, you'll have two scenarios to view:

As you can see, we have our Original Budget, and Budget Two. With Budget Two selected, click the Show button at the bottom. The values in your spreadsheet will change, and the new budget will be calculated. The image below shows what it looks like in the spreadsheet:

Click on the Original Budget to highlight it. Then click the Show button. The first values will be displayed!

Click the Close button on the dialogue box when you're done.

So a Scenario offers you different ways to view a set of figures, and allows you to switch between them quite easily.

How to Create a Report from a Scenario

Another thing you can do with a scenario is create a report. To create a report from your scenarios, do the following:

• Click on Data from the Excel menu bar
• Locate the Data Tools panel
• On the Data Tools panel, click What if Analysis
• From the What if Analysis menu, click Scenario Manager
• From the Scenario Manager dialogue box, click the Summary button to see the following dialogue box:

What you're doing here is selecting cells to go in your report. To change the cells, click on your spreadsheet. Click individual cells by holding down the CTRL key on your keyboard, and clicking a cell with your left mouse button. Select the cells D3, B12 and D13. If you want to get rid of a highlighted cell, just click inside it again with the CTRL key held down. Click OK when you've selected the cells. Excel will then create your Scenario Summary:

All right, it's not terribly easy to read, but it looks pretty enough. Perhaps it will be enough to convince our family to change their ways. Unlikely, but a nice diagram never hurts!

We'll now move on to Goal Seek.

Goal Seek in Excel

Goal Seek is used to get a particular result when you're not too sure of the starting value. For example, if the answer is 56, and the first number is 8, what is the second number? Is it 8 multiplied by 7, or 8 multiplied by 6? You can use Goal Seek to find out. We'll try that example to get you started, and then have a go at a more practical example.

Create the following Excel spreadsheet

In the spreadsheet above, we know that we want to multiply the number in B1 by the number in B2. The number in cell B2 is the one we're not too sure of. The answer is going in cell B3. Our answer is wrong at the moment, because we has a Goal of 56. To use Goal Seek to get the answer, try the following:

• From the Excel menu bar, click on Data
• Locate the Data Tools panel and the What if Analysis item. From the What if Analysis menu, select Goal Seek
• The following dialogue box appears:

The first thing Excel is looking for is "Set cell". This is not very well named. It means "Which cell contains the Formula that you want Excel to use". For us, this is cell B3. We have the following formula in B3:

= B1 * B2

So enter B3 into the "Set cell" box, if it's not already in there.

The "To value" box means "What answer are you looking for"? For us, this is 56. So just type 56 into the "To value" box

The "By Changing Cell" is the part you're not sure of. Excel will be changing this part. For us, it was cell B2. We're weren't sure which number, when multiplied by 8, gave the answer 56. So type B2 into the box.

You Goal Seek dialogue box should look like ours below:

Click OK and Excel will tell you if it has found a solution:

Click OK again, because Excel has found the answer. Your new spreadsheet will look like this one:

As you can see, Excel has changed cell B2 and replace the 6 with a 7 - the correct answer.

We'll now try a more practical example.

Goal Seek Number Two

Consider this problem:

Your business has a modest profit of 25,000. You've set yourself a new profit Goal of 35,000. At the moment, you're selling 1000 items at 25 each. Assume that you'll still sell 1000 items. The question is, to hit your new profit of 35,000, by how much do you have to raise your prices?

Create the spreadsheet below, and we'll find a solution with Goal Seek.

The spreadsheet is split into two: Current Sales, and Future Sales. We'll be changing the Future Sales with Goal Seek. But for now, enter the same values for both sections. The formula to enter for B4 is this:

= B2 * B3

And the formula to enter for E4 is this:

= E2 * E3

The current Price Per Item is 25.00. We want to change this with Goal Seek, because our prices will be going up to hit our new profits of 35,000. So try this:

• From the Excel menu bar, click on Data
• Locate the Data Tools panel and the What if Analysis item. From the What if Analysis menu, select Goal Seek
• The following dialogue box appears:

For "Set cell", enter E4. This is where the formula is. The "To Value" is what we want our new profits to be. So enter 35000. The "By changing cell" is the part we're not sure of. For us, this was the price each item needs to be increased by. This was coming from cell E3 on our spreadsheet. So enter E3 in the "By changing cell" box. Your Goal Seek dialogue box should now look like this:

Click OK to see if Excel can find an answer:

Excel is now telling that it has indeed found a solution. Click OK to see the new version of the spreadsheet:

Our new Price Per Item is 35. Excel has also changed the Profits cell to 35 000.

Exercise

You've had a meeting with your staff, and it has been decide that a price change from 25 to 35 is not a good idea. A better idea is to sell more items. You still want a profit of 35 000. Use Goal Seek to find out how many items you'll have to sell to meet your new profit figure.

Absolute Cell References

An important difference in Excel spreadsheets is between absolute cell references and relative cell references. To see what this is all about, we'll create a simple spreadsheet. This will illustrate relative cell references, which is what we've been using so far.

So open up Excel and enter the same values as in the image below:

In cell B2, you need the following formula:

= A1 + A2

What do you think would happen if we copied an pasted the formula from B2 to cell B3? Let's see:

• Click inside cell B2 to highlight it
• Click on cell B2 with your right mouse button, and select Copy from the menu that appears
• Now click into cell B3
• Again, right click the cell to get the menu. But this time click Paste
• Your spreadsheet should now look like ours:

Cell now says 25! We were trying to work out what 20 + 25 was, and have the wrong answer. So why did Excel put 25 into cell B3 and not 45?

With cell B3 still highlighted, look at the formula bar at the top of Excel. You should see this formula:

= A2 + A3

Click into B2, however, and the formula is this:

= A1 + A2

The problem is due to cell referencing. When you clicked Copy from the menu, Excel didn't only copy the formula. It took at look at where the cells were in the formula, relative to the B2 cell, and copied this as well. From B2, the first cell reference (A1) is up one row, and left 1 column (the red arrow below):

The second cell reference (A2) is one column to the left of cell B2:

When you clicked into cell B3 and selected Paste from the menu, Excel was not only pasting the formula, it was pasting this "up 1, left 1". Take a look at the two images below. We're now starting at cell B3. Have a look at where the two red arrows are pointing now.

The first cell reference:

The second cell reference:

So the first red arrow is pointing to cell A2, and the second red arrow is point to cell A3. This is what was copied. Excel then took the formula to mean this:

= A2 + A3

But it should have been this:

= A1 + A2

If you want the correct answer in cell B3, you have to stop Excel from using this Relative Cell Referencing that it's currently doing. What you need is Absolute Cell Referencing.

Absolute cell referencing involves nothing more than placing a dollar symbol ( $ ) before each letter and number.

Click inside of cell B2 on your spreadsheet, and change the formula to this:

= $A$1 + $A$2

Now copy and paste it over to cell B3 again. You should have the correct answer, this time:

Excel will use Absolute Formula in its own calculation, so it's worth getting used to them. But to recap:

• If you need to copy and paste formulas, use Absolute cell references
• Absolute referencing means typing a dollar symbol before the numbers and letters of each cell reference (You can mix absolute and relative cell references, though).

Named Ranges in Excel

A Named Range is way to describe your formulas. So you don't have to have this in a cell:

= SUM(B2:B4)

You can replace the cell references between the round brackets. You replace them with a descriptive name, all of your own. So you could have this, instead:

= SUM(Monthly_Totals)

Behind the Monthly_Totals, though, Excel is hiding the cell references. We'll see how it works, now.

Open up Excel and create the spreadsheet below:

The formula is in cell B5, and just adds up the monthly totals in the B column.

Define a Name

Setting up a Named Range is a two-step process. You first Define the Name, and then you Apply it. To Define your name, do this (make sure you have the formula in cell B5):

• Highlight the cells B2 to B4 (NOT B5), then click the Formulas menu
• Locate the Named Cells panel in Excel 2007. In Excel 2010 and 2013, locate the Defined Names panel instead.
• Click Name a Range in Excel 2007 and Define Name in Excel 2010 and 2013

From the Name a Range menu, click Name a Range (Define Name again in Excel 2010/13):

You'll then get the following dialogue box:

Click OK on the New Name dialogue box. Notice that the Name is our heading ofMonthly_Totals.

When you click OK, you'll be returned to your spreadsheet. You won't see anything changed. But what you have done is to Define a Name. You can now Apply it.

Apply a Name

To apply your new Name, click into cell B5 where your formula is, and do this:

• On the Named Cells panel, Click Name a Range. For Excel 2010/13 users click Define Name > Define Name
• From the menu, select Apply Names
• From the Apply Names dialogue box, select the Name you want and click OK:

When you click OK, Excel should remove all those cell references between the round brackets, and replace them with the Name you defined:

In the image above, cell B5 now says:

=SUM(Monthly_Totals)

The cell references have been hidden. But Excel still knows about them - it's you that can't see them!

Exercise

Study the spreadsheet below, now that we have added another Named Range to cell C5:

Using the same techniques just outlined, create the same Named Range as in our image above. Again, the formula we've used is just a SUM formula:

= SUM(C2:C4)

You need to start with this, before you Define the Name and Apply it.

Using Named Ranges in Formulas

We'll now use two Named Ranges to deduct the tax from our monthly totals.

So, to define two new Names, do the following:

1. Click inside cell B5 to highlight it
2. From the Formulas menu bar, locate the Named Cells panel, and click Name a Range > Name a Range (Excel 2007). In Excel 2010/13, click Define Name > Define Name from the Defined Names panel.
3. From the New Name dialogue box, click in to the Name textbox at the top and enter Monthly_Result (with the underscore character)
4. Click OK
5. Click inside cell C5 and do the same as step 2 above. This time, however, enter Tax_Result as the Name

You should now have two new Names defined. We'll now Apply these new names. First, add a new label to your spreadsheet:

Click in to cell B7, next to your new label, and enter the following formula:

= B5 - C5

With the formula in place, we can Apply the two new Names we've just defined:

• From the Formulas menu bar, locate the Named Cells panel, and click Name a Range > Apply Names (Excel 2007). In Excel 2010/13, click Define Name > Apply Names from the Defined Names panel.
• The Apply Names dialogue box appears
• Click Monthly_Result to select it
• Click on Tax_Result to select it:

• Click the OK button
• Excel will replace your cell references with the two Names you Defined
• Your spreadsheet should look like ours:

If you look at the formula bar, you'll see the two Named Ranges. The formula is easier to read like this. But it's not terribly easy to set up! They can be quite useful, though.

Custom Names - Constants in Excel

You can set up a custom name to be used as a constant. An example of a constant is PI when working with circles. If you need to use PI in formulas you don't need to type out 3.1415 all the time, you can just do this:

= PI() * 5

We'll use the spreadsheet below to set up our own custom name:

We'd like to work out a discount, depending on the shoe type. So Slingback shoes might have a discount of 5 percent but Stacked Heels might have a discount of 12 percent. To set up a custom name, click on the Formulas ribbon at the top of Excel. On the Defined Names panel, select Define Name > Define Name: (In Excel 2010 and 2013, locate the Defined Names panel instead.)

You should see the New Name dialogue box appear:

In the Name area at the top, type slingback_discount. In the Refers to textbox at the bottom, you can type a cell reference or a formula. Type =5% in the textbox, though. Then click OK when your dialogue box looks like this:

To use your new custom name click into cell D2 to select it. Then click into the formula bar at the top. Type = C2 *. Then start typing your custom name. As soon as you type the "sl" you'll see a popup box appear:

The popup box should have your custom name on the list. Double click it and Excel will add it to your formula. Because we typed =5% in the Refers to textbox of the New Name dialogue box the constant slingback_discount will always be 5%.

Press Enter when the formula is complete and you should see the discount appear in cell D2:

If you want to delete a custom name, click the Name Manager on the Defined Names panel:

You'll see the following dialogue box appear:

If you want to edit your custom name, click the Edit button at the top. To delete a name, simply select the name from the list then click the Delete button.

Pivot Tables in Excel

A Pivot Table is way to present information in a report format. The idea is that you can click drop down lists and change the data that is being displayed. For example, choose just one student from a drop down list and view only his or her scores. Pivot tables are a lot easier to grasp when you see them in action. Here's the one we're going to create in this section:

Look at Row 4. This shows that the student is Elisa. If we click Elisa's drop down arrow, we'll see this:

Now we have another student to select (we'll only use two students, for this tutorial). We could untick Lisa, and tick Mary instead. Then her scores would display.

The Subject and Month cells also have drop down lists. So we could view only January's scores, and just for Art and English, for example.

So this is a Pivot Table - a report that we can manipulate by selecting items from drop down lists. Let's make a start.

The first thing you need for a Pivot Table is some data to go in it. Instead of typing all the data out, you can simply grab ours. Go to this web page on our website and save the spreadsheet to your own hard drive:

Download the Data for the Pivot Table (Right click and select Save Link/Target As)

Once the spreadsheet is on your own computer, open it up. You should see this (If you get a warning across the top, click on Enable Editting):

The Pivot Table Data in an Excel Spreadsheet (New window)

Highlight the data that will be going in to your Pivot Table (cells A1 to D37).On the Excel Ribon, click the Insert tab. From the Insert tab, locate the Tables Panel.

On the Tables panel click Pivot Tables. The Create Pivot Tables dialogue box appears:

In the dialogue box above, the data that we highlighted is in the Table/Rangetextbox. You can select different cells by clicking the icon to the right of the Table/Range textbox. You can also specify an external data source, such as a text file, for the data in your Pivot Table.

We've selected a New Worksheet as the place where the Pivot Table will be placed. Click OK.

When you click OK, Excel presents you with a rather complex layout. The area on the right should look something like one of these below, depending on which version of Excel you have:

     

It helps to have a look again at what we're trying to create. Here's the completed Pivot Table again:

Now take a look at the Pivot Table Field List image again, the one above the completed pivot table. It has tick boxes for Month, Subject, Student, and Score. These are column headings from the original spreadsheet data. We've put the Month in cell A7 on our Pivot Table, Subject is in cell B6, Student is in cell B4, and Score is the Average scores in cells C8 to G10. You'll see how it works, though.

The idea is that you tick a box in the Pivot Table Field List, and then drag it to the four areas below. Excel will take care of the rest.

So, tick all four boxes in the field list:

Excel will create a basic (and messy) Pivot Table for you. But we're going to put our 4 fields into the 4 areas below. Here are the 4 areas we can drag to:

For the Report Filter, we want the name of a Student. For the Column Labels, we want the Subject, and for the Row Labels, we'll just have the Month. The Values will be the Average scores.

If you look at the Field areas after you have ticked all four boxes, however, you may see something like this:

Month, Subject and Student have all been grouped under Row Labels. You can drag and drop these, though.

So click on Student in the Row Labels box. Hold down your left mouse button, and then drag it in to the Report Filter box. If you don't fancy dragging and dropping, simply click the Student item with your left button. From the menu that appears, select Move to Report Filter:

Your Field areas will then look like this:

Move Subject from Row Labels to the Column Labels area:

Your Field areas will then look like this:

The Pivot Table on your spreadsheet will look a lot different, too. It should be looking like this:

Our Pivot Table is coming along, but the scores are all wrong, and it needs tidying up a bit.

The reason why the scores from our Pivot Table are so strange is because Excel is using the wrong formula. It's using a Sum total when we want it to use an Average.

Here's the Pivot Table so far:

The numbers have all been added up. But we want averages, instead. To change the formula, click on Sum of Score under the Values field area:

You'll see the following menu:

Select, Field Settings (or Value Field Settings in Excel 2010 and 2013). You'll then see the following dialogue box:

Change the Formula from Sum to Average, and then click OK. Your Average formula won't be formatted to any decimal places. So highlight you data. On the Home tab in Excel, locate the Number panel. Format your Averages so that it has no decimal places. Your Pivot Table will then look like this:

Almost there!

Look at cells A3, B3 and A4 above. These all have the not very descriptive names of Average of Score, Column Labels, and Row Labels. You can click inside of these cells and type your own headings, in exactly the same way as you would to enter text in a normal cell.

In the new version of the Pivot Table below, we have renamed these cells. We've also centred the data.

Only one thing left to do - spruce up the table by adding a bit of colour.

Click anywhere on your Pivot Table to highlight it. Now look at the Ribbon at the top of Excel . You'll notice a Design menu. Click on this to see the various design options.

The Pivot Table Style Options panel is interesting.

Select Banded Rows and see what happens. Now click Banded Columns.

Next to this panel, there are lots of Pivot Table Styles to choose from. Select one that catches your eye. Here's our finished Pivot Table again, only with a different Style:

And here's the original:

There's a lot more you can do with Pivot Tables, but we hope that this introduction has whetted your appetite! But click the dropdown boxes on your Pivot table and play around with them. Change the values you see on the various lists for Student, Subject, and Month.

How to reference formulas and data on other worksheets

You don't have to have all your data on one worksheet. In fact, it's common practise to create lots of worksheets in the same workbook. In this lesson, you'll see how to reference a formula that is on a different worksheet. This comes in handy if, for example, you have 12 worksheets, one for each month of the year. You can then create another worksheet that holds things like totals for the entire year. We'll do that now.

• Open up Excel (If it's already open, close the project you're currently working on and open a new blank workbook.)
• Locate Sheet1, Sheet2, and Sheet3 at the bottom of Excel (Excel 2013 users will only have one worksheet. To create two more worksheets, click the plus symbol to the right of Sheet1.)
• Rename these to May, June, July (Right click, and select Rename from the menu)

Click on the May sheet, and enter the same data as in the following image:

Click on the June sheet and enter the following:

Then click on the July sheet and enter the following:

We now need to create a new worksheet. So click on the New Worksheet icon at the bottom of Excel, the one to the right of July in the image below (Excel 2013 users should click the plus symbol again):

The new worksheet will be called Sheet4 by default. Rename it to Annual Total, and your workbook will look like this at the bottom (If your new worksheet is not at the end, hold down your left mouse button on the worksheets name. Keep it held down and drag to the end):

We're now going to add up the figures on the May, June and July worksheets, and put the answer on the Annual Total worksheet.

Add a label to your Annual Total worksheet:

Then click inside cell B1.

To reference data on another worksheet, you use the exclamation mark (exclamation point, if you're in the USA). This is commonly called a Bang!

So enter this in cell B1 of your Annual Total worksheet:

=May!B1

So we start with an equals sign (=), and then type the Name of the worksheet we want to reference (May). After the exclamation mark (bang), we have the cell we want to reference (B1). If you just type B1 by itself, Excel would assume that you meant the current worksheet.

When you press the enter key, you should see this on your Annual Total worksheet:

This is the same figure as the one on your May worksheet. To add up all our monthly worksheets, just reference them in the same manner:

=May!B1 + June!B1 + July!B1

So click inside cell B1 of your Annual Total worksheet and replace your formula with the one above. Press the enter key and you should see the answer:

So when you want to include figures or formula from other worksheets, remember to include the name of the worksheet followed by a bang

The LOOKUP Function

The LOOKUP function in Excel is used to search one column of data and find data in the corresponding row. For example, if you are searching a column of employee IDs the LOOKUP function can find, say, employee number 12345 in the ID column. Once it has found the ID 12345 it then can return data from that same row. Let's create a spreadsheet to clear things up.

(The LOOKUP function we'll examine is called a VECTOR LOOKUP. There's also an ARRAY LOOKUP, but we won't cover that here.)

Create the following simple spreadsheet:

So we have item numbers in the A column, then a list of products in the B column. Prices are in the C column. Our spreadsheet only has 10 items, but it could have hundreds, even thousands of entries. What we'd like to do is to take an item number and see what Shoe Type, or what Price corresponds to that item number. An item number of 229, for example, would return either "Stacked heel" or a price of 70. We can use the LOOKUP function for this.

The LOOKUP function needs three between its round brackets. It needs these:

LOOKUP(value_to_lookup, data_to_search, results_column)

The value to lookup would be 229 in our example. This is in the A column. So the data to search is A2 to A10. If we want the results to come from the B column, we need the data from B2 to B10. If we wanted the Price to be returned, the data would be in C2 to C10.

So we can add the following LOOKUP function to return a Shoe Type:

=LOOKUP(229, A2:A10, B2:B10)

Or this one, for the Price:

=LOOKUP(229, A2:A10, C2:C10)

So click into cell F1 in your spreadsheet. Click inside the formula bar and enter:

=LOOKUP(229, A2:A10, B2:B10)

When you've entered the formula, press Enter. You'll immediately see that there is a problem:

Cell F1 has #N/A in it, indicating there the value is not available. The reason for this error is that LOOKUP needs the data you're searching to be sorted, otherwise problems like this will occur.

To sort the data, highlight the cells A2 to C10. With the cells highlighted, click the Editing panel on theHome ribbon. From the Editing panel, click Sort & Filter. Then select Smallest to Largest from the menu:

When your data is sorted, LOOKUP should produce the correct result:

So the Shoe Type for item number 229 is Stacked heel.

There are some things to be aware of with the LOOKUP function. If the item you're searching for is less than the smallest item in your data then an error will occur. As an example, try entering 100 as the first number in your LOOKUP function:

=LOOKUP(100, A2:A10, B2:B10)

The N/A error should appear in cell F1 again.

If LOOKUP can't find your value then it will return the highest value lower than your number. So for us, the highest value lower than 229 is 156.

As well as entering a number like 229 you can change it to a cell reference. So the formula could be this:

=LOOKUP(F1, A2:A10, B2:B10)

In cell F1 you'd enter the value you want to search for.

The value you're searching for doesn't have to be a number. You can enter text as well. For example, suppose we wanted to return the item number for Mules. We'd do it like this:

=LOOKUP("Mules", B2:B10, A2:A10)

The value to search for is now text, surrounded by double quotes. For us, the text is in the B column, B2 to B10. The Item Number is in the A column, A2 to A10. Excel will search for the text "Mules" in the B column and return the Item Number from the A column. (Instead of typing Mules in double quotes you can change it to a cell reference, if you prefer.)

The VLOOKUP Function

In the previous section, you learned about the LOOKUP function in Excel. There's another type of LOOKUP function you can use, however, called the VLOOKUP (there's also an HLOOKUP, but we won't cover that). VLOOKUP is used to search the first column of your spreadsheet for a value. If the value is found you can return data from any cell in that row.

The VLOOKUP function needs three pieces of information, separated by commas. (You can also add an optional fourth value, which we'll see in a moment)

LOOKUP(value_to_lookup, data_to_search, results_column, [match_case])

To illustrate VLOOKUP, create the following spreadsheet (or you can use the same one from the previous section).

Notice that the data in the first column, Item Number, is sorted lowest to highest. If you don't sort your data then LOOKUPS can give you problems.

The item number we want to look up is in cell F1, and is a value of 583. We can use VLOOKUP to return the Shoe Type associated with item number 583.

Click inside cell F2 to select it, then click inside the formula bar at the top. Enter the following formula:

=VLOOKUP(F1, A2:C10, 2)

Press the enter key on your keyboard when you have finished typing the formula. You should find the a value of "Kitten heel" is returned.

So the value we want to search for is in F1. The data we want to search is the cells A2 to C10. The column that needs to be returned is column 2. When Excel finds the 583 it sees that this on row 6. The value in Row 6, column 2 is what VLOOKUP returns its answer.

As was mentioned, you can add a fourth argument after the column number. This argument is either TRUE or FALSE and has to do with matching values from what you are searching for (the F1 for us). If you enter a value of TRUE (the default) then Excel looks for an exact match. If no exact match is found, it settles for the next lowest value. As an example, enter 580 in cell F1. When you press enter you should see "Platforms" appear in cell F2. Because we left off a value of TRUE or FALSE as the fourth argument it defaults to TRUE. When Excel can't find a value of 580 it looks at the next value lower than 580, which is 471 for us. This is on Row 5. Row 5, column 2 is "Platforms".

Now click in cell F2 to highlight it. Click inside the formula bar and amend your function to this:

=VLOOKUP(F1, A2:C10, 2, FALSE)

All we've done is added FALSE on the end, but after a comma. Press the enter key on your keyboard and Excel gives you N/A error. It does this because FALSE means "find an exact match". Because we didn't have a value of 580 an error is returned.

Change FALSE to TRUE and try again. You get Platforms as an answer.

Now change the 580 in cell F1 to 150. When you press enter, cell F2 shows the N/A error again. This time, the error occurs because 150 is smaller than the smallest value we have for Item Number.

Change the value in cell F1 to 612. Hit the enter key and you'll find that "Mules" appears in cell F2.

Calculations with VLOOKUP

You can use VLOOKUP to do some calculations for you. Suppose we wanted to work out the discount price for Mules. We can use VLOOKUP to get the price then multiply by its discount value in the D column.

First, click into cell F3 to select it. Click into the formula bar and enter the following:

=VLOOKUP(F1, A2:C10, 3, FALSE)

When you press the enter key, you should find that a value of £40 appears in cell F3. The discount for the Shoe Type mules is 15%. We can use a second VLOOKUP to calculate how much the shoes would be if the 15% discount is applied.

Click into cell F4. Click into the formula bar and enter the following:

=VLOOKUP(F1, A2:D10, 3, FALSE) * (1 - VLOOKUP(F1, A2:D10, 4, FALSE) )

So we now have the VLOOKUP function twice. The first one gets the value in column 3. This is the Price of the shoes. The second one gets the value from column 4, which is the discount. The result from the first VLOOKUP (£40) is then multiplied by the result of the second VLOOKUP (15%).

When you press enter on your keyboard a value of £34 should be returned - the price minus the discount.

Using VLOOKUP for calculations can be quite tricky, but they are worth getting the hang of as mastering them will bring your Excel skills on a treat!

A Business Invoice with VLOOKUP

In the last section, you saw how to use VLOOKUP. We're now going to create a business invoice that relies heavily on VLOOKUP. This is intermediate excel, so don't worry too much if you don't understand it all - just skip ahead to the next section and come back to it at a later date.

But the Invoice we're going to create looks like this:

The invoice works by typing an invoice number into cell B5 (the 12343 in the image above). When you press the Enter key on your keyboard, data will be pulled from two more worksheets. The two worksheets contain Customer Data and Sales Data. The Customer's Name, Address, etc, gets pulled from a worksheet called Customer_Data, and information about what the customer bought is pulled from a worksheet called Sales_Data. All the information on the Invoice worksheet is automatically updated when you change the invoice number is cell B5.

Let's make a start, then. Create a new spreadsheet for this. If you're using Excel 2013, you'll only get one worksheet by default, so add two more at the bottom of Excel. Name the first worksheet Customer_Data, and the second one Sales_Data. The third worksheet should be changed to Invoice. Your new spreadsheet will then look like this at the bottom:

Click on your Customer_Data worksheet to select it. On the first row, enter the following headings:

CUSTOMER_NUMBER

FIRST_NAME

SURNAME

ADDRESS1

ADDRESS2

ADDRESS3

POSTCODE

PHONE

EMAIL

You can format this first row, if you like. Make the text bold, and change the background colour of the cells. Your Customer_Data worksheet will then look something like this:

Now enter some data under each heading. Enter data for the customers. Make up the details, just as we have below. The Customer number and the Phone number columns should be formatted as Text. You can leave the other Columns on General. Your worksheet will then look like this:

Now click on your Sales_Data worksheet to select it. On the first row, enter the following headings (make sure you format the columns as below, as well):

INVOICE_NUMBER General

CUSTOMER_NUMBER Text

DATE Date

DESCRIPTION General

QUANTITY General

PRICE Currency

TOTAL Currency

Format the first row however you like. It should then look like this:

Notice that the first row also has a Customer_Number column. This will contain the same number from the Customer_Data worksheet.

Now enter some data. You can use the same data as ours below, our just make up your own:

The invoice number can be anything you like. But you need to get the Customer Number from your Customer_Data worksheet. The customer numbers we used are these:

0001, 0002, 0003, 0004

This means that customer number 0001, who is called Jack Attack, has the invoice number 12340; and customer number 0002, Mary Fae, has the invoice number 12341. In other words, the same customer numbers are on both the Customer_Data worksheet and theSales_Data worksheet.

Now that we have customer and sales data set up, we can turn our attentions to the actual invoice.

Business Invoice, Part Two

In the previous lesson, you set up an Excel spreadsheet with three worksheets: Customer Data, Sales Data and Invoice. You have filled out the customer and sales data. It's now time to to set up the invoice.

Click on your Invoice worksheet to select it. Create the following labels:

The cell A11, under Date, should be formatted as a date. Description and Quantitycan be left on General. Price and Total should be formatted as Currency.

This is a very basic invoice, without any formatting. You can format it later, though, if you like. To start pulling data from the other two worksheets, we'll start with the Date, in cell A11.

So click inside cell A11. Now enter the following VLOOKUP formula:

=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 3)

Just to refresh your memory, inside of the round brackets of the VLOOKUP function, we have three pieces of information:

VLOOKUP(value_to_lookup, data_to_search, results_column)

For us, the value we want to lookup is the invoice number in cell B5. The data to search can be found on the Sales_Data worksheet, in cells A2 to G5. (If you added more rows to your sales data then you need to change the G5 to whatever cell is the end of your data.) The column we want, the Date column, is column 3 (Column C).

After you've entered the formula, press the enter key on your keyboard. You should see #N/A appear. The N/A stands for Not Available. The result is not available because you haven't entered an invoice number yet. Do that now. Look at yourSales_Data worksheet and locate one of your invoice numbers. Return to the Invoice worksheet and enter your invoice number. Press the enter key on your keyboard and you should see a date appear in cell A11:

The VLOOKUP for the other headings (Description, Quantity, Price, and Total) are very similar. The only thing you need to do here is to change the column number for the final position. So the VLOOKUP function cell B11 would be this:

=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 4)

And the VLOOKUP function cell C11 would be this:

=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 5)

Cell D11 is this:

=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 6)

And cell E11 is this:

=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 7)

Enter the VLOOKUP function for yourself in these cells. Your spreadsheet should now look like this:

Now change your invoice number in cell B5. When you press the enter key on your keyboard, you should find that your invoice will update all by itself:

We now need to get the customer details for the invoice. This is more complicated. It's more complicated because we need to match a customer number from theSales_Data worksheet to a customer number from the Customer_Data worksheet. 

A Business Invoice, Part Three

In the previous section, you pulled sales details about an invoice. In this section, you'll see how to get the customer details and add them to your invoice.

The problem we face is that the customer data is on a separate worksheet to the sales data. The sales data worksheet is where we have the invoice number. The question then is how to associate an invoice number with the customer's details. The answer is the Customer Number. We have this on both the Customer_Data and theSales_Data worksheets. We need to select a customer's details where the Customer Number matches on both worksheets.

As an example, take the invoice number 12340 on the Sales_Data worksheet. In the next column, we have a Customer Number, which is customer 0001:

This Customer Number is also on the Customer_Data worksheet:

We can use nested VLOOKUPs to pull data from both worksheets where the customer number matches, and then insert just the customer details into the Invoice worksheet.

Nested VLOOKUPs

You can nest one VLOOKUP function inside of another. The technique can be quite tricky to understand, so you may need to re-read this a couple of times!

Let's do the Phone number and email address first, as we'll use CONCATENATE as well as nested VLOOKUPs in the name and address fields, which will add another layer of complexity.

Click inside of cell D7 of your Invoice worksheet. Now click inside of the formula bar at the top of Excel and enter the following: (You can enter yours on one line. You can just copy and paste the formula below)

=VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 8)

Press the enter key on your keyboard and you should see a phone number appear in cell D7. This phone number is coming from the Customer_Data worksheet. But how does it work?

First, have a look at what's needed for a single VLOOKUP again:

VLOOKUP( value_to_lookup, data_to_search, results_column)

The first item between the round brackets is value_to_lookup. Previously, we just entered a cell reference here. This cell reference was B5, which was the invoice number. This time, however, we don't need the invoice number from the Sales_Data worksheet. We need the Customer Number. This is in the cell next to the invoice number. Our inner VLOOKUP is designed to get this Customer Number. Here it is:

VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2)

Again, we used B5 as the value to look up. This is the invoice number. The data to search is in the cells A2 to B5 of the Sales_Data worksheet. The result column is column 2, which is the Customer Number.

When this nested VLOOKUP has returned the Customer Number it is used with the outer VLOOKUP:

=VLOOKUP(RETURNED_CUSTOMER_NUMBER, Customer_Data!A2:Customer_Data!I5, 8)

The RETURNED_CUSTOMER_NUMBER above is the result from the inner VLOOKUP. This is then used to search the Customer_Data in cells A2 to I5 of the Customer_Data worksheet. The results column at the end is column 8, which contains the phone number.

Now click inside of cell D8 of your Invoice worksheet. Enter this nested VLOOKUP:

=VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 9)

The only difference here is the column number at the end, column 9. This is where we have stored the email address on the Customer_Data worksheet.

Press the enter key on your keyboard and you should see an email address appear in cell D8.

Now that we have an email address and phone number, we can get the customer's name and address. We could have separate cells here: a cell for the first name, a cell for the surname, a cell for the first line of the address, a cell for the next line of the address, and a separate cell for the zipcode/postcode. What we have done, however, is to have one cell for the customer's full name, and one cell for the customer's address. The customer's full name is in cell B7:

And the customer's address is in cell B8:

If you have a look at the customer name you'll see it's in the format LAST_NAME, FIRST_NAME, with a comma separating the two:

Doe, Jane

In order to get this we'll need two nested VLOOKUPs, one for the last name and one for the first name. In order to join the two together, and add a comma, we can use the inbuilt function CONCATENATE. The CONCATENATE function looks like this:

CONCATENATE( TEXT_ITEM_1, TEXT_ ITEM_2, TEXT_ ITEM_3 …)

You can have up to 255 Text items. A comma is used to separate each Text item. If you actually want a comma you need to treat it as a Text item. For example, here's our Doe, Jane text using CONCATENATE:

=CONCATENATE("Doe", " ,", "Jane")

So Text_1 is "Doe", Text_2 is "," and Text_3 is "Jane".

With this in mind, here's our nested VLOOKUP with CONCATENATE (you can copy and paste this):

=CONCATENATE(VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!C5, 3), ", ", VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!C5, 2) )

This is a very long and messy formula. So let's break it down.

=CONCATENATE(

VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!C5, 3),

", ",

VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!C5, 2)

)

The first VLOOKUP gets the surname from the Customer_Data worksheet. This goes as the first Text Item of CONCATENATE. To put the comma in we have ",". This is the second Text Item of CONCATENATE. The third Text item is the other VLOOKUP. This gets the first name from the Customer_Data worksheet.

So click inside cell B7 on your Invoice worksheet. Click into the formula bar and enter (or copy and paste) the CONCATENATE code above. When you press the enter key on your keyboard you should have the surname and first name just like ours.

The address uses the same technique, but the CONCATENATE formula is even longer. (It's only longer because we need the address lines and the zipcode/postcode. Here it is to copy and paste into cell B8 on your Invoice worksheet:

=CONCATENATE(VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 4), " , ",

VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 5), " , ",

VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 6), " , ",

VLOOKUP(VLOOKUP(B5, Sales_Data!A2:Sales_Data!B5, 2), Customer_Data!A2:Customer_Data!I5, 7))

It looks insanely complicated, but it's just a longer version of the first CONCATENATE. The only difference is that we have more VLOOKUPs and more commas to insert.

Once you've added the new formula, you should have an address in cell B8 of your worksheet.

And that's it! Try it out. Enter a new invoice number into cell B5, one of your invoice numbers from the Sales_Data worksheet. When you press enter, you should see the invoice automatically update itself.

You can format your invoice however you like. Once formatted, the invoice can be printed out and sent to a customer.

One final word on invoices. If you click File > New you should find that Excel comes with some invoices templates. Most of these templates use the VLOOKUP techniques you have explored in these lessons. Try them out and you should find that you have a better understanding of how the invoice templates work.

In the next section, you'll learn how to create an Excel template.