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

You can also practice this through our practice files. Click on the below link to download the 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
  • On the Insert tab, in the Tables group, click “Table
Excel Tables - Complete & Advanced Guide 2024
 The Create Table dialog box will pop up.
  • Excel will automatically select the data range. Make sure to check “My table has headers” and then click OK.
Excel Tables - Complete & Advanced Guide 2024

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

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

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.
Excel Tables - Complete & Advanced Guide 2024

Now, you can format the range as needed.

9.8 Excel Tables

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
9.13 Excel table
  • Click the Borders drop-down and select No Border.
Excel Tables - Complete & Advanced Guide 2024

Your data is now free of any previous formatting and ready to be formatted into a table.

9.14 Excel Table

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.
Excel Tables - Complete & Advanced Guide 2024
  • Ensure the range is correct and check “My table has headers” if applicable. “Click OK” 
9.17 Excel Table

Your data is now formatted as a table with the selected style.

Excel Tables - Complete & Advanced Guide 2024

Header Row in Excel Tables

Hiding the header row in Excel keeps the “Header Row” option checked. It indicates if your data includes a header row, essential for sorting and filtering.
9.20 excel table

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.

Excel Tables - Complete & Advanced Guide 2024

Banded Rows/Columns in Excel Tables

In banded rows/columns, even and odd rows/columns are displayed with different colors. Banded rows mean each row alternates in color. In MS Excel, the banded column feature differentiates the formatting of even columns from odd columns.

Banded Rows Example:

9.22 Excel Table
9.32 Excel Tables

Banded Columns Example:

9.23 Excel Tables

Sorting and Filter in Excel Tables

You can easily sort and filter data in your table:
  1. Go to the Design tab.
  2. Ensure the Filter Button is checked.
  3. Sort and filter toggles will appear in the column headings.
  4. Click a toggle in any column heading to access the sort and filter menu.
9.24 Excel tables

Slicer in Excel Table

All tables have filter controls by default, but you can add a slicer for easier filtering with large buttons. To add a slicer:
  1. Go to the Design tab in the Table Tools menu.
  2. Click Insert Slicer.
Excel Tables - Complete & Advanced Guide 2024
9.26 excel slicer
  • Check the boxes for the columns you want slicers for (e.g., “Category“.
9.27 Excel Tables
  • Click OK: This will add slicers for the selected columns.
9.28 Excel Tables
  • 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

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:

  1.  Add a new column for “Yield Percentage” 
  2. Enter Formula in cell F2. Use columns “Initial Value” and “Final Value” write formula =(D2/E2) and press “Enter”
9.30 Excel Tables

You’ll have the yield percentage calculated.

9.31 Excel Tables
  • 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.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *