Most small business owners have used Excel before. If you’ve used the tool before, you know that it offers a spreadsheet grid that allows user to record and store any type of information that they require. However, most Excel users are not taking full advantage of everything that the tool has to offer. There are a variety of simple yet powerful Excel formulas that allow you to automate calculations and number crunching.
In this post, we’ll walk you through our favorite Excel formulas and show you how they can be used for everyday business tasks.
Determine the Sum of Your Data
Formula: =SUM( )
One of the easier yet most important formulas that you should know is the sum formula. This formula tells Excel to carry out basic arithmetic by adding numbers in a range of cells. To carry out this formula, you simple have to enter =SUM into a blank column, and select which cells you want Excel to calculate the sum for. You can use this formula for as many different cells as you wish.
Determine the Average of Your Data
Formula: =AVERAGE( )
Similar to the sum formula above, the average formula allows you to calculate the average of a range of cells containing numbers. For instance, if you have your monthly expenses in one column from A1 to A12, you can type =AVERAGE(A1:A12) into a blank column and determine your average monthly expenses.
Count How Many Cells Contain Numbers
Formula: =COUNT( )
With the count formula, you can count the number of cells that contain numbers. For instance, if you had a list of towns with over 10,000 people, you can use this formula on the list to determine how many towns fit that criteria.
Determine the Smallest or Largest Number in a Range of Cells
Formula: =MIN( ); =MAX( )
If you want to find the lowest number in a range of cells, say from cells A1 – A100, you can enter =MIN(A1:A100) and instantly find the smallest number contained in those cells. You can do the opposite with the MAX formula and quickly determine the largest number in a range of cells.
Clean up your data
Formula: =TRIM( )
Often times when copying and pasted data into Excel, you will end up with a ton of excess whitespace that makes the spreadsheet difficult to organize. The TRIM formula is a great way to clean it up. You can type =TRIM(A1), and this will automatically remove the excess space in that specific cell. If you want to remove line breaks instead of excess space, you can use the CLEAN formula instead of TRIM.
Get character counts in a cell
Formula: =LEN( )
You can enter =LEN(A1) to instantly get the character count for that specific cell. This includes both number of characters and any white spaces.
Take data from two cells and turn into one
Formula: =CONCATENATE( )
When using the Concatenate formula, you can take data from two completely different cells and make them into one. For instance, if you have a customer list in Excel that has first and last name in different cells, you can use Concatenate to put their first and last names together in the same cell.
Determine number of days between two dates
Formula: =DAYS( )
With the Days spreadsheet, you can determine the number of days between two different dates.
Determine number of work days between two dates
Formula: =NETWORKDAYS( )
Network days is similar to the formula above, but it allows you to calculate only the number of work days between two different dates.
See the current date and time
Formula: =NOW( )
If you use the Now formula you can see the current date and time whenever you open a worksheet. You can also use this formula to see a future date by entering =NOW()+2. This would show you 2 days after the current date.
Round off numbers
Formula: =ROUND( )
The Round function allows you to round of numbers. It requires two different inputs – both the number or a cell, as well as the number of digits to round to. For instance, if you have the number 105.782738 in A2, you can enter =ROUND(A2, 0), which will give you 106. =ROUND(A2, 1) will give you 105.8.
Find specific information in large data tables
Formula: =VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
VLookUp is a great formula to use if you need to find specific data in a large data table. For instance, if you have a long list of customer names and numbers, you can look up a specific number for a specific customer. Office support provides a great video that outlines how to use this formula.
Determine whether given condition is true or false
Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)
The IF formula allows you to determine whether a specific condition is true or false. For instance, if you wanted to find if one of your employees worked for 40 hours last week, you could use this formula. Check out Office Support to learn more uses of the IF function.
If you want to learn more about Excel, check out our post, Top Free Small Business Excel Templates That Can Save You Time.