Formulas

Tips, Tricks, and Answers

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

Activating the Formula Bar with the Keyboard
Hate to take your hands off the keyboard while working on a worksheet? Here's one way to activate the Formula Bar without the need of using the mouse.

Adjusting Formulas when Pasting
The Paste Special feature in Excel can be used to uniformly adjust values and formulas. This tip shows how powerful this feature can be.

Alphabetic Column Designation
Want to find out the alphabetic designation of a particular column? There are several ways you can go about the task, but the results will depend on your version of Excel and the number of columns you are working with.

Automatically Numbering Rows
Adding row numbers to a column of your worksheet is easy; you just need to use a formula to do it. Here's a quick look at a few different formulas that will do the trick.

Calculating Statistical Values on Different-Sized Subsets of Data
Analyzing huge amounts of data presents challenges that are different from those faced with smaller amounts of data. This tip examines how you can analyze subsets of a huge data table to derive statistical values from that data. It examines a simple grouping technique, and then applies Excel's subtotaling and PivotTable features to derive the desired information.

Calculating the Distance Between Points
Excel is great a performing calculations, so it should be relatively easy to calculate the distance between two points, right? If those points are latitude and longitude coordinates, the answer is a bit more difficult than you may think.

Calculating the Interval Between Occurrences
With a long list of items in a worksheet, you may want to determine the last time a particular item appeared in the list. There are a couple of ways you can tackle this problem, as described in this tip.

Calculating the Median Age of a Group of People
Suppose you have a worksheet that contains a list of ages and then a count of people who correspond with those ages. You may wonder how you can figure out the median age for the people in your worksheet. Here's how to do it.

Cell Address of a Maximum Value
Finding the maximum value in a range of cells is easy; finding the address of the cell containing that value is a different story. Here's several ways you can get the address you need.

Character Replacement in Simple Formulas
Do you see some small rectangular boxes appearing in your formula results? It could be because Excel is substituting that box for a character it cannot display, as discussed in this tip.

Cleaning Up Lists
When you have huge amounts of data you need to check for matches, Excel may not be the best tool to use. If you can fit the data within a worksheet, however, then you can relatively easily check for matches, as described in this tip.

Combining Cell Contents
The formulas you can create in Excel can be simple or complex, but there are some basic building blocks you need to use. This tip examines one building block that allows you to combine text values to create new text values.

Combining Numbers and Text in a Cell
Most people put either text or numbers in cells, but seldom both. You can easily combine the two data types in a single cell, however. This tip shows how easy it is.

Complex Lookup Formulas
If you need to combine information in some of your cells in order to produce a result needed to, in turn, look up other information, the task can be daunting. Here's some ideas on how you can make the task more manageable.

Condensing Sequential Values to a Single Row
If you have a bunch of ZIP Codes or part numbers in a list, you may want to "condense" the list so that sequential series of numbers are shown as a range. There are a number of ways this can be accomplished.

Counting Cells According to Case
Text placed in cells can either be lowercase, uppercase, or a mixture of the two. If you want to count the cells based upon the case of the text within those cells, you'll need to resort to one of the techniques in this tip.

Counting Consecutive Negative Numbers
If you have a range of values that can be either positive or negative, you might wonder how to determine the largest consecutive sequence of negative numbers in the range. This can be done in a couple of ways, as described in this tip.

Counting Employees in Classes
Excel is very good at counting things, even when those things need to meet specific criteria. This tip shows how you can do a comparison to individual values to make a decision as to whether they should be included in your count or not.

Counting Groupings Below a Threshold
When working with some data sets, you may need to know how many groups of values in the data set have elements that fall below a certain threshold. There are several ways you can determine this count in Excel, as described in this tip.

Counting Non-Blank Cells
Need to count the number of cells in a range that are not blank? You can use the COUNTA function of a more complex formula to get the result.

Counting Only Money Winners
If a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who actually won money. Here's a couple of easy formulas to derive the desired result.

Counting Unique Values
If you have a list of values in a range of cells, you may want to know how many unique values appear in the list. The ways you can go about making that determination depend on whether the list has blanks in it or not. This tip explains your options.

Counting Wins and Losses
Need to count the number of W (win) or L (loss) characters in a range of cells? You can develop a number of formulaic approaches to getting the value you need.

Dealing with Circular References
Circular references occur when a formula includes a reference to the cell in which the formula appears. Here's how you can recognize circular references and track them down.

Dealing with Long Formulas
If your worksheet formulas seem to go on forever, here's a handy way to make them more understandable. (All you need to do is judiciously use Alt+Enter when you enter the formula.)

Deriving a Secant and Cosecant
Two rather common trigonometric functions are secants and cosecants. Excel doesn't provide functions to calculate these, but you can use a formula to derive them, as described here.

Deriving High and Low Non-Zero Values
Excel provides handy functions that allow you to determine the high and low values in a range. You may want to limit what is returned to non-zero values, which involves the use of a slightly more complex formula, as described in this tip.

Deriving Monthly Median Values
When processing huge amounts of data, it can be a challenge to figure out how to derive the aggregate values you need. This tip examines how you can derive median values, month by month, from a huge amount of data.

Determining "Highest Since" or "Lowest Since"
When compiling statistics on a collection of data points, you may want to know whether a particular value is the "highest since" or "lowest since" when compared to the preceding data in the collection. You can figure out this information with either a formula or a macro, as described in this tip.

Determining Business Quarters from Dates
Many businesses organize information according to calendar quarters, especially when it comes to fiscal information. Given a particular date, you may wonder how you can determine the quarter of the year in which that date falls. There are a number of ways you can devise formulas for such an answer.

Determining If a Value is Out of Limits
Need to figure out if a value is outside of some arbitrary limit related to a different value? There are a number of ways you can make the determination.

Determining Winners, by Category
Do you need to determine the top three values in a range of columns? The techniques discussed in this tip will come in useful.

Entering Formulas in Excel
The way you signify that you are entering a formula is to start a cell entry with an equal sign. Here is the reason why Excel expects that character.

Errors When Subtracting
When you subtract two numbers from each other, you have a certain expectation of what Excel should deliver. What if you get something that is not quite what you expected, however?

Evaluating Formulas
Need a bit of help in figuring out how Excel is evaluating a particular formula? It's easy to figure out if you use the Evaluate Formula tool.

Excluding Values from Averaging
Calculating an average of a group of numbers is easy. What if you want to exclude a couple of the numbers from the group you are averaging? Here's the tools you need to develop a formula that will give you what you want.

Extracting a Pattern from within Text
If you have a large amount of data in a worksheet and you want to extract information from the text that meets certain criteria, you have several options you can follow. This tip examines different ways you can extract the desired information.

Figuring Out the Low-Score Winner
Need to figure out the lowest score in a range of scores? Here's the formulas to get the information you need.

Filling References to Another Workbook
When you create references to cells in other workbooks, Excel, by default, makes the references absolute. This makes it difficult to automatically fill a range of cells with relative references based on the created reference. This tip explains how you can get around this default Excel behavior.

Finding Differences Between Lists
A common task faced by Excel users is to determine whether items in one list are also found in a different list. There are a couple of powerful ways you can determine this information.

Finding the Address of the Lowest Value in a Range
Uncovering the lowest value in a range is relatively easy; you can just use the MIN worksheet function. Discovering the address of the cell containing that value is a different story. This tip examines a couple of macro-based solutions as well as a worksheet formula that can return the desired information.

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 Directory Name
Need to know the directory (folder) in which a workbook has been saved? You can create a formula that will return this information within any cell of a worksheet.

Finding the First Non-Digit in a Text Value
If you have a string of text that is composed of digits and non-digits, you may want to know where the digits stop and the non-digits begin. There are a couple of ways you can determine this demarcation line, as described in this tip.

Finding the Nth Occurrence of a Character
The FIND and SEARCH functions are great for finding the initial occurrence of a character in a text string, but what if you want to find an occurrence other than the first? This tip presents a couple of ways that you can find exactly the occurrence you need.

Fitting Your Information
Excel has a few ways to size the width of columns in a worksheet.

Formatting Canadian Postal Codes
Postal codes in Canada consist of six characters, separated into two groups. This tip explains the format and then shows how you can use a formula to implement the format.

Formulas Don't Calculate as Formulas
Enter a formula (starting with an equal sign) and you may be surprised if Excel doesn't calculate the formula. Here's a good candidate for what that may happen and what you can do about it.

Generating Random Strings of Characters
If you need to generate a random sequence of characters, of a fixed length, then you'll appreciate the discussion in this tip. You'll discover both macro and non-macro ways to accomplish your goal.

Getting a Count of Unique Names
When you've got a column full of names, you may want to get a count of how many of those names are unique. You can make quick work of this task using the techniques described in this tip.

Goal Seeking
Using Excel to return values for future goals.

Hiding Columns Based on a Cell Value
Want to hide the information in a particular column based on the value stored in a specific cell in your worksheet? It's relatively easy to do if you have a macro do the work for you.

Hiding Rows Based on a Cell Value
This tip contains a macro to hide rows that contain data you don't want to see.

How Operators are Evaluated
An explanation of how operators are used in Excel.

Ignoring N/A Values in a Sum
You can use some of Excel's worksheet functions across a range or worksheets, but not all of them. One that has problems with multi-sheet use is SUMIF, as discussed in this tip. There are ways to overcome the problems this can cause, but they involve rethinking some of your formulas.

Incrementing Numeric Portions of Serial Numbers
If you use serial numbers that include both letters and numbers, you might wonder how you can increment the numeric portion to generate a list of those serial numbers. There are a couple of ways you can accomplish this task, as described in this tip.

Last Non-Zero Value in a Row
If you have a lot of values in a single row, you might want to pull the last non-zero value from that row. There are a variety of ways you can accomplish this task, as illustrated in this tip.

Listing Combinations
You can easily use the COMBIN worksheet function to determine the number of combinations that can be made from a given number of digits. However, you might want a way to enumerate all those combinations. This can be easily done using the macros presented in this tip.

Maintaining Text Formatting in a Lookup
The lookup functions provided by Excel are very powerful, but they cannot copy formatting from the source to the target cells. This tip explains why this is so.

Matching Formatting when Concatenating
You can use a couple of different methods to use formulas to put text strings together. When doing so, you may notice that the results of the concatenations may not be exactly what you expected. This tip examines one cause and provides several different ways you can get just the results you want.

Non-adjusting References in Formulas
Sometimes making sure that a reference in a formula doesn't get changed is not as simple as putting dollar signs in front of the reference's row or column. Here are several ideas on how you can make sure that the references really do stay unchanged.

Notation for Thousands and Millions
When working with very large numbers in a worksheet, you may want the numbers to appear in a shortened notation, with an indication as to whether the number represents thousands or millions. There are a couple of ways you can utilize this type of notation, as discussed in this tip.

Number of Terms in a Formula
Formulas are made up of operands, separating a series of terms that need to be acted upon by the operands. You may want to know, for some strange reason, the number of terms in a particular formula. This tip presents a user-defined function that will help you figure out the result you need.

Patterns of Numbers with a Formula
Want to create a sequential pattern using formulas? It's easy to do if you take a look at how your data repeats. This tip examines how to repeat your pattern for as many cells as you need.

Placing Formula Answers in a Comment
Excel won't allow you to directly or automatically insert the results of a formula into a cell's comment. You can, however, use a macro to place that result exactly where you want it.

Pulling Formulas from a Worksheet
The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can then easily make a copy of those formulas for documentation or analysis by applying the ideas in this tip.

Referencing the Last Six Items in a Formula
If you have a list of data in a column, you may want to determine an average of whatever the last few items are in the column. This tip examines a couple of formulas you can use to calculate the average of the last six items in a data column, even if the number of items changes over time.

Relative References within Named Ranges
Named ranges are a great boon when putting together formulas. They can make complex cell references easier and faster. This tip explains some additional ways you can refer to specific cells within a named range.

Relative Worksheet References
Copy a formula from one place to another and Excel helpfully adjusts the cell references within the formula. That is, it adjusts everything except the names of any worksheets that may be referenced in the formula. Here's how you can get around that lack of change.

Replacing Dashes with Periods
Replacing one character in a text value with another character is easy. All you need to do is use the SUBSTITUTE function, as illustrated in this tip.

Retrieving the Last Value in a Column
Sometimes you need to grab the last value entered in a column, even when you keep pushing that last value downwards by adding data to the column. There are a number of ways you can get just the data you need.

Returning Least-Significant Digits
Do you ever have a need to return just a few digits out of a number? This tip shows different formulas you can use to return the three right-most digits in a four-digit number.

Returning the Smallest Non-Zero Value
If you have a range of values that include some zero values, you may want to find out what the smallest value is within that range that isn't zero. This can be done easily with an array formula, and this tip presents two variations that are helpful in this regard.

Returning Zero when a Referenced Cell is Blank
Reference a cell in a macro, and if that cell is blank Excel normally equates that to a zero value. What if you don't want Excel to make that assumption? Here's ways you can make sure that Excel doesn't miss the distinction.

Saving Common Formulas
If you've got a formula you like to use over and over again, you can assign the formula a name, just like you can with cell ranges. When you want to reuse the formula, just reference the name.

Searching for a Value Using a Function
Searching for a value using Excel's Find tool is easy; searching for that same value using a formula or a macro is more elusive. This tip discusses ways you can find the address of a cell containing a desired value.

Segregating Numbers According To Their Sign
If you have a bunch of numbers—some positive and some negative—you may want to obtain a list of only those numbers below or above zero. There are several ways you can get this information, as described here.

Separating Names into Individual Columns
If you have a list of names in a column, and you want to separate those names into individual cells, there are several approaches you can take. This tip discusses how you can pull out various parts of the names in your list.

Shortcut for Viewing Formulas
If you need to switch between viewing formulas and viewing the results of those formulas, you'll love the keyboard shortcut introduced in this tip. It's much easier to use than the normal menu method of toggling the display.

Simulating Alt+Enter in a Formula
You can use the Alt+Enter keyboard shortcut while entering information in order to force your data onto multiple lines in a single cell. Here's how you can simulate that shortcut when concatenating cell values.

Solving Simultaneous Equations
One branch of mathematics allows you to work with what are called "simultaneous equations." Working with this type of equation can be rather advanced, but that doesn't mean you can't use Excel to perform some of the math that you need.

Starting Out Formulas
When entering a formula, the normal way to being is by typing the equal sign. There is another way to start formulas, however, that can make data entry easier when using the numeric keypad.

Stopping a Formula from Updating References
Insert or delete a column, and Excel automatically updates references within formulas that are affected by the change. If you don't want to have a reference updated by the change, then you can apply the techniques described in this tip.

Summing Absolute Values
You can easily sum a series of values in Excel, but it is not so easy to sum the absolute values of each value in a range. Here is a full discussion of the various ways you can get the desired sum.

Summing Based on Formatting in Adjacent Cells
It is easy to use Excel functions to sum values based on criteria you establish, unless those criteria involve the formatting applied to cells in adjacent columns. In that case, you need to resort to macros, such as the ones provided in this tip.

Summing Digits in a Value
Want to add up all the digits in a given value? It's a bit trickier than it may at first seem.

Summing Every Fourth Cell in a Row
Excel provides several powerful functions to calculate a sum of values, but how you put those functions to use in non-standard ways can be perplexing. For instance, how can you sum values, at a set interval, within a range of cells? This tip provides some innovative ways to accomplish the task.

Summing Only Positive Values
If you have a series of values and you want to get a total of just the values that meet a specific criteria, then you need to become acquainted with the SUMIF function. This tip shows how it can be used to sum just the positive values in a list.

Totaling Across Worksheets
Putting together formulas to sum a range of cells is fast and easy, as long as the cells are all on the same worksheet. If you want to get a sum of cells on multiple worksheets, then you need to use some special addressing techniques, as described in this tip.

Using a Numeric Portion of a Cell in a Formula
If you have a mixture of numbers and letters in a cell, you may be looking for a way to access and use the numeric portion of the value. You can do it in a couple of different ways, as described in this tip.

Using an Input Mask
When you are entering information in a worksheet, it sure would be handy to have a way to "mask" the information being entered. Excel doesn't provide input masks, but you can simulate one as described in this tip.

Using Named Formulas Across Workbooks
Using Excel, you can create names that represent ranges of cells, constant values, or even formulas. If you want to use a named formula in more than just the current workbook, you need to change the manner in which you address the formula. This tip shows you how.

Using Named Formulas or Constants
An easy way to create a name for a formula or constant value. The name can then be used in other formulas or for referencing purposes.

Viewing Formula Results
When editing information in a cell, you may need to know the result of a portion of your formula. The shortcut described in this tip will save your tons of time in figuring out such intermediate results.

Viewing Formulas versus Results
Instructing Excel to display the formulas used instead of the results.

Working In Feet and Inches
Your chosen occupation may require that you work with linear distances in feet and inches. Excel can do this, to a degree, but you need to understand what the limitations are.

 

More Information

The following are additional topics related to the subject of 'Formulas'. A bracketed number after the topic indicates how many articles are related to that subject.