Charts are great way to tell story about what is going on in your business. But they also feel a bit too impersonal and meh. How about adding your personality to them? I don’t mean making them tall, dark and pretty. I mean using hand-drawing style to make them pop out. Something like this example of hand-drawn chart:
The best thing is, You don’t need to actually draw these charts by hand. We can use a powerful charting trick to get these charts automatically generated (and linked) to your data. Interested? Read on to learn how to create hand-drawn charts in Excel.
Hand-drawn charts – Set up your data
Let’s say you have some data like this:
Add 2 more columns so that you can split the data in to Head & Rest like this:
Make a regular stacked column chart from your data
Select head & rest columns and insert a normal stacked column chart. Make sure head is on top of rest. You will get this:
Time to get drawing – create head & body images
Using your favorite drawing program (MS Paint / Power Point / Paint.Net or good old Excel itself), make a column drawing. Use the free form scribble tool from Insert > Shape to create these drawings in Excel or Power Point. See this demo:
How to create a hand-drawn column or bar in Excel?
Crop and split the drawing in to head & rest:
Once you made the drawing, paste it in to Excel as an image. Using Format ribbon, crop this in to head & tail as shown below:
Replace the column fill with images now – Ctrl+C Ctrl+V time…
Time to use the most important shortcuts in the world. CTRL+C and CTRL+V.
- Copy the head image from your drawing (CTRL+C)
- Select head series in the chart
- Paste (CTRL+V)
Bonus tip: Use images and shapes in your charts to prettify them
That is all. Your hand-drawn chart is ready. Share it with a colleague or boss and see them drool.
More hand-drawn inspiration for you…
Here are few more examples of what you could achieve with this technique.
Hand-drawn bar chart:
Hand-drawn line chart:
Caution: Don’t go overboard
I am a big fan of story telling with charts. While I appreciate the flexibility and possibilities Excel (and other tools like Power BI) offers, I strongly recommend that you do not go overboard with formatting charts. When used in moderation (or for a particular situation) these charts can evoke a laugh, thought or both. But when used in excess or out-of-place these can look silly. You have been warned.
See the entire thing in 90 seconds
Check out this recipe style video (with peppy background score) to re-cap all the key steps for creating hand-drawn charts.
You can also watch this on Chandoo.org YouTube Channel.
Download hand-drawn charts template
Click here to download free template with a few charts. Paste your data to get the charts or use the images elsewhere.
More creative charting techniques for you…
If you liked this hand-drawn chart, you will love below examples too:
Budget vs. Actual chart with variance & emotion:
Salary & Performance in a jitter plot
Twisted column charts (E90E50 Charts)
Tried your hand at hand-drawn charts?
Excuse the pun. How did it go? Did it draw crowd’s attention? Share your story and examples in the comments.
5 Responses to “Preparing Profit / Loss Pivot Reports [Part 2 of 6]”
[...] Preparing Pivot Table P&L using Data sheet [...]
[...] Preparing Pivot Table P&L using Data sheet [...]
[...] Preparing Pivot Table P&L using Data sheet [...]
I am not getting sound from the videos. I have checked all the settings and spent several hours searching the Internet to no avail.
Has anyone else had this problem?
Is there anyway to get the Grand Total to be broken out in the same fashion as the items above it? For instance, if you have in column 1, widget a, widget b, and have their sales by month in column 2, I'd like to see the grand total also be by month, for widget a & b combined.
I can't get anything other than a single line for the grand total, rather than the same format as the data above.
Widget A Month Sales
Jan 100
Feb 200
Widget B
Jan 150
Feb 250
Grand total - here I would also like to have Jan, Feb.
Jan 250
Feb 450