Updated

Excel formulas: The most popular functions and tools, with examples

Make sure these date and time functions, math tricks, and data manipulation tools are all at your disposal.

Today's Best Tech Deals

Picked by PCWorld's Editors

Top Deals On Great Products

Picked by Techconnect's Editors

1 2 3 Page 2
Page 2 of 3
07 the min max function JD Sartain / IDG Worldwide
The MIN & MAX function

7. CONCAT/CONCATENATE

The functions CONCAT and CONCATENATE do the same thing: They both combine multiple cells, ranges, or strings of data into one cell. The most common use of this function is to combine first and last name into one cell or join the city, state, and ZIP code into one cell.

NOTE: CONCAT replaced CONCATENATE in Excel 2016, but both functions are still available. Note that CONCAT appears only under Formulas > Text and Formulas > Insert Function > Category > Text, but both CONCAT and CONCATENATE appear under Formulas > Insert Function > Category > All.

Enter some first names in column A and last names in column B. Enter the following formula in column C: =CONCATENATE(A4,” “,B4) or =CONCAT(B4,” “,C4), then copy the formula down. What are the double quotes for? See Note below #2.

2. Enter a few cities (or ski resorts) in column F, states in column G, and ZIP codes in column H. Enter the following formula in column I: =CONCATENATE(F4, “,”, “ “, G4,” “,H4).

NOTE: If you want a space between the first and last name, you must enter that space inside quotation marks in your formula. The same thing is true for punctuation, such as a comma between city and state. In the following formula the “,” (quote comma quote—in red) tells Excel to insert a comma between the data in F15 (city) and the data in G15 (state). The “ “ (quote space quote—in purple) adds a space after the comma between F15 (city) and G15 (state) and another space between G15 (state) and H15 (zip code).

=CONCATENATE(F15, ”,”, ” “, G15, ” “,H15).

08 use concatconcatenate to combine data from multiple cells into one cell JD Sartain / IDG Worldwide
Use CONCAT & CONCATENATE to combine data from multiple cells into one cell

8. TRIM

This function removes extra (or padded) spaces that infect your data as a result of user error, downloading data from an external source such as the Internet, or importing data from another computer system. And you don’t have to “tell Excel” where the spaces are located in the string of text in each cell; it recognizes the extra spaces and removes them. Note; however, that it will not remove a space in the middle of a word. The syntax is simple: =TRIM(cell address).

1. Enter some data in column A. Add some spaces before, after, and in the middle of multiple words, then enter the following formula in cell A4: =TRIM(A4).

2. Copy the formula down. It’s that easy!

NOTE: There is one case where this function does not work, and that’s with a non-breaking space character used in webpages. The decimal value is 160, and the HTML code is &nbsp. You can remove this character using a combination of TRIM, CLEAN, and SUBSTITUTE.

09 use trim to remove extra or padded spaces from your data JD Sartain / IDG Worldwide
Use TRIM to remove extra or padded spaces from your data

9. UPPER/LOWER/PROPER

Another easy group, these functions convert text in a cell or range of cells to uppercase, lowercase or proper case. Proper case is first letter in caps and remaining letters in lowercase. The syntax is simple: function, cell address.

1. Enter some mixed-case data in column A; e.g., cAlifornia, nEW yORK, spanISH. Enter the following formula in column B: =UPPER(A4), in column C: =LOWER(A4), and column D: =PROPER(A4).

2. Notice that Excel corrects all the misplaced case errors and converts the data correctly. Copy the formulas down, and that’s it for this simple one.

NOTE: In Word, you can use Shift-F3 to cycle through uppercase, lowercase, and proper case, but this shortcut key is not available in Excel. Note that the Excel function =PROPER is called Sentence case in Word.

10 use upper lower proper case to alter case of characters JD Sartain / IDG Worldwide
Use UPPER & LOWER & PROPER case to alter case of characters

10. REPT

When Lotus 1-2-3 was the only game in town, you could enter a backslash followed by any character and Lotus would repeat that character throughout a cell. If the cell width grew larger or smaller, so did the character. In Excel, this feature is handled by the function REPT. It’s not quite as efficient because you must add the character to the formula, then specify how many times you want that character repeated. This means if the cell width is increased, the repeated character is not, and if the cell width is decreased, the repeated character bleeds over into the adjacent cell.

The syntax for this function: =REPT(“*”,5); =REPT(“—“,10), =REPT(“+”,12). You can repeat any character on the keyboard plus symbols.

11 use the rept function JD Sartain / IDG Worldwide
Use the REPT function

11. IF statement

The IF function (also more commonly called IF statements) work like this: IF, then, else. Basically, that means if a condition is true, then do one thing, else/otherwise do something else. For example, if the puppy is a Labrador, then buy a blue collar, otherwise/else, buy a red collar.

The syntax (the way the commands are organized in the formula) of the IF statement is: =IF(logic_test, value_if true, value_if_false). IF statements are used in all programming languages and, although the syntax may vary slightly, this function provides the same results.

1. Enter the following column headers: Cookie Boxes Sold; 3rd Prize =More than 500 Sold, Less than 1000; 2nd Prize =More than 1000 Sold, Less than 1500; 1st Prize =More than 1500 Sold, Less than 2000; Grand Prize =More than 2000 Sold

2. Enter some numbers into column A4:A13. Mix it up so you get data in all of the Sold columns.

3. Enter this formula in B4: =IF($A4>500, $A4, 0).

NOTE the $ sign before the column letter ‘A’ in the above formula. Place your cursor on the first ‘A’ in the formula, then use the function key F4 to cycle through the Absolute and Relative References. Stop when the $ sign precedes the ‘A’ (for each A in the formula). This tells Excel NOT to change the column letter, but only change the row numbers when this formula is copied. If you put a dollar sign before both the column letter and the row number, neither would change. 

4. Copy the formula in B4 to C4, D4, and E4, then edit as follows: C4 =IF($A4>1000, $A4, 0); D4 =IF($A4>1500, $A4, 0); and E4 =IF($A4>2000, $A4, 0). Then copy down.

5. The formula works, but you have to review each column to see who won the prizes, because each column shows ALL the values greater than the amount in the formula. That’s ok for a small spreadsheet, but not for anything larger than a single screen.

6. We need a Nested IF statement for this one. Repeat numbers 1, 2, and 3 above beginning on row 20; but instead of the formula in 3 above, enter this formula in B20: =IF(AND($A20>500,$A20<1000),$A20,0).

7. Repeat number 4 above, but edit the formulas like this: C20 = =IF(AND($A20>1000,$A20<1500),$A20,0); D20 = =IF(AND($A20>1500,$A20<2000),$A20,0); and E20 = =IF($A20>2000, $A20, 0). Yes, this last one is different because there is no “less than” amount. Then copy down. Now you can look at each column and determine immediately who the winner is for that category.

12 basic if statements and nested if statements JD Sartain / IDG WorldwideBasic IFstatements and nested IF statements

12. AND/OR

AND and OR are common functions in the programmers’ environment, also referred to as Boolean operators (along with NOT). AND means that all conditions in the query must be true; OR means that at least one condition must be true.

For example, looking for an applicant with MS Word AND MS Excel experience means the applicant must have both skills to qualify for the job. This condition would provide a TRUE result. Looking for an applicant with MS Word OR Excel means the applicant must have one OR the other, but not necessarily both. Also a TRUE result. Having neither skills would, obviously, provide a FALSE result.

1. Copy the numbers from the spreadsheet in figure 13, or download the full workbook (link below).

2. Enter the following AND formula in cell D4: =AND($B4>=501,$C4<=500). Again, note the $ signs. Then copy down to cell D13.

3. Enter this formula in cell F4: =OR($B4>=501,$C4<=500), then copy down. Notice the results in the rows with borders; that is, 5, 8, and 13. The AND results are all FALSE because both conditions were false (or not true); while the OR results were all TRUE because one of the conditions was true, while the other was false.

If this seems confusing, study the numbers in columns B and C. Then read the formulas that calculate for the AND function, then the OR function, and it will make more sense.

14 and plus or functions JD Sartain / IDG Worldwide
AND plus OR functions

13. NOT

Having explained above how the AND and OR functions (also called Boolean operators) work, the third Boolean operator in that mix is the NOT function. Ever search through a really long list of data and wish you could remove all the entries that do NOT apply? For example, I want to see everything available about alternative energy EXCEPT (or NOT) nuclear.

In Excel, it’s an easy task. Create a list of 25 companies that provide various alternative energy sources and what those resources are (columns A, B, C; begin on row 4). Enter the following formula in cell C4: =NOT(B4="Nuclear"). Then copy the formula from C4 down to C5:C28.

If the response is TRUE, the energy source is NOT nuclear. If the response is FALSE, the energy source IS nuclear. Yes, it’s reverse logic and you may not immediately see a need for this function but, if you’re an avid Excel user, you will discover many reasons to use this formula in the future.

TIP: Remember that Boolean logic applies throughout all database programs, including your favorite search engines. The Boolean operators AND and OR must be in all-caps to function as an operator. In Google, the Boolean NOT operator is the minus sign; for example, to list all alternative energy sources EXCEPT nuclear, type this in the search field box: list all alternative energy sources –nuclear.

Got text and numbers with delimiters all over the place? Keep reading for the formula to fix that.

1 2 3 Page 2
Page 2 of 3
  
Shop Tech Products at Amazon