Data tables: what is a table in Excel and how to create them

What is a table in Excel? Data tables form part of one of the tools main functions. In fact, did you know that this is one of the most powerful features of the application? However, its use is not as widespread as could be expected.

The reality is that it helps us to understand results according to a range of variables, predicting or estimating figures based on hypothetical data and extracting valuable information, even when making decisions. So, anyone who knows what a table is in Excel and how to handle them is already one step ahead.

That's why it should come as no surprise that, in a company, for example, the departments of finance and accounts, sales, commerce and even production, among others, often use this function to access certain data that illustrate results in line with the figures being input. The thing is, in today's workplaces, prediction is a key factor that allows us to get ahead of the competition, and here, Excel data tables can be very useful.

What is a data table in Excel?

Excel data tables are the main feature used in the tool to consider different hypotheses. We're dealing here with a set of cells whose data is organised and connected to one another.

The main aim of what is a table in Excel lies in offering information that responds to certain analytic commands, which we must plug in ourselves, in advance.

What is a data table in Excel for?

So, why use data tables in Excel to extract this information? Well, this is the best tool to understand results with, because it lets you enter, analyse and understand which variables have been changed to generate said result and, in turn, understand the reasons behind this change.

Likewise, its usefulness lies in formulating hypotheses based on different variables that yield disparate results. In other words, if you need to make an investment, but you aren't sure of the budget available, you can explore various options to thus determine the sums you can reach in each situation.

How can a data table be created in Excel?

When it comes to creating what is a data table in Excel, you should first include all the basic information in two columns: a quantitative one, and one that defines or identifies what each quantity refers to. Next, in another range, you will have to plug in all of the potential variables, select the Data Table option, and pick which commands the system should respond to.

Main features of what is a table in Excel

The main elements that make up an Excel data table are the rows, which are set out in columns and identify the concept, and the data, which can come in different types and formats, and are included in the adjacent rows. Meanwhile, the commands and calculations performed to obtain certain results based on the input variables are hidden.

How to fill in data tables in Excel

The way to fill in what is a data table in Excel will depend on the number of variables you want to include. However, the basic data providing sufficient information needs to be included at all times. From there on, you can enter the variables you need in another column, to obtain the results in each case.

Excel data tables with one variable

Going back to the previous example, single variable data tables in Excel offer different results according to hypothetical changes made to specific data. For example, if you are considering the budget you need to allocate to an investment, you will have to enter the cost of the investment, the available budget, and how many purchases you will make.

Once all of the information is entered into the table, you need to create another with the variables you want to interpret. If the idea is to understand how many purchases you can make with your budget, this should be the variable you enter next to the quantities. To fine tune it, you enter information in the Data Table field inside the What-If tool, which is under the Data tab.

Excel data tables with two variables

Although from the outset it may seem much more complex, in reality, this involves repeating the same operation, albeit expanding the number of columns. To create two-variable data tables in Excel, just add another variable to the columns section of the same tool, and add as many cells to the range as you need.

In this way, and following on from the previous example, if you want to predict hypothetical changes to the price of potential investments you may make, just include the various scenarios that may arise. How? By adding a percentage increase or decrease over the base price you entered in the first step.

How to add a formula to a spreadsheet in Excel?

To add formulas to Excel data tables, you need to select the data in question in the headings of the rows and columns and then enter the chosen command for the main cell in the Data tables field. Then, so that the formula will spread to the rest of the table, drag it from this cell to the others.

What is a table in Excel: data table applications for the personal and professional spheres

As you can see, data tables in Excel are often used in business - especially in departments such as accounting and sales - to forecast results depending on different scenarios that may arise. Meanwhile, in the personal sphere, they can be used in the same way, albeit to calculate savings and for financial management purposes. 

Why is it useful to know how to use Excel data tables?

Excel data tables are the perfect example to demonstrate that certain acquired knowledge boosts the so-called soft skills. Is it truly possible that mastery of one function of a tool can improve your soft skills? If said function helps you to carry out your work more efficiently, then yes, the end result offers a formidable blend of skills.

The fact is that, in the workplaces of today, lifelong learning is becoming one of the key ways to achieve success in the professional domain. Do you want to find out what else Excel is for? Discover Santander Open Academy, the training space you need to keep growing.

Join our global platform for learning and professional development and access courses at zero cost, training content in a wide range of formats and scholarships from leading universities and institutions.

If, like us, you believe that we should never stop learning, sign up here and find out what we have for you!