When was the last time you used excel tables? Do you know how powerful excel tables are?

If you have never or not recently used excel tables, it could be because you have not realized how powerful they are in data analysis.

By reading below 7 excel table’s secrets, your answer to above question will always be recent.

1. Simple to create —Ctrl + T

There are 2 ways of creating an excel table: Select any cell on your data, Go to HOME tab, under styles, click format as table and then select table format. Finally, click OK on the “FormatAsTable” Dialogue box that appears. This is the most common method used but it is too long.

The simplest way to create an excel table is by use of a shortcut “Ctrl +T”. Select anywhere in your data then Ctrl +T and then click OK on Create Table Dialogue.

2. The best data source for Pivot tables

One of the biggest mistakes excel users make when using pivot tables is the failure to include in the pivot table analysis extra rows or columns added to their data source. This can be easily sorted by use of Excel tables.

Excel tables automatically expand to include the new rows and columns in the table saving time to check if you have included all data. All that is required is to hit refresh in and your pivot table is fully updated.

Furthermore, you can easily convert a table to a pivot table with just one click. Under Table tools in Design tab, Click Summarize with PivotTable. That’s All!

3. Use of Slicers to Filter table

Slicers are excellent tools for data visualization, especially creating dashboards. These work best when data is formatted as an excel table.

To easily filter table data using slicers, select the table, Under Design tab, Select insert Slicer

Select the column you want to create slicers for and then OK.

az

Excel creates a Slicer as shown in the image below which is an excellent way to filter tables:

4. Easily create Summaries

You can easily create a one-line summary on the table without writing any formula.

To do this, Under Design Tab, tick Total Row.

Click the total figure and you will find a drop down that has more summary types you can apply by just clicking on any of them.

5. Create Dynamically expanding Charts

Manually updating chart data range is hectic and prone to errors. With Excel table, your chart data range is automatically expanded.

To do this, Select Columns in the table to create a chart, then Insert the chart. As Excel tables automatically expand to include the new rows and columns in the table, so will the chart labels and values be expanded.

This is far much easier than using the indirect and offset functions to create a dynamic chart.

6. The quickest way to insert Running Totals

Running totals are important in data analysis as they show the overall progress. Excel tables structure enables a quick insert of calculated fields which is the best fit for running totals.

For example to add a running totals column in below sales data, just type below simple sum formula on a column adjacent to the last column in the table. When you hit the enter button, excel table instantly applies the formula to all other cells in that table creating a column that contains running totals.

This is possible because of the structured reference format of the formula, a feature unique in excel tables.

With just that one formula, you get below accurate running totals

7.  Excel table’s awesome data referencing

Most Excel users are used to cell referencing which is mostly hard to understand.

Excel tables introduced structured references which are easily explainable especially in a formula created outside the excel table.

Structured reference syntax = Table name [Column name]

For example, in below formula, you can tell the count range is data contained in products column which is in a table called sales.

=COUNTIF(Sales[Product],"Paper")

In case you need to drag your formula across, the easiest way to create a Column Absolute reference is to create a duplicate column name separated by a full colon.

=COUNTIF(Sales[[Product]:[Product]],G1)

Conclusion:

As you can see from above discussion, the whole aim of excel tables is to make a data analyst life simple.

So, does your life seem complicated?

Check out if you are using Excel tables and include them in your daily excel work.

For further reading check out this article: https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

Interesting Links

4 WAYS TO SUM DATA BY WEEK NUMBER