How to Tabulate Data in Excel (4 Effective Ways)

Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we need to tabulate data in Excel. In this article, I am going to show you 4 effective methods in Excel to tabulate data.

How to Tabulate Data in Excel: 4 Effective Ways

This is the dataset that I am going to use. I have some products along with their price per unit (kg) and the Amount of these products. I will tabulate these data in this article.

1. Use Insert Tab Option to Tabulate Data in Excel

In this section, I will show the way to tabulate data using the Insert tab option step by step.

Steps:

• Select the entire range B4:E12. Then go to the Insert tab >> select Table.

• Create Table box will pop up. Select the data range you want to tabulate. Check the My table has headers box if you want headers in your table. Then click OK.

• Excel will tabulate your data.

2. Apply Keyboard Shortcut to Tabulate Data in Excel

We can also use keyboard shortcut if we want to tabulate data in Excel. I am going to describe an effortless keyboard shortcut now.

Steps:

• Select any cell from your dataset. I am going to select B4. Then press CTRL + T.
• You will see that the Create Table box appears. Select B4:E12 as your data range. Check the My table has headers box if you want headers in your table. Then click OK.

• Excel will tabulate your data.

3. Create Pivot Table to Perform Data Tabulation in Excel

Pivot Table is a fantastic tool in Excel and it is a child’s play to tabulate and organize a huge dataset using this. Let’s do it step by step.

Steps:

• Select B4:E12. Then go to the Insert tab >> select Pivot Table >> select From Table/Range.

• A window will appear. Select your range. Then choose the destination of your pivot table. I have selected a New Worksheet as the destination. Click OK.

• Excel will create a pivot table for you.

• From the PivotTable Fields, you can organize the data in suitable ways.

• For example, when you put the Product in the Rows and Amounts (kg), Price/kg, and Total Price in the Values field, your pivot table will look like this.

4. Use Power Query to Tabulate Data in Excel

We can do data tabulation using Power Query also.

Steps:

• Go to the Data tab >> select From Table/Range.

• Create Table box will pop up. Select your dataset and Check the My table has headers box if you want headers in your table. Then click OK.

• Power Query Editor will appear. Now click Close & Load.

• Excel will do the data tabulation in another worksheet.

Read More: How to Analyze Satisfaction Survey Data in Excel

Things to Remember

• You can also create a pivot table by pressing ALT+N+V+T. Just remember that you have to press them one by one, not simultaneously.
• Do not forget to change the format to Currency in the new table created by using Power Query.

Download Practice Workbook

Download this workbook and practice while going through this article.

Conclusion

In this article, I have demonstrated 4 effective methods to tabulate data. I hope it helps everyone. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

Related Articles

• How to Tally Survey Results in Excel
• How to Display Survey Results in Excel
• How to Encode Survey Data in Excel
• How to Create a Questionnaire in Excel
• How to Analyze Survey Data with Multiple Responses in Excel
• How to Analyze Survey Data in Excel

<< Go Back to Survey in Excel | Excel for Statistics | Learn Excel