Formulas are essential to processing data in Excel. Once you have information in your worksheet, you can use formulas to manipulate what you have entered into your cells. Learn how Excel treats formulas and calculations with the following articles to get accurate results in your worksheets.

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.

   Adding a Missing Closing Bracket
When working with large amounts of data, it is a good idea to make sure that the data all consistently follows a pattern. This makes it easier to process the data in a predictable way. This tip examines a way that you can make sure text values follow a particular desired pattern.

   Adding Dashes between Letters
When processing some text data, you may need to perform some esoteric function, such as adding dashes between letters. While this can be done with formulas, it is better to use a macro-based approach.

   Adding Ordinal Notation to Dates
Want to add an ordinal suffix to a number, as in 2nd, 3rd, or 4th? Excel doesn't provide a way to do it automatically, but the ideas presented here can be helpful in devising a way to get the desired notation.

   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.

   Adjusting Test Scores Proportionately
Teachers often grade on what is affectionately referred to as "the curve." The problem is, it can be a bit difficult to figure out how to curve the scores when it comes time to adjust them. This tip examines one grading scenario and the formulas you can use.

   Alphabetic Column Designation
Want to know the letters assigned by Excel to a particular column? Excel normally deals with column numbers, but you can translate those numbers into the corresponding alphabetic column designations.

   Applying Range Names to Formulas
If you define your named ranges after you create your formulas, you can have Excel update those formulas to reflect the newly defined names. This can be a powerful way to use those names in your worksheet.

   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.

   Averaging a Non-Contiguous Range
Figuring out how to average data that is in a contiguous range of cells is easy. When the data is spread over a group of non-contiguous cells then getting the average can be a bit more challenging. Throw into the mix the need to exclude zero values from the average and the solution can be downright elusive.

   Averaging the Last Numbers in a Column
Need to calculate a running average for the last twelve values in a constantly changing range of values? The formula discussed in this tip can make quick work of getting the average you need.

   Averaging Values for a Given Month and Year
Excel is often used to analyze data collected over time. In doing the analysis, you may want to only look at data collected during a given range of time, such as a particular month and year. Here's how to be selective with your data.

   Calculating Statistical Values on Different-Sized Subsets of Data
Discovering different ways to analyze your data can be a challenge. Here's how to work with arbitrary subsets of a large universe of data so you can get the analysis you need.

   Calculating the Distance between Points
Want to figure out how far it is between two points on the globe? If you know the points by latitude and longitude, you can calculate the distance using the techniques in this tip.

   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.

   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 are several ways you can get the address you need.

   Changing the Reference in a Named Range
Define a named range today and you may want to change the definition at some future point. It's rather easy to do, as described in this tip.

   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.

   Checking for Either of Two Text Values
Using a formula to find information in a text value is easy. Using a formula to find either of two text values within a larger text value is a bit harder. This tip examines different formulas you can use to figure out how many cells in a range contain either of two text values.

   Checking for Proper Entry of Array Formulas
Excel allows you to enter two different types of formulas in a cell: A regular formula or an array formula. If you need to make sure that a formula is entered into a cell as an array formula, you'll appreciate the ideas presented 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
Excel allows you to easily combine text together. The key is to understand and use the ampersand operator.

   Combining Numbers and Text in a Cell
There are times when it can be beneficial to combine both numbers and text in the same cell. This can be easily done using a formula, as described in this tip.

   Compiling a List of Students in a Course
Need to pull just a limited amount of information from a large list? Here are a few approaches you might be able to use with your data.

   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.

   Concatenating Values from a Variable Number of Cells
Excel makes it easy to concatenate (or combine) different values into a single cell. If you need to combine a different number of cells into a single cell, then the proposition becomes more complex. Here are several ways to approach the problem.

   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 Asterisks
For some operations and functions, Excel allows you to use wild card characters. One such character is an asterisk. What if you need to count the number of asterisks in a range of cells, however?

   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 analyzing data, you may need to distill groupings from that data. This tip examines how you can use formulas and macros to determine the number of groupings in a larger set of raw numbers.

   Counting Jobs Completed On a Date
When you store the date and time in a single cell, it can be a bit confusing to count how many cells contain a particular date. This tip examines a scenario where such counting is required and provides several ways you can get the desired count.

   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 Odds and Evens
If you have a series of values in a range of cells, you may wonder how many of those values are even and how many are odd. There are a couple of ways you can find the desired counts in Excel.

   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
Need to know a count of unique values in a range of cells? There are a couple of ways you can tackle the problem.

   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.

   Creating an Amortization Schedule
An amortization schedule is a report that shows how the outstanding balance on a loan changes with payments made over time. Usually, they are created for property mortgages but could be done for any type of loan. Here are some ideas on how you can make your own amortization schedules.

   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
When analyzing your numeric data, you may need to figure out the largest and smallest numbers in a set of values. If you don't want the smallest value to be a zero, then your analysis task becomes just a bit harder.

   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.

   Determining a Name for a Week Number
You could use Excel to collect data that is useful in your business. For instance, you might use it to collect information showing which people have reserved various weeks of your facilities. This tip explains a few ways you can find the names associated with a range of reservation weeks.

   Determining a Simple Moving Average
A moving average can be a great way to analyze a series of data points that you've collected over time. Setting up a formula to display the moving average you need can be a bit confusing, however. This tip explains how you can derive the average.

   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.

   Extracting a State and a ZIP Code
Excel is often used to process or edit data in some way. For example, you may have a bunch of addresses from which you need to extract information, such as a state and ZIP Code. This tip examines one such scenario and how you can get the information you need.

   Extracting First and Last Words
When working with text phrases stored in cells, it might be helpful to be able to extract words from the phrase. In this tip you discover easy ways to access both the first and last words of such a phrase.

   Extracting Street Numbers from an Address
Want to know how to move pieces of information contained in one cell into individual cells? This option exists in using formulas.

   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 was 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.

   Finding the Smallest Even Value
When processing data in a worksheet, you may have a need to know what the smallest (lowest) even value in a range is. You may be able to figure it out simply by looking at the values or by sorting them, but this gets harder as the list of values gets larger or if you can't sort them for some reason. You can create either a formula or a macro to determine the value you seek.

   Finding the Sum of a Sequential Integer Range
In mathematics, the sum of a range of sequential integers, starting with 1, is known as a triangular number or Gaussian Summation. Here's a simple formula you can use to determine this sum.

   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
Excel provides a great tool that is helpful in figuring out what certain variables should be in your formulas. This tip provides an example to illustrate how to use goal seeking.

   Hiding Columns Based on a Cell Value
Need to hide a given column based on the value in a particular cell? The easiest way to accomplish the task is to use a macro; several are highlighted in this tip.

   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
Operators are used in formulas to instruct Excel what to do to arrive at a result. Not all operators are evaluated in the same way, however.

   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.

   Incrementing References by Multiples when Copying Formulas
You can easily set up a formula to perform some calculation on a range of cells. When you copy that formula, the copied version may not reflect the update to the cell range in the way you need. Here's how to put together a formula that will increment properly when copied.

   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
Want to maintain the formatting used in one cell when you use formulas to reference that text in another cell? The answer is not as straightforward as you might think. This tip explores how Excel handles lookup formulas.

   Matching Formatting when Concatenating
Convert a numeric value to text and you may be surprised by how Excel displays the value. Here's a run-down on exactly what Excel does in the conversion and how you can modify the way the conversion is done.

   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 that separate a series of terms 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.

   Only Showing the Maximum of Multiple Iterations
When you recalculate a worksheet, you can determine the maximum of a range of values. Over time, as those values change, you may want to keep track of the largest value that has ever been in that range. Maintaining this maximum value can be a bit tricky, but it is possible.

   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 Results 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 a Phone Number with a Known First and Last Name
When using an Excel worksheet to store data (such as names and phone numbers), you may need a way to easily look up a phone number associated with a name. If the name is stored in two columns, it can be confusing to get to the phone number associated with those names. Here are a variety of ways you can get what you need.

   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.

   Pulling Initial Letters from a String
When working with names or a different series of words, you may need to pull the initial letters from each word in the string. There are a variety of ways you can approach this task, as discussed 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 to Cells in Other Workbooks
When you construct a formula and click on a cell in a different workbook, an absolute reference to that cell is placed in the formula. There is no way to tell Excel you want the references to be relative, but there are some things you can try to change the references.

   Relative References within Named Ranges
Excel is usually more flexible in what you can reference in formulas than is immediately apparent. This tip examines some different ways that you can reference cells relative to a named reference.

   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.

   Removing Dashes from ISBN Numbers
ISBN numbers are used to denote a unique identifier for a published book. If you remove the dashes included in an ISBN, you might end up with something Excel assumes is a numeric value. To stop Excel parsing the dashless ISBNs as numbers, apply the techniques in this tip.

   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
Need to get at the last value in a column, regardless of how many cells are used within that column? You can apply the techniques in this tip to get just the information 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
In a series of values you may need to know the smallest value that isn't a zero. There is no built-in function to do this, but you can determine it with a couple of different array formulas.

   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
It is not uncommon to reuse formulas in a variety of workbooks. If you develop some "gotta keep" formulas, here are some ideas on how you can keep them close at hand so you can use them again.

   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
Remember your number line from your early years in school? Some numbers can be below zero (negative numbers) and others above (positive numbers). If you want to separate numbers based on there sign, there are few ways you can approach the task.

   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.

   Splitting Cells by Case
Excel provides several different ways that you can split apart the contents of a cell. One way it doesn't provide is to split cells based upon the case of the text within the cell. This tip presents several formulaic and programmatic approaches to pulling apart text strings based on character case.

   Starting Out Formulas
When you enter a formula from the keyboard, Excel only knows it is a formula if you start it with an equal sign. You can also use the plus sign if you find using the equal sign bothersome.

   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 Based on Part of the Information in a Cell
Excel provides a variety of tools that allow you to perform operations on your data based upon the characteristics of that data. Here are some ways you can use those tools to calculate a sum based upon just a portion of a cell associated with the values you are summing.

   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
Need to sum a series of cells that fits some regular pattern? Here are several ways that you can get the summation that you need.

   Summing Only Positive Values
If you have a series of values and you want to get a total of just the values that meet 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.

   Summing Only the Largest Portion of a Range
Given a range of cells, you may at some time want to calculate the sum of only the largest values in that range. Here is an examination of various ways you can get the sum you need.

   Totaling Across Worksheets
Want to sum the values in the same cell on a range of worksheets? It's not as easy as summing a range on the same worksheet, but it can be done. Here's how.

   Tracking Down Invalid References
When you discover that there is an invalid reference in a workbook, you can have a bear of a time tracking down the problem. Here are some ideas on places you can look and some techniques you can use to track down the errant reference.

   Understanding Operators
At the heart of working with Excel is the process of creating formulas that calculate results based on information within a worksheet. These formulas rely upon operators to do their work. Excel provides a number of different operators you can use, as outlined in this tip.

   Using a Formula to Replace Spaces with Dashes
If you need a formula to change spaces to some other character, the SUBSTITUTE function fits the bill. Here's how to use it.

   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
You can use the naming capabilities of Excel to name both ranges and formulas. Accessing that named information in a workbook external to your current workbook can be a challenge. Here's how to approach the problem.

   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
Sometimes it is helpful to see the actual formulas in a cell, rather than the results of those formulas. Here's how to make the switch in what is displayed.

   Where Is that Text?
Looking for a formula that can return the address of a cell containing a text string? Look no further; the solution is in this tip.

   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.