Basic Excel Formulas Every Small Business Should Master
Microsoft Excel isn’t just for accountants and data analysts. Excel is a powerful tool for small business owners to track finances, manage inventory, analyse sales trends, and more. If basic formulas and functions seem intimidating, don’t worry! We’ll break down the important Excel formulas you can use to streamline your operations and gain valuable insights.
Excel Bookkeeping Templates
To save time, we have created bookkeeping templates that are a great way to keep track of your business’s finances. They can help you track income and expenses, create invoices, and track mileage. The templates come with formulas already set up, which can save time and reduce the risk of errors.
Some templates we have created 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 allows you to create custom spreadsheets, track income and expenses, generate invoices, and even perform basic financial analysis. However, as your business grows and transactions become more complex, Excel may not be sufficient on its own.
In such cases, consider using Excel in conjunction with 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:
The intersection of a row and a column creates a cell. 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 would add up 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 it by clicking the mouse over it. Using the example above, you type = followed by clicking on A1 + clicking B1 and entering.
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: You can 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 have key differences.
A formula is any expression you create to calculate, combining cell references, operators (like +, -,*, /), and constants. For example, =A1+B1 or =C10.15. 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
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: Creating alerts for low inventory levels, identifying high-performing sales reps, or flagging 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.
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 B2, B3, B4, and B5 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: =(B4/B2)*100 (This calculates gross profit as a percentage of sales)
Net Profit Margin: =(B5/B2)*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, and select format cells, then select numbers and commas if required.
Example: If you want to display two decimal places, enter “2” in the “Decimal places” box.
The above example will provide the following table.
Currency Format:
Purpose: This format displays numbers with a currency symbol (e.g., $, £, €) and usually includes commas for thousands separators.
How to apply: Select the cells, right click and select format, choose currency and enter the currency symbol.
Using the above example, we have added a £ sign.
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. There are many resources online, including YouTube, online courses and Websites.
Conclusion to Excel Formula
Even basic Excel formulas can be game-changers for businesses. You’ll gain the power to analyse your finances, track sales, and make informed decisions by mastering simple calculations like sums, averages, and percentages. With a bit of practice, you’ll unlock Excel’s full potential and wonder how you ever managed without it!
