Using Microsoft Excel as a Database

Use the Microsoft Excel program to manipulate databases. You can make filters to only get one unique person. In this video, we are going to learn how to use the Advanced Filter feature

Creating a Data Table in Microsoft Excel

Data tables are part of the What If analysis tools that allow us to change the value of some cells to see how it affected the original result. A data table analyzes a set of values and determines possible outcomes.

Data table of one variable

The simplest example of a data table is one that uses a variable for calculations. Assume the following scenario:

In this example I’m making a sales forecast for 2015 based on 2014 sales and expecting a growth rate of 3.2%. What I want to do is to know what the sales projection for 2015 if the growth rate was different. For this test I will place the growth rates that will be used as follows:

To create the data table I select the range of cells as shown in the previous image and then go to the Data tab, and in the Analysis group press the What-If button finally select the Data Table option.

The Table Data dialog box is displayed and in the text box Column Input Cell you must select the cell B2 that is the cell that contains the percentage of growth.

Clicking the OK button, adjacent cells will be filled with growth rates projected value corresponding to each of the sales rates.

Excel has created the data table in the range A4: B12 and thus can analyse different sales projections for different growth rate. Once you’ve finished analysing the information, if I try to remove some of the cells belonging to the range of the data table, Excel will display a warning message that you cannot change part of a data table. If you want to delete the data table you must first select the entire range before pressing the delete key. (Source: Excel Total)