And you can also add some styling to make them look good and present the data clearly with the help of a CSS file.

Before we wrap up, let’s do one more task:

Create a table using what we learned to summarize what we have covered in the article today. After that compare your design with my pinned code playground below:

How To Make & Use Tables In Microsoft Excel (Like a Pro)

Tables might be the best feature in Excel that you aren’t yet using. It’s quick to create a table in Excel. With just a couple of clicks (or a single keyboard shortcut), you can convert your flat data into a data table with a number of benefits.

The advantages of an Excel table include all of the following:

In this tutorial, I’ll teach you to use tables (also called data tables) in Microsoft Excel. You’ll discover how to use all of these features and master working with data tables. Let’s get started learning all about MS Excel tables.

What is a Table in Microsoft Excel?

A table is a powerful feature to group your data together in Excel. Think of a table as a specific set of rows and columns in a spreadsheet. You can have multiple tables on the same sheet.

You might think that your data in an Excel spreadsheet is already in a table, simply because it’s in rows and columns and all together. However, your data isn’t in a true «table» unless you’ve used the specific Excel data table feature.

The flat data on the left spreadsheet has been converted to a table in the example on the right.

In the screenshot above, I’ve converted a standard set of data to a table in Excel. The obvious change is that the data was styled, but there’s so much power inside this feature.

Tables make it easier to work with data in Microsoft Excel, and there’s no real reason not to use them. Let’s learn how to convert your data to tables and reap the benefits.

How To Make a Table in Excel Quickly (Watch & Learn)

The screencast below is a guided tour to convert your flat data into an Excel table. I’ll teach you the keyboard shortcut as well as the one-click option to convert your data to tables. Then, you’ll learn how to use all the features that make MS Excel tables so powerful.

If you want to learn more, keep reading the tutorial below for an illustrated guide to Excel tables.

To follow along with this tutorial, you can use the sample data I’ve included for free in this tutorial. It’s a simple spreadsheet with example data you can use to convert to a table in Excel.

How to Convert Data to a Table in Excel

Here’s how to quickly create a table in Excel:

Start off by clicking inside a set of data in your spreadsheet. You can click anywhere in a set of data before converting it to a table.

Now, you have two choices for how to convert your flat, ordinary data to a table:

In either case, you’ll receive this pop-up menu asking you to confirm the table settings:

Format as Table menu in Excel.

Confirm two settings on this menu:

I highly recommend embracing the keyboard shortcut (Ctrl + T) to create tables quickly. When you learn Excel keyboard shortcuts, you’re much more likely to use the feature and embrace it in your own work.

Now that you’ve converted your ordinary data to a table, it’s time to use the power of the feature. Let’s learn more:

How to Use Table Styles in Excel

Tables make it easy to style your data. Instead of spending time highlighting your data, applying background colors and tweaking individual cell styles, tables offer one click styles.

Once you’ve converted your data to a table, click inside of it. A new Excel ribbon option called Table Tools > Design appears on the ribbon.

Click on this ribbon option and find the Table Styles dropdown. Click on one of these style thumbnails to apply the selected color scheme to your data.

With a table selected, choose the Design tab on Excel’s ribbon and choose the Table Styles dropdown to add some style to your data.

Instead of spending time manually styling data, you can use a table to clean up the look of your data. If you only use tables to apply quick styles, it’s still a great feature. But, there’s more you can do with Excel tables:

How to Apply Table Names

One of my favorite table features is the ability to add a name to a table. This makes it easy to reference the table’s data in formulas.

Click inside the table to select it. Then, click on the Design tab on Excel’s ribbon. On the left side of this menu, find the Table Name box and type in a new name for your table. Make sure that it’s a single word (no spaces are allowed in table names.)

With a table selected, click on the Design tab and give it a name on the left side of the ribbon.

Now, you can use the name of the table when you write your formulas. In the example screenshot below, you can see that I’ve pointed a new PivotTable to the table we created in the previous step. Instead of typing out the cell references, I’ve simply typed the name of the table.

Once you name your tables, you can simply type those table names in your formulas and references.

Best of all, if the table changes with new rows or columns, these references are smart enough to update as well.

Table names are a must when you create large, robust Excel workbooks. It’s a housekeeping step that ensures you know where your cell references point to.

How to Make Cleaner Excel Formulas

When you write a formula in a table, the formula is more readable and cleaner to review than standard Excel formulas.

In the example below, I’m writing a formula to divide the Amount Billed by the Hours Spent to calculate an Hourly Rate. Notice that the formula that Excel generates isn’t «E2/D2», but instead includes the column names.

Excel generates a simpler formula when working in a table.

Once you press enter, the Excel table will pull the formula down to all of the rows in the table. I prefer the formulas that tables generate when creating calculations. Not only are they cleaner, but you don’t have to pull the formula down manually.

Time-Saving Excel Table Feature: Auto Expand

Tables might evolve over time to include new columns or rows. When you add new data to your tables, they automatically update to include the new columns or rows.

In the example below, you can see an example of what I mean. Once I add a new column in column G and press enter, the table automatically expands and all of the styles are pulled over as well. This means that the table has expanded to include these new columns.

Adding a new adjacent column will expand the Excel table to include it.

The best part of this feature is that when you’re referencing tables in other formulas, it will automatically include the new rows and columns as well. For example, a PivotTable linked to an Excel data table will update with the new columns and rows when refreshed.

How to Use Excel Table Filters

When you convert data to a table in Excel, you may notice that filter buttons appear at the top of each column. These give you an easy way to restrict the data that appears in the spreadsheet. Click on the dropdown arrow to open the filtering box.

You can check the boxes for the data you want to remove, or uncheck a box to remove it from the table view.

Excel tables automatically have filtering buttons for each column.

Again, this is a feature that makes using Excel tables worthwhile. You can add filtering without using tables, sure—but with so many features, it makes sense to convert to tables instead.

How to Quickly Apply Subtotals

Subtotals are another great feature that make tables worth using. Turn on totals from the ribbon by clicking on Total Row.

Turn on a total row from the ribbon to use subtotals in Excel tables.

Now, the bottom of each column has a dropdown option to add a total or another math formula. In the last row, click the dropdown arrow to choose an average, total, count, or another math formula.

With this feature, a table becomes a reporting and analysis tool. The subtotals and other figures at the bottom of the table will help you understand your data better.

These subtotals will also adapt if you use filters. For example, if you filter for a single client, the totals will update to only show for that single client.

Recap and Keep Learning More About Excel

It doesn’t matter how much time you spend learning Excel, there’s always more to learn and better ways to use it for controlling your data. Check out these Excel tutorials to keep learning useful spreadsheet skills:

If you haven’t been using tables, are you going to start using them after this tutorial? Let me know in the comments section below.

How to make a Table in Google Sheets?

Most of the business world runs on spreadsheets and many of these spreadsheets contain tabular data. Therefore, one might assume that spreadsheet software comes with native support for working with these tables effectively and efficiently. Sadly this isn’t true!!

In this tutorial, I will show you how you can use formulas and other features in Google Sheets to build some desirable features that will make your tables less error prone and more effective at conveying information.

What does having native support for tables in spreadsheets even mean? Isn’t the spreadsheet itself basically just one large table?

Let me try and explain what I mean by native support for tables with a few examples.

#1 Formatting for visual presentation.

Let’s say you have a table in Google Sheets that contains information about 10 students in your class. Here is how that data looks upon entering it:

Now, if you want to format it to make it look ‘presentable’, there isn’t a simple ‘one-click’ mechanism to do that. As you’ll see later in this tutorial, you could do a couple of things to make it look nice but you’d have to do this manually and repeat these steps every time you create a new table in Google Sheets.

#2 Filtering and sorting

A very common use case when working with tables is filtering and sorting their contents to make sense of the data they contain. Again, you can certainly do this manually in Google Sheets but there is no way to tell the spreadsheet that these 11 rows and 7 columns are a table and have this filter be created automatically.

#3 Automatically compute aggregate metrics (aka the «Totals» row)

This is probably the most important requirement and the one that I wish was supported by all spreadsheet software.

Let me explain the problem first. Let us say you have a table with three rows. You create a «Totals» row where you add up numbers in the three other rows. Now suppose you insert two new rows into the table. Unless you carefully structure the formulas in the totals row, they might still be adding numbers from just the three original rows instead of the five current ones.

Ideally, the spreadsheet software would automatically ensure that the totals reflected the data in the table. Unfortunately it is left to us, the users of the spreadsheet, to double and triple check the results produced by spreadsheet analysis. It is not surprising that errors creep into analysis performed using spreadsheets.

The good news is that Microsoft Excel does have a «Tables» feature and has good native support for working with tables. In this tutorial I will show you how to replicate some of this functionality in Google Sheets.

Prerequisites

This tutorial assumes that you are familiar with using Google Sheets. In particular, it assumes that you are familiar with the following concepts in Google Sheets:

Formatting data (how to format text, cell backgrounds, etc.)

How to create a table in Excel

by Svetlana Cheusheva | updated on February 15, 2022

The tutorial explains the essentials of the table format, shows how to make a table in Excel and leverage its powerful features.

At the surface, an Excel table just sounds like a way to organize data. In truth, this generic name covers a ton of useful features. Tables containing hundreds or even thousands of rows and columns can be instantly recalculated and totaled, sorted and filtered, updated with new information and reformatted, summarized with pivot tables and exported.

Excel table

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:

How to create a table in Excel

With the source data organized in rows and columns, carry out the below steps to covert a range of cells into a table:

As the result, Excel converts your range of data into a true table with the default style:

Many wonderful features are now just a click away and, in a moment, you will learn how to use them. But first, we’ll look at how to make a table with a specific style.

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:

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:

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:

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:

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:

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

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:

Excel table styles

Tables are very easily formatted due to a predefined gallery of styles. Additionally, you can create a custom style with your own formatting.

How to change table style

When you insert a table in Excel, the default style is automatically applied to it. To change a table style, do the following:

Apply a table style and remove existing formatting

When you format a table with any predefined style, Excel preserves the formatting you already have. To remove any existing formatting, right-click the style and choose Apply and Clear formatting:

Manage banded rows and columns

To add or remove banded rows and columns as well as apply special formatting for the first or last column, simply tick or untick the corresponding checkbox on the Design tab in the Table Style Options group:

How to remove table formatting

If you’d like to have all the functionality of an Excel table but do not want any formatting such as banded rows, table borders and the like, you can remove formatting in this way:

Note. This method only removes the inbuilt table formatting, your custom formatting is preserved. To remove absolutely all formatting in a table, go to the Home tab > Formats group, and click Clear > Clear formats.

How to remove table in Excel

Removing a table is as easy as inserting it. To convert a table back to a range, just do the following:

This will remove the table but retain all the data and formatting. To keep only the data, remove table formatting before converting your table to a range.

This is how you create, edit and remove a table in Excel. I thank you for reading and hope to see you on our blog next week!

• ← How to make a summary of a research
• How to make a video game in unity на русском →