Display Text And A Value In One Cell
Did you know that you could combine text and values in a single cell?
For example, assume cell A12 contains the value 1435. Enter the following formula into another cell:
The formula cell will display: "Total: 1435."
The ampersand is a concatenation operator that joins the text with the contents of cell A12.
Applying a number format to the cell containing the formula has no effect, because the cell contains text, not a value. As a work-around, modify the formula to use the TEXT function (the second argument for the TEXT function consists of a standard Excel number-format string).
This formula will display "Total: $1,435.00."
Here's another example formula that uses the NOW function to display some text along with the current date and time:
="Report printed on "&TEXT(NOW(),"mmmm d, yyyy at h:mm AM/PM")
Automatic List Numbering
It's fairly easy to create a formula that generates consecutively number items in nonconsecutive cells. Refer to the figure below.
Column A consists of formulas that refer to column B. The formula in cell A1 is:
This formula, which is copied down to the other cells in column A, displays the next consecutive item number if the corresponding cell in column B is not empty. If the cell in column B is empty, the formula displays nothing.
As items are added or deleted from column B, the numbering updates automatically.
Calculate The Day Of The Year And Days Remaining
If you've ever had to figure out which of the year's 365 days a particular date falls on, or how many days remain in the year, you've probably found that Excel lacks functions to perform the calculation. But you can create formulas to do the job.
The formula below returns the day of the year for a date in cell A1:
Note: Excel automatically formats the cell as a date, so change the number format to another option (like General).
To calculate the number of days remaining in the year (assuming that the date is in cell A1), use the following formula:
Rounding To “n” Significant Digits
Excel includes three functions (ROUND, ROUNDUP, and ROUNDDOWN) that round values to a specified number of digits. In some cases, however, you may need to round a value to a specified number of significant digits.
For example, you might want to express the value 1,432,187 in terms of two significant digits (that is, as 1,400,000). Here's an elegant solution. The formula below rounds the value in cell A1 to the number of significant digits specified in cell A2:
Working With Pre-1900 Dates
A companion file is available: Click here to download
In the eyes of Excel, the world began on January 1, 1900. Excel is not capable of working with dates earlier than that.
People who use Excel to store historical information often need to work with pre-1900 dates. The only way to create a date such as July 4, 1776, in Excel is to enter it into a cell and have the program interpret it as text. Unfortunately, you can't manipulate dates stored as text -- if you want to alter their formatting, for example, or if you need to calculate the day of the week they fell on.
To address this problem, I created an add-in (for Excel 97 or later versions) called Extended Date Functions. With this add-in installed, you'll have access to eight new worksheet functions that let you work with dates in any year from 0100 through 9999.
Note: Be careful if you plan to insert dates that occurred before 1752. Differences between the historical American, British, Gregorian, and Julian calendars can result in inaccurate computations.
Using Data Validation To Check For Repeated Values
Sometimes you just don't want data to repeat itself. On an order form or an inventory sheet, for instance, you may not want a part number entered in one cell to repeat in another cell. You can use Excel's Data Validation feature to to prevent a value from appearing more than once in a range.
In the example below, the range A2:A20 requires unique part numbers. If the user enters a number that already exists, a message box pops up and asks for a different one.
To create this type of message box for your worksheet:
Select the cells for which you need to punch in unique entries (here, the correct range to select is A2:A20).
Choose Data, Validation and click the Settings tab.
Choose Custom from the Allow drop-down list. The Custom option requires a logical formula that will return either "True" or "False." This example requires a formula that will return "True" only if the content of the cell does not match one that already exists in the range. The COUNTIF function will do the job. Type the following formula into the Formula field:
This formula counts the number of cells in range A2:A20 that contain the same value that appears in cell A2. If the count is 1, the formula returns "True"; otherwise, it returns "False." Notice that the first argument for COUNTIF is an absolute reference to the entire validation range. In contrast, the second argument is a relative reference to the upper left cell in the validation range (it will be adjusted for each of the other cells in the range).
Next, to create the warning that appears in a pop-up message box when a duplicate value is entered into the selected range, click the Error Alert tab in the Data Validation dialog box. For the Style, select Stop (this option rejects existing values). Enter a title for the message box (such as Duplicate Data) and type your error message.
Click OK and try it out.
You'll find that you can enter any data into the validation range, but if you type an entry that already exists, you'll get the warning message. Click Retry to edit the cell's contents or choose Cancel to clear the cell.
While Data Validation is a useful feature, it contains a potentially serious design flaw. If you copy a cell and paste it to a cell that uses the feature, the Data Validation rules are wiped out. This problem also applies to cells that use Conditional Formatting. You'll need to keep this in mind when you're cutting and pasting in mission-critical applications.
Sum The Largest Values In A Range
Q. I need to calculate the sum of the three largest values in a range of 100 cells. The range isn’t sorted, so I can’t use a SUM function. Do you have any suggestions about how I could handle this problem?
Excel’s LARGE function returns the nth-largest value in a range, in which n is the function’s second argument. You need a formula that calls the LARGE function three times and then sums the results. The following formula, which assumes the numbers are located in the range A1:A100, will do the job:
=LARGE(A1:A100,1)+ LARGE(A1:A100,2)+ LARGE(A1:A100,3)
Another approach is to use an array formula like this one:
The formula first passes an array of three values to the LARGE function, and then uses the SUM function to add the values returned by the LARGE function. Notice that the values 1 through 3 are enclosed in brackets rather than parentheses. After typing an array formula, press Ctrl-Shift-Enter instead of Enter.
Formulas of this type can become unwieldy as n gets larger. For example, to sum the top 30 values in a range, a formula must contain a list of integers from 1 to 30. Here is a more general version of the array formula:
This formula uses the ROW function to generate a series of integers between 1 and 30, and uses this array as the second argument for the LARGE function. To sum a different quantity of numbers, just change the 30 to the desired number.
Count Autofiltered Rows
A companion file is available: Click here to downloadQ. When I use Excel's AutoFiltering, the status bar displays the number of qualifying rows. But for no apparent reason, that number often vanishes. How do I keep this number visible while I work?
AutoFiltering a list hides rows that don't meet your filter criteria. After applying filtering criteria, Excel shows the record count on the status bar--but this value disappears when the sheet is calculated.
To display a permanent count of the visible rows in an AutoFiltered list, create a formula using Excel's SUBTOTAL function. The first argument for SUBTOTAL specifies the type of operation (an argument of 2 displays a count of the visible cells in a range).
The figure below shows a list in rows 6 through 3006. The formula in cell D3 is:
The formula counts the number of visible cells in the range (minus the header row). Apply different filtering criteria, and the formula updates to show the new count. The SUBTOTAL function only works for AutoFiltering and outlining. If you hide rows manually, it won't return the correct result.
Perform Two-Way Table Lookups
All spreadsheets support lookup functions, tools that return a value from a table by looking up another value in the table. An income tax table is a good example. You can write a formula that uses the VLOOKUP function to determine the tax rate for a given income amount.
The lookup functions in Excel are only appropriate for one-way lookups, however. If you need to perform a two-way lookup, you'll need more than the standard functions. The figure below shows a simple example.
The formula in cell H4 looks up the entries in cells H2 and H3 and then returns the corresponding value from the table. The formula in H4 is:
=INDEX(A1:E14, MATCH(H2,A1:A14,0), MATCH(H3,A1:E1,0)).
The formula uses the INDEX function, with three arguments. The first is the entire table range (A1:A14). The second uses the MATCH function to return the offset of the desired month in column A. The third argument uses the MATCH function to return the offset of the desired product in row 1.
You may prefer to take advantage of Excel's natural-language formulas. For example, enter the following formula to return Sprocket sales for June:
If natural-language formulas aren't working, select Tools, Options, click the Calculation tab, and place a check mark next to "Accept labels in formulas." Be aware that using natural language formulas is not 100% reliable!
Change The Formatting Of Your Subtotal Rows
Excel's Data, Subtotals command inserts subtotal formulas into a list. This is a very useful command, but it's often difficult to identify the subtotal rows.
You may want to make the subtotal rows stand out by applying special formatting. However, this can't be done by any of Excel's autoformats. Use the outline controls on the left side of the workbook to collapse the outline so only the subtotal rows are visible. Press F5, select Visible Cells Only, and click OK. Then apply formatting to the selected cells.
When you expand the outline, only the subtotal rows will have the formatting you applied.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks