Today we are going to learn about “Excel Tables”. When I heard this term ‘excel table’ in the second lecture , I had quite a few questions in my mind. Some of them were:
- Is excel not a table itself? (I had an idea about tables in MS Word but not in MS Excel)
- Why to use tables in excel?
- Did we have tables in previous versions of excel as well? (I am using excel 2016 for the sake of this learning project and learning excel)
- What are the key features associated with excel tables?
- What are advantages of excel table?
- How it can help me in data analysis?
To help answer these questions, before I could focus on learning excel tables, I did a google search and found some very interesting things about excel tables. I found, “With the release of Excel 2007, Microsoft has introduced a new concept of working with tables of data. This new functionality is (not surprisingly) called “Tables”. In fact, Tables in Excel 2007 are the successor of Excel 2003’s “List” feature, with added functionality.”
Susan Harkins describes what are some of the advantages of Excel tables over data ranges and what are the handy things tables enable you to do. (The article is worth reading)
By now, we know why to use excel tables. Let’s start to explore how to use excel tables and when to use excel tables.
Previously, we did not have enough data to show the company’s performance from different perspectives. When it comes to reporting, it can be a lot more than just having the charts. Also, the data can be huge to analyze and to create reports. Here is what the scenario is:
Lucy (a sales manager) wants to know more about the year over year sales, sliced into different categories, sub-categories, and countries. She also wants to see additional information such as customer demographics.
This time the data has more than one hundred thousand rows. Following are the screenshots of the data source (start and end):
The first thing we will do is to convert the data into an Excel table. Then we will perform different functions on the table to derive different results.
To calculate total revenue for a specific month [ Text( ) ]: