Search

# Shading an area chart with different colors for up & down movements [case study]

Share

We all know that area charts are great for understanding how a list of values have changed over time. Today, let’s learn how to create an area chart that shows different colors for upward & downward movements.

The inspiration for this came from a recent chart published in Wall Street Journal about Chinese stock markets (shown below).

We will try to create a similar chart using Excel.

This is what we are going to come up with.

### Creating an area chart with different colors for up & down slopes

Step 1. Gather the data

For our example, let’s use Indian stock market data for last 10 years. Specifically, BSE Sensex weekly closing prices between 1-July-2005 and 27-July-2015.

There are 3 columns in this data – Date, Closing price & Volume, as shown below. Let’s say all of this data is in a tabled named data that starts at cell B6.

Step 2. Find out when to switch colors

The next step is to find out when to switch colors.

We can add 3 additional columns to our data to spot the switches, and split data to Advances & Declines accordingly.

Here is what we get.

Detecting when a switch occurs:

When looking at closing price for a day, we need to know if the line direction has changed or not. To detect this, we can use a formula like this:

Assuming the closing price we are looking at is in cell C7,

=C7<>MEDIAN(C6:C8) will tell us if the value in C7 is switching the trend or not.

Why does this formula work? Think again. For more on this technique, refer to BETWEEN Formula in Excel.

Step 3. Expanding the data so that we can create an area chart

If we create an area chart with just the data from above step (only advances & declines columns), we end up with a chart that looks like this.

As you can see, the green & red areas (advancing & declining data) have tiny white space between them.

This is because, when we switch from green to red, the green series goes from peak to 0 and simultaneously, red series goes from 0 to peak, creating an effect like below (chart made from sub-set of data)

To create correct shading effect, we need to expand the data so that on dates when switching happens, there is a duplicate row.

See below illustration to understand what we need.

Writing formulas to expand data

We can use simple arithmetic along with healthy dose of INDEX formulas to create expanded data set. Can you figure out the formulas yourself as homework?

After expanding the data, the same area chart looks like this:

Step 4. Create area chart from expanded dataset

Select the expanded advances & declines columns and create an area chart from them. Make sure horizontal axis labels are pointing to the expanded date column we constructed in step 3.

We can add few more bells and whistles to it and come up with below output.

### How do you like area chart with different shades?

I think this is a powerful technique to quickly eye-ball data and see where directional changes are occurring, what patterns (if any) are they following etc.

If you observe carefully, our Excel version and WSJ’s charts differ in one key aspect. In WSJ chart, they are shading bull & bear markets where overall trend is upwards or downwards with minor changes during the market period. What formula / approach changes do you think are necessary to make exact replica of WSJ chart in Excel?

### Addendum – Moving average based smoothing of trends

We can use simple moving averages to smooth the trends so that we can spot upward / downward movements better.
Here is an example chart.

### Charts to show change over time

Understanding change is a key component of any analysis. Check out below charting techniques & tutorials to learn few more valuable skills.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### How to calculate WEEKNUMBER in Month / Quarter / Year with Excel?

Let’s say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month or quarter? In this article, let me explain the logic and formulas we can use Excel for this.

## Related Tips

Charts and Graphs

### Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Charts and Graphs

### Make an Impressive Interactive Map Chart in Excel

Charts and Graphs

### How to Create a Dynamic Excel Dashboard in Just 5 Steps

Charts and Graphs

### How to create a fully interactive Project Dashboard with Excel – Tutorial

Charts and Graphs

### 9 Box grid for talent mapping – HR for Excel – Template & Explanation

Charts and Graphs

### 6 Responses to “Shading an area chart with different colors for up & down movements [case study]”

1. carlos barboza says:

Chandoo thanks so much for sharing this valuable technique. I will look further into making an exact replica of the WSJ chart. Greeting from Lima, Peru. All are welcome to visit Machu Picchu!

2. Tyler Barr says:

Very great visualization technique -- the downloadable excel doc chart title lists date range as "(1-July-2015 to 27-July-2015)", rather than "(1-July-2005 to 27-July-2015)"

This is a very useful technique to use.

3. MF says:

Nice trick of using sparklines for the volume chart! 🙂

4. james says:

I'm having a problem in that what I'm using this against is a small number set (highest is 25) so I get duplicate numbers.
I've altered the formula to:

=IF(ISNUMBER(C12),IF(C13>C12,C13,IF(C13=C12,C13,0)),C13)

so that if the same number appears it carries on, but when it drops down I have the "gap tooth" effect (White spacing as mentioned above)

I was wondering if anyone would be able to help resolve this

• james says:

I believe it's something to do with the "Switch" equation because I can manually manipulate the index table and it seems to rectify this.
But I was wondering if someone had a more elegant solution?

5. Hocine Satour says:

Thank you Chandoo

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.