If you work on Excel, you may have encountered many times with data list. Usually, we spend lots of time summarizing and analyzing data lists.

Excel List is a way of data presentation in a cross-tabular format where each **Column** contains a different category and **Row** representing the respective set of values or vice versa. Like, Month and Sales are ‘*category*’ and Sales value or months like January or November are ‘*set of values or record*’.

Converting a list to an Excel table make data sets accessible to style, manage (add or delete row and column) and perform data summary functions like addition, counting numbers, finding the largest or smallest value in the data.

In this post, you will understand how to create an Excel table and using them to extract various information on a list.

The key topics which you will be covering here are:

While writing this I used **Office 365**, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.

## Create a list into an Excel Table

You have a data set with three columns (Year, Month and Sales) and a set of value for the last three years in rows.

Moreover, now let’s convert this into an Excel table following steps below:

**To create a list or data set into Excel Table**

- Go to worksheet ‘
**Query**’ of Excel working file*(Image instructions below)* - Select any cell of the list.
- Click
**Insert**on the Excel ribbon - Click the
**Table**button under the Tables group - A Create Table window detects list range reference
**=$C$4:$E$40**, edit if required, in the dialog box; - Confirm that the ‘
**My table has headers**’ (as require) - Click
**OK**

- The shortcut key to create an Excel table, is to click any cell within your data list and then press
**Ctrl + T**or**Ctrl + L** Excel table automatically select the adjacent columns

*(non-empty)*as part of the table.

## How to change Excel table style

As I said the Excel convert a list to a table with a default style *(Blue, Table Style Medium 2)*.

However it provide an Excel user with the freedom to select other predefined style format or create one of yours.

Moving ahead with our previous example, let’s change the default style or color format.

**To change Excel table style format**

- Go to worksheet ‘
**Query**’ of Excel working file*(Image instructions below)* - Select any cell of the list
- Click
**Design**under Table Tools Group - Click
**More**under**Table Styles** - Use
**live preview**just by hovering each style option - Select best style format
*(as require)*

We can format a Table with this quick style option of Excel. Use **New Table Style **option to create your custom color or style for table format.

The new Excel style format a data range into a format table and applying a Style; the branded rows remain in the same format even if it is sorted.

Many times, we also find that we have many data tables and you may want to remove the table format. Use **Clear** option to delete any format in the Excel table.

- The Excel return with default style or color converting the list into Table. You can quickly change the style or color format of the Excel table.
- Clear only delete the Table format but the list is still an Excel table.

## Sorting and Filtering the Excel table

A **sorting** can display data into a specific order. We can quickly sort data tables based on a name or numerical value.

**Filtering** a data set in the table gives an ability to display only selected data sets not all value in a data range. Filtering our data in the table is equally comfortable as sorting them.

Let’s check out this with our data sets; **Firstly**, you want to display data in such a manner that Sales data of the year 2017 is in the top rows.**Secondly**, to display the corresponding value of the year 2016 only, using the filter option of the Excel table.

### To sort and filter data in an Excel table

- Go to worksheet ‘
**Query**’ of Excel working file*(Image instructions below)* - Click on the
**drop-down**area of cell**C4** - Click on
**Sort Largest to Smallest**(*Check changes in the table)* - Click on the
**drop-down**area of cell**C4**again - Click on the
**Select All**checkbox to deselect everything. - Click on the
**2016**tick box - Click on
**OK**

Sorting ‘**Largest to Smallest’ **change a data table so that year display 2017, 2016 and 2015 respectively.

The data after filtering will returns all data related to the Year 2016 including Month and Sales.

Yes, one important thing that needs to cover.

**How to add a new row of data in the Excel Table?**

Let’s say that I click **D40**, which is the last cell in the table and then press **Tab** key. This step creates a new row, and it is within the table. Now, we can add respective value to each cell in the new row.

## Summarize data of Excel Table

One great way to summarize data within a table by Total Row box check.

Like if you want to add all numbers in a column, we can use this option, it adds the total row and give the option for other formulas automatically.

Using the same data set let’s understand this tool of Excel Table. Let’s suppose if we want to find the total average value of our data set, we can follow these steps.

### To find data summary *(Average)* using Excel Table

- Go to worksheet ‘
**Query**’ of Excel working file*(Image instructions below)* - Select any cell of the table
- Go to the Table Tools
**Design**tab **Turn on**the Total Row option- Select cell
**E41** - Click Drop-Down box
- Select
**“Average”**

This display the average sales value of the last three years in the bottom right corner *(rightmost cell)*.

The **SUBTOTAL function** is one of two functions that ignore data hidden by filtering. The function adjusts its return value dynamically depending upon the row is hidden or visible.

- The AGGREGATE function also ignore data hidden by filtering.
- Make sure the width of the Column is wide enough to accompany the summary value.

## Naming an Excel table

You may have noticed some differences between tables and normal data ranges *(list)*. It also displays the **Table contextual tab** of the Excel ribbon.

The one significant difference which you will notice as we learn further the Excel table in naming a table for reference. An arbitrary name you give to a table, in place of its reference as a range.

The ability to refer a table by its name instead of by referencing cell location. A given table name can be referred to as a part of a formula and can easily be located by name in the same way as that a named range identifies a range of cells.

Check out the next exercise by continuing with the same example:

**How to name an Excel table?**

- Go to worksheet ‘
**Query**’ of Excel working file*(Image instructions below)* - Click any cell of the table
- Select Table Tools
**Design**tab - Go to Table Name: Box and write “
**MonthlySales**” as a table name, - press
**Enter**

## The reference data table in an Excel formula

Now we have named the Table “**MonthlySales**” but what all this matter If we cannot make some analytical application directly. Apart from summarizing the data in the Excel table using the formula predefined by the Excel, it is important to highlight that we also get an ability to create our **custom formula** to analyze the data range of a Table.

So far you were using Cell references such as **D41**, or the range **$A$1:$C$37**, to designate which cells to use for evaluating an appropriate action or formula. Although these cell references are enough for an effective formula, it is hard to comprehend what cell refers to a data set.

So, it is more accessible to grasp a formula by assigning names to groups of cells and then referring them to our custom formula.

In this exercise, you will calculate commission at the rate of 7% of sales value that needs to be paid to a 3^{rd} party using **a reference to a cell name** for the formula.

### To refer a cell name in a formula

- Go to worksheet ‘
**Query**’ of Excel working file*(Image instructions below)* - Select
**J5**and enter**7%** - Name J5 as ‘
**Crate**’ using Name Box - Go to cell
**E6**name a new table heading “**Commission**”*(did you notice a new column in the table)* - Calculate commission using formula
**=[@Sales]*Crate**(E5*J5)*(did you notice formula autofill in column F)*

The table in column F automatically get filled by style format, and the formula also gets autofill in the table.

Excel recognizes **four constants** that refer to the same general area of a table:

| Refer to all cells in the table |

| All data within table excluding the header row at the top and any total or subtotal rows |

| Cells in the first row of a table |

| Total Row |

## Edit formula of Excel Table

Although, the disadvantage is that the same formula autofill to all the cell in the Excel table. However, if we want to edit the formula of a cell and want to add a different formula not the same to a given cell, we can do that as well in the data table.

Let’s suppose that Management decides that commission in 2017 will **reduce to 5%** only.

**How to name an Excel table?**

- Go to worksheet ‘
**Query**’ of Excel working file*(Image instructions below)* - Select
**J6**and enter**5%** - Name
**J6**as ‘**Crate17**’ using Name Box - Go to cell
**F5**enter formula**=[@Sales]*Crate17**(E5*J6) (*You will notice all value in commission column changes*) - Use undo action using
**Ctrl+Z**for Window or**⌘+Z**for Mac*(Now only F5 formula will change)* - Now
**drag or copy cell**from F5 to the end of the Year 2017

Now we have two different formula in the table, and two different rates now calculate the commission.

- The cell formatting would move along with the cells, and your alternating bands would be jumbled up.
*We can name each specific cell by using ‘***Name Box**’**.**Each cell names can be a reference for adding an Excel formula.