There are beautiful, powerful & awesome charting examples all around us. Today, I want to show you how we can harness the power of Excel to create Analytical Charts.
Analytical What?!?
To be frank, I do not know what to call these charts, so I choose the term Analytical Charts. But this is what I have in mind (see below) when I say Analytical charts:
A chart is analytical chart,
- If it is interactive
- It it can answer different questions by re-structuring same data differently
What is the inspiration for Analytical Charts?
Google Analytics. I use Google Analytics, a web-app that tells me about the visitors and traffic flowing in to my site. It gathers millions of data points every day and presents all this information in rich, easy to read views. One of the powerful features of Google Analytics is that, you can tell it to show the same information in different views so that you can answer different questions.
On top of every report in Google Analytics, we have these buttons:
When you click the button, the report is instantly restructured to answer a different question.
So how to make such charts in Excel?
The basic technique behind this is similar to the one we discussed in show one chart from many article. Since, It would be 1800 word long if I describe the process, I decided to make a short video (18 minutes) explaining how the charts are constructed. Watch the video tutorial below:
Excel Analytical Charts – Video Tutorial:
The Process for creating Analytical Charts:
- Make individual views of the data in different cell ranges.
- Name each range uniquely, like chtRng1 for tabular view and chtRng2 for percentage comparison view etc.
- Insert radio buttons (option buttons) from developer ribbon > forms, one each for a different view.
- Now, link all the radio buttons to same cell. That way, when select a view the linked cell would show corresponding number.
- Create a named range called chtSel and point it to a CHOOSE formula that would select the corresponding named range defined in (2)
- Now, select any few cells, press CTRL+C and paste them as a picture link. (tutorial on picture links).
- Select the picture link, go to formula bar and type =chtSel
- That is all. Now, you have made an analytical chart that makes your boss love you.
Download Excel Analytical Chart Example Workbook
Click here to download the example workbook. This should work in Excel 2007 or above.
Do you use Analytical Charts?
As I mention in the video, I find the “views” option in Google Analytics quite useful. It hides answers for questions that are not yet asked. But with just a click, I could visualize information in a different way.
What about you? Do you find analytical charts useful or as clutter? How would you implement them? Please share your ideas and techniques using comments.
66 Responses to “Use Analytical Charts to Make your Boss Love You”
Thank you, thats great and astonishingly simple!
Chandoo, this is great! It's a very elegant and simple way to have a dynamic "analytical" charts feature in Excel. We will be making this a standard part of our Excel Dashboards practice from now on. Thanks for the great tip!
Chandoo, great tip. I really like the idea and have thought of similar solutions in the past. I have one problem which really is a bit of a deal breaker for me (at the moment). The camera tool, or picture link, image quality is very rough - especially when dealing with representing numbers and text. It has such potential, I just wish they would clean it up a bit. If the picture was an exact representation of what is seen in the source, I would be sold, and I would use this all the time. As it is now, I tend to use buttons (i.e. icons, but you could use radio buttons if you wanted) which are hyperlinks, or macro commands to navigate to a different sheet which shows the data you desire to be displayed. Definitely more of a burden, but no image quality issues. Thanks for sharing!
Hi Chandoo,
Great post. I like to call these types of chart setups "dynamic charts" with dynamic meaning they change based on user interaction.
All the best,
Matt
Awesome!
Amazing trick chandoo! It is very helpful to us. Nice way to display things as per user wish how he wants to see. Keep it up...,....
I'm so glad that you're showing all of these tips and tricks for Excel 2010. Awesome!
Wow! Super Awesome! Thank you very much teaching us this!
@Tom... they did clean-up the picture link quality in Excel 2010. However, it is still not up to the mark.
That said, you can also write a simple macro that would take dynamic snapshots of the ranges and show them. Although, the macro might be a burden, I think it is worth it when you want precision and sharpness.
@All.. thank you very much. I am glad you like this.
That was an awesome post.. I am a SEO guy.. Looking for this type of reports.
Thanks for sharing...
- Ravi Kumar
Chandoo,
What an awesome way to show your work an impress your boss, colleagues, friends and everyone around. Great work, keep it up.
All the best
Hi,
This is a brilliant stuff and I like it very much. Could not hold back from trying the same.
Just a quick one, I was doing all these days by creating the same layout in different sheets, and giving hyperlink to different sheets and finally by hiding the sheet tabs.
Regards,
Ramnath
Nice post Chandoo.
It really shows how a few simple tricks and ingredients in Excel can stir up a delicious result!
However, I tend to stay away from using the camera tool for the exact same reasons as Tom mentions. Therefore, the potential snapshot macro you are referring to would be of great interest.
Best regards,
Ulrik
Chandoo, one query..
Do all picture links have to be of the same size to use this method..? Would different picture link size work?
Apologies if I have asked a stupid question..
Rj
@Rohit... That is the beauty.. the picture links can have varying sizes (in fact, they are like that in the example file too)
Chandoo, one query ..
Good morning, impressive this post. I was wondering if you could put a picture of the CHOOSE formula that was used in the example file ... Thanks
Chandoo - thanks for the response to my comment. I hadn't actually noticed a difference in the Camera Tool quality, but any improvement is welcome. I would also like to see how you would use a macro to show dynamic snapshots of ranges. Are you talking about copying and pasting on the fly? Or showing an actual image link of a range? If it's the later, I'm assuming you'd still have the image quality issues.
Anyways, thanks again. This is good stuff...
@Tom... I meant dynamic copy pasting of the ranges as PNG or enhanced meta files. Let me work on the macro sometime in next week or two and share it with all.
Hello Chandoo,
First at all, I'm so grateful for all I've learned from you during the last six month. I'm checking this new awesome chart and I had a problem respect to the grafic. I can't see the grafic when I go from "Table" to "Percentage"
I think it happen because I'm working with Excel 2007, but being honest I'm not sure at all. Hoping you can help me I say good bye!
Greeting from Peru!
Amazing, Chandoo!!
Thank you
Ah...now that's a cool idea. I'll definitely be interested to see that one 🙂 I'm curious where you will save the files temporarily (if need be - I'm assuming so). I recently built a userform with a chart preview which updated everytime a variable changed using a similar technique to the one you are describing, I think. This would be VERY useful indeed.
Tom
Great stuff as usual! Very simple and easy to follow as always.
This is awesome! Thanks for posting. I was able to re-create everything and plug it into an existing spreadsheet. Thanks again!
Nice post Chandoo,,,,, Keep on work.....
thanks
Istiyak
Nice analytic chart chandoo!
however,
#1 your color scheme choice for the pie chart legend is epic fail, but i believe your example is just to show how to differentiate different categories.
#2 by your explaination number 2, we need to view the same info from different angle, hence consistent & meaningful color scheme is very important here. e.g why the performance chart is blue whereas you can use green to denote positive value as in comparison chart. what does olive green in the pie chart signifies?
@Xsaed... very good points...
#1 I agree. I choose the scheme because it makes the task of adding legends dead-simple. Also, I was following the google analytics example as much as possible 😛
#2 I agree. this is my mistake. I should have used same colors.
Great stuff chandoo...
Chandoo
This is a Great post!
I'm from Santo Domingo, and I think you do an amazing work with this site.
Congratulations for your work!!
Is there any possibility of sending reminder mail based on the due date set in the excel sheet.Even if we did not open the sheet.
Hi Chandoo,
First of all thanks for the article.
I am using Excel 2007 and have downloaded your example file. But I observe that it does not update the percentage pie chart and can see only the data set on the left hand side.
If you need to view the pie chart then you have to scroll down once and come back. I think Excel is not updating the pie chart till you scroll and have it on screen.
Please comment and help me how this can be done.
Thanks in advance.
@Chandoo - Sir, Thanks a lot for shairng such a wonderful tip.
I tried to replicate the same (as you've explained above). Everything was working fine but the only problem i'm facing is 'chart is not getting updated'.
As Ashwin pointed, i too am facing the same. No idea why the chart part is not getting updated. And from what I observe, the chart gets updated only if i move my cursor to that concerned data_range and press F2, and then 'enter'. The chart then gets updated. No idea why this is happening.
Can anyone please throw some light on this. Thanks a ton !
100% awesome. It's verging on magic.
Chandoo, hats off to you, you are making all our live easy & magical by posting such wonderful videos....Thanks a ton, boss.
Hey Chandoo,
Thanks for this tip. Could this be combined with a chart slider? For example I want to change the display but then when in the display I want to use a slider to move through the data.
Any thoughts?
@Stu
Have a read of : http://chandoo.org/wp/2010/11/04/analysing-large-tables/
Chandoo, that one is great - thank you!
Ehm, if I don't get things wrong completely the pictures do not refresh when the data changes, is that right?
I am actually planning a dynamic dashboard that fetches data automatically. Thus it would be useless for me if those pictures don't update. Shall I attach a Macro to those Radio Buttons that create the pictures everytime someone clicks on them?
Cheers,
Andy 🙂
@Andy... the beauty of picture links is that, they are Dynamic... So if your data changes the picture link updates too (assuming your formulas are on automatic calculation mode). See here: http://chandoo.org/wp/2010/10/19/how-to-use-picture-links/
Whoops, got it wrong. That's ingenious anyway!
Thank you Chandoo :))
Chandoo, thank you!!! I'm from Brazil. Parabéns pela sua planilha!
Thank you for this tutorial - so simple once you broke it down...and thank you for your site - it's so awesome!
Chandoo,
Thanks for this and all your work - awesome! I'm using Excel 2003 (my employer won't update). The choose function appears to work differently in Excel 2003 than in Excel 2007 and I can't get it to return a range reference.
I guess I could use buttons that invoke a macro to change the picture reference rather than use the radio buttons but I was wondering whether there is any way around this without using VBA?
Thanks!
@William
I don't think it is the choose function that is in error
Here is the sample file in Excel 97/03 format:
http://chandoo.org/wp/wp-content/uploads/2011/03/analytical-charts-example-97.xls
It works using the same Choose function.
Check your 's and format of sheet names
Simplify sheet names to have no spaces etc
Hi there,
This is really cool. Many thanks for uploading it. I just need to now figure out how can I use this for my own data
Raj
UK
Dear Chandoo,
it's right that you are a good teacher. we learn a lot from your blessed skills.
In 'Chart 2 - Percentage', using cond. formatting for 10,9,8,7,6 & so on, the text exist but not visible in your XL file. but when i use it, the text are remain visible even in any color they are.
how it could be unvisible?
thanks in advance for your comments.
I can't find the choose formula in this file! Please help 🙁
Congratulations! Great, simple and beautiful solution.
André Luiz Bernardes
bernardess@gmail.com
http://brzexceldeveloper.blogspot.com/
Santos - SAO PAULO - BRAZIL
@kapila
try Formulas>Name Manager and there you will see the chtSel CHOOSE reference
Great technique...but unfortunately this only seems to work with ranges that are the same size. I have a series of different charts by region and some have 5 lines of countries, some have 1. When the 1 country region pops up, its hugely stretched out to the same area as the 5 line table.
I usually pile up the linked pictures on the dashboard and hide/unhide the pictures using VBA depending on the option buttons. Any tips appreciated!
Chandoo ...i should compliment your thought process...i have some amazing things done on excel but this excellent !!!
Hi Chandoo
I need your help ..
I have a weekly chart From week1 to week 6....
this will continue.. All the chart should be shown in a single sheet as like a chart... we can use a scroll option.
Pls help..
[...] Showing one chart analysis from many – Analytical charts in Excel [...]
We are not worthy.
I bow down to your greatness.
Thank you Chandoo!
Hey Chandoo!
Awesome charts ..... the only issue i have is my employer uses ms excel 2003 and some advanced stuff that you teach me cannot be performed on 2003. If possible in the future check with 2003 compatibility as well.
hi there, love the site; i have a quick (hope so) question;
i have many sheets of data, and i ahve charts that corraltes to each of those data; around 16 charts; so i want to be able to select specific time frame to display in those charts; i have an option entering the number of dates, where it shows it, but it always either ends or starts with that one date that is specified on OFFSET; - i created drop down menu in data validation for the date, but is there a way now to link that drop down menu on top of those charts (together with existing =SERIES formula, not affecting other formulas? meaning that i want specific time frame to be visible on sharts?! can you do it for all of them so they change
simoultaneously or it has to be individually.
Hope it makes sense, but i need help on this!!!! if not drop down menu, what is the other option to display certain time frame on charts?
Great!
Like this tutorial and yes: "Chandoo rocks"!!
Amazing the number of thing we can learn here.
I have a question on this tutorial: I don't like the L&F of the checkbox (font size) and it seems you can't change it.
is it possible to use for the selection and switching the linked image, something with hyperlinks like the buttons in the bottom ("Visit Chandoo.org", "Sign-up..")
Ok got a solution by reading another tutorial on chandoo.
Wondering if possible to do it without VB function.
Great work! I have updated it to automatically populate with Google Analytics data using the free Analytics Edge add-in, plus fixed a few things that seemed not-quite-right:
1. updated to current Sessions terminology from Visits.
2. changed from just top 10, to top 9 + 'other' category. This way the pie chart accurately shows the proportions of traffic.
3. Note that the last chart comparing to site average is a little weird for the Sessions metric, and my calculation of 'site' average is just as wrong/arbitrary -- it really should be (total sessions)/(#unique sources), but even that is an almost meaningless metric. If people change the metric used, they should be aware of this, and never take an average of an average...
4. rotated the pie slices back to 0 degrees (for some reason they were rotated?)
5. automated your scaling factor through formulas.
My version: http://www.analyticsedge.com/2014/08/interactive-analytical-charts/
Hi Chandoo,
I have been visiting your blog as the go-to source for neat tricks on Excel - awesome job!
These days, I have been thinking - is there a way to present my data (which is basically number of employees who are without a project, at onsite location, for which we have a quarterly target to meet) so as to make it look like the cricket scores that they keep showing during an ODI match?
The idea is:
1. There is a target to be met (which is similar to the runs to be scored by the opposing team, once the other side has finished batting)
2. Week by week progress (which is like an over-by-over update)
3. Current rate (which is like run rate)
4. Prediction - with the number of weeks left in the quarter, at what rate will we need to progress (similar to the projected run rate that the team will have to bat at, in order to meet the target score)
Possible? We have so many reports going out, and they all look the same - boring excel tables with so many numbers! I thought this would be fun to look at. Thanks a ton!
[…] Inspired by an article from Chandoo.org […]
Thank you for this additional great idea I can use in my reporting. Over the last few years, you guys, and this site, have been a great help to me.
I have one question pertaining to the "Percentage" option of this analytical chart... There is a colored legend to the left of the "source/medium" data column and the colors correspond with the pie chart. I noticed they do not change when I change the chart colors. How can I make them dynamic to match whichever chart color I select?
WOW its beautiful I created one dashboard using this and it works....... Thank you so much chandoo....... You Rock 🙂
If i have a set of pivot tables and i have linked them to a picture link as explained on the video, but the pivot table change...How do i make the link dynamic to adjust to the new pivot table
Very very helpful!
Hi Chandoo, would u plz help on the below question:I hv schedule code,raised against,raised by, vendor code,scon/po , defect ,incident date, response received date,tat date and Tat status data in Excel sheet.all are A to I column wise,so how to prepare extra ordinary chart and dashboard reports.i have to prepare weekly, monthly, quarterly, yearly report.i HV to show how many raised Nd closed and vendor Tat wise data analyze.how to prepare analytical chart.plz help me on the above.
Thanks in advance for your help.
Thanks, great addition!!!!