|
1. The goal of this Google Sheets formula is to end up with an inverted pyramid chart with bar sparklines that represents our data. Here's the base formula being used:Â =SPARKLINE({1-A2,A2} , {"charttype","bar" ; "color1","white"}). |
|
2. The goal of this Google Sheets formula is to end up with an inverted pyramid chart with bar sparklines that represents our data. Here's the base formula being used:Â =SPARKLINE({1-A2,A2} , {"charttype","bar" ; "color1","white"}). |
|
3. You could simply cut-and-paste that formula into your first results cell, but we'll explain it more fully to allow you to make minor changes to address different kinds of data. We've copied the data from the finished example to help our walk through. |
|
4. Where our prototype formula used reference cell A2, now our reference cell is D2. Click into the next top cell, and Type =1-D2, which means show 100% minus the amount in D2, and Press Enter. Here, D2 is 40%, so E2 should result in 60%. |
|
5. In our example, we did get 60%. To copy down this formula, with the cell selected, use your mouse to Click and Drag the blue handle, then Drop it in the last cell where you want to apply the formula. |
|
5b. Drop
|
|
6. The formula resulted in each cell being the remainder of the reference cell from 100%. You've completed the first step. |
|
7. According to Ben, for the second step we want to create an array "to capture both the original value and the 'gap' value." Click into the cell that currently has your formula. |
|
8. To tell the formula to make the first cell of the array the same as our value, and then the second cell of the array into the gap between 100% and our data value, Type ={D2,1-D2}, and Press Enter.
|
|
9. The array shifted the results to show the value in E2 and the remainder of 100% minus the value in cell F2. With your mouse, Select both cells, so we can copy down this formula. |
|
10. Use your mouse to Click and Drag the blue handle, then Drop it in the last cell where you want to apply the formula.
|
|
10b. Drop
|
|
11. You now have both the values and their gaps in columns E and F. You've completed the second step. |
|
12. For the third step, Click into the cell that currently has your formula in it, and Type =SPARKLINE({D2,1-D2},{"charttype","bar"}), and Press Enter.
|
|
13. You just told Sheets to "wrap this array with the SPARKLINE function and specify the 'bar' as the chart type." Once again, use your mouse to Click and Drag the blue handle, then Drop it in the last cell where you want to apply the formula. |
|
13b. Drop
|
|
14. You've created a bar chart using the selected array of data. You're finished the third step. |
|
15. For the fourth step, Click into the cell with the current formula, and Type =SPARKLINE({D2,1-D2} , {"charttype","bar" ; "color2","white"}), and Press Enter.
|
|
16. You've told Sheets to only show a visible color on one part of the bar. With your mouse, Click and Drag the blue handle, then Drop it on the last cell where you want to apply the formula. |
|
16b. Drop
|
|
17. The fourth step is complete, and you have the beginning of a cascading bar pyramid.
|
|
18. For the fifth step, let's align the chart to the right. Click into the cell with your current formula, and Type =SPARKLINE({A2,1-A2} , {"charttype","bar" ; "color2","white" ; "rtl",true}), and Press Enter. |
|
19. Ben noticed, however, that Sheets still has a slight colored border around the sparkline chart. Here, that's an orange border around each white bar in the bar chart. That takes us to the sixth step. |
|
20. Ben recommends flipping the data around instead of using the "rtl" property. To switch the order of the array, Type =SPARKLINE({1-A2,A2} , {"charttype","bar" ; "color1","white"}), and Press Enter.
|
|
21. That removes the bar outline. To copy the formula down, with your mouse, Click and Drag the blue handle, then Drop it on the last cell where you want to apply the formula. |
|
21b. Drop
|
|
22. You've created your first pyramid chart using bar sparklines. But what if it's not percentages for which you want a chart? Our second example uses dollar amounts, and instead of a base of 100%, it uses a base of $5,000.00. |
|
23. To accomplish this change of data type, we'll be replacing the 1 that represents 100% with the goal in the form of a number to use for comparison. Remember to also change the reference cell when you're using data from another cell. |
|
24. Note, we've copied the formula as text here to clearly show to replace D2 with A9, and replace the '1' with a number amount; this example used 5000, which does not need to be formatted as currency.
|
|
25. Now you can create and modify simple pyramid charts using bar sparklines.
|
25 steps should take 7 minutes