Referencing A Sheet Indirectly
Q. My Excel workbook has a sheet for each month, named January, February, and so on. I also have a summary sheet that displays key calculations for a particular month. For example, one of my formulas is:
Is there any way that I can enter the month name into a cell on my summary sheet, and then have my formulas use the data for the specified sheet?
Yes. Excel's INDIRECT function was designed specifically for this sort of thing. This function accepts a text string as an argument, and then evaluates the text string to arrive at a cell or range reference. In your case, assume that cell B1 on your summary worksheet holds the month name. The following formula utilizes the INDIRECT function to create the range reference used by the SUM function:
Note that I use the ampersand operator to join the month name with the cell reference (expressed as text). Refer to the figure below. If cell B1 contains the text March, the SUM function returns the sum of the range March!F1:F10.
Delete All Input Cells, But Keep The Formulas
Your worksheet may be set up with formulas that operate on a number of input cells. Here's an easy way to clear all input values while keeping the formulas intact.
Press F5 to display the Go To dialog box, and click the Special button. In the Go To Special dialog, choose the Constants button and select Numbers. When you click OK, the nonformula numeric cells will be selected. Press Delete to delete the values. The Go To Special dialog box has many other options for selecting cells of a particular type.
Round Values To The Nearest Fraction
Q. Is it possible to round a dollar amount to the nearest 25 cents? For example, if a number appears as $1.65, I would like to convert it to $1.75. Excel's ROUND() function seems to work only with whole numbers.
Yes, you can use Excel's ROUND() function to achieve the rounding you want. The following formula, which assumes that your value is in cell A1, will do the job for you.
The formula divides the original value by .25 and then multiplies the result by .25. You can, of course, use a similar formula to round values to other fractions. For example, to round a dollar amount to the nearest nickel, simply substitute .05 for each of the two occurrences of ".25" in the preceding formula.
Avoid Error Displays In Formulas
Sometimes a formula may return an error message. Usually, you'll want to know when a formula error occurs. But now and then you may prefer to avoid the messages. You can do so by using an IF() function to check for an error.
For example, the formula below displays a blank if the division results in an error.
You can adapt this technique to any operation. The original formula serves as the argument for the ISERROR() function, and it repeats as the last argument of the IF() function. Like this:
Change Cell Values Using Paste Special
Q. I have a price list stored in a worksheet, and I need to increase all prices by 5 percent. Can I do this without reentering all the prices?
Excel provides two ways to accomplish this. The "traditional" technique goes something like this:
- Insert or find a blank column near the prices.
- In that column's first cell, enter a formula to multiply the price in that row by 1.05.
- Copy the formula down the column.
- Select and copy the entire column of formulas
- Select the original prices, and choose Edit, Paste Special.
- In the Paste Special dialog box, select Values to overwrite the original prices with the formulas' results.
- And finally, delete the column of formulas.
The other, more efficient approach also uses the Paste Special dialog box. To increase a range of values (prices, in this example) by 5 percent:
- Enter 1.05 into any blank cell.
- Select the cell and choose Edit, Copy.
- Select the range of values and choose Edit, Paste Special.
- Choose the Multiply option and click OK.
- Delete the cell that contains the 1.05.
Hiding Your Formulas
Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected. By default, every cell is locked and not hidden. But it's important to remember that these attributes have no effect unless the worksheet itself is protected.
First, to change the attributes, select the appropriate cell or range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden (or both). Unlock cells that accept user input, and lock formula and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells: The results of the formulas will be visible, but the formulas will not.
Now, to protect the worksheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog box. Make sure the Contents box is checked. You can enter a password to prevent others from unprotecting the sheet. Locked cells in a protected sheet cannot be edited, and other worksheet changes are disabled. For example, no one can insert rows or columns, change column width, or create embedded charts.
NOTE: Keep in mind that it is very easy to break the password for a protected sheet. If you are looking for real security, this is not the solution.
Counting Distinct Entries In A Range
First, let's clarify the question. We're hunting for a formula that, given the range that contains the values 100, 99, 98, 100, 98, 100, 98, would return 3. In other words, this range contains three different values, some of them repeated.
This type of counting requires an array formula. The formula below, for example, counts the number of distinct entries in the range A1:D100.
When you enter this formula, you must press Ctrl-Shift-Enter. Pressing only Enter will give you the wrong result. Excel will place brackets around the formula to remind you that you've created an array formula.
The preceding formula works fine in many cases, but it will return an error if the range contains any blank cells. The formula below (also an array formula, so input it with Ctrl-Shift-Enter) is more complex, but it will handle a range that contains a blank cell.
=SUM(IF(COUNTIF(A1:D100,A1:D100)=0, "", 1/COUNTIF(A1:D100,A1:D100)))
Force A Global Recalculation
Q. I find that sometimes my formulas do not get fully calculated. This often happens when I use custom functions created with VBA.
Microsoft has acknowledged some problems with the Excel calculation engine in some version of Excel. In order to be assured that all of your formulas have been calculated, press Ctrl-Alt-F9 to force a complete recalculation.
This key combination will also update formulas that use custom VBA functions.
Summing Times That Exceed 24 Hours
Q. I have a range of time values, but when I try to sum them, the total is never greater than 24 hours.
When you add a range that contains time values, Excel ignores the hours that exceed 24. The solution is to use a custom number format.
- Activate the cell that contains your total time
- Choose Format, Cells.
- In the Format Cells dialog box, click the Number tab.
- Choose Custom from the Category list
- Type [h]:mm into the box labeled Type.
Using brackets around the hour portion of the format string tells Excel to display hours that exceed 24 hours.
Transforming Data With Formulas
This tip describes a technique that should be in the arsenal of every Excel user. It describes how to use formulas to transform data.
The figure below shows a simple example. The text in column A consists of lower case letters. The goal is to transform these cells so they display "proper" case. This will be done by creating formulas that use Excel's PROPER function.
The steps below are specific to this example. But they can easily be adapted to other types of data transformations.
Creating the formulas
In this case, the formulas will go in column D. As you'll see, this is just a temporary location. The formula results will eventually replace the names in column A.
- Enter the following formula in cell D2:
- Copy the formula down the column to accommodate the data. In this case, the formula is copied down to cell D11. The worksheet now looks like this (the formula cells are selected, so they appear highlighted).
Copying and pasting the formula cells
In this step, the formula cells are copied, and pasted as values -- overwriting the original data in column A.
- Select the formula cells. In this case, D2:D11.
- Choose Edit - Copy
- Select the first cell in the original data column (in this case, cell A2).
- Choose Edit - Paste Special. This displays the Paste Special dialog box.
- In the Paste Special dialog box, click the Value option button. This step is critical. It pastes the results of the formulas -- not the formulas.
- Click OK.
At this point, the worksheet looks like this:
Deleting the temporary formulas
The formulas in column D are no longer necessary, so you can delete them.
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