Fresh Repetitive Data

Bob is back! Greetings to all of my fellow Excel Enthusiasts; It is so good to be back on the blog after an unavoidable absence.

Here is a great little technique that I recently ran across. Although it is very intuitive to copy and paste repetitive data to a set of noncontiguous cells (copy data, select the cells and paste), entering newly typed data doesn’t work quite that way.

To enter new data into a series of noncontiguous cells, simply hold down the Ctrl key and select all of the cells into which you want to enter your new data. Then type the text you want to enter and (drum roll, please…) press Ctrl+Enter.

Bamm! Excel will enter the typed text into all of the cells in your selection. (How cool!)

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!

Green Bar Accounting Paper

I have to admit that I am old enough to remember using the old-fashioned Green Bar Accounting Paper (and not on a computer, mind you). One of my favorite variations had every third row highlighted in light green. A quick search of the internet shows that it is still around today, since it makes reading long rows of data much easier.

Wouldn’t it be great if there was an Easy Way to simulate this in Excel? Yes, indeed, and it is so simple, you will laugh! Here is how you do it:

1. Fill the third row in your database a light green

2. Select the first three rows (two with no color and the third in light green), and using the fill handle, drag to the end of the database

3. Important: Click on the Auto Fill Options box in the lower right-hand corner of your database and select Fill Formatting Only.

Voila! An Easy-to-read Green Bar Excel worksheet!