4 Most Useful Spreadsheet Formulas For Managing Small Business

Managing a business is not easy. You need to track the sales, do your accounting, keep inventories, etc. The good thing is spreadsheets can help you with these tasks to save your time.

Below are the four most useful spreadsheet formulas that every small business owner or entrepreneur should know.

4 Most Useful Spreadsheet Formulas

SUMIF

The SUMIF formula is perfect for spreadsheets with categories. It lets you sum the values in a range that meets specific criteria. This formula has the syntax:

SUMIF ( range, criterion, [ sum_range ] )

• range is where the different categories are. This is where you want the sum to be evaluated. It is important to have the cells contain a number or any character because if they do not, they will not be accounted for in the sum.

• criterion is the category we want to get the sum of.
• sum_range is the range of cells containing the values we are going to sum. The sum_range should be of the same size (number of cells) as the range for the formula to work properly.

Take a look at the image below. We took the following steps to get the total sales amount for all the Samsung phones sold.

1. First, type the following in any empty cell: = SUMIF (
2. Highlight the range containing the different criteria.
3. Select any cell containing the specific category you want to get the sum of.
4. Highlight the cells containing the amounts.

Fot this example the final formula should look like this.

= SUMIF ( A3:A18, A4, B3:B18 )

Alternatively, you can just also type “Samsung” for the criterion.

= SUMIF ( A3:A18, “Samsung”, B3:B18 )

As you can see, the answer shows up above the formula even before pressing Enter.

COUNTIF

Now, what if you want to get the total number of sold pieces from a specific category? COUNTIF is the perfect formula for that. It counts the number of cells based on the criteria you set. Its syntax looks like this:

= COUNTIF ( range, criterion )

• range is the range of cells to count.
• criterion is the specific category to be counted.

Let’s go back to the example above and count the number of Samsung phones sold. To do this:

1. Type the following in an empty cell: =COUNTIF(
2. Highlight the range containing the criterion.
3. Select the cell containing your criterion.

Your formula should look like this:

= COUNTIF ( A2:A18, A4 )

1. Press Enter and you should be able to get the total number of Samsung phones sold. You can try this for other phone brands and see if it will give you the correct answer.

CONDITIONAL FORMATTING

Conditional formatting is used to make your data be easily read and analyzed. You can use many formatting options such as highlighting texts, changing the font size, and applying different styles.

 To do conditional formatting:

1. Highlight the cells you want to be formatted.
2. Click Format > Conditional Formatting in the Toolbar. A box will appear containing the format rules and style.
3. Choose the formatting rule and the style you want to apply.

In the example below, I have formatted the price of Samsung phones to be color red. You can apply your own formatting style and even add more formatting rules. Through this way, you can easily see which are priced at 999 and which are not. This makes it easier to analyze your spreadsheet data.

VLOOKUP

The VLOOKUP function saves you time by allowing you to look up information across multiple sheets. The syntax of this function is shown below.

= VLOOKUP ( search_key, range, index, [ is_sorted ] )

• search_key is the information you are looking for.
• range is the range of cells where we will search.
• index is the position of the column containing the value to be returned. The first column is always index number 1.
• is_sorted is true or false. For an exact match, use “false”, otherwise, use “true”.

In the example below, I looked up the price of the Samsung phone using VLOOKUP.

More Spreadsheet and Business Tips

Learning these formulas will help you manage your business more efficiently. You can now look at your spreadsheet data and not be overwhelmed because you know how to organize it. Take a look at some of our other business blog content to help you run your company as effectively as possible.