Basic Excel Formulas for Small Businesses
Learn to calculate totals, percentages, and profits; copy formulas; and fix common Excel errors with practical examples.
Basic Excel formulas help you turn business records into useful figures, from total monthly expenses to the profit on a sale. You can use them to track income, calculate discounts and compare results without working out each figure manually.
This guide explains how formulas work, with examples you can follow in your own spreadsheet. You’ll learn how to add, subtract, multiply, and divide; use functions such as SUM and IF; and avoid common mistakes. You do not need any previous experience with Excel formulas.

Basic Excel Formulas at a Glance
- Start with =: This tells Excel you are entering a formula.
- Basic calculations: Use + to add, – to subtract, * to multiply and / to divide.
- Useful functions: SUM adds totals, AVERAGE calculates the mean, and IF returns a result based on a condition.
- Copying formulas: Cell references normally adjust when copied. Use dollar signs, such as $E$1, to keep a reference fixed.
- Percentages: Divide one amount by another and apply Percentage formatting. Do not also multiply by 100.
- Check your results: Incorrect references, numbers stored as text and missing entries can affect your calculations.
Excel Bookkeeping Templates
To save time, we created bookkeeping templates to help you track your business finances. They can help you track income and expenses, create invoices, and track mileage. The templates come with formulas already set up, saving time and reducing the risk of errors.
Disclosure: This content may contain affiliate links, which means if you click on them, I may get a commission (without any extra cost to you).
Some templates we offer for free download include cash book, profit and loss, balance sheet, expenses, sales invoices, and petty cash. Please view our complete list of Excel Bookkeeping Templates.
Excel and Accounting Software
Excel can be a valuable tool for managing all your accounting needs, especially for small businesses or freelancers with straightforward finances. Its flexibility lets you create custom spreadsheets, track income and expenses, generate invoices, and even do basic financial analysis. However, as your business grows and transactions become more complex, Excel may not be sufficient on its own.
In those cases, consider using Excel alongside dedicated bookkeeping software like QuickBooks or Xero. These platforms automate many time-consuming tasks, reduce the risk of errors, and provide a more comprehensive accounting solution. You can still leverage Excel’s strengths by exporting data from your bookkeeping software for deeper analysis, forecasting, and creating tailored reports.
Microsoft Excel Fundamentals
Before we look at the basic Excel formulas and functions, we need to understand the basics of Excel and how it is made up.
Sheets:
Think of sheets like individual pages in a notebook. Each sheet in an Excel workbook is a separate workspace where you can organise and analyse different data sets.
You can have multiple sheets in a single workbook, which is great for keeping related information together but in distinct areas. For example, you might have a sheet for your monthly budget, another for sales data, and another for inventory tracking.
Rows and Columns:
Each sheet is divided into a grid of rows and columns:
- Rows: These run horizontally across the sheet and are identified by numbers (1, 2, 3, and so on).
- Columns: These run vertically down the sheet and are identified by letters (A, B, C… AA, AB, AC… and so on).
Cells:
A cell is the intersection of a row and a column. Cells are the fundamental building blocks of your Excel sheet. Each cell has a unique address based on its row and column (e.g., A1, B2, C3). This is how you reference specific cells in formulas.

How Rows, Columns and Cells Work
Organising Data: You enter your data into cells. This data can be text, numbers, dates, or even formulas.
Referencing Cells: Formulas use cell addresses to perform calculations. For example, the formula =A1+B1 adds the values in cells A1 and B1.
Formatting: You can change the appearance of cells (font, colour, alignment, etc.) and the entire sheet to make your data easier to read and understand.
Analysing Data: Excel has many functions (like SUM, AVERAGE, IF, VLOOKUP) and tools (like charts and pivot tables) to help you analyse your data and uncover insights.
Example:
Imagine you’re tracking sales data.
- You could have a sheet named “Sales.”
- The columns might be Date (column A), Product (column B), Quantity (column C), Price (column D), and Total Revenue (column E).
- Each row would represent a single sale.
- In the Total Revenue column (E), you could use a formula like =C2*D2 to calculate the revenue for each sale.
Entering Formulas and Functions in Excel: Two Easy Methods
When it comes to harnessing the power of Excel for calculations, you have two main ways to enter formulas and functions:
Directly Typing in the Cell

Directly typing is the easiest approach:
Select the cell: Click on the cell where you want the calculation result to appear.
Start with the equal sign (=): This signals to Excel that you’re about to enter a formula.
Type the formula: Use cell references (e.g., A1, B2), operators (+, -, *, /), and function names (SUM, AVERAGE, IF, etc.) as needed.
Press Enter: Excel will calculate and display the result in the cell.
Example:
To add the values in cells A1 and B1, you’d type =A1+B1 directly into the desired cell.
Instead of typing the cells, you can also select them by clicking the mouse over them. Using the example above, type = followed by clicking A1 + clicking B1, then press Enter.
Using the Excel Function Button (fx)

This method is beneficial for more complex functions or if you’re unsure of the exact syntax:
Select the cell: Click on the cell where you want the result.
Click the Excel Function button: This is usually located next to the formula bar and looks like fx.
Search or browse: Search for a specific function by name or browse through categories.
Select the function: Click on the function you want to use.
Fill in the arguments: Excel will prompt you to enter the required information for the function (e.g., the range of cells you want to average).
Click OK: Excel will calculate the result.
Basic Excel Formulas and Functions
Now that we’ve covered the basics of Microsoft Excel’s layout with sheets, rows, columns, and cells, let’s look at the basic Excel formulas that turn Excel into a must-have tool for any business or data enthusiast. Once we have looked at the basic Excel formulas, we will show an example of an Income Statement, calculating the total sales, cost of sales, net profit, total expenses and net profit.
Equal sign Explained
In the world of Excel formulas, the humble equal sign (=) plays a crucial role:
Signalling a Formula: When you type an equal sign into a cell, you’re telling Excel that you are about to enter a formula, not just plain text or numbers.
Triggering Calculation: It initiates the calculation process. Without it, Excel will treat your input as text and won’t perform any mathematical operations or functions.
Universal Syntax: It is a universal spreadsheet software. Whether you’re using Excel, Google Sheets, or another program, formulas always begin with an equal sign.
What’s the difference between basic Excel Formulas and Functions
Basic formulas and functions are used for calculations in Excel, but they differ in key ways.
A formula is any expression you create to calculate, combining cell references, operators (like +, -,*, /), and constants. For example, =A1+B1 or =C1*D1. Formulas offer flexibility, allowing you to tailor calculations to your specific needs.
Functions are predefined formulas Excel provides for everyday tasks, like SUM, AVERAGE, or VLOOKUP. They simplify complex calculations and save time but are less flexible than custom formulas. Think of functions as building blocks you can incorporate into your formulas. For instance, the formula =SUM(A1:A10)*0.1 uses the SUM function to calculate a total and then multiplies it by 0.1.
So, while both formulas and functions help you crunch numbers, formulas offer broader customisation, while functions provide convenient shortcuts.
Basic Excel Formulas Examples
All our examples are simple and show the Excel formulas in the formula bar next to the fx button. Here are the most useful Excel formulas:
The SUM Function in Excel: Your Go-To for Totals
What the SUM formula does: Adds up a range of numbers.
How to use it: =SUM(A1:A10) (This would add up the values in cells A1 through A10)
When you need it: Calculating total sales, expenses, or the number of items sold.
Example: You have sales for each day of the week listed in cells B2 through B8. To find the total weekly sales, use =SUM(B2:B8).

The AVERAGE Function: Find the Mean
What it does: Calculates the average of a range of numbers.
How to use it: =AVERAGE(B1:B5)
When you need it: Determining average order value, average customer rating, or average daily sales.
Example: Your customer ratings are in cells C2 through C10. Use =AVERAGE(C2:C10) to find the average rating.

Simple Addition and Subtraction
What it does: Adds or subtracts values in specific cells.
How to use it: =A1 + B1 (adds the values in cells A1 and B1), =A1 – B1 (subtracts the value in B1 from the value in A1)
When you need it: Calculating profits (revenue – expenses), remaining inventory (starting inventory – items sold), or price changes.
Example: Your revenue is in cell A2, and expenses are in B2. Calculate profit using =A2-B2.

Simple Multiplication Formula
What it does: Multiplies values in specific cells.
How to use it: =A1*B1 (multiplies the values in cells A1 and B1)
When you need it: Calculating total costs (price per item Quantity), sales tax (price tax rate), or discounts (price * discount percentage).
Example: The price of a product is in cell A2, and the Quantity sold is in B2. Calculate the total revenue using =A2*B2.

Simple Division Formula
What it does: Divides values in specific cells
How to use it: =A1/B1 (divides the cels in A1 by B1
When you need it: Calculating average sales per month if a total is given (total sales divided by 12)
Example: Total sales are in A2, and the number of months is in B2. Calculate the average sales using =A2/B2

Calculating Percentages in Excel
What it does: Calculates discounts, percentage changes and one amount as a percentage of another.
How to use it:
- Percentage of an amount: =A2*B2
- Price after a discount: =A2*(1-B2)
- Percentage increase or decrease: =(B2-A2)/A2
- One amount as a percentage of another: =A2/B2
When you need it: Calculating discounts, comparing sales between periods or working out profit margins.
Example: Calculate a discount
The original price of £200 is in A2, and the discount rate of 10% is in B2.
Use =A2*B2 to calculate the £20 discount.
Use =A2*(1-B2) to calculate the discounted price of £180.
Example: Calculate a percentage change
Last month’s sales of £1,000 are in A2, and this month’s sales of £1,200 are in B2.
Use =(B2-A2)/A2 and format the result as a percentage to display a 20% increase. A negative result indicates a decrease. The original amount in A2 must not be zero.
Example: Calculate a profit margin
Gross profit of £300 is in A2, and sales of £1,000 are in B2.
Use =A2/B2 and format the result as a percentage to display a gross profit margin of 30%.
Formatting tip: Select the result cell and click the % button on the Home tab for percentage changes and margins. Do not also multiply by 100. Keep discount amounts and discounted prices formatted as currency.
The IF Formula: Make Decisions with Data
What it does: Performs a calculation based on whether a condition is met.
How to use it: ==IF(C1>100,”Over Budget”,”On Track”) (This would display “Over Budget” if the value in cell C1 is greater than 100, and “On Track” if it’s not). To display a blank cell, use “”.
When you need it: Create alerts for low inventory levels, identify high-performing sales reps, or flag overdue invoices.
Example: Total stock level in A2, the formula in B2 is =IF(A2<10, “reorder”, “”)

The COUNT Function in Excel: Tally It Up
What it does: Counts the number of cells containing numbers.
How to use it: =COUNT(D1:D20)
When you need it: Find out how many orders were placed in a month, count the number of employees, or track how many products are in stock.
Example: Amount of different stock items in a warehouse in B6, the formula =COUNT(B2:B5) shows there are 3 different stock items. It only counts the cells with a number in it.

Copying Excel Formulas and Using Absolute References
You can copy a formula to other rows instead of typing it again. Excel normally adjusts the cell references to match each new row.
For example, if column A contains quantities and column B contains prices, enter this formula in C2:
=A2*B2
Select C2, then drag the small square at the bottom-right corner of the cell down the column. This is called the fill handle. In C3, Excel changes the formula to =A3*B3.
Keeping a Cell Reference Fixed
Sometimes a formula needs to refer to the same cell when you copy it. Use an absolute reference, with a dollar sign before both the column letter and row number.
For example, enter a discount rate of 10% in E1 and list your original prices in column B. To calculate the discount amount, enter this formula in C2:
=B2*$E$1
When copied down to C3, the formula becomes =B3*$E$1. The price reference changes, but the discount rate stays fixed.
- B2 is a relative reference that adjusts when copied.
- $E$1 is an absolute reference that stays fixed when copied.
If you change the discount rate in E1, all formulas referring to that cell update automatically.
Basic Excel Formulas Example: Income Statement
Below is a basic Income statement example and instructions on how to create it yourself.

Formulas Breakdown:
Gross Profit (C4): This formula subtracts the cost of sales (C3) from the sales (C2).
=C2-C3
Net Profit (B5): This formula subtracts the expenses (C5) from the gross profit (C4).
=C4-C5
Additional Tips:
Formatting: Format cells C2, C3, C4, and C5 as currency to display the values correctly.
Adding More Items: You can easily add more line items to your income statement (e.g., different types of expenses) by inserting rows and adjusting the formulas accordingly.
Formulas for Percentages:
Gross Profit Margin: =(C4/C2)*100 (This calculates gross profit as a percentage of sales)
Net Profit Margin: =(C5/C2)*100 (This calculates net profit as a percentage of sales)
This simple example demonstrates how Excel can automate calculations, making it a valuable tool for financial reporting.
Formatting Number Cells
In Excel, formatting ensures that your numbers are presented clearly and professionally. Here’s how to format cells for numbers, decimals, and currency:
Number Format:
Purpose: This is the default format for general numbers. You can customise how many decimal places are displayed and insert a comma to separate numbers.
How to apply: Select the cells, right-click, select Format Cells, then choose Number and Commas if needed.
Example: If you want to display two decimal places, enter “2” in the “Decimal places” box.

The example above will produce the following table.

Currency Format:
Purpose: This format displays numbers with a currency symbol (e.g., $, £, €) and usually includes commas as thousands separators.
How to apply: Select the cells, right-click and select Format, choose Currency, and enter the currency symbol.
Using the example above, we added a £ sign.

Common Excel Formula Errors and How to Fix Them
If a formula does not work or gives an unexpected result, check these common causes.
Missing Equals Sign
Excel formulas should begin with an equals sign (=). Without it, Excel may treat your entry as text or a value rather than a calculation.
Example: Enter =A2+B2 rather than A2+B2.
If the formula still appears as text, change the cell format to General, then press F2 followed by Enter.
Numbers Stored as Text
Numbers copied from another source may be stored as text. This can cause errors or leave values out of totals calculated using SUM.
How to fix it: Select the affected cells. If Excel displays a warning icon, click it and choose Convert to Number. Changing the number format alone may not convert existing text values.
Division by Zero
Excel displays #DIV/0! when a formula divides by zero or an empty cell.
Example: The formula =A2/B2 returns this error if B2 is zero or blank.
How to fix it: Check the divisor, which is B2 in this example. Enter the correct value if it is missing. If the calculation isn’t possible yet, leave the result blank until the required information is available.
Incorrect Cell References
A formula can calculate successfully but give the wrong answer because it refers to the wrong cells.
Example: If sales are in B2 and expenses are in B3, profit should be calculated using =B2-B3.
How to fix it: Select the formula cell and check the references in the formula bar. After copying formulas, check that references have adjusted correctly and any fixed references use dollar signs, such as $E$1.
If Excel displays #REF!, a reference is invalid, often because a referenced cell, row or column has been deleted.
Advanced Excel Formulas: Taking Your Skills Further
After mastering the basic Excel formulas and functions, you’re ready to step into the realm of advanced Excel functions that can significantly enhance your data analysis capabilities. Many resources are available online, including YouTube, online courses, and Websites.
Basic Excel Formulas FAQs
Conclusion to Excel Formula
Even basic Excel formulas can be game-changers for businesses. By mastering simple calculations like sums, averages, and percentages, you’ll gain the power to analyse your finances, track sales, and make informed decisions. With a bit of practice, you’ll unlock Excel’s full potential and wonder how you ever managed without it!









