How to Create a Table with Existing Data in Excel

In this article, we will learn to create a table with existing data in Excel. The Table is one of the most powerful features of Excel. It helps the users to arrange data systematically and perform many operations very easily. Today, we will demonstrate step-by-step procedures. Using these steps, you can easily create a table with existing data in Excel. So, without further delay, let’s start the discussion.


Download Practice Book

You can download the practice book from here.


Step-by-Step Procedures to Create a Table with Existing Data in Excel

To explain the steps, we will use a dataset containing information about some Sellers’ Sales Amounts. It also includes the Region and Dates of the sales. So, this dataset is our existing data. Now, we will try to create a table in excel with this existing data.

Step-by-Step Procedures to Create a Table with Existing Data in Excel


STEP 1: Create Table with Existing Data

  • First of all, select any cell of the existing dataset.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • Secondly, go to the Insert tab and click on the Table option.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • After that, a Create Table dialog box will appear.
  • It will show the data range for the table.
  • If you do not want the whole data in your table, then select the range you want to insert inside the table.
  • In our case, the range is B4:E13.
  • Also, check the ‘My table has headers’ option if the table contains headers.
  • Otherwise, uncheck it.
  • Click OK to move forward.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • As a result, the existing data will convert into a table.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

Read More: How to Create Table from Data Model in Excel (With Easy Steps)


STEP 2: Change Table Design

  • After creating a table, you can change the table style.
  • To do so, select a cell in your table to make the Table Design tab appear in the ribbon.
  • In the next step, navigate to the Table Design tab and select a style from the Table Styles section.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • After applying the style, the table will look more attractive.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

Read More: Create Table in Excel Using Shortcut (8 Methods)


STEP 3: Name Excel Table

  • In the third step, we will set a name for the table.
  • For that purpose, go to the Table Design tab and type the name inside the ‘Table Name’ box.
  • You can not use any space while naming the table.
  • Click Enter to set the name.

Step-by-Step Procedures to Create a Table with Existing Data in Excel


STEP 4: Insert Total Row

  • However, one of the most exciting features of an Excel table is that you can insert a row where you can see the totals.
  • To insert the total row, go to the Table Design tab and check the Total Row option.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • As a result, you will see the sum of the sales amount in the last row of the table.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • Moreover, if you click on Cell E14, a drop-down menu will appear.
  • You can select different types of functions from there to see the Average, Count, Max, Min, Variance, and many more values of the sales amount.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

Read More: How to Create Excel Table with Row and Column Headers


STEP 5: Sort Excel Table

  • At this moment, we will learn to sort the Excel table.
  • To do sorting, click on the wide drop-down arrow.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • After that, select Sort A to Z from the menu. It will sort the dataset in ascending alphabetical order.
  • You can also sort it in descending order by selecting the Sort Z to A option.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • After sorting, the table will look like the picture below.
  • You can see the Region starts from East, then North, and finally, West.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • In the same way, you can also sort the table from the oldest to the newest dates.

Step-by-Step Procedures to Create a Table with Existing Data in Excel


STEP 6: Filter Table in Excel

  • If you want to see certain data from the table, then you need to use the Filter feature.
  • In order to apply a filter in a table, click on the drop-down arrow of a header.
  • Here, we will filter the table using the Region. So, we will click on the drop-down arrow of the Region header.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • We want to see the information about the sales of the North and West regions.
  • So, we unchecked the East option.
  • Click OK to proceed.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • After clicking OK, the filter will be applied.
  • Hence, you will see the sales amount of the North and West region only.

Step-by-Step Procedures to Create a Table with Existing Data in Excel


STEP 7: Add New Data to Table

  • To add new data to a table, move to the row below the last row and start typing your information.
  • Here, we have selected Cell B14 and typed James.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • Now, press Enter and the information will be added to the table.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

  • Lastly, complete inserting all the data.

Step-by-Step Procedures to Create a Table with Existing Data in Excel

Read More: How to Create a Table Without Data in Excel (2 Easy Methods)


STEP 8: Resize Table

  • Finally, if you need to add more data, then you will have to resize the table.
  • To do so, put the cursor on the bottom right corner of the last cell of the table.
  • The cursor will change into a double-headed arrow.
  • You can move it to increase the size of the table.

  • Alternatively, you can go to the Table Design tab and select Resize Table.

  • After that, change the range of the table to resize it.
  • Here, our new range is B4:E17.
  • Lastly, click OK in the Resize Table dialog box.


Final Output

  • After completing all the steps, the table with the existing data will look like the picture below.


How to Convert Table to Range in Excel

Sometimes, we need to remove the Excel Table and convert it to a normal range. We can do it by following some simple steps. So, let’s observe the steps below to see how we can convert a table to a range.

STEPS:

  • Firstly, go to the Table Design tab and click on the More icon in the Table Styles section.

  • Secondly, select None from the table styles.

  • After that, the table will have no formatting.

  • Now, go to the Table Design tab again and select the Convert to Range option.

  • A message will pop up.
  • Click on Yes to move forward.

  • Finally, the table will convert to a range like the existing dataset.

Read More: Excel VBA to Create Table from Range (6 Examples)


Conclusion

In this article, we have discussed step-by-step procedures to Create a Table in Excel with Existing Data. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo