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!

Navigating on Excel

Readers of this blog have said they would like to occasionally return to some of the basics, so today we are going to review the topic of navigating in Excel.

Sailing the seas of your Excel worksheets can be chore unless you know a few simple tricks. For instance, if you wish to go to your last entry at the bottom of a list that contains 30 records, scrolling to where you wish to go is simple and effective. When you have a list containing 3,000 records, however, it is a bit tedious.

As is true with so many Cool Moves in Excel, keyboard shortcuts rule when it comes to saving time moving from one location to another on your spreadsheet.

Here are few Cool Moves you can make without ever touching a mouse:

1. Control / Down Arrow: Goes to last cell in column with data

2. Control / Right Arrow: Goes to last cell in row with data

3. Control / End: Goes to last row, column and cell

4. Control / Home: Returns to cell A1

When using your mouse, another way to navigate to the end of your data (whether in a column or row) is to carefully place your pointer on the adjacent border of cell in your range and double-click. For instance, if you wish to navigate to the last cell in a column of data that starts with cell C1, you can select C1 and double-click on the bottom border of the cell.

Want one additional way to navigate when you know the exact address of some remote cell? Simply enter the address (e.g. ET3000) in the Name Box and Bamm; you have beamed directly to that location.

Try a few different ways of navigating on the Excel seas; you can sail to where you want to be in the blink of an eye!

Dynamic Chart Title (Oh, Wow!)

If you are looking for an “Oh, Wow!” feature for your next report containing a chart, the use of a Dynamic Chart Title is for you! By linking the chart title to a value displayed in a specific cell on your worksheet, you can create a very accessible chart that will amaze and delight your users.

As we have discussed before, it is super easy to create a dropdown list using Validation/List. If you have created a table that changes in accordance with the value shown in the dropdown, (e.g. a salesperson’s name), you can in turn create a chart that shows the updated data (e.g. a salesperson’s performance), along with the sales professional’s name in the title.

Here is how you do it:

1. Select the Chart Title
2. Go to the formula bar and type “ = ” and
3. Select the cell to which you want to link (be sure to use an absolute reference)
4. (Note: The final cell reference formula should look something like: “ =Sheet1!$B$2 ”

Oh, Wow! What a great way to add functionality, ease-of-use, and Pizzazz to your reports!

Happy Holidays, All!

When in Rome...

Once in a while, it is good to have a bit of geeky fun. Although of no real practical use, it can be interesting, (once again, in a geeky sort of way), to play around with Roman numerals. For an Excel user, it is particularly interesting to find that the application has a built-in Roman numeral function.

Practicality, of course, can be overrated, and it is readily apparent that Hollywood, Football, and the Olympics have all used Roman numerals on a regular basis. If you also wish to do this sometime in Excel, (I probably wouldn’t recommend it for your next quarterly report to your boss, unless the individual has a really good sense of humor), you can use the Roman function.

For a classic numeral, (other formats are available, but who needs them…), simply enter a value in cell A1 and type, “=Roman(A1)” in cell B1. Hit Enter and Presto, a Roman numeral of the A1 number! Now you will be all set if the NFL needs someone to come up with the name for the next Super Bowl!