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.
How to Create Basic Data Tables
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.
How to Use Existing 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.
How to Create a Custom Filter
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.