| Home > About PCs > Excel > Basic tips |
This is a little habit you can cultivate that tends to prevent confusion and save time.
Always type in your Excel formulas in lowercase.
When you press Enter, Excel parses the formula you just typed to see if it computes, and all function names and cell references Excel successfully recognizes will be converted to uppercase. This way, if you ever type in a formula and get an error, you can immediately begin troubleshooting it by re-selecting the cell and scanning the formula text for unconverted lowercase, which is often the result of a simple typo in a function name.*
There's an exception. Certain Excel functions and operators use one or more string constants, a literal string of characters surrounded by quotes, which the formula will either compare to cell contents, or add to cell contents. Excel doesn't change capitalization of string constants, so when a string constant needs to contain uppercase characters, you'll have to type them that way inside the quotes.
Some example formulas with string constants:
| As you would type them | As viewed after parsing | Formula type |
|
=if(e5="Taxable",d5*.05,0) =countif(a2:a11,"Eastern") =b5&" AND "&c5 |
=IF(E5="Taxable",D5*0.05,0) =COUNTIF(A2:A11,"Eastern") =B5&" AND "&C5 |
Conditional calculation Conditional consolidation Concatenation operator (&) |
This issue is all about spreadsheet references and dependencies, and I'm going to lay it all out for you.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Description | Unit price | Quantity | Ext price |
| 2 | Widget type A | 19.95 | 1 | 19.95 |
| 3 | Widget type B | 9.95 | 2 | 19.90 |
| 4 | Widget type C | 17.95 | 1 | 17.95 |
| 5 | ||||
| 6 | Subtotal | 57.80 | ||
| 7 | Tax | 2.89 | ||
| 8 | Amt due | $60.69 |
The table at right represents a very simple invoice worksheet, with formulas in the highlighted cells. This should be a familiar concept to anybody who's ever used mail order. It has three rows for individual billable items, with quantities and prices, a subtotal cell, and then cells for sales tax and total amount due. Most Excel users would know how to do this.¹ If you change the line items on the invoice, of course, the subtotal, tax, and total cells will recalculate.
In Excel terms, the extended price cells are dependent on the unit price and quantity cells in their individual rows, the subtotal cell is dependent on the extended price cells, the tax cell is dependent on the subtotal cell, and the amount due cell is dependent on the subtotal and tax cells. The formulas in those cells contain references which define what cells they depend on.
In any Excel file with formulas, Excel keeps track of the tree structure of these dependency connections, and refers to that tree structure whenever it recalculates. It starts by recalculating the most senior parent cells with formulas (extended price in the example) and follows the dependency chains to recalculate all the results.²
Now we come to the crux of the biscuit, as Frank Zappa put it long ago. If you create a chain of dependency links that closes a circle, then there's no logical way for Excel to figure out where to start.
This is when you will see the CIRC/Circular indicator in your Excel status bar. In Excel 2002 and 2003 there's also a floating toolbar that appears (pictured) with tools to help you resolve the circular references. Depending on the nature of the circular references, Excel may make some assumptions and still attempt to recalculate. If things get confusing enough, Excel may simply refuse to recalculate all or parts of the file.³
People seem to get into this situation when they're not even aware of circular references as a potential problem, usually while doing some sort of financial model such as a budget. When they're adding formulas, they think they can grab input data from any direction, up down left or right, and when the Circular Reference toolbar pops up, they mutter an imprecation, click the X button, and try to just keep going. A while later they notice part of their file doesn't recalculate any more, and maybe they call tech support and say something like "Excel's broken, fix it."
"Begin at the beginning," the King said gravely, "and go on till you come to the end: then stop." (Lewis Carroll, Alice's Adventures in Wonderland)
To avoid circular references, start with a plan. Lay out your information so that your formulas generally depend on data located above them in the worksheet, so that recalculation proceeds through the worksheet from top to bottom. For some files you may decide you want to have your calculations run from left to right instead of top to bottom. In the little invoice example at the top of this topic, calculations on each line-item row go left to right, and the rest of the calculations go top to bottom.
If you end up with circular references anyway, resolve them. In Excel 2002 and 2003 the floating toolbar can help you lots with this; the drop list will actually give you a list of cells involved in the selected cell's circular reference chain. Sometimes you may only need to replace a simple cell reference with the text of the calculation formula present in the referenced cell.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Code | Amount |
| 2 | Eastern | A | $44.01 |
| 3 | Central | B | $199.22 |
| 4 | Western | B | $977.81 |
| 5 | Central | B | $932.25 |
Excel has some limited database features. You can maintain a simple list of data with first-row column headings for the different fields, and up to a few thousand records. (List in Excel jargon is approximately the same idea as table in a database manager.)
There are three rules for Excel lists you need to follow, for the tools on the Data menu to work reliably:
If your list follows these rules, for example, when you sort, the sort-key menu will show your own column labels that you picked as the choices, rather than "Column A" and so forth, and so will the field-insert menus during a Word mail merge.
There's a way to tell if you are following the three rules correctly. When you use features like Data Sort or Data Subtotals, all you need to do is click on a cell inside the list first, and as soon as you do Data, Sort, Excel should correctly find and select the entire list range for you, including the column labels row. Using this technique, rather than selecting the whole list manually, tends to prevent problems like accidentally leaving part of a list out of a sort, and thereby trashing your data.
My Excel KB articles page links to articles about Excel databases.
This is a handy trick for neatly printing out Excel databases without a lot of fiddling, and it often works well for things like budgets too, if they don't use too many columns.
When it's time to print, go to File, Page Setup, Page tab, Scaling section. Click on the Fit to radio button, and set the spinners so they read 1 page(s) wide by 999 page(s) tall. You can type in the 1 and the 999. You can use this in combination with either Portrait or Landscape orientation settings on the same tab.
This causes Excel to figure out on its own what Zoom setting to use, to make the width or columns of your content exactly fit onto the width of the printed page. Excel will then use as many pages as necessary to output the vertical extent or rows of your content. That "999 page(s) tall" setting just lets Excel use as many pages as it needs. (It wouldn't print 999 pages unless you actually had that much data, which would be circa 50,000 rows.)
This trick will often let you completely avoid the finicky process of tweaking column widths and cell contents, in an effort to get your unscaled content to fit onto the printed page. You also tend to be able to read your content on the screen in a bigger font, even if Excel has to print rather teeny to make things fit.
After you make this Fit to setting, you may want to check Print Preview before you actually print, or maybe print out just the first page (using File, Print) to make sure the printed text isn't going to be too small to read. If it's too small in Portrait orientation, just switching to Landscape with the same settings may make it big enough.*
A man's got to know his limitations. (Harry Callahan, Magnum Force, 1973)
Excel is not a database manager. Excel's primary design function is numerical modeling: budgets, for example. I think sometimes people look at Microsoft Access—which is a database manager—get freaked out by the eight zillion different properties you can set, and try to use something they feel like they understand.
Excel has no relational capability, something certain kinds of databases need. If you don't know what relational means in this context, you have lots of company among Excel users, including advanced ones. For an easier-than-usual explanation of this idea, see the normalization section of my database tips page. Access is a relational database manager.
There's a limit to how much data you can cram into an Excel file. Some people tend to find this out the hard way. When the file size of your Excel workbook database passes 5-10MB, expect trouble. Access may have limits to how many records it can handle, but if so they're probably up in the millions somewhere.
Recent Excel versions are limited to 65,536 rows and 256 columns* per sheet tab, columns A through IV, and in theory those limits would apply to Excel lists as well. In practice, usually your Excel list will hit the 5-10MB file size limit before you're confronted by those rows-and-columns limits.
To look up other fundamental Excel limitations, in most versions I believe you can use keyword search in Excel Help on the keyword limitations (Excel 2003: specifications). This would include things like the maximum number of characters allowed in a single cell, maximum number of sheet tabs in a workbook, and maximum function nesting level in formulas (seven).
Too much data is one of the common causes of corrupted Excel workbook files. A corrupted or damaged Excel file may not open at all, or may open but misbehave in some way after it's open. Take my word for it, corrupted Excel workbook files can do a lot of strange things.
Another common damaged-file scenario is when a big workbook is heavily edited and modified for a long period of time. There's nothing wrong with using Excel to accumulate data, but if you can, cut it off a quarter at a time. In other words, accumulate data for first quarter in one Excel workbook file, and then on April first start over with a fresh workbook file. See the templates section below for more on the best way to do this.
There are ways to try to recover data from damaged workbook files, including dozens of third-party shareware repair utilities, which can sometimes recover data when nothing else can. If you do a Google search on excel file repair you will find lots of them. Some damaged workbooks can't be recovered by any means.
There are recovery methods you can try within Excel first: see the damaged files section of my Excel KB articles page for details. You'll find links to three version-specific Knowledge Base articles on techniques for recovering damaged files, some particular suggestions from me, and links to some other KB articles on this subject.
Of course, if you have some sort of orderly backup system in place, probably you can just revert to an earlier backup of your Excel file from before the problem occurred. The KB articles linked here pretty much assume that's not an available option.
Excel 2002 and later includes features specifically for repairing damaged files. Sometimes you can have a damaged file that will not open in Excel 2000, but if you open it in Excel 2002 it will repair the file, and the saved repaired file will thereafter work fine in Excel 2000. All the recovery techniques in the Excel 2000 and Excel 97 KB articles make use of features designed for other purposes.
Since the alternative office suite OpenOffice.org is open-source freeware, and normally can open, edit, and save Microsoft Office document files, you could always try opening problem Excel workbooks in OpenOffice. I have no experience with this as a recovery technique, but the price is right.
Another way you can hedge your bets, when you have an Excel database that's important, is to periodically save the sheet tab with the data out to a CSV text file. The CSV text format doesn't save any of your formatting, but if your XLS workbook file goes bad, you can import the CSV data into a new workbook file and get all your data back, as of the last save at least.
When a big Excel workbook file is used to accumulate data for a long period of time, say, a year, or gets edited a lot over a long period for some other reason, that file is more likely to become damaged. A good way to avoid this is to start over with a fresh file once every quarter (three months).
Unfortunately what people do sometimes is take the old quarter's file, clear the changing data out of it, and do a Save As to the new quarter's file name. This actually doesn't help with the file corruption issue, because any incipient corruption problems that might have been accumulating tend to get perpetuated in the new file. This technique is actually not much better than just continuing to add to the old file. Also, if you clear the old data and start typing in the new data, and then by mistake do Save instead of Save As, you will overwrite the old quarter's file.
When you first create a file that's going to be used periodically like this, once you have the layout and formulas complete, clear the changing data out of it, do Save As, and change the format to Template (XLT). When you change to XLT format, Excel automatically switches the save location from My Documents to a standardized Templates folder in another location. Don't override this template save location.¹
After you save your template, if you make changes in the working file to elements that are part of your template file, you'll need to open the template² and make the same changes there. Other than that, your normal work on the accumulating data in the XLS file will not affect the template file.
When you get to the end of the quarter, don't touch the old quarter's working file. Just do File New from the menus. You should see the template file you created in the General tab of the Templates dialog box. It will open up with a generic filename, just as if you were starting a new blank workbook, and you get to do Save As and pick the new filename for the new quarter the first time you save it.
The ROUND() function should be included in currency calculations whenever they involve multiplication or division, or it may appear that your formulas are producing incorrect results. If your currency calculations are exclusively addition and subtraction, or when you're multiplying an integer quantity by a unit price, this problem doesn't arise.
The likely cause of confusion here is that cell formats only control what is displayed and printed, not what's stored internally. For example, if you compute an 8% discount on a price of $19.95 by simply multiplying by 0.92, the exact result stored internally will be 18.354, displayed by the cell format as $18.35. When you total a series of these, Excel adds the internal numbers, not the displayed values, and therefore sometimes the displayed total may vary a few cents from the result you will get if you use a calculator to add up the displayed dollars and cents.
Instead of using just =A2*0.92, use a formula like =ROUND(A2*0.92,2). The second argument (2) controls how many digits after the decimal to round to. This will cause both the displayed and internal values to be rounded to the nearest penny, and calculator totals will always match the ones displayed.
When there are organizational rules that call for always rounding up or down to the next penny, rather than the nearest penny, there are ROUNDUP() and ROUNDDOWN() functions available also. I believe sales tax is typically rounded up to the next penny, meaning you would use ROUNDUP(). You can also round to whole dollars instead of whole pennies, simply by using 0 (zero) for the second argument instead of 2.
| Input number | Formula | Result | Notes |
|---|---|---|---|
| 7,864.3642 | =ROUND(C4,3) | 7,864.3640 | Nearest thousandth |
| =ROUND(C4,2) | 7,864.360 | Nearest hundredth | |
| =ROUND(C4,1) | 7,864.40 | Nearest tenth | |
| =ROUND(C4,0) | 7,864.0 | Nearest integer | |
| =ROUND(C4,-1) | 7,860 | Nearest ten | |
| =ROUND(C4,-2) | 7,900 | Nearest hundred | |
| =ROUND(C4,-3) | 8,000 | Nearest thousand |
My Excel KB articles page links to three Knowledge Base articles that discuss rounding and floating-point arithmetic in Excel at various levels of technical detail.
Mail merge is the process used in Microsoft Office, OpenOffice.org, and other office suites to generate multiple form letters, mailing labels, printed envelopes, or other types of documents, based on a list of addresses or other data. You need to have two files ready before you can do a Microsoft Office mail merge: a data source, usually a list of names and addresses, and a merge document, a Word document defining the formatting of the form letters, labels, or envelopes to be produced.
The formatted Word merge document contains merge fields, which during the merge operation are replaced, record by record, with data from the data source.
The data source file can be in any of several formats including:
In Microsoft Office, the actual mail merge operation is always performed from within Word.
Microsoft Office mail merges with an Excel, Access, or CSV text data source should work for you up to around 500-1,000 records. If you have a bigger data source, you can probably get the job done cheaper and faster* by calling a local third-party direct-mail bureau; they will have more efficient software and hardware for large mail merges.
My KB articles page links to articles about Office mail merges.
site feedback