You can install a desktop version of Excel on your Mac or PC. Or, you can use a free online app for your smartphone, tablet or computer. Each of these versions functions a little bit differently. [1] X Research source Excel has been in existence for many years, and every year or so, Microsoft releases a new, updated version. Each of these versions also works a little bit differently. [2] X Research source In Excel, rows run horizontally and are ordered by number. Columns run vertically and are ordered by letter. Each box where a row and 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 is named D5 (column D, row 5). [3] X Research source Understand the difference between a worksheet and a workbook in Excel. A worksheet is a single spreadsheet. A workbook is an Excel file that contains one or more spreadsheets. Each worksheet in an Excel workbook is in a separate tab. By default, new Excel workbooks have three tabs. You can add more if needed. To make a basic check register, you only use one tab. To create a function for categorizing expenses in a check register, you will use two tabs.
On a PC with an operating system older than Windows 7, begin by clicking on the start menu in the lower left hand corner of your desktop. From the popup menu, click on Programs. From the next popup menu, click on Microsoft Office. Then click on Microsoft Office Excel. At this point you will have a blank excel spreadsheet on your desktop. [4] X Research source 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 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 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 menu bar. Click “Open” and then click “New. ” This will bring you to a new, blank spreadsheet.
Enter the word “DATE” in cell B1 (column B, row 1). This is where you will enter the date of the transaction. Move one cell to the right to cell C1 (column C, row 1). Type “ITEM #”. Here you will enter the check number or the type of transaction, such as “ATM” or “deposit. ” Move one cell to the right to cell D1 (column D, row 1). Type “PAYEE. ” This is the person to whom a check was made payable or to whom money was paid. Move one cell to the right to cell E1 (column E, row 1). Type “DESCRIPTION. ” Record any details you want to remember about the transaction. Move one cell to the right to cell F1 (column F, row 1). Type “DEBIT. ” This is where you record money that leaves your account, also known as outflow. Move one cell to the right to cell G1 (column G, row 1). Type “EXPENSE CATEGORY. ” You will leave this blank for now. Options for this column will be created in a later step. Move one cell to the right to cell H1 (column H, row 1). Type “CREDIT. ” This is where you record money coming into your account, also known as inflow. Move one cell to the right to cell J1 (column J, row 1). Type “INCOME CATEGORY. ” As with the expense category column, options for this column will be created in a later step. Move one cell to the right to cell K1 (column K, row 1). Type “BALANCE. ” This is the current amount in your account after all transactions have been recorded.
Find the formatting options in the “HOME” tab of the toolbar ribbon. When you open a new workbook, the “HOME” tab is open by default. [7] X Research source Select cell B1 (DATE) and drag the cursor across all of the labels, through cell K1 (BALANCE). In the upper left hand corner of the toolbar, click on the “B” for the bold formatting option. To change the color of the background, click on the paint bucket icon to see the palette from which you can select your background color.
Click the header for column A to select the whole column. In the upper right hand corner of the toolbar in the “HOME” ribbon, click on the “FORMAT” button. From the drop down menu, click on “Column Width. ” Enter the number 2 and click on “OK. ” Now column A is very narrow. Widen column D, “PAYEE. ” Select column D by clicking on the header. Hover the cursor over the border between columns D and E. The cursor will change from the arrow 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 as you want it to be. Repeat the same procedure to widen column E, “DESCRIPTION. ”
In cell B2, add a date for the opening balance, for example 9/27/15. In cell D2, which is the “PAYEE” column, type “Opening Balance. ” In cell K2, which is the “BALANCE,” type the amount of money you have in your account as of the date you entered in cell B2. Add three more transactions. Try to have 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 column may be formatted to read “9/27/2015” or “27-Sept. ” The columns in which you entered dollar amounts may have the wrong number of decimal points. Formatting will clean all of this up.
Click on the header for column B, “DATE. ” 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 desire for the date and click on “OK” in the bottom right corner of the window. While this column is still highlighted, center the data in these cells by clicking on the “center” icon in the upper left hand corner of the toolbar in the “HOME” ribbon.
Check the formatting for columns D and E, “PAYEE” and “DESCRIPTION. ” 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. Now that you have some data in these cells, adjust the column width to make the columns wider or narrower if necessary.
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 “2” in the “Decimal places” option. Select the dollar sign in the “Symbol” option. Repeat for columns H and K. To make your debits appear red, 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 down arrow next to the “Color” option. From the palette, click on red.
Click on cell K3. Now click on the formula bar at the top of the spreadsheet. This is where you will type in the formula that tells the cells to perform a calculation. Type the formula =SUM(K2-F3+H3). This tells the spreadsheet to take our opening balance (cell K2), and subtract a debit if one exists (cell F3) and add a credit if one exists (cell H3). [10] X Research source Suppose your opening balance was $200, and your first entry was a check you wrote for $35. 00. The $35. 00 is recorded as a debit in cell F3. The formula you entered in cell H3 takes the opening balance and subtracts the debit, leaving you with a balance of $165. 00.
In 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, then cell H6 should be blank. But if cell B6 is not blank, then the balance should be calculated. [11] X Research source
Find the AutoFill handle in the active cell. Observe the small, dark square in the bottom right corner of the active cell. Hover over it and the cursor will change to the AutoFill cursor, which looks like a thin plus sign. [13] X Research source Click on cell K6. Hover over the AutoFill handle and the cursor will change 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 column K through row 100. The row and column numbers in each cell have been automatically adjusted so the formula calculates correctly. [14] X Research source
In cell B4, type the word “Categories. ” Format the cell to have bold font and change the alignment to center.
The most common income category would be “Wages. ” You might need multiple wage categories if you have more than one job. Other income categories you might want to include depend on your financial circumstances. If you own stock, create a category for “Dividends. ” If you receive child support, create a category for that. Other categories to add include “Interest Income,” “Gifts” and “Miscellaneous. ”
Be as broad or as narrow as you wish with your expense categories. Expense categories might include “Mortgage,” “Rent,” “Insurance,” “Car Payment,” “Gas,” “Electricity,” “Phone” and “Entertainment. ”
In the check register tab, select cell G2. This is the first 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 “Data Validation” window. [17] X Research source In the “Settings” tab of the “Data Validation” window, look for the “Allow” drop down box. Click on the down arrow and select “List. ” Under “Source” enter “=Categories. ” Click OK. [18] X Research source You will now see a little arrow next to cell G2. Click on the arrow to see the list of categories. Click on the appropriate category for the transaction in that row. [19] X Research source Using AutoFill, copy the formula from cell G2 all the way down to cell G100. Go to cell J2 to repeat the process in the “INCOME CATEGORY” column.
Select cells B2 through J100. This is all of the cells in all of the columns of the check register except the last column, K, which is the “BALANCE” column. You 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.
Go to the “REVIEW” ribbon on the toolbar. Click on “Protect Sheet. ” The “Protect Sheet” window will appear. If you want to password protect your worksheet, add it here. If not, leave this field blank. Click on OK. Your check register is no protected.
