One of the most common features of Excel is to make Tables. 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. In this article how to make an Excel table expand automatically will discuss with a broad explanation with demonstrations.
Download Practice Workbook
Download this practice workbook below.
3 Ways to Make an Excel Table Expand Automatically
In this article, I am going to use this dataset for demonstrating purposes. In the range of Cells B4:D12, the whole table shown below is in nice shape and all texts are visible perfectly. But in some cases, the table can become unclear due to inadequate row height and column width. Maybe 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 discuss here with explanations and demonstrations.
1. Make an Excel Table Expand Automatically Using AutoCorrect Option
Autocorrect is a frequent use feature on 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 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 uncheck 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 expanded as new information is added.
Read More: Create Table in Excel Using Shortcut (8 Methods)
2. Expand an Excel Table Changing Column Width
Sometimes, column width in Excel is in such a bad shape that the texts are 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 below table 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 texts perfectly.
Read More: Excel Table Formatting Tips – Change the Look of the Table
- Pivot Table Not Refreshing (5 Issues & Solutions)
- VBA to Refresh Pivot Table in Excel (5 Examples)
- [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions
- Calculated Field Sum Divided by Count in Pivot Table
- How to Make Group by Same Interval in Excel Pivot Table (2 Methods)
3. Make an Excel Table Expand Changing Row Height
In many cases, row height in Excel is ended 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 situation 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.
Read More: How to Extend Table in Excel (4 Ways)
To sum it up, the question “how to make an Excel table expand Automatically” is answered here in 3 different ways. Starting from using menu ribbon options such as using Autofitting row and column width with using autocorrect option in Excel options. Among all of the methods used here, using ribbon options is the easier to understand and simple one.
For this problem, a macro-enable 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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
- How to Lookup a Table and Return Values in Excel (3 Simple Ways)
- [Fix] The Pivot Table Name Is Not Valid (7 Causes with Solutions)
- How to Get a Count in Excel Pivot Table Calculated Field
- Pivot Table is Not Picking up Data in Excel (5 Reasons)
- How to Refresh Pivot Table in Excel (4 Effective Ways)
- Pivot Table Custom Grouping: With 3 Criteria