Frequently Asked Questions About Microsoft ExcelYour most nagging questions about the world's most popular spreadsheet: Answered, at long last.
Microsoft Excel reigns supreme in the world of spreadsheets, its competition having all but died off long ago. But despite its immense popularity, users still struggle with various, seemingly simple aspects of the program because Microsoft has turned a blind eye to many of Excel's shortcomings. As a result, certain questions pop up again and again.
If you use Excel frequently, bookmark this page. In this article, I've gathered the Excel questions our readers ask most frequently, categorized them, and tracked down their answers. The solutions in this FAQ are geared toward Excel 97 and 2000, but most of them also apply to earlier versions.
1. When I enter a value, it appears with two decimal places. For example, when I enter 154 it shows up as 1.54. What's wrong?
Somehow Excel's fixed-decimal mode was turned on. To return to normal, select Tools, Options to display the Options dialog box. Then click the Edit tab and remove the check mark from the "Fixed decimal" option. Of course, this feature can be useful when entering some types of data, but most of the time, you'll want to keep the fixed-decimal mode turned off.
2. Can I change the color of the worksheet tabs in my workbook?
In a word, no. It would certainly be helpful to be able to color-code your worksheet tabs. For some reason, Microsoft hasn't implemented this feature, which has been available in 1-2-3 and Quattro Pro for quite a while.
3. I saved my workbook with a password, but Excel doesn't recognize it and won't let me open the file. Am I out of luck?
First, remember that passwords are case sensitive. So if you originally entered your password as Xyzzy, typing xyzzy won't work. If you know you're entering the password correctly, then it's time to start looking for a password recovery utility. Several utilities exist, and none of them are free. Do an Internet search for "Excel password recovery," and you'll find several products that will come to the rescue. By the way, the fact that these products exist might raise some alarms for the security-minded. Bottom line? Excel password protection isn't as secure as you might expect.
4. How can I increase the number of rows or columns in a worksheet?
In Excel, every workbook has 255 columns and 65,526 rows. These values are fixed and cannot be changed. If you need more rows, you might want to consider using a database (such as Access) or another spreadsheet (Quattro Pro can handle a million rows) for data storage. In most cases, you probably don't need to work with all of your data at once. You'll usually need only a subset of your data. You can use Excel's Data, Get External Data command to query the database and bring in only the records you need.
Formulas and Functions
5. Is there a function that returns the name of the worksheet?
Excel's CELL function comes close. The following formula displays the workbook's full path along with the worksheet name:
For example, this formula might return something like:
Returning only the sheet name requires a more complex formula:
=MID(CELL("filename"), FIND("]",CELL("filename"))+1, LEN(CELL("filename"))-FIND("]", CELL("filename")))
6. 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. Then 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. Then select the range of values and choose Edit, Paste Special. Choose the Multiply option and click OK. Then delete the cell that contains the 1.05.
7. I've created some clever formulas, and I don't want anyone else to see them. Is it possible to hide the formulas but display the results?
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.
8. Can I write a formula that returns the number of distinct entries in a range?
First, let's clarify the question. We're hunting for a formula that, given the range 100, 99, 98, 100, 98, 100, 98, would return 3. 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)))
9. When I open a workbook, Excel asks if I want to update the links. I've looked everywhere, and I can't find any links in my formulas!
I've never known Excel to be wrong about identifying links, so there's an excellent chance your workbook does contain one or more links--but they are probably not formula links.
- If you have a chart in your workbook, click each data series in the chart and examine the Series formula in the formula bar. If the formula refers to another workbook, you've identified the link. To eliminate it, move the chart's data into the current workbook and recreate your chart.
- If your workbook contains any dialog sheets, select each object in each dialog box and examine the formula bar. If any object contains a reference to another workbook, edit or delete that reference.
If these two approaches don't solve your problem, follow these steps:
- Select Edit, Links. (In some cases, this command is not available. If you can't select it, skip to step 4.) The Links dialog box will appear.
- Click the Change Source button and change the link to the active file.
- Select Insert, Name, Define. Scroll down the list in the Define Name dialog box and examine the "Refers to" box. Delete names that refer to another workbook or that contain an erroneous reference (such as #REF!). This is the most common cause of "phantom links."
- Save your workbook. When you reopen it, Excel won't ask you to update links.
10. How can I make text in a cell display in multiple lines?
When entering text into the cell, press Alt-Enter to insert a line break. When you do so, Excel will automatically apply text wrapping to the cell.
To reformat existing cells so they sport wrapped text, select the cells and then choose Format, Cells. On the Alignment tab, select "Wrap text," and click OK.
11. I find that sometimes my formulas do not get fully calculated. This often happens when I use custom functions created with Visual Basic.
Microsoft has acknowledged some problems with the Excel calculation engine. In order to be assured that all of your formulas have been calculated, press Ctrl-Alt-F9 to force a complete recalculation.
Dates and Times
12. How can I calculate the difference between two dates?
Excel stores dates as serial numbers. The number 1 represents January 1, 1900, the number 2 represents January 2, 1900, and so on. Formatting these numbers using a date format causes them to appear as actual dates. Therefore, if you have dates stored in two cells, you can simply create a formula that subtracts one from the other to get the number of intervening days. You'll want to make sure that the formula cell is formatted as a number, not a date.
You might also find the DATEDIF function useful. This function, which was not documented prior to Excel 2000, returns the difference between two dates, expressed in years, months, or days. You might use the DATEDIF function to calculate how many months the payment on an invoice is overdue or to determine a person's age when you know their birth date.
Excel's DATEDIF function takes three arguments. Its syntax is:
In the syntax, start_date is a date or reference to a date, end_date is a date or reference to a date, and units is a one- or two-digit string (in double quotes) specifying the units for the difference between the two dates. Acceptable values for the units argument are shown below.
- y returns the number of full years in the period.
- m returns the number of full months in the period.
- d returns the number of full days in the period.
- md returns the number of full days in excess of the last full month.
- ym returns the number of full months in excess of the last full year.
- yd returns the number of full days in excess of the last full year.
For example, assume cells A1 and B1 contain dates. The formula below returns the number of full years between the dates (useful for calculating a person's age):
The formula below calculates the number of full months between the two dates:
13. 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, and then choose Format, Cells. In the Format Cells dialog box, click the Number tab. Choose Custom from the Category list, and 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.
14. I have a worksheet that shows total hours and minutes worked, along with the hourly pay rate. When I multiply these vales, I don't get the result I'm looking for. What's wrong?
You need to multiply the result by 24, for the number of hours in a day. If cell A1 contains the number of hours worked (for example, 16:45, for 16 hours and 45 minutes) and cell B1 has the hourly rate, the formula below will calculate the total wages:
Make sure the cell that contains the formula is formatted as a number, not a time.
15. Is it possible to determine the day of the week for a particular date?
Yes, and Excel offers several ways to do so. Assume that cell A1 contains a date value. The formula below uses the WEEKDAY function, which returns an integer between 1 and 7 (1 for Sunday, 2 for Monday, and so on).
If you'd prefer to see words rather than integers, modify the formula as follows:
=CHOOSE(WEEKDAY(A1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
Another approach is to change the number format for the date cell. Activate the cell that contains your date, and then choose Format, Cells. In the Format Cells dialog box, click the Number tab. Choose Custom from the Category list, and type a custom number format string into the box labeled Type. The trick here is to use dddd as part of the format string. For example, a format string of dddd mmmm d, yyyy will display the date and the day of the week, like this: Thursday November 23, 2000.
16. I need to determine the difference between two times. If the difference is positive, it works fine. But Excel doesn't want to display negative time values. Why not?
Excel stores dates and times as numeric values, so it should be possible to add or subtract one from the other. The problem occurs if you have a workbook containing only times and no dates. As you discovered, subtracting one time from another doesn't always work. Negative time values appear as a series of pound signs, even though you've assigned the [h]:mm format to the cells. By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date-time combination that falls before this date, which is invalid.
The solution is to use the 1904 date system. Select Tools, Options. In the Options dialog box, click the Calculation tab and check the 1904 date system option to change the starting date to January 2, 1904. Your negative times will now be displayed correctly.
If you use the 1904 date system, be careful when linking to date cells in other workbooks. If the linked workbook uses the 1900 date system, the dates retrieved by the links will be incorrect.
17. How can I enter the current date into a cell so it doesn't change from day to day? When I use the NOW function, it always shows the current date.
The easiest way to enter the current date into a cell is to use the Ctrl-; key combination (press the Ctrl key and type a semicolon). Similarly, you can enter the current time by pressing Ctrl-Shift-; (press the Ctrl and Shift keys and type a semicolon). Both of these key combinations enter the information as a volatile value, not as a formula.
Charts and Printing
18. How can I print a workbook's full path in the header or footer? The Page Setup dialog box doesn't seem to offer the option.
For whatever reason, Microsoft continues to ignore what must be thousands of requests for this feature. Although Microsoft Word offers this feature, Excel offers no direct way to print a workbook's full path in the header or footer. The only solution is to create a macro. The technique described below works with Excel 97 and later.
In Excel, press Alt-F11 to activate the Visual Basic editor. In the Project window, double-click the project that corresponds to your workbook. The project list will expand to show several objects. Double-click the item labeled Microsoft Excel Objects, and then double-click the object labeled ThisWorkbook. Enter the following three lines of VBA code into the code module for the ThisWorkbook object (usually in the right pane of the window you're seeing at this point).
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = ThisWorkbook.FullName
After inserting the code, press Alt-Q to return to Excel.
This procedure will be executed before you print or preview your workbook. It simply inserts the workbook's path into the left header position. If you prefer to put the path in a different position, substitute any of the following for LeftHeader: CenterHeader, RightHeader, LeftFooter, CenterFooter, or RightFooter.
19. How can I save a chart as a GIF file?
You can save your worksheet as an HTML file, and Excel will automatically convert any charts to GIF files.
If that seems like overkill, you can write a simple macro that will do the job. Press Alt-F11 to activate the Visual Basic editor. Select your workbook in the Projects window, and choose Insert, Module to insert a new VBA module. Then type the following four-line procedure into the module:
Sub SaveChartAsGIF ()
Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif"
ActiveChart.Export FileName:=Fname, FilterName:="GIF"
After the macro is entered, reactivate Excel and click the chart to be saved. Press Alt-F8 to display the Macro dialog box. Select the SaveChartAsGIF macro and click Run. The procedure uses the chart's name as the GIF file name, and the file is stored in the same directory as the workbook.
This simple macro does no error checking, so it will generate an error if a chart is not selected or if the workbook has not been saved.
John Walkenbach is a PC World contributing editor and writes the magazine's monthly Spreadsheet Tips column.