How To Make A Homemade Check Register Folder

Download Article

Information technology's convenient to record your checking business relationship action with an electronic check annals y'all create using Microsoft Excel. You can customize your bank check annals to categorize expenses and so yous tin continue runway of where your money goes, and create a formula that keeps a running balance of how much coin is in your account. Non only is this a useful project, but it is also simple plenty that beginners can apply information technology to acquire some basics of how to utilise Excel.

i
Become familiar with Excel. Excel is a spreadsheet software program that allows users to manage data and perform calculations with formulas. Excel spreadsheets are organized past rows and columns.
- You can install a desktop version of Excel on your Mac or PC. Or, you lot can utilise a free online app for your smartphone, tablet or computer. Each of these versions functions a picayune bit differently.^[i]
- Excel has been in existence for many years, and every year or so, Microsoft releases a new, updated version. Each of these versions besides works a little bit differently.^[two]
- In Excel, rows run horizontally and are ordered by number. Columns run vertically and are ordered by letter of the alphabet. Each box where a row and column intersect is called a cell. The prison cell is named according to its row and cavalcade. For example, a cell in the fifth row of column D is named D5 (cavalcade D, row 5).^[3]
- Empathize the divergence between a worksheet and a workbook in Excel. A worksheet is a unmarried spreadsheet. A workbook is an Excel file that contains 1 or more than spreadsheets. Each worksheet in an Excel workbook is in a dissever tab. Past default, new Excel workbooks accept three tabs. You tin can add more if needed.
- To brand a basic cheque annals, you only use one tab.
- To create a part for categorizing expenses in a check register, you lot will use two tabs.
2
Open up a bare Excel spreadsheet. How yous open the spreadsheet differs depending on the type of computer and operating system you are using.
- On a PC with an operating system older than Windows seven, begin by clicking on the start card in the lower left hand corner of your desktop. From the popup card, click on Programs. From the next popup menu, click on Microsoft Office. Then click on Microsoft Office Excel. At this point you will take a blank excel spreadsheet on your desktop.^[4]
- On a PC with Windows 7 or newer, click on the Windows icon in the lower left hand corner of your screen. Then click on the Microsoft Excel tile to open Excel. You volition see a folio with your contempo files listed on the left and template options for new documents on the right. Click on the first template option, Blank Workbook, to open a new Excel spreadsheet.
- To open Excel on a Mac, click on Excel in the dock. Click "File" in the bill of fare bar. Click "Open" and and then click "New." This will bring you lot to a new, bare spreadsheet.
Advertizement
3
Create column labels. Use labels that you would find in an ordinary paper check book register. Create columns for the date, check number, the payee and a description or memo. After that, create columns for debits (which are payments or withdrawals), credits (which are deposits) and the balance frontward.^[5]
- Enter the word "DATE" in cell B1 (column B, row 1). This is where you lot will enter the date of the transaction.
- Move i jail cell to the correct to cell C1 (column C, row ane). Type "Particular #". Here you will enter the check number or the blazon of transaction, such as "ATM" or "eolith."
- Move one cell to the right to prison cell D1 (column D, row 1). Type "PAYEE." This is the person to whom a cheque was made payable or to whom money was paid.
- Move ane cell to the right to jail cell E1 (cavalcade Eastward, row 1). Type "DESCRIPTION." Tape any details y'all want to recall about the transaction.
- Movement one cell to the right to cell F1 (column F, row one). Blazon "DEBIT." This is where you record money that leaves your account, likewise known as outflow.
- Move i prison cell to the right to cell G1 (column G, row 1). Type "EXPENSE CATEGORY." You will exit this blank for now. Options for this cavalcade will exist created in a later stride.
- Move one cell to the right to prison cell H1 (column H, row 1). Type "CREDIT." This is where yous tape money coming into your account, also known as inflow.
- Move 1 cell to the correct to cell J1 (cavalcade J, row 1). Type "INCOME CATEGORY." Every bit with the expense category column, options for this column volition be created in a subsequently footstep.
- Move one cell to the correct to cell K1 (column K, row 1). Type "Residuum." This is the electric current amount in your account afterward all transactions accept been recorded.
iv
Format cavalcade labels. To make the column labels easy to read, format them so they are in bold font and give the row of register labels a dissimilar groundwork color. To do this, first yous select the range of cells that you desire to format. And so you lot select the formatting options.^[half-dozen]
- Find the formatting options in the "HOME" tab of the toolbar ribbon. When you open up a new workbook, the "Dwelling" tab is open up by default.^[7]
- Select cell B1 (Date) and drag the cursor across all of the labels, through cell K1 (Remainder).
- In the upper left hand corner of the toolbar, click on the "B" for the bold formatting option.
- To change the colour of the background, click on the paint bucket icon to run into the palette from which you tin select your background color.
5
Resize some of the columns. The default size of some of the columns will probable be besides modest to hold the data y'all will put in them. For example, the "PAYEE" and "DESCRIPTION" columns could hold long names or lengthy memos. Also, column A, which has no data and is only a spacer, should be very narrow.^[8]
- Click the header for column A to select the whole cavalcade. In the upper correct hand corner of the toolbar in the "Home" ribbon, click on the "FORMAT" push. From the driblet down menu, click on "Column Width." Enter the number 2 and click on "OK." Now column A is very narrow.
- Widen cavalcade D, "PAYEE." Select column D by clicking on the header. Hover the cursor over the border between columns D and East. The cursor will change from the pointer to the resizing cursor. The resizing cursor looks like a cross with arrows. When you see the resizing cursor, left-click and drag your mouse to the right to make the column as wide equally y'all want it to be.
- Repeat the aforementioned procedure to widen column E, "DESCRIPTION."
6

Center the annals labels. Select the entire showtime row past left-clicking on the number ane on the left border of the page. In the upper left hand side of the toolbar on the "HOME" ribbon, click on the "heart" formatting push. This action centers all of the data in the selected cells. You volition detect that the cavalcade labels are now centered in their cells.^[9]

Advertizement

1
Input some test data. In club to see the formatting changes you lot make, enter four rows of information into the cells. Begin with the opening balance, and so enter iii more transactions.
- In prison cell B2, add a engagement for the opening balance, for instance 9/27/xv. In cell D2, which is the "PAYEE" column, type "Opening Balance." In cell K2, which is the "Rest," blazon the amount of coin you have in your account as of the date you entered in cell B2.
- Add three more transactions. Effort to accept a mix of debits (such as checks written or ATM withdrawals) and credits (such as deposits).
- Notice the inconsistent formatting of the numbers in the cells. The date cavalcade may be formatted to read "9/27/2015" or "27-Sept." The columns in which you entered dollar amounts may have the incorrect number of decimal points. Formatting will clean all of this upwardly.^[10]
2
Format the dates. Make this column display the dates in a consistent style. Excel provides unlike options for formatting the appointment. Select the 1 that you adopt.^[11]
- Click on the header for column B, "Engagement." This selects the entire column.
- Right-click on the column, and select "Format Cells." The "Format Cells" window will appear.
- Select the "Number" tab. Under "Category" choose "Date." Select the format you want for the date and click on "OK" in the lesser right corner of the window.
- While this column is still highlighted, center the data in these cells by clicking on the "middle" icon in the upper left manus corner of the toolbar in the "Domicile" ribbon.
3
Format the "ITEM #" cavalcade. The data in this column should be centered. Highlight all of column C by clicking on the header for the column. Click on the "eye" icon. Notice the test data you entered in this column. It should be centered in the cells.^[12]
- Check the formatting for columns D and E, "PAYEE" and "Clarification." By default, Excel formats cells so that the data is aligned to the left. This should work well for these columns. Double check the sizing of the columns. At present that you take some data in these cells, adjust the column width to make the columns wider or narrower if necessary.^[thirteen]
4
Format the currency in columns F, H and Chiliad, "DEBIT," "CREDIT" and "Residuum Frontward." The currency should have 2 decimal points. Yous can choose to show the dollar sign if you desire. You can likewise make your debits appear in red font if you desire.
- Select column F. Right-click on the column and select "Format Cells." The "Format Cells" window will appear. In the "Number" tab, select "Accounting. " Choose "two" in the "Decimal places" selection. Select the dollar sign in the "Symbol" selection.
- Repeat for columns H and K.
- To brand your debits appear cherry, click on the header for column F to select the entire column. Right click on the column and select "Format Cells." When the "Format Cells" window appears, select the "Font" tab. In this tab, click the downwardly arrow adjacent to the "Colour" option. From the palette, click on cherry-red.

Advertising

1
Create a formula to calculate a running balance. Insert a formula into column Thou that does the math to calculate the running balance. Note that you did not need a formula in cell K2. This is where you lot entered your opening balance.
- Click on cell K3. Now click on the formula bar at the acme of the spreadsheet. This is where you volition blazon in the formula that tells the cells to perform a calculation. Type the formula =SUM(K2-F3+H3). This tells the spreadsheet to have our opening residue (cell K2), and subtract a debit if ane exists (jail cell F3) and add a credit if one exists (cell H3).^[xiv]
- Suppose your opening residue was $200, and your first entry was a bank check you lot wrote for $35.00. The $35.00 is recorded every bit a debit in cell F3. The formula you entered in cell H3 takes the opening balance and subtracts the debit, leaving you with a residue of $165.00.
two

Re-create the formula. Select cell K3. Right-click and select "Copy." Select cells K4 and K5. Correct-click and select "Paste." The formula is now copied into these cells. You should see that the running rest has been calculated in column Yard for all the rows of test data that you entered.
3
Create a provisional formula to clean upwards the running residuum column. Y'all could re-create the above formula into cell K6. But since you lot haven't entered any data in this row, the running balance from jail cell K5 will besides announced in cell K6. To clean this up, create a provisional formula that leaves the cell bare if no transactions take been entered, but displays a balance if they have been entered.
- In jail cell K6, enter the formula =IF(ISBLANK(B6),"",SUM(K5-F6+H6)). This tells Excel that if cell B6, in the "DATE" column, is blank, and so prison cell H6 should be blank. But if cell B6 is not blank, and so the balance should be calculated.^[fifteen]
4
Extend the formula with AutoFill. The AutoFill automatically fills in formulas in adjacent cells then you won't accept to enter the "BALANCE" formula over and once again.^[xvi]
- Detect the AutoFill handle in the active cell. Observe the pocket-size, night foursquare in the bottom correct corner of the agile cell. Hover over it and the cursor will modify to the AutoFill cursor, which looks like a sparse plus sign.^[17]
- Click on prison cell K6. Hover over the AutoFill handle and the cursor will modify to the thin plus sign. Left-click and hold the AutoFill handle. Drag the cursor down to cell K100 (column K, row 100).
- The formula has now been copied into all of the cells in cavalcade Yard through row 100. The row and column numbers in each cell take been automatically adjusted so the formula calculates correctly.^[18]

1

Categorize transactions. Create categories of transactions to proceed runway of how you lot spend your money and the types of income. Categories could be related to income taxes, such as holding taxes or charitable giving. You lot could also use the categories to create a charts to easily visualize financial activity in your account.
2
Create a "Categories" tab. This is where y'all volition shop all of the potential income and expense categories for your bank check annals. Rename one of the tabs in your workbook "Categories." Double click on the current title of the tab to highlight the name. The current name will be something like "sheet2" or "sheet3." When the name of the canvas is highlighted, you can type in the new proper name of the tab. Blazon "Categories." .^[19]
- In cell B4, type the word "Categories." Format the prison cell to take bold font and alter the alignment to eye.
iii
Create income categories. In cell B5, blazon "*** Income ***". Recollect of all of the categories of income y'all take or may utilize in the futurity. In prison cell B6 and continuing downward, enter all of your income categories.
- The most mutual income category would exist "Wages." You might need multiple wage categories if you have more ane task.
- Other income categories you lot might want to include depend on your financial circumstances. If you ain stock, create a category for "Dividends." If you receive kid support, create a category for that. Other categories to add include "Interest Income," "Gifts" and "Miscellaneous."
4
Create expense categories. Go out a cell blank nether your final income category. Motility one prison cell down and type "*** Expenses ***". Enter all of the expense categories under this section heading.
- Be every bit wide or as narrow as you wish with your expense categories. Expense categories might include "Mortgage," "Hire," "Insurance," "Car Payment," "Gas," "Electricity," "Phone" and "Amusement."
five

Name the range of cells containing your categories. Select cell B5. Highlight from cell B5 all the way down through all of your income and expense categories. Find the cell name box in the upper left hand corner of the window. It is to the left of the formula box. Information technology volition say "B5," which is the proper name of the first prison cell in the highlighted range. Click on the prison cell name box and blazon "Categories." This names the range of cells and then you tin can use it in your bank check register.^[twenty]
half-dozen
Use the expense and income categories in the check register. Get back to the tab where you created the check register. You will now add drop down menus to the "EXPENSE CATEGORY" and "INCOME CATEGORY" columns you created.
- In the check annals tab, select cell G2. This is the first jail cell in the "EXPENSE CATEGORY" column.
- On the toolbar, select the "DATA" ribbon. Click on the "Data Validation" button. Select "Data Validation" from the drop down menu. This will open the "Information Validation" window.^[21]
- In the "Settings" tab of the "Data Validation" window, look for the "Allow" drop down box. Click on the down arrow and select "List." Nether "Source" enter "=Categories." Click OK.^[22]
- You will now run across a little arrow next to cell G2. Click on the arrow to see the list of categories. Click on the advisable category for the transaction in that row.^[23]
- Using AutoFill, re-create the formula from cell G2 all the way down to cell G100.
- Get to prison cell J2 to repeat the process in the "INCOME CATEGORY" column.

Advertizing

i

Lock the cells with formulas and protect the worksheet. Protecting the worksheet means that data in locked cells cannot be overwritten. This fashion you lot don't have to worry about the balance forward being incorrectly calculated because the formula was accidentally changed. You lot tin can besides create a password to further protect your bank check register from other users.^[24] Just brand sure information technology'southward something that is easy for you to remember, or you write it in a secure place; if you lot forget the password you volition exist unable to access the worksheet.
two
Unlock cells. By default, in one case a worksheet is protected, all of the cells go locked. So to begin, you need to unlock the cells into which information will entered even subsequently the worksheet is protected.^[25]
- Select cells B2 through J100. This is all of the cells in all of the columns of the check register except the concluding column, 1000, which is the "BALANCE" cavalcade. Y'all are going to need to be able to enter data in these cells even after the worksheet is protected.
- Right-click inside the selected range of cells. Select "Format Cells."
- In the "Format Cells" window, select the "Protection" tab. Uncheck the box that says "Locked" and click OK.
3
Activate "Protection" for the worksheet. Once "Protection" is activated, this means that all cells which remained locked, including the cells in column K, "BALANCE," cannot be overwritten.^[26]
- Go to the "REVIEW" ribbon on the toolbar. Click on "Protect Sail." The "Protect Canvass" window will appear.
- If you want to password protect your worksheet, add together it here. If non, go out this field blank.
- Click on OK. Your bank check register is no protected.
4

Unprotect your worksheet to alter locked cells. If you decide to change formulas in locked cells, yous tin can reverse this procedure. Go to the "REVIEW" ribbon on the toolbar. Click on "Unprotect Canvass." The "Unprotect Sheet" window will appear. Click on OK.^[27]

Add New Question

Question

How practice I convert a check ledger to a Summary Report by either payee or category?

Darron Kendrick is an Adjunct Professor of Accounting and Constabulary at the Academy of North Georgia. He received his Masters degree in taxation police from the Thomas Jefferson School of Constabulary in 2012, and his CPA from the Alabama State Board of Public Accountancy in 1984.

Financial Advisor

Proficient Answer

Support wikiHow by unlocking this expert answer.

This could be washed using the Filter option on the tool bar. The filter will allow you to select the range you wish to report on, set the parameters, and so it will render all of the cells as designated. This grouping may then exist copied into a report format.

Ask a Question

200 characters left

Include your email accost to get a message when this question is answered.

Submit

Advert

Video

About This Article

Article Summary X

To create a simple checkbook annals with Microsoft Excel, open a blank spreadsheet and create cavalcade labels that would exist in a newspaper register, like Date, Check Number, and Payee. Then, format the cavalcade headings so that they are bold and piece of cake to read, and adjust the cell formatting to account for currency and dates. Next, create a formula in the Remainder column to subtract a debit or add a credit from the opening balance. Then, copy that formula into each prison cell in that cavalcade to keep a running balance. To learn more from our Auditor co-writer, like how to password-protect your register, keep reading the article!

Did this summary help you lot?

Thank you to all authors for creating a page that has been read 569,731 times.