I came across this stock chart in the Kuala Lumpur’s edition of the Business Times while I was in Malaysia. The chart has an elegant treatment of dates. I have seen this sort of designs in many magazines and wanted an afternoon challenge to do this in Excel.
It uses alternating blocks in the plot area to indicate passing months.
The exercise wasn’t very challenging after all. I managed to capture the nice features of the original chart. To create the clean horizontal date axis, I used a column with the formula “=IF(DAY(A2)=1,A2,””)“. This formula only displays a date if it is the 1st day of the month.
The alternating month blocks was created with an area chart using a column of formulas like this: “=IF(MOD(MONTH(A2),2),3000,NA())“. This creates a pulse train that varies between 0 and 3000 across months.
About Aeternus Consulting
Related Link: Aeternus Consulting Excel Training Courses Singapore
Aeternus Consulting offers an excellent workshop Storytelling with Data Visualization using Beautiful Excel Charts. This workshop is aimed at students visualizing thesis data, managers and analysts needing to communicate in a data-driven way and leaders informing their board to drive actions.
Related Link: Making Charts Beautiful with Microsoft Excel
3 Responses
Hi,
Do you you author any presentation books?
Here’s a trick: an Excel chart that displays specific data from a large array based on your input. You do this with the (incredibly useful) MATCH and OFFSET functions, which pluck data from an array and present it in cells that the chart is linked to. Let’s say you have seven columns representing years and 12 rows representing sales reps, with the cells containing yearly sales amounts.
That is awesome! Thank you.