Copying Formulas

As we probably all know, you can use the fill-handle (small black box that appears when you hover your mouse over the lower right corner of a cell or range) to drag (and therefore, copy) a formula down the rest of the range. Now that is fine when you have just a few cells, but if the range has any significant length, “dragging” can be, well, a drag!

A better solution is to hover your mouse over the handle (you will see it turn into a crosshair) and Double-click it. Bamm! You have copied your formula down the entire range. It doesn’t matter whether the range is 3 rows or 30,000, the results are for all practical purposes, instantaneous!

Now, let’s say we have something just a bit more complex. It is important to be aware of Absolute and Relative (I know, let’s keep our relatives out of this…) references. For instance, if you want to lock into a column, put a $ sign in front of the column (e.g. $B2). If you want to lock into a row, put a $ sign in front of the row (e.g. B$2).

Make a worksheet similar to the illustration below and try constructing the following simple concatenation formula in cell B2:   =$A2&" Sales in "&B$1

Then grab the handle on cell B2 and drag it through cell E5 and watch what happens…

Is that cool or what?!?! Go on, give it a try!

Insert Function Wizard

Regardless of the version of Excel you may be using, the Insert Function Wizard can be a very helpful in performing a bit of magic when you need it. The magic it provides is translating your wishes into dreams come true (well, that is overstating it, but I think you will see my point).
Let’s face it, there are times when you may think that Excel has a Function that you can use in a formula, but you don’t know what it is called or how to use it. You can describe it, but what Excel calls it is simply a mystery. The solution is (a little magic music here…) the Function Wizard!

Just go to the icon that looks like an fx on your toolbar (or the Insert Function under Formulas if you are using Excel 2007), and type a brief description of what you want to do. For instance, let’s say you want to calculate what your loan payments are going to be when you buy the new car you have had your eye on. All you need to do is:

1) Pull up the Insert Function Wizard
2) Type your description in the search textbox (in this case, “loan payment”)
3) Select the function (in this instance it would be PMT)
4) Complete the Function Arguments and click OK

Presto! You have your customized function for your formula! Give it a try; it is easy and can perform a bit of magic when you need it.

Databases are Cool!

When it comes to maintaining the integrity and ease-of-use of reports over a length of time, nothing makes life for the Excel user more trouble-free and uncomplicated than using a well-constructed database and a few simple formulas. Creating a good database can solve a lot of miserable problems in Excel (I am sure most of us have seen how reports can build up to many, many pages, negatively impacting their usefulness).

Although setting up and maintaining a viable database is quite simple, there are a few basic principles by which you should abide. Let’s look at three simple rules:

1) When you first set up a database, be sure to include data in its most granular form. Each column should be characterized by conveniently small and independent parts. For instance, do not create a single column (or “Field” as it is commonly referred to) using a person’s full name. Use separate fields for first, middle, and last names. The same applies to locations, where it is best to separate city and state for instance.

By doing this, you simplify extracting specific data for your reports.

2) Eliminate any blank records, (a row is a record in a database), as well as any blank cells. Use “N/A” or similar notation for any cells in your database.

3) Finally, and very importantly, do not include any calculated fields in your database. The purpose of a database is to store data, not provide any computed information (that is what your linked reports are for).

Okay, you say, what do we do with this database? That is where the fun begins! Next week we will look at how to use some basic database formulas that will make your Excel life soooooo easy! (And that, as Martha Stewart would say, “Is a Good Thing”)

Paste Special Revisited

Back last June we took a look at how you can use Paste Special to preserve cells’ Values (rather than formulas).

Whereas that is cool, Paste Special has other valuable tricks! Here are a couple of my favorites:

1) Let’s say you would like to Multiply (works for other arithmetic functions as well) a range of cells by a certain value (For instance, perhaps you would like to increase all of your projections by 10%). Simply do the following:

a) Enter the value you wish to have as a multiplier (e.g. 1.10) in a cell, right-click with your mouse and copy it.
b) Select the range you wish to multiply, right-click and choose Paste Special
c) From the Paste Special menu, choose Multiply, click OK and Bamm! All of your cells in the range have been increased by 10%.

2) Another favorite of mine is the Transpose option on the Paste Special menu. Let’s say that you have created a range in a column, and you now wish you had made the range a row rather than a column. Paste Special to the Rescue! Simply do the following:

a) Select the range in the Column, right-click Copy and choose your location cell where you want the Row range to begin
b) Choose Paste Special
c) Select the Transpose option box and click OK.  Bamm! Your range has been changed from a column to a row (row to column works as easily).

How Cool is That?!? As I said back in June, Paste Special is our Friend!