Formulas and Functions made Fun!

Fun is usually not the first word that comes to mind when we talk about formulas and functions.

During Excel training one of the participants sat straight up in her chair and said to the colleague beside her “Isn’t this fun?”

This feeling can only come from a user who’s just discovered something exciting that will save them time and aggravation. Here are a couple of tips that may do just that:

Auto-fill formulas
Double click for vertical fills

In this screenshot there’s an easy way to copy and paste the formula =SUM(B7:D7) to the cells that total the remaining expenses for the 1st Quarter. Double-click the fill handle and the remaining results are entered!

TIP: You must wait until the mouse point changes to a cross hair shape before you double-click.

NOTE: this only works when filling vertically. You still need to drag the fill handle for horizontal fills.

Convert data to a Table
You don’t need to know how to build a function at all – Excel does it for you

1. To convert your data to a table click anywhere in your data and do one of the following:

a. On the Home tab and click Format as Table and choose a table style.
b. On the Insert tab, select Table.

2.  If the proper data range is selected, click OK.

Once your data is converted to a table you will see several tools available on the Design tab. In my opinion, the most useful one that I wish was a default is Total Row. When enabled this feature allows you to click on any cell at the bottom of anyone one column and you can choose from popular functions from a drop down list.

Oh, if you want to convert your table back to data, click Convert to Range on the Design tab.

Now wasn’t that FUN!!!!

Let me know if you have questions.

Lorrie