I love spreadsheets.
Microsoft Excel is the most important tool for a Category Manager.
Other 'user-friendly' data interfaces have come and gone over the years, but the one constant has been Excel.
I've been lucky enough to come across many spreadsheet gurus throughout my career, every one with their own methods. I've picked up so many tricks and I'm still learning new capabilities to this day.
There are some functions I couldn't function without.
Here are the Top 10 formulae I use every day.
1. SUMIF
Let's start with my absolute fave.
SUMIF allows you to create a total of values from one column/row based on a specific criteria from another column/row.
When you have a list of sales by product with various attributes in other columns, you can use this to calculate a sales total of one of these attributes.
Pro Tip: SUMIFS allows you to do the same thing, but with any number of criteria you want.
2. COUNTIF
COUNTIF works in a similar fashion. Rather than the sum total of a column, this formula will return the number of cells that meet your criteria.
This is useful when you have a long list of products and you want to breakdown the line count by certain sub-categories.
Pro Tip: COUNTIFS allows you to do the same thing, but with any number of criteria you want.
3. VLOOKUP
When you've spent hours upon hours manually coding your product attributes, you can relax safe in the knowledge that VLOOKUP is there to make the most of your hard work.
VLOOKUP will search a table for a specific value and return another value based on the column number you have inputted.
Pro Tip: While VLOOKUP is one of my most used functions, I love when I get the opportunity to use HLOOKUP which does the same thing but looking up by row (H = Horizontal) rather than column (V = Vertical).
4. MATCH
This nifty function comes in useful when you have a more complex lookup.
MATCH will tell you what row or column number a specific value is in.
Pro Tip: Use MATCH to input the column number in a VLOOKUP formula. This is super helpful if you're creating a new table and your columns aren't in the same number as your lookup array.
Bonus Pro Tip: The problem with VLOOKUP is it can only look to the right of the column you start with, so using INDEX and MATCH together you can look up values to the left.
5. IFERROR
The more you automate your reports, the more you'll want to use this one.
IFERROR allows you to create an alternative output in the likelihood another formula will return an error.
Pro Tip: Use IFERROR in your reports for % change calculations, as NPD and delists always result in errors.
6. LEFT and RIGHT
Sometimes the simple ones are the best.
LEFT and RIGHT allow you to extract a number of characters from another cell.
This comes in useful when you have a cell containing a product name and a barcode.
Pro Tip: You can combine this with the FIND function to extract the first or last word, which is helpful for extracting a brand name or pack size.
7. SUBSTITUTE
SUBSTITUTE allows you to switch out part of the value for something else.
One way I use this regularly is if I want to change how the time period is presented.
Top Tip: This function becomes even more powerful when combined with LEFT or RIGHT, as you don't need to know the set value you want to substitute.
8. RANK
If you're unable to sort the values themselves, RANK helps you identify values in order from top to bottom (or bottom to top).
Top Tip: Use RANK in combination with VLOOKUP to create a top products list.
9. CONCATENATE (&)
This helpful little ampersand allows you to combine values of other cells.
Top Tip: Combine values with Text to create headings or sources in your reports.
10.UPPER, LOWER and PROPER
One of my more recent discoveries, these formulae allow you to change the formatting of your text to upper case or lower case.
Pro Tip: I tend to use PROPER for my SKU lists to make them look much neater.
There's so much you can do with Excel functions and they become even more powerful when you combine them.
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 and you're bound to find something new.
Looking to up-skill yourself or your category team?
Check out my online training courses or get in touch to find out how I can help!
Now, I have to say, I love Excel too, genuinely.
I will say though, it was a bold statement to start the conversation by saying so.
Lots of really good information here for anyone (especially catagory managers as you say) - something to share with the team for sure. Everyone loves a tips post, but this has some Excel info that you don't usually see.
Thank you for the tips.
James - www.akcela.co.uk