- A few core formulas and tools solve most small business spreadsheet problems.
- Clean, consistent data matters more than complicated formulas.
- Back up your files and protect sensitive sheets with proper sharing settings.
The Short Answer
You do not need to be an expert to run a business with spreadsheets. A small set of skills will let you track sales, expenses, stock, customers and profit. The most valuable habit is keeping your data clean and consistent. Formulas and charts are only as good as the numbers you put in. The same ideas work in Excel and in Google Sheets.
Start With Clean Data
Keep one row per record, such as one sale per row, and one column per type of information, such as date, customer, item, amount and payment method. Use the same format for dates and names, avoid empty rows and merged cells in your data and give every column a clear heading. Clean tables are easier to sort, filter and analyse.
The Formulas to Learn First
| Formula | What it does |
|---|---|
| SUM | Adds numbers, such as total sales |
| AVERAGE | Finds the average, such as average order value |
| COUNT and COUNTIF | Counts entries, or entries that meet a rule |
| SUMIF and SUMIFS | Adds amounts that meet one or more conditions, such as sales by month |
| IF | Shows a result based on a test, such as paid or unpaid |
| XLOOKUP or VLOOKUP | Finds information in another table, such as a price for a product code |
Type an equals sign, the formula name and the cells you want to use, and let the spreadsheet suggest the rest. Learn them one at a time using your real data.
Use Pivot Tables for Quick Summaries
A pivot table sums up large lists in seconds. For example, it can show sales by product, by month or by salesperson without writing formulas. Select your data, insert a pivot table and drag fields into rows, columns and values. Practise with a copy of your data first.
Use Charts and Conditional Formatting
A simple chart can show a trend that is hard to see in numbers. Use line charts for change over time, bar charts for comparisons and keep the design simple. Conditional formatting colours cells automatically, such as highlighting overdue payments in red or low stock in orange.
Reduce Mistakes
Use data validation to create drop-down lists, so people enter consistent values. Lock cells that contain formulas, and keep a separate sheet for settings like tax rates. Name your sheets clearly and add a short note on how the file works.
Share and Protect Your Files
Share files with the right access level, such as view only for most people and edit for those who need it. Remove access when someone leaves. Do not store passwords or full card numbers in a sheet. Back up important files using the 3-2-1 rule. Also see our comparison of budgeting apps if you would rather use an app than a sheet.
Action Checklist
- Keep one row per record and one column per type of data
- Use consistent date and name formats
- Learn SUM, IF, SUMIFS and XLOOKUP or VLOOKUP
- Try a pivot table with a copy of your data
- Add one chart that shows your main trend
- Use drop-down lists to prevent entry mistakes
- Set sharing permissions and keep backups
