How to use Excel – Top Excel Functions and Formulae for Sales and Marketing Professionals
I’ve been in Sales and Marketing roles for 7+ years now. During my first job I quickly realised that in order to make effective decisions, report accurately and generally perform well I needed to get to grips quickly with Microsoft Excel. Business decisions, especially within Sales and Marketing, need to be based on cold, hard data. This is even more true when you have a B2C (business to consumer) company and you are dealing with volumes of individual customers. Intuition can still play a part but the real thing that is going to make a difference is data-based decision making.
Whether you are a digital marketer, responsible for partnerships, or ad spend you need to be basing your decisions on the data in front of you. To do that you need Excel. That being said I firmly believe you don’t need to know absolutely everything to be able to draw meaningful insight from data. With a broad understanding of two key areas on Excel you can spot patterns, improve processes and become the sales and marketing superstar you were born to be.
Those two areas are:
- Data Cleaning and Preparation
- Data Analysis
In this article I will take you through the functions and formulae that I use most when performing data-cleaning and analysis. It’s by no mean an exhaustive list but will hopefully help you direct your own learning and development.
Does data get dirty? Why does it need cleaning? “Grab the mop Alan, this data is filthy!” Of course you don’t actually need to clean anything. Data-cleaning in Excel is about getting your data into a format that you can analyse effectively from. It’s the foundations of your work. It’s incredibly important because the data you are likely to be using will inevitably be .csv downloads from third party systems that you use, be it a CRM (customer relationship management) or payments system, some monitoring tool you use or your own site. It might be data from a client that you are expected to pull insight from.
This data may come in all shapes and sizes and you need to get it into a standardised (nice and neat!) format to start your analysis. So let’s look at some best practice, functions and formulae that help with this.
Best Practice Rule 1 – Organise Your Data Properly
Whatever you are doing in Excel, the first thing you need to think about is how you want your data organised and that is really based on what your ultimate objective is. If you are doing a quick bit of analysis to check a figure you need a very different set up to if you are creating an ongoing report that is going to be used by multiple people. Think carefully about what the purpose of the spreadsheet is and set it up in the most appropriate way.
Whether for yourself or others, my top two best practice tips are:
1. Clearly identify inputs, calculations and outputs. Make it obvious which cells are performing calculations, which are data input cells and which are outputs/results. Excel even has some handy preset formats to help with this available at Home > Cell Styles. Use them to make super clear which cells are inputs, which are performing calculations and which are giving results (output).
2. Don’t embed inputs in formulae. You can see below that C1 and C2 will achieve the same results, the difference is that it is very apparent in C2 what all the inputs are – nothing is hidden!
Some of these, and more, are explained in more detail in the ICAEW’s “20 Principles for Best Spreadsheet Practice” can be viewed here. I highly recommend you take 10 minutes to go through it, especially before embarking on a more significant spreadsheet build.
Text to Columns & Remove Duplicates
I use both of these all the time when cleaning data, they are two of my favourite, especially Text to Columns.
Remove Duplicates does what it says on the tin, select a data range, hit Remove Duplicates (Data > Data Tools section) and any duplicate values within that range will be removed. Great for loads of different situations.
Text to Columns is a bit more complicated but hugely powerful. It allows you to separate strings of data into multiple cells. The best way to explain this is to show you with an example:
Say, you have a list of names that you want separating by first name and last name. Start by highlighting the column/row(s) that you want to change, go to the Data tab and in Data Tools select Text to Columns. You now have the option to adjust the data via a delimited or fixed width. Delimited basically means by anything common within the data, this could be a comma, colon or space. Fixed width means by a fixed number of characters. The latter is great if we are separating something a standard length, maybe a shopping SKU, but for the purposes of this you choose the delimited option.
Step two is to select your delimiter, in this case we are going to choose a space. You can see in the Data Preview what you are going to get.
The final step is to select your data format. In most cases you will just leave this selected as General. I have found that the Date format is useful if you want to turn dates formatted in the US style (Month/Day/Year) to the UK style (Day/Month/Year) or vice-versa.
Hit finish, Excel does its work and your text is separated into different columns.
TRIM & IFERROR – Two Excellent Data Cleaning Formulae
There are so many formulae that help you clean and organize your data. Two that are super simple to use and that I have found very helpful are TRIM and IFERROR.
First, TRIM. The trim function simply strips text in a cell of any extra (and unnecessary) spaces. It leaves spaces between words but removes them from the beginning and end of any strings of text. So often a function or formula can return completely wrong results because there is a stray space after a word in a cell. The TRIM function sorts that out and is simple to use:
=TRIM(CELL THAT I WANT TO TRIM)
If you are working on a dataset with errors of some sort e.g. #N/A and that is messing up your analysis, then IFERROR can really help. You can wrap most formulae with an IFERROR to get something different to an error message:
Say you are performing a VLOOKUP, your formula might look something like this:
If that is returning the dreaded #N/A for a lot of the results but you just want to discount them from your analysis then you can turn your errors into blank cells with IFERROR:
All we are doing is wrapping an extra condition into the formula. The VLOOKUP is still taking place but if an error comes up the IFERROR kicks in and returns a blank cell. The blank cell is achieved by putting two quotation marks together “”. Excel will return any text value that is within quotation marks, you could put a word or phrase in there such as “NOT WORKING” and it would return that for the error values.
Now for the fun bit – analysing your data. This is where we get the insight we need out of our nicely prepared (and clean!) dataset. There are a few tools here that I use regularly:
- PivotTables – simply the best, quick data analysis function in the Excel toolkit
- COUNTIF, SUMIF, VLOOKUP, INDEX MATCH – essential formulae for data analysis
- Conditional Formatting
PivotTables are brilliant. There are Excel purists that claim their application is limited, but I am yet to find a quicker and more flexible tool for performing data analysis. A PivotTable is a flexible, drag and drop way of counting, averaging, summing (and so much more) sets of data. Basically put, if you have lines and lines of product or sales data a PivotTable can quickly summarize it by any common factor, be that a date, product line or any other element within your data.
I will write a full blog explaining how to do a PivotTable soon but in the meantime here are my top tips when creating them:
1. Make sure your data has headers. Each column of data you want to include in your PivotTable needs a title/header otherwise Excel won’t let you create a PivotTable.
2. Numbers go in the bottom right “Values” box on the PivotTables field grid. Excel will automatically SUM those numbers if it detects all numbers or COUNT them if it detects non numbers (e.g. any #N/A’s). Use the Value Field Settings to adjust this.
3. Use the Filter field to quickly remove elements from your PivotTable or better use slicers to do this dynamically.
4. For advanced users, the PowerPivot function connects your PivotTables to an external data source (i.e. SQL or Access). You can then refresh the data in your PivotTables with one click. This is particularly cool if you have regular sales reports to generate.
COUNTIF, SUMIF, VLOOKUP
These (with maybe the exception of =SUM) are the formulae I use the most for Sales and Marketing analysis. Below is what each one does and how to do it.
COUNTIF. COUNTIF counts based on a given criterion. If, for example we want to see how many times a particular product appeared in a big list of multiple products we would use a COUNTIF. The syntax looks like this:
The range is the range of data we are looking at, in this case that would be the big list, say that’s column A, we would probably just select it all =COUNTIF(A:A,…. The criteria is the item we want to look up. Say that is in Cell B2, the final formula would read =COUNTIF(A:A,B2). Excel would then count how many times the value in B2 appears in column A. It’s worth noting that this works for text or numeric values.
SUMIF. This is a variation on COUNTIF, it is a very similar function but it sums instead of counts. The syntax looks like this:
Starting in a very similar way to the COUNTIF, we first select the range. Using the example in the image below that’s the column B with the names in. We then select the criteria we are looking up, in this case cell D3 – Dave. We then add the final part of the formula which is the sum range. This is the range that we want to add together based on the criteria we are searching for. So in this case we can see the sum of figures in the Total column allocated to Dave.
VLOOKUP. Loved and hated in equal measure by many Excel users. Mostly because it isn’t the most reliable of formulae. INDEX MATCH I always find more complicated but is definitely more reliable than the VLOOKUP. That said, I still love VLOOKUPs, so I am going to show you that. VLOOKUP stands for vertical lookup. It’s a way to take a particular value, look that up in a separate table and return an adjacent value from that table. The syntax looks like this:
=VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM,[RANGE_LOOKUP])
That might look complicated but when you start using it you will find it easy (promise). Let’s bring this formula to life with an example. Imagine you have a column of customer emails with the date they became a customer in the adjacent column: CUSTOMER EMAIL, DATE BECAME CUSTOMER. You then have a list of your most recent sales, the columns look like this: CUSTOMER EMAIL, TOTAL SALES VALUE, DATE PURCHASED. You want to see what date those most recent customers first became a customer. The unique identifier here is the customers’ emails so that’s what your lookup value is going to be. The table array is the table you want to look up, in this case that is the table with the list of customer emails and the date they became a customer (this doesn’t have to be a table, but I recommend it is).
This is important – the value you are looking up, the email in this case, has to be in the far left column of the data/table you are looking up.
The next part of the formula is the column index number, or how many columns across you want to count. The column you are looking up is 1, the next column is 2, which is the one we want in this example. The final part of the formula (range lookup) is whether you want an exact match or approximate match. If you put TRUE Excel will return the next largest value that is lower than your lookup value. You will only really use TRUE in edge circumstances, for most lookups you will use FALSE – an exact match. This will return the date each person first became a customer into your sales data sheet, throw that into a PivotTable and you can see how much people who joined in different months are spending – boom.
Want a way to make your data light-up based on certain criteria? Look no further than conditional formatting. This function can instantly make you look like a spreadsheeting pro and it’s so easy to use you will wonder how you haven’t been doing it for ages.
Conditional formatting allows you to turns cells different colours, add traffic lights to them, or data bars and lines based on the information or numerical value within that cell. You can set up loads of different rules and really make the data you want to see stand off the page. Some of the most popular rules are:
- Greater/Less Than – Highlights a cell based on it being a higher or lower numerical value
- Text that Contains – Highlights cells that contain a certain text
- Equal to – Highlights cells equal to the value you select
- Top/Bottom rules – Select various rules based on the numerical value. This might be the top 10%, or items above the average of the range.
- Duplicate Values – My favourite. Simply highlights duplicate values.
You can also use Data Bars and Colour Scales to highlight the largest and smallest numerical values within a range of data. Don’t they look good:
That’s all we are going to cover today. One area that we haven’t covered yet – which is probably the natural progression of this – is presentation. Hopefully this has been useful and helped point out a few areas where you should focus your Excel training if you are in Sales and Marketing (or even if you aren’t, to be honest).
The best way to master all these functions and formulae of course, is to get into Excel and start practising! If you think it is time that you started your Excel journey/brushed up on this essential skill then take a look at our Excel training course – normally it’s £49/$69, but you can sign up for 7 days access completely free here.
About the Author
Adam Lacey is a Director at Skillsology, an online learning platform specialising in personalised professional training courses. Online Microsoft Excel training is one of their biggest-selling titles worldwide.