Help me to summarize data in excel

Asked By sanjeewa 0 points N/A Posted on - 06/20/2012

 

 

Hi,

I have a lot of data of sales which is relevant to whole seller business. There are three different categories of sales. Those are Cash sales, Check sales, Credit sales. I have Excel files for each month including daily sales of different items. Please see below image.

What I want is I want to summarize those monthly data to show total amount of each item under each category. Like this,

 

 

Can anybody tell me an easy way to do this?

Thanks.

• Status: Open
• Question Views: 957
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS Excel

Answered By Sherin Viluel 0 points N/A #161664

Help me to summarize data in excel

 

In Microsoft Excel in order to summarize data there is a special option that we can use.” Pivot table”

You can use “Pivot table” to summarize your data as per your requirement. Further you can adjust the field as you wish according to the situation.

Here is the way.

First select all the data including heading (except total) the excel sheet. (Make sure that every column contains a heading)

Then go to insert tab and click on “Pivot table” and click ok.

 

 

A new sheet will be appeared with a pivot table and field list as follows.

 

 

 

Then drag and put category field in to “Column Labels” area, Item field in to “Row Label” area and amount field in to “Values” area.

 

 

 

Now if the amount field is not display as “Sum of amount” click on the amount field in order to make it sum of. Click on value field setting then a dialog box will appear.

In that box click on the “sum” in summarize by tab and click ok.

 

 

 

Now you are able to see the summarize data as follows.

 

 

About Sherin Viluel

Questions
0

Answers
50

Best Answers
37

• Vote Up 0 Vote Down

• Posted on - 06/20/2012
• Question Category: MS Excel