Calculate Car Payments

Okay, let’s say it is time to trash the Nerdmobile and buy a new car. Well, no self-respecting geek is going into a car dealer without his or her trusty laptop or netbook loaded with the most recent version of Excel.

Why is that, you ask? To calculate the monthly car payments, of course! Here is how:

The Excel PMT() function can calculate the monthly repayment using the following format:

PMT(AnnualInterestRate/12, NumberOfPayments, AmountOfLoan)

For example, if your annual interest rate is 6%, on a 5-year (60 months) loan of $30,000, your formula would look like this:

=-PMT(0.05%,60,30000)

Note that a negative sign (-) was placed in front of the function in order to return a positive value (negative payment values look messy…)

With your formula in place, hit Enter and, Presto, you have your monthly payment. If you don’t like the results, play around with the interest rate, the length of the loan, or maybe check out some cheaper cars. How cool!

Using and Creating Templates

Excel templates are files containing common data and formatting options used as models for other spreadsheets. A template is a great shortcut for standardizing the look and functionality of your data. Templates can also provide you with a standard framework in which to input and share your data. Reusable formulas and fields enable you to speed up your input and get the information that you want in the form you want.

Using a Template

1. Go to the File menu and choose New
2. Click on Templates and select the general category
3. Double-click on the desired template
4. A new worksheet opens and displays the template
5. Be sure to name the worksheet when you save it

Creating a Template:

1. Open a worksheet you want to use as the basis for the template
2. Choose Save As and enter a name for the template
3. From the Save as Type list, select Template
4. Save the file inside the Templates folder

That’s it! Try it out – Templates can save you a lot of work, and help you maintain a consistent, professional look.

Sorting Order – Have it Your Way

Although the innate sorting options in Excel are usually sufficient, there are times when you need a Special Sorting Order to fit your data. Never fear, you can easily create your own customized sorting orders.

For instance, if you have a field containing your corporate locations (let’s say Chicago is the home office, and the other locations are ranked according to production). You may want Excel to sort the locations as follows: Chicago, Minneapolis, Albuquerque, and Los Angeles. The problem is, of course, that Excel would typically sort this list alphabetically, starting with Albuquerque. The solution, however, is so easy you will laugh (it is good to laugh).

Follow These Steps to Create a Custom Sorting Order for Your List:

1. Go to Options in the Tools menu
2. Select the Custom Lists tab
3. Ascertain the NEW LIST is selected in the Custom Lists list
4. In the List entries dialog box, type or paste your custom list in the order you wish to have them appear.
   

Bamm! That’s it! When you want to have Excel sort according to this new list in the future, just go to Sort, click the Options button, and choose your new customized list.

Quick Filter Selection

More speed and simplicity are always good things in Excel. Wouldn’t it be great to be able to use the functionality of AutoFilter whereby you merely select the value in an active cell for your filter criteria?

Although it is easy to do this in Excel 2007 (improved functionality), you can use this cool feature in prior versions of Excel versions of Excel as well (if you know where to look).

The fact is that it is a bit tricky to find, and perhaps even confusing when you do. For instance, in Excel 2003, you should:

1. Click on Tools and go to Customize
2. Choose Data under the Categories and find the “AutoFilter” in Commands
3. Drag the AutoFilter icon to your toolbar

What is confusing is that this “AutoFilter” works differently than the one you most familiar with.

Here is what you can do with it:

1. Point to any cell in your table
2. Click on your new “AutoFilter” icon
3. The table will automatically filter according to your selection
4. Cumulative filters can be added by selecting another cell and clicking on the icon again

Wow! How cool is that?!? Try it out; you may never go back to the ordinary AutoFilter again!

Countif and Concatenation

As any longtime user of Excel will tell you, Countif is a powerful tool that is frequently useful. To maximize the use of this function, you can reference cell locations through the use of concatenation (simply an “&” in this case).

For instance, if you wish to count only cells that are greater than the value in a particular cell, (A30 in this example), you should use Quotation Marks for the Greater Than symbol, and an & preceding the cell reference:

=COUNTIF(A1:A29, ">"&A30)

Give it a try. Countif may be even More Powerful than you thought!