How to Tabulate Data in Excel (4 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.


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.


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


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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo