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
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…
Continue reading How to calculate YOY growth in Excel Pivot Table
Read Histogram and Pareto articles first. Excel Data Analysis tool can create a Pareto chart while creating a histogram. Small tweak but very useful.
Continue reading Quality Management 5: Histogram and Pareto
This is continuation of the previous article. In this article, we will see another way of creating Histogram using Pivot Table.
Continue reading Quality Management 4: Histogram using Pivot Table
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.
Continue reading Quality Management 4: Histogram (any version of Excel)
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.
Continue reading Instant benefit: Try Distinct Count wherever you are using Count
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.
Continue reading Act Now: Discover one new and useful thing from familiar data
Consider a pivot table which has many fields in row as well as column area. Now, for whatever reason, you have to transpose the pivot table. Whatever is in the rows has to go into columns and vice versa. We cannot use Paste Special Transpose with Pivots.
The only choice seems to be manually dragging and dropping fields across row and column areas. Not only is this cumbersome, but it can also lead to mistakes. Don’t worry. I just found a smarter way.
Add a Pivot Chart. Never mind which type. Choose Pie because it takes least amount of effort graphically and it happily ignores child series of data. Now click inside the chart. Choose Design tab and click Switch Rows / Column. It instantly transposes the row and column fields. Delete the chart. Job done.
This works with Power Pivots as well. For large pivot tables, you may get the maximum series limit reached error for charts. Ignore that error and continue – because in this case, the chart is just a temporary means of achieving transpose operation.
Now that we know about Excel Recommended Charts, let us explore it in-depth. This is an implementation of artificial intelligence or machine learning at your fingertips. Don’t underestimate it… exploit it.
Continue reading In-depth: Excel Recommended Charts
There are two types of charts. First type is a chart which you create to interpret data more effectively. The second type is more common. These charts are blindly created billions of times everyday – why? Because boss wants it that way!
In this article, I am going to attempt to change the mindset of all users of Charts. Please spend 5 minutes of your time reading this article and trying out a new thought process. I am confident that it will add value to your life.
Continue reading Don’t just make charts the way boss wants. Use Recommended Charts