Excel Tables: Learn How To Make An
Excel Table In 60 seconds (Or Less)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Most Excel data is in the format of a table…

But if you tell Excel that a specific set of data should be considered a single table, you open up many new possibilities!

Excel tables let you do a lot of cool things with a specific set of data—without affecting the other information in your spreadsheet.

There’s a lot to cover, so let’s get started!

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

Tables can be a bit complex at times, so it’s a good idea to get some practice.

Download our free example workbook and you can follow along through the rest of the tutorial.

Download the FREE Exercise File

Download exercise file
Download free exercise file

Defining a table

To get started, we need to tell Excel which parts of our data constitute the table. Open up the example spreadsheet and follow along!

As you can see, we have a table of bike parts, prices, and discounts. We also have notes to either side of the table.

These notes aren’t important.

They just show you how an Excel table lets you work with data without messing up the rest of the things in your spreadsheet.

Kasper Langmann, Co-founder of Spreadsheeto

Defining a table in Excel is easy.

First, highlight the tabular data:

select-table

Note that we haven’t highlighted the notes, as we don’t want them to be part of the table.

Next, press the Table button in the Insert tab of the Ribbon:

insert-table

Once you’ve done that, you’ll see a window asking you to confirm your selection (or change it). It also asks if your table has header rows—ours does, so we’ll leave that box checked.

Hit OK, and you’ll see that the table has been formatted:

table-highlight

Header rows are filled with dark blue, and the rows of the table alternate between blue and white.

Each column also gets a sorting/filtering button, which we’ll use later on.

If you’ve created a table of the wrong size, you can use the handle in the lower-right corner to change it:

table-adjustment

Styling your table

Excel is more about form than function, but it does give you some options for making your tables look nice.

Click into any cell in the table, and you’ll see a new tab, called “Design,” pop up in the Ribbon:

insert-table

There are many useful options and features up here, so it’s a good idea to familiarize yourself with this tab.

Kasper Langmann, Co-founder of Spreadsheeto

If you’re not a fan of Excel’s default color scheme, you can easily change it in the Table Styles section.

Mouse over any of the options or scroll down for more; there are different colors, bold and fill patterns, and some very stylish dark color schemes:

table-styles

The Table Style Options section of the Design tab has numerous useful checkboxes.

table-style-options

Here’s a quick summary of what they do:

  • Header Row hides or displays the header row
  • Total Row inserts an automatically calculating total row at the bottom (we’ll take a look at how that works in a moment)
  • Banded Rows enables or disables the alternating bands of color
  • First Column makes the first column bold
  • Last Column makes the last column bold
  • Banded Columns applies banding to columns (it’s a good idea to use this or banded rows, but not both)
  • Filter Button enables or disables the sorting and filtering options in the header row

Adding new rows and columns

Adding new rows and columns to your table is easy.

To add a new row, click into the row below your table, and type:

new-table-row

Columns work the same way (though Excel will automatically generate a new column header for you, and you’ll probably want to change it).

You can also right-click on any cell and hover over Insert. You can insert new rows and columns into the middle of your table with the available options:

insert-table-rows-columns

To remove a row or column, use the handle on the bottom-right corner of the table to scale the table back, then delete that row or column.

You can also right-click and select Delete > Table Columns or Delete > Table Rows.

Sorting and filtering table columns

Now we’ll get into some of the cool things that you can do with tables. First, we’ll look at sorting and filter the columns.

This might not sound that useful, but remember that you can sort and filter a table without sorting or filtering anything else in your spreadsheet.

And that can come in handy.

Kasper Langmann, Co-founder of Spreadsheeto

If you’ve used Excel’s sorting and filtering capabilities before, you’ll be very familiar with this. All you need to do is click the downward-facing arrow next to the column name to bring up the sorting and filter menu:

table-sorting-filtering

From here, you can sort in ascending and descending order or by color.

You can also filter rows based on the values in a single column. Deselect anything you don’t want to appear in the table (but don’t worry, it’s not getting deleted; you can always bring it back by resetting the filter).

You can even use the search bar above the filtering options to find specific entries to select or deselect.

Try sorting the table now. Do the notes outside of the table get sorted too?

Kasper Langmann, Co-founder of Spreadsheeto

Auto-calculating data

This is where tables become really useful. Much like flash fill and auto fill, they take what you’ve done and extrapolate it to other cells.

We’ll start by creating a total row that automatically adds (or does other things to) your rows. Go back to the table in the example spreadsheet, then click on one of the cells in the bottom row.

When you do, you’ll see a new tab, called Design, in the Ribbon:

table-tools-design

To insert a total row, check the Total Row box.

total-row

You’ll see that we now have another row, labeled “Total,” on the table.

In our case, it came up with some strange results:

total-row-table

Excel didn’t total the prices—which would have been useful—but it did total the discounts, which gives us a misleading figure. Let’s change that.

First, click on the cell that’s displaying “42%,” then click the downward-facing arrow next to the sum of the discount percentages.

Then select Average:

total-row-average

That gives us a more reasonable figure. Now, to get the total price.

There’s nothing displayed in the total row under the prices, but if you select the cell, you’ll still see an arrow. Click on the arrow and select Sum:

total-row-sum

Now we have the total of the prices from our list, as well as the average discount, automatically calculated.

You’ll notice that there are many options beside Average and Sum—remember these for later! You never know when you might need them.

Kasper Langmann, Co-founder of Spreadsheeto

Excel isn’t limited to auto-calculating rows. It can auto-calculate columns, too.

And it’s very adept at figuring out what you want in your column, too. Let’s take a look.

We have the prices and discount percentages in our spreadsheet—why don’t we find the final prices, too?

Click into cell E4, and type the following formula:

=C4*(1-D4)

column-fill-prep

This formula will get us the final price after the discount is applied.

Hit Enter to see Excel work its magic:

column-fill

Excel not only calculated the result of this formula, but filled it into each row of the table and added the column to the table.

The column header isn’t very descriptive, and the numbers aren’t formatted as currency, but those are easy problems to fix.

And now we can use the total row to add the discounted prices:

new-column-sum

It might seem like a lot of steps, but once you get used to working with tables, you’ll be able to complete tasks like these much faster than if you hadn’t defined the table.

Kasper Langmann, Co-founder of Spreadsheeto

Tables:
More powerful than you might think

At first, Excel tables just seem like a way to make your data look nice. And while that’s always a good thing, it’s not that impressive.

But when you see how much time tables can save you when you’re working with a lot of data, you’ll start to realize how powerful they are.

Tables with hundreds of rows and columns can be instantly updated with new information, sorted and filtered, and reformatted in a few clicks. And that makes your job easier.

2019-09-02T13:40:07+00:00