ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week

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

Author: David Lyford-Smith

Published: 19 Jan 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.
Contents

Hello all and welcome back to the Excel Tip of the Week! This week we have a General User level post in which we’re going to be talking – in a very literal sense – about the limits of Excel. Like all programs, Excel can only handle so much data – so we’re going to go through and talk about where those limits are and what they mean for our Excel use.

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.

Limits in the cells

Characters in a cell – 32,767 (215-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 / ±10308 / ±21,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 ±10308, which is a little less than 21,024. The limit for values returned from formulas, instead of directly typed, is actually anything below 21,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 (220)
Worksheet columns – 16,384 (214)

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 (234) 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 (213) characters / 255 (28-1) arguments / 64 (26) 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 (210) 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!

Other relevant limits

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 (-215 to 215-1)
• Long: -2,147,483,648 to 2,147,483,648 (-231 to 231)
• Single: -3.402823 x 1038 to -1.401298 x 10-45 for negative values; 1.401298 x 10-45 to 3.402823 x 1038 for positive values (-2128 to 2128)
• Double: -1.79769313486232 x 10308 to -4.94065645841247 x 10324 for negative values; 4.94065645841247 x 10-324 to 1.79769313486232 x 10308 for positive values (-2512 to 2512)

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

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.