Excel Tables Explained — 14 Amazing Best Practices For Your Excel Table
Using Excel Tables adds a lot of power to your spreadsheets and increases your options when working with Excel as a database. We have put together all you need to know about creating Excel tables and how to get the most out of them in Excel and by adding features from Airrange, a free Microsoft 365 layer for your Excel workbooks.
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.
7 best practices you get from an Excel table in Excel
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.
From Excel table to powerful Excel database
When using Excel workbooks as lists or mini databases, rows are records and columns would be the “fields of a database”. As seen above, formatting your Excel list as an Excel table adds a lot of power and increases your options when working with Excel. Using Excel tables with Airrange.io can do even more for you. It adds apps and features for reports to your Excel database in Microsoft 365 — always linked to your original Excel workbook but with safe granular sharing and more comfortable views and features.
How does Airrange improve the way you work with Excel?
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.
Here are seven more options to add to your Excel database when based on an Excel table. All apps are linked in real-time to your Excel workbook. They can be based on the same data but provide the best view to get the job done.
7 more best practices for Excel tables with Airrange
Here are 7 app interfaces you can add to your Excel spreadsheet with Airrange.io and share in Microsoft Teams, by e-Mail or embed in a website.
8. Kanban with Excel: You can move activities along process steps with a Kanban board. It’s easily build in Airrange.
9. Interactive Pivot: Share a broad range of data analysis with a pivot evaluation view that everyone can customize without manipulating the original.
10. 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. Go to Airrange.io and learn how to build it.
11. Calendar view with Excel: Show schedules or time tables in calendar view
12. Checklists: Use your Excel table as a checklist for to-dos or audits
13. Mix workbooks: Add views from different workbooks to your interactive Airrange Reports
14. Microsoft Teams: Share single views in Microsoft Teams fully editable
Interested in upgrading your Excel table to an Excel database? Find all you need on Airrange.io
