Spyder's Favorite Excel Tips

I don't know about you, but Microsoft Excel is my absolute favorite program in the world. I use it for almost everything. I still run Office 2003 and have not yet upgraded to 2007 version, but I still want to list for you some of my favorite little shortcuts, tips and tricks. They are in no particular order and some you might already know and use, but for some novices out there, I hope there is something here that you will learn, use and enjoy.

Microsoft released it's first spreadsheet program, called Multiplan, in 1982. It's presence was dwarfed by the popular Lotus 1-2-3 software. Oddly, the first version of Excel, Excel 1.0, was released for the Apple Macintosh platform in 1985, then to PC for the Windows platform in 1987, when it was known as Excel For Windows 2.0. By 1988, Excel became the predominant spreadsheet software.

Excel is used for calculations and tests, graphs and charts, pivot tables for data, sorting data, creating organized documents, analyzation and more. Some of the popular uses are for organizing address books, lists, account statements and invoices, expense reports, calendars, reports, stationery, etc.

To learn more about Excel and these functions and others, click on Help in Excel and type in the function name or what calculation you want to research.

This list is some of the more basic functions for more of the novice Excel user. There are tons of formulas of functions, but I have chosen some of my favorites.

If any of these functions do not work, you might need to install and load the Analysis ToolPak add-in. To install this add-in, go to Tools, click Add-Ins. In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK (this might require you to have your Microsoft Office disc handy).


TODAY Function
In any cell, type in:
=TODAY()
The value will display the current date in that cell. If you save your document, this value will update with the current date when you view the file.

This comes in handy for many reasons. I like using it for accounting statements to determine how many days until an invoice is due or how many days it is past due. If you want further information on how I utilize if for that purpose, please email me.

Related to the TODAY Function, is the NOW Function, typed as this in any cell:
=NOW()
In addition to the date, it will show the current time in 24-hour view.

Shortcuts to insert the current date or time in a cell as a STATIC value (means it is a fixed time or date and does not update when you view your document at different times and dates), is to type this in a cell:
CTRL+;
That is type the Control (CTRL) key and hold it down and then hit the semicolon key, then hit enter.

For just the current time, select a cell and press:
CTRL+SHIFT+;

For both the current date and time, select a cell and press:
CTRL+; then SPACE then CTRL+SHIFT+;

Now, what's cool about using Date functions, is you can use them for calculations. The reason is that Excel stores dates as sequential numbers, where January 1, 1900 is serial number 1, for example, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. So you can determine 90 days from today by typing this into any one cell:
=TODAY()+90

Likewise, if you already are using the TODAY function on your document, you can add 90 days to that cell. For example, if you are displaying the value of the TODAY Function in cell A1 and you want to add 90 days to that value in cell A2, just type in cell A2:
=A1+90


Basic Adding, Subtracting, Multiplying, Dividing, Averaging
The =sign (equal sign) is very powerful and if you enter it in a cell as the first character, Excel will think you are creating a formula. You can use the symbols + (plus sign), - (minus sign or hyphen), * (multiply sign or asterisk) and / (divide sign or forward slash or forward diagonal).
To combine the equal sign and using one of the basic formula characters, you can easily and quickly add, subtract, multiply and divide and never need a calculator again.
Here are some basic formulas which you would type into any single cell:

To add 7 and 8, type:
=7+8 then hit enter. The value returned in the cell will appear as 15.

To subtract 3 from 10, type:
=10-3 then hit enter. The value returned in the cell will appear as 7.

To multiply 5 and 4, type:
=5*4 then hit enter. The value returned in the cell will appear as 20.

To divide 25 into 100, type:
=100/25 then hit enter. The value returned in the cell will appear as 4.

Now to take these basic formulas just one step further.
Let's say you have a range of numbers you want to add. For example you have numbers or dollar amounts in a single column, let's say cells A4 down to A15, you now want to use the:
SUM Function
To use this function in our example, you would type this in the cell you want the sum of that range to appear:
=SUM(A4:A15) then hit enter
The formula is asking for a sum, in parenthesis you are noting the range from beginning cell to end cell and the : (colon symbol) is used as the word "through" meaning it wants the sum of the range from cells A4 through A15.

Now if you want the average of all the numbers in the range in the above example, you would use the:
AVERAGE Function
and your formula would be typed as:
=AVERAGE(A4:A15)
This formula is now looking for the average of all the values in cells A4 through A15.

The SUM Function and AVERAGE Function do not require you to use cell values. You can enter static values right in the function itself.
For example, let's say you want the add up the numbers 1, 3, 7, 44 and 11. You would type:
=SUM(1,3,7,44,11) then hit enter
Your answer will be 66.
In the parenthesis, just put the numbers you want to add up and separate with a comma and no spaces.

For example, let's say you want the average of the numbers 1, 3, 7, 44 and 11. You would type:
=AVERAGE(1,3,7,44,11) then hit enter
Your answer will be 13.2.
In the parenthesis, just put the numbers you want to average and separate with a comma and no spaces.

Then to top it all off, you can use a combination of cell values and static values in your sums and averages.
If you want the sum of the value of the range of cells from A4 through A15 and also add in the static number of 18, you would type:
=SUM(A4:15,18)
So if you want an average of the value of the range of cells from A4 through A15 and also include the static number of 18, you would type:
=AVERAGE(A4:A15,18)


COUNTIF Function
The COUNTIF Function will count the number of times a certain condition is met in a range of cells. To let's say you have a column of numbers in the range of cell A2 through A30 and the values vary with some being over 100, and you want to know how many instances occur in that range where the value is greater than 100. You would type in a new cell:
=COUNTIF(A2:A3,">100") then hit enter


DATEDIF Function
This is a cool one you can have fun with. This will calculate the difference between any two dates. Let's say you have a date in cell A1 and a date in cell B1 you can determine how many years, months or days between those two dates by typing:
=DATEDIF(A1,B1,"y") then hit enter
You can substitute the letter y (for years) with the letter m (for months) or d (for days).

Now for more fun...
You can use the DATEDIF Function combined with the TODAY Function to determine how old you are in years, months or days.
Enter your birth date in any cell, let's say A1. Now in cell A2, type in:
=DATEDIF(A1,TODAY(),"d") then hit enter
This is telling Excel you want to know the range between the date value in cell A1 to today's date and you want your answer in a value of the number of days (the d in the formula).
Kinda cool, huh?


The CONVERT Function
To use this function, you will have needed to install the Analysis Toolpak (as mentioned at the top of this post). This is one of the coolest Excel functions. It will let you convert between measurements of units of time, weight, mass, temperature, etc. The syntax of the formula is:
CONVERT(number,from_unit,to_unit)
Where "number" is the unit of measurement you want to convert. "from_unit" and "to_unit" get replaced with the type of unit you want to convert from and convert to. You can only convert weight to weight, distance to distance, temperature to temperature, etc. So don't try converting 10kgs to degrees in Celsius, ya hear?

You can look up CONVERT in Excel help to get a complete list of units to convert from and the abbreviations you need to use within the formula, but I will list some of the most common one's here with an example of each.

If you have a number 6 in cell A2, type:
=CONVERT(A2,"C","F") Convert 6 degrees Celsius to Fahrenheit (42.8)
=CONVERT(A2,"tsp","tbs") Convert 6 teaspoons to tablespoons (2)
=CONVERT(A2,"gal","l") Convert 6 gallons to liters (22.71741274)
=CONVERT(A2,"mi","km") Convert 6 miles to kilometers (9.656064)
=CONVERT(A2,"km","mi") Convert 6 kilometers to miles (3.728227153)
=CONVERT(A2,"in","ft") Convert 6 inches to feet (0.5)
=CONVERT(A2,"cm","in") Convert 6 centimeters to inches (2.362204724)

You can also use a static number in your formula instead of reading a value in a separate cell, such as:
=CONVERT(1.0, "lbm", "kg") Converts 1 pound mass to kilograms (0.453592)
=CONVERT(68, "F", "C") Converts 68 degrees Fahrenheit to Celsius (20)

Things to note using the CONVERT Function:
* If the input data types are incorrect, CONVERT returns the #VALUE! error value.
* If the unit does not exist, CONVERT returns the #N/A error value.
* If the unit does not support an abbreviated unit prefix, CONVERT returns the #N/A error value.
* If the units are in different groups, CONVERT returns the #N/A error value.
* Unit names and prefixes are case-sensitive (I learned this when I was converting temperature and trying to use "c" and "f" and wondered why the formula didn't work - it's supposed to be "C" and "F").

So an error example would be:
=CONVERT(2.5, "ft", "sec") Data types are not the same so an error is returned (#N/A)

Sweet, huh?


PROPER Function
Here's another one I find myself using all the time. Since I am in the music industry and deal with hundreds of record labels, I need to import the label's database into my database and I want the fields to be consistent. I use title-casing, meaning, I like the first letter of each word capitalized. Many times I get lists that are in ALL CAPS or all lower case and I hate that. Using the PROPER Function, I can convert from one to the other real easy.

Let's say in cell A1 I have the word SPYDER in all caps and I want to convert to title-casing (first letter of each word is capitalized), I would type this formula in the cell I want the result:
=PROPER(A1) then hit enter and the result will display Spyder.

Now to convert to ALL CAPS and all lower case, let's say I have in cell A1:
spyder's raNDOM tHiNGS
use:
=UPPER(A1) Changes text to all UPPERCASE (SPYDER'S RANDOM THINGS)
=LOWER(A1) Changes text to all lowercase (spyder's random things)
=PROPER(A1) Changes text to Title Case (Spyder'S Random Things)
(note: the letter S after the apostrophe in the Title Case version will be capitalized since that letter S is following a punctuation and is regarded as if the apostrophe is a space. This is something I don't like in the results of using this function, but, again, I still use this function all the time at work).


CONCATENATE Function
Here you can combine text or values from various cells or static values all into one cell. The basic formula can be done two ways:
=CONCATENATE (text1,text2,...)
or
=text1&text2&...
(where the &, or ampersand, is used as the combine formula)

To add a space between values, you need to put a space between to quote marks.

Here's an example. The following values are in these cells:
A1= I
A2= like to
A3= read
A4= Spyder's
A5= Random Things

You can combine the value of those cells into one new cell using either of these formulas:
=CONCATENATE(A1," ",A2," ",A3, " ",A4," ",A5)
or
=A1&" "&A2&" "&A3&" "&A4&" "&A5
(note: the quote marks are only surrounding a space between them to put the space between the values of the cells)
Your result will be:
I like to read Spyder's Random Things

To add a static value using these formulas, you can replace the cell location in the formula with the static value marked in quotes. So if you used either of these functions for the above example:
=CONCATENATE(A1," ",A2," ",A3, " ",A4," ",A5," every day")
or
=A1&" "&A2&" "&A3&" "&A4&" "&A5&" every day"
Your result will be:
I like to read Spyder's Random Things every day


Now the last trick I will teach you is a little more advanced, but if you just follow the instructions exactly, you will probably use this fairly often. I use this in many of my spreadsheets. This is my favorite Excel tip.


LOCATING DUPLICATES
In a range of cells by using conditional formatting.

Wow do I love this trick!!!
Let's say you have a email list of 5000 addresses but you don't want to sort them and scroll down and look to see if you have any duplicate matches. Or, let's say you are creating a customer account statement with 300 invoices and you want to make sure there are no duplicated invoice numbers. You want to make a formula that will make any duplicates pop right out in your face. Right?

This tip I got from the Microsoft Office Excel website

You can locate duplicates in a range of data by using conditional formatting and the COUNTIF function. Here are the details on how to make that work.

Set up the first conditional formatting formula
I'll start by setting up a conditional format for the first data cell. Later, I'll copy that conditional format for the whole range.

In my example, cell A1 contains a column heading (Invoice), so I will select cell A2, and then click Conditional Formatting on the Format menu. The Conditional Formatting dialog box opens. The first box contains the text, Cell Value Is. If you click the arrow next to this box, you can choose Formula Is.

After you click Formula Is, the dialog box changes appearance. Instead of boxes for between x and y, there is now a single formula box. This formula box is incredibly powerful. You can use it to enter any formula that you can dream up, as long as that formula will evaluate to TRUE or FALSE.

In this case, we need to use a COUNTIF formula (which we used earlier in this post). The formula to type in the box is:
=COUNTIF(A:A,A2)>1

This formula says: Look through the entire range of column A. Count how many cells in that range have the same value as cell A2. Then, compare to see if that count is greater than 1.

When there are no duplicates, the count will always be 1; because cell A2 is in the range, we should find exactly one cell in column A that contains the same value as A2.

Note: In this formula, A2 represents the current cell — that is, the cell for which you are setting up the conditional format. So, if your data is in column E and you are setting up the first conditional format in cell E5, the formula would be =COUNTIF(E:E,E5)>1.

Choose a color to highlight duplicated entries
Now it is time to select an obnoxious (that is, obvious) format to identify any duplicates that are found. In the Conditional Formatting dialog box, click the Format button.

Click the Patterns tab and click a bright color swatch, like red or yellow. Then click OK to close the Format Cells dialog box.

You will see the selected format in the preview box. Click OK to close the Conditional Formatting dialog box, and…

Nothing happens. Wow. If this is your first time setting up conditional formatting, it would be really nice to get some feedback here that it worked. But, unless you are lucky enough that the data in cell A2 is a duplicate of the data in some other cell, the condition is FALSE and no formatting is applied.

Copy the conditional formatting to the rest of the cells
You need to copy the conditional formatting from cell A2 down to the other cells in your range. With the cursor sill in A2, click Copy on the Edit menu. Press CTRL+Spacebar to select the entire column. Then click Paste Special on the Edit menu. In the Paste Special dialog, click Formats, and then click OK.

This will copy the conditional formatting to all cells in the column. Now — finally — you may see some cells with the colored fill formatting, indicating that you have a duplicate.

It is informative to go to cell A3 and look at the conditional formula (after you've copied it from A2). Select cell A3 and click Conditional Formatting on the Format menu. The formula in the Formula Is box has changed to count how many times the data in cell A3 appears in column A.

You can have up to 65536 cells with conditional formatting, each cell comparing the current cell to 65535 other cells. Technically, the formula in the first step could have been =COUNTIF($A$2:$A$1751,A2)>1.

Also, when copying the conditional format to the entire column, you could have selected just the cells that contained data before using the Paste Special command.

Highlight only the second instance of a duplicate
The previous solution assumes that you want to highlight both of the duplicate invoice numbers so that you can manually figure out which to delete or correct. If you don't want to mark the first occurrence of the duplicate, you can adjust the formula to:
=COUNTIF($A$2:$A2,A2)>1

Note: It is important to enter the dollar signs exactly as shown.

In the first argument of this formula, only the second cell reference for the data range changes as it is copied down the column. This means the formula will compare only the cells from the current cell up to the first cell in the data range when looking for duplicate entries.

Sorting the data
You really cannot sort a column on the basis of a conditional format. If you want to sort the data so that the duplicates are in one area, follow these steps:

First, type the heading Duplicate in cell B1. Then, type this formula in B2:

=COUNTIF(A:A,A2)>1

With the cursor in cell B2, double-click the AutoFill handle (the little square in the lower-right corner of the cell) to copy the formula all the way down the column.

You can now sort the columns by column B (descending), then by column A (ascending), to show the duplicate invoice numbers at the top of the range.