In this Excel tutorial, you will learn about Excel limits. We will discuss workbook and worksheet limits, limitations of calculations, and specifications of data models.
Excel limits refer to the predefined constraints and boundaries that Microsoft Excel imposes on workbooks, worksheets, calculations, and data models. Understanding these limits is crucial for efficient and error-free data management and analysis in Excel.
For example, consider the following image. It shows the maximum row and column number (1,048,576th row and 16,384th column) in an Excel worksheet. If you require more rows or columns than this limit, then you have to use alternative tools like Power Query.
Note: We used Microsoft 365 to prepare this article. However, the same limits and specifications apply to Excel 2021, Excel 2019, Excel 2016, and Excel 2013 versions as well.
⏷Worksheets and Workbook Limitations
⏷Calculation Limitations
⏷Data Model Limitations
⏷Limitations of Charts
⏷PivotTable and PivotChart Limitations
⏷Shared Workbook Limitations
⏷Work Around Row Limitations
⏷Limit Number of Rows and Columns
⏵Limit Number of Rows
⏵Limit Number of Columns
⏵Use Sheet Properties to Limit Rows and Columns
⏷Things to Remember
⏷Frequently Asked Questions
⏷Excel Limits: Knowledge Hub
Worksheets and Workbook Limitations in Excel
| Parameter | Maximum Limit |
|---|---|
| What is the maximum column number? | 16,384 columns |
| What is the maximum row number? | 1,048,576 rows |
| How many non-contiguous cells can be selected? | 2,147,483,648 cells |
| What is the maximum column width? | 255 characters |
| What is the maximum row height? | 409 points |
| How many lines can be inserted in a cell? | 253 line feeds |
| What is the character limit in an Excel cell? | 32,767 characters |
| What is the maximum number of characters allowed in Headers/Footers? | 255 characters |
| What is the maximum number of fill styles available in Excel? | 256 |
| What is the maximum number of line weights and styles available in Excel? | 256 |
| How many cell format styles are available in Excel? | 65,490 |
| How many hyperlinks can a sheet contain? | 65,530 |
| How many panes can a window have? | 4 |
| What is the maximum number of horizontal and vertical page breaks allowed in Excel? | 1026 |
| What is the number of formats a sheet can contain? | Between 200 to 250 (dependent on the language version of Excel) |
| How many times can you undo Excel? | 100 |
| What is the maximum number of items a drop-down list can have in Excel? | 10,000 |
| What is the minimum and maximum zoom (i.e. zoom range) in Excel? | 10% and 400% |
| What is the maximum number of fields in a data form? | 32 |
| What is the maximum number of parameters in a workbook? | 255 |
| What is the maximum file name length of an Excel file? | 218 characters (including the file path and file extension) |
| What is the maximum number of custom functions, custom named ranges, named views, linked sheets, and sheets in a workbook, and the number of open workbooks at a time? | Dependent on the system’s available memory |
Calculation Limitations in Excel
| Parameter | Maximum Limit |
|---|---|
| How many decimal points can Excel consider while calculating? | 15 decimal points |
| What is the lowest allowed negative number in Excel? | -2.2251E-308 |
| What is the lowest allowed positive number in Excel? | 2.2251E-308 |
| What is the highest allowed negative number in Excel? | -9.99999999999999E+307 |
| What is the highest allowed positive number in Excel? | 9.99999999999999E+307 |
| What is the character limit for writing formulas? | 8,192 characters |
| What is the internal length for a formula? | 16,384 bytes |
| What is the maximum number of arguments allowed in a function? | 255 |
| How many nested levels of functions are allowed in Excel? | 64 |
| What is the earliest allowed date in Excel? | January 1, 1900 |
| What is the latest allowed date in Excel? | December 31, 9999 |
| What is the highest amount of time allowed in Excel? | 9999:59:59 |
Data Model Limitations in Excel
| Parameter | Maximum Limit |
|---|---|
| What is the character limit for a Table or column name? | 100 characters |
| How many tables for a particular model can you have? | 2,147,483,647 |
| What is the maximum number of columns for a table? | 2,147,483,647 |
| What is the number of processes Excel can run simultaneously? | 6 |
| How many connections can you create in a model? | 5 |
| What is the number of rows and distinct values a column can have? | 1,999,999,997 |
| What is the maximum string length in calculations? | 512 MB equivalent, or 268,435,456 Unicode characters (with some exceptions for particular functions) |
Limitations of Charts in Excel
| Parameter | Maximum Limit |
|---|---|
| What is the maximum number of charts linked to a Worksheet? | Dependent on the system’s available memory |
| How many worksheets can be referred to by a chart in Excel? | 255 |
| What is the maximum number of data series in a chart in Excel? | 255 |
| How many data points are allowed for a data series for a 2-D or 3-D chart in Excel? | Dependent on the system’s available memory |
PivotTable and PivotChart Limitations in Excel
| Parameter | Maximum Limit |
|---|---|
| How many PivotTable reports are allowed in a sheet? | Dependent on the system’s available memory |
| What is the maximum number of unique items per field in a PivotTable? | 1,048,576 |
| How many row or column fields are allowed in a PivotTable report? | Dependent on the system’s available memory |
| How many report filters are allowed in a PivotTable or PivotChart report? | 256 |
| How many value fields are allowed in a PivotTable or PivotChart report? | 256 |
Shared Workbook Limitations in Excel
In newer versions of Excel, multiple users can edit a workbook simultaneously due to the new co-authoring experience. If you are using older versions, then you can enable the shared workbook feature by enabling the Allow changes by more than one user setting. In both instances of shared workbooks, the following limitations apply.
| Parameter | Maximum Limit |
|---|---|
| How many users can open a workbook at the same time? | 256 |
| What is the maximum number of days a workbook change history is maintained? | 32,767 days (default is 30 days) |
| How many colors are used to identify changes made by different users when change highlighting is turned on? | 32 |
| What is the maximum number of cells that can be highlighted? | 32,767 |
| How many tables are allowed in a shared workbook? | 0 |
Note:
- In a shared workbook, each user is identified by a separate color. Highlights in navy blue are the changes of the current user.
- You can’t enable the Allow changes by more than one user setting in a shared workbook.
How to Work Around Row Limitations in Excel
- Go to the Data tab on your ribbon and select Get Data from the Get & Transform Data group.
- Select the type of file you want to import from in the drop-down menu.
- After selecting the file, you will get the following preview window. You can select Transform Data here.
Click the image for a detailed view
- It will take you to the Power Query window where you can work with more data before loading them.
Click the image for a detailed view
- Or you can directly create a pivot table report to minimize the rows and work with just the reports. You can do that from the Load To option of the preview window.
You can click the image for a detailed view
- Select PivotTable Report in the Import Data box and click on OK to get the report. It will have fewer rows to fit into Excel row limits.
How to Limit the Number of Rows and Columns in an Excel Worksheet?
Sometimes you may want to limit the number of rows and columns in Excel. For example, consider the following image that shows a dataset in the range B2:G13 of a worksheet. As the remaining rows and columns are not required here, you can limit your worksheet to only these required rows and columns.
1. Limit the Number of Rows
- Click the row number from where you want to hide the rows.
- Use the keyboard shortcut Ctrl + Shift + ▼ (Down Arrow) to select all the below rows.
- Go to the Home tab >> click Cells dropdown >> click Format dropdown >> hover your mouse pointer over the Hide & Unhide option >> select the Hide Rows option.
- This will hide all the selected rows.
2. Limit the Number of Columns
- Click the column number from where you want to hide the columns.
- Use the keyboard shortcut Ctrl + Shift + ► (Right Arrow) to select all columns to the right.
- Go to the Home tab >> click Cells dropdown >> click Format dropdown >> hover your mouse pointer over the Hide & Unhide option >> select the Hide Columns option.
- This will hide all the selected columns.
3. Use Sheet Properties to Limit Access to Rows and Columns
If you don’t want to hide any rows or columns but limit the access to a range, then you can use the ScrollArea property of sheets.
- Right-click on the sheet tab and select the View Code option.
- This will open the Visual Basic Editor window. Click on the View tab and select the Properties Window option.
- Now, you will get the Properties window for the active sheet. Set the ScrollArea property to the range for which you want to limit the access. Here, I have entered the range A1:H15.
- Now, if you return to the active sheet, you will be able to select cells within range A1:H15. However, the outside cells are not selectable.
Click the GIF for a detailed view
What Are the Things to Remember?
- If you enter any data that exceeds the limit of the particular, it shortens automatically. For example, inserting more than 16384 columns will cut out extra columns.
- If your formula gets too large for the formula length limit, try to break it down into different cells and use those references.
- Manually limit the decimal points by customizing cells to prevent operations from clogging.
- Try Power Query if the row number of a dataset is very large to work with and may exceed the limit of Excel.
- The Pivot Table Report will only give you the summary. You will not have all the rows to work with.
Frequently Asked Questions
1. Is there a limit to the sheets you can create in a workbook?
Excel usually allows 1048576 sheets in a workbook. However, it reduces to a custom number depending on the system it is running in. Because most of the systems can’t afford that processing power.
2. How do you set limits in Excel?
Some limits are predetermined by default. For example, Excel predetermines the column and row numbers, characters in a cell, etc. However, you can set custom-reduced limits to some of them. Check out the knowledge hub articles in the next section to learn more about them.
3. Why does Excel have 1048576 Rows?
Excel had 65536 rows in the older versions matching the 16th power of 2. Later, they increased the number to the 20th power to match the current number because it became too low for larger datasets.
Conclusion
That concludes our discussion on Excel limits. Be sure to check out the knowledge hub for manipulating some of the limits allowed in the application. Hopefully, this guide helped you with the basics of these. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
Excel Limits: Knowledge Hub
- Increase Character Limit in Excel Cell
- Limit Row Height
- Determine Sheet Name Limit
- Is There Any Way to Increase Excel Row Limit?
<< Go Back to Excel Parts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
