Excel Tutorial Series - some simple functions

This is part two of the Excel Tutorial Series. Read all tutorial posts by browsing the Tutorial category.

I'll continue the tutorial series with some simple functions that can make your Excel life much easier.

The Proper Function

Used to capitalize the first letter of each word in a cell (and other letters too!). Specifically, it "capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters."

Type =PROPER(cell) and press Enter, where cell is a cell reference, string or single-cell range you are trying to change to Proper Case.

The Upper Function

Description:

Converts text in a cell to ALL UPPERCASE.

The If Function

Description:

Returns conditional value depending on evaluation of some expression.

IF function

The If function is used when your formula needs to make a decision. You can nest If functions to give your formulas intelligence. For example, this formula stays blank until you type a date in A1, then it prints the month, but if it is Christmas day you get a special message.

=IF(ISBLANK(A1),"",IF(MONTH(A1)=12,IF(DAY(A1)=25,"Merry Christmas!","December"),CHOOSE(MONTH(A1),"January","February","March","April","May","June",
"July","August","September","October","November")))

The VLOOKUP Function

Description:

Returns a value found in the same row a given number of columns to the right of a column of data in a table.

It's important to remember that the value to be found must be in the leftmost column in the table range you specify. That doesn't mean that the table has to be in column A, or that there can't be any data to the left of your table. For example:

Syntax: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Ex: =VLOOKUP(G2,B100:E500,4,FALSE)

This means "look for the value in G2 in B100:B500 and return the corresponding value in the fourth column in the range B100:E500 (which is column E)." So if the value in G2 was found in B89, this formula would return the value in E89. You'll notice that the table starts in column B, and that there can be anything in column A. The data to the left of the table is ignored by the function; for the purposes of the function, the table is B100:E500, so the lookup value must be in column B.

How to insert Functions on your worksheet

There are several ways to use Excel's built-in functions.

Start typing '=' in a cell, followed by function name and arguments
Click the function button next to the Formula bar: [fx]

insert function

On the Insert Menu, click 'Function…"

insert function menu

And you can also press Shift-F3 to bring up the Insert Function dialog box. These examples are for Excel 2003. If something has changed in Excel 2007, I feel for you

Analysis Toolpak

The Analysis Toolpak (ATP) is an add-in containing many important functions not available natively in Excel 2003.

For example:

NETWORKDAYS – calculate number of work days between two dates
WORKDAY – calculate work date with given number of work days
RANDBETWEEN – create random number between two given numbers

These functions are essential for several of the routines found on this site, for example Calculate Working Days Minus Holidays in VBA and Calculate Working Hours in VBA.

See Analysis ToolPak for a complete list of functions added by the ATP.

To install:

On the Tools menu, click 'Add-Ins.'
In the Add-Ins available box, select the check box next to Analysis Toolpak, click OK.
If you see a message that tells you the Analysis Toolpak is not currently installed on your computer, click 'Yes' to install it.

If you need to install it, you may need the Office installation CD.