Select the data column that you want to count.Ģ. If you are familiar with the Pivot Table of Excel, it may do you a favor, please do as follows:ġ.

Excel will create a one-variable Data Table.Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari! Save 50% of your time, and reduce thousands of mouse clicks for you every day! The quantity is set out in the Row of the Data Table, and the input for it in the formula is cell B2.Ĭlick OK. Since the goal is to get a revenue Data Table for a fixed price, you only need to feed the Data Table the number of toy cars sold and not their prices. In the Data Table dialog, click on Column input cell and type in B2. Go to the Data tab, and from the Forecast, section click on What-If Analysis.įrom the What-If Analysis list, select Data Table. Select the table by clicking G1 and dragging it over to H7. For this example, enter 5, 10, 15, 20, 25, and 30 in cells G2 to G7. Select Cell H1 and in the formula bar, enter the formula below and press Enter:Įnter the numbers in cells G2 and below. Once you have the formula ready, it's time to set out the data. The formula for this example is the same as the previous one. To get started with creating the Data Table, you need to create a formula. Related: How to Copy Formulas in Microsoft Excel The method is still the same as the two-variable data table, albeit the positioning is slightly different. However, this time suppose that you want to get a table of potential revenue exclusively for the $50 toy cars. However, you can certainly have less than two: A Data Table with single variable input.įor this example, keep the previous example in mind. This means that you can't have more than two variable inputs in a Data Table. Since the Data Table is, after all, a table, it can only house inputs in a row and a column. This formula will multiply the price of sold units (A2) by their quantity (B2), and then it will subtract the tax value from it (A2*B2*C2).

Select cell D2 under Revenue and enter the following formula in the formula bar, and press Enter: In the Home tab, from the Numbers section, click the % symbol to change the Number Formatting of this cell to Percentage. In the Home tab, from the Numbers section, click the $ symbol to change the Number Formatting of these cells to Accounting (This is because these cells will house accounting values). In cells A1, B1, C1, and D1, type in Price, Quantity, Tax, and Revenue, respectively. So, all in all, for this Data Table, you'll have two variable inputs: The quantity and the price. To do this, you need to multiply the number of sold toy cars of a type by the price, then finally subtract the tax rate to achieve the revenue. For this example, suppose that you have six toy cars with different prices and want to know how much revenue you'll get by selling given quantities of them.

Frequency table in microsoft excel 2011