Step-by-Step Guide 2:

Data Tables & Filters

Create a Data Table

Excel Data Tables allow you to easily filter and manage a dataset in Excel. Below you will learn how to create a data table in excel. You can use these tables to create a Pivot Table and easily visualise your data later.

Note: There are three different ways of doing the same thing. We will show you one of them as well as use the command keys on your keyboard.

Step-by-step Guide: Creating a Data Table using the menu

Select a cell within your dataset
Go to Home > Formate As Table
Now select your style from the dropdown menu.
A prompt will come up. Check your cell range (though this should usually be right).
Select that you have a header (this is the top row with your variable names in it).
Once you are happy. Select Ok.

Voila, you have created your first data table in Excel.

Step-by-Step Guide: Creating a Data Table using the Command Keys

Hover anywhere over your keyboard.
Select Control + T (on PC) or Command + T (Mac)
A prompt will come up. Check your cell range (though this should usually be right). Ensure you have selected the box that says you have a header (this is the top row with your variable names in it)
Once you are happy. Select Ok.

Voila, you have created your first data table in Excel.

You can change the style of your table by going to Table > Styles

Adding Rows/Columns to your Table

Sometimes you may want to add new variable or another row of data. This is easily done. Follow the steps below.

Note: The new Column can us functions to combined the values of other variables. You can learn more about functions in the next step-by-step tutorial

Step-by-Step: Add a new Column or Row

Adding a new column couldn't be easier.

Hover on the column where you want to add a new column
Right-click > Insert
Now decide if you want the new column to the left or right of the existing column.
Name the variable and add data.

Follow the above steps - but select Rows to add an additional Row

Filtering your Data

Step-by-Step Guide: Using Table Filters

Access the fliter by clicking on the little downward arrow on the variable you want to filter by.

Use the pop-up menu (image below) to use the various in-built filters
If you want to filter by catergory, just unselect (Select All) and select the categories you want to filter by
Now select Apply Filter

Your table should now only show the relevant data.

Turning your filters off

Select the variable the filter is applied to (you should see a small filter symbol - see image below)
In the pop-up window select Clear Filter

The table will now show the original data again.

Note: You can use the filter on numerous columns. If you do, it the filter will work in the order you have set them up. Any subsequent filter will filter from the subset you have created.

Use the inbuilttable filters to explore your data. Excel provides numerous options. You can fliter by category, range, or values above or below a selcted threshold.

Using Excel Formulas

Step-by-Step Guide: Excel Formulas

Excel makes calculations really simple using something called formulas. There are numerous formulas available. Below is just a basic introduction to formulas.

Formulas allow you to calculate Averages, Sums, get the Minimum, Maximum, Ratio, etc.

The basic keys you should remember are:

Divide /
Sum up +
Subtract -
Multiply *

Remember the computer does the math for you, you just need to figure out what calculations to use to create your new variable.

To start using a formula, all you need to do is type the = symbol into the cell, afer this you can type your formula as outlined in the video below.

There are many calculations available, to have a look at some of the basic ones follow this link here. The 25 most useful can be found here.

Others include the Min and Max functions.

Additional Options

Excel offers you many other options. Follow the link below to see additional options that will allow you to filter and play with your data.

Page updated

Google Sites

Report abuse