Analyzing related pieces of data is easier if you create and use a table in Microsoft Excel. A table allows you to separate data on one sheet, which you can then manage separately from the rest. Here’s how to create and use tables in Excel.
How to Create a Table in Excel
There are two ways to create a table in Microsoft Excel. The only noticeable difference is whether you want to apply a specific color style to the table.
Create a table with style
If you want to use a nice color scheme, follow this method to create your table.
Select the range of cells in your spreadsheet that you want to convert to a table and open the “Home”
Create a basic table
If the color of the table is not a problem, you can easily place a basic table. Still applies alternating colors to the rows, only in the default blue and white color scheme.
Select the range of cells you want to convert, open the “Insert” tab and click “Table” in the ribbon.
Confirm the cells and apply headers
If you use any of the above methods, a popup window will appear next. Here you can confirm or edit the cell range. If you plan to add more data to the table, you may want to add more rows or columns from scratch.
You can manually edit the range of cells in the box or drag your cursor through the area on your sheet while the window remains on the screen.
If you want to use your own header row for the table, check the box for “My Table Has Headers” and click “OK” when you’re done.
If you do that not check the box to use table headers, Microsoft Excel will assign them as Column 1, Column 2, and so on by default, which you can edit if you want. Note that if you have a header row but choose not to use the function, that row will be treated as data, which will affect your table filtering.
How to Customize Your Excel Table
Now that you have your table, you can adjust it. Select a cell in the table and you will see the “Table Design” tab appear above the ribbon. Open that tab and check out the following options.
Each table you create is given the default name Table 1, Table 2, and so on. You can give your table a more meaningful name, which is useful if you want to refer to it in your workbook. Type the name you want to use in the “Table Name” field.
Show or hide rows, columns, and buttons
In the center of the ribbon are checkboxes to display things like a total row, the first and last columns, and the filter button. Check the boxes for the items you want to display.
Whether you started your table with a particular style or just used the default, you can change it here. Use the arrows on the right side of the ribbon to view and select a color scheme.
How to manage your Excel table data
When you’re ready to put that Excel table to work, you have options to sort, filter, and search your table data. Click on the “Filter button” (arrow) next to the header of the column you want to use.
Sort your table
You have two quick and easy options for sorting at the top of the window: ascending and descending. Keep in mind that although you sort with a single column, the rest of the data in your table will shift. So you don’t just sort that column; you are also sort your table by that column.
Sorting is perfect for sorting text data alphabetically, numeric data by number, or time-based data chronologically.
Filter your table
While sorting your table is useful for viewing the data in a particular way, filtering is useful for recalling specific data. Below the sort options in the window, you have “Filters” (on Mac, this is the “Pick one” drop-down list.) As the options in the list vary depending on the type of data in your table, see “Date Filters,” “Number Filters,” or Text filters.
So if your table has dates, you can filter by time frames such as tomorrow, next week, or last month. If your table contains numbers, you will see options such as equals, is greater than, or below average.
After selecting a filter, you may need to enter some data in the box that appears. For example, if you select “Equals” for numbers, enter the value “Equals” and if you choose “Before” for a date, enter the “Before” date.
You can also choose to apply a quick filter. The data in that column is displayed in a box in the window. Simply check or uncheck the boxes for the data you want to filter.
If you have a large amount of data in your table, you can also use the search box to find what you are looking for. The results of the search are displayed in the filter pane immediately below. Search is basically a filter in itself.
In Windows, click “OK” to apply your filter. On Mac, the filters you select apply immediately to your table. When you are done with a filter, select “Clear Filter” to return your table to normal.
Sort or filter by color
In both the ‘Sort’ and ‘Filter’ sections of the window, you will see an option for ‘By color’. If you are applying a color to a cell or font in that table, this option allows you to sort or filter it.
Convert your table back to a range of cells
If you decide that you no longer want to use the table you created, you can easily convert it back to a range of cells. Select a cell in the table, open the Table Design tab and click “Convert to Range” in the ribbon.
A table in Microsoft Excel makes it easier to manage and analyze a range of related cells in your spreadsheet. So keep this handy feature in mind when viewing your next workbook. For large data sheets, you can also look into using a pivot table in Excel.