How to create, filter, and format tables in Excel

Tables give you options plain old spreadsheets can't.

Credit: Rob Schultz

What’s the difference between a table and a range of columns and rows on an Excel spreadsheet? How do I create and populate tables? And, once a table is created, how do we custom filter, format, and design those tables so they look professional in a report? We’ll show you how it's done. 

What’s the difference between a table and a range in an Excel spreadsheet?

There’s not much difference regarding the data. Both use columns (the database fields) and rows (the database records), and both can be sorted, filtered, calculated, custom formatted, and printed.

Tables, however, have many additional benefits that regular spreadsheets lack. The biggest plus is the option to use data from multiple tables to create queries and reports. For example, say that TABLE ONE (Corp) has the company name, address, city, state, ZIP Code, and phone number; while TABLE TWO (Products) has the company name, product number, product name, price, inventory, discounts, etc.; and Table THREE (Orders) has the product number, number of products ordered, price, extended price, sales, tax, and totals.

TABLE ONE (Corp) is “connected” to TABLE TWO (Products) by the unique field called “Company.” TABLE TWO is connected to TABLE THREE (Orders) by the unique field called “Product Number.” These are called “relationships” and save you from continually duplicating fields/data on three different spreadsheets (or tables). Because each company can have multiple products, and each product can have multiple orders, you really need three separate tables to accurately manage this type of data. We’ve provided this sample spreadsheet so you can follow along. 

download
Use this spreadsheet to practice creating, filtering, and formatting tables in Excel. JD Sartain

For deeper dives, check out my earlier articles on creating relational tables and creating relational databases.

How to create an Excel table

1. First, enter the field names in the columns across the top, and then enter some records/data in the rows under each column. You could also open a workbook that’s already created and populated with data, or download the spreadsheet table we provided above.

2. Next, place your cursor anywhere inside the range you want to convert to a table.

3. Select INSERT > Table.

4. The Create Table dialog opens with the table range pre-selected. If this is wrong, enter (or point) to the correct range; however, if correct, check the box that says My Table Has Headers, then click OK.

5. Excel converts the range into a beautiful color table with dark blue column headers and alternating shades of blue on every other row for an easy “at a glance” view of your data. 

01 create an excel table in two seconds JD Sartain / IDG Worldwide

Create an Excel table in two seconds

In addition, notice that the filter feature is automatically applied, so sorting by a specific field is accomplished by a few clicks of the mouse.

6. For example, if you want to see which ski resorts get the most snow, click the down arrow beside the column/field called Average Annual Snowfall (inches). Select Sort Largest to Smallest, and it’s done. You don’t even have to click OK.

Notice that Grand Targhee Ski Resort in Targhee, Wyoming tops the list with an annual snowfall of 500 inches. (And I can attest to that, because I have skied there many times. It also has the best powder skiing in the world.)

02 use the filter to sort query the table for specific information JD Sartain / IDG Worldwide

Use the Filter to sort + query the table for specific information

Multiple sorts are also easy. What if you want to know which ski resorts in Colorado have the most skiable terrain? Or the most vertical feet?

7. Click the down arrow beside State field. Uncheck Select All, then check Colorado, and click OK.

Notice that Excel shrinks the table so only the Colorado ski resorts are visible. Not to worry, the other ski resorts are still there. Once you change the State field back to Select All, the other ski resorts reappear.

8. Next, click the down arrow beside the field called Skiable Terrain. Select Largest to Smallest and it instantly sorts placing Vail at the top with 5,289 acres.

9. Now click the down arrow beside the field called Vertical Feet/Drop. Select Largest to Smallest again and it instantly sorts placing Telluride at the top with 4,425 feet.

03 sort by state then sort by skiable terrain vertical feet JD Sartain / IDG Worldwide

Sort by State, then sort by Skiable Terrain + Vertical Feet

10. Next, let’s add another field called Snowboards Welcome. Move your cursor to the blank column (L) at the far-right side of your table. Type the name of the new column in row 3 and notice that Excel adds the filtering and style format automatically.

11. It’s also easy to add new records. Just move your cursor to the bottom of your table and enter a new record on the next available row; for example, enter Snowbird on row 17 and Alta on row 18. Excel formats the style as you type.

NOTE: You can also add/insert or delete columns and rows using the HOME tab, Cells group, Insert or Delete buttons.

04 easily add new columns rows JD Sartain / IDG Worldwide

Easily add new columns & rows

How to custom-design tables to look professional in a report 

You can easily change the colors and style of your table with just a few mouse clicks.

1. Move your cursor anywhere inside your table.

2. Select HOME > Styles > Styles Table and choose a style design from the large drop-down graphical list.

3. You can select New Table Style from the same drop-down list and customize your table design using the features provided in the New Table Style dialog window.

4. You can also customize the individual cells. Just select HOME  > Styles> Cell Styles and choose an individual cell design from the large drop-down graphical list.

05 select a custom style format for your new table JD Sartain / IDG Worldwide

Select a custom style format for your new table.

Join the newsletter!

Or

Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

Tags Office 2013Office 2016

Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

JD Sartain

PC World (US online)
Show Comments

Most Popular Reviews

Latest Articles

Resources

PCW Evaluation Team

Jack Jeffries

MSI GS75

As the Maserati or BMW of laptops, it would fit perfectly in the hands of a professional needing firepower under the hood, sophistication and class on the surface, and gaming prowess (sports mode if you will) in between.

Taylor Carr

MSI PS63

The MSI PS63 is an amazing laptop and I would definitely consider buying one in the future.

Christopher Low

Brother RJ-4230B

This small mobile printer is exactly what I need for invoicing and other jobs such as sending fellow tradesman details or step-by-step instructions that I can easily print off from my phone or the Web.

Aysha Strobbe

Microsoft Office 365/HP Spectre x360

Microsoft Office continues to make a student’s life that little bit easier by offering reliable, easy to use, time-saving functionality, while continuing to develop new features that further enhance what is already a formidable collection of applications

Michael Hargreaves

Microsoft Office 365/Dell XPS 15 2-in-1

I’d recommend a Dell XPS 15 2-in-1 and the new Windows 10 to anyone who needs to get serious work done (before you kick back on your couch with your favourite Netflix show.)

Maryellen Rose George

Brother PT-P750W

It’s useful for office tasks as well as pragmatic labelling of equipment and storage – just don’t get too excited and label everything in sight!

Featured Content

Product Launch Showcase

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?