Data Tables 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

Read More

A Second Data Table Microsoft Excel

A Second Data Table Microsoft Excel

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. In the next section, we'll take a look at scenarios.

Read More

Scenarios in Excel

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.

Read More

Goal Seek in Excel

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.

In the next part, well take a closer look at cell references in Excel.

Read More

Absolute Cell References Excel

Absolute Cell References Excel

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

In the next part, we'll take a look at Named Ranges in Excel.

Read More

Named Ranges in Excel

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.

In the next part, we'll take a look at how to set up your own custom names that you can use in formulas.

Read More