Elegant Dates in Line Charts

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.

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.

To learn more about how to create beautiful charts like these in Excel, check out this workshop: Beautiful Excel Charts

BP Oil Spill shown in a Microsoft Excel Chart

On 20 April 2010, a BP oil well exploded in the Gulf of Mexico, causing the largest offshore spill in U.S. history. This Microsoft Excel line chart illustrates some key events 2 months after the explosion and BP’s stock price using a common Date horizontal axis.

The resulting chart is rich in information yet it’s not cluttered. The key relationship between stock price, event and dates are tied up using the “Lollipop Approach” described earlier.

See the full pic: BP oil spill infographic in Excel (full resolution: 792x448).

references: http://en.wikipedia.org/wiki/Deepwater_Horizon_oil_spill. http://www.ft.com.

Lollipop approach to clarify Microsoft Excel line charts

Traditional Microsoft Excel line charts have a common problem. It is hard to visually associate the data points with the horizontal axis. The traditional axis line is no help at all.

We can get around this problem by using data markers. Certainly an improvement, but we still need to mentally drop imaginary lines between the points and the labels.

My solution is to add 100% negative error bars for each data point. This removes any ambiguity between the data points and the axis labels. I call this the “Lollipop Approach” for line charts! The error bars may add additional weight to the chart, but they serve a useful function.

In a later post, I will show you a beautiful excel chart example using this lollipop approach.

Singapore population infographic in Microsoft Excel

This is an info-graphic I created in Microsoft Excel. It shows the population composition of Singapore in 2009. The world map and the stick-man was from the standard web-ding font. The only exception was the Singapore flag which I copied from Wikipedia. Nothing fancy was used. Just pure Excel 2007 features, no more no less.

Who says Microsoft Excel is only for number crunching?

See the full pic: Singapore Population Info-graphic 1105x490.

Ugly Real Economic Growth Chart from Singapore Statistics-Part 2

Quoting from my favourite marketeer Seth Godin:

The only way to succeed is to be remarkable, to be talked about

So I was really amused that Peltier Tech picked up on my past article “Ugly Real Economic Growth Chart from Singapore Statistics” and was inspired to write another. Good ideas do travel.

I made certain aesthetics changes to an original chart from Singapore Statistics.
Some modest changes and this was created:

What mirthed me was that the author proposed charts that had everything I hate about Excel charts.

He merely recoloured the original ugly chart! Compare and see for yourself.

I always wanted to combine business aesthetics and business savvy in Excel charts.
In my latest workshop Making Beautiful Excel Charts I will break all the traditional rules in Excel charting. What you get are designer-quality charting ideas with Excel.

Let me summarise some typical visual sins with a page from my new book.

That being said, I whipped up another version. Without be-labouring the point, the results are obvious for everyone to see.

Let me end this with Seth Godin’s words again:

The new Dream that markets around the world are embracing is this:

1. Be Remarkable
2. Be generous
3. Create Art
4. Make Judgement calls
5. Connect people to new ideas

and we have no choice but to reward you.

A gallery of Beautiful Charts for Microsoft Excel part 5

It’s time to share some great charts I’ve come across lately. This is part of my on-running gallery of beautiful chart designs which can be re-created in Microsoft Excel.

You can look at the whole gallery with this link: Beautiful Charts for Excel

Ugly Real Economic Growth Chart from Singapore Statistics.

Singapore Statistics have the most amazing numbers on Singapore. Anything on our country’s GDP to how many handphones we have on average can be found. But the folks from SingStats seriously need to improve their charts.
Take a look here (http://www.singstat.gov.sg/stats/charts/econ.html#econA)

I could hardly make any sense out of these charts. They seems to be just thrown together with out any theme or purpose. After all the work of collecting the data, if you can’t present data in a useful manner, it’s simply a waste of efforts.

I took up the challenge to clean up this miserable chart using Excel, and try to show that beautiful charts can be useful to convery data in a clearer manner.

The improvements I made:
1. The distinct shape of our total economy is much clearer as an area chart.
2. The Manufacturing and Financial sectors are reduced to scatter chart with drop lines. The trends can still be discerned yet is less noisy compared to the original version.

There is a part 2 to this story:

Ugly Real Economic Growth Chart from Singapore Statistics-Part 2

Quoting from my favourite marketeer Seth Godin:

The only way to succeed is to be remarkable, to be talked about

So I was really amused that Peltier Tech picked up on my past article “Ugly Real Economic Growth Chart from Singapore Statistics” and was inspired to write another. Good ideas do travel.

I made certain aesthetics changes to an original chart from Singapore Statistics.
Some modest changes and this was created:

What mirthed me was that the author proposed charts that had everything I hate about Excel charts.

He merely recoloured the original ugly chart! Compare and see for yourself.

I always wanted to combine business aesthetics and business savvy in Excel charts.
In my latest workshop Making Beautiful Excel Charts I will break all the traditional rules in Excel charting. What you get are designer-quality charting ideas with Excel.

Let me summarise some typical visual sins with a page from my new book.

That being said, I whipped up another version. Without be-labouring the point, the results are obvious for everyone to see.

Let me end this with Seth Godin’s words again:

The new Dream that markets around the world are embracing is this:

1. Be Remarkable
2. Be generous
3. Create Art
4. Make Judgement calls
5. Connect people to new ideas

and we have no choice but to reward you.

A 10 year Balance Sheet Dashboard

Imagine you are the new CFO. You took over the position in the new firm and you want to look at the firm’s latest balance sheet.

You’ll probably want to see the firm’s 10 year balance sheet history.  It will be tough to make sense of things. What if you have a balance sheet dashboard instead? It contains the latest information complete with a 10 year history of each balance sheet line item! That’s truly a powerful tool in the hands of a competent accountant!

PowerUp Workshop: Making Beautiful Excel Charts

What if you could turn a drabby Excel chart:

into something like these?

For many Excel users, there is a need to produce clear, concise and even attractive charts in Excel without using expensive software. What is lacking are the clear techniques to do so. Compelled by this need, AEternus has designed a PowerUp Workshop to show participants how to achieve this objective.

The Workshop Outline:

• A collection of Formatting Chart Techniques.
• Demonstration of each technique to achieve beautiful results.
  • How to replicate a Wall Street Journal chart.
  • How to replicate a Economist Magazine chart.
  • How to replicate a New York Times chart (read why this chart is beautiful?).
• Reverse-engineer other beautiful charts.

This 1 day workshop will serve as the pre-requisite for the more advanced Creating Executive Dashboards in Excel workshop.

You can sign up Making Beautiful Excel Charts.

A gallery of Beautiful Charts for Excel part 4

I saw these couple of charts from the Standard Chartered Bank website.
Things I like about it:

1. Fresh colour scheme. Blues and greens work well together.
2. Bold titles that explain the intentions of each chart.
3. Each chart has an Increase/Decrease call out to emphasize the conclusion from the data trend.
4. Overall it’s a format that is pleasing to the eyes and easy to understand. Great job!

I hate pie charts for many reasons. But in this case, this particular pie tastes pretty good for the following reasons:

1. It looks pretty. Very nice color scheme . The green & blue hues fits nicely like Lego blocks.
2. Sorted and group slices with the biggest slices at the bottom. I can easily make out which slices are bigger.
3. I don’t feel confused. This pie chart uses only 6 slices which I can mentally organize in my head.
4. Overall clarity is well maintained with the skillful use of color, slice size sorting and small number of slices.

Next Page »