Learning how to effectively use Microsoft Excel can be a daunting proposition. It is arguably the most valuable tool businesses have at their disposal, but the hundreds upon hundreds of functions, formatting options and other uses can seem overwhelming. From formatting monthly expense reports to organizing an inter-departmental to-do list, there are a number of ways Excel can be used and several formatting options to make it all flow that much smoother. (You can also use Excel spreadsheets to help organize your office Fantasy Football picks, too. Just sayin’!)

Thankfully, unless your goal is to create a small business that sells Excel expertise, you won’t need to know all of them. Here are a few of the more useful functions that Excel has to offer and some practical ways you can use these to make your life at the office a bit easier.

1. SUMIFS:

SUM is one of the most basic yet most useful functions in Excel. Being able to quickly add up a large amount of data with one simple formula has saved countless hours of using the Google search bar as a calculator (PRO TIP: use “alt + =” to autosum an entire row or column of data for ultimate efficiency). However, the versatility of the sum function is limited by its simplicity, and this is where SUMIF(s) come into play.

Let’s say you have a large table of sales data that includes total sales, seller, city, product sold and month the product was sold in:

The SUM formula could easily add up the total sales, but your boss wants the total sales of hand sanitizer products. Simply use SUMIF to find the answer:

First, select the product range. Then add the criteria of “hand sanitizer”, and finally, choose the range of values to be summed. Your formula would look something like this:

=SUMIF(D:D,”hand sanitizer”,E:E)

This tells Excel to sum all the data in column E that matches the criteria in column D. With 1,000 rows of data, the total comes to over 6,000 units sold.

Now, let’s say your boss wants the total sales of hand sanitizer products in Chicago, IL, that were sold by your co-worker Amy in the month of March (because your boss is a jerk). Sound impossible? Not with SUMIFS!

SUMIFS allows you to do a SUMIF formula with multiple criteria:

This time, start with values to be summed and then add each criterion needed. The end result will look like this:

=SUMIFS(E:E,B:B,”Amy”,C:C,”Chicago, IL”,D:D,”Hand Sanitizer”,A:A,”3″)

That value totals 97. To put it simply, choose each column to select data from and then tell Excel which value to sum from that column. Values that match every criterion will be summed.

It would be quicker to use a pivot table to get a complete view of all the data separated out by these criteria, but SUMIFS are a useful way to pick and choose which data to filter out of a large pile without dealing with all of the data.

2. Find/Mid:

The combination find/mid function has probably saved me the most time out of any function in Excel. It is one of a few Excel functions that can extract text blocks from a cell, and it’s arguably the most useful function since it can be manipulated to have the same formula across thousands of cells. There are a few things that need to be explained first— the individual Mid and Find functions.

MID:

Like the Left and Right functions, the Mid function extracts text from a cell. First, reference the cell to extract text from. Then, include the character number within the cell to start from and the number of characters to return. For example:

By using the Mid function, excel will reference cell D1 (Excel functions are super cool), find the 21^st character and then return the first 10 characters from that point. Pretty simple.

FIND:

Also simple. The Find function will locate a string of text within a cell and return the start number for the first character. Using the same example:

“are” begins at the 17^th character in cell D1.

Note: If the string of text is not unique in a given cell, excel will return the first instance of the text. =FIND(“c”,D1) will return 3 and ignore the rest of the cell.

Note 2: The Find function is case sensitive, so using “Are” instead of “are” will return an error.

Now let’s put it all together using Quill.com Brand® URLs as an example

https://www.quill.com/hand-sanitizer-bulk/cbd/2616.html

Let’s say you needed to extract the “Page ID” number from this URL—in this case “2616”. The number begins at the 45^th character, so you could use =Mid(cell,45,4) to return the ID. Now let’s say we have 10 URLs:

Notice that each Page ID starts at a different character in each URL. It would be a pain but not impossible to find the start number for each ID and plug that into the Mid function. Now imagine that you needed to this for 20,000 URLs…the solution? Combine the Mid function with the Find function to automate all of the counting. By replacing “start_num” in the Mid with the Find function, Excel will automatically locate the start string of the text in every cell:

At first glance, it may seem overly complicated.

“A function within a function?” you might say.

“Inception confused the hell out of me!” you might say.

“What do I look like? An Astrophysicist??” you might say.

What’s actually happening is pretty simple since, hypothetically, you’ve already completely mastered the Mid and Find functions by themselves. The Find function (+5, since the text we want starts 5 characters after the returned value) will give the Mid function the start value. Mentally replace the Find function with the number it is producing to simplify the whole thing. Best part? Just double click on the cell containing the formula to get all the Page IDs you could ever want:

If the Page IDs aren’t all 6 characters, the last value in the Mid function can be replaced with more Find functions.

In this case, you want to determine the number of characters between “/cb*/” and “.html” and dynamically place it in the last part of the Mid function. Sound hard? Not really! Since the Find function returns the start location of text as a number, we can subtract the start of one from the other:

=MID(A1,FIND(“/cb”,A1)+5,(FIND(“.html”,A1) – (FIND(“/cb”,A1)+5)))

Now, that looks like a mess, but switch the Find functions for the numbers they produce and it looks much simpler:

=MID(A1,(72)+5,(80) – ((72) + 5))

Or

=MID(A1,77,3)

Copy the formula down for each cell and it returns this:

3. LEN function + Conditional Highlighting

If you’re like me, then you’ve been subjected to character limits when creating content. That’s no fun—especially when dealing with hundreds of different things to write about. A great way to keep track of character limits is to use the LEN function in conjunction with conditional highlighting. LEN is very basic—simply returning the number of characters in a given cell. Take this example:

If I asked you to count each character and then tell me which cells are over 175 characters, you’d rightfully slap me. Now imagine there are 1,500 blocks of text. How would you be able to quickly determine which ones are over and which ones are under the limit?

Start by formatting column B. Choose Conditional Formatting under the “Home” tab. Then select “Highlight Cells Rules” and “Greater Than…”

Now enter the number that will cause the cells to highlight

Enter the LEN function and reference the text:

Copy the function all the way down and it’ll be clear what content needs to be changed:

“Greater Than” can be switched with other formatting options, depending on the character range you’re working with for content. Conditional formatting by itself is incredibly useful, and applying it with other functions adds a whole new level to Excel’s applications, especially when dealing with content.

While this post barely scratches the surface into all that Excel has to offer to you and your business, it does provide a look into some of the more advanced applications that it can perform. Understanding these and other advanced applications can save countless hours and provide better insight into data.

For more office tips and tricks subscribe to our newsletter.