A step by step guide to create your an interesting Lollipop Chart
The Dataset
We have a list of video games and we have generated their random ratings using the Randbetween function.
If somehow your favourite video game is rated lower, then don't worry, these are just random numbers.....
Here is the function applied to all cells in the ratings column
=Randbetween(1,10)
When will keep Randbetween function and not paste as values as we need to generate random numbers once our Lollipop chart is finalized.
Steps involved
Step 1: Select any cell on your sheet and press Window button and full stop from the keyboard to open this box. Select a circle from Emojis
Step 2: in another cell, again press Window and full stop from the keyboard and select a hyphen from the symbols.
Step 3: In the next column, use REPT function to repeat hyphens w.r.t number of ratings of each game and concatenate it with the cell containing lollipop head. Now copy it to the rest of the cells:
=REPT($E$2,C3)&$E$1
Step 4: Next step is to do conditional formatting for column E.
- Select all the cells in range in column E against ratings.
- Go to Home > Conditional Formatting > New Rule
- Click on Use a formula to determine which cells to format
- In the formula bar type =$C3>7 and choose Green color press ok and apply.
- Again go to Home > Conditional Formatting > New Rule
- Select Use a formula to determine which cells to format
- In the formula bar type =$C3<4 and choose Red color and press ok and apply.
Step 5: You have finally got it. Now go to Data ribbon and press refresh to generate new numbers and see the lollipop charts modify.
