What is Excel Tables?
Excel tables organize data into rows and columns with special features like unique names for columns, easy-to-use formulas, automatic formatting, and simple sorting and filtering. They make data management and analysis more efficient.
Table of Contents
Download Practice File
Creating Excel Table
Creating a table in Excel is easy. Follow these steps:
- Highlight the cells you want to include in your table. Make sure to include the headers.
![9.1 Excel Table](https://www.excelsirji.com/wp-content/uploads/2024/06/9.1-Excel-Table.png)
- On the Insert tab, in the Tables group, click “Table“
![](https://www.excelsirji.com/wp-content/uploads/2024/06/9.2-Excel-Table.png)
- Excel will automatically select the data range. Make sure to check “My table has headers” and then click OK.
![](https://www.excelsirji.com/wp-content/uploads/2024/06/9.3-Excel-Tables-1024x357.png)
Result: Excel creates a nicely formatted table for you. Although it may look like a regular data range, many powerful features are now just a click away.
![9.4 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.4-Excel-Tables-002.png)
Shortcut to Create Excel Tables
You can quickly create a table in Excel using a shortcut. Here’s how:
1. Select the range of cells you want to include in the table.
2. Press “Ctrl + T” on your keyboard.
3. In the “Create Table” dialog box, ensure the range is correct and check “My table has headers” if your data has headers.
4. Click “OK”.
Your selected range will be converted into a table with formatted rows and columns, and filter buttons added to the headers.
![9.5 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.5-Excel-Tables-1024x531.png)
Formatting Excel Tables
Step 1:- Convert Table into Range
Note:-Converting a table to a range in Excel removes table-specific features like filter buttons and structured references. However, your data stays in the same layout.
- Select Table: Click anywhere inside the table.
- Go to the Table Design tab (or Table Tools tab), and click Convert to Range in the “Tools” group. Confirm by clicking Yes.
![](https://www.excelsirji.com/wp-content/uploads/2024/06/9.7-Excel-Table-1024x519.png)
Now, you can format the range as needed.
![9.8 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.8-Excel-Tables-1024x294.png)
Step 2:- Clear Previous Formatting:
- Go to the Home tab.
- In the Font group, Font Color select Automatic, click the Fill Color drop-down and select No Fill.
![9.14 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.14-Excel-Tables.png)
![9.13 Excel table](https://www.excelsirji.com/wp-content/uploads/2024/06/9.13-Excel-table.png)
- Click the Borders drop-down and select No Border.
![](https://www.excelsirji.com/wp-content/uploads/2024/06/9.15-Excel-Tables.png)
Your data is now free of any previous formatting and ready to be formatted into a table.
![9.14 Excel Table](https://www.excelsirji.com/wp-content/uploads/2024/06/9.14-Excel-Table.png)
Step 3:-Formatting Excel Table
- With your data range selected, go to the Home tab.
- In the “Styles” group, click Format as Table.
- Choose a table style from the gallery.
![](https://www.excelsirji.com/wp-content/uploads/2024/06/9.16-Excel-Tables-1024x442.png)
- Ensure the range is correct and check “My table has headers” if applicable. “Click OK”
![9.17 Excel Table](https://www.excelsirji.com/wp-content/uploads/2024/06/9.17-Excel-Table.png)
Your data is now formatted as a table with the selected style.
![](https://www.excelsirji.com/wp-content/uploads/2024/06/9.18-Excel-Tables.png)
Header Row in Excel Tables
![9.20 excel table](https://www.excelsirji.com/wp-content/uploads/2024/06/9.20-excel-table-1-1024x423.png)
Total Row in Excel Tables
If you check or uncheck the “Total Row” option in Excel table design, it toggles the display of a row at the bottom of the table that shows predefined calculations like sums or counts for selected columns.
![](https://www.excelsirji.com/wp-content/uploads/2024/06/9.21-Excel-Table-1024x445.png)
Banded Rows/Columns in Excel Tables
Banded Rows Example:
![9.22 Excel Table](https://www.excelsirji.com/wp-content/uploads/2024/06/9.22-Excel-Table-1024x375.png)
![9.32 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.32-Excel-Tables-1024x354.png)
Banded Columns Example:
![9.23 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.23-Excel-Tables-1024x402.png)
Sorting and Filter in Excel Tables
- Go to the Design tab.
- Ensure the Filter Button is checked.
- Sort and filter toggles will appear in the column headings.
- Click a toggle in any column heading to access the sort and filter menu.
![9.24 Excel tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.24-Excel-tables-1024x720.png)
Slicer in Excel Table
- Go to the Design tab in the Table Tools menu.
- Click Insert Slicer.
![](https://www.excelsirji.com/wp-content/uploads/2024/06/9.25-Excel-Tables-1024x203.png)
![9.26 excel slicer](https://www.excelsirji.com/wp-content/uploads/2024/06/9.26-excel-slicer-1024x654.png)
- Check the boxes for the columns you want slicers for (e.g., “Category“.
![9.27 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.27-Excel-Tables-1024x538.png)
- Click OK: This will add slicers for the selected columns.
![9.28 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.28-Exce-Tables-1024x530.png)
- Click on any button in the slicer to filter the table based on that value. For example, if you click on “Electricity” in a Category slicer, the table will only show rows related to electricity.
![9.29 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.29-Excel-Tables.png)
This is all “How to add and use slicer in Excel Tables”
Yield% in Excel Tables
Here’s how to calculate yield percentage in Excel:
- Add a new column for “Yield Percentage”
- Enter Formula in cell F2. Use columns “Initial Value” and “Final Value” write formula =(D2/E2) and press “Enter”
![9.30 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.30-Excel-Tables-1-1024x232.png)
You’ll have the yield percentage calculated.
![9.31 Excel Tables](https://www.excelsirji.com/wp-content/uploads/2024/06/9.31-Excel-Tables-1.png)
- Select cells in column F2, right-click, choose “Format Cells,” and pick “Percentage.”
- Drag the fill handle from C2 down to apply the formula to other cells.
That’s it! You’ll have the yield percentage calculated for each row.
Conclusion
Excel tables are powerful tools for organizing and analyzing data. However, there are times when you may need to simplify your data. Converting a table to a range can remove unnecessary features while keeping your data intact and easy to work with. This flexibility allows you to choose the best way to manage your data for any situation
Hope you like this article, follow us to learn more about Excel tips.