Learn to Manage Data with Excel Table

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 we 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
Convert a data range to an Excel table
Convert a range of data or list to an Excel table
  • 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)
Use Design Quick Style to format a Table in Excel
Use Design Quick Style to format a Table

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 data table in Excel.
Sorting our data table by year
Filtering our data table in Excel.
Filtering our data by the Year 2016.

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.

Enter Tab key to add row in Excel Table
Add Row in the Excel Table

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”
Click Total Row and summarize data in Excel Table
Click 'Total Row' and find average of our sales

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 we 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
Renaming our data table in Excel
Renaming our data table for structure reference in formula

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 3rd 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)
Name Box for creating cell reference in Excel
Use 'Name Box' for creating cell reference
Reference Table and Cell name in formula Excel
Entering our formula using Structured References of Table and Cell.

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:


#All


Refer to all cells in the table (MonthlySales)

#Data

All data within table excluding the header row at the top and any total or subtotal rows (MonthlySales) refer the name box

#Headers

Cells in the first row of a table (Year, Months, Sales, Commission)

#Totals

Total Row (Row 41) with SUBTOTAL function

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
Edit formula of Excel Table
Edit formula of Excel Table

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.
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments