Home / Business & Management / Big Data & Analytics / How to Use Microsoft Excel for Data Analysis / Excel Tables and Features
Excel Tables and Features
Learn more about Excel tables and their associated features.
Now, we’re going to look at the data. I remember Jack– I have a friend in IT that promised to send me some detailed data that I will be able to build the necessary reports from. So we’re going to dive right into the Excel I just got, and see how can I do the task with more success this time. So let’s switch to Excel. And this is what I got. It’s looked like a pretty big range of data. If I go down with control-down arrow, I see that there’s over 56,000 rows. If I go across, I see that I have from columns A to N.
And when I start looking a little bit at the data, I see that I have the data summarise to each day. I have a column for the year, because probably, I would like to do some analysis on the year, that’s good. I have the customer ID, the customer age, the gender for the count. I have some information about where the customer came from, country and state. I have information by product. And I have some numbers, like the cost of the sale, how much did it cost us, and how much revenue we’ve got. So it seems, indeed, that this is a totally different approach.
This is very detailed data, but I need to somehow analyse it in visualising to produce the results that we want. Now, if I remember, Jack mentioned something about Excel tables. I thought, and probably you think, that Excel is just one big table. So why would you need tables as an object in Excel? So there is a new feature in Excel that you see, it says there, tell me what you want to do. It’s in the top. Tell me what you want to do. This is something that was introduced to help people that are looking for something in Excel to just say what they are looking for, and it will tell them. So I’m just saying a table. I don’t know.
I’m going to set a table, and it comes as Insert Table. OK, let’s try that. Insert Table, I clicked on it. Opened a dialogue, says the data, where is it? And it’s guessed that it’s from A1 to N50,000, and it has headers. I agree to everything, so I click OK. And immediately, the data looks very different. So now, I’ll switch, for a moment, to my Excel head, and explain you to a little bit of what happened here. So this is an Excel table. How do I know? First of all, it looks different. It’s not like a regular range. Also notice that when I’m on this table, in the ribbon, there is a new area called Table Tools.
And I have a whole area here of specific features, which are features for the table. If I’m going out of the table, this disappeared. So this is very typical to all kind of objects in Excel, like charts, pivot tables, pivot tables that they see forward. So that when I’m parking on them, there is new content in the ribbon. So what else? There is this dropdown that I’m familiar with, that are part of filtering. So I can click the dropdown here. This is not specific to table, it’s available also even before this data was a table. But with this, I can actually start filtering the data. I can say, I want to see just data for 2016.
I can say that I only want to see data from a specific country. So I can uncheck and check one country, let’s say France. And in the bottom here, I see how many rows are left. Actually, now, I see that there are only 2,975 out of 56,518 rows that are filtered by those two columns, the year column and country column. There’s more things that I can do with this. For example, I see that I can add a total row. So I click on Total Row. When I go to the bottom, I can see that now, for each column, there is– and one is already provided for me, you see here is a total for this column.
But I can go to any column that I want and create all kinds of totals and aggregates. I can see the sum, which is normal for a column like this, for cost. If go to, let’s say, a date, I can say that for the date, I want the minimum, which would like the first date. And I see that the first date now is January 1st, 16, because I’m actually filtered on the year 2016. So it’s pretty cool, and I can see that I can use tables for filtering the data and creating summaries. But this is not all, and is also not everything that I needed for creating my report. What else can I do with the table?
I remember that one of the things that Lucy was asking me was to be able to create reports based on the customer ages, and not just age, like 25, 26, 27, but to bucket the ages in some meaningful buckets that I will be able to report on. In order to do that, I know that I need to create a new column that is going to be in the table and represent these customer buckets. So I can go here and say that I want to insert a new column to the table. I will give it a name. I will say that this will be customer, let’s say, bucket, or age bucket, or age group, or something like that.
And the usual approach that it will take to take, to do this kind of column, will be what is called a Nested If. Let me switch for a moment to my PowerPoint, and show you how will such a expression look normally. There will be a series of ifs nested within each other– if ages less than 31, then return this value. If not, if it’s less than 51, return another value, and so on. And then I have to close all this parenthesis. So I have a function within a function within a function for as many groups as I want. And then I have to close all of them, as it’s called.
And you also might notice, actually, this function doesn’t look exactly– this formula doesn’t look exactly like what you expect in normal functions. I will explain in a moment, when we go back to our table. So one thing is that, if you saw, that the moment– if I click Equals, and I go to the customer age, you will see that the expression that is coming is not referencing like you normally would, say, like equals E2 or G13, or something like that. It actually refers to the column by name. In this case, customer age. So this is one thing.
This is one of the behaviours of the table, and you will see that once I create the entire formula, it will be much more readable, because it uses column names. But because this is– as I said, this is Excel 2016, and not only Excel 2016 is a constantly improving Excel version, that I’m getting new features and new functions periodically, based on my subscription to Office 365, there is a new function that I can use called Equals Ifs. The Ifs function is a better approach to doing this kind of nesting. So using this function, I can do something like that.
If ifs, and then the first expression is the age is less than, let’s say, 20, then return the value less than 20 otherwise. And I don’t need to open a new if, I’m staying within the same function. Now, I say customer age less than 31, then I’m going to return that the age group is between 21 and 30. And so on and so forth, for as many buckets that I want. I’ll do a couple more. So here is between 31 and 40.
And let’s say, for now, I don’t want to bore you too much. So let’s say that if the customer age is greater than 41, I will call it greater than 41. So that’s the final thing. And that’s it. So when you look at the expression, it’s simpler, because it doesn’t have these nested functions one within the other. Doesn’t have so many closing parenthesis that I need to count, and make sure that they are all fine. And the other hand, I have the values here for each one of the users by their age. I can see, even if I open the dropdown here, I see that I have all these groups.
I have some error here, probably, that I will have to fix, because some of them are coming as if they don’t have a proper value. So maybe I did some mistake. I don’t want to dwell too much on that. So I do have my customer buckets, and now, the next thing I want to do is– remember, I have a table. And I using the table, I was able to philtre. I can also sort. For example, if I wanted to sort this. Yeah, I can sort it by age, see, my older customers on descending order. So I can philtre, I can sort, I can do summaries, I could do calculated columns.
But this is not getting into really the results I need to create reports to show to someone. This is a good way to know the data, to explore it a little bit, but I need to go to use a more advanced tool to create, actually, the necessary summaries, the necessary reports. And this is where I’m going to need another tool, which is the pivot table. And in the next module, the next video, we’ll go and explore the same data that we have here using a pivot table, and we’ll see that the pivot table, based on the table, with combination, with also something called a pivot chart, and another object called the slicer.
So there’s three of them together– pivot tables, pivot charts, and slices will give us the right combination to create dashboards and reports that will enable very flexible reporting. And I’m sure that Lucy will be happy using this combination of tools. Thanks. See you next time.
In this article, you’ll explore Excel tables and their associated features.
Excel tables
An Excel table is not just any range of data with headings. It is a specific object that unlocks additional table-specific features. Excel tables allow you to analyse your data quickly and easily.
There are many benefits to using Excel tables because Excel recognises that each column is a separate field:
- Tables are easily formatted
- Table headers remain visible even when you scroll down
- Filters are added to your data
- Tables automatically expand when you add new rows or columns
- Tables automatically name ranges
- You can add totals to tables automatically
- When tables automatically expand, they also expand their format
- Tables drag formulas down automatically
- You can create dynamic charts
- Tables adjust named ranges automatically
- You can add forms to facilitate entry of new data
- You can use slicers and timelines to filter your data and charts.
Please take some time to review the following resources. They’ll provide you with an overview of Excel tables and an in-depth look at different Excel table functions which will make using Excel tables easier.
Click on these links to be taken to the resources.
- Overview of Excel tables
- Using structured references with Excel tables
- How to use the SUBTOTAL() function
- The IF function
- Advanced IF functions
Join the discussion
Did you, like Dany, think Excel was ‘just one big table’ or were you aware of Excel tables and its functionalities? Based on what we’ve covered so far, what do you think is the most useful feature of Excel tables for reporting business data and why?Use the Discussion section below and let us know your thoughts. Once you’re happy with your contribution, click the Mark as complete button to check the step off, then move on to the next step.
This article is from the online course:
How to Use Microsoft Excel for Data Analysis
Created by
This article is from the free online
How to Use Microsoft Excel for Data Analysis
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
