Make Your Excel Data Chartable With This EPIC Excel Formula
We’ve all been there! We’ve been tasked with analysing a dataset in Excel. It’s big – thousands of rows of data, and the clock is ticking. All the numbers are different.
How to even get started? It would be great to create an Excel chart. But, simply throwing all the data into the chart creates nothing useful. What to do?
Analysing a continuous dataset in Excel is actually easy if you know the steps … and if you know the one Excel formula that makes it all happen. Let’s think about it conceptually first, and let’s start with a key concept – a bin. Yes, I know that’s where you want to throw your laptop when trying to chart data in Excel! But, the concept of a ‘bin’ is key to this task.
It would be great to ‘categorise’ the data by creating intervals and then counting the number of entries that fall into each interval. For example, if we were looking at salary data, we could create intervals between 1 and 30 000, 30 000 and 60 000 and 60 000 and above, then count how many pieces of data fall into each one. Do you get the idea?
This analysis is not detailed enough, but hopefully it illustrates the point: intervals, or ‘bins’ are the best way to analyse continuous numerical data in Excel.
If you understand the concept, you might be asking if there is a formula that makes this all happen…?
Guess what – there is! It’s not a new formula or a particularly fashionable one, but it’s incredibly powerful. I use it in all my data analysis work and I have never worked out why people don’t use it more – they is very little coverage of it on YouTube. Sometimes it pays not to follow the crowd.
I give you: the FREQUENCY formula.
If you can understand the concept of a ‘bin’, you can then define them in Excel, and let FREQUENCY do the work. It’s very simple, input a number in a cell, with more numbers beneath it in the same column, making sure the smallest number is at the top and the biggest at the bottom.
Here is the genius: the FREQUENCY formula takes these numbers and creates what are known as ‘implied ranges’. How does this work? Excel looks at the number and converts it to an ‘upper bound’. It becomes the highest possible number that fits in that bin.
Excel then works down the list, creating intervals using the number in the row as the upper bound, and the number in the row ABOVE as the lower bound. We can’t see this happening, but you can label the rows (as I do in the video) to help you understand. And, it all makes sense once you create the formula.
Once this setup is complete, you can go ahead and create the FREQUENCY formula. Because of all the background calculations it entails (Excel does a huge amount of work in the background to make the magic happen) it has to be entered in a special way.
Don’t worry, the steps are not too intricate though you are unlikely to get it right fist time. Don’t give up! Practice makes perfect and once you have got the knack, it’s almost impossible to forget.
The result is a simple but game-changing analysis of any continuous numerical data processed into ‘bins’ in Excel. Phew!
The best bit: this setup can be translated into a chart very easily. Simply select the row labels and the cells containing the formulae then insert a column chart. Excel loves this kind of chartable data and now comes the fun part: making the chart look good!
I hope you enjoy the FREQUENCY formula and the simplicity it brings to continuous numerical data analysis in Excel. Let me know how you get on in the YouTube comments.
