Md. Abdul Kader
Undoubtedly, a data table is a useful tool for updating, sharing, and analyzing data effectively. But sometimes we face issues that hamper working with the table. In this article, I’ll point out 7 significant issues for what a data table is not working in Excel and also demonstrate their solutions.
Download Practice Workbook
Issues for What Data Table Not Working & Their Solutions
As this article covers the issues with the data table, I hope you know how to create a data table in Excel. Whatever, let’s dive into the scenarios of Excel data table not working & their solutions.
1. An Issue with AutoCorrect Settings
A data table will look like this. Every table in Excel has an incredible feature to expand automatically.
Occasionally, the data table may not expand automatically if we input new data like the following picture where “1-Sep” is written in the B13 cell, but the table has not been expanded.
Do you ever understand the problem?
Actually, AutoCorrect Options govern this expansion in Excel.
We have to check the options from AutoCorrect Setting.
Let’s do this by following the steps below.
⏩ Click on File>Options>Proofing> then choose AutoCorrect Options.
After that, you’ll see the following command bar.
⏩ Choose AutoFormat As You Type
⏩ Check the box before Include new rows and columns in table Automatically as you work
⏩ Check the box before Fill formulas in tables to create calculated columns
⏩ Press OK.
Now, you’ll see the following table automatically expand if any new column or row is added.
Here, I write “1-Sep” in the B13 cell and the table has been automatically expanded with the formula.
Read More: How to Add Data Table in an Excel Chart (4 Quick Methods)
2. Problem with Table Functionality
A data table in Excel has some functionality like sort and filter arrows, structured references, etc.
But in some cases, you may face problems with the table functionality. In such a situation, you can overlook the Excel feature by following the steps below.
⏩ Select the whole data table
⏩ Move the cursor on the Table Design tab
⏩ Click on Convert to Range.
⏩ Then you’ll see the dialog box and click Yes.
Finally, the data table will be as follows. The format of the following data table will not change anymore.
Read More: Example of Excel Data Table (6 Criteria)
3. Data Table Not Working When Cells Have Heterogeneous Characters
As you know a data table has numerous cells, it is necessary for all cells to be in a homogenous character.
For example, if any cell is locked or unlocked, the data table may not work properly.
You may adjust the thing in the following way.
⏩ Right-click on the mouse or press CTRL+1
⏩ After opening the Format Cells dialog box, move the cursor on Protection and check the options.
Read More: How to Create a Two Variable Data Table in Excel (3 Examples)
4. Data Table Not Working When the Sheet Has Protection
If you are provided a data table with a protected sheet, you cannot do anything unless unprotect the sheet!
Follow the way to unprotect the sheet.
⏩ Move the cursor on Review Tab
⏩ Click on Unprotect Sheet
5. When Data Table Returns Same Value (Problems with Sensitivity Analysis)
Now, I am discussing common issues regarding sensitivity analysis in data tables, especially about What-if Analysis.
Look at the following picture where all the attributes become similar as the value of operating profit $1500 though the row and column input is correct.
What is the reason? Can you explain?
There may be many reasons.
⏩ You must check the calculation options from Formulas tab Calculations Options. Here, the option is Manual that’s why it returns the same result for all. So if you want to get the correct output you should click on Automatic.
⏩ When you put the value $1500 in the top-left part, make sure that it is linked with cell C10 (by clicking “=C10”).
Then the output will look like this.
6. Issues with Row and Column Inputs
You must be careful about row and column inputs while doing What-If Analysis as all the output is shown based on the inputs of row and column.
For example, if we put Price/AC as row input and Cost/AC as column input like the following picture.
The output with the wrong row and column inputs will look like this.
So, be careful when you are entering the row and column inputs. AC sold will be row input and Cost/AC will be column input.
Read More: How to Create One Variable Data Table in Excel (2 Examples)
7. Data Table Not Working for Residing on Different Worksheet
Sometimes, a data table may be located on a different worksheet. But in the case of sensitivity analysis, it is necessary to link the data on the same worksheet. Otherwise, you’ll not get the expected output.
Conclusion
This is how you can fix some common issues regarding Excel data table not working. I firmly believe this article will enrich your Excel learning journey. However, if you have any queries and suggestions, please let me know in the following comments section.
