Consider the following image. It shows the maximum row and column number (1,048,576th row and 16,384th column) in an Excel worksheet. To insert more rows or columns, you have to use alternative tools like Power Query.
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 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 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 available memory |
PivotTable and PivotChart Limitations in Excel
| Parameter | Maximum Limit |
|---|---|
| How many PivotTable reports are allowed in a sheet? | Dependent on the 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 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 more recent versions of Excel, multiple users can edit a workbook simultaneously. If you are using older versions, you can enable the shared workbook feature in Allow changes by more than one user.
| Parameter | Maximum Limit |
|---|---|
| How many users can open a workbook at the same time? | 256 |
| What is the maximum number of days the 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 Allow changes by more than one user in a shared workbook.
How to Work Around Row Limitations in Excel
- Go to the Data tab and select Get Data in Get & Transform Data.
- Select the type of file you want to import.
- You will see the following preview window. Select Transform Data.
Click the image for a detailed view
- The Power Query window will open.
Click the image for a detailed view
- You can also create a pivot table report to minimize the rows and work with the reports: choose Load To in the preview window.
You can click the image for a detailed view
- Select PivotTable Report in Import Data and click OK to get the report.
How to Limit the Number of Rows and Columns in an Excel Worksheet?
Consider the following dataset:
1. Limit the Number of Rows
- Click the row number after which you want to hide rows.
- Press Ctrl + Shift + ▼ (Down Arrow) to select all rows below.
- Go to the Home tab >> click Cells >> click Format >> choose Hide & Unhide >> select Hide Rows.
This will hide the selected rows.
2. Limit the Number of Columns
- Click the column number after which you want to hide the columns.
- Press Ctrl + Shift + ► (Right Arrow) to select all columns to the right.
- Go to the Home tab >> click Cells >> click Format>> choose Hide & Unhide >> select Hide Columns.
This will hide the selected columns.
3. Use Sheet Properties to Limit Access to Rows and Columns
Use the ScrollArea property.
- Right-click the sheet tab and select View Code.
- The Visual Basic Editor window will open. Click View and select Properties Window.
- Set the ScrollArea property to the range you want to limit the access. Here, A1:H15.
- Go back to the active sheet. You will be able to select cells within A1:H15. Cells outside the range can’t be selected.
Click the GIF for a detailed view
What Are the Things to Remember?
- If your formula exceeds the formula length limit, try to break it down into different cells and use references.
- Manually limit the decimal points by customizing cells to prevent operations from clogging.
- 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, there is a custom number depending on the system used.
2. How do you set limits in Excel?
Some limits are predetermined by default. However, you can set custom-reduced limits.
3. Why does Excel have 1048576 Rows?
Excel had 65536 rows in the older versions matching the 16th power of 2. Later, it increased to the 20th power of 2 to comprise larger datasets.
Excel Limits: Knowledge Hub
<< Go Back to Excel Parts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I tried to replicate an odd math coincidence by squaring the number 111,111,111. The answer should have been 12,345,678,987,654,321 which is quite cute except that no matter how I varied number formatting, the answer was always 12,345,678,987,654,300 which is the wrong answer by the amount of 21. Did I violate an Excel technical limitation?
Hello John Facey,
Yes, Excel has a limitation in handling very large numbers due to its floating-point arithmetic, which can lead to precision errors. When squaring 111,111,111, the correct result is 12,345,678,987,654,321, but Excel is displaying 12,345,678,987,654,300 due to this limitation. This discrepancy is because Excel can only precisely handle integers up to 15 digits. To get the exact result, consider using specialized software or programming languages that support arbitrary-precision arithmetic.
Regards
ExcelDemy