# Excel tables: How to perfectly format your data

When you hear the word Excel, tables are probably what come to mind. With good reason, many people directly associate **spreadsheets** with the Microsoft software solution. The individual sheets or pages of an Excel spreadsheet resemble tables with their **grids of columns and rows**. With just a few clicks, you can enter data or import entire datasets into Excel spreadsheets. You can also organize your data into tables without special formatting. However, if you also want to **sort**, **filter**, or **graphically present** your data, formatted tables are your best option.

In the following tutorial we’ll show you **how to create a formatted Excel table and tailor it to your needs**. We’ll also explain how to remove formatting when you’re done with it.

## How to create an Excel table

Thanks to a **wide range of predefined table styles**, you don’t need any special expertise to create formatted Excel tables. If none of the available templates meet your needs, you can easily **create your own custom table style**. Excel offers a range of options for designing a table format.

Note Unlike custom table styles, predefined styles for Excel tables cannot be deleted. However, you have the option of **clearing** both predefined and custom styles. The formatting is then removed and the data is displayed in the default table format.

### Integrating or creating a dataset

You need a suitable dataset before you can start creating a table. If you want to use features like the filter or sort function, you should **also assign appropriate column headers to the data**. You can easily integrate data from an external source using the **import feature in Excel.** To do this, go to the “**Data**” tab and click the “**Get Data**” button. Supported import sources include documents (text, CSV, XML, JSON), databases (including Microsoft Access and SQL Server database), Microsoft Azure, and Facebook:

[![Image: Menu for importing data in Excel 2016](https://www.ionos.ca/digitalguide/fileadmin/_processed_/4/6/csm_menu-for-importing-data-in-Excel-2016_f01f351962.webp "Menu for importing data in Excel 2016")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/menu-for-importing-data-in-Excel-2016.jpg) Menu for importing data in Excel 2016 Alternatively, you can manually enter the data for your new Excel table. In the following example, which is also the basis for further steps in this Excel tutorial, we’ve created a small dataset with ten customers. The table lists the **name**, **age**, **revenue** and **year** of the customer’s first purchase:

[![Image: Excel 2016: Example dataset](https://www.ionos.ca/digitalguide/fileadmin/_processed_/c/a/csm_excel-2016-example-dataset_d21e3da099.webp "Excel 2016: Example dataset")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/excel-2016-example-dataset.jpg) Excel 2016: Example dataset ### Formatting a dataset as a table

Once you’ve inserted your dataset, you can convert it into a formatted Excel table. To do this, **first select the complete dataset** by holding down the left mouse button and then selecting all Excel cells you want to convert:

[![Image: Selected dataset in Excel 2016](https://www.ionos.ca/digitalguide/fileadmin/_processed_/a/b/csm_selected-dataset-in-excel-2016_649c272d80.webp "Selected dataset in Excel 2016")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/selected-dataset-in-excel-2016.jpg) Selected dataset in Excel 2016 Once you’ve selected the dataset, click the “**Format as table**” button on the “Home” tab. Excel will then show you an overview of the available predefined styles:

[![Image: Overview of predefined Excel table styles in Excel 2016](https://www.ionos.ca/digitalguide/fileadmin/_processed_/4/2/csm_overview-predefined-table-styles-excel-2016_5ceb7f2cdb.webp "Overview of predefined Excel table styles in Excel 2016")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/overview-predefined-table-styles-excel-2016.jpg) Overview of predefined Excel table styles in Excel 2016 In the dialog box that appears, confirm the dataset cells (in this example, all cells from A1 to D11) and select the “**My table has headers**” checkbox if you have titled the columns of your dataset. Then click “**OK**” to create the selected Excel table:

[![Image: Excel 2016: Formatted Excel tables](https://www.ionos.ca/digitalguide/fileadmin/_processed_/2/0/csm_excel-2016-formatted-excel-tables_1a45ec9693.webp "Excel 2016: Formatted Excel tables")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/excel-2016-formatted-excel-tables.jpg) Excel 2016: Formatted Excel tables  Note If you haven’t defined titles for your table columns, Excel automatically adds **header placeholders** to the table during formatting.

### Using and removing sort and filter functions

If you create a formatted Excel table for your dataset, Excel adds a sort and filter feature to the table by default. You can use this feature to **sort** the values of the individual columns **alphabetically** or **numerically** or **filter them according to your own criteria**. To do this, simply click the drop-down arrow icon in the header line of a column and select the option you want to use.

For example, in the Excel table in this tutorial, you can **sort** customers by revenue (highest to lowest) by clicking the arrow icon in the Sales column and selecting “**Sort largest to smallest**”:

[![Image: Drop-down windows for the sort and filter feature in Excel tables (Excel 2016)](https://www.ionos.ca/digitalguide/fileadmin/_processed_/4/2/csm_dropdown-windows-for-sort-and-filter-feature-in-Excel-tables-2016_076d51584c.webp "Drop-down windows for the sort and filter feature in Excel tables (Excel 2016)")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/dropdown-windows-for-sort-and-filter-feature-in-Excel-tables-2016.jpg) Drop-down windows for the sort and filter feature in Excel tables (Excel 2016) Like the sort function, the filter function automatically differentiates between text and number filters, which you use according to the contents of the column. For example, you can use this feature to display only those customers who are under 50 years old in the Excel table in this tutorial. To do this, choose “**Number Filters**” and then “**Is less than**.” In the “Custom AutoFilter” dialog box that appears, enter the value “50” and confirm the filter by choosing “**OK**”:

[![Image: Excel table with custom filtering](https://www.ionos.ca/digitalguide/fileadmin/_processed_/4/8/csm_excel-table-with-custom-filtering_393003d144.webp "Excel table with custom filtering")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/excel-table-with-custom-filtering.jpg) Excel table with custom filtering If you don’t want to use the filter or the sort function for your Excel table, you can also **turn off the feature**. First, select one of the cells with the drop-down arrow icon and then click “**Sort & Filter**” in the upper menu bar (on the Home tab). Then select “**Filter**” from the drop-down menu to turn off the functions and hide the icons:

[![Image: Excel table without filter/sort function](https://www.ionos.ca/digitalguide/fileadmin/_processed_/8/8/csm_excel-table-without-filter-sort-function_0a6ce6c677.webp "Excel table without filter/sort function")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/excel-table-without-filter-sort-function.jpg) Excel table without filter/sort function ### Adding a totals row to an Excel table

One of the advantages of using a formatted Excel table is that you can add a totals row that automatically presents the results of **different calculation options selected from a drop-down list**. For example, you can display the sum, the average, specific individual values as well as the minimum or maximum value. To add a total row to your Excel table, simply right-click any cell of the table and select “**Table**” and then “**Totals Row**” from the menu:

[![Image: Excel 2016: Adding a totals row to tables](https://www.ionos.ca/digitalguide/fileadmin/_processed_/0/2/csm_Excel-_2016-adding-a-totals-row-to-tables_609692cad7.webp "Excel 2016: Adding a totals row to tables")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/Excel-_2016-adding-a-totals-row-to-tables.jpg) Excel 2016: Adding a totals row to tables The totals row uses the familiar drop-down arrow icons. Click this icon in the table column for which you want to **select a calculation option**. Then select an option to **display the result of the calculation in the row**. In the following example, we want Excel to calculate the averages for the age and revenue of the customers in the table:

[![Image: Excel 2016 table: Totals row with averages](https://www.ionos.ca/digitalguide/fileadmin/_processed_/2/7/csm_excel-2016-table-totals-row-with-averages_8e061581b0.webp "Excel 2016 table: Totals row with averages")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/excel-2016-table-totals-row-with-averages.jpg) Excel 2016 table: Totals row with averages ### Creating charts from Excel tables

The advantage of creating a formatted Excel table is that you can present the data in charts **that are automatically adjusted as you make changes to the table or table values**. Follow these steps to create a dynamic chart:

1. Open the “**Insert**” tab.
2. **Select** all rows for which you want to present data in a chart.
3. Click “**Recommended Charts**” in the “Charts” section of the ribbon and select the chart type you want to use. Alternatively, you can click on one of the preselected chart icons.

[![Image: Excel table with dynamic chart](https://www.ionos.ca/digitalguide/fileadmin/_processed_/f/5/csm_excel-table-with-dynamic-chart_dfedf9b14c.webp "Excel table with dynamic chart")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/excel-table-with-dynamic-chart.jpg) Excel table with dynamic chart ### Removing formatting from an Excel table

If you no longer need certain (or any) formatting in your Excel table or you’ve accidentally formatted cells, you can **undo the formatting** of these cells at any time. This function is located on the “**Home**” tab of the ribbon. You can use it as follows:

1. **Select** the cells for which you want to clear the formatting.
2. Click the “**Clear**” button in the “Editing” section.
3. Select “**Clear Formats**” from the drop-down menu.

[![Image: Microsoft Excel 2016: Clear format](https://www.ionos.ca/digitalguide/fileadmin/_processed_/8/2/csm_microsoft-excel-2016-clear-format_fe568e3b2f.webp "Microsoft Excel 2016: Clear format")](https://www.ionos.ca/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/microsoft-excel-2016-clear-format.jpg) Microsoft Excel 2016: Clear format  Tip Excel also has a built-in feature for **creating pivot tables**. For step-by-step instructions on how to do this, see our detailed how-to article on [pivot tables in Excel](https://www.ionos.ca/digitalguide/online-marketing/web-analytics/excel-pivot-tables/ "Excel Pivot Tables").


This is a markdown version of: [https://www.ionos.ca/digitalguide/online-marketing/online-sales/creating-an-excel-table/](https://www.ionos.ca/digitalguide/online-marketing/online-sales/creating-an-excel-table/) for AI/LLM consumption.