There are two ways of organizing your data in Excel, either in a list format or by utilizing an Excel table. Both have their advantages and disadvantages. In today’s post, we will learn about the latter approach and see how to get the most out of them – i.e. Excel tables.
Tables are identified by a header row and a column, whereas in a list, there is only a header row. Tables are good when you want to summarize the information. The conventional table can be created by simply putting border lines around the data, but for a table to be an Excel table, you need to apply the table formatting to it using the ribbon menu or simply press CTRL+T as you have selected the data.
You can apply formatting according to your choice of color. This is the table produced by applying table formatting:
Using options in the Table Design Ribbon:
There are number of options available to format the layout of the table. Here are they listed with an explanation of each:
This will add the header row for the table. For our case it is months listed in Row #3.
Enabling this option will show the horizontal border lines in the table cells.
Enabling this option will show the vertical border lines in the table cells.
This will automatically add a total row to the bottom of the table – if you have added the total column then it will be added to the total column. More on this in later section of articles.
This option will turn First column Text bold.
This option will turn Last column Text bold.
Other Options Available:
The Excel table can be exported to SharePoint and the data can be refreshed from the option given. We can also refresh the range of the table or remove duplicates from the table.
Adding Subtotals to the Last Row:
Adding subtotals to the last row is easy – you can go to table design menu, select add total row and it’s done. By default, it is added to the last column to the right if you have added the total column; but it could be added to any column in the table.
The last row can display a variety of functions beside the default SUM function. We can utilize average, max, min and count functions as well for our tables.
There is even an option to get additional functions for the table, just click more functions to access all the functions.
Consolidating Data in Tables:
This will work even if the data is not present in tables, but works effectively if it is an Excel table, as you have already that subtotal function added to you data. In order for the consolidation option to work, you need to have multiple tables of similar format on different sheets. Just as shown in the following diagram:
The process starts by creating three tables, and a similar table for the summary. Then click on the ribbon and go to “Data” and select the consolidate option. A menu will come up asking for a few following options:
Function to be used:
This option lists the type of function you would like to use – there are around a dozen functions available such as: Sum, Count, Average, Min, Max, Product and so on.
Further Advantages of Using Tables:
With using data tables, it becomes easy to enter data as the table continues to expand as more data is entered. This is done with the help of a small blue box that appears in the last row of the table. As you reach the cell with this box and press Enter, a new row is automatically added.
- There is no need to drag down the formulas or flash-fill formulas, the formulas in the Excel table are automatically filled. We just need to enter in the top row and the rest of the column is filled automatically.
- There is no need for freezing panes when working with a table. With non-Excel tables, we need to freeze panes so that when we scroll, we can see the header row – but with Excel tables the header row is always visible.
- You don’t need to use the total row, as already described in the previous section. In fact you can use anything besides sum to summarize the sheet.
- There is a lot of ease in creating charts. If you are making tables the basis of the chart, the data in the chart will be updated automatically and there is no need for manually dragging ranges down.
Creating tables is an extremely useful and essential element within MS Excel. They are useful because they allow for better management of data along with simple calculation. Please download the file from this link and see how they actually work.