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.


Download Practice Workbook

Download this workbook and practice while going through this article.


4 Effective Ways to Tabulate Data in Excel

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.

how to tabulate data in excel


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.

how to tabulate data in excel

  • Excel will tabulate your data.

Read More: How to Analyze Survey Data in Excel (with Quick Steps)


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.

how to tabulate data in excel

  • Excel will tabulate your data.

Read More: How to Analyze Survey Data with Multiple Responses in Excel (2 Methods)


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.

how to tabulate data in excel

  • 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.

how to tabulate data in excel

  • 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.

how to tabulate data in excel

Read More: How to Do Cross Tabulation in Excel (3 Suitable Examples)


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.

how to tabulate data in excel

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

  • Excel will do the data tabulation in another worksheet.

how to tabulate data in excel

Read More: How to Analyze Satisfaction Survey Data in Excel (With Easy Steps)


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.


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

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo