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**.

- The
**Power Query**window will open.

- You can also create a pivot table report to minimize the rows and work with the reports: choose
**Load To**in the preview window.

- 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.

## 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**

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