1. Business Ideas
  2. Business Plans
  3. Startup Basics
  4. Startup Funding
  5. Franchising
  6. Success Stories
  7. Entrepreneurs
  1. Sales & Marketing
  2. Finances
  3. Your Team
  4. Technology
  5. Social Media
  6. Security
  1. Get the Job
  2. Get Ahead
  3. Office Life
  4. Work-Life Balance
  5. Home Office
  1. Leadership
  2. Women in Business
  3. Managing
  4. Strategy
  5. Personal Growth
  1. HR Solutions
  2. Financial Solutions
  3. Marketing Solutions
  4. Security Solutions
  5. Retail Solutions
  6. SMB Solutions
Product and service reviews are conducted independently by our editorial team, but we sometimes make money when you click on links. Learn more.
Grow Your Business Technology

Excel Table Formatting and Filtering Guide

Excel Table Formatting and Filtering Guide
Credit: Shutterstock/Dennizn

Formatting tables in Excel helps you add functionality and aesthetic dimension to your spreadsheets. This guide will start with beginner formatting and move through intermediate skills.

When you convert a data range into a table in Excel, you not only get a nicer looking design, but you also add functionality. Data is easier to sort and filter in a table, and it's easier to maintain a consistent nomenclature.

Data entry is easier because formatted tables can be dynamic and change as new information is added. Copying formulas and selecting date ranges is also simpler in a table than it is in a raw spreadsheet.

With all that in mind, here's how you can turn your data ranges into tables.

 

We'll start with a basic Excel spreadsheet that depicts two weeks of tracking sales, operation costs and profits based on date.

 

To turn this data range into a table select it and click Format as Table. You'll immediately see a drop-down menu with many design options. All these designs function in the same way; it's only the colors that are different, so just choose what you like.

 

I've selected green. You'll notice that the top of each column now has a small arrow next to the header. These arrows are invaluable.

When you click on any of the arrows, you can access drop-down menus that make sorting simple. You can do basic sorting, or you can use specialized filters.

The type of filters you access in each column is dependent on the data category. For example, columns that are filled with numbers give you access to Number Filters, columns with text give you access to Text Filters and columns with dates give you access to Date Filters.

 

Here I've chosen to filter Profits based on Below Average. As you can see, the only visible rows are now those that contain below average profits.

 

To unfilter your table, click the icon next to the column you sorted and select the Clear Filter option.

Here are some other helpful built-in sorting features in each category:

  • Date Filters: These filters are robust. You can sort data based on day, month, year, week and quarter.
  • Number Filters: The most useful of these for entrepreneurs are Top 10, Below Average and Above Average.
  • Text Filters: Begins With, Contains and Does Not Contain are the most valuable preset text filters.

If none of the existing filters suit your needs, you can create a custom filter. We'll do that now using Dates.

 

Just like before, you click the arrow next to the column you want to sort, go through the Filters for that column, and select Custom Filter.

 

The Custom AutoFilter box that opens is incredibly intuitive to use. Use the drop-down menus to structure a sentence that describes the filtering you want.

For my table, I've chosen to select dates that are after January 5, 2016, and before January 10, 2017.

 

And you can see the results here.

If you want to become an Excel power user, enroll in an affordable online course now.

Mona Bushnell

Mona Bushnell is a New York City-based Staff Writer for Tom’s IT Pro and Business News Daily. She has a B.A. in Writing, Literature, and Publishing from Emerson College and has previously worked as an IT Technician, a Copywriter, a Software Administrator, a Scheduling Manager and an Editorial Writer. Mona began freelance writing full-time in 2014 and joined the Purch team in 2017.