- Excel is not just for budgets; it can be used for statistics and data science, too.
- With just a few tweaks to the features in Excel, you can be an intermediate user in no time.
- Excel allows you to create custom tables and filters easily.
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 simpler because formatted tables can be dynamic and change as new information is added. Copying formulas and selecting date ranges is also clearer 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 of these designs function the same way; only the colors differ, 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 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 very 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 after Jan. 5, 2016, and before Jan. 10, 2017.
And you can see the results here:
What are the best uses for an Excel table?
For decades, Excel has been the go-to application to work through and analyze large amounts of data. It can help you prepare your content, organize data projects, and create budgets and lists.
Excel can be great for education use, because you can use it for probability, statistics and data science. You can also use Excel for prototyping to collect observations, brainstorm and create hypotheses. It is capable of handling relational databases and can take data from various sources, including IBM, Oracle and Google BigQuery. Excel contains self-service portals that allow only designated users to access data, and it offers data visualization tools.
How do you name an Excel table?
Any time you create a file in Excel, it is called a workbook. Each workbook has its own tables. When you add a table to your workbook, it is given a default name, Table 1. Then, the next one is Table 2, then Table 3 and so on.
To change the table names, click the table. Then, go to Table Tools, then Design, then Properties and then Table Name. From there, you can highlight the table name and type in a new name.
A table name is limited to 255 characters. You cannot use spaces in the table name; you must use some other type of separator, such as an underscore (_) or a period. Each table name must be unique because Excel will not allow duplicate titles.
How do you disable a hyperlink in Microsoft Excel?
To remove hyperlinks from an Excel cell, highlight the cell that contains the hyperlink, and then do one of two things.
The first option is to click the Home tab that is on the Excel ribbon. Then, you can highlight the option that says Clear and then Remove Hyperlinks. This removes the hyperlink in the cell you have selected, but it does not change the formatting of the cell.
The second option removes the hyperlink and the formatting of the cell you have selected. Once you have selected the cell, right-click from the menu that pops up, and select the Remove Hyperlink option.
What is the best custom formatting option?
Creating a custom format in Excel is pretty simple. Before I tell you the best custom format option, I want to explain how to create one. No matter what type of custom format you want to create, you go to the same space to do so.
First, open the Format Cells dialog box. You can do this by highlighting a cell and right-clicking. Format Cells appears as an option in the list. Under the Number tab, in the list on the left side of the format box, you will see Special and Custom listed at the bottom of the list. You will use one of these for many of your custom format options.
You can create custom dates, numbers or percentages. In this space, you can change numbers into text, display days of the week, create numbers that are invisible, and turn decimals into fractions, along with many other options. Once you have decided exactly what format you want to customize, click the Custom selection in the list mentioned above. If there is already a custom format in the list, you can select it, or you can edit one that is presented for you.