Free Online Tutorials on Spreadsheets

Data Tables in Excel

If you have a final value based on different variables, the Data Table feature will help to see the impact of changing variables on the final value. Instead of testing each variable individually, the data table feature will let you see the impact at glance without even changing original data.

For example, suppose you sell a vehicle for $100,000. Payment will receive after 15 days in the customer’s home currency, which is being traded at 1 Dollar = 0.77 Pound Sterling. Considering the current exchange rate, the total price of the vehicle will be 77,000 GBP.

Current Scenario

Now, what if the exchange rate fluctuates, how would you see the impact of change on the selling price in GBP? The easiest way is to use the data table feature.

1- Select the data table range.

2- In the Data tab, select What-If Analysis from the Data Tools section.

3- A drop-down menu will appear, select Data Table.

4- In the Data Table dialog window, click in the Column Input cell box and select the variable cell referenced in the formula. Press OK.

5- Result.

Next Lesson: Data Table in Excel – Setting up Two Variables

Copyright © 2016 - 2019 | All Rights Reserved