Tip of the Week: Essential Excel Formulas to Know

Microsoft Excel is, putting it frankly, a hugely capable piece of software that can do more than most people would imagine—more than we could go over in a million blog posts. However, there are a few essential functions that Excel offers that business users are bound to find useful. Let’s go over these most essential functions so that you have them in your back pocket.

Before we do, however, we should review how these formulas can be used.

How to Use Excel Formulas

Once a cell is selected, you can either type your formula into the cell itself or into the formula bar found at the top of the sheet. One rule of thumb is that every formula will begin with an equals sign. From there, you can select or type in the coordinates of the cells and the appropriate operators to build out your needed formula. If used properly, these formulas can effectively automate your spreadsheets to update themselves as your data inputs change.

What follows are a few of the more common formulas that you might find use for in your operations.

=SUM

This function gives you the total value of a selected range of cells, making it much easier to total up long lists of numbers for your reference. If, for instance, you had a list of numbers in Column C, starting at Row 3 and ending at Row 35, you’d type =SUM(C3:C35) to find the total, which would be displayed in the cell that you assigned the function to.

=AVERAGE

Similarly, =AVERAGE produces the average of the cells you list in the formula. For instance, =AVERAGE(A1,B2,C3) gives you the average of the values in cells A1, B2, and C3.

=CEILING and =FLOOR

These functions allow you to round numbers up and down to the nearest multiple of significance that you dictate. For instance, let’s say you were applying =CEILING(D8, 5) when D8 was populated with 51.06. The cell the formula was applied to would be rounded up to 55. Likewise, =FLOOR(D8,5) would round down to 50.

=CONCATENATE

Of course, Excel can be used to keep track of text-based data as well. The =CONCATENATE function allows you to tie different text data together. So, if A1 read Comprehensive data backup and B1 read is a necessity for a modern business, the formula =CONCATENATE(A1, “ “, B1) would produce a cell that reads Comprehensive data backup is a necessity for a modern business. Alternatively, you can accomplish the same thing with =CONCATENATE(A1&” “&B1).

=NOW()

Entering this into a cell will present the current system date and time, allowing you to keep track of when data was entered.

=TODAY(), et alia.

Similarly, there are numerous functions that provide data based on what the computer indicates:

• =TODAY() provides the current date
• =DAY(TODAY()) provides the current day of the month
• =MONTH(TODAY()) provides the month, represented as a number between 1 and 12 

=TIME, et alia.

Likewise, there are various functions that allow you to track the time that data has been input into Excel.

• =TIME() allows you to put in the time, formatted as a serial number in the hours, minutes, and seconds, that will be converted into a typical time format. For instance, =TIME(18,28,45) would display as 6:28 PM.
• =HOUR(NOW()) generates the current hour as a value between 0 and 23 (12 AM to 11 PM).
• =MINUTE(NOW()) generates the current minute as a value between 0 to 59.
• =SECOND(NOW()) generates the current second as a value between 0 to 59.

=VLOOKUP() and =HLOOKUP()

These functions, short for vertical lookup and horizontal lookup, allow you to seek out specific values in one column or row within a table and kick back data associated with that value. For instance, you could create a table that outlined your top selling products or services, the gross sales of each, the total cost to you each brings, the employee responsible for selling the most, and the employee responsible for selling the least. Using =VLOOKUP() or =HLOOKUP(), you can select data to reference.

Using the above example, you could use =VLOOKUP() or =HLOOKUP() to identify the relationship between different data points.

=IF

The IF() function can be used to get an idea of whether a condition is true or false as it pertains to your data. So, if you wanted to determine if you had reached your operating threshold, you could put in =IF(D6>5,000,”Yes”,”No”) with your current revenue in D6. This can help give you an easier means of gathering information at a glance—particularly on a busy spreadsheet.

=COUNTIF and =SUMIF

Finally, there are times when you just need to take a tally or a sum from a massive dataset. =COUNTIF and =SUMIF make it a lot easier to do so.

• =COUNTIF() allows you to take a tally of all cells that match the conditions you set. You select the range you want to examine and identify the condition you want to meet. For example, if you wanted to tally up the number of customers you had in certain cities, you could put in =COUNTIF(A1:A100, “Smallville”) to get your answer.
• =SUMIF() does the same thing, except instead of counting up the number of instances of a certain input, it adds the cells up. To continue our example, you could use =SUMIF(A1:A:100,”Smallville”, E:E).

This Is a Tiny Portion of What Excel is Capable of

What are some of your favorite or most-used Excel functions? Let us know in the comments, or ask us if there are any ways to do certain things you need Excel to do!