Working with tables

1. Working with tables

In this video, we'll be diving deeper on how to work with your data in Excel and how to incorporate Excel tables into your workflow.

2. Tables in Excel

Despite following a tabular layout, by default data in Excel is not grouped together. It is essentially data within cells that are only related by proximity to one another. Tables let Excel know that all the data contained inside is related. We can think of a table in two key sections, with an optional third section. A table has column header row, which is the first row in a table that contains the column headings that identify each column of data. They must be unique, cannot be blank, or contain a formula. Next is the body of the table, this is where all the data and formulas live within the rows and columns. Lastly is the totals row. By default this is turned off, but can be switched on which would add a row to the end of the table. It can contain text, numbers, formulas, or remain blank. It can be a great way to summarize data within the table.

3. Benefits of using tables

There are many benefits of using tables in Excel but the two key benefits are the use of structured references and data integrity. Structured references work like a cell reference, but they are easier to read and understand. You can reference your data by a column name rather than trying to figure out which column the data is located in. Additionally, they are also dynamic and adjust automatically as data is added or removed. When using a table, you can utilize Excels built-in data validation features. This could mean applying rules to only allow certain data types within a column on a table.

4. Formatting your data

Excel has a ton of formatting features built in. But some key ones we'll walk you through in a later video including formatting tables and formatting cells. Within table formatting, you can easily re-size your table, apply slicers to filter your data, create pivot tables, remove duplicates, apply table style options, and apply table style designs. Within cell formatting there is a vast number of options, but some include customizing the fonts, alignment, and data types. Additionally you can apply conditional formatting, cell styles, and even style themes.

5. Filtering and sorting data

Filters and sorting are an important capability in Excel. Let's assume you have a dataset with 10,000 rows. Filtering enables you to easily show or hide data that isn't relevant to your analysis. It has many built-in filtering features including comparison operations such as greater than, less than, and top N records. Similarly, sorting can be really useful in your data analysis. It can help you quickly visualize and understand your data better. Sorting can make finding the data you're looking for easier, especially if you're looking for a particular name. You can sort by various types such as text, numbers, dates, and times. But sorting can also be applied to formatting such as cell color, font color, and icon sets.

6. Adventure Works

For the rest of this course, we'll be working with data from a fictional bike company Adventure Works that sells bikes, bike-parts, and accessories globally. We have a single table called Sales which contains information about every order and item purchased.

7. Adventure Works

Some of the key columns we'll use from this dataset include order number, item cost, item price, order quantity, order date, delivery date, product, and customer information such as name.

8. Let's practice!

You now understand the benefits of tables and why they are useful when working with related data in Excel. Let's practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.