As we dive into this wonderful program, with its powerful tools, we are more amazed. This time we bring you an interesting topic. We will teach you how to create a two-variable data table in Excel. This is great if you want to see the behavior of your data. Well, let’s get familiar with its terms first.
Data table and variables
The data table is an excellent resource that Excel has, which allows you to change the value of certain cells, and observe how the result affects the rest of the table. In this way, it analyzes a set of values together with the “What if analysis” tool.
Variables It is used to save temporary data that is used within a process, module or workbook. The use of variables helps to make the work more efficient and summarized. When using them you have to comply with certain rules.
- The variable name is advised to start with a letter and not a number. If you can add numbers to the name, but the first character must be a letter. In Excel the cells are usually the variables, it would look like this: C8 .
- It is good that you do not use one of the Excel keywords as a name for the variable.
- It must consist of a continuous string of characters.
Taking this into account, and better understanding its usefulness, imagine that you acquired a loan to pay it off in a certain time at a variable interest rate. In the table, you determine the variables. In this case, it would be the time , because it will vary as you make the payment.
Example: Every 6 months you pay a part. In 12 months you pay another part … etc. Another variable would be the interests , because as time passes the interests vary. The matrix should reflect : Payments every 6 months, according to the current interest at that time. And you can see its behavior within 5 years. Time can be reflected in the columns. And the interests in the ranks. Now we will teach you the practice, how to create a two-variable data table in Excel .
Create data table with two variables
A good friend said: “With illustrations we understand each other better . “ Well, we will use a very simple example that will help you guide you in creating the table. We will create the multiplication table .
- You open a new Excel spreadsheet.
- Pick and select the cell to pivot . (It can be anyone)
- Enter the formula that will generate the multiplication. Example: A1 * A2. Here are our 2 variables. One of them will represent the axis of the columns and the other the axis of the rows. From this cell to its right you will create the columns, with consecutive numbers: 1,2,3… And below this cell, establish the rows with the numbers 1,2,3… etc. Until where? The limit is set by you.
- Then select the entire table with the mouse. That is, from the pivot cell that stores the formula, to the limits of the column and rows.
- Go to the top menu, activate the “Data” tab and tap the “What If Analysis” tool. (This is valid if you are in a previous version of Excel, if you have an updated version, searching for this resource is more direct and faster).
- Click on “Data Table” . A window will appear. In the row field, place a cell of the formula (Example: A1). In the column field, put the other cell (Example A2). And accept”. That’s it.
You will be able to see how throughout the table each cell stores a value (the result of the multiplication of an element in the column with another element in the row). There are cases in which you need to paste the data from a table in another location, but with certain criteria, for that it can be useful to learn how to use the special excel payment.
It is really simple; Practicing this resource will help you include and experiment with multiple more complicated formulas and elements. We hope you continue to enjoy the great mastery and handling that Excel has with tables. If you want to tell us about your experiences working in Excel, leave your comments.