2 Input Data Table

Two Input Data Tables

It's Monday already and what a gorgeous fall day it is too - only problem is that it is still August! Autumn is my favorite season but I think it is a bit too early this year. I had a great weekend - spent quite a bit of it shopping which is always fun although I got to spend a fair amount of time outdoors. I even made it to the movies with my daughter to see Bandslam.

Anyway, last week I talked about 1 input data tables and told you that I would talk about 2 input data tables this week. The example I am using is a loan example since this is the most common use but tomorrow I'll give you some ideas of what else you can do with this.
Data Table- Two Inputs
In the example below, we are testing the effects of different interest rates and the investment amount so we are using both a row and a column input.

The row input is years and the column input is the interest rate. An input cell is one in which each input value from a data table is substituted.

Click in cell D6 and type: =B14.
Select the cell range D6:H12
Click the Data tab on the Ribbon.
Click the What-if Analysis button on the Data Ribbon and click Data Table from the menu.
Click in the Row Input Box and type: B10
Click in the Column Input Box and type: B8 as shown
Click OK

and Voila- you get the following:

Give it a shot and see what you think.

Data Tables - What-If Analysis

Okay- I couldn't sleep so I figured I would get a jump on the day's work and get my blog finished up early. As I mentioned before I am working on an Advanced Excel Analysis Tools Ebook for a course I am teaching at the Indiana CPA Society in September. One of my associates was fascinated with some of the Excel What-IF features I am covering so I thought I would share part of that with you. Today or tonight I guess- I am going to talk about one variable data tables.

DATA TABLES
A data table allows you to study the effects a range of values has on a formula. This is a timesaver as you only need to set the formula up once. A one-variable data table is a range of cells that shows the results of substituting different values in one or more formulas. Data tables are actually array formulas which allows them to perform multiple calculations at once. In other words, you can see a number of different scenarios at a single glance.
There are one-input and two-input tables. We are just going to cover one-input tables today.

ONE INPUT DATA TABLES
Input values can be either listed down a column or across a row. Formulas used in the data table must refer to the input cell which is the cell in which each input value in the data table will be substituted. Any cell can be the input cell.
Type the list of values you want to substitute in the input cell either down one column or across one row.


To Create A One-Input Data Tables (General Steps for Excel 2007)
1. Type the list of values you want to substitute in the input cell either down one column or across one row.
2. Enter the formula you want to use. If the data table is in column format, enter the formula in the first blank cell above and to the right of the top of the table. If the data table is in row format, type the formula in a blank cell to the left of the first value and one cell below the row that contains the values.
3. Select the data table, including the formula.
4. Click the Data Ribbon.
5. Click the What-if Analysis button on the Data Tools group and select Data Table from the menu.
6. Enter the input cell (the value that you want to substitute with the values from your data table). If the data table is in a column, enter the cell reference in the Column Input text box. If the data table is in a row, enter the cell reference in the Row Input text box.
7. Click OK.

Clear as mud? Okay - let's go through a specific example.

We are going to create a one variable data table. The variable that is going to change is the interest rate. This is also considered the input cell. The Data Table Calculation will look at the PMT formulas, the input cell (interest rate) and the cell we want to change (payment amount).

Basically, we have a column of interest rates and we want to determine what the monthly payment will be at each different rate.


(Click on the picture for a larger view. Sorry, I need to figure out how to add files out here). Email me if you want the file.

• Observe the cell range D6.D12 which contains the interst rates. (highlighted in green).We have created a data table that we are going to use for substitute down payment values. We want to see how different rates will affect the monthly payment value.
• In cell E6 type: =B14 and press Enter. [B14 is the PMT calculation]
• The formula in cell E6 is the one whose values we want to display which in this case is the Payment Amt.
• Select the cell range D6:E12 and click the Data Ribbon.
• Click the What-if Analysis button and select Data Table.
• Click in the Column Input Box and type: B8
• Click OK
  Below is data table showing our results based upon the down payment values in our data table and our interest rates.
  
  
  
  

Look at all the different payments or scenarios if you will that you can see at once. Tomorrow or is it Thursday, I will show you an example of a 2 input variable. In that example, we will have interest rates and years as the 2 variables that change. Hey- this should be very handy if you are thinking of buying a new car or a new house however if you think about it, you can apply this to a lot of business situations. I'll give you an example tomorrow but see what you can come up with on your own.

Patricia

Keyboard Shortcuts

Sorry- I skipped yesterday - not on purpose... just lost track of time. And guess what? - I don't have a lot of time today either. Unfortunately, nothing exciting - just trying to complete the training manuals I am using at the the Indiana CPA Society next month. One course is on Advanced Tools for Analysis and the other is Timesavers for Experienced Users.

Anyway, I wanted to talk about the basics - keyboard shortcuts. If you are like me, you learned some of them in an intro Excel course ..oh so long ago and then promptly forgot them. Well, let me tell you the control key and the shift key are two that you don't want to forget.

CONTROL KEY
Selecting non-contiguous rows can be useful for formatting. For instance, if you find yourself formatting the top row and then the bottom row of your income statement as currency then this technique will be useful for you.

Selecting non-contiguous rows of data is easy. Simply select the first row of data as you normally would and then hold down the CTRL key on the keyboard and then select the next row of data. You can repeat this multiple times.

SHIFT KEY
If you’ve selected too much or too little, simply hold down the Shift Key and then use the arrows on the keyboard to add or reduce cells. The Shift Key locks you into place. This also works in Microsoft Word.

If you know the size of the data you want to select , you can click on the 1st cell that you want to select, hold down the Shift Key, and then click the last cell in the range and Excel will select everything in between automatically. In this example, if I click on Cell A1, hold down the Shift key and select C3, all the cells in between are selected.

By the way, these shortcuts also work in Microsoft Word.
Talk to you tomorrow.

Alternate Shading with Conditional Formatting

Hey- I hope everyone enjoyed the weekend.

I sure did - started the weekend out at the Naked Chopstix which has the best sushi ever. If you ever make it there, make sure to try the Maui roll! Ended the weekend at the Indiana State Fair eating kettlecorn and walking and walking. Along with the typical fair stuff, we saw a dog agility show, a car show and finished up with a demolition derby that my daughter loved. My favorite part was the car show where I saw my dream car - a 1958 Corvette - a beauty.

Anyway, let's talk about conditional formatting and applying rules. As I mentioned in my last post there is a lot you can do with conditional formatting and a lot of it is pretty easy to figure out but I thought it would be worthwhile to chat about using formulas and rules. Below are the steps you can use to apply a rule to data. In my example here I am using the MOD function and telling Excel to shade every second row of my P&L so that it is easier to read. I am specifying 2 in the MOD function so that every second row is shaded and I am specifying green as that is the traditional accounting color but obviously you can select any color you want - just make sure that it is light enough that that the data can be seen through it.


1. Select your data


2. Click Conditional Formatting on the Home Ribbon.
3. Select New Rule.
4. Click on Use a Formula to determine which cells to format.
5. In the rule description, type =MOD(row(),2)=0.
6. Click Format
7. Select a light green fill.
8. Click OK.
9. Click OK.


And voila.. it should look something like my example below:

Conditional Formatting - Overview

Oh my goodness.. Friday hurray!
I need to buy a birthday card for my Dad plus it is Friday and a very nice day so I need to get out and enjoy it however I have promised myself that I would try to write something every day. Let me make this short but sweet and talk about Conditional Formatting.

Excel 2007 has done a lot with conditional formatting. The feature was really beefed up in this newest version and with more people putting information online and having color printers, color formatting is taking off. It is a great way to visualize your data and can often help you pinpoint items of interest.

The great thing about the conditional formatting is that you can apply it to a cell or range of cells and nothing happens until the value or values specified change and meet the applied criteria.

For example, you can select a budget variance column and tell Excel to change any budget value to red if the variance is less than zero. The formatting only changes to red if a number in that column becomes zero or less. If later the variance returns to a positive, the red formatting disappears.

• Here are just some of the things you can do with it.
• You can format only cells that contain text, numbers or date values.
• You can format only top or bottom ranked values.
• You can format duplicate values.
• You can use formulas to determine which cells to format!

Take a look at it at conditional formatting. Go to the Home Ribbon, go to the Styles tab and click on Conditional Formatting and see all the choices.

Next week when I have more time, I will overview a couple of things that you can do with Conditional formatting. The coolest thing I use it for is to have Excel shade alternate rows. I'll show you how to do that next week so check back. If you use conditional formatting for something interesting please let me know - I would love to hear about it.
Have a great weekend.

Excel Tip- Range Names

Range Names as Absolutes

I decided to talk about Range Names today since my blog of yesterday was on absolute cell references. Whenever you name a cell, Excel treats it as an absolute cell reference. So, let's talk about this a bit and see why it can be extremely useful to give a cell or a range of cells a name.

• First, you can use range names in formulas. =Sum(expenses) is a lot more explanatory as well as easier to remember than =sum(F15.F36). This can be very useful in those budget spreadsheets that you use a couple of times a year and have trouble remembering what things are.



• You can use range names to navigate around a workbook as a range name can only be used once in a book. Select the range name and Excel will automatically hop you over to the cell or range of cells that you named - even if they are on a different sheet.



• You can use a range name in a print area so if you are printing different sections of the same file every month it would be easier to remember a range name than cell references.

Okay- you get the idea. I personally like to use range names in Vlookups and other functions where I need to refer to a table - particularly if I am going to copy that function down. Why? Because range names are absolute cell references and if I incorporate the range name into the Vlookup, I don't have to worry about making sure that the cell references are absolutes cell references - it does it automatically!! Honest....

So, now that I have convinced you of your need to know about range names, let me tell you how to create one. There are a couple of ways but this works in all Excel versions and frankly it is the easiest way to do it so I won't bore you with the other ways.

1. Select the cell or range of cells that you want to name.


2. Click the Name box, located at the left end of the formula bar, just above Column A .


3. Type in the name that you want to use.


4. Press the ENTER key.

I told you it was easy!

Okay.. now for the rules...
Names can be up to 255 characters but most people keep them very short so that it is easy to remember and easy to type.
Range names cannot begin with a number ( 2008 Sales is won't work but Y2008_Sales would )
Range names cannot contain any spaces. (You can use underscores though)

• Careful- in Excel 2007, some range names such as Tax08 won’t be valid range names as it is considered a cell address due to the size of the 2007 Excel spreadsheet!

Okay- last thing... if you want to select the Range Name simply click the drop down arrow beside the Name box.
If you want to see the range names or use them in a formula, click the F3 key to provide a list of them. (In Excel 2007, you can also use the Name Manager found on the Formula Ribbon).

Excel Tip - Absolute Cell References

Absolute Cell Reference

I wanted to start with some real basics as a lot of Excel websites tend to skip them. Probably one of the most important things everyone needs to understand is the difference between an absolute cell reference and a relative cell reference. The default in Excel is a relative cell reference.




Think about it, if you copied the formula of A2*B2 as shown in the example, you don't expect to get the answer of 3 everytime do you? Of course not, instead you just assume that Excel is smart enough to know that when you copied the formula to row 3, Excel should adjust the formula to its new relative position and the new formula becomes A3*B3 with the resulting answer of 6 without you having to do anything. That is relative cell referencing.







Sometimes though, you don't want Excel to automatically adjust the formula and that is where the trouble comes in as you need to tell Excel that by specifying the cell is an absolute cell reference. Absolute cell references become very important if you are referencing an input cell, doing percentages or using functions such as Vlookup.



Let's change the example above slightly and use an input cell. Notice that the price for all of these is the same. It would be much more efficient to create a price input cell and reference that. I have done that in the next example. My input cell is E1. Then in Cell B2 I created a formula of =A2*E1. Okay, now if I just go and copy that down to row 3, I am going to end up with =A3*E2. Now, I do want Excel to "pick up" A3 but cell E2 has nothing in it and I will get an error. By using dollar signs around E1 in the formula, it tells Excel to freeze and always refer to cell E1. This makes E1 an absolute cell reference. So, if you go and create this and take a look at cell B4, you should see =A4*$E$1 with the resulting answer of 9.Because the dollar sign is in front of the column heading and the row heading, it knows that it must refer only to Column E and it must refer only to row 1. This is absolute cell referencing.






You can just type the dollar signs in or after typing in the cell reference E1, press the F4 key on your keyboard.

Most of the time, people don't think about absolute cell references so here is a handy tip - always check your formulas when you copy and don't just check the first one- check the 3rd or 4th one to make sure that your formula is actually doing what you wanted. If it is not working, chances are you need to make a cell or cells absolute.

I hope this made sense to everyone. If you have any questions, email me at patricia@cpaselfstudy.com

First Day!

Day 1... I've been planning on starting a blog for awhile however I have been so busy with cpaselfstudy.com that I just have not had the time. So, why a blog given all the other blogs out there??
Too few actually provide good clear examples so that people can actually understand what the Excel feature or function does much less how to actually use it. My goal is to provide practical Excel examples that you can understand and actually apply to your business. Of course, you'll have to listen to my random thoughts too.

I hope you enjoy this blog - bear with me as I get it up and running. If you have any useful software tips- even if they are not on Excel- feel free to post.

I do have a few videos on some different Excel features on Youtube - a link is below.
By the way, did you know that Yahoo! was originally called "Jerry's Guide to the World Wide Web" ? - Me neither.