Where can I find a copy of a shared living space expenses spreadsheet template? Right here of course! I've created a new template for my Shared Apartment Expenses spreadsheet. Please email or comment if you would like a copy. This is the first version of this document. I could use some good feedback while I am improving and working on a new version. Is it easy to use? Are there any features you would like to see added? Do you care about the statistics or not? This is a good companion spreadsheet to my Apartment Search template.
Excel help, tips, and templates. Learn how to write VBA macros or browse our project management resources.
Tuesday, May 25, 2010
Monday, May 24, 2010
Updated Apartment Search Spreadsheet
Based on recent users suggests and feedback I have made a few additions to my Apartment Search Spreadsheet. Many of these suggestions I had never heard of before, mainly due to the fact the reader lives in a different geographic region than I do. The following is the complete list of additions:
-Added "Gym / Fitness Center" to the Yes or No options. Duh. Can't believe I forgot that obvious one.
-In the input info, added rows for "Square Feet (Bedroom 1)", "Square Feet (Bedroom 2)", "Square Feet (Bedroom 3)", "Square Feet (Living Room / Dining Room)". The reason for this is that if you are sharing an apartment with roommates they may have a different requirement for bedroom space.
-In move-in fees, I added "Credit Check Fee". In the states, it is very common for landlords to charge a nominal fee to check a prospective tenant's creditworthiness (cool word bro, creditworthiness).
-In some major metropolitan areas, such as New York City, it is very common to install a pressurized wall in a bedroom or living room so you can squeeze one more person into the apartment and decrease living costs. Usually these walls are leased, as they are temporary and must be removed before the lease is up. Sometimes they aren't even allowed. Thus, I added a "Pressurized Wall Lease / Month" in Monthly Expenses, "Pressurized Wall Installation Fee" in move-in fees, and "Pressurized Wall Allowed" in the Yes or No Options.
-I also added a "Rent Controlled" in Yes or No Options. Some apartments are rent-controlled to protect tenants from massive rent increases when their lease is up and they wish to renew. Of course, these apartments are extremely coveted. There are other places which have rent-controlled apartments, but apparently New York City is the most well known for it.
Hopefully now the apartment search spreadsheet can accommodate an even larger percentage of the population and address each individual's needs. Do you have any other suggestions? As usual, email me or comment below for your copy of this latest version of the Apartment Rating/Search Spreadsheet.xls.
Wednesday, May 19, 2010
New Apartment Search Rating Spreadsheet
I recently completed a new and improved version of my Apartment Search Spreadsheet. Please leave a comment or email me if you would like a copy. The instructions are included within the spreadsheet. I would appreciate any feedback you have: is it easy to use, is there any information missing, anything suggestions you have to make it better.
This rating spreadsheet utilizes a simple house of quality to give each potential apartment an overall score based on several factors in which you give an importance rating. This way, items that are important to you contribute more to the overall score if they meet your requirements. I've tried to make the process as simple as possible through the use of drop down lists.
Also, I am putting together a job search spreadsheet. I need your help. What are some items you would like to see on this spreadsheet template? I have listed a few already such as: distance to home, salary expectations, relocation paid for, etc. What other information are you most interested in when searching for a new job and to help keep it organized?
Monday, May 10, 2010
How do I clear rows in Excel spreadsheet with a macro?
Do you have a huge spreadsheet with a ton of rows that you need to delete each time you use it? Here is a simple macro to help you. This is for a spreadsheet that is password protected. Now, I have headers in row 1 and formulas starting in row 2 that I don't want to wipe away so I select the range starting in row 3.
Sub Clear_Rows()
''' Protect User Interface Only '''
ActiveWorkbook.Sheets("sheet1").Activate
Sheets("sheet1").Protect Password:="pass1", UserInterfaceOnly:=True
''' Clear Rows '''
Range("a3:xfd1048576").Select
Selection.ClearContents
Range("a1").Select
End Sub
Thursday, April 29, 2010
How do I save an Excel file with a macro?
There may be times when you will want a macro to save a file automatically after it is run. The second macro will save the file with a name called "MySavedFile". You may specify the path if you need to. The last macro saves all opened workbooks.
Sub SaveFile()
ActiveWorkbook.Save
End Sub
Sub SaveName()
ActiveWorkbook.SaveAs Filename:="C:\MySavedFile.xls"
End Sub
Sub SaveAll()
myFile = ActiveWorkbook.Name
ActiveWorkbook.Save
ActiveWindow.ActivateNext
Do While mySavedFile <> ActiveWorkbook.Name
ActiveWorkbook.Save
ActiveWindow.ActivateNext
Loop
End Sub
Sub SaveFile()
ActiveWorkbook.Save
End Sub
Sub SaveName()
ActiveWorkbook.SaveAs Filename:="C:\MySavedFile.xls"
End Sub
Sub SaveAll()
myFile = ActiveWorkbook.Name
ActiveWorkbook.Save
ActiveWindow.ActivateNext
Do While mySavedFile <> ActiveWorkbook.Name
ActiveWorkbook.Save
ActiveWindow.ActivateNext
Loop
End Sub
Tuesday, April 20, 2010
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!
Saturday, April 17, 2010
Excel Spreadsheet Macro: Highlight Duplicates
This is a simple yet effective macro for your Excel spreadsheet. There are times you need to highlight duplicate data in your worksheet. You could use the duplicates function but that actually ends up deleting everything that is a duplicate. Sometimes you may just want to point out what is a duplicate and not physically delete that data. This macro is what you could use instead.
Sub DupinRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub
Subscribe to:
Posts (Atom)