Tables in MS Excel
In MS Excel, Tables were formally known as lists in the menu based versions, but in recent versions, they’ve become more powerful and more significant. By converting a data range into a table, extends functionality and allow a user to work more efficiently and effectively.
Tables are generally used to analyze data in MS Excel in a fast and easy manner. In this tutorial, you will learn how to insert, filter and sort a table, and to display an auto generated total row at the end of a table.
Insert a Table
To insert a table, follow the steps below
1. Click any single cell inside the data set.
2. On the Insert tab, under Tables section, click Table.
3. MS Excel automatically selects the data range. In this tutorial, it has selected range from A1 to F11. Check ‘My table has headers’ and click OK.
The output figure below reflects that MS Excel has created a nicely formatted table. It seems like a normal data range but now it has a number of strong features associated with it.
After creating a table, whenever you will click on any cell within the table range, a Table Tools contextual tab (with the underlying Design tab selected) will be shown on the ribbon. It is the startup point for working with tables.
Sort a Table
To sort by Category first and Origin second, first sort by Origin, next sort by Category.
1. Click the arrow next to Origin and click Sort Smallest to Largest.
2. Click the arrow next to Category and click Sort Smallest to Largest.
Filter a Table
To filter a table, follow the step below.
1. Click the down arrow next to Origin header
2. Uncheck select all and then check only Pakistan.
Displaying a Total Row at the bottom of the table is very simple. To display a total row at the end of the table, follow the steps below.
1. Select any cell on the table.
2. On the Design tab, under the Table Style Options section, check Total Row.
The output is shown as the figure below.
3. You can change or add a Total (Average, Count, Max, Min, Sum etc.) against any column. For example, calculate the sum of the Amount column.
The final result is as follows: