By combining INDEX and MATCH, we can easily calculate sales results if the data we analyse is given in a table that is a matrix. By using Data Validation for certain cells you’ll get a very simple sales analysis tool that is easily scalable and can be used in more complex scenarios. Pivot tables are an irreplaceable tool but there are situations where we get similar results by a different procedure, this is one of them!
When creating a Stacked Bar or Stacked Column chart, this is a convenient way to show the proportion of a product or its categories sold over a given period of time. However, even if we choose to show values on the series, it is not clear what the total sum is. Here’s a simple trick to create a chart that shows the share of each series in the sum, as well as the total sum of values.
In one of the previous “recipes” I wrote about an Excel add-in called People Graph which you can use it to effectively display the results of statistical surveys. You can also create this type of chart yourself if you have some experience with photo editing applications and by applying the technique I will show you here.
I got an e-mail today from my wife who is a big cat lover. She says YouTube suggested her to watch the „Cat Icon Array Chart“ video. And since there is often a need to show the share of a value in a whole, using icon array chart, I decided to write a “recipe” that would teach you how to present data this way, without really using charts.
We’ll use a quote from a familiar joke as an introduction to a problem you might encounter in everyday work: how to get the user to enter data only in a predefined cell group? There are several ways to achieve this, and one of the easiest is to format these cells in the desired manner (eg, set a specific background color) and to prevent entry into the cells that are not formatted in a given manner.