Spreadsheets are one of the classic ways to organize and analyze key data like your patient information, key performance indicators, and practice metrics. However, you’ve likely become frustrated with Excel spreadsheets many, many times when things get tedious or overwhelming. If you’ve ever wondered why there isn’t a better way to do what you need it to, you’ll be delighted to hear that the chances are very high that Excel can do the very sophisticated data calculations, organization, and presentation that you need.

Unfortunately, most people only touch the surface of what Excel can do with the right shortcuts, formulas, and tools. Excel has incredible depth, and it takes dedication to truly unlock the bulk of its functions – but it’s worth it to spend time learning it or making sure one of your employees becomes an Excel expert. In the meantime, here are a few important functions to implement into your marketing spreadsheets to elevate your data collection and make smarter business decisions.

Note: This applies to the most recent version of Excel and thus these formulas might be different for older versions.

How to Use Excel Formulas

You’ve probably noticed the “Formulas” tab at the top of your Excel window and wondered what it does. In fact, it can seem like a lot of gibberish without some further knowledge. Called “functions,” Excel formulas are organized by type so it’s easy to look for a formula that might do what you need.

To insert one, highlight a cell and click on “Insert Function”. A browsing window will pop up and you can scroll through recently used formulas and potential other candidates. It will even show a small summary of the function and what it does.

Alternatively, you can type the equal sign (=) to begin a formula if you know a specific formula you want to enter.

Now, let’s take a deeper dive into some of the most common formulas and how they can be used.

15 Excel Formulas Worth Knowing About

Here’s a quick collection of essential formulas, shortcuts, and other tips and tricks to help make your marketing spreadsheets more accessible.

1.      SUM

=SUM is one of the most basic Excel formulas. It simply totals two or more values, saving you from having to grab your calculator to add up numbers. You can type it in the following format:

=SUM(value 1, value 2)

This can be any number you need or the value of a specific cell (and the sum will automatically update if you change the values in the corresponding cells).

If you’re trying to add any two numbers, for example, you can type:

=SUM(40,60)

If you’re trying to add the values in two specific cells, it looks like this:

=SUM(B2,B6)

You can also total a list of numbers in Excel. You simply insert a colon in between the first and last cells. So, it might look like:

=SUM(B3:B10)

Excel will automatically insert the total below the list and update if you make changes to any of the values in the indicated cells.

2.      IF

The =IF function allows you to add value to a cell if something else in the spreadsheet meets a true/false criterion. It takes the following form:

 =IF(logical_test, value_if_true, value_if_false)

This means that if the value meets the criterion, it will plug in the “value_if_true” data. If not, it will plug in the “value_if_false” data. For example, if the C column denotes Yes or No values (let’s say rewards membership status), then you could enter the following function:

=IF(C2=”Yes”,1,2)

Using this formula, you could streamline your email marketing process by designating certain activity codes for members (1) and non-members (2).

3.      Percentage

The percentage function is a very quick shortcut to find the percentage of two cells. It takes this format:

=A1/B1

Alternatively, you can highlight the cell and select “Percentage” from the dropdown menu. Keep in mind that Excel might return your percentages as decimals – just select the Percentage setting from the dropdown menu on the right side of the Home banner.

4.      Subtraction

Just as you can easily add two values, you can also subtract them. It’s actually just a small tweak to the =SUM function. It takes this format:

=SUM(A1,-B1)

There’s actually no designated subtraction formula in Excel but you can simply plug in a negative sign before a value to get the same result.

5.      Multiplication

Multiplying two cells in Excel is also very quick and easy. You might think it’s a simple “x” but multiplication is actually denoted using the asterisk (*). It looks like this:

=A1*B1

You can actually add multiple cells to the formula as needed, or =A1*B1*C1*D1… etc.

6.      Division

Dividing numbers in Excel can be performed with the forward slash (/), like so:

=A1/B1

Keep in mind that order matters – in this case, you’re dividing A1 by B1. If it needs to be the other way around, then adjust the cells accordingly.

7.      DATE

You can quickly add dates into your Excel sheet with the following formula:

=DATE(year,month,day)

The year, month, and day values can actually be taken from specific cells if needed, or =DATE(A1,B1,C1). You can also quickly add today’s date to your spreadsheet with this text:

=DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()))

8.      Array

Array formulas can help extend your other functions by including a range of values rather than specific ones. It uses the brace symbols to denote a range of numbers – for example, if you’re multiplying a range of numbers, it can look like this:

{=(Start Value 1:End Value 1)*(Start Value 2:End Value 2)}

In practice, it might look like:

{=SUM(B2:B5*C2:C5)}

This totals all the values in the cells B2-B5 and C2-C5 and multiplies the totals together. Excel uses a specific key command to calculate the result, which is Ctrl+Shift+Enter. So, if you’re adding up multiple columns and multiplying them by each other, an array function can save lots of time.

9.      COUNT

This is a much quicker way to find out the number of values in a specific range of cells. For example, if you’ve selected a group of patient names, the =COUNT function will tell you how many you’ve selected. The formula takes the following form:

=COUNT(Start Cell:End Cell)

Keep in mind that it won’t total the values – it will simply tell you how many cells there are. But, if you’re looking to keep track of how many patients have purchased the product you’re featuring, it can be extremely helpful.

10.  AVERAGE

This formula can calculate the average of a range of values. This means it adds all values and divides them by the number of values there are. You can enter it one of three ways based on your needs:

=AVERAGE(number1, number2, etc.) OR =AVERAGE(A1, B2, etc.) OR =AVERAGE(Start Value:End Value)

As you might have guessed, the first will take the average of specific numbers while the second will take the average of specific cell values. The third will average a range of cells.

11.  SUMIF

The =SUMIF function totals the values in a designated range if they all meet a specified criterion. It’s denoted:

=SUMIF(range, criteria, [sum range])

So, an example would be =SUMIF(B5:B10,”>50,000″) where the formula would total all cells between B5 and B10 if they’re greater than 50,000.

12. TRIM

=TRIM is a quick and easy way to delete any spaces around a specified text. It’s written like:

=TRIM(text)

For example, if a cell (B3) contains the words “Breast Augmentation “ with an extra space at the end, then =TRIM(B3) would remove the excess space without having to comb through your sheets by hand. It’s a much quicker way to ensure that your patient contacts can easily be pulled for email lists, for example.

13.  LEFT, MID, and RIGHT

If you have a long string of text in a cell that you want to break up into different segments, you don’t need to retype it all manually. The =LEFT, =MID, and =RIGHT functions are a must faster way to extract characters from a string of text or numbers and place it in a different cell. The =LEFT function extracts characters starting from the beginning of the string, while =MID and =RIGHT extract them from the middle and end respectively. The look like this:

=LEFT(text, number_of_characters)

=MID(text, start_position, number_of_characters)

=RIGHT(text, number_of_characters)

In these cases, the text is the full string of text or numbers that you want to extract from while the number of characters is (you guessed it) the number of characters you want to extract. In the case of =MID, the start position begins with 1 at the far left of the string so you’ll need to count from left to right appropriately.

14.  VLOOKUP

This one can be a little complicated, but =VLOOKUP can combine two separate spreadsheets. For example, it comes in handy when you have one spreadsheet with patient names and phone numbers and another one with patient names and email addresses. This way, you don’t have to manually copy them all over and match up the contacts by hand. It follows this formula:

=VLOOKUP(lookup value, table array, column number, [range lookup])

Firstly, you’ll need to make sure you have at least one identical column in each spreadsheet. This is the “lookup value.” The “table array” is the range of columns in sheet 2 that you plan to pull data from (including your lookup value). The “column number” is which column in the table array that you want to copy over. And finally, the “range lookup” should be FALSE since you want exact matches only. So, in all, your final product might look like =VLOOKUP(E3,Sheet3!C:D,2,FALSE).

15.  RANDOMIZE

The =RAND function is similar to shuffling a deck of cards. It takes a range of numbers and randomly places them in a designated column. So, it can be a quick way to randomize a list of contacts if you’re trying out a new email marketing strategy. It looks like this:

=RANDBETWEEN(bottom,top)

In this case, “bottom” is the lowest number in the range and “top” is the highest. It will automatically populate your designated cells with random numbers in that range without repeats.

Let’s Get In Touch

Effectively and efficiently analyzing your patient information can be tedious without the right knowledge and support. At Digital Limelight Media, we offer industry-leading tools for you to manage your EMRs all in one place. To learn more, give us a call or fill out our online contact form.