12 Excel functions everyone should know (especially marketers)

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.

Content

Content Article

1. Split text with functions Left, Right and Find.Spec

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:

  • Feature Links
  • Function Right
  • Function Length
  • Function Find.Spec

Example:

  • Define first name: =LINKS(B3;VIND.SPEC(" ";B3;1))
  • Determine last name: =RIGHT(B3;LENGTH(B3)-LENGTH(C3))

Tip Excel - Split names in a cell using Left and Right 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.

2. Convert text to capital letters

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:

  • Function Initial letters

Example:

  • =BEGINNING LETTERS(C3)

Convert text to all caps - Function Excel

3. Removing spaces in Excel

Possibly also recognizable: certain text contains an annoying space at the front. How can this be solved without retyping the entire text?

  • Function Blanks.Delete

Example:

  • =BLANKS.DELETE(C3)

Removing spaces in Excel

4. Merge text from various cells

The following function is not actually a specific function in Excel. Nevertheless, it is a tip that can save you a lot of time:

  • Use of & sign i.c.w. " "

Example:

  • =C3&""&D3

Tip Excel - Merge text from several cells into one cell

5. Text to columns (useful for CSV files).

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.

  • Function: Text to columns

Tip Excel - Text to columns

6. Removing duplicate values in Excel

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.

  • Function: Delete duplicate values

Tip Excel - Remove duplicate values

7. Horizontal search

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:

  • Function: Horizontal Search
  • Function: Fix a cell or row using $ character

Example:

  • Search for 'Years of Service': =HORIZ.SEARCH($B12;$B$2:$G$6;3;0)
  • Search for 'Date retired': =HORIZ.SEARCH($B12;$B$2:$G$6;3;0)

Tip Excel - Horizontal Search

8. Vertical search

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:

  • Function:Vertical Search
  • Function: Fix a cell or row using $ character

Example:

  • =VERT.ZOEKEN($C14;$B$3:$J$10;9;0)

Tip Excel - Vertical Search Function

9. Function As

Perhaps the function I use most often in Excel is the well-known if function. Useful for incorporating controls, for example:

  • Function: As

Example:

  • =ALS($B4<4; "Junior"; "Senior")

Tip Excel - As Function

10. Function Number If

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:

  • Function:Number as

Example:

  • =AANTAL.ALS($H$12:$H$14;$D19)

Tip Excel - Function Number As

11. Function Search and Replace

The following function is a classic: 'Search and replace' or CTRL+H. Ideal for quickly replacing certain values in a database with something else:

  • Function: Search and replace

Tip Excel - Search and replace

12. Moving columns quickly in Excel

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?

  • Function: Move columns by shift key

Tip Excel - Move columns

Conclusion

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.

RECENT POSTS

Hereby, I like to share 15 handy Excel functions that will save you frustrations and hours of manual work.

Tom Hufkens

Tom Hufkens

Passionate about marketing & founder of this blog .

+ Follow