But you could do the same on something much more complex, and Excel will give you the perfect answer in no time.Optional: You can download this example for extra practice.
With just a few clicks, we can get Excel to do some magic computation and give us interesting information.įor this example we had a simple formula. You cannot link the data table to cells on a different tab.
Limitationsįor your information, using data tables have some drawbacks. For example it shows that selling 50 copies at $49 will make us $2,450. Now we can see exactly how much revenue we will make based on the different combination of inputs. Excel did the math for us, and automatically updated the spreadsheet with all the numbers we wanted. Column input cell": we listed the quantity in the left, so the "column input" is the quantity, which is in C3.Row input cell: we listed the price at the top, so the "row input" is the price, which is in C2.There select "data table".Ī popup will appear that you need to fill like this: Select the new table we just created, and in the "data" tab, click on the "what if analysis" button. Our goal is to fill this table with the profit for each combination of price and quantity. To the right of the formula we list all the prices ($19, $29, $49), and below the formula we list all the quantities (25, 50, 100).Īnd we can add some basic formatting (borders and currency) to make things look slightly better. Then we need to tell Excel what values we would like to test. You can do this by simply typing =C4 in a blank cell. Nobody likes doing repetitive tasks manually, so let's try the second option -) The Set Upīefore we start using the data table, we need to do some changes to our data.įirst we need to copy the formula to a separate place. Do that automatically with the data table feature.Manually change the 2 inputs, and see how the result change.For example: how much profit will we make if we sell 150 copies instead of 100, and what if the price is $19 or $49? But we're actually not sure, so we'd like to know the revenue for different combination of price and quantity. The data above assume we know the book price and how many copies we will sell.
If we sell 100 books at $29 each, we will make $2,900. Here's a table with 2 inputs (book price and quantity sold), and a formula (revenue = price * quantity). To use the data table feature we will need some data. This is extremely useful to analyze a problem in Excel and figure out the best solution. The data table is perfect for that situation. Sometimes a formula depends on multiple inputs, and you'd like to see how different inputs values would impact the result. And the "data table analysis" is one of them. There are some Microsoft Excel features that are awesome but somewhat hidden. Clear Excel Tutorials home about Excel: The What If Analysis With Data Table
0 kommentar(er)
