Excel can help you do lots of things quickly. Need to manage your own finances or your small business? Curious about how your profit margin will change? Then you need data tables. They are not hard to master. Read on to learn how to create data table in excel.
Data Table Analysis
“Data table analysis” is a hidden tool in Excel that can save you lots of time and effort. If you’re familiar with entering the same data manually over and over – like for expense reports – a data table will help.
Not all formulas are straightforward. Usually they require many different inputs. A data table is good for analyzing how different inputs might affect your results. If you want to analyze your data and figure out the best solution, a table in Excel can give you the context you need.
Create Data Table In Excel
Here’s how to start.
1. Create a Data Set
In order to create a data table, you first need to create a data set. Open Excel on your Mac or PC.
Create a table and enter the number of inputs you need. As an example, you might be a bookstore owner and need two inputs: the price of each book and the amount you sold, and a formula for revenue: revenue = price x quantity sold.
This is where it gets tricky. Book price and quantity sold are not always fixed variables. You don’t know for sure how many books you will sell in a certain week or month. The data table helps solve that problem. You can enter a few different quantities and prices and see how your profits change.
Let’s get into setting up that table.
2. Set Up Your Data Table
You will need to make a few changes to your data first.
Copy your formula to a different place – enter the cell number into a blank cell.
Next, tell Excel what values you want to test. List your prices to the right of the formula, and the quantities underneath it.
Add a bit of formatting if you want to, like borders, shading, or currency. There are shortcuts for that, too. For example, under “Page Layout,” you can change the color shading and font. On the “Home” tab, under “Number” , type “Currency” in the option box to add dollar signs.
Now that you have your table set up the way you like, we can get into the table analysis.
3. Data Table Example
Move your cursor over the table you just created to highlight it.
Under the “Data” tab, click on the “What-If Analysis” button. It’s the one with an image of a table and a question mark.
A pop-up will appear. You will need to fill it in this way: in the prompt for row input cell enter the row that you put in the initial price. In the prompt for column input cell enter the cell number where you put the initial quantity.
Press ok, and Excel will do the calculations for you.
The reference calculation is : cell C4 x cell C5.
4. Variable Entry: Two Variables, One Formula
The pop-up box that asks for row input cell and column input cell is asking for two different kinds of data. The first is the range of values for the Row Input Cell, which is across the first row of the table. The second is the range of values for the Column Input Cell, which is down the first column of the table.
It’s easy to get confused here. Just remember that the input numbers will be from your initial formula set up. It is also very important to select the cell range of the table you want to create before you enter the input numbers. If not, Excel will show you an error message.
5. Variable Entry: One Variable
When you only have one variable to input for the what-if analysis – like a percentage of profit, for example – leave the row input cell prompt blank and only enter your value in the column input cell box. Excel will still do the calculations for you, you'll just get a different kind of result.
6. Making Changes
If you want to make changes to anything in your new data table, you can go into the table itself and change quantities, prices, or other values. The table will automatically update with your changes. Important: note that this will apply to the entire range of the table.
The formula for this example to create data table in excel was very simple. But you can easily do more complicated calculations with Excel and it will give you similarly powerful results. Now that you know how to use the data analysis feature, you can get a lot more out of your spreadsheets.