No Need to Reinvent the Wheel: 8 Most Used Excel Formulas in Small Retail Businesses

Despite huge advancements in data analysis programs over the past few years, Excel has withstood the test of time. 

Still relevant in 2022, everyone from freelancers to big corporations can benefit from the service that Excel provides. But few can benefit from this streamlining software as much as a retail SME. 

When your business is too small to invest in high-tech data analysis software, yet big enough that the admin can’t be done on brain power alone, Excel steps in as a highly supportive tool that makes those mundane yet essential everyday tasks that much more tolerable. 

From comparing sales data to drawing up invoices, Excel has the potential to save you significant time and energy. If you know how to use it properly. 

I’m A Beginner – How Do Formulas Work?

Excel’s formulas are built-in functions that significantly increase the speed and accuracy of business expense tracking and data analysis. You can input formulas by selecting an empty cell, typing the equal sign =, and then typing the name of your formula. 

There are over 475 formulas in the Excel Functions library, but only a few basic ones are needed to optimize the back-end management of your small business. Without further ado, the following list details the most used and most useful formulas Excel has to offer for businesses just getting started.

  • Sum, Count, Average 

In Excel terms, these formulas would be presented as SUM, COUNT, and AVERAGE. Think of them as the bread and butter of your Excel experience—helping you to make fast, intuitive calculations at the press of a button. 

The SUM formula provides you with the sum total of any numbers inside columns or rows you choose to select. To make things even more precise, you can add the first and last cell numbers in parentheses to save time. Example: =SUM(A6:A15) 

The COUNT formula counts the number of cells within an array that contains a number value. You could use this to determine if someone has paid a bill, or generate statistical calculations. 

Like the others in this category, AVERAGE is pretty self-explanatory. Inputting this formula into your spreadsheet will provide you with a swift average of all the numbers you’ve selected. 

  • Autofill 

This is a great formula if you’re pressed for time but need to fill in a high volume of sequential numbers, dates, days of the week, or months. AutoFill can fill up a range in any direction of your choosing by using the fill handle. 

The range can be filled with numerical or textual values, which must either then be copied from the initially nominated cell or based on the pattern you create of those nominated cells. Simply select the left mouse button, and drag the plus symbol over the cells you want to fill. Easy!

  • FlashFill 

FlashFill is an extension of the AutoFill formula—and it’s a great way to clean up or enter new data. It comes in very handy for entering big batches of data in an accurate, near-instantaneous way. 

There are three main steps needed to execute the FlashFill command: first, you’ll need to enter your source data into a cell. Next, to the right of that cell, enter what you want in the first cell. Pressing Ctrl+E activates the FlashFill, and Excel will complete the list for you. 

  • Vertical Lookup

Read as VLOOKUP in Excel formula terms. This powerful function can help your business search for a specific value in any column (also known as a table array), as a means to return a value from a separate column into the same row. 

Simply input your formula, followed by parenthesis that contains the lookup value: =VLOOKUP(F4,H5:B3,C4) and add an approximate match (either TRUE or FALSE) to help Excel understand what your values are. Great for finding costs quickly in a stocktaking spreadsheet!

  • Conditional Formatting

Conditional formatting in Excel allows you to highlight cells with a color of choice, depending on that specific cell’s value. This can be useful when glancing over a spreadsheet and needing to understand values without reading into each individual cell. 

Start by selecting your range of cells and then click the Conditional Formatting button. Select Highlight Cells Rules, and then Greater Than. Enter your chosen value and select a color and formatting style. Now, Excel will change any values greater than that number for your chosen color. 

  • Array

Array formulas are used to perform multiple calculations on one (or more) cells at a time. An array is essentially a collection of cells that have similar values stored within them, whether they are in rows, columns, or even a combination of the two. 

Also referred to as the CSE formula, the Array function is executed by pressing Ctrl+Shift+Enter keys, making this one of the easier formulas to perform. It can be used for complex mathematical calculations, or something as simple as a salary calculation for your next employee of the month. 

  • Forecast Linear 

The FORECAST. LINEAR function on Excel helps you to predict a future value following a linear trend – all with the power of mathematics. Previously known as just the FORECAST formula, the addition of LINEAR helps the function take trajectory into account. 

By adding .ETS (FORECAST.ETS) to your formula, you can even predict a future value using Exponential Triple Smoothing, which will add a buffer calculation for seasonality. This formula is important for small businesses needing to follow trends and predictions for future sales or engagement. Once you have a forecast sheet, you can even save it as a PDF and combine it with other PDFs to create a non-editable paper trail that you can easily refer to.  

  • Proper 

Ever come across a spreadsheet with misplaced capitalization and other easily fixable errors, but the sheer quantity overwhelmed you? Well, PROPER is your new best friend. Use it to properly capitalize words without affecting punctuation, numbers, or spaces. 

All you need to do is input your =PROPER formula into the desired cell, followed by the first and last cell numbers you wish to capitalize. Ex: =PROPER(A1:B10). Et voila, no more clunky, unprofessional text!

It’s clear that a basic understanding of Excel formulas can help you manage your retail business and streamline operations. Sometimes, there’s no need to reinvent the wheel, it stays the same for good reason.