Ever wanted to make an area chart with up down colors, something like this? Then this tip is for you.
How to create area chart with up / down colors?
Simple. You need,
- Data with positive & negative values.
- optional: A cup of coffee or a cold beverage of your choice.
Start by creating a regular area chart. You will get this:
Now, select the are and go to format settings (use shortcut CTRL+1).
Go to fill color for the area and change it to gradient fill.
Set it to one of the default gradient fills, you will see a screen like this.
The last step: Setting two color gradient
We are nearly there. Take a victory sip out of that coffee cup or cold beverage. First calculate the mid point for our gradient stops. This will be =maximum up value / (maximum up value + maximum absolute down value)
For example, if your data has +5 has maximum positive value and -5 as maximum negative value, then the gradient stop will be 50% since 5 divided by (5+5) is 50%.
Take another sip and set the gradient stops as shown below.
- Create 4 gradient stops (most of the default gradient settings have 4 stops)
- Set stop 1 & 2 to positive color (say blue)
- Stop 3 & 4 to negative color (say red)
- Adjust the position of 2 & 3 to the gradient stop calculation you have done earlier.
- Make sure Gradient is linear with 90 degrees
Done. Your area chart with positive / negative colors is ready. Admire its beauty while finishing your beverage.
Watch video tutorial: Area chart with positive / negative colors
If you are not sure about the whole gradient color trick, check out this video. I explain clearly the idea with few examples (plus there is a bonus trick in there). See it below or head over to my YouTube Channel for it.
Download Example File
Please click here to download a sample file with up down colors for area charts. Play with the data, examine the chart settings to understand this better.
More charting tricks
If you enjoyed this charting trick, there is more where it came from. Check out below examples and spice up your dull chart life.
Cropped chart: When some values are too big
11 Responses to “Fix Incorrect Percentages with this Paste-Special Trick”
I've just taught yesterday to a colleague of mine how to convert amounts in local currency into another by pasting special the ROE.
great thing to know !!!
Chandoo - this is such a great trick and helps save time. If you don't use this shortcut, you have to take can create a formula where =(ref cell /100), copy that all the way down, covert it to a percentage and then copy/paste values to the original column. This does it all much faster. Nice job!
I was just asking peers yesterday if anyone know if an easy way to do this, I've been editing each cell and adding a % manually vs setting the cell to Percentage for months and just finally reached my wits end. What perfect timing! Thanks, great tip!
If it's just appearance you care about, another alternative is to use this custom number format:
0"%"
By adding the percent sign in quotes, it gets treated as text and won't do what you warned about here: "You can not just format the cells to % format either, excel shows 23 as 2300% then."
Dear Jon S. You are the reason I love the internet. 3 year old comments making my life easier.
Thank you.
Here is a quicker protocol.
Enter 10000% into the extra cell, copy this cell, select the range you need to convert to percentages, and use paste special > divide. Since the Paste > All option is selected, it not only divides by 10000% (i.e. 100), it also applies the % format to the cells being pasted on.
@Martin: That is another very good use of Divide / Multiply operations.
@Tony, @Jody: Thank you 🙂
@Jon S: Good one...
@Jon... now why didnt I think of that.. Excellent
Thank You so much. it is really helped me.
Big help...Thanks
Thanks. That really saved me a lot of time!
Is Show Formulas is turned on in the Formula Ribbon, it will stay in decimal form until that is turned off. Drove me batty for an hour until I just figured it out.