fbpx

Magic spreadsheet formulas all marketers should know

With digital marketing becoming more and more data-driven, there are some basic spreadsheet formulas for marketers that you should definitely make sure you know.

Here are seven simple, but important, ones for you to study.

1. Sum, Average, and Median

I hope you know how to summarise numbers in Excel.

However, if you’ve been living under a rock for the last 15 years, you can summarise multiple cells – containing numbers – using =SUM().

You put the cells you want to summarise between the parenthesis.

If you instead want the average of numbers from cells you can use =AVERAGE().

Also, finally. You can get the median by using =MEDIAN().

If you want to refresh the difference between average, median and mode – here’s a post for you.

2. Split

Split is technically not a formula, but who cares. It’s still one of the Excel functions I use the most.

When you have much information all in one cell in Excel, you might want to split it up into multiple ones. Splitting information into smaller pieces often makes it easier to work.

When splitting cells, you need to locate something that divides your information in smaller bits. This “splitter” is called a delimiter, and it can be a space, dash, comma or something else.

The easiest way is to use the tool “Text to Columns”, which you find in the menu, and tell Excel what your delimiter should be. It will then automatically split your cell into multiple ones.

If you want to write a formula which does this, I’d recommend to check out some more advanced use cases of Left, Right and Mid listed here as number 6.

3. Concatenate

Concatenate is the opposite of split.

The =CONCATENATE function is designed to join different pieces of information together from several cells into one. You combine the content of the cells, but the original cells are untouched.

The word concatenate means “to combine” or “to join together”, and the =CONCATENATE function lets you combine text from different cells into one cell.

For example, you might have First name and Last name into separate columns, and you want to have a combined columned with Full Name.

Alternatively, you have your clients addresses spread out over many different cells in your spreadsheet – street, street number, zip code, city etc. are all in individual cells. Also, maybe you want to combine all that information into one cell to use when you are mailing them stuff.

Here’s a useful instruction if you’re looking to concatenate your cells in practice.

4. Trim

One of the most annoying things in Excel is when you have extra spaces in your cells. Maybe you’ve split columns containing spaces that have populated into new cells, or it’s just dirty data in your file, to begin with.

Annoying!

=TRIM() is your best friend when this happens.

When you run a cell through =TRIM, it removes all spaces in the beginning and end of your cell, and if it’s more than one space between words, you will only have one space left between them. Very handy.

For example:

=TRIM(”    I    Love  Macaroni       and    Cheese     “)

Will return “I Love Macaroni and Cheese”

Usually, you put a reference to another cell, like D12 or C2 between the parenthesis.

It might sound like a simple feature, but the joy you feel when you don’t have to remove all those spaces manually from hundreds of cells… Just imagine.

5. CountIf

CountIf is a function that counts all the cells that meet specific criteria. The function supports logical operators – larger than, less than, between, or equal to (>,<,<>,=) – and wildcards (*,?) for counting a cell that partly matches some criteria.

The return value is a number representing the number of cells counted.

For example, if you have “city” as a column in your Excel file, and you want to know the number of people in your spreadsheet who live in Austin. Then you ask Excel to count every cell in that column equal to  “Austin”.

The number you get back is the number of cells which have “Austin” as their content, which is also the number of people in your spreadsheet who live in Austin.

Here is the magic formula:

=COUNTIF (range, criteria)

range – The range of cells to count (for example a column)
criteria – The criteria that decide which cells should be counted (for instance “Austin”)

Here’s an article which goes into detail on how to use CountIf.

6. Left, Right and Mid

The functions “LEFT”, “MID”, and “RIGHT” allow you to get a substring from an existing string of data.

If you know you want five characters from a much longer string. With LEFT you will get the characters from the left side of a string, with RIGHT, you’ll get them from the right side. With MID you’ll get them from somewhere in the middle, but then you need to tell the formula where in the middle it is.

Maybe you have a package with a long sequence of digits as its ID. However, the person picking up the package, only need the last four. Then you can use =RIGHT(cell with the string, number of required characters). The same use case can be relevant for zip-codes, dates, and so much more.

Here’s a great article about this, with some more advanced use cases.

7. VLookup

Typically you would use VLookup in something like a report template. Every time someone entered a specific code; it would retrieve all the needed information.

The VLookup function lets you search for a specific value in one column and returns data from a different column on that same row.

For example, if you were using the data in the table below, you might use the VLOOKUP function to identify the price of a specific fruit in the table.

Fruit
Price
In Stock
Apple
$0.5
33
Banana
$0.6
23
Pineapple
$0.2
12
Pear
$0.8
10

If you ask the formula for “Apple”, and you get $0.5

You could get the number of apples in stock instead. Actually, I think you can find multiple other examples where this functionality might come in handy.

Here’s a useful instruction if you are looking to get started with VLOOKUP.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Reply

Your email address will not be published. Required fields are marked *

Hi!

Every Sunday,
more than 100 people get my executive newsletter with the most essential Internet-related news from the previous week.

Do you want it too?

All you have to do is fill in your name and email address below. Also, the newsletter is free.