MS Excel - Data Tables

Advertisements


Data Tables

In Excel, a Data Table is a way to see different results by altering an input cell in your formula. Data tables are available in Data Tab » What-If analysis dropdown » Data table in MS Excel

Protect Workbook

Data Table with Example

Now let us see data table concept with an example. Suppose you have Price and quantity many values. Also you have discount for that as third variable for calculating Net Price. You can keep the Net Price value in the organized table format with the help of data table.Your Price runs horizontally to right while quantity runs vertically down. We are using formula to calculate Net Price as Price multiplied by Quantity minus total discount (Quantity * Discount for each quantity)

Data table Example

Now for creation of data table select the range of data table. Choose Data Tab » What-If analysis dropdown » Data table. It will give you dialogue asking for Input row and Input Column. Give the Input row as Price cell(In this case cell B3) and Input column as quantity cell(In this case cell B4). Please see below screen-shot.

Data table Example

Clicking OK will generate data table as shown in below screen-shot. It will generate the table formula. You can change the price horizontally or quantity vertically to see changed in the Net Price.

Data table Example

Advertisements
Advertisements