Keeping on top of your books as a small business owner can sometimes feel like a full-time job.
Luckily, there are plenty of ways you can streamline your processes and take the time and hassle out of all those laborious data entry tasks – even if you’re not yet ready to invest in a full-blown bookkeeping or accounting platform, like Xero or QuickBooks, which would do all the hard work for you.
I’m going to talk you through a series of Microsoft Excel shortcuts that will make your life so much easier, you’ll wonder how you ever lived without them!
1. Use Excel templates for bookkeeping
Did you know that Microsoft already supplies a series of bookkeeping-focused spreadsheets? Instead of starting from scratch, take a look at the pre-built options and customise them as you see fit. These templates will often include preset formulas, categories for expenses and income, and sections for cash flow or balance sheets – so there’s no need to create all this code yourself!
2. Automate calculations with formulas
Reduce your workload by using formulas to automate calculations for total income, expenses, profit and loss, and more. Some of the most commonly used formulas include:
- SUM (=SUM(range)): Adds up values in a range.
- AVERAGE (=AVERAGE(range)): Calculates the average value of a set of fields within a range.
- SUBTOTAL (=SUBTOTAL(function_num, range)): Calculates totals while allowing for filtered data.
3. Create and use drop-down lists
Put together drop-down lists for categories like expense types, payment methods, or account names to ensure your data entry is consistent and avoid simple mistakes.
To create a drop-down list, go to Data > Data Validation and select List, then enter your options.
4. Track your expenses with Excel tables
You can convert your data range into an Excel table by selecting your data and pressing Ctrl + T. Tables allow for easier sorting, filtering, and automatic formula updates as you add new data.
5. Use conditional formatting to track your key data
You can apply Conditional Formatting to highlight key financial metrics, such as overdue payments or expenses that are exceeding a pre-set budget.
To do this, select the cells you want to work with, go to Home > Conditional Formatting, and set your rules (for example, you might want to highlight values that are greater than a specific amount).
Conditional Formatting helps you to visualise important data and spot any trends, outliers, or issues that might need your attention, particularly when it comes to overdue invoices or overly high spending.
6. Use SUMIF and SUMIFS for conditional summation
Now, this sounds like a bit of a mouthful – but essentially, Excel’s SUMIF function allows you to sum up values that meet specific criteria. For example, it can help you sum up all expenses in a particular category, so you don’t need to do this manually.
SUMIFS can be used to sum values based on multiple conditions – like summing expenses in a specific category for a particular month.
Here’s the formula:
- =SUMIF(range, criteria, [sum_range])
- =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)
7. Use Excel’s built-in data validation feature to reduce errors
Use Data Validation to control what can be entered in certain cells. For instance, you can restrict a column to only allow numbers (for quantities or prices) or dates in a specific format (for invoice dates). This reduces the likelihood of text being entered into a numerical field, and vice versa.
8. Leverage pivot tables for reporting purposes
You can use PivotTables to summarise, analyse, and report on your data more efficiently, meaning they are invaluable for trying to find important information amongst a sea of numbers! For example, you could group and categorise your data such as income by customer or expenses by category, so you can quickly gain insights into how your business is performing in these areas.
9. Stay organised by linking multiple sheets
Use separate sheets for different categories (such as income, expenses, and tax records), then link these sheets together using cell references to create handy summaries. Following this practice will allow you to arrange your data in separate sections but still pull through information from multiple sheets into an easy-to-digest summary or dashboard.
10. Back everything up in the cloud in case disaster strikes
Regularly back up your Excel workbook by saving it to your cloud storage service of choice. It’s the only way you’ll be able to retrieve your data if it gets lost or corrupted. Many of my clients like to use OneDrive, Google Drive, or Dropbox. You can also use Excel’s version history feature to track changes.
