Excel Table Formatting Problems (with Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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 to using the table. But sometimes, you may face problems while using the table. Basically, the problem is one kind of formatting-related problem. In this article, we will discuss table formatting problems in Excel. So, let’s get started.

Overview Image


Excel Table Formatting Problems: 4 Different Problems with Their Solutions

Here, we have tried to discuss table formatting problems. Also, we give the solutions for those respective problems. To show you the examples, we have taken a dataset where we put some Product IDs with their Price.

Dataset

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.

Later, we need to convert the dataset to a table to show you the problem that arises while using formatting in the table. After creating the table, the dataset will look like the image below.

Creating table


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.

Steps:

  • In this section, we want to find Prices against the Product ID. We use the INDEX function here to do that. Firstly, set 3 IDs to get the price.

Copy formula with table reference

  • Next, go to cell G5. and write the formula to get the price of the ID mentioned in cell F5.
=INDEX(Table2[Price],MATCH(F5,Table2[Product ID],0))
  • Then, press ENTER to get the return.

Index function for the table formatting problem

  • Now, drag the Fill Handle icon to the right. We see that #N/A is showing.

The problem occurring while using table formatting

Here, the Table References are changed when dragging toward the right.

#Fixation of the Problem:

  • To solve this problem, we can copy the formula from cell G5 and paste it into cells G6 and G7.

Solving the table formatting problem

Now, we see that the exact result is showing.

Read More: How to Remove Format As Table in Excel


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 a table modifier reference. The @ symbol identifies a specific cell where you want to put the reference. It cannot show the entire column value rather it shows an entity. See the image for better visualization.

Structured Reference Problem in table formatting

Here, the symbol in the structured formula refers to a particular cell. That is why it returns Rice instead of the whole column. Remove the @ symbol, and you will get the corresponding column where you entered the reference.

Solving the table formatting problem

Read More: How to Extend Table in Excel 


3. Table Expansion Problem When Adding New Data in Excel

Moreover, if you want to add a new value to your data table. You cannot insert the value according to the table. Rather it becomes a cell value. Follow the description for better visualization.

Steps:

  • Initially, we want to add a new data row. For this reason, put a value next to the cell where the table ends.
  • Press ENTER.

Table Expansion Problem

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

#Fixation of Table Expansion Problem:

  • To solve the problem, firstly, press File >> then click Option.
  • After that, we will get Excel Options. From the Excel Options window, select Proofing.

Proofing

  • Eventually, select AutoCorrect Options.

Excel options window

  • After clicking AutoCorrect Options, we will enter a new window. From there, select AutoFormat As You Type.
  • Consequently, select the boxes of the last two options. And hit OK.

AutoCorrect Window

  • Apparently, move to cell B13 and insert any value.

Solving of Expanded Table problem

Now, we can see that the table is expanded.

Read More: How to Make an Excel Table Expand Automatically


4. Data Validation Problem in Excel Table Formatting

When we use Data Validation, we cannot bring the validation instantly after entering new data. To solve this problem, we need to insert the data validation manually with the copy-and-paste process. Follow the steps to do it.

Steps:

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

Data validation Problem

  • Apparently, define a name for the new column.
  • For that reason, firstly, select all the cells of the new column >> go to the Formula tab.
  • Sequentially, select the Define Name option from Defined Names.

Define Name

  • Give a name on the box. We used List. Select the reference from the List.
  • Subsequently, press OK.

New Name Window

  • Select the cells of the Name column. Sequentially, go to the Data tab >> choose Data Validation from the Data Tools command.

Data Validation from data tab

  • The Data Validation window appears. Select List in the Allow box and type List in the Source box.
  • Finally, press OK.

Data Validation window

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

Problem in Data Validation

  • At this moment, add a new row to the table.

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

Adding additional data

  • To solve this issue, copy any cell that contains a drop-down >> select all the cells, including the new one.
  • Sequentially, go to Paste Special.

  • Consequently, select Validation from the Paste Special. And hit OK.

Paste Special window

Now, you can find that the validation-related problem has vanished.

Solving Data validation problem

Read More: How to Mirror Table in Excel


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 use a large table, 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.

Download Practice Workbook

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


Conclusion

That’s all about today’s session. And these are some easy methods to solve table formatting problems in Excel. Please let us know in the comments section if you have any questions or suggestions.


Related Articles


<< Go Back to Edit Table | Excel Table | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo