12 Microsoft Excel Accounting Tips for Small Business Owners

While there is a plethora of financial tools to manage your business bookkeeping, many businesses still use Microsoft Excel to record transactions. You may be using Microsoft Excel 2022 or Office 365 for recording transactions.

According to a report by Benchmarking the Accounting & Finance Function, nearly 63 percent of companies use MS Excel for accounting purposes. The spreadsheet software has various features that make it easy to record different accounting transactions.

MS Excel can be used for recording income and expenses. The software can also be used for preparing financial statements. Here we will provide some tips to save time in using spreadsheet software for recording transactions.

1. Paste Special

Paste Special is a powerful feature of MS Excel. The feature allows you to paste text with more options as compared to the normal function.

To use the special paste function, you should press CTRL + ALT + V if using the software on Windows. If you are using MAC, you should press CTRL + CMD + V.  You can also paste special by clicking on the Home tab and then selecting the Clipboard icon.

The Paste Special option allows you greater control in pasting values. You can paste formulas, values, formats, comments, and validation text using the feature. In addition, you can specify to paste all using the Source theme, all except borders, column widths, formulas and number formats, and values and number formats. In addition, you can specify to paste all emerging conditional formats.

The function also lets you carry out specific functions with the values. You can tell Excel to add, subtract, multiply, and divide. Moreover, you can specify to Skip blanks and Transpose values.

2. Edit a Cell

You must double-click a cell to edit the content. A more time-saving keyboard shortcut to edit the cell is F2 for Windows and CTRL+U for MacOS. This shortcut will make editing accounting entries in Excel a breeze.

3. Select a Row

You can select a row by clicking on the far right corner of a row. Another time-saving way to select a row is to press Shift and Spacebar on Windows. The keyboard shortcut for selecting a row on Mac is CTRL + Spacebar.

4. Currency Format

Adding currency in front of values can be cumbersome. You can easily apply a currency to the values by clicking on Shift + $ on PC or Shift + 4 on Mac.

5. Multi-Tabbed Worksheet

Most accounting excel files have multiple sheets. You can easily move between the sheets by clicking on CTRL + Tab on Windows and Option + Tab on MacOS.

6. Sum Formula

The sum formula in Excel automatically adds the values of the cells. You can sum entire columns by clicking on Alt+ on PC and CMD+Shift+T on Mac. The shortcut will select all the values in the above cells. This is a great shortcut that can save a lot of time and hassles when recording transactions.

7. Use CountIF Formulae

The countIF formula is a valuable function for accounting. The accounting function will let you analyze accounting data. Suppose that you want to know about the daily sales that exceed $10,000 during a day. If you have 30 daily sales in column C, you should use the following formula.

=CountIF(C1:C30, “>10000”).

8. Use SumIF Formulae

SumIF is also a valuable feature of Excel. The function will sum up a group of values that meet the specified criteria. Let’s suppose that you want to sum the sales made by a Brady during a year. Also, suppose that sales values are listed in column B contains and a salesman is listed in  C. To search for sales made by Brady, you can use the following formula.

=SumIF(C1:C30; Tom;B1:B30).

9. Use SumIFs Formulae

An advanced version of the SumIF formula is the SumsIf formula. This formula lets you specify multiple conditions. For instance, you can specify Excel to sum sales of apples by Brady using this formula.

Suppose that column A contains the number of sales, column B lists product names, and column C contains the name of the salesman. If you want to know the sum of bananas sold by Vincent, you will use the following formula.

=SUMIFS(A2:A9,B2:B9,”=Bananas”,C2:C9,”Vincent”)

10. Sort Values

Excel allows you to sort values In multiple ways. You can sort values by columns, cell values, and order. You can order from smallest to largest or the other way around.

To sort values, you should go to Data and select Sort. Then you must select sort by column, values, and order.

The Sort on drop-down value also allows you to sort by font color or cell color. In addition, you can use conditional formatting to sort values.

Excel also allows you to create a custom list for sorting values. For instance, you can select names and create a custom list for sorting the list.

11. Advanced Filter Data

Excel contains a Filter option that lets you filter data based on different criteria.  You can filter the data by font or cell color. In addition, you can apply conditional formatting to filter cells. The Custom filter option allows you to filter values using a wildcard, partial matches, and custom conditions.

You can also filter dates differently. For instance, you can sort dates by years, months, and weeks.

12. Apply Excel Table to Data

A nifty Excel feature is filtering data. You should press Ctrl + T to insert an Excel table to data. Another option is to go to the Insert Excel Table in the menu.

An important point to note is that when you change the data you should also change the reference. The range address data changes when you update it. Updating the reference will ensure that you get the correct figures.

Conclusion

The above are just a handful of Excel tips for recording accounting data. Remember that spreadsheet software is appropriate if you own a small business. However, the software does not create financial statements to know the financial health of your company.

You must hire outsourced bookkeeping providers that use accounting software to get an overview of your business performance.

Join 1000s of Small Businesses and CPAs who outsourced their Bookkeeping to Maxim Liberty!

Try our award winning services with no risk. We have an unconditional money back guarantee on your 1st payment. Just 5 minutes of your time over the phone, can get you started with the best Bookkeeping service in USA while saving you hundreds of dollars.