Macros help you automate repetitive tasks in Excel, and if you use spreadsheet accounting, the right macros can really help you save time. To create macros, you need to be in the developer mode of Excel, and you have to input Visual Basic code based on what you want the macro to do.

1. Automatic Backups and Sharing

So you don’t lose information, you may want an Excel macro that creates a backup file of your spreadsheet. For example, the macro could create a PDF version of a specific report every time you save it. Similarly, if you want to share or print information automatically, you can also create a macro that emails a copy of the spreadsheet or sends the document to your printer when you save it.

2. Reconciliation

Reconciling bank statements can be a laborious process with spreadsheet accounting, but it is possible to create macros that help. In particular, a macro can find all the dollar amounts from your bank statement that match numbers in your accounting software. Once these numbers are removed, you are only left with the discrepancies, and that can be an effective way to note outstanding cheques or errors.

3. Auditing

If you want to audit expenses in a certain time period, it can be helpful to choose items to audit randomly. To ensure your selections are truly random, you may want a macro that can generate random numbers. These macros are set up so you put in the first and last cheque number written in the time period you are auditing. Then, the macro generates a random cheque number for you to audit. You could also use this type of Excel macro to audit transactions by date, invoice number, or other numerical identifiers.

4. Capital Cost Allowance Calculations

Entering formulas for capital cost allowance in Excel can be time consuming, especially as you often have to type the same formula repeatedly. However, you can create macros that enter these formulas for you. Depending on your needs, the right code can create Excel macros linked to formulas concerning depreciation, capital cost, and remaining capital cost allowance.

5. Print Whole Workbook

When using Excel, you often keep multiple spreadsheets together in a virtual workbook. So that you don’t have to print each sheet individually, you may want a macro that allows you to print the whole workbook. This is one of the relatively simplest macros to set up, and may be one of the first you want to try. You can also customize the macro depending on whether you want to print comments, hidden worksheets, charts, or other information with the workbook.

6. Starting Over

You may also set up a macro that creates a totally new workbook, and you can set it up so it generates the amount of sheets you want. There are macros that allow you to copy an existing workbook into a new workbook or draw information from one sheet into another sheet.

References & Resources

• thebalance.com
• ww2.cfo.com