In this article, how to make an Excel table expand automatically will be discussed with a broad explanation and demonstrations. Making various types of tables with different features made Excel table functionality second to none. While working with tables made in Excel, sometimes we face problems like tables not expanding after adding new data, which we often need to do as part of updating tables. So, let’s see how to make an Excel table expand automatically.
Make an Excel Table Expand Automatically: 3 Ways
In this article, I am going to use this dataset for demonstration purposes. In the range of cells B4:D12, the whole table shown below is in nice shape, and all texts are visible perfectly. However, in some cases, the table can become unclear due to inadequate row height and column width. Their row and column height are too shallow to understand the texts. Sometimes when new data is inserted, tables do not expand themselves and accommodate the new values. How we solve this issue of automating the table expansion task will be discussed here with explanations and demonstrations.
1. Make an Excel Table Expand Automatically Using AutoCorrect Option
Autocorrect is a frequent use feature in Excel, by which you can correct typos like capitalization errors and misspelled words and symbols. In Excel, they can fill or extend Excel tables.
To use the AutoCorrect feature to expand the table automatically, follow the below steps,
- Enter any new entry below the existing table.
- Observe that the table isn’t expanding to accommodate new data.
- To resolve this, go to File > Option (Double Click) > Proofing > AutoCorrect Option.
- A new window will open. In that window, go to AutoFormat As You Type.
- In that tab, tick in the second and third checkboxes if it is in unchecked condition.
- After tick marking the boxes, click OK.
- After clicking OK, try to enter a new row containing new data.
- You will notice that the table now expands as new information is added.
Read More: How to Rename a Table in Excel
2. Expand an Excel Table Changing Column Width
Sometimes, column width in Excel is in such a bad shape that the text is not in a visible state at all. In that situation, you need to alter the column width such that it is able to contain all text perfectly.
- Notice the table below in which the table column’s text is in too tight and crumbled spaces, resulting in lesser visibility of texts.
- To resolve this issue, click on the Select all button in the corner of the worksheet, which results in selecting all the cells in the worksheet.
- Then from the Home tab, go to Cells > Format Cells > AutoFit Column Width
- After clicking AutoFit Column Width, you will notice that your table is now expanded and contains text perfectly.
Read More: How to Extend Table in Excel
3. Make an Excel Table Expand Changing Row Height
In many cases, row height in Excel ends up in such a shape that the texts are no longer in the visible state at all. In that situation, you need to use autofit row height to alter row height such that it is able to contain all text perfectly.
- In the table shown below, rows are too tight, resulting in texts not showing properly.
- To resolve this issue, click on the Select all icon in the corner of the worksheet, which results in selecting all the cells in the worksheet.
- Then from the Home tab, go to Cells > Format Cells > Autofit Row Height.
- After clicking AutoFit Row Height, you will notice that your table rows are now perfectly expanded, and the text is clearly visible.
Download Practice Workbook
Download this practice workbook below.
To sum it up, the question of how to make an Excel table expand automatically is answered here in 3 different ways. Starting from using menu ribbon options such as Autofitting row and column width with using autocorrect option in Excel options. Among all the methods used here, using ribbon options is the easier-to-understand and simpler one.
For this problem, a macro-enabled workbook is available for download where you can practice and get used to these methods.
Feel free to ask any questions or feedback through the comment section.
- How to Use Sort and Filter with Excel Table
- How to Remove Format As Table in Excel
- Excel Table Formatting Problems
- [Fix]: Formulas Not Copying Down in Excel Table
- How to Remove Table Functionality in Excel
- How to Remove Table in Excel
- How to Undo a Table in Excel
- How to Mirror Table in Excel