How many times are the values in the table?

There is an easy way to find number of occurrences of items in the table. For example, the following table contains repeated Products. We want to know, how many times was each product ordered.

Table with duplicates

Solution

A simple and convenient solution of this task offers the Pivot table. Select one of the cell from the list of products and choose Insert PivotTable.

Insert PivotTable

 

PivotTable can be placed in the new sheet or in the existing sheet. The choice depends on you, it doesn’t matter.

Create new PivotTable menu

 

Move the item Name into the fields Row Labels and Values. In the Values should be Count of Name. If it is not, just click on the item, select Value Field Settingsā€¦ and choose Count.

PivotTable Field list - count of products

 

PivotTable now shows how many times are the products in our source table.

Count of Products

 

Task 2 / show this for each month

In the source table is the information about Date of Order. We can add this field into the pivot table and group it by months.

PivotTable Field list - count of products by month

 

PivotTable now looks like this (view by days):

Pivot table with date of order

 

To change it, to view by months, do left-click on the dates and from the menu select Groupā€¦ . You can find it also in the top menu section PivotTable Tools-> Options-> Group Selection.

Menu in pivot table

 

In the Grouping menu choose Months. If you need more detailed views, you can select multiple items at once.

Grouping menu

 

Fields in the Pivot table can be renamed, so I changed the labels to make the table nicer :)

Pivot table grouped by month

Video tutorial:



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress