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+)
One variable Data Table in excel means by changing the one variable along with multiple options and get the results for multiple scenarios.
Table of Contents
What is One-Variable Data Table in Excel?
Data table is a very useful tool which is available in the forecast group of the what-if analysis tab, technically there are two types of data tables one which is two variable and one which is one variable data table, in one variable data table we analyze how a single variable impacts on the total table or data.
In this article, we are going to discuss one of the most powerful data analysis technique in excel.
Data Table is a What-if-Analysis tool, which shows the different scenarios for one module. A Data Table is nothing but creating scenarios at different alternatives. Our project, investments, plan depends on variables. It could be one or two. Similarly, in excel we have two types of data tables one is depended on a single variable and another one if depended on two variables.
In this article, I will demonstrate one variable data table excel
How to create One-Variable Data Table in Excel?
This is the first of those two data tables. One variable means single factor changes and the result also changes. For example, You are running a business and you want to see the scenarios at different growth levels.
You are expecting a revenue growth of 15% from the last year. Here is 15% is the variable. If your revenue is increased by 16% what would be the scenario if your revenue increased by 17% what would be the scenario and so on.
One Variable Data Table in Excel Example #1
You are taking the loan of Rs. 2, 50,000 for 2 years. You are in discussion with the loan officer concerning interest rate.
You need to analyse at different interest rates what is the monthly payment you need to pay to clear the loan. For the calculation purpose, take the base rate of 12% per anum.
Step 1: Calculate the monthly EMI using the PMT function.
Step 2: Now create a scenario table as shown in the below image.
Step 3: Select the cell E9 and give a link to the cell B6 (EMI Amount). Now the cell E9 showing the monthly EMI.
Step 4: Select the range from D9 to E22.
Step 5: Click on Data Tab then What-if-Analysis and Data Table
Step 6: Data Table dialogue box will come up. In the Column Input Cell, select the cell B5 (which contains basic loan interest rate).
One Variable Data Table in Excel, we always ignore either ROW input cell or Column input cell. It depends on our table structure. If our scenario table different interest rates are vertical then we ignore row input cell and if our scenario table interest rates are horizontal then we ignore column input cell. In this example, I have ignored Row input cell because scenario table different interest rates are in a vertical way.
Step 7: Now click on the OK button to create different scenarios.
Now the range E10:E22 showing some new values. From the table, it is very clear that @12.5% interest rate monthly EMI will be 11,827 INR and @13.5% interest rate monthly EMI would be 11,944 INR and so on.
This is how works one variable data table in excel. You can show this in a chart as well.
One Variable Data Table in Excel Example #2
Assume you are a sales manager of a company. From the management, you have received a monthly sales target of 1, 70, 00 USD from your team. Below table shows the sales target for 6 members. You need to analyze what should be their efficiency level to hit the target of 1.7 lakh USD in a month.
Your teams’ overall target is 2.04 lakhs. You are not sure at what percentage of efficiency they need to bring in to the table to achieve the target given by the management.
Your team can give a maximum of 90% efficiency level and you have calculated the total revenue at 90% efficiency level.
At 90% efficiency level your team can achieve the total revenue of 1.83 lakh USD in a month. You need to what should be the efficiency level to achieve the revenue target given by the management.
Create a scenario table as shown in the below image.
Step 1: Below the table, you need to create an excel file.
This table shows at different efficiency levels what will be revenue?
Step 2: Select the cell H3 and give a link to the cell B11 (at 90% efficiency level revenue cell). Now the cell H3 cell showing the 90% efficiency level revenue.
Step 3: Select the range from G3 to H12
Step 4: Now find the Data Table under What if Analysis section.
Step 5: Once you click on Data Table we need to give a link in the Column Input Cell, select the cell B10 (that contains efficiency percentage).
For Column input cell I have given the link to the cell B10 because based on the different efficiency level we are going to create the scenarios. Now, data table understands at 90% revenue will be 1.83 lakh USD. Similarly, it will create scenarios for 100%, 95%, 90%, 85%, 80% and so on.
Step 6: Click on OK to create different scenarios.
Now the range G3:H12 showing scenarios. Management has given the target of 1.70 lakh USD for this month. In order to achieve that much revenue, your team needs to perform at an efficiency level of at least 85%.
In this way, you can use Data Table to create different analysis and chose a suitable scenario to achieve the goals.
Things to Remember One-Variable Data Table in Excel.
- Once the scenarios have created, we cannot undo action. It will remain the same.
- We cannot even modify the cell values because it becomes an array formula excel and you need to delete everything not one by one.
- In one variable data table always leave out Row input cell if the scenario to be shown in the vertical form. If the scenario is to be shown in horizontal form then leave Column input cell.
- You can change the value in the main database to see the real-time results at different types of alternatives.
This has been a step by step guide to One-Variable Data Table Excel. Here we discuss how to create One-Variable Data Table in Excel using examples and downloadable excel templates. You may also look at these useful excel tools –