Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Data tables in excel are used to compare variables and their impacts on the result and overall data, data table is a type of what-if analysis tool in excel and is present in the data tab in the what-if analysis, this tool asks for a row input and a column input table to make a data table and the impact is calculated by one variable or two-variable data table.
Data Table Excel (Table of Contents)
What is a Data Table in Excel?
- A Data Table will show you how by changing the certain values in your function you can change the result of the formula.
- It stores the results of many variable scenarios in one table so that you can finalize the best scenario for your business or project. The results are written in the table format.
- It is an array formula, which allows multiple calculations in a single location.
Types of Data Table in Excel
There are two types of data table.
- One-Variable Data Table
- Two-Variable Data Table
1) One-Variable Data Table in Excel
The basic requirement for the data table in excel is to create a base or testing model. You need to instruct your data table which formulas from your data model you want to test. The one-variable data table is best suited when you want to see how the eventual result changes when you change the input variables.
How to Create a Data Table in Excel?
Let’s understand how to create the table in excel with examples.
One-Variable Data Table – Example #1
Mr. Murali running a factory named ABC Pvt Ltd. In addition, he is estimating the revenue for the year 2019. Below table shows the revenue for the year 2018 and estimated revenue at different increment levels.
The above table shows revenue for the year 2018 is 15 lakh USD and expecting a minimum growth of 12% for the next year. Now, Murali wants a data table, which shows the revenue growth table at different increment rates. He wants the scenario table in the below format.
Apply Data Table in excel technique to get the desired results.
Step 1: Create this data table format in an excel file. Estimated revenue for 2019 is shown in the cell B5.
Step 2: Select the cell D8 and give a link to the cell B5 (estimated revenue cell). Now the cell D8 showing the estimated revenue for 2019.
Step 3: Select the range from C8 to D19.
Step 4: Click on Data Tab > What-if-Analysis > Data Table
Step 5: Data Table dialogue box will come up. In the Column Input Cell, select the cell B3 (which contains the minimum growth rate percentage).
Since it is a one-variable data table leave out Row Input Cell. The reason why we have selected the cell B3 because based on the different growth rates we are going to create the scenarios. Now, data table understands at 12% the estimated revenue is 15 lakh USD. Similarly, it will create scenarios for 12.5%, 13.5%, 14.5% and so on.
Step 6: Click on ok to create different scenarios.
Now the range D9:D19 showing some new values. From the table, it is very clear that @12.5% growth rate estimated revenue will be 16.875 lakh USD and @14.5% estimated revenue would be 17.175 lakh USD and so on.
This is how one variable data table example works. You can show this in a chart as well.
2) Two-Variable Data Table in Excel
We can use a two-variable data table to analyse scenarios if two variables change at a time. For this, we need two ranges of possible input values for the same formula. That means here it will affect both Row & Column.
Now go ahead and examine this two-variable data table example.
Mr. Murali was thinking of revenue growth at different rates. Similarly, he is planning to give discounts to his customers to increase the sales opportunities.
Two-Variable Data Table – Example #2
Below table shows the Murlali’s plan to increase the growth in the next year. He wants to estimate the revenue at different growth levels with different discount rates.
Step 1: Create an above Data table in excel.
Step 2: In the cell, B6 put the below formula to calculate eventual revenue after the discount.
=B2 + (B2*B3)-(B2*B4)
First, it will add the growth rate from the previous year and deduct the discount rate.
Cell D9 contains the reference to the cell B6.
Now, the above table shows the possible growth rate from D10 to D18 (Column Value) and possible discount rate from E9 to J9 (Row Value).
Step 3: Select the range D9:J18.
Step 4: Click on Data Tab > What-if-Analysis > Data Table
Step 5: Data Table dialogue box will come up. In the Column Input Cell, select the cell B3 (which contains the minimum growth rate percentage) and in the Row Input Cell, select the cell B4.
Together with the formula in cell D9 (refers to cell B6), Excel knows that it should replace cell B4 with 2.5% (cell E9), and cell B3 with 12.5% (cell D10) and so on for others as well.
Step 6: Click ok.
Now, if Murali is aiming at 13.5% growth with 2.5% discount rate then the revenue will be 16.65 lakh USD. If Murali aiming at achieving 17 lakh USD, next year maximum discount he can give is, 3% and it can give him the revenue of 17.025 lakh USD.
This is how helpful is Data Table in excel to create different scenario models to assist the decision-making process.
- Once you have calculated the values through the data table, you cannot undo the action (Ctrl + Z). However, you can manually delete all the values from the table.
- You are not allowed to modify a single cell in the data model. Because it is an array, you have to delete everything.
- It would be so confusing to select the Row input cell and the column input cell. You need to select the cells properly to get accurate results.
- Data table in excel does not need to be refreshed all the time, unlike the pivot table.
This has been a guide to Data Table in Excel. Here we discuss how to create Data Table (one variable and two variable data table along with practical examples. You may learn more about excel from the following articles –