It's easy to record your checking account activity with an electronic check register.If you want to keep track of where your money goes, you can create a formula that keeps a running balance of how much money is in your account.Not only is this a useful project, but it is also simple enough for beginners to learn how to use excel.
Step 1: Get familiar with the program.
Users of excel can manage data and perform calculations with formulas.There are rows and columns in excel spreadsheets.You can use a desktop version of excel.You can use a free online app.Each version has a different function a little bit.Every year or so, Microsoft releases a new, updated version of excel, which has been in existence for many years.The versions work a little differently.Rows are ordered by number in excelColumns are ordered by letter.A cell is a box where a row and column intersect.The cell's row and column are named.D5 is a cell in the fifth row of column D.Understand the difference between a sheet of paper and a spreadsheet.There is a single spreadsheet.A spreadsheet is an excel file.There is a separate tab for each worksheet.By default, the new workbooks have three tabs.If needed, you can add more.Only one tab is used to make a basic check register.You will use two tabs to create a function for categorizing expenses.
Step 2: A blank spreadsheet can be opened.
The way you open the spreadsheet can be different depending on your computer and operating system.Click on the start menu in the lower left hand corner of your desktop if you have an operating system older than Windows 7.Click on Programs from the popup menu.Click on Microsoft Office from the next popup menu.Click on Microsoft Office.You will have a blank spreadsheet on your desktop at this point.Click on the Windows icon in the lower left hand corner of the screen on a PC with Windows 7 or newer.Click on the Microsoft excel tile to open it.You will see a page with your recent files listed on the left and template options for new documents in the right.To open a new spreadsheet, click on the first template option.Click on the excel in the dock to open it on a Mac.The menu bar has a file button.Click "open" and then click "new" to get a blank spreadsheet.
Step 3: You can create column labels.
You can use the ordinary paper check book register labels.Columns for the date, check number, payee and description or memo can be created.Columns for payments, credits, and deposits should be created after that.The word was entered in cell B1 (column B, row 1).The date is where you will enter it.Move one cell to the right."ITEM #" is what you should type.Move one cell to the right to see the type of transaction you entered.This is the person who made the check payable or who paid the money.Move one cell to the right.If you want to remember the transaction, typeDESCRIPTION.Move one cell to the right.This is where you record the money that leaves your account.Move one cell to the right.You will leave this blank for now.The options for this column will be created later.Move one cell to the right.This is where you record money coming into your account.Move one cell to the right.As with the expense category column, options for this column will be created in a later step.Move one cell to the right.After all transactions have been recorded, this is the current amount in your account.
Step 4: Column labels are formatted.
To make the column labels easy to read, format them so they are in bold type and give the row of register labels a different background color.Select the range of cells you want to format first.Then you choose the options.You can find the options in the home tab of the ribbon.The home tab is open when you open a new workbook.Select cell B1 and drag the cursor across all of the labels.The bold formatting option is available in the upper left hand corner.To change the color of the background, click on the paint bucket icon to see the palette from which you can choose.
Step 5: Resize some of the columns.
Some of the columns will be too small to hold the data you will put in them.The "PAYEE" and "DESCRIPTION" columns could hold long names.column A, which has no data, should be very small.The whole column can be selected by clicking the headers for column A.Click on theFORMAT button in the upper right corner of the home ribbon.Enter the number 2 and click on "OK" to narrow column A.Click on column D to select it.The border is between columns D and E.A cross with arrows is what the resizing cursor looks like.Left-click and drag your mouse to the right to make the column as wide as you want it to be.To widen column E, repeat the procedure.
Step 6: The register labels should be centered.
Left-clicking on the number 1 in the left border of the page will allow you to select the entire first row.Click on the center button in the upper left hand side of the home ribbon.All of the data is in the selected cells.The column labels are now centered in their cells.
Step 7: Test data to input.
To see the changes you make, enter four rows of data into the cells.If you want to enter more transactions, begin with the opening balance.For example, add a date for the opening balance in cell B2.In cell B2, type the amount of money you have in your account as of the day you entered it.Add three more transactions.If you can, try to have a mix of both debits and credits.The numbers are formatted in different ways in the cells.The date column can be formatted to read 9/27/2015 or 27/09/2018.All of this will be cleaned up with the help of Formatting.
Step 8: The dates should be formatted.
The dates should be displayed in a consistent way.There are different ways to format the date in excel.You can choose the one that you prefer.The entire column can be selected by clicking on the headers for column B.Click on the column to see theFormat Cells window.The number tab is selected.Select the format you want for the date and click on "OK" in the bottom right corner of the window.Clicking on the center icon in the upper left hand corner of the toolbar will center the data in these cells.
Step 9: The "ITEM #" column should be formatted.
The data should be centered.If you want to highlight all of column C, click on the heading.Click on the center icon.You entered test data in this column.It should be in the center of the cells.If you want the data to be aligned to the left, check the formatting for columns D and E.This will work well for these columns.Double check the size of the columns.If you need to make the columns narrower or wider, adjust the column width.
Step 10: The currency should be formatted in columns F, H and K.
The currency should have 2 digits.If you want, you can show the dollar sign.If you want, you can make your debits appear in red.The "Format Cells" window will appear when you click on column F.Select "Accounting" in the "number" tab.There is a choice of 2 in the "Decimal places" option.The dollar sign can be selected in theSymbol option.Select the entire column if you want your debits to appear red.Click on the column and select "Format Cells." When the window appears, select the "Font" tab.Next to the color option, click the down arrow.Click on red.
Step 11: To calculate a running balance, create a formula.
If you want to calculate the running balance, insert a formula into column K.You didn't need a formula in cell K2.You entered your opening balance here.Click on cell K3.The formula bar is at the top of the spreadsheet.The formula that tells the cells to perform a calculation is located here.The formula isSUM(K2-F3+H3).If one exists, subtract a debit from the opening balance and add a credit to the spreadsheet.If you had an opening balance of $200, your first entry would be a check for $35.00.The $35.00 is recorded in cell F3.The formula you entered in cell H3 subtracts the opening balance and leaves you with a balance of $165.
Step 12: The formula can be copied.
Select cell K3.Select cells K4 and K5.Click and copy the formula into the cells.The running balance has been calculated for all the rows of test data that you entered.
Step 13: There is a running balance column.
The above formula can be copied into cell K6.Since you haven't entered any data in this row, the running balance from cell K5 will also show up.If no transactions have been entered, create a formula that leaves the cell blank, but shows a balance.In cell K6, enter the formula.If cell B6 is blank, then cell H6 should be blank.The balance should be calculated if cell B6 is blank.
Step 14: The formula can be extended with AutoFill.
You won't have to enter the "BALANCE" formula over and over again because the AutoFill automatically fills in formulas in adjacent cells.The AutoFill handle can be found in the active cell.The active cell has a small dark square in the bottom right corner.The AutoFill cursor looks like a thin plus sign if you hover over it.Click on cell K6.The thin plus sign appears when you hover over the AutoFill handle.Click and hold the handle.The cell K 100 has column K and row 100.The formula has been copied into all of the cells.The formula calculates correctly if the row and column numbers are adjusted.
Step 15: The transactions should be categorized.
To keep track of how you spend your money, create categories of transactions.There are categories related to income taxes.To visualize financial activity in your account, you could use the categories.
Step 16: There is aCategories tab.
This is where you will keep your potential income and expense categories.Click on the current title of the tab to highlight the name.When the name of the sheet is highlighted, you can type in a new name for the tab.You can typeCategories.To format the cell, type the wordCategories in cell B4 and change the alignment to center.
Step 17: You can create income categories.
In cell B5 you can type income.Think of all the different types of income you may have in the future.All of your income categories can be entered in cell B6.If you have more than one job, you might need multiple wage categories.Depending on your financial circumstances, you might want to include other income categories.If you receive child support, create a category for that.Other categories include interest income, gifts, andMiscellaneous.
Step 18: You can create expense categories.
Under your last income category, leave a cell blank.Put one cell down and type in expenses.Under this section heading, enter all of the expense categories.You can choose to be broad or narrow with your expense categories.Expense categories could include Mortgage, Rent, Insurance, Car Payment, Gas, Electricity, and Phone.
Step 19: The range of cells contain your categories.
Cell B5 should be selected.You can highlight from cell B5 all the way down.The cell name box can be found in the upper left corner of the window.The formula box is to the left.B5 is the name of the first cell in the highlighted range.You can use the range of cells in your check register by clicking on the cell name box.
Step 20: The check register has the expense and income categories.
The check register was created in the tab.Drop down menus will be added to the Exploration and Income columns.Go to the check register tab and select cell G2.This is the first cell in the exploration column.The ribbon is called the "DATA"Click on the button.From the drop down menu, select data validation.This will open the data validation window.Look for the Allow drop down box in the settings tab of the data validation window.You will see an arrow next to cell G2 if you click on the down arrow.You can see the list of categories by clicking on the arrow.Click on the appropriate category for the transaction.If you want to copy the formula from cell G2 to G 100, use AutoFill.Go to cell J2 to repeat the process.
Step 21: The cells should be locked and protected with formulas.
Data in locked cells cannot be changed.You don't have to worry about the balance forward being incorrect because the formula was accidentally changed.Passwords can be created to protect your check register from other users.If you forget the password, you won't be able to access the worksheet, so make sure it's easy for you to remember, or write it in a secure place.
Step 22: The cells can be unlocked.
All of the cells become locked when a worksheet is protected.To begin, you need to unlocked the cells in which the data will be entered.Select cells B2 and J 100.This is all of the cells in the check register except the last column, K.You will need to be able to enter data in these cells after the worksheet is protected.Click inside the range of cells.The "Protection" tab can be found in the "Format Cells" window.Click OK if the box says "locked"
Step 23: There is a protection for the worksheet.
The cells in column K can't be overwritten if protection is activated.Go to the preview ribbon.The Protect Sheet window will appear if you click on it.If you want to password protect, add it here.Leave this field blank if not.Click on it.The check register is not protected.
Step 24: To change locked cells, unprotect your worksheet.
You can reverse this process if you change formulas in locked cells.Go to the preview ribbon.The "Unprotect Sheet" window will appear if you click on it.Click on it.