Download the Apartment Search Spreadsheet

I have uploaded the latest version of my apartment search spreadsheet and made it available to download. Are you looking for a new place to live? This apartment search spreadsheet is the perfect guide and tool to use for your search. Trust me, it will make your life much easier and you won't have to put much thought into which apartment is the best one for you. The spreadsheet does all of the work for you. Download the apartment hunting guide today.

This Excel document could also work for scenarios where you need a rating formula system to decide which option is best. You may also want to download this template and use it as an example to create your own unique rating comparison spreadsheet. Or I can do it for you. Simply comment below what your needs are and I will gladly tailor the spreadsheet for your specific situation.

That's not all! Are you moving into a new apartment with friends? It's a pain in the butt to keep track of who owes who what and which bills have been paid. I was in that same situation many times which is why, out of necessity, I created the Shared Living Expenses Spreadsheet! Download it here. Know exactly which roommate owes the other roommates at any moment simply by using my apartment roommates expenses spreadsheet. Again, I appreciate any suggestions.

Update: 6/11/12 Check out the latest apartment comparison spreadsheet.

How do you create pivot tables in Excel?

Today's post is all about pivot tables. I am going to answer the following questions: How do you use and create a pivot table in Microsoft Excel? What are the advantages of using a pivot table? What is a pivot table? How do you make a pivot chart? Let's begin with the definition of a pivot table.

What is a pivot table?

Pivot tables provide an interactive summary of data. You can quickly rearrange and sort the information in the pivot table. There's no need for any complicated formulas. A pivot table classifies numeric data in a list based on other fields in the list. Before creating your pivot table, you should organize your data in a nice and neat table. Do this by creating headers in the first row. Try not to have any blank cells, rows, or columns in your table.

What are the advantages of using a pivot table?

Pivot tables can easily be rearranged by moving, adding, or deleting unneeded fields. Pivot tables are also dynamic, which means that the results of the table are automatically recalculated and updated whenever something changes, like when fields are added or removed, or if categories are hidden or displayed. Pivot tables are also simple to update. It's also very easy to generate charts from your pivot table.

How do you create a pivot table in Excel?

First, under the "Data" menu, select "Pivot Table and Pivot Chart Report."  This will bring up the Pivot Table Wizard, which consists of the following three steps.

Step 1: If your data resides in a worksheet, simply click "Next."

Step 2: Excel will automatically select the data we are currently working with.  Click "Next" again.

Step 3: I usually open the pivot table in a new worksheet (again, this is the default).  Note that you can save a few seconds simply by clicking "Finish" during step 1 if you don't plan to do anything unusual (which, in my experience, is almost every time).

How do you create a pivot chart?

To create a chart from your pivot table right click on a cell in the Pivot Table and select "Pivot Chart."  A chart is automatically generated.  Note that you can view the data by using the drop down menu at the top of the chart, and you can modify the chart by either of the other variables by using the filter menus on the bottom and side.
If you don't like the type of chart Excel chooses, you can modify it by right clicking anywhere on the chart and selecting "Chart Type."
Note that if you modify the pivot table in any way, the chart is automatically updated.

Now you know all about pivot tables and pivot charts! More examples and pivot table templates are coming soon. Stay tuned!

Excel Tips: Skill Assessment Exam Answers

I recently took a Microsoft Excel skills assessment exam. There were a few questions which surprised me. Luckily, I was able to figure them out rather easily. I have decided to share a few of the answers from the skills assessment exam (though not all of them) with you. If you're never going to take an Excel exam then please few these as a few friendly spreadsheet tips.

Q: How do you go to the dialog box in Excel (what is the shortcut to go to the dialog box)? Answer: F5

Q: If you enter a time into Excel, does it default to AM or PM? Answer: AM

Q: How do you display the current time in Excel? Answer: crtl+shift+:

Q: How do you recalculate all formulas in a worksheet (what is the shortcut to recalculate all formulas)? Answer: crtl+alt+F9

Please follow my blog and stay tuned for more!

Advanced Custom VLOOKUP Formula Help

You may encounter a problem with the VLOOKUP function the VLOOKUP formula will only return the first solution it finds. How can the VLOOKUP function return all correct entries? The easiest method to accomplish this will require VBA. We must create a custom function in Excel. Begin by pressing ALT+F11 to open VBA window. Next, click Insert- Module. Try the following code:


Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Long, _Optional Nth As Long = 1)
'Similar to Vlookup, but returns the Nth value found from the top of myrange.
'Not necessarily the First.
'The TRUE/FALSE argument usually found in Vlookup is assumed FALSE in this function. 'Data does NOT need to be sorted, and it searches for EXACT match.
'if ColRef is omitted, uses the number of columns in myrange
'if Nth is omitted, returns the first value found

Dim Count, i As Long
Dim MySheet As Worksheet

Count = 0
Set MySheet = Sheets(MyRange.Parent.Name)
If ColRef = 0 Then ColRef = MyRange.Columns.Count
For i = MyRange.Row To MyRange.Row + MyRange.Rows.Count - 1
If MySheet.Cells(i, MyRange.Column).Value = MyVal Then
Count = Count + 1
If Count = Nth Then
VlookupNth = MySheet.Cells(i, MyRange.Column + ColRef - 1).Value
Exit Function
End If
End If
Next i
VlookupNth = "Not Found"
End Function

Now, once the VBA is complete, close the window and save your file. Now you can enter this custom formula in the worksheet to pick any row number that you want:

=VLOOKUPNTH(LOOKUP_VALUE, LOOKUP_ARRAY, COLUMN_NUMBER, Nth_Number_Row)

So, if you want to choose the value in the 4th column of the second row where the value matches exactly, then the Excel formula would be something like this:

=VLOOKUPNTH(Lookup_Value,LOOKUP_ARRAY,4,2)

What's the difference between this advanced VLOOKUP formula and the standard VLOOKUP? If you have a normal VLOOKUP formula like this =VLOOKUP(A1,B:C,2,FALSE) whereas the VLOOKUPNTH formula would look like this =VLOOKUPNTH(A1,B:C,2,2). The last digit corresponds to the nth part which will return the nth occurrence of the lookup value.

Now you're probably wondering what happens if there are more than two occurrences? What if there are 200 occurrences of the data? Well, then you would need 200 formulas with one returning the first value, one returning the second and so on. You don't want to have to manually edit all 200 formulas so insert the ROW function in order to increment the nth as you fill the formula down. =VLOOKUPNTH($A$1,B:C,2,ROW(A1))


Check out our follow-up post: advanced custom HLOOKUP formula.