Excel Tip of the Week #377 - The limits of Excel

Because computer programs are written and executed in binary, you’ll notice that these limits are almost all either powers of two, or very close to them. In this article we’ll be reporting the values for the Microsoft 365 version, which are more or less identical to the limits to any version of Excel from 2010-2019; the numbers for Excel 2007 or earlier versions are significantly lower in most cases.

Characters in a cell – 32,767 (2¹⁵-1)

You can type an awful lot into a single Excel cell – but there is an upper limit and it’s a little shy of 33,000 characters. This is still an awful lot – you could fit this entire blog into a single cell – but it can be exceeded. After this point you simply can’t type any more; if you are using a formula to return something longer than this, you’ll get a #VALUE! error.

Numerical value of a cell – fifteen decimal places / ±10³⁰⁸ / ±2^1,024

There are two different concepts here. Firstly, Excel only stores numbers with fifteen digits of precision – anything beyond that is curtailed automatically:

Note that this scales – so decimals If you have something with more than this many digits across all relevant places. If you have something made of numbers and you need to store all of them – such as a reference number or phone number – then you need to format the cell as text to prevent the excess digits being lost.

Secondly, the values typed into cells can’t be larger than ±10³⁰⁸, which is a little less than 2^1,024. The limit for values returned from formulas, instead of directly typed, is actually anything below 2^1,024. Anything outside those limits returns a #NUM! error. You also can’t store numbers smaller than 2^-1,022.

Worksheet rows – 1,048,576 (2²⁰)
Worksheet columns – 16,384 (2¹⁴)

Easily seen by holding Ctrl and pressing down / right, the final cell in a post-Excel 2003 worksheet is XFD1048576. That makes for a total of 17,179,869,184 (2³⁴) cells in a worksheet – although most computers will give out and be unable to handle anything with anywhere close to that many that actually contain data.

If you for some reason have data that reaches one or the other of these limits, you will be unable to insert new rows / columns until the data is moved or deleted.

Formula length – 8,192 (2¹³) characters / 255 (2⁸-1) arguments / 64 (2⁶) levels of nesting

Formulas can’t exceed 8,192 characters, have more than 255 separate inputs, or reach more than 64 layers of nesting. Anything over this will return a #VALUE! error. You also can’t have more than 1,024 (2¹⁰) consecutive operands – e.g. +, - and so on. If you ever run into these limits you have gone somewhere seriously wrong!

Dates – January 1, 1900 to December 31, 9999

Excel uses January 1, 1900 as its start date – the date that the number 1 represents if converted to a date format. Pre-1900 dates aren’t possible and won’t work if the cell is converted to any date format. At the other end we have plenty of room!

Worksheets in a workbook – dependent on memory

This is limited by the file size limit, which depends on the complexity of your sheets, the exact version and environment you are running on, and other factors.

Names in a workbook – dependent on memory

Another one that is only capped by the global file size limit – although humans’ ability to read and understand your workbook will run out long before that!

VBA variable type limits - various

There are various different variables you can use to store number in Excel – the main ones and their limitations are:

• Integer: -32,768 to 32,767 (-2¹⁵ to 2¹⁵-1)
• Long: -2,147,483,648 to 2,147,483,648 (-2³¹ to 2³¹)
• Single: -3.402823 x 10³⁸ to -1.401298 x 10^-45 for negative values; 1.401298 x 10^-45 to 3.402823 x 10³⁸ for positive values (-2¹²⁸ to 2¹²⁸)
• Double: -1.79769313486232 x 10³⁰⁸ to -4.94065645841247 x 10³²⁴ for negative values; 4.94065645841247 x 10^-324 to 1.79769313486232 x 10³⁰⁸ for positive values (-2⁵¹² to 2⁵¹²)

These will cause your code to break if exceeded.

There are, of course, limits for every aspect of a spreadsheet. You can read the full list from Microsoft if of interest.

You may also like

• Previous post - Discounting functions redux
• TOTW index
• Next time – Revisiting splitting data by category