Two charting principles we hear all the time are,
- Sort your chart data in a meaningful order.
- Show only relevant information, not everything – because un-necessary information clutters the chart.
Today we will learn a dynamic charting technique that will mix these two ideas in a useful way. I call this a Top X chart.
Note: This article uses the concepts from How to make chart data ranges dynamic. I suggest reading that article first if you haven’t.
What in the name of 3d square pie is a TopX chart?
A top-x chart is an interactive (or dynamic) chart which automatically sorts the data from top to bottom and displays only TOP ‘X’ items and showing the remaining balance as the last item. Users can adjust the value of ‘X’ and chart will be re-drawn to show more (or less) values.
See this example implementation:
How to make a Top X chart using Excel – 5 Step Tutorial
1. Have your data ready
It should be in 2 columns – first column – the attribute (for eg. customer name) and second column – the value. Lets assume the data is in range A1:B10.
2. Add 3 dummy columns
We need to add 3 dummy columns to this list. (you can do away with dummy columns if the list is sorted).
- First dummy column – to make the values unique. We just take the value in column B and make it unique in Column C by adding a small incremental fraction to it. Something like =B1+10^-6*ROWS($B$1:B1) will do. [Help on ROWS formula]
- Second dummy column – to get first X sorted customer names.
- Third dummy column – to get first X sorted sales values. We use LARGE excel formula [14 more powerful excel formulas] for both these columns.
It is your home work to figure out how to write these formulas.
3. Find a cell where user can input the X
Lets call it $F$2.
4. Update the dummy column formulas
We need to update the formulas in dummy columns 2 & 3 so that we can show “all remaining customers” as well.
To Do this, you can add an IF formula that would check if the number of the customer is >X and then just show “All remaining” with the sum of remaining values. Remember, your IF formula should be smart enough to show empty values if the row number is >X+1.
At this point, the data table should look something like this for X=5
5. Finally, select Dummy column 2 and 3, make a chart
We will re-visit our tutorial on how to make charts with dynamic ranges of data. We use the same concepts to make this interactive top x chart.
So make a named range pointing to the result of an OFFSET formula. If this sounds like turkish, I suggest getting a cup of coffee and reading the charts with dynamic ranges post. Now.
Once you have created the named range, just insert a new chart and use the named ranges as data sources. Format the chart a bit if needed and you should have a Sparkling Top X Chart, ready to fly.
Why Top X charts are cool?
- Top X charts let users play with them and find what they want. They are better than static versions.
- The show the necessary while hiding the rest.
- They show data in sorted order, which is awesome.
- You can easily build up on this concept to make them more presentable / fun. For eg. you can add a slider control and point it to cell F2.
Go ahead and download the Top X chart Template
Click here to download the topx chart template [Click here for Excel 2007 version, it is even more awesome] Play with it to learn how the formulas are working.
This is a slightly complicated chart, so beginners, you may want to jump around PHD and to get a grip on the key concepts.
What are your views on Top X Chart?
Please share your ideas and implementations suggestions using comments. I *love* to hear what you think about this.
Other Charts you can try:
Check out some of the excel dynamic charts to get inspired.
12 Responses to “Analyzing Search Keywords using Excel : Array Formulas in Real Life”
Very interesting Chandoo, as always. Personally I find endless uses for formulae such as {=sum(if(B$2:B$5=$A2,$C$2$C$5))}, just the flexibility in absolute and relative relative referencing and multiple conditions gives it the edge over dsum and others methods.
I've added to my blog a piece on SQL in VBA that I think might be of interest to you http://aviatormonkey.wordpress.com/2009/02/10/lesson-one-sql-in-vba/ . It's a bit techie, but I think you might like it.
Keep up the good work, aviatormonkey
Hi Chandoo,
You might find this coded solution I posted on a forum interesting.
http://www.excelforum.com/excel-programming/680810-create-tag-cloud-in-vba-possible.html
[...] under certain circumstances. One of the tips involved arranging search keywords in excel using Array Forumlas. Basically, if you need to know how frequent a word or group of keywords appear, you can use this [...]
@Aviatormonkey: Thanks for sharing the url. I found it a bit technical.. but very interesting.
@Andy: Looks like Jarad, the person who emailed me this problem has posted the same in excelforum too. Very good solution btw...
Realy great article
"You can take this basic model and extend it to include parameters like number of searches each key phrase has, how long the users stay on the site etc. to enhance the way tag cloud is generated and colored."
How would you go about doing this? I think it would need some VB
Hi,
I found the usage very interesting, but is giving me hard time because the LENs formula that use ranges are not considering the full range, in other words, the LEN formula is only bringing results from the respective "line" cell.
Using the example, when I place the formula to calculate the frequency for "windows" brings me only 1 result, not 11 as displayed in the example. It seems that the LEN formula using ranges is considering the respective line within the range, not the full range.
Any hint?
@Thiago
You have to enter the formula as an Array Formula
Enter the Formula and press Ctrl+Shift+Enter
Not just Enter
Thank you, Hui! I couldn't work out how this didn't work
is there a limit to the number of lines it can analyse.
Ie i am trying to get this to work on a list of sentances 1500 long.
@Gary
In Excel 2010/2013 Excel is only limited by available memory,
So just give it a go
As always try on a copy of the file first if you have any doubts
Apologies if I am missing something, but coudn't getting frequency be easier with Countif formula. Something like this - COUNTIF(Range with text,"*"&_cell with keyword_&"*")
Apologies if I missed, but what is the Array Formula to:
1. Analyze a list of URL's or a list of word phrases to understand frequency;
2. List in a nearby column from most used words to least used words;
3. Next to the list of words the count of occurrences.