Excel is a very common application for people who work with data. We can collect, store, process, and get information from raw data using Excel. The table is one of the popular features of Excel. There are lots of benefits of using the table. But in this article, we will discuss table formatting problems in Excel.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 4 Table Formatting Problems in Excel with Their Solutions

Here, we will discuss some of the problems we face in table formatting. We will try to give their solution to the problems.

### 1. Copy Formulas with Table References in Excel Table Formatting

We will show what problems we face in the case of using Table Reference. This problem arises when we need to drag a formula from a cell left to right. But in the case of dragging up to the bottom, no problem occurs.

**Step 1:**

- Here, we want to find prices against the
**Product ID**. A formula will be used to do so.

**Step 2:**

- We will first set 3 IDs to get the price.

**Step 3:**

- Now, go to
**Cell G6**. - Write the formula to get the price of Id mentioned in
**Cell G5**.

`=INDEX(Table2[Price],MATCH(G5,Table2[Product ID],0))`

**Step 4:**

- Now, press
**Enter**to get the return.

**Step 5:**

- Now, drag the
**Fill Handle**icon to the right.

**Step 6:**

- We see that
**#N/A**is showing. - Click
**Cell H6**to see the formula.

Here, we see that Table references are changed when drag towards the right.

**#Fix the Problem**

- To solve this problem, select
**G6**to**I6**and press**Ctrl+R**. - Or we can copy the formula from
**Cell G6**and paste it to**Cell H6**and**I6**.

Now, we see that the exact result is showing.

**Read More:** **How to Remove Table Formatting in Excel (2 Smart Ways)**

### 2. Insufficient Execution of Structured Table Formatting

We can refer to a table column or table cell in a formula. From the formula, we noticed that it is a “structured” reference rather than a regular cell reference. Usually, we refer to any column or cell value as a reference, but here we used table modifier reference.

**Read More:** **Use Formula in an Excel Table Effectively (With 4 Examples)**

**Similar Readings**

**Excel VBA to Create Table from Range (6 Examples)****How to Make a Comparison Table in Excel (2 Methods)****[Fix]: Formulas Not Copying Down in Excel Table (3 Solutions)****Reference Table Column by Name with VBA in Excel (6 Criteria)****Excel VBA Code for Each Row in a Table (Add, Overwrite, Delete, etc.)**

### 3. Table Expansion Problem When Adding New Data in Excel

**Step 1:**

- We want to add a new data row.
- Put a value to next the cell where the table ends.

**Step 2:**

- Then press
**Enter**.

We can see that newly added data is not added to the table. We can solve this by changing **AutoFormat Settings**.

**#Fix Table Expansion Problem**

**Step 3:**

- Press
**File,**then click**Option**. - We will get
**Excel Options**.

**Step 4:**

- Now, select
**Proofing**. - We will get
**AutoCorrect Options**.

**Step 5:**

- After clicking
**AutoCorrect Options,**we will enter a new window. - Select
**AutoFormat As You Type**.

**Step 6:**

- Select the boxes of the last two options.
- Then press
**OK**.

**Step 7:**

- Now, go the
**Cell B13**and press**Enter**.

Now, we can see that table is expanded.

**Read More: How to Make an Excel Table Expand Automatically (3 Ways)**

### 4. Data Validation Problem in Excel Table Formatting

**Step 1:**

- Here, we will make a drop-down list and use it in Table. So, a column named
**List**is added.

**Step 2:**

- Define a name for the new column.
- First, select all the cells of the new column.
- Go to
**Formula**tab. - Then select the
**Define Name**option from**Defined Names**.

**Step 3:**

- Give a name on the box. We used
**List** - Then press
**OK**.

**Step 4:**

- Select the cells of the
**Name**column. - Go to the
**Data**tab. - Select the
**Data Tools**command. - Then, select
**Data Validation**.

**Step 5:**

- In the setting select
**List**to allow.

**Step 6:**

- Then, put the
**Source**. We used**List**, defined previously. - Then, press
**OK**.

**Step 7:**

- Now, go to the cells of the column and select names from the drop-down.

**Step 8:**

- Completed names for all the cells.

**Step 9:**

- Now, add a new row to the table.

A new row is added but sometimes will face the drop-down list is showing for this cell. We will solve this by **Data Validation.**

**Step 10:**

- Now, copy any cell that contains a drop-down.
- Select all the cells including the new one.
- Then go to
**Paste Special**. - Select
**Validation**from the list.

**Step 11:**

- Then press
**OK**.

**Read More: How to Create a Table in Excel with Data (5 Ways)**

## Some More Problems in Excel Table Formatting

- In the case of protected sheets, the
**Table**will not work properly. We can’t easily add or remove rows. - When we will use a large size table then our workbook will work slowly.
- When we add protections to lock our formula, the table functions are lost. Specifically, it will no longer auto-expand.
- When we link tables in other workbooks, the links will return a
**#REF!**error if the source workbook is not open. By changing all links to relative cell references we can solve this. - When we delete data from our table, the table doesn’t automatically shrink. We need to resize the table manually. We can do this with the
**Resize Table**option or by deleting the rows.

## Conclusion

In this article, we described some problems with table formatting in Excel. We also showed how to fix them. Besides, there are lots of advantages to using tables. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.

## Related Articles

**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****[Fixed!] Pivot Table Field Name Already Exists (2 Quick Methods)****Pivot Table is Not Picking up Data in Excel (5 Reasons)****How to Refresh Pivot Table in Excel (4 Effective Ways)**