||When you calculate the subtotals, the new column contains a formula, that's why the dbf can't be opened in ArcMap. If you create a brand new worksheet and copy/paste the two columns as text (special copy), then expand the fields to show the whole text, and finally select all the cells to be saved, you should be able to save to a dbf that ArcMap can read. The main idea is that any time you open the dbf file in Excel to make a small change, you have to create a blank worksheet, copy the results as described, and save again.
Indeed, it's much better to handle dbf files in Access. You can make all kind of queries and summaries and export in dbf without any problem.
More info on your problem. A long time ago I happened upon some very useful guidelines at this website, but the page has been deleted.
However, I saved the text. Here it is, it saved me many times and I apologize for the long post.
Preparing tables for use with ArcView and ArcGIS
A common task for GIS users is to import tables into the GIS application. This guide explains how to use Microsoft Excel to translate tabular data to the format required for use with ArcView and ArcGIS, the dBase format.
To save a file to dBase format (*.dbf) using Microsoft Excel:
1. With the file open in Excel, select the “Save as…” option from the main “File” menu (located in the upper left corner of an Excel window.
2. When the “Save as…” window appears, use the folder options to navigate to the space where you would like to save the file.
3. Choose a name for the file (by typing the name into the “File name” box near the bottom of the “Save as” window), keeping in mind that Arc products may have problems reading the file if the file name contains spaces or special characters such as @ # $ % etc. Arc products may also have problems with file names that begin with numbers.
4. The file type (found in the “Save as type:” dropdown menu located below the “File name” box in the “Save as” window) must be set to ‘DBF4 (dbase IV)’ or ‘DBF3 (dbase III).’ The DBF4 option is usually preferable.
5. Press the “Save” button to complete the process (located in the lower right corner of the “Save as” window).
The following formatting steps will help minimize the likelihood of processing errors during the conversion from Excel to DBF (Adapted from web page entitled "Chapter 15: Tables" from William Huber’s course "SYS EN 597D: Geographic Information Systems" at Pennsylvania State University. URL as of October 12, 2004: http://www.quantdec.com/SYSEN597/GTKAV/section4/chapter_15a.htm):
1. Reorder the data rows if necessary so that the first one has valid, non-blank values in all fields. If there is no such record, create a "fake" first record and fill it with valid non-blank values. You can delete it after the conversion has occurred.
2. Format each numeric and date column explicitly. Make sure the entire column is formatted, not just part of it. Specify the number of decimal places you want.
3. Widen all columns to contain all the data in them. One way is to increase the font to 14 points and auto-size all columns. This seems to work well. You can increase column widths beyond this minimum to accommodate future values that might be even longer in width. [Note: Another way is to 1) format the text as Courier font; 2) select all rows and columns, by clicking in the rectangular box above the row numbers and to the left of the column letters; 3) from the Format menu select Columns/Autofit.]
4. Type short meaningful field names in the single header row. Remember, the dBase format limits you to 10 characters.
5. Delete any blank rows placed in the spreadsheet merely for formatting purposes.
6. Check that all values in all numeric columns really are numbers. Fix any errors.
7. Remove any hidden rows or columns within the data (Excel will not export data in hidden rows or columns, anyway, so unhide any data you want to export).
8. Save the spreadsheet at this point in its native Excel format. This preserves everything you see, including all formatting, giving you a point you can return to if the next steps reveal errors.
9. Convert the spreadsheet to dBase format using Excel's 'Save as...' command. dBase IV and III formats are compatible with Arc products.
10. Close the spreadsheet immediately (Minimizing does not work: it has to be closed.). Now open the .dbf version you just created.
11. Check the results carefully. If they are unacceptable, close the .dbf version and reopen the Excel version. Fix the problem(s) and resume at step 8 [Note: if formatting as text or numeric is not successful from within Excel, the problem can be addressed in ArcView/ArcGIS by creating a new field (defining it as text or numeric as appropriate) and setting it equal to the original field's values].
12. Clean up: if the results are ok, delete your initial "fake" record if you used one and re-save the file in dBase format.
Adding rows or columns:
The steps you will need to take if you wish to go back and add rows or columns to your table are explained in Saving Excel files in dBase format, a page maintained by Barbara Parmenter at the University of Texas School of Architecture.
It says as follows:
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:
On the Insert menu, point to Name, and then click Define.
In the Names in workbook box, click the name whose cell reference you want to change (this is "database")
In the Refers to box, change the reference (just change the last row and/or column number so that it covers all your records)
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)