How many columns does MS Excel contain

How to determine the number of columns in an Excel table in all versions

The Excel versions differ in the maximum number of columns available in a table. In many cases it is important to determine the number of columns that are available to you.

In Excel up to and including version 2003, a maximum of 256 are allowed in a table. In this case, the first column is column A, the rightmost column is column IV. In Excel 2007 or later, a maximum of 16,384 columns are available. In this case, the column name begins with A and ends with XFD.

However, if you open a table in Excel 2007 or later that is from an earlier version of Excel, only 256 columns are available. This also applies if you save a table in XLS format, the data format used by Excel up to and including version 2003.

Therefore, even when using Excel 2007 or later, it can happen that the last column on the far right is the XFD column.

To use a formula to determine how many columns are available in a table, you can use the following array formula:

= MAX (COLUMN (1: 1))

In order for the formula to work, it is entered as an array formula. To do this, press the SHIFT and CTRL keys and keep these keys pressed. Then press the ENTER key at the same time. The SHIFT key means the key that you must hold down to type an uppercase letter on your keyboard. You can also use this key combination after changing the formula.

The following figure shows the result that the formula displays in Excel 2010 if you have also generated the appropriate workbook in Excel 2010 or Excel 2007.

If you use the formula in an Excel 2003 table, you will get the result from the following figure:

You will also get this result when you open a table in Excel 2007 or Excel 2010 in Compatibility Mode.

Make sure to select the line number in the column (in the example 1: 1) so that it is not identical to the line number in which you are using the formula, otherwise you will receive a circular reference with a corresponding error message.

By the way: The limits of the number of columns, which at first glance seem strange, have to do with the binary system. The number 256 corresponds to 2 to the power of 8, the number 16,384 corresponds to 2 to the power of 14.