Alternate Method of Autosizing a Database in Excel

Last reviewed: November 2, 1994
Article ID: Q46458

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

Using the Form command from the Data menu, you can redefine a Microsoft Excel database automatically to include new records as they are added. This is the recommended method when using databases with up to 32 fields.

For databases with more than 32 fields, the method below can be used. This method is paraphrased with permission from the Cobb Group's journal "The Expert," (c), Vol. 2 No. 4, April 1989.

This method uses a defined name that refers to a formula to determine the lower-right-corner cell of your database.

To make your database autosizing (that is, to make it redefine itself to the number of entries in your database), use the definition

   =tl:INDEX(tr:br,COUNTA(t:b))

where the following apply:

   tl = top left corner of database
   tr = top right corner of database
   br = reference far below bottom right corner
   t  = cell that contains a field name that indicates a field that
        will have no blank cells
   b  = column from t, row from br

Before you enter this definition make sure that Database has been entered in the Name box of the Formula Define Name command. (If you are using version 5.0, choose Name from the Insert menu, then select Define.) Type the formula above into the Refers To box and press ENTER or click OK.

Note: in Microsoft Excel version 5.0, these steps are not required because Microsoft Excel automatically highlights your list when you select Sort from the Data menu.

MORE INFORMATION

The COUNTA function counts the number of nonblank cells in the range t:b, so all database records should have something in this field; once a blank cell is reached, that should be the end of the database. This value, the number of nonempty cells, (that is, the number of records in the database) is used in the INDEX function to indicate which cell from tr:br (the rightmost column of the database) to return as the bottom right cell of the database.

For example, if your database were in columns A through E, and you had records defined in rows 2 through 89 (row 1 has the field names), you would use a database definition similar to the following:

   =$A$1:INDEX($E$1:$E$1000,COUNTA($B$1:$B$1000))

Column B is assumed always to have an entry for any record in the database. Because a limit must be indicated, if the database extends beyond row 1000, the formula would have to be redefined, substituting a larger number for 1000 above. The larger the number used, the longer calculations will take; the smaller the number used, the smaller the absolute limit on the database size (until redefined).

Note: Make sure that absolute references are used.

KBCategory: kbusage
KBSubcategory:
Additional words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00 5.0 5.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.