Quick Tips: Yes/No Drop Down List

Today I'm going to show you how to make a quick and easy multiple option drop down list in Microsoft Excel 2007 (works in other versions too). Drop down lists are useful when you want to limit a user's entries to only values that you specify. I've showed you earlier how to create a drop down list using Named Ranges, which takes a few extra steps but offers more repeatability. Now I will show you a much faster and simpler solution for creating these types of lists in Excel. 

 

 

First, begin by selecting the cell you would like to add the selectable list to. Next, go to the Data tab on top and select Data Validation. A window will popup. Here you will find from the list of pull down options the ability to change the Allow: feature to List. Now, under the Source type in the name of the values you want to appear in the pull down menu, which each term separated by a comma. Click OK when you are done. That's it! 

 

 

If you click on the cell you should see an arrow appear on the right hand side of the cell. When you click the arrow you should see the name of the values that you entered in the Source box. You can fill down this cell to other cells you would like to contain the same pull down lists. 

 

 

 

Also, you can convert your user entered Yes or No value into a numeric value for calculations using a simple IF formula,: If the user selects Yes then the value is 1, if No, then 0: 

 

=IF(A1="Yes",1,0) 

 

For more Excel spreadsheets tips please consider joining our newsletter. 

 

Check out some of our recommended tools to learn Excel and how to write VBA macros.

Shared Apartment Expenses Spreadsheet

Have you ever been in that situation where you are sharing a living space with a number of other people and you are constantly trying to figure out who owes who what because each utility is in a different person's name? I've moved fourteen times in the past six years (because of going back-and-forth from university to co-op every semester) and I've had different roommates at each location. I needed a quick and easy way to keep track of who owed me money or what I owed someone else and to make sure all the bills were paid. I wanted this to stay updated in real time so I could simply look at it whenever and know exactly what was due and who to pay, all without needing to sit down do the math every time. This has lead to the creation of my Shared Apartment Expenses Spreadsheet. The following example is for an apartment with three roommates.

The first thing I do is list a description of what utility or rent is due. In the next column I list the amount due. The next column is the category where the expense falls under. These are actually drop-down lists which originate on another sheet where I listed out all of the possible categories. See data validation if you don't know how to make a drop down list. I only did this so I could look at statistics for each month and category.

I also put the due date for each bill and when it was actually paid for my records. Now the important part: who paid what? I make a column for each person and every time they pay a bill I put the amount under their name next to the item that they paid. 

The next section is who owes who what. Now this could be a simple formula if you have decided to split everything evenly among yourselves. But come on, life is never that simple. The apartment I am living in has a one car garage and a master bedroom. It's not really fair to the person that gets screwed out of the garage and doesn't even get their own bedroom! Therefore, we decided to split the rent like so: 

  Instead of having a simple formula you have to manually enter the amounts for the rent. Every other utility is split evenly. The totals are listed at the bottom of the columns. Now, the next thing we want to prevent is to have to write a million checks. If you owe me money, but I owe you more money, I simply subtract what you owe me then write you a check. 

Next is the all important who paid what. This is more complicated and confusing the more people you have but it works and in the end makes things much easier. I list each person and what they've paid to the other two people. I then add up the total at the bottom.

Alright, so that's the easy and obvious part but I want to know what the balance is right now. Below the who paid what section is where the magic happens. First, in cell R16 I take what Nick owes Brian and subtract what Nick PAID Brian (=K12-R12). In the next cell I take what Nick owes Ron and subtract what Nick PAID Ron (=L12-S12) and so on for the other two people. Now we can see who owes who what after payments. Brian owed Nick $86.71 but after a payment of $22.16 only owes him $64.55.

However, Nick also owes Brian $31.67, which is less than what Brian owes Nick. So, we subtract what Brian owes Nick so that Brian just has to pay Nick $32.88 instead of $64.55. Make sense? The formula looks like this: 


=IF(R16>T16,R16-T16,0)


If what Nick owes Brian is greater than what Brian owes Nick, subtract the two, otherwise put zero. Nick's balance for Brian is zero. Brian's balance is greater than Nick's so subtract the two and get $32.88. Basically, each person either has a zero balance or they owe money to someone. There should be no negative values.

The last line tells you who you owe money to in real time. Every time someone makes a bill payment or pays someone else it automatically updates and adjusts the values. No math involved! No more fighting over who paid what and when.

On the last sheet (Statistics) I decided to get a little crazy and keep track of some statistics: the totals for each month and which utility was the biggest drain on my wallet. I used this formula for the utilities

=IF(Statistics!B$1=Balance!$C4,Balance!$B4,"")


And this formula for the months: 


=SUMIF(Balance!D:D,A15,Balance!B:B)

This is also a good place to check the totals with the master sheet to ensure your work is correct.

I had to make some quick graphs to visualize the data. Who doesn't love a good pie chart? 

And there you have it, another practical, real world application of Microsoft Excel!

 

 

 

 

How do I use a formula to count the remaining days until the end of the month in Excel?

Using the =DAY(TODAY()) commands I can get the date 2/15/2010 to show that we are on day 15 of the month. I would like the number all the way up to the last day of the month and then stay there. In this example November counted up to 30 days, Dec to 31, Jan to 31, and since today is the 15^th of Feb it is currently on 15. Tomorrow it will be 66. After the 31 I would like it to stop at 31. Is there a possible formula for this?

Month	Days
2009-Nov	30
2009-Dec	31
2010-Jan	25
2010-Feb	15

Yes there is a formula. First, you would need to have a fixed component or refer to a year/month value. Try this for January 2010:

=IF(--TEXT(TODAY(),"yyyymm")>201001,DAY(DATE(2010,2,0)),DAY(TODAY()))

That formula would begin by returning 1 on 01-JAN-2010 and increment by 1
each day up through 31-JAN-2010...then it would stay at 31

February 2010 would be this:

=IF(--TEXT(TODAY(),"yyyymm")>201002,DAY(DATE(2010,3,0)),DAY(TODAY()))

Now, one other thing you can do is take the 201002 numbers and place those in a separate column and then just point to that column in your formulas.

What do the hypens before TEXT do? A leading hyphen causes Microsoft Excel to interpret the contents of a cell as a formula. If you want to use a leading hyphen but not create a formula, use an en dash (option-hyphen) or an em dash (shift-option-hyphen) in place of the hyphen. Excel treats them like any letter or number.

You can also force Excel to treat the contents of any cell as text by typing an apostrophe at the beginning of the cell contents. Alternatively, format the cell as text (choose Cells from the Format menu, click the Number tab, and double-click Text in the category list). After changing a cell to text format, you may have to press command-U to make the change take effect.

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.

How do I create a week ending date formula for an expense report?

Let’s say you are putting together an expense report sheet for all of your employees to use. You want them to be able to enter a week ending date and the spreadsheet will automatically compute the rest of the dates for the week. Here are the two main formulas we will be using:

=IF(B1<>"",IF(WEEKDAY(B1)<>7,B1+7-WEEKDAY(B1),B1),"")

=IF($B$2<>"",$B$2-6,"")

First it says, if the date in cell B1 does not equal (< >) blank (“”), then go on to the next part of the formula, otherwise leave blank. The formula =weekday() returns a number 1 to 7 identifying the day of the week of date. In our example, I have chosen 2/12/2010 as the week ending date. =weekday(B1) turns out to be 6. So, if the weekday of B1, which is 6, does not equal 7, then take the value of B1, 12, add 7 to get 19, then subtract weekday of B1, 6. We end up at 13. (recap: 12+7=19. 19-6=13.) The date displayed is 2/13/2010.

Now it is on to the next formulas for each day. Sunday’s formula is: =IF($B$2<>"",$B$2-6,""). If B2 does not equal blank then subtract 6, otherwise leave blank. In our example we have 13-6=7 which gives us the correct date for Sunday, February 7, 2010. The formula for Monday is: =IF($B$2<>"",$B$2-5,"") which gives us 13-7=8 for Feb. 8th and so on until Saturday. There you have it, one easy way to improve your expense reports.

I could just put this up as templates and not even bother explaining how they work but how many people would take the time to try and figure out how they work? You will learn a lot and feel much more accomplished if you try building these spreadsheets on your own. Feel free to let me know if you have any questions or comments.

How do I create nested IFs formula or an IF in conjection with OR function?

IF/OR

To create a Microsoft Excel formula with an IF and an OR statement read the following example:

=IF(OR(E15=“ALUMINUM 2117”,E15=“STEEL ALLOY”), ”CADIUM PLATE”, ””)

If the text of cell E15 is exactly “ALUMINUM 2117” OR “STEEL ALLOY”, then the selected cell will display “CADIUM PLATE”, otherwise no value will be displayed because of the null set, or empty quotations at the end.

Nested IFs

You may need a formula that involves several IF statements in one. We call these IFs inside IFs Nested IFs (that's a lot of IF's!) See these examples:


=IF(C2=2,0.062, IF(C2=3, 0.094, IF(C2=4, 0.125, "")))

Explanation: If the value in cell C2 is equal to 2, then the dimension 0.062 will be displayed, if the cell C2 is equal to 3, 0.094 will be displayed, if cell C2 is 4, then 0.125 will be displayed, otherwise nothing will be displayed.
Make sure the number of left parentheses equals the number of right parentheses (color coded in Excel). Also note that Microsoft Excel 97-03 only allows users to have 7 levels of nested Ifs.

Other examples:

=IF(U17="CRES 303", "C", IF(U17="CRES A286", "CA", IF(U17="ALLOY STEEL", "-", "")))

=IF(C2=2,0.062, IF(C2=3, 0.094, IF(C2=4, 0.125, IF(C2=5, 0.156, IF(C2=6, 0.187, IF(C2=8, 0.25, ""))))))

Stay tuned for more!

Please feel free to ask any questions.

How do I use the IF formula in Excel?

IF Definition:  IF(CONDITION, action or value if CONDITION is true, action or value if CONDITION is false)  “If” Formula can work for anything that is based off of the data in another column.

=IF(E15=“ALUMINUM 2219”, “TRUE”, “FALSE”)

If the text of cell E15 is exactly “ALUMINUM 2219-T81” then the selected cell will display “TRUE”, otherwise “FALSE” will be the value.



More on IFs including nested ifs and use in conjunction with OR formula coming soon!

EXAMPLE: Rating Comparison Spreadsheet Template

In my very first post I mentioned that I use Excel all of time and in a variety of ways. One good example is when I began working at a new job after I graduated college and I needed to quickly find a new place of residence. I didn’t want to settle for just any old place. I wanted to know which location was best for me. What did I do? I turned to Excel to illustrate the pros and cons of different apartments I had chosen as candidates in order to help me decide which was the best.



Along the top you can see the names of all the apartment complexes. Down the left hand side I listed all of the features I was interested in knowing and specifically asked about when I visited the apartments. In-between resides the gathered data. The majority of the wanted features required a simple yes or no answer. The apartment rating line is the average rating given to the apartment from an online apartment rating website. I computed the yearly cost just to get an idea of how much it would be and if I could afford it. 

The final line is the most important, the scores for each apartment. How did I come up with those? Well if you look at the left hand side you might have noticed a bunch of hidden rows. That is where the magic happens. Each feature is given or subtracted points based on a factor of importance I created. The last column of the spreadsheet is the base line, my perfect living arrangement, and you can see the highest possible score is 12.5.



This is what the rows look like unhidden. I gave each listed feature a rating as some of them were more important than others. I have my own microwave already so that was not as important as having a washer and dryer in the room. Price of rent was essential but having a covered parking lot was not.



Here are the formulas I used for each feature:

Monthly Cost: =IF(B5<680,1.5,0)

Sq Feet = IF(B7>790,0.5,0)

Lease =IF(B8=6,1,0)

Pet Friendly =IF(B9="YES",1,0)

Dish-washer =IF(B10="YES",1,0)

Microwave =IF(B11="YES",0.5,0)

Washing Machine =IF(B12="YES",1.5,0)

Outdoor Pool =IF(B13="YES",1,0)

Indoor Pool =IF(B14="YES",0.5,0)

Covered Parking =IF(B15="YES",0.5,0)

Gated =IF(B16="YES",1,0)

Water =IF(B17="YES",0.5,0)

Private Entry =IF(B18="YES",1,0)

Deposit =IF(B19>200,-0.5,0)

Ap Fee =IF(B20>100,-0.5,0)

Distance to Work =IF(B21<12,0.5,0)

Online Rating =IF(B22>=0.5,0.5,0)

By using this Excel spreadsheet I no longer have to guess which apartment suits my needs, I now KNOW the best fit.

How do I count by increments in Microsoft Excel 2007?

You may be wondering how to make a list in excel count from one specified number to another in user defined increments. Well, I recently had to use this application myself and this is the format I created. A user enters the number to start counting from in the first yellow square, the number to end at in the next square, and finally, in what increments they would like to count up at. Here is the driving formula:

=IF(I2="","",IF(I2+1*$F$1<=$D$1,I2+1*$F$1,"")) and =B1 in cell I2.

Pictured below are two examples.