How to Create Excel Table

How to create excel table? Creating table in excel is one of the most common things. But when you are creating table in excel, you have to choose an ideal design, so people can easily understand table’s content.

Though the process of making a table in an Excel spreadsheet has many similarities to making a table in Word, there are some differences. Lets find out how to make a table in excel sheet.

You might be under the impression that the data in your worksheet is already in a table simply because it’s organized in rows and columns. However, the data in a tabular format is not a true “table” unless you’ve specifically made it such.

Excel table is a special object that works as a whole and allows you to manage the table’s contents independently from the rest of the worksheet data.

The screenshot below contrasts a regular range and the table format:

The most obvious difference is that the table is styled. However, an Excel table is far more than a range of formatted data with headings. There are many powerful features inside:

• Excel tables are dynamic by nature, meaning they expand and contract automatically as you add or remove rows and columns.
• Integrated sort and filter options; visual filtering with slicers.
• Easy formatting with inbuilt table styles.
• Column headings remain visible while scrolling.
• Quick totals allow you to sum and count data as well as find average, min or max value in a click.
• Calculated columns allow you to compute an entire column by entering a formula in one cell.
• Easy-to-read formulas due to a special syntax that uses table and column names rather than cell references.
• Dynamic charts adjust automatically as you add or remove data in a table.

For more information, please see 10 most useful features of Excel tables.

How to Create Excel Table

Part 1: Creating a Table

1. Open your Excel document. Double-click the Excel document, or double-click the Excel icon and then select the document’s name from the home page.
   • You can also open a new Excel document by clicking Blank Workbook on the Excel home page, but you’ll need to input your data before continuing.
2. Select your table’s data. Click the cell in the top-left corner of the data group you want to include in your table, then hold down ⇧ Shift while clicking the bottom-right cell in the data group.
   • For example: if you have data in cells A1 down to A5 and over to D5, you would click A1 and then click D5 while holding ⇧ Shift.
3. Click the Insert tab. It’s a tab in the green ribbon at the top of the Excel window. Doing so will display the Insert toolbar below the green ribbon.
   • If you’re on a Mac, make sure you don’t click the Insert menu item in your Mac’s menu bar.
4. Click Table. This option is in the “Tables” section of the toolbar. Clicking it brings up a pop-up window.
5. Click OK. It’s at the bottom of the pop-up window. Doing so will create your table.
   • If your data group has cells at the top of it that are dedicated to column names (e.g., headers), click the “My table has headers” checkbox before you click OK.Advertisement

Part 2: Changing the Table’s Design

1. Click the Design tab. It’s in the green ribbon near the top of the Excel window. This will open a toolbar for your table’s design directly below the green ribbon.
   • If you don’t see this tab, click your table to prompt it to appear.
2. Select a design scheme. Click one of the colored boxes in the “Table Styles” section of the Design toolbar to apply the color and design to your table.
   • You can click the downward-facing arrow to the right of the colored boxes to scroll through different design options.
3. Review the other design options. In the “Table Style Options” section of the toolbar, check or uncheck any of the following boxes:
   • Header Row – Checking this box places column names in the top cell of the data group. Uncheck this box to remove headers.
   • Total Row – When enabled, this option adds a row at the bottom of the table that displays the total value of the right-most column.
   • Banded Rows – Check this box to color in alternating rows, or uncheck it to leave all rows in your table the same color.
   • First Column and Last Column – When enabled, these options make the headers and data in the first and/or last columns bold.
   • Banded Columns – Check this box to color in alternating columns, or uncheck it to leave all columns in your table the same color.
   • Filter Button – When checked, this box places a drop-down box next to each header in your table that allows you to change the data displayed in that column.
4. Click the Home tab again. This will take you back to the Home toolbar. Your table’s changes will remain.Advertisement

Part 3: Filtering Table Data

1. Open the filter menu. Click the drop-down arrow to the right of the header for the column whose data you want to filter. A drop-down menu will appear.
   • In order to do this, you must have both the “Header Row” and the “Filter” boxes checked in the “Table Style Options” section of the Design tab.
2. Select a filter. Click one of the following options in the drop-down menu:
   • Sort Smallest to Largest
   • Sort Largest to Smallest
   • You may also have additional options such as Sort by Color or Number Filters depending on your data. If so, you can select one of these options and then click a filter in the pop-out menu.
3. Click OK if prompted. Depending on the filter you choose, you may also have to select a range or a different type of data before you can continue. Your filter will be applied to your table.

How to make a table with a selected style

The previous example showed the fastest way to create a table in Excel, but it always uses the default style. To draw a table with the style of your choosing, perform these steps:

1. Select any cell in your data set.
2. On the Home tab, in the Styles group, click Format as Table.
3. In the gallery, click on the style you want to use.
4. In the Create Table dialog box, adjust the range if necessary, check the My table has headers box, and click OK.

Tip. To apply the selected style and remove all existing formatting, right-click the style and choose Apply and Clear Formatting from the context menu.

How to name a table in Excel

Every time you make a table in Excel, it automatically gets a default name such asTable1, Table2, etc. When you deal with multiple tables, changing the default names to something more meaningful and descriptive can make your work a lot easier.

To rename a table, just do the following:

1. Select any cell in the table.
2. On the Table Design tab, in the Properties group, select the existing name in the Table Name box, and overwrite it with a new one.

Tip. To view the names of all tables in the current workbook, press Ctrl + F3 to open the Name Manager.

How to use tables in Excel

Excel tables have many awesome features that simply calculating, manipulating and updating data in your worksheets. Most of these features are intuitive and straightforward. Below you will find a quick overview of the most important ones.

How to filter a table in Excel

All tables get the auto-filter capabilities by default. To filter the table’s data, this is what you need to do:

1. Click the drop-down arrow in the column header.
2. Uncheck the boxes next to the data you want to filter out. Or uncheck the Select All box to deselect all the data, and then check the boxes next to the data you want to show.
3. Optionally, you can use the Filter by Color and Text Filters options where appropriate.
4. Click OK.

If you don’t need the auto-filter feature, you can remove the arrows by unchecking the Filter Button box on the Design tab, in the Table Style Options group. Or you can toggle the filter buttons on and off with the Ctrl + Shift + L shortcut.

Additionally, you can create a visual filter for your table by adding a slicer. For this, click Insert Slicer on the Table Design tab, in the Tools group.

How to sort a table in Excel

To sort a table by a specific column, just click the drop-down arrow in the heading cell, and pick the required sorting option:

Excel table formulas

For calculating the table data, Excel uses a special formula syntax called structured references. Compared to regular formulas, they have a number of advantages:

• Easy-to-create. Simply select the table’s data when making a formula, and Excel will build a structured reference for you automatically.
• Easy-to-read. Structured references refer to the table parts by name, which makes formulas easier to understand.
• Auto-filled. To perform the same calculation in each row, enter a formula in any single cell, and it will be immediately copied throughout the column.
• Changed automatically. When you modify a formula anywhere in a column, the other formulas in the same column will change accordingly.
• Updated automatically. Every time the table is resized or the columns renamed, structured references update dynamically.

The screenshot below shows an example of a structured reference that sums data in each row:

Sum table columns

Another great feature of an Excel table is the ability to summarize data without formulas. This option is called Total Row.

To sum a table’s data, this is what you need to do:

1. Select any cell in the table.
2. On the Design tab, in the Table Style Options group, put a tick mark in the Total Row box.

The Total row is inserted at the bottom of the table and shows the total in the last column:

To sum data in other columns, click in the Total cell, then click the drop-down arrow and choose the SUM function. To calculate data in a different way, e.g. count or average, select the corresponding function.

Whatever operation you choose, Excel would use the SUBTOTAL function that calculates data only in visible rows:
Tip. To toggle the Total Row on and off, use the Ctrl + Shift + T shortcut.

How to extend a table in Excel

When you type anything in an adjacent cell, an Excel table expands automatically to include the new data. Combined with structured references, this creates a dynamic range for your formulas without any effort from your side. If you don’t mean the new data to be part of the table, press Ctrl + Z. This will undo the table expansion but keep the data that you typed.

You can also extend a table manually by dragging a little handle at the bottom-right corner.

You can also add and remove columns and rows by using the Resize Table command. Here’s how:

1. Click anywhere in your table.
2. On the Design tab, in the Properties group, click Resize Table.
3. When the dialog box appears, select the range to be included in the table.
4. Click OK.

How to convert an Excel table to the range of data

After you have created your Excel table, you cannot convert your table into the range of your data in Excel online. But you can summarize your table into the pivot table. But how? Check the step by step process here.

• First, create your excel table.
• Select all the cells of your excel table.
• In table tools, go to the Summarize Pivot table.
• Click on the link and you will find a dialog box for creating a pivot table.
• Click OK and you will find an Excel pivot table in front of you.

How to remove duplicates from the Excel Table?

Apart from the pivot table, you can also remove duplicates from the Excel table by following this below-mentioned process.

• Create an Excel table.
• Select the table.
• Go to the Table tools.
• In table tools, you will find remove duplicates icon.
• Click on the icon and you will find that all the duplicates have removed.

This is how you can create excel tables. You can also create a pivot table using excel tables. You can also remove duplicates from the table. Stay connected to get the latest information about Excel.

Conclusion

The excel table is one of the most basic functions in Microsoft Excel. It helps make your worksheets more organized, hence makes it easier for users to read and understand business data on a single glance.