Mursalin Ibne Salehin
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 1: Create Table with Existing Data
- First of all, select any cell of the existing dataset.
- Secondly, go to the Insert tab and click on the Table option.
- 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.
- As a result, the existing data will convert into a table.
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.
- After applying the style, the table will look more attractive.
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 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.
- As a result, you will see the sum of the sales amount in the last row of the table.
- 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.
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.
- 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.
- After sorting, the table will look like the picture below.
- You can see the Region starts from East, then North, and finally, West.
- In the same way, you can also sort the table from the oldest to the newest dates.
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.
- 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.
- After clicking OK, the filter will be applied.
- Hence, you will see the sales amount of the North and West region only.
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.
- Now, press Enter and the information will be added to the table.
- Lastly, complete inserting all the data.
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.
