Microsoft Excel is old software, but it is still a great option for creating spreadsheets. Spreadsheets are a great way of organizing our data, keeping important records, and bookkeeping.
How many of you have considered the importance of the latter? How many of you use Microsoft Excel for accounting purposes?
Excel accounting is one of the best-kept secrets, though it shouldn’t be!
Using Excel for accounting is a great skill that all business owners should know. If you’ve never had any experience with Excel accounting, we’ve put together this guide to show you how to get started.
The Basics of Excel Accounting
We want you to get into the habit of using Microsoft Excel for your company’s accounting purposes.
Here are some basic skills to prepare for Excel accounting:
1. A Bookkeeping Template
If you are an expert Excel user, you may try to create your own bookkeeping template. If you do, make sure you save the template and always use it for keeping a record of your accounts.
You can also search the Internet for bookkeeping templates to download for your use. You want to choose a bookkeeping template that meets your needs. Ideally, it should have different features — such as an Accounts Payable tab, an Accounts Receivable tab, other expenses, and a tab of charts for a visual representation.
But the main sections of a bookkeeping template should be the chart of accounts, the list of transactions, and the income statement.
You should also make sure that the bookkeeping template can be edited so that you can customize it to your liking.
2. Tracking Your Invoices
You want to create a separate tab for tracking your invoices. In this tab, you want to create a column that has the invoice number or other form of reference.
Be sure to include the date that you sent the invoice as well as the date you received the payment for the invoice. And of course, make sure that you include the amount paid and include a column where you can write what the invoice is for.
Ideally, this invoice tab should be placed next to your list of transactions tab.
3. Cash Flow
You should also create one tab that records your cash flow. The quickest way to do this is to download a template from the Internet. If you prefer, you can make one from scratch.
In this cash flow tab, you will display how much money your company has. This will be a calculation based on income received and income taken account. You can also create a second cash flow tab that shows a chart of how much your company’s cash flow is.
The tab would also show a visual representation of all the income received and a separate representation of the income paid out.
4. Adding Transactions
Once your bookkeeping template is set up, you are ready to start.
The first step is to add transactions to your spreadsheet. Make sure you familiarize yourself with the basic mathematical functions in Excel, such as how to convert numbers to Excel.
When adding your transactions, make sure that you type in the exact dollar amount. Your transactions have to include money that has come in and money that has been sent out.
Every time your company receives or sends a payment, make sure you record it as follows:
- Enter the date of the transaction
- Put the dollar amount in full; add a “-” sign if this was a sent payment
- Choose the category of the transaction (i.e., what was this payment made for?)
- Write a short description of the transaction (i.e., “payment to the Social Media Manager”)
If your cash flow tab is set up to collect data from your transactions tab, then you will see the visual representation of your cash flow getting automatically updated.
Additional Skills to Know
Once you have managed the basics of bookkeeping with Excel, here are some crucial additional skills to know:
1. Formatting
You should know the various keyboard shortcuts to format data in a cell. For example, there are several shortcut keys that every Excel expert should know.
If you hold the Ctrl and Shift key simultaneously, you can perform a number of tasks if you combine it with a numerical value. For example, Ctrl+Shift+1 formats the number with two decimal places. So, if you receive $1,000 to your company, you can highlight the cell and press Ctrl+Shift+1 to display $1000.00.
2. Creating a Sparkline Chart
Sometimes, you want to compare how your earnings and spending have been over a period of time.
To do this, you arrange the data side by side. You can look at each cell to see how one time period compares with another. But this may not be enough. Sometimes you want a visual representation.
You can add a Sparkline Chart in an empty cell next to your compared data. Simply select your range of data (they have to be numerical) and then navigate to the Insert menu. If you choose the ‘line’ chart option, a visual representation of the growth/loss of your numerical data will be created.
3. Using Data Validation
As we mentioned previously when inputting your transactions you’ll want to write the category of the transaction. It can be very time consuming to manually type a category. The best way is to select the category from a pre-created drop-down list.
To do this, simply highlight a range of cells in a column. Then go to the Data menu and choose ‘Data Validation.’ You will open a dialog box where you can type your categories — separated by a comma.
Once you click OK, you will notice an arrow on all of the highlighted columns. If you click on the arrow, a drop-down list with your categories will appear. Now, instead of clicking, all you have to do is select the category of your choice.
You can copy and paste the cell into additional cells once you fill-up the highlighted ones.
Keep It in the Books
Now that you know the fundamentals of Excel accounting, you are ready to take your bookkeeping to the next level.
We encourage you to continue learning about Microsoft Excel so that you can optimize your bookkeeping for your business.
Be sure to read more great content on business and professional development on our site.
