There are times when it is necessary to edit the data table. Sometimes we need to delete or edit the information in the table. Excel can be an extremely useful tool for editing data tables. Let’s take a look at how to edit a data table in Excel now.
What Is Data Table in Excel?
The Excel data table is a set of cells that are linked to the What-If Analysis tool. You can experiment with various input values and interpret the results appropriately. Single-variable and two-variable data tables are the two types of available data tables. In a 1 variable data table, you can work with a single variable, and in a 2 variable data table, you can work with two variables simultaneously.
How to Edit a Data Table in Excel: With Easy Steps
To explain the steps, we will use a dataset that contains information about a Company’s Capital, Growth Per Year, and Total Revenue. By using the Excel data table, we have figured out how Total Revenue changes with the change in Growth Per Year. In STEP 1, you will see the data table. Let’s follow the steps below to understand how it works.
STEP 1: Make Data Table Ready
- First of all, we need to make a data table.
- To do so, insert the data in an Excel sheet and then form a data table.
- We have used the range B4:C6 to insert the company’s information.
- Then we made the data table by varying the Growth Per Year information.
- Now we will edit this data table.
STEP 2: Select Data Table Range
- Next, select the data table range from where you want to edit data.
- As we want to edit the information regarding the Growth Rate from 11% to 15%, we will select the range F4:G10.
STEP 3: Remove Data Table Formula
- In the Formula Bar, we can notice the data table formula.
- We need to delete this data table formula from Formula Bar.
- Select the formula and press the Delete button to erase the data table formula.
STEP 4: Insert Specific Value
- Then, we will insert the value of our own choice.
- Suppose we want to show the value 70000 and so we will insert 70000.
- Now, press CTRL + ENTER.
STEP 5: Edit Data Table
- After that, the inserted value will be in all selected cells.
- Now, you can edit each individual cell of the data table according to your preferences.
How to Delete Data Table in Excel
Naturally, we can not delete any cell from the data table in Excel. So, we will give you an example of how to delete a data table. To complete the task we have used the same data set used before.
Let’s follow the steps below to learn how we can delete a data table in Excel.
STEPS:
- Firstly, take a look If you can delete any cell from the data table.
- But it is showing a warning message which is Can’t change part of a data table.
- To delete the data table, select the entire range of the data table.
- So, we have selected the range F4:G10.
- Now, press Delete on the keyboard, and the entire data table is deleted.
- Instead of pressing Delete from the keyboard, after selecting the range, we can right-click on the mouse.
- Then, a Context Menu will open up.
- From there select Delete.
- After that, another dialog box will pop up.
- We will select Shift cells left and press OK.
- Finally, the data table is deleted.
Download Practice Workbook
To practice by yourself, download the following workbook.
Conclusion
In this article, we have demonstrated the way to edit a data table in Excel. Furthermore, there is a practice workbook at the beginning of the article. So, go ahead and give it a try. To read similar articles, check out our website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.
Related Articles
- How to Create a Sensitivity Table in Excel
- One and Two Variables Sensitivity Analysis in Excel
- [Fixed] Excel Data Table Input Cell Reference Is Not Valid
<< Go Back to Data Table in Excel | What-If Analysis in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!