As usually is the case, there were thousands of rows of data. There is no way she could have used Excel in-built charts as that would have taken her ages to create charts for each set of data points. This got me thinking, and fortunately, conditional formatting came to the rescue. I was able to quickly create something neat that fits the bill. Suppose you have sales data for 12 months for three products (P1, P2, and P3). Now you want to create a 100% stacked bar chart in Excel for each month, with each product highlighted in a different color. Something as shown below:
Download the Example file
First, you need to calculate the percentage breakup for each product for each month (I was trying to make a 100% stacked chart remember!!).
To do this, first create three helper columns (each for P1, P2, and P3) for all the 12 months. Now simply calculate the % value for each product. I have used the following formula:
Once you have this data in place, let’s dive in right away to make the stacked chart
Hide the helper columns, and you have your dynamic 100% stacked bar chart ready at your service. Now it’s time to bask in the glory and take out some time to brag about it Try it yourself.. Download the Example file from here If you have enjoyed this tutorial, you might also like these:
Creating a Sales funnel chart in Excel. Creating a Gantt Chart in Excel. Creating a dynamic Pareto Chart in Excel. Step Chart in Excel – A Step by Step Tutorial. Creating Heat Map in Excel Using Conditional Formatting. How to Quickly Create a Waffle Chart in Excel. Excel Sparklines – A Complete Guide
While working on it be careful cos the $K$4:K4 part seems to change all of a sudden while adding the other conditional formatting rules, so you need to check back at end for any such seemingly random changes that may have occurred. Next process is to try and figure out how to make this with just 10 columns, cos 100 columns is insane and may slow down a huge worksheet… Can you help me understating the Formulas and the results — I am not getting the logics and not the reaching the solution too. =COLUMNS($K$4:K4) no of columns =1 which is less than or equal to Cell g4 =AND(COLUMNS($K$4:K4)>$G4,COLUMNS($K$4:K4)($G4+$H4),COLUMNS($K$4:K4)<=100 ??? Hope this helps! I don’t have a ready format for more than 3 columns, but if you get stuck, feel free to comment and I can help you out. Actually if you’ve got little time I’d (and probably a few others) would need help converting this into just 10 columns, cos 100 is just too much processing power. The loss in accuracy is not an issue.
