More Topics

The following are additional topics related to the category listed above. A bracketed number after the topic indicates how many articles are related to that subject.

• Data Conversion [24]
• Date Formulas [14]
• Random Numbers [5]
• Rounding [10]
• Time Formulas [8]

Tips, Tricks, and Answers

The following articles are available. 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Automatically Numbering Rows Do you need to keep track of a row number for your data table? Excel includes some worksheet functions that make numbering your rows easy, provided you know where to place them and how to adjust the formulas. This tip explains a few of the options you can use. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Complex Lookup Formulas Not all lookup operations are straight-forward and easy. This tip discusses a more complicated lookup using concatenated string data to look up information from a table where the data is not concatenated and is not in text format. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Counting Only Money Winners Do you have a column or row that contains a formula summing a group of other cells? Do you want to count how many of those cells are greater than zero? That’s the problem this user faced, and how to go about it was unclear. This tip presents a couple of formulas you can use to count only those cells desired. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Dealing with Circular References Circular references are created when a formula includes a reference to the cell in which the formula is located. This tip provides a helpful example of a circular reference and explains how you can tell when such inconsistencies are affecting your data. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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.) Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Deriving Monthly Median Values One of the things that Excel is very good at is crunching numbers and processing huge amounts of data. This tip gives step-by-step examples of how you can process very large datasets (sixty years of daily data) to derive median values for each month during that period. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Entering Formulas in Excel The ability to enter formulas in a cell is at the heart of Excel’s usefulness. Excel provides a primary method of entering formulas (using the equal sign) and a secondary method; both are described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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? Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Finding Differences Between Lists If you have two data lists that are related to each other, you can use the techniques in this tip. For instance, you may want to compile a list of the differences between a customer list and an active customer list, resulting in a list of inactive customers. This tip explains how. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Fitting Your Information Excel has a few ways to size the width of columns in a worksheet. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Formulas Don't Calculate as Formulas You are typing along in your workbook, and you notice that a formula you enter is not being processed right by Excel. What do you do? You apply the information in this tip to ensure that the formula is no longer treated simply as text. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Getting a Count of Unique Names Do you have a list of names in which there may be some duplication? You might want to know how many unique names there are in the list. There are a couple of ways you can make the determination, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Goal Seeking Using Excel to return values for future goals. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Hiding Rows Based on a Cell Value This tip contains a macro to hide rows that contain data you don't want to see. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

How Operators are Evaluated An explanation of how operators are used in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Non-adjusting References in Formulas Insert and delete cells and Excel will routinely modify the formulas affected by those edits. You may not want the references in those formulas to be changed. This tip explains how you can create a formula that won’t be affected by the edits you routinely make. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Patterns of Numbers with a Formula Some uses of Excel may require you to create patterns of numbers. This is the case with Helen, who needed a specific pattern, but was at a loss as to how to do it with a formula. This tip explores several different ways that Helen’s pattern can be created. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Relative Worksheet References When you copy a formula from one place to another, Excel automatically adjusts the references within that formula relative to the target location of the copy. There is one big exception to this, which is references to worksheet names; these are not modified. This tip provides some ways that you can make the relative adjustments you need to make in formulas that contain worksheet references. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Returning Zero When a Referenced Cell is Blank Formulas in Excel must return some sort of value; they cannot return nothing. This can cause problems for some formulas, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Separating Names into Individual Columns You can use the Text to Columns wizard to separate names into different cells. The success you have with this, however, depends on the condition of the original names: You may end up with names in two or three columns. The formulas in this tip are great for figuring out what part of a name goes into which column so that you have a better chance of consistent data after separation. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Simulating Alt+Enter in a Formula When entering data in a cell, Alt+Enter allows you to start a new line within the current cell. In a formula you may want to create a new line, the same as if you had pressed Alt+Enter. There are two ways you can accomplish this task, both of which are explained in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Solving Simultaneous Equations Simultaneous equations are those in which there are multiple variables that need to be solved for. A full discussion of how to handle simultaneous equations in Excel is beyond the scope of ExcelTips, but the resources provided in this tip will be helpful to those who need guidance in this advanced topic. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Summing Absolute Values Excel provides functions that allow you to calculate the sum of a range of values, but what if you want to know the sum of the absolute values of the range? That becomes a little more tricky, but Excel provides several ways you can elegantly derive the desired sum. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Summing Digits in a Value Do you need to calculate a total for all the digits in a number? (Are you dying to know that for the value 123, 1+2+3 adds up to 6?) There are several ways you can get the desired total using formulas, array formulas, or user-defined functions. This tip explains all your options. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using an Input Mask Do you need to enter times into a cell, and you want to do it without the necessity of entering the colons? This tip explains how you can create an “input mask” that can help, but such ease doesn’t come without drawbacks. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using Named Formulas or Constants An easy way to name a formula or constant value. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Viewing Formula Results When working with formulas, it is sometimes helpful to see the intermediate results of each operation. You can do this easily by using the F9 shortcut key while editing the formula. This tip explains how this works and why you might want to use it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Viewing Formulas versus Results Instructing Excel to display the formulas used instead of the results. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Helpful Links

Ask an Excel Question
Make a Comment

Tips.Net Home

ExcelTips FAQ
ExcelTips Premium

Learn Access Now

Beauty Tips
Bugs and Pests Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Money Tips
Organizing Tips
Pet Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

 

RSS Feeds

 Daily Nuggets (an ExcelTip every day)

 Allen Wyatt's ExcelTips (the weekly classic)