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 a Statement Showing an Automatic Row Count
If you want to add a dynamic statement to a worksheet that indicates how many rows are in a data table, you might be at a loss as to which worksheet functions you should use. This tip looks at several ways you can determine the number of rows.

   Adding an Area Code
When you work with phone numbers in a worksheet, you may want to normalize those numbers so that they contain an area code. This tip examines the different ways you can perform this task easily.

   Adding Area Codes to Phone Numbers
If you keep phone numbers in an Excel worksheet, you may need a way to add an area code to the beginning of all of the numbers. This tip takes a look at the different ways you can do the addition.

   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 Rows without Changing a Cell Reference
Insert a row at the top of a range of cells, and the effects within your formulas may not match your expectations. This tip explains what happens when you insert rows in a worksheet and how your formulas are affected.

   Adding Up Tops and Bottoms
When you are working with sequenced values in a list, you’ll often want to take some action based on the top X or bottom Y values in the list. This tip takes a look at how you can sum just these selective values.

   Address of a Cell in Which a Threshold is Exceeded
If you keep a lot of data in Excel, you may be interested in figuring out when that data surpasses a threshold. This tip includes many different formulas you can use to determine such an address.

   Adjusting Formulas for Top-Added Rows
Formulas are the heart of using Excel, and formulas often refer to ranges of cells. How you insert cells into the referenced range can affect how Excel updates your formula that uses that range. This tip provides a number of ways you can make sure that Excel updates the formulas to reflect your full range.

   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 Based on Multiple Criteria
When you need to determine an average based on a very small selection of cells from a large dataset, based on multiple criteria, getting exactly what you can be challenging. This tip provides an example of multiple ways you can get just the needle you want from your data haystack.

   Breaking Up Variable-Length Part Numbers
Part numbers can often be long, made up of other component elements. Breaking up part numbers into individual components is easy if each component is a particular length. The task becomes somewhat more difficult if the components can be variable lengths. This tip presents a variety of methods of extracting variable-length component elements of a part number when those components follow a discernable pattern.

   Calculating a Geometric Standard Deviation
One of the areas in which Excel provides worksheet functions is in the arena of statistical analysis. You may want to calculate some statistics for which Excel doesn't provide built-in functions. This tip discusses one such statistic: a geometric standard deviation.

   Calculating an Average within a Date Range
Need to find an average of the values that fall within a date range? Excel provides a number of ways you can do this, relying on a number of worksheet functions.

   Calculating an IRR with Varying Interest Rates
You might wonder how you can calculate an IRR (internal rate of return) when the person repaying the loan pays different interest rates during the term of the loan. As discussed in this tip, this might not be a big concern as the IRR doesn't really take the repayment interest rate into account.

   Calculating Monthly Interest Charges
Trying to calculate how much people owe you? If you charge interest or service charges on past-due accounts, there are a myriad of ways you can calculate balances. Here's a discussion on a few ways you can try.

   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.

   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.

   Calculating Unique IDs Based on Names and Initials
Sometimes it can be tricky to figure out how to get exactly what you want from a dataset. In this tip, you discover how to use worksheet formulas to determine a unique ID for a name, plus how to do the same thing with a macro.

   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.

   Cell and Name References in COUNTIF
The second parameter of the COUNTIF function is used to specify the criteria to be used when determining what should be counted. If you want that criteria to include a cell reference, it can be confusing to make the reference work properly.

   Changing the Cycling Sequence for the F4 Cell Reference Shortcut
When editing a formula, the F4 shortcut key can be helpful. It may not, however, be helpful in all instances. This tip looks at why this could be the case.

   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.

   Checking for Duplicate Rows Based on a Range of Columns
When working with data in Excel, you might want to figure out which rows of data represent duplicates of other rows. If it takes analyzing the values in multiple columns to determine if rows are duplicates, the task can seem daunting. There are several ways you can approach the problem and find your duplicates.

   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.

   Combinations for Members in Meetings
Got a large group of people listed in a worksheet and you want to make sure that each person has met with every other person in the list? This tip explains a scenario in which you can figure out who needs to meet with whom.

   Combining Cell Contents
Excel allows you to easily combine text together. Interestingly, it provides two ways you can perform such combinations. Both methods are discussed in this tip.

   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.

   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.

   Converting from Relative to Absolute
Addresses used in a formula can be either relative or absolute. If you need to switch between the two types of addressing, here's a convenient way to do it.

   Counting Alphabetic Characters in a String
Need to find out how many times a certain letter appears in a text string? It's easy to do if you rely on the SUBSTITUTE function to do the heavy lifting.

   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 Asterisks in a Column
Excel can be used as a simple database program. If you use asterisks in a column of your database to designate ranking of each row, then you may need a way to count how many "asterisk rankings" of each type you've used. Here are several different formulas to get the results you want.

   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 Cells Containing a Formula
Cells in a worksheet can contain different types of information, such as numbers, text, and formulas. If you want to determine the number of cells in a range that contain the latter type of information (formulas), then this tip provides several techniques you can use.

   Counting Cells with Specific Characters
Excel is used by many people to hold all sorts of data, not just numbers. If you have cells that include meaningful leading characters, you may want to count the cells that contain a specific character. Here's how to do it.

   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 Names Based on Two Criteria
Need to figure out how many rows in a worksheet meet two criteria that you specify? Here's how to get the info you desire.

   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 Occurrences of Words
If you would like to determine how many instances of a particular word appear within a range of text, there are several ways you can proceed. This tip looks at a handful of formulas and a macro that can do the counting for you.

   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 are a couple of easy formulas to derive the desired result.

   Counting Precedents and Dependents
Do you need to know how many precedents or dependents there are on a worksheet? You could count them manually, or you could let a short macro derive the information for you.

   Counting Records Matching Multiple Criteria
Excel provides worksheet functions that make it easy to count things. What if you want to count records that match more than one criterion, though? Here are six easy ways you can count those records.

   Counting String Occurrences in Odd Rows
Counting the number of times text occurs within a range of cells can be relatively easy. If you need to only count occurrences in the odd rows in a range, though, things can get a bit trickier.

   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 Unique Values with Functions
Using Excel to maintain lists of information is not unusual. When working with the list you may need to determine how many unique values it contains. This tip shows you how.

   Counting Values within 10% of a Target
If you need to count the number of values that fall between a lower and upper value, then you can use a number of approaches. This tip looks at what worksheet functions you can use to get the count you want.

   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.

   Criteria-Based Counting in a Filtered Column
The filtering capabilities of Excel are excellent, providing you with great control over which records in a worksheet are displayed. If you want to perform criteria-based calculations on the records that are displayed, however, you can easily find yourself stymied. This tip focuses on how to do the counting you need based on only filtered data.

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

   Deleting Duplicate Text Values
Got a list of data from which you want to delete duplicates? There are a couple of techniques you can use to get rid of the unwanted data, as described in this tip.

   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 Antilogs
Creating math formulas is a particular strong point of Excel. Not all the functions that you may need are built directly into the program, however. Here's how you can use the existing functions of Excel to derive antilogs, which do not have functions built into the program.

   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 State from an Area Code
Want to be able to take information that is in one cell and match it to data that is contained in a table within a worksheet? Excel's VLOOKUP function makes this a snap.

   Determining a Zodiac Sign from a Birthdate
If you want to find out the Zodiac sign for a birthdate, there are a number of ways you can do it. This tip provides three different ideas you can implement to solve the task.

   Determining Combinations to Make a Total
If you have a range of cells that contain values, you may wonder which combinations of those cells should be used to meet a specific target total. This can be an interesting challenge, as described in this tip.

   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 the Used Range
You may have a need to determine the range occupied by data within a worksheet. The approach you take in devising a formula can depend heavily on the nature of your data, as described in this tip.

   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.

   Developing Reciprocal Conversion Formulas
When converting between measurement systems, you might want to use two cells for each type of measurement. Make a change in one cell and the result appears in the other, and vice-versa. This is, unfortunately, easier said than done.

   Displaying a Value and a Percentage in a Single Cell
Sometimes it can be helpful to show both a numeric value and a percentage in the same cell. This can be done through creative use of some of Excel's worksheet functions, most notably the TEXT function.

   Entering Formulas in Excel
The primary way you signify that you are entering a formula is to start a cell entry with an equal sign. The equal sign is not the only way you can start a formula, as you discover in this tip.

   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.

   Exact Formula Copies
When you copy a formula from one cell to another, Excel normally adjusts the cell references within the formula so they are relative to where the formula is copied. This tip shows a variation on copying that allows you to avoid the formula adjustments.

   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 File Names from a Path
If you have a full path designation for the location of a file on your hard drive, you may want a way for Excel to pull just the file's name from that path. There are a number of ways you can accomplish this task, using both formulas and macros.

   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 Foreign-Language Characters
If you need to analyze some text to determine if it contains accented characters, there are a couple of ways you can accomplish the task. This tip presents two methods that should work fine for most Excel users.

   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 if 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 Cells with Decreasing Cell References
AutoFill is a great feature. It can detect patterns and adjust cell contents as you drag a selection on-screen. It doesn't work in all instances, however, such as when you want the values in formulas to actually decrease. Here's why, along with what you can do about it.

   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.

   Filtering to a Standard Deviation
When you are working with large data sets, you may want to filter the information in those data sets according to various criteria. This tip looks at a couple of ways you could filter your data to values that lie within one standard deviation of the mean.

   Finding Circular References
If you have circular references in a workbook, you may see an error message appear when you first open that workbook. If you want to track down those offending circular references, Excel provides two different techniques you can use.

   Finding Odd Values Greater Than 50
If you have a special need to find cell values that meet two different criteria, where to start can be daunting. This tip can help provide a starting point.

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

   First Value Less Than or Equal to 100
If you need to evaluate a row of values to meet specific criteria, then you'll appreciate the discussion in this tip. It shows a few ways that you can determine the address of the first non-blank cell that contains a value of 100 or less.

   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.

   Formula Shows Instead of Formula Result
When you enter a formula in a cell, you expect Excel to display the result of that formula once you are completed. If this doesn't happen, there are a few things you can check to get the result you want.

   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 why that may happen and what you can do about it.

   Functions that Can Access Closed Workbooks
When creating a workbook, you can include formulas that reference data stored in other workbooks. Some functions will even allow you to reference data in another workbook that may be closed. This tip looks at which functions can access that other data, and which cannot.

   Generating a Gift Exchange List
Want to figure out how to do a gift exchange for your family or office? There are a variety of ways you can approach the task, as described in this tip.

   Generating a Unique ID Number
If you are keeping track of people or things within Excel, you may want to devise unique ID numbers you can use for those people or things. This tip examines how to go about that and provides ideas you can use when coming up with your own ID numbers.

   Generating Double-Digit Random Numbers
Normally you use the RAND and RANDBETWEEN functions to generate random numbers. What if you want to generate random numbers within a certain range but exclude some numbers in that range? Here's some ideas on how to get what you want.

   Generating Numeric Testing Data
Excel can be used to generate random testing data that you can use in any way you deem necessary. This tip provides several different formulas and a macro that you can use to get the data.

   Generating Random Door Access Codes
People often use Excel to maintain lists of information that they need to track. This tip shows several ways you can track a set of unique, random door codes in a workbook.

   Generating Random Strings
Do you need to generate strings of random characters? The ideas presented in this tip will help you do it in a hurry.

   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 Conditional Count of Cells Containing Values
Excel provides several worksheet functions that can be used to count cells containing values--"particularly numeric values. Things get a bit trickier when you want to use those functions to count cells based on what is in a different set of cells.

   Getting Rid of Unused Range Names
Excel allows you to easily create names for different ranges in your workbook. If you ever want to clean up the list of names by deleting those that are no longer used, then you'll appreciate the ideas and examples presented in this tip.

   Grabbing the Second-to-Last Value in a Column
Need to get at the next-to-last value in a column, regardless of how many cells are used within that column? This tip provides many different ways to accomplish the task, based on the characteristics of the data in the column.

   Hiding Rows Based on Two Values
It's easy to use filtering to hide rows based on the value in a cell, but how do you hide rows based on the values in two cells? Here are a couple of ideas on how you can pare down your data.

   How Many Times Does Each Name Appear in a List?
If you have a list of names in a column, and you want to know how many times those names appear in a larger list of data, then this tip is for you. It provides several ways you can determine the counts that you need.

   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.

   Identifying Digit-Only Part Numbers Excluding Special Characters
When working with data in Excel, you often need to determine if that data meets criteria that you specify. This tip examines one scenario where you may need to determine if a cell contains only digits and a few permissible characters.

   Identifying Missing Numbers in a Consecutive Series
If you have a series of consecutive numbers in a column, you may want to know if it really is consecutive. (In other words, if it is missing numbers or contains duplicates.) This tip provides a variety of ways you can quickly identify any deficiency in the series.

   Identifying Values that Don't Follow a Specific Pattern
When you store textual information in a worksheet, it can be helpful to figure out if that information follows a pattern that is necessary for your needs. This tip looks at how you can validate whether a part number follows a specific pattern or not.

   Ignoring Case in a Comparison
Do you want Excel to take the case of your text into account when it does comparisons in a formula? The IF statement ignores case, but here's a technique to get it to pay attention.

   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.

   Indirectly Referencing a Cell on a Different Worksheet
Excel includes the powerful INDIRECT function which can be used to assemble references to other cells in your workbook. Getting the function to work as you expect can be a bit tricky, though. Here are some usages and some things to look out for.

   Inserting Dashes between Letters and Numbers
If you need to add dashes between letters and numbers in a string, the work can quickly get tedious. This tip examines some ways you can make the work faster and easier.

   Inserting the Workbook Name
Do you want to insert the name of your Excel workbook into a cell? There are a number of ways you can do this, as described in this tip.

   Keeping a Max Value in a Cell
Sometimes it is helpful to have a cell contain the maximum value that has ever occurred within changing data. This tip looks at two different ways you can maintain such a value.

   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.

   Limiting a Calculated Value to a Range
If you want to limit what is returned by a formula to something between lower and upper boundaries, the solution is to make your formula just a bit more complex. This tip looks at two ways you can modify your formula to impose the resulting range you want.

   Links between Two Desktop Workbooks
If you have formulas that reference values in an external workbook and that workbook is closed, Excel may generate errors. This can be frustrating, especially since Excel is not consistent in how such external references are handled.

   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.

   Locating a Single-Occurrence Value in a Column
Given a range of cells containing values, you may have a need to find the first value in the range that is unique. This tip looks at a variety of ways you can find the desired information.

   Looking Backward through a Data Table
Sometimes you need to look backward, through the information above your formula, to find the data you need. This can be done with a variety of formulaic techniques, as described in this tip.

   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.

   Moving the House Number to Its Own Cell
Excel is great at manipulating data, but sometimes it is difficult to figure out the best way to do the manipulation. This tip examines how to separate the first portion of an address (the house number) from the original address.

   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.

   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.

   Outlining Cells Referenced in a Formula
When you are editing a formula, Excel helpfully outlines the cells referenced in the formula. If you want this capability outside of Edit mode, you are going to be out of luck. There are, however, some other ways you can easily highlight the cells referenced in a formula.

   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.

   Picking Different Random Numbers from a Range
It is not unusual to need to select two random items from a list. There are a couple of ways you can approach the task; the techniques discussed in this tip make the job quite easy.

   Producing an Array of Numbers
When working with arrays in a formula, it can be a bit confusing to understand how they work. In this tip I examine a formula that uses two arrays to produce a two-dimensional array or matrix.

   Projects in Process for Each Day in a Range of Dates
If you use Excel to track projects on which you are working, you may want to determine how many projects were in-process on a particular date. This tip shares a simple formula you can use to make the determination.

   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.

   Putting Amounts in Month Columns
Sometimes putting together a formula can be a challenge. Part of the process, though, is examining the data with which you can work and then craft the formula to fit that data. This tip examines a simple formula that can be used to pull information from a single column into multiple columns, based on a month.

   Quickly Adding Formulas Referencing Multiple Worksheets
When you need to pull information from a lot of different worksheets into a single worksheet, it can be baffling to figure out how to do it easily. This tip covers two ways you can approach the problem and get the information you need.

   Randomly Assigning Names to Items
If you need to randomly match up items in two lists, there are a variety of techniques you can use. Here are a couple of good approaches.

   Referencing Every Third External Cell
When you enter references to cells in a worksheet, using the Fill Handle to propagate the formulas to other cells can be a real timesaver. This doesn't work well, though, if the references are to cells external to the current workbook. This tip looks at several different ways you can use formulas (and even Find and Replace) to reference the proper external cells.

   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.

   Referring to the Last Cell
It is not unusual to use worksheets to collect information over time. As you keep adding information to the worksheet, you may need a reliable way to refer, in your formulas, to information in the last row of the worksheet. Here are some ways you can approach the need.

   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.

   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.

   Removing Duplicates at a Reduced Precision Level
The precision of numeric values you display in a worksheet can be less than what is actually maintained by Excel. This can affect how you use some of Excel's tools, such as Remove Duplicates. This tip shows how to use the tool successfully at a lower precision than what Excel internally maintains.

   Removing the Last Digit in a Number
If you have a long numeric value in a cell, you may have a need to remove the last digit of that value. You can do so using either a really short formula or by using a macro. This tip covers both approaches.

   Reordering Last Name and First Name
If you've got a list of names in a column, you may want to change the order of each name. For instance, the name have the last name first, and you want to reorder the names so that they are in proper order. Here's how to do it.

   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 a Weight and a Date
If you have two columns containing dates and weights from those dates, you may want to pick a date associated with a given weight. It's easy to do using just a few Excel worksheet functions.

   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 Minimum of Integers of a Range
If you have a range of numbers that contain both integers and decimal numbers, you may have a need to determine the minimum of just the integer values. This tip focuses on three different approaches, none of which require the use of macros.

   Returning the Rightmost Value in a Row
Do you need to figure out the rightmost value within a row in which not all cells may contain values? This tip provides a couple of very simple formulas you can use to get at that particular value.

   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.

   Reversing Integer Values
Do you need to reverse a series of integer values, such as 5 becomes 1, 4 becomes 2, etc.? There are several ways you can approach this task, each showing the flexibility of working with Excel.

   Saving Common Formulas
Got some formulas you slaved over and want to use in lots of workbooks? This tip presents some helpful ideas on how you can save those formulas for easy recall later.

   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 their sign, there are a few ways you can approach the task.

   Selective Summing
If you want to add up the contents of a range of cells based on what is contained in a different range of cells, you need either the SUMIF or SUMIFS functions. Here's how you can do your own selective summing by using these handy worksheet functions.

   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.

   Shortening ZIP Codes
US ZIP Codes can be of two varieties: five-digits or nine-digits. Here's how to convert longer ZIP Codes to the shorter variety.

   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 a Quadratic Equation
One of the staples of high school algebra classes is the quadratic equation. If you need to solve such equations in Excel, you'll appreciate the information in this tip.

   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.

   Strange Formula Conversions
When you are getting the hang of how to put together formulas in Excel, you might run into a situation where you open a workbook and the formulas all look very strange. This tip looks at the cause of that situation and what you can do about it.

   Strange Value Results in a Cell
When Excel performs a calculation, the results you see in an unformatted cell may cause a bit of concern. This tip explains why you may see the concerning results and what you can do about them.

   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 a Control Cell
When analyzing data, you may have a need to calculate a sum based on just part of a particular cell. This tip examines three ways you can approach such a task.

   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 Cells Using a Particular Background Color
Do you need to total all the cells that are a particular color, such as yellow? This tip looks at three different ways you can get the sum you need.

   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 Filled Cells
If you have a column of data that contains color-coded cells, you may want a way to sum values in the column based on the presence of the color or lack thereof. This tip examines a couple of ways you can get the desired results.

   Summing Only Cells Containing Formulas
If you want to figure out the sum of all cells that contain formulas, there are a couple of ways you can go about it. This tip looks at several approaches to getting the sum 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 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.

   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.

   Summing When the First Character Matches a Value
Summing data is a common need in Excel. Summing lots of data based on a condition that needs to be met can be a bit more challenging, but it can be done. Here's one specific example.

   Throwing Out the Lowest Score
Want to add up a bunch of scores, without including the lowest one in the bunch? You can make a small change to your formula to get the desired result.

   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.

   Tracing Errors
Sometimes it can be confusing to figure out the source of an error that is displayed in your worksheet. Excel provides a quick tool that can help you get closer to the error's source.

   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.

   Transposing and Linking
Sometimes it is helpful to look at data that is rotated 90 degrees. Excel allows you to use Paste Special to transpose data, but this tip looks at how your transposed data can be dynamically linked to the original data.

   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.

   Understanding Scope for Named Ranges
When you add a named range to a worksheet, you can specify if you want that named range to apply to the workbook or only to the current worksheet. This tip looks at the meaning of this setting.

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

   Values Beginning with a Specific Letter or Digit
Excel can be used to store both numeric values and text values. If you want to examine a range of values and return those that begin with a specific character, there are several ways you can approach the task, as described in this tip.

   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 with Imperial Linear Distances
Excel works with decimal values very easily. It is more difficult for the program to work with non-decimal values, such as those used for linear distances (miles, feet, etc.). Here's a way you can approach the task of working with such values.