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.
Looks interesting? Read on…
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?
Please examine the downloadable workbook to understand these formulas more.
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.
Your chart is ready now.
We can add few more bells and whistles to it and come up with below output.
- The volume chart at the bottom is a sparkline
- We can find longest bull & bear rallies using longest winning streak formula
Download Area Chart with different colors for up & down slopes workbook
Please click here to download area chart with different colors workbook. Play with the chart & formulas to learn more.
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?
Also, do share your feedback about this chart and how you are planning to reuse the concepts at your work.
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.
You may download this workbook to examine the formulas & 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.
- Narrating the story of change – Case study on how fast America changes its mind
- Advances vs. Declines chart
- How tax burden has changed over years – interactive Excel chart
- Use indexed charts when analyzing change over time
- Never show simple numbers in your dashboards
- Comparing with benchmarks – shading under / over achievement
21 Responses to “How to Filter Odd or Even Rows only? [Quick Tips]”
Infact, instead of using =ISEVEN(B3), how about to use =ISEVEN(ROW())
So it takes away any chance of wrong referencing.
I like Daily Dose of Excel
I like it.
Just a heads up, you do need to have the Analysis ToolPak add-in activated to use the ISEVEN / ISODD functions. An alternative to ISEVEN would be:
=MOD(ROW(),2)=0
rather than use a formula, couldn't you enter "true" in first cell and "false" in the second and drag it down and than filter on true or false.
Just for clarification, is Ashish looking to filter by even or odd Characters or rows?
so many functions to learn!
Nice support by chandoo and team as a helpdesk. Give us more to learn and make us awesome. Always be helpful.......
In case you want to delete instead of filter,
IF your data is in Sheet1 column A
Put this in Sheet2 column A and drag down
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)
(This is to delete even rows)
To delete odd rows :
=OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)
If your numbered cells did not correspond to rows, the answer would be even simpler:
=MOD([cell address],2), then filter by 0 to see evens or 1 to see odds.
I sometimes do this using an even simpler method. I add a new column called "Sign" and put the value of 1 in the first row, say cell C2 if C1 contains the header. Then in C3 I put the formula =-1 * C2, which I copy and paste into the rest of the rows (so C4 has =-1 * C3 and so forth). Now I can just apply a filter and pick either +1 or -1 to see half the rows.
Another way, which works if I want three possibilities: in C2 I put the value 1, in C3 I put the value 2, in C4 I put the value 3, then in C5 I put the formula =C2 then I copy C5 and paste into all the remaining rows (so C6 gets =C3, C7 gets =C4, etc.). Now I can apply a filter and pick the value 1, 2, or 3 to see a third of the rows.
Extending this approach to more than 3 cases is left as an exercise for the reader.
Another way =MOD(ROW();2). In this case, must to choose betwen 1 and 0.
[...] How to Filter Even or Odd rows only [...]
very different style Odd or Even Rows very easy way to visit this site
http://www.handycss.com/tips/odd-or-even-rows/
Thanks for the tip, it worked like magic, saved having to delete row by row in my database.
Thanks!
Thankssssssssssssssss
Hi Chandoo- First of all thanks for the trick. It helped me a lot. Here I have one more challenge. Having filtered the data based on odd. I want to paste data in another sheet adjacent to it. How can I do that?
For Example-
A 1 odd
B 3 odd
C 4 even
D 6 even
I have fileted the above data for odd and want to copy the "This is odd number" text in adjacent/next sheet here. How can I do that. After doing this my data should look like this
A 1 odd This is odd number
B 3 odd This is odd number
C 4 even
D 6 even
Hi! Could you please help me find a formula to filter by language?
Thank you!
Chandoo SIR,
I HAVE A DATA IN EXCEL ROWS LIKE BELOW IS THERE ANY FORMULA OR A WAY WHERE I CAN INSTRUCT I CAN MAKE CHANGES , MEANS I WANT TO WRITE ONLY , THE FIG IS FRESH, BUT IN BELOW ROW IT WILL AUTOMATICALLY TAKE THE SOME WORDS FROM FIGS AND MAKE IN PLURAL FORM , WHILE USING '' ARE'' LIKE BELOW
The fig is fresh - row 1
Figs are fresh - row 2
The Pomegranate is red - row 3
Pomegranates are red - row 4
=IF(EVEN(A1)=A1,"EVEN - do something","ODD - do something else") with iferron (for blank Cell)