Excel Data Analysis For Beginners: Learn This Formula FIRST
Excel Data Analysis For Beginners: Learn This Formula FIRST
There are so many Excel formulae and techniques out there. So what Excel formula should Excel beginners learn first?
For me, the answer is simple: the COUNTIF formula.
COUNTIF does exactly what it suggests: it counts cells IF they meet a certain criteria.
Say you have a column with “Apples” and “Pears” in, you can say to COUNTIF, “look at that column and tell me the number of cells with ‘Apples’ in.” It counts cells if they meet a certain criteria – in this case if they contain a value. Get the idea?
It’s simple but actually rather profound when you think about it. What do you typically want to do when you analyse data? You probably want to ‘filter out’ certain rows, as we filtered out ‘Apples’ above.
Then, you may wish to perform an operation on these filtered rows – get an average or sum up the total, for example (I recommend AVERAGEIF(S) and SUMIF(S) for that, by the way).
But, the most basic information you would want is … the number of filtered rows. How many times does this piece of data (eg. ‘Apples’) occur? It’s the most simple operation in data analysis: counting. It’s why you need COUNTIF – it’s the formula that counts.
Here are some other benefits of COUNTIF. It’s a great data validation tool because it encourages us to explore the dimensions of the dataset. I like to ‘close the loop’ on a data analysis use COUNTIF by identifying the unique values in a dataset and counting them. If the total of the counts equals the number in the dataset, you have accounted for all the rows in the dataset and ‘validated’ your data. If not, time to start cleansing …!
It’s also a great precursor to visual analysis. Can’t get Excel charts to work? You’re probably not preparing the data properly first. COUNTIF facilitates quick tabulated analyses, and tabulated data can mean only one thing: easy charting! Yes, with COUNTIF, all we have to do is select the data and the chart type and there is it: the easiest chart you’ve ever created.
And let’s not forget the ability to ‘scale up’ a counting operation with COUNTIF’s sister formula – COUNTIFS. Yes, adding multiple criteria to the calculation is easily done, as we demonstrate in the video.
For these reasons, it’s the first formula aspiring Excel data analysts should learn. It might allow you to build confidence and move on to even more sophisticated analyses. I’ve seen it time and again: mastering a single formula can unleash somebody’s Excel potential – so why not download the video exercise file and give COUNTIF a go?
It’s the formula that counts.