(If you are adding data in Excel to a file you have already saved once to dBase, see the important instructions at the end of this page)

The dBASE file formats DBF 2 (dBASE II), DBF 3 (dBASE III), and DBF 4 (dBASE IV) save only the text and values as they are displayed in cells of the active worksheet. All cell formatting, page layout settings, graphics, objects, and other Microsoft Excel 97 features are lost. The data displayed in the current view of a PivotTable is saved; all other PivotTable data is lost.

All rows are saved; the following numbers of columns are saved.

• DBF 2 (dBASE II) 32
• DBF 3 (dBASE III) 128
• DBF 4 (dBASE IV) 256

Text values in the first row of the range are used as dBASE field names, which are limited to 10 characters and cannot contain any spaces; subsequent characters are truncated. If the first row contains all numbers, automatic field names (N1, N2, and so on) are inserted in the converted file.

Data types of values in the first row of data in the converted range determine the data types of each field (column) of data. If the first row of data contains a blank value for a field, the field is converted as a text field, even if subsequent rows contain numbers in that field; the numbers are converted as text.

Only data visible in the worksheet cells is saved. Widen the columns in Microsoft Excel, and make sure all the data is visible before you save the sheet in dBASE format.

If cells contain decimal fractions but are formatted in Microsoft Excel with the General number format, the values are rounded to the closest whole number. To preserve numbers to the right of the decimal point, format the cells with the Number format, and then specify the number of decimal places you want saved.

If cells in the converted range contain formulas, only the resulting values of formulas are saved, even if the sheet display options are set to show formulas in the worksheet cells instead of values. To ensure that the formula values are converted correctly, click Options on the Tools menu, click the View tab, and clear the Formulas check box. Then make sure that all values are visible before you save the file. If you want to save a formula as text, insert an apostrophe (') before the formula.

Important Note - Adding new rows and columns to a previously saved dBase file in Excel

If you have used Excel to create a .dbf file, and you later want to go back and add records or columns to that .dbf file in Excel, please read the following before you save or export the modified file:

To add to an existing .dbf file in Excel, you must redefine the named range.  This is because, unbeknownst to you, when you first exported the Excel file to a .dbf file, Excel created a "named range" for that .dbf file.  If you do not modify this named range, Excel will continue using this original range and thus not include any added rows or columns on subsequent exports to .dbf of the same file . If you don't believe me, feel free to find out the hard way by losing all your added data!  

To modify the named range to after you have added data to an existing .dbf file and before you save or export the file:

1. On the Insert menu, point to Name, and then click Define.
2. In the Names in workbook box, click the name whose cell reference you want to change (this is "database")
3. In the Refers to box, change the reference (just change the last row and/or column number so that it covers all your records)
4. Choose File-Save As, and save to a new .dbf  (this ensures that you don't accidentally destroy your original file without being sure this process has worked)

11October 1998
The School of Architecture at UT Austin
Comments to: Barbara Parmenter