Excel is a comprehensive piece of software by Microsoft that people know for its spreadsheets and data calculations, but it can be a practical tool as well.
Retailers may have come across the idea that it can be used as a till system. But what is a point of sale (POS) system in Excel?
Rather than using specialised POS software on a touchscreen tablet, you can make an application (app) in Excel that acts like a checkout. This allows you to have a homemade POS system on a computer without buying expensive till equipment or software – but you do need to know what you’re doing.
Let’s delve into the different types of POS systems in Excel.
How does it work?
An Excel POS system can be as basic or complex as you’re able to make it. You should have at least some experience of using spreadsheets and formulas within their columns and rows, as otherwise it would take longer to work out and mistakes would be much more likely.
There are different ways to create an Excel point of sale:
- Simple spreadsheet with a list of inventory and sales that you manually edit field-by-field when making a sale.
- Complex spreadsheet with macros (programmed actions) that automatically update relevant fields when registering a sale.
- Downloading an Excel POS application that’s already created and just needs your inventory, pricing and shop details set up.
An Excel POS that uses macros connects information across tabs of spreadsheets containing different data groups. For example, you may have an inventory list with stock counts in one tab, transactions list in another, and a POS page in the main spreadsheet where you record sales.
The beauty of Excel is that you can create and customise any information to suit your retail shop, but these are common things to record:
- Inventory (products, codes, categories, stock levels, etc.)
- Pricing (purchase price, sales price, profit, etc.)
- Sales (transaction number, product quantity, payment method, etc.)
Image: Mobile Transaction
Example of a product spreadsheet linked to a point of sale.
The POS screen can emulate professional POS software, with stylised buttons (if you know how to make that in Excel) to tap on a touchscreen laptop.
It’s even possible to integrate a barcode scanner, for example via a web application that feeds data into Excel automatically. This enables you to add new products without manually entering them into the spreadsheet.
A Google search will show you many videos of how to make a POS programme in Excel. Some websites and YouTube videos even include links for downloading the app so you don’t have to make it from scratch.
Alternatively, you can create simple spreadsheets without data where every field has to be manually updated when, for example, a sale is made, stock changes and refunds are made. You can avoid using formulas and macros this way, but it’s more laborious and time-consuming to manage.
Is it worth the effort?
With the selection of cheap or free POS apps available today, it’s hard to promote Excel as the better option unless you:
- Prefer the familiarity of data entry on a PC computer
- Can’t afford a POS tablet or touchscreen register
- Don’t want to pay for software that can be replicated in Excel
- Need the customisability of Excel’s macros (automation) and formulas (calculations)
- Enjoy creating your own POS system
Retail point of sale apps to download on your iPad, iPhone or Android device are designed to be user-friendly with the most relevant functions for brick and mortar shops. Excel is not user-friendly and more prone to human errors than POS apps. It requires a certain skill level to use and can be time-consuming.
A big reason why some shops choose Excel is its complex data analysis capabilities that some POS systems lack. But even in this case, they may still use a separate checkout system in parallel with Excel just for the sales and inventory data.
If you need more complex reports, you can usually export sales data to Excel to create any kind of customisable reports with formulas. That said, most POS software already have functions to analyse products and sales.
An Excel POS system does not have a sophisticated user experience, and requires using a keyboard or mouse to navigate fields that could just be tapped on a touchscreen. Connecting with a barcode reader only makes certain operations easier.
In contrast, POS software integrates directly with card machines, cash drawers, receipt printers etc. as opposed to Excel that mostly works independently from POS equipment. This means you’ll have to manually operate equipment in tandem with data entry, unless the checkout is minimal with no equipment.
Another consideration is data storage: does your laptop have antivirus software? Are you storing inventory records and transactions adequately on your hard drive or in the cloud (online)? It’s important to protect sensitive data against hacking and avoid losses of records.
Most EPOS systems today store data securely in the cloud in real time, so you never have to back it up on a local computer.
Despite all this, Excel can save money for a one-person business with limited products if all you have is a computer. There’s no doubt you can have fun with it (if you’re technically-minded), but inexpensive POS software can vastly improve operations if you’re open to it.
