How to create a simple control register with Microsoft Excel

It is useful to record your checking account activity in an electronic verification register that you can create using Microsoft Excel. You can customize your audit log to categorize your spending so you can track where your money is going and create a formula that calculates your current account balance. It is not only a useful project, but also simple enough that beginners can use it to learn some basics of using Excel.

Steps

Part 1 of 5: create the columns

Create a Simple Checkbook Register With Microsoft Excel Step 1

Step 1. Familiarize yourself with Excel

Excel is spreadsheet software that allows users to manage data and perform calculations using formulas. Excel spreadsheets are organized by rows and columns.

You can install a desktop version of Excel on your Mac or PC. You can also use a free online application for your smartphone, tablet or computer. Each of these versions works a little differently.
Excel has been around for many years, and every year or so, Microsoft releases a new, updated version. Each of these versions also works a little differently.
In Excel, rows run horizontally and are numbered. Columns run vertically and are indicated by a letter. Each box where a row and a column intersect is called a cell. The cell is named according to its row and column. For example, a cell in the fifth row of column D will be called D5 (column D, row 5).
Know the difference between a worksheet and a workbook in Excel. A worksheet is a single worksheet. A workbook is an Excel file that contains one or more worksheets. Each worksheet in an Excel workbook is on a separate tab. By default, new Excel workbooks have three tabs. You can add more if you want.
To create a basic audit log, you will use a single tab.
To create a function to categorize expenses in a control ledger, you will use two tabs.

Create a Simple Checkbook Register With Microsoft Excel Step 2

Step 2. Open a blank Excel spreadsheet

The way you open the spreadsheet differs depending on the type of computers and the operating system you are using.

On a PC with an operating system older than Windows 7, start by clicking on the Start menu in the lower left corner of your desktop. On the menu, click Programs, and then click Microsoft Office. Then click on Microsoft Office Excel. At this point you will have a blank excel spreadsheet open in your desktop.
On a PC with Windows 7 or later, click the Windows icon in the lower left corner of your screen. Then click on the Microsoft Excel layer to open Excel. You will see a page with your recent files listed on the left and template options for new documents on the right. Click on the first template, Blank Worksheet, to open a new Excel worksheet.
To open Excel on a Mac, click Excel in the dock. Click on File in the menu bar. Click "Open", then click New. This will allow you to open a new blank spreadsheet.

Create a Simple Checkbook Register With Microsoft Excel Step 3

Step 3. Create the column captions

Use the captions you would find in a typical accounting ledger. Create columns for date, transaction type, payee, and a description or memo. Next, create columns for Debits (which are payments or withdrawals), Credits (which are deposits), and Balance.

Enter the word "DATE" in cell B1 (column B, row 1). This is where you enter the date of the transaction.
Move your cursor one cell to the right on cell C1 (column C, row 1). Type "TRANSACTION #". Here you enter the check number or type of transaction, such as "ATM" or "deposit".
Move your cursor one cell to the right on cell D1 (column D, row 1). Type "BENEFICIARY". This is the person to whom the check was addressed or who received the transaction.
Move your cursor one cell right to cell E1 (column E, row 1). Type "DESCRIPTION". Record any details you want to mention about this transaction.
Move your cursor one cell to the right on cell F1 (column F, row 1). Type "RATE". This is where you record the money that leaves your account, also known as an exit.
Move your cursor one cell to the right on cell G1 (column G, row 1). Type “CATEGORY OF EXPENDITURE”. You will leave this cell blank for now. The options for this column will be created in a later step.
Move your cursor one cell to the right on cell H1 (column H, row 1). Type "CREDIT". This is where you record the money that goes into your account, also known as an entry.
Move your cursor one cell to the right on cell J1 (column J, row 1). Type "INCOME CATEGORY". As with the expense category column, the options for this column will be created in a later step.
Move your cursor one cell to the right on cell K1 (column K, row 1). Type "BALANCE". This is your current account balance after recording all transactions.

Create a Simple Checkbook Register With Microsoft Excel Step 4

Step 4. Format the column caption

To make captions more readable, format them so that they appear bold and give the caption row in your registry a different background color. To do this, first select the cells you want to format. Then you will select the formatting options.

Find formatting options in the ribbon HOME from the toolbar. When you open a new workbook, the ribbon HOME is open by default.
Select cell B1 (DATE) and drag the cursor over all the captions, up to cell K1 (BALANCE).
In the upper left corner of the toolbar, click B to format your characters in bold.
To change the background color, click on the palette icon from which you can select your background color.

Create a Simple Checkbook Register With Microsoft Excel Step 5

Step 5. Change the size of some of the columns

The default size for some of the columns will probably be too small to accommodate the data you are going to put in them. For example, the "BENEFICIARY" and "DESCRIPTION" columns can contain rather long names or memos. Also, column A, which has no data and only serves to mark a space, should be very narrow.

Click the header of column A to select the entire column. In the upper right corner of the toolbar HOME, Click on the button FORMAT. In the drop-down menu, click Column width. Enter the number 2 and click OK. Column A will now be very narrow.
Expand column D, “BENEFICIARIES”. Select column D by clicking on the header. Hover the cursor over the border between columns D and E. The cursor will move from the arrow to the resize cursor. The resize cursor looks like a cross with arrows. When you see the resize slider appear, left click and drag your mouse to the right to make the column as wide as you want.
Repeat the same procedure to widen column E, "DESCRIPTION".

Create a Simple Checkbook Register With Microsoft Excel Step 6

Step 6. Center the captions for your registry

Select the entire first row by left clicking on the number 1 on the left border of the page. In the upper left corner of the ribbon toolbar HOME, click on the layout button center. This action centralizes all the data in the selected cells. You will notice that the column captions are now centered in their cell

Part 2 of 5: format cells

Create a Simple Checkbook Register With Microsoft Excel Step 7

Step 1. Enter the test data

To see the formatting changes, enter four rows of data in the cells. Start with the opening balance, then enter three more transactions.

In cell B2, add a date for the opening balance, for example 9/27/16. In cell D2, which is the “BENEFICIARY” column, type “opening balance”. In cell K2, which corresponds to "BALANCE", type the amount of money you have in your account on the date of your entry in cell B2.
Add three more transactions. Try to have a mix of debits (such as checks or ATM withdrawals) and credits (such as deposits to your account).
Note the inconsistent formatting of the numbers in the cells. The date column can be formatted to indicate "2016-27-09" or "27-Sep. »Columns in which you have entered amounts in euros may have the wrong number of decimal places. Formatting your document will resolve these issues.

Create a Simple Checkbook Register With Microsoft Excel Step 8

Step 2. Format the dates

Make this column display dates consistently. Excel offers different options for formatting dates. Select the one you prefer

Click on the heading of column B, “DATE”. This will allow you to select the entire column.
Right-click on the column and select Format cells. The window Cell Format should be displayed.
Select the tab Number. Under Category, choose Dated. Select the format you want to apply for the dates and click on OK in the lower right corner of the window.
If this column is still highlighted, center the data in these cells by clicking on the icon center in the upper left corner of the toolbar in the ribbon HOME.

Create a Simple Checkbook Register With Microsoft Excel Step 9

Step 3. Format the “TRANSACTION #” column

The data in this column should be centered. Highlight the entire C column by clicking on the column header. Click on the icon center. Write down the test data you entered in this column. They should now be centered in the cells

Check the formatting of columns D and E, "BENEFICIARY" and "DESCRIPTION". By default, Excel formats cells so that the data is aligned to the left. It should be appropriate for these columns. Check the calibration of the columns. Now that you have data in these cells, adjust the column width to make the columns wider or narrower as needed

Create a Simple Checkbook Register With Microsoft Excel Step 10

Step 4. Format the currency in columns F, H and K, “DEBIT”, “CREDIT” and “BALANCE”

The currency should have 2 decimal places. You can choose to show the euro sign if you wish. You can also make your debits appear in red if you want them to appear on the spreadsheet.

Select column F. Right-click on the column and select Format cells. The window Cell Format will appear on your screen. In the tab Number, select Accounting. Choose 2 in the option Decimals. Select the euro sign in the option Symbol.
Repeat for columns H and K.
To make your debits appear in red, click on the header of column F to select the entire column. Right-click on the column and select Cell Format. When the window Cell Format will appear, select the tab Police. In this tab, click the down arrow next to the option Color. In the palette, select red.

Part 3 of 5: Create the formulas

Create a Simple Checkbook Register With Microsoft Excel Step 11

Step 1. Create a formula to calculate a running balance

Insert a formula in column K that will perform the calculations to determine the current balance. Note that you don't need a formula in cell K2. This is where you will enter your opening balance.

Click on cell K3. Now click on the formula bar at the top of the worksheet. This is where you'll type the formula that will tell cells to perform a calculation. Type the formula = SUM (K2-F3 + H3). This will tell the spreadsheet to take the opening balance (cell K2) and subtract a debit from it if it exists (cell F3) and add a credit if it exists (cell H3).
Suppose your opening balance was $ 200, and your first entry was a check you wrote for $ 35.00. The 35.00 euros will be recorded as a debit in cell F3. The formula you entered in cell H3 will then take the opening balance and subtract the debit from it, leaving you with a balance of $ 165.00.

Create a Simple Checkbook Register With Microsoft Excel Step 12

Step 2. Copy the formula

Select cell K3. Right-click and select To copy. Select cells K4 and K5. Right click and select To stick on. The formula is now copied to these cells. You should see that the running balance has been calculated in column K for all the rows of test data you entered.

Create a Simple Checkbook Register With Microsoft Excel Step 13

Step 3. Create a conditional formula to clean the balance column

You can copy the above formula to cell K6. But since you have not entered any data in this row, the current balance in cell K5 will also appear in cell K6. To avoid this, create a conditional formula that leaves the cell blank if no transaction was entered, but displays a balance if you entered any.

In cell K6, enter the formula = IF (SIBLANC (B6), "", SUM (K5-F6 + H6)). This will tell Excel that if cell B6, in the "DATE" column is blank, cell H6 should be blank. But if cell B6 is not blank, the scale needs to be calculated

Create a Simple Checkbook Register With Microsoft Excel Step 14

Step 4. Expand the formula with autofill

AutoFill automatically fills the formulas in adjacent cells, so you won't have to reenter the "BALANCE" formula.

Find the AutoFill option in the active cell. Observe the small dark square in the lower right corner of the active cell. Hover your cursor over it and it will switch to the autofill cursor, which looks like a thinner sign.
Click on cell K6. Hover your cursor over the AutoFill option and the cursor will change to the thinner sign. Click with the left mouse button and hold the Auto-fill option. Drag the cursor down to cell K100 (column K, row 100).
The formula was copied to all cells in column K up to row 100. The numbers of rows and columns in each cell were automatically adjusted so that the formula was calculated correctly.

Part 4 of 5: Adding categories

Create a Simple Checkbook Register With Microsoft Excel Step 15

Step 1. Categorize your transactions

Create transaction categories to keep track of how you spend your money and types of income. The categories could be related to income tax, such as property taxes or donations to charities. You can also use these categories to create charts that make it easier to visualize your financial activity.

Create a Simple Checkbook Register With Microsoft Excel Step 16

Step 2. Create a “Categories” tab

This is where you will store all of the income and potential expense categories for your ledger. Rename one of the tabs in your workbook "Categories". Double-click the current tab title to highlight it. The current name will logically correspond to "sheet2" or "sheet3". When the sheet name is highlighted, you can enter the new tab name. Type "Categories".

In cell B4, type the word "Categories." Format the cell so the characters are bold and change the alignment so that it is centered

Create a Simple Checkbook Register With Microsoft Excel Step 17

Step 3. Create the income categories

In cell B5, type "*** Income ***". Think about all the categories of income that you have or could have in the future. In cell B6 and continuing down, enter all of your income categories.

The most common income category will be “Salary”. You might need more than one salary category if you have more than one job.
Other income categories you may want to include depend on your financial situation. If you own stocks, create a category for “Dividends”. If you are receiving child support, create a category for it. Other categories to be added include “Interest income”, “Gifts” and “Miscellaneous”.

Create a Simple Checkbook Register With Microsoft Excel Step 18

Step 4. Create categories for your expenses

Leave a cell blank under your last income category. Move your cursor down one cell and type "*** Fee ***". Enter all expense categories under this heading.

Be as specific or vague as you want in your expense categories. Expense categories can include “Mortgage”, “Rental”, “Insurance”, “Car reimbursement”, “Gas”, “Electricity”, “Phone” and “Entertainment”

Create a Simple Checkbook Register With Microsoft Excel Step 19

Step 5. Name the range of cells containing your categories

Select cell B5. Highlight from cell B5 and then all of your income and expense categories. Find the cell name box in the upper left corner of the window. It is located to the left of the formula box. This will be cell B5, which is the first cell in the highlighted range. Click the Cell Name box and type "Categories." This specifies the name of the range of cells so that you can use it in your control register.

Create a Simple Checkbook Register With Microsoft Excel Step 20

Step 6. Use the expense and income categories in the register

Return to the tab where you created your control register. You will now add drop-down menus to the “EXPENDITURE CATEGORY” and “INCOME CATEGORY” columns that you have just created.

On the Monitoring Register tab, select cell G2. This is the first cell in the “CATEGORY OF EXPENDITURE” column.
In the toolbar, select the “DATA” ribbon. Click on the button Data validation. Select Data validation from the drop-down menu. This will open the window Data validation.
In the tab Settings from the window Data validation, find the checkbox To allow. Click the down arrow and select Listing. Under Source Come in = Categories. Click on OK.
You will now see a small arrow next to cell G2. Click on the arrow to see the list of categories displayed. Click on the appropriate category for the transaction in that row.
Using the AutoFill option, copy the formula from cell G2 to cell G100.
Go to cell J2 to repeat the process in the “CATEGORY OF INCOME” column.

Part 5 of 5: Protect Your Control Register

Create a Simple Checkbook Register With Microsoft Excel Step 21

Step 1. Lock cells with formulas and protect worksheet

Protecting the worksheet means that data in locked cells cannot be overwritten. This way, you won't have to worry about the scale being miscalculated anymore because the formula was accidentally changed. You can also create a password to further protect your registry from other users. Make sure it's a code that's easy to remember, or write it down in a safe place if you forget it. If you forget your password, you will not be able to access the spreadsheet.

Create a Simple Checkbook Register With Microsoft Excel Step 22

Step 2. Unlock the cells

By default, after a worksheet is protected, all cells are locked. To start with, you need to unlock the cells that data will enter even after protecting the worksheet.

Select cells from B2 to J100. These are all cells in all columns of the control register, except the last column, K, which is your “BALANCE”. You will need to be able to enter data into these cells even after protecting the worksheet.
Right-click in the selected range of cells. Select Cell Format.
In the window Cell Format, select the tab Protection. Uncheck the box that indicates Locked and click OK.

Create a Simple Checkbook Register With Microsoft Excel Step 23

Step 3. Enable Protection for your spreadsheet

Once the Protection will be enabled, this means that all cells that remain locked, including cells in column K, “BALANCE”, cannot be replaced.

Go to the ribbon REVISION in the toolbar. Click on Leaf protection. The window Protect the sheet should be displayed.
If you want to password protect your spreadsheet, add it here. Otherwise, leave this field blank.
Click on OK. Your control register is now protected.

Create a Simple Checkbook Register With Microsoft Excel Step 24

Step 4. Protect your worksheet to change locked cells

If you decide to change the formulas in the locked cells, you can reverse this process. Go to the ribbon REVISION from the toolbar. Click on Unprotect the sheet. The window Unprotect the sheet will appear on your screen. Click on OK.