Sagufta Tarannum
Using the table formula in Excel is a quick and easy method for referencing data tables and creating formulas inside them. The table formula in Excel is also known as a structured reference. To remove the Excel table formula, first, ensure that the dataset is in table format. This article will guide you to remove the table formula in Excel with some step-by-step procedures.
Download Practice Workbook
Download the practice workbook from here.
Introduction to Table Formula in Excel
Table formula or structured reference is a new sort of mathematical notation that we can use in Excel tables. It refers to the column names rather than specific cell locations. Besides, it enables you to make formulas that we can apply to the entire table at once. Moreover, the table formulas are very easy to copy and are more reliable than conventional formulas. Here, we can see a table formula in the C10 cell of the dataset below. When we selected the range C5:C8 in the formula, the column name is automatically inserted. This happens only when we use the table formula in Excel. Suppose, we need to remove the formula. Therefore, step-by-step procedures are below to do so.
Read More: Applications of Absolute Structured References in Excel Table Formulas
Step-by-Step Procedures to Remove Table Formula in Excel
Now, we can remove the table formula in Excel easily by following the steps below.
Step 1: Go to File Tab
- To remove the Excel table formula, first, open the Excel file.
- Next, go to the File tab that is at the top left corner of the Excel file.
Step 2: Open Excel Options Window
- After that, click on Options below to open the Excel Options window.
- Consequently, the Excel Options window will appear.
Step 3: Remove Table Names in Formulas
- In order to remove the table formula, select the Formulas option from the menu on the left of the Excel Options window.
- Subsequently, go to the Working with formulas section.
- Then, remove the tick mark from the ‘Use table names in formulas’ checkbox.
- Finally, click OK.
- At this time, if we select the range C5:C8 then we can see the range in the formula rather than the column name.
- In this way, we can discard the table formulas in Excel quickly.
Things to Remember
There are a few things to keep in mind concerning this setting.
- Here, no existing formulas with structured references will be altered. we can only affect the newly created or modified formulas by this.
- This is a setting for the application. This implies that it is applicable to all workbooks that you use. Your workbooks don’t travel with the setting. When a co-worker receives your file and the setting is open, their formulas will contain structured references. Additionally, they need to disable their computer’s ‘Use table names in formulas’ setting.
Conclusion
I hope the above methods will be helpful for you to remove table formulas in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.
