Category Archives: Analytics

How to create Custom Data Labels in Excel Charts

I received a query from a customer about dynamic data labels for charts. Instead of replying directly, I thought of writing this article. This will help all of you in refining your charts. The idea is to create a chart which explains the fluctuations using text based explanations. The best part is, the explanation can be a part of the data itself.
Watch the two minute video and read details below. This works with Excel 2013 onwards. I have also included the solution for older versions, which is not as elegant, but it works.

Continue reading How to create Custom Data Labels in Excel Charts

How to calculate YOY growth in Excel Pivot Table

Estimated reading time 3 min. Works with ALL versions of Excel.
The data is a list of sales transactions, two columns – amount and date.
We have 5000 transactions over many years. We want to know how the business grew year on year. Here are the steps…

SNAGHTML48223f                  image

Continue reading How to calculate YOY growth in Excel Pivot Table

Quality Management 4: Histogram (any version of Excel)

Histogram is used to visualize the frequency with which data occurs. This is a good way of understanding data more than just sum and average. It is a good idea to look at each data set you get as a histogram. Here is how you do it in Excel.

Quality Management: Histogram in Excel

Continue reading Quality Management 4: Histogram (any version of Excel)

Instant benefit: Try Distinct Count wherever you are using Count

SUM and COUNT are the most common methods of summarizing data. It is easily done in Pivot Table or any other analytical tool. What is equally important is DISTINCT COUNT. But it is not commonly used. Why not? Firstly, due to lack of awareness and secondly, due to lack of that feature in Pivot Table. Let us solve both problems in the next 10 minutes.

distinct count or unique count

Continue reading Instant benefit: Try Distinct Count wherever you are using Count

Act Now: Discover one new and useful thing from familiar data

We get data and make reports repeatedly. Often we forget to look at the same data in different ways. Due to this unbelievable amount of useful information is lost.

Act Now is a new idea I am trying. It asks you to do some activity and post the results.

Act Now for post results

Photo credit: Machine Project / Foter / CC BY-NC-SA

Continue reading Act Now: Discover one new and useful thing from familiar data