Bin or Bucket Analysis is a common requirement.We will see how easy this is using Grouping feature of Pivot Table.Estimated reading time 10 min |

## Download sample file and follow along

## The Need

Here is a business scenario for Accounts Receivables. However, this concept can be applied to any kind of bucket / bin analysis / ABC analysis.

Open the file. There are only two columns – to keep things simple. Amount and Ageing. Ageing is the number of days since the invoice was sent. 200 rows of data. Minimum is 1 day, maximum is 112 days.

What we need is analysis which shows buckets of 30 days and total amount outstanding along total count of invoices.

*Here the assumption is that there is a single uniform bucket size of 30. If you want different bucket sizes, this approach does not work. We have to use VLOOKUP. This will be covered in a separate article.*

## First thought which comes to mind is usually inefficient!

Many of us think we have to add a new column to the raw data and put multiple nested IF statements like

NO! That is like YOU helping EXCEL.

## Here is the right way

Create a Pivot Table

Drag Amount TWICE into data area.

Right click in one of them – **Summarize Values As** – choose **Count**.

This way we see the total value and count (volume).

Now drag the Ageing column into row area.

It will summarize the data for each unique number in Ageing column.

For example there are two invoices which are ONE day overdue and five invoices which are TWO days overdue… and so on.

This is too detailed. We just want it summarized by 30 day intervals.

Right click in the Row area and choose **Group…**

Look at the dialog carefully. Really carefully. Think about each item you see.

Type 30 in the **By **textbox and click ok. That’s it.

Of course you can go to Group dialog again and change the value to any other number. Pivot will recast the data.

## What next?

Just get this thing working first. Try this with your data. Teach this to everyone around you. Of course you will have more questions…

We will cover those in the second part of this article. Also think why I asked you to look at the Group dialog carefully.

## Articles in this series

Grouping Text in Pivot Tables

How to correct spelling mistakes while analyzing data?

Ageing analysis in seconds (Number Grouping in Pivot) – this article

Ageing Analysis (Numeric Grouping in Pivot) – Part 2

Ageing Analysis (Bucket Analysis) using VLOOKUP – Part 3

Grouping Dates into months, quarters and years – in seconds!

Custom Date Grouping using VLOOKUP

Grouping with Date – Time Data using Pivot

***

Pingback: Ageing Analysis (Numeric Grouping in Pivot) – Part 2 | Efficiency 365

Pingback: Ageing Analysis (Bucket Analysis) using VLOOKUP – Part 3 | Efficiency 365

Pingback: Grouping Dates into months, quarters and years – in seconds! | Efficiency 365

Pingback: Grouping with Date – Time Data using Pivot | Efficiency 365

Pingback: Custom Date Grouping using VLOOKUP | Efficiency 365

Pingback: How to correct spelling mistakes while analyzing data? | Efficiency 365

Pingback: Grouping Text in Pivot Tables | Efficiency 365

Pingback: Have you noticed Recent File List (MRU) options? | Efficiency 365

Pingback: Knowledge Pack – Grouping Data | Efficiency 365

Pingback: Knowledge Pack: Data Analytics | Efficiency 365