+ Follow on LinkedIn
Passionate about online marketing & founder of this blog. In daily life Marketing Manager at a Belgian ICT company.
The power of Microsoft Excel is sometimes underestimated. Especially as a marketer, you come into contact with it almost daily. Below I share 12 handy Excel functions that will save you frustrations and hours of manual work.
A common problem: you receive an Excel file, but both first name and last name are in one cell. No problem thanks to the following functions:
You can also use these functions to extract domain name from the e-mail address, for example, or to split zip code and municipality, for example, when they are in one cell.
Or how about this one: a colleague sends you an Excel file where some of the text is in capitals (all caps). We can easily solve this too:
Possibly also recognizable: certain text contains an annoying space at the front. How can this be solved without retyping the entire text?
The following function is not actually a specific function in Excel. Nevertheless, it is a tip that can save you a lot of time:
As a marketer, you undoubtedly work with CSV files from time to time. You can convert these to a table via 'Data' > 'Retrieve and transform data' > 'From Text/CSV'. Or you can use the function 'Text to columns'. Also useful to separate other texts with a clear separator.
Did you know there is a function in Excel to detect and remove duplicate values? Tip: Of course, it's best to search here for unique values such as an email address or ID number, for example.
No doubt you have ever had to do an exercise in school involving horizontal and vertical search? Do you also always confuse the two? In Horizontal Search, Excel is going to try to match the specified value horizontally, and then - hence the confusion often - retrieve a certain value vertically.
In the example below, I extract the years of service and date of retirement from a table for the specified staff members:
In vertical search, Excel is going to match a specified value vertically and then retrieve value horizontally in a given cell. Below I give an example of searching for a congregation of certain people in a table:
Perhaps the function I use most often in Excel is the well-known if function. Useful for incorporating controls, for example:
The "Number If" function is definitely one to take good note of as a marketer. It sometimes comes in handy, for example, when you need to compare two Excel files. In order to detect duplicate values. Or to summarize a large database.
In the example below, I used the 'Number of If' function to determine the number of Junior vs Senior profiles in a company:
The following function is a classic: 'Search and replace' or CTRL+H. Ideal for quickly replacing certain values in a database with something else:
Perhaps you - like most - also move columns via cut and paste? Did you know that by holding the shift key you can move columns quickly?
Of course, there are still convenient features within Microsoft Excel which I have not yet mentioned above. Think "Top Row Blocking," "Include Macros," or "Pivot Table. These I will keep for a future article. But it should be clear: Microsoft Excel is - still - an extremely powerful tool. Also for marketers.