Excel is great for amassing information, both numeric and textual. But without formulas it is hard to imagine that Excel would be anything more than a simple data storage program. The thing that makes formulas powerful, however, are worksheet functions. There are hundreds of worksheet functions available to do everything from simple summations to complex statistical analysis.

Tips, Tricks, and Answers

The following articles are available for the 'Worksheet Functions' topic. Click the article''s title (shown in bold) to see the associated article.

   Calculating Combinations
The COMBIN function is used to determine the number of combinations that can be made from a group of elements. This tip explains the function and how you can use it.

   Calculating the Day of the Year
Need to know what day of the year a certain date is? You can figure it out easily using the formulas in this tip.

   Checking for Text
Need to figure out if a particular cell contains text? You can use the ISTEXT function to easily return this bit of trivia.

   Choosing SUBSTITUTE or REPLACE
If you need to modify information in a text string, two common functions you can use are SUBSTITUTE and REPLACE. This tip examines how these functions work and when they should be used in your worksheets.

   Cleaning Text
You can use the CLEAN worksheet function to remove any non-printable characters from a cell. This can come in handy when you want to pare down what is stored in a worksheet.

   Colors in an IF Function
You can use the IF worksheet function to test for a number of different conditions or values. You can't use it to check for cell colors, however. Here are some ideas on how you can take cell colors into account in the IF function.

   Converting Codes to Characters
Character codes are the numeric values used, by a computer, to signify various alphanumeric characters. You can use the CHAR worksheet function to discover the character code of any character.

   Converting Radians to Degrees
When applying trigonometry to the values in a worksheet, you may need to convert radians to degrees. This is done by using the DEGREES worksheet function, described in this tip.

   Converting to Hexadecimal
Excel allows you to easily convert values from decimal to other numbering systems, such as hexadecimal. This tip explains how to use the DEC2HEX worksheet function.

   Converting to Octal
If you need to do some work in the base-8 numbering system (octal), you'll love two worksheet functions provided by Excel for this purpose. These functions allow you to convert values to octal and convert them back again.

   Converting to Other Number Systems
Most calculations are done using the decimal numbering system. If you need to convert your decimal values to a differing numbering system, then you'll appreciate the functions discussed in this tip.

   Counting Displayed Cells
When you filter data, Excel displays only a portion of what is really in a worksheet. If you want to count the number of cells that are displayed after filtering, then you'll want to explore the techniques in this tip.

   Counting the Number of Blank Cells
If you need to count the number of blank cells in a range, the function to use is COUNTBLANK. This tip discusses the function, along with a "gotcha" that you should be aware of.

   Counting with Formulas
When you need to count a number of cells based upon a single criteria, the standard function to use is COUNTIF. This tip explains, through an example, how you can use the function to do the desired counting.

   Determining a Value of a Cell
Cells can store all sorts of information that can be formatted and displayed in a myriad of ways. If you want to quickly get to the value stored within the cell, you can use the N worksheet function.

   Determining Columns in a Range
If you need to know the number of columns in a particular range, you can use the COLUMNS worksheet function. This tip demonstrates how simple it is to use the function.

   Determining the Least Common Multiple
Need to figure out the least common multiple of a range of values? It is a snap when you use the LCM function, described in this tip.

   Disabling #SPILL! Errors
In the newest version of Excel, a change in how formulas are calculated can cause havoc for some "older" formulas. Here is why that happens and what you can do about it.

   Displaying Letter Grades
Grading in schools is often done using numeric values. However, you may want to change those numeric values into letter grades based upon a determined scale. This can be done most easily using Excel's lookup functions.

   Doing Math Using Roman Numerals
Excel allows you to place Roman numerals in your cells, as well as to convert to and from Roman numerals. In this tip you discover how to create a formula to perform math with Roman numerals.

   EOMONTH Function is Flakey
Some users have reported problems using the EOMONTH function in later versions of Excel, beginning with Excel 2007. The problem seems to be related to Excel's compatibility mode and the fact that the function was moved from the Analysis ToolPak to Excel itself.

   Establishing a FLOOR and CEILING
Excel includes a surprising number of functions you can use to round your data. Two such functions are FLOOR and CEILING, described in this tip.

   Exact Matches with DSUM
The DSUM function is very handy when you need to calculate a sum based on data that matches criteria you specify. If you don't specify the criteria just right, however, you may not get the results you expect.

   Finding the Date Associated with a Negative Value
When working with data taken from the real world, you often have to determine which certain conditions were met, such as when a particular reading dropped below a certain value. This tip examines how you can easily tell the date on which a reading drops below zero.

   Finding the Lowest Numbers
Need to find the lowest numbers in a range of values? It's easy to do using the SMALL worksheet function, or you can use a macro for more demanding needs.

   Finding the Nth Root of a Number
Finding a square root is easy because Excel provides a worksheet function for that purpose. Finding a different root may not be as easy, unless you adapt the formula presented in this tip.

   Finding Unique Rows Based on Two Columns
Using the UNIQUE function you can derive unique values from a range of cells. By expanding the range of cells accessed by the function you can control what is returned.

   Getting Help when Entering Functions
Need a quick memory jog when entering a worksheet function? Here's a shortcut that will be invaluable.

   Getting the Name of the Parent Workbook
If you need to insert into a cell the name of the workbook in which a worksheet is contained, you can use the CELL function. This tip explains the proper way to get the info you need.

   Indirect References to a DSUM Parameter
Indirect references can be very helpful in formulas, but getting your head around how they work can sometimes be confusing. Here's an example of using an indirect reference within another formula.

   Leaving a Cell Value Unchanged If a Condition Is False
Ever want the IF function to only return a value if the condition it is testing is true, and not if the condition is false? This tip explains how this function works and what you can do to only return what you are looking for.

   Making PROPER Skip Certain Words
The PROPER worksheet function is used to change the case of text so that only the first letter of each word is uppercase. However, some words should not have the initial letter uppercased. There are a couple of ways you can modify the work done by PROPER, as described in this tip.

   Making VLOOKUP Case Sensitive
The VLOOKUP function, like other lookup functions in Excel, is not case sensitive. In other words, it doesn't matter whether the characters being evaluated are upper- or lower-case. If you need the function to pay attention to character case, then you will need to devise a workaround. The techniques in this tip are a great place to start.

   Making VLOOKUP Trigger a Macro
VLOOKUP is an oft-used worksheet function to lookup values in a data table. If the function cannot return a value, it normally returns an error. This knowledge can help you figure out how to run a macro when VLOOKUP fails.

   Making Your Formulas Check for Errors
Want to use a formula to check if there is an error in your formula? (Sounds confusing, but it's not that bad.) You'll love the IFERROR function, described in this tip.

   Median of Selected Numbers
Need to find a median value in a series of values? It's easy with the MEDIAN function. What isn't as easy is to derive the median from only a subset of the values based upon some criterion. Here's an easy way to approach the task.

   Modifying Proper Capitalization
The PROPER worksheet function is used to change the case of text so that the first letter of each word is capitalized. If there are some words you don't want to have an initial cap, then you'll want to use the ideas presented in this tip.

   Nesting IF Worksheet Functions
The IF worksheet function is very handy to make conditional evaluations. You are not limited to a single IF comparison, however. Excel allows you to easily nest IF statements within each other.

   Outstanding Macro Function Reference
Looking for a great reference that you can use to help figure out the various worksheet functions available in Excel? Here's a free resource you need to check.

   Phantom Counts
Two common worksheet functions used to count things are COUNT and COUNTA. Not understanding how these functions treat cell contents can result in incorrect counts.

   Random Numbers in a Range
Excel provides several different functions that you can use to generate random numbers. One of the most useful is the RANDBETWEEN function, which allows you to generate a random number between a lower and upper boundary that you specify.

   Returning an ANSI Value
Need to know the character value of the first character in a string? It's easy to do, without using a macro, by using the CODE function, described in this tip.

   Returning Blanks or Asterisks from a Lookup
Want to return more than a value when doing a lookup? Here are a couple of ways to do it by adding an IF clause to your formula.

   Returning Blanks with VLOOKUP
Normally the VLOOKUP function returns a value, and if it can't return a value it returns a zero. Here's how you can use the function within a formula to be more discriminating in what is returned.

   Returning Item Codes Instead of Item Names
The data validation capabilities of Excel are really handy when you want to limit what is put into a cell. However, you can't use data validation to return a value different than what the user selects. There are ways around this, though.

   Returning the Left-most Characters
When working with text in a formula, you may need to extract the left-most characters from a string of text or from a cell. You can do this using the LEFT worksheet function, described in this tip.

   Returning the MODE of a Range
The MODE function is used to determine the most frequently recurring value in a range. This tip explains how to use the function in your worksheet.

   Returning Values to the Left of a VLOOKUP
VLOOKUP is a great function to use in accessing data based on a lookup value. Problem is, you can't easily return information to the left of the cell in which you are looking up values. This tip focuses on three approaches to getting around this limitation.

   Selecting Random Names
Got a ton of names from which you need to select a few random names? There are several ways you can extract what you need; several different ideas are explained in this tip.

   Specifying a Date Range in a SUMIFS Formula
The SUMIFS function allows you to specify criteria by which values can be included in a sum. Putting together the criteria for the function can be confusing, though, when working with dates. This tip provides a step-by-step look at how you can specify the criteria properly.

   Specifying a Language for the TEXT Function
You may want to use Excel to display dates using a different language than your normal one. There are a couple of ways you can go about this, as described in this tip.

   Specifying Proper Case
If you need to change the case of letters in a cell, one of the functions you can use is the PROPER function. This tip describes how to use the function to alter the appearance of your text.

   Strange ATAN Results
You may use Excel's trigonometric functions to do some quick calculations, and suddenly notice that the results in your worksheet are different than the results you get using a calculator. The reason could be as simple as understanding what Excel expects in its functions.

   The EDATE Function
Want to figure a date a certain number of months in the future or past? The EDATE function may be just what you need for your calculation.

   Understanding the LET Function
The LET function provides an easy way to define and use variables within a formula. This tip shows you how you can start using the function in your worksheets.

   Understanding the POWER Function
The POWER function is used to raise one value to another value. However, Excel also provides an exponentiation operator for this purpose. Which one should you use?

   Understanding the VLOOKUP Function
Functions are at the heart of Excel's power in working with data. One of the most misunderstood functions provided by Excel is VLOOKUP. Here's a brief overview of how to use the function.

   Using a Cell Value as a Worksheet Name in a Formula
Excel allows you to easily develop formulas that pull values from worksheets and workbooks other than the one in which you are working. A great way to put together such a formula is through the use of the INDIRECT function, described in this tip.

   Using a Week Number as One Criterion in a Formula
The SUMIFS function can be quite powerful in conditionally summing information based on criteria you specify. This tip looks at one example of using the function based on two criteria, one of which is the week number of the dates associated with some values.

   Using COUNTIF with Colors
Excel allows you to easily format cells with different fonts, borders, and colors. If you want to count the number of cells that use a particular background color, there are a couple of approaches you can use. This tip shows you how.

   Using GEOMEAN with a Large List
When performing a statistical analysis on a large dataset, you may want to use GEOMEAN to figure out the geometric mean of the data. Doing so, however, could possibly generate an error, as described in this tip.

   Using the ABS Function
Need to find the absolute value of a number? That's where the ABS function comes into play.

   Using the COLUMN Function
Need to know the column number for use in a formula? The worksheet function you want is the COLUMN function, described in this tip.

   Using the CONCATENATE Worksheet Function
The process of combining string (text) values to make a new string is called concatenation. Excel provides the CONCATENATE function to accomplish the task, but there is an even easier way to join strings together.

   Using the EOMONTH Function
If you need to determine the date of the last day in a month, it's hard to beat the flexibility of the EOMONTH function. This tip explains how to use this function in your formulas.

   Using the FORECAST Function
Excel provides a handy worksheet function that allows you to forecast values based upon a set of known values. This function, appropriately enough, is known as the FORECAST function and is described in this tip.

   Using the IF Worksheet Function
Programmers know that a staple of any language is the ability to create conditional statements. Excel understands this, as well, so it provides the IF worksheet function that allows you to evaluate an expression and thereby conditionally choose a result.

   Using the INT Worksheet Function
The INT function allows you to convert a value to an integer. The effect the function has depends on the characteristics of the number it is converting.

   Using the IRR Function
When working with finances, you often need to know the rate of return on a given investment. The most common type of calculation used is the internal rate of return, which in Excel is calculated using the IRR worksheet function.

   Using the REPT Function
Excel includes a handy function that allows you to repeat characters or strings of characters. How you use the REPT function is presented in this tip.

   Using the TRUNC Worksheet Function
Want to chop off everything after a certain point in a number? The TRUNC function can help with this need.

   Using the UNIQUE Function
The UNIQUE function can be used to evaluate a range and return the unique values in that range. Understanding how the function works, as described in this tip, can allow you to do even more with UNIQUE.

   Using the WEEKNUM Function
Need to know which week of the year a particular date falls within? Excel provides the WEEKNUM function so you can easily calculate this statistic.

   Using the XIRR Function
One of the financial worksheet functions provided in Excel is the XIRR function. This is used to figure out an internal rate of return when the payments associated with an investment are not evenly spaced. Here's how to use the function.

   Ways to Concatenate Values
Users of the most recent versions of Excel have four different ways available to combine values into strings. Even those using older versions have two ways they can use. So how do these concatenation techniques compare to each other? This tip provides the info you need.