Excel Table Explained - 14 Amazing Best Practices For Your Excel Table

Angelika
·
November 26, 2021

Using Excel Tables adds a lot of power to your spreadsheets and increases your options when working with Excel. We have put together all you need to know about how to create an Excel Table and how to get the most out of them. Including our favorite tutorial links.

Excel is versatile. Apart from calculations and dashboards, most of us use Excel workbooks as lists or mini databases. Lines are records, columns would be the “fields of a database”. Formatting your Excel list as an Excel table adds a lot of power and increases your options when working with Excel. Here’s how to add an Excel table and some great things you can do. Including our favorite tutorial links.

Read in this post

Why do I need an Excel table?

Excel Tables are containers for your data. The definition as a table shows Excel that all the data in the columns and rows you have marked are related. That’s a big time-saving. First, you get a lot of list-related features automatically. And second, when you add a row, all formats and formulas are added automatically.

How do I create an Excel table?

Creating an Excel table is one of the easier features of Microsoft Excel. Open your Excel workbook and select the worksheet with your list. Either select the desired list range or have your list selected by Excel. Then click on insert and select table. And done.

The now-a-table list will change to a blue design by default. It automatically contains filters and locks your headlines. It is a really good idea to name your table while you’re at it. When using the table otherwise they will be shown as Table1, Table2, Table3… which is not very helpful for identifying a special table. Take a look at our quick tip video to set up your Excel table in 30 seconds.

Now, let your Excel table work for you

The Excel table comes with a lot of built-in features for filtering, design, and data analysis.

  1. Organize your data: A table helps you to format your data in a flash. Column headers remain on top when you scroll down. The filter option is automatically available in the header columns as well.
  2. Design to support data evaluation: There are a ton of table designs and individual designs to make your data more clearly legible: from zebra design in rows to coloring of certain columns that carry the most important information. Our favorite tutorial from Microsoft Support. Making data clearly distinguishable is one thing. But we collect data to learn more and so Excel tables can help with basic data analysis.
  3. Data formatting is easier: Data formats and formulas are carried down by columns. If you add a new row, the format in each column is the same. This makes sure that each field and row is consistent. And thus, data quality is higher.
  4. Ease the use of formulas: With an Excel table, you can also make the use of formulas much easier. E.g. instead of cell references, you can use structured references for formulas that include table names and column names as Excel automatically names them as named ranges. The tutorial on Chandoo.org explains that in a great way. You can also total tables without even writing a formula (or use average).
  5. Add pivot tables and dynamic charts: Let’s turn to Pivot tables. You can create a pivot table from scratch but Excel tables are the ideal basis. Pivot tables are ideal for grouping and summarizing your list data. To change your list of data into a pivot select “Summarize with PivotTable. Select the fields you want to group and count. Of course, there is more than sum, also count and average. And done. Excelcampus explains the benefits and how to do it in an easy-to-understand way. You can also add a smart pivot chart based on your pivot table.
  6. Add a timeline to your pivot table or chart: If there are dates in your Excel table or pivot you can group them by days, months, quarters, or year. Learn how to do it in step 7.
  7. Add slicers to filter your table or pivot: If you have several recurring data like country, product, or contact you can create easy filters for your pivot charts. Using the slicers your can simultaneously filter by several categories and keep an overview of the set filters. At Microsoft support, you’ll find a step-by-step video guide.

Excel table SOS: troubleshooting for your Excel table?

There is not much that can go wrong with an Excel table. These are the most common issues that can occur:

  • Your Excel table doesn’t contain your entire list. Check for empty or almost empty columns or lines. Never mind if you can’t fill your list right now. Go to the right bottom side of your table and drag the outlines to the columns and lines that should be included.
  • You would want to expand your Excel table to the left or add a row? That’s easy. When you fill in the column next to your table or the line below it is added to the table.
  • You don’t want the Excel table anymore. Revert an Excel table to the normal list range. Click in your table and select table design. Click on convert to range on the left-hand side. The layout you chose will remain.

Use Excel in collaboration and add a Kanban, pivot, calendar, and more

Using Excel tables can do even more for you. Airrange.io is a layer that helps you to better use Excel in project and process management, sales, corporate management, or accounting. It adds apps and features for reports to your Excel data in Microsoft 365 – always linked to your original Excel workbook but with safe granular sharing and more comfortable views and features.

What can you do with Excel tables in Airrange?

  1. Kanban with Excel: You can move activities along process steps with a Kanban board.
  2. Interactive Pivot: Share a broad range of data analysis with a pivot evaluation view that everyone can customize without manipulating the original.
  3. Database grid with user accountability: Give everyone a database grid for data lookup or editing (directly linked to the original Excel workbook). You can even filter and provide only the data required by a user.
  4. Calendar view with Excel: Show schedules or time tables in calendar view
  5. Checklists: Use your Excel table as a checklist for to-dos or audits
  6. Mix workbooks: Add views from different workbooks to your interactive Airrange Reports
  7. Microsoft Teams: Share single views in Microsoft Teams fully editable

Airrange is particularly helpful when co-authoring and collaborating with Excel in a team. In addition to an easier view that helps to interpret and use your Excel data faster, you can track activities and changes with Airrange and see who viewed and edited data. Watch our video on Excel tables in Airrange or find out more on Airrange.io.