In case you didn’t already know, I really like spreadsheets.
I’ve spent a lot of my working life using Excel, so it amazes me that there are still new things to learn and new functionalities to make my analysis (and my life) easier.
Whether you work in Category Management or not, you probably already know the magic of the spreadsheet. Other software has come and gone over the years but Excel remains a constant tool.
A while ago I shared 10 Functions Every Category Manager Should Know. Well, there’s so much I have learned since I shared that list. That’s why I’m back with a part two that's bound to give you at least one new trick to try out!
So, here are 10 MORE Excel Functions Every Category Manager Should Know, the sequel that's all about taking your Excel skills to the next level. Just like before, these functions aren't reserved for tech wizards – they're for anyone who uses Excel as part of their job.
1. UNIQUE
One of my new faves, UNIQUE is one of the many “dynamic array” functions introduced in 2018.
When dealing with lists of data, you often want to extract a unique list of values without duplicates. The UNIQUE function does just that – it gives you a tidy list of distinct values from a range.
This is incredibly handy for quickly identifying different categories, products, or any type of unique entries in your data.
If you’re familiar with using the “remove duplicates” button - this is a more dynamic way of doing the same thing.
2. FILTER
Imagine having a vast sea of data and wanting to see only a specific portion of it.
FILTER is another 'dynamic array' formula, that does just that. You give it criteria, and it extracts only the rows that match those criteria, creating a new table with just the data you're interested in.
The beauty of these 'dynamic array' formulae are that you can use it for a single column, row, or a whole table!
3. AVERAGEIF (and AVERAGEIFS)
In my previous first article, we talked about SUMIF and COUNTIF.
Did you know you can also do an AVEREAGEIF?
This is an old classic and can be really useful in promotional analysis, especially when the promo periods are different lengths and you want to calculate uplifts.
4. SORT
Another 'dynamic array' formula is SORT.
You can sort data in a much easier, more dynamic way with this function.
Just add the array, the column or row number you want to sort by, and whether you want it ascending (1) or descending (-1).
5. XLOOKUP
Once you start using XLOOKUPs, you’ll forget all about VLOOKUPs and HLOOKUPs.
Unlike its out-dated counterparts, XLOOKUP:
Work with vertical and horizontal data.
Can lookup data to the right or left of lookup values.
Always defaults to an exact match.
Can perform a reverse search (last to first).
Can return entire rows or columns, not just one value.
6. MIN and MAX
This pair are super useful and easy to use.
MIN finds the smallest value in a range (minimum), while MAX, you guessed it, locates the largest (maximum).
7. TEXT
This function is a super simple one with tonnes of uses. It lets you convert numbers to text in a specified format.
I use this one all the time for formatting chart labels or extracting date information like the day of the week or the month.
8. RRI (CAGR)
One thing you have to calculate a lot when building category growth strategies or growth projections is Compound Annual Growth Rate (CAGR).
I used to have to look up "how to calculate CAGR" all the time, but once I knew about the RRI function, life became much easier!
You just need to input how many years, the start value and the end value...
9. QUARTILE
Sometimes you want to add a quartile view to things like rate of sale rankings. Did you know there’s a QUARTILE function that works it out for you?
I did my own calculations for an embarrassing amount of time before I knew!
You just need to add the array and the number of the quartile you want (e.g 1 = 1st quartile), and the formula will give you the maximum value.
10. SUMPRODUCT
I don't use this one too often, but it's a huge time saver when I do need it!
SUMPRODUCT helps you multiply corresponding numbers from different ranges and then summing up those products. This becomes really useful in planogram analysis for space to sales.
With a small amount of cells you can do =A1*B1+A2*B2+A3*B3, but as the number of cells grow, SUMPRODUCT makes the calculation much easier!
So there you have it, 10 more functions for you to try out.
I'd love to know which ones are new to you and which ones you're excited to try out!
Whatever challenge you face with your spreadsheets, there's always a function or combination of functions to help you out.
If you don't find a solution from this list, just Google your problem (or ask Chat GPT for help), and you're bound to find the perfect solution!
Trying to upskill your team in all things Cat Man?
Check out our training courses or get in touch for a chat about how we can help.
Comments