How To Register On Microsoft Excel

Download Article

It's convenient to record your checking account activity with an electronic check register you create using Microsoft Excel. Y'all can customize your check register to categorize expenses so you lot tin continue rail of where your money goes, and create a formula that keeps a running balance of how much money is in your account. Not but is this a useful projection, but information technology is also simple enough that beginners tin use it to acquire some basics of how to use Excel.

1
Become familiar with Excel. Excel is a spreadsheet software programme that allows users to manage data and perform calculations with formulas. Excel spreadsheets are organized by rows and columns.
- You can install a desktop version of Excel on your Mac or PC. Or, you tin use a gratis online app for your smartphone, tablet or computer. Each of these versions functions a little chip differently.^[1]
- Excel has been in existence for many years, and every year or then, Microsoft releases a new, updated version. Each of these versions likewise works a lilliputian fleck differently.^[2]
- In Excel, rows run horizontally and are ordered by number. Columns run vertically and are ordered by letter. Each box where a row and cavalcade intersect is called a jail 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 v).^[3]
- Sympathise 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 carve up tab. Past default, new Excel workbooks have 3 tabs. Y'all can add more if needed.
- To make a bones check register, you but use ane tab.
- To create a function for categorizing expenses in a check register, you will use two tabs.
2
Open a bare Excel spreadsheet. How yous open the spreadsheet differs depending on the type of computer and operating organization you are using.
- On a PC with an operating organisation older than Windows seven, 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 carte, click on Microsoft Office. And then click on Microsoft Office Excel. At this point you volition have a blank excel spreadsheet on your desktop.^[iv]
- On a PC with Windows seven or newer, click on the Windows icon in the lower left paw corner of your screen. And so click on the Microsoft Excel tile to open up Excel. Yous will encounter a page with your recent files listed on the left and template options for new documents on the correct. 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 and so click "New." This will bring you to a new, blank spreadsheet.
Advertizement
three
Create column labels. Use labels that you would notice in an ordinary paper bank check book register. Create columns for the appointment, 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 frontwards.^[five]
- Enter the word "Date" in jail cell B1 (column B, row 1). This is where y'all will enter the date of the transaction.
- Motion ane cell to the right to jail cell C1 (column C, row 1). Type "Detail #". Here yous volition enter the bank check number or the blazon of transaction, such as "ATM" or "deposit."
- Motility 1 cell to the right to jail cell D1 (column D, row ane). Blazon "PAYEE." This is the person to whom a bank check was fabricated payable or to whom coin was paid.
- Motion one cell to the right to prison cell E1 (column Due east, row i). Type "Description." Record whatsoever details you desire to think well-nigh the transaction.
- Move one jail cell to the right to jail cell F1 (column F, row 1). Type "DEBIT." This is where y'all tape money that leaves your account, too known as outflow.
- Move one cell to the right to prison cell G1 (column K, row ane). Blazon "EXPENSE CATEGORY." You lot will get out this blank for now. Options for this column will be created in a later on step.
- Motility one cell to the right to cell H1 (column H, row 1). Blazon "CREDIT." This is where you record money coming into your account, likewise known every bit inflow.
- Move one cell to the correct to prison cell J1 (column J, row 1). Blazon "INCOME CATEGORY." As with the expense category column, options for this column will exist created in a later on footstep.
- Move one prison cell to the correct to jail cell K1 (column K, row 1). Type "BALANCE." This is the current corporeality in your business relationship afterward all transactions have been recorded.
4
Format column labels. To brand the column labels piece of cake to read, format them and so they are in bold font and give the row of register labels a different background colour. To do this, first you select the range of cells that you lot desire to format. Then y'all select the formatting options.^[6]
- Observe the formatting options in the "HOME" tab of the toolbar ribbon. When y'all open a new workbook, the "HOME" tab is open by default.^[7]
- Select cell B1 (Appointment) 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 saucepan icon to run into the palette from which you can select your background colour.
5
Resize some of the columns. The default size of some of the columns will likely be too small to hold the information y'all will put in them. For instance, the "PAYEE" and "DESCRIPTION" columns could hold long names or lengthy memos. Also, column A, which has no data and is merely a spacer, should exist very narrow.^[8]
- Click the header for column A to select the whole column. In the upper right hand corner of the toolbar in the "Dwelling" ribbon, click on the "FORMAT" button. From the drop down carte du jour, click on "Column Width." Enter the number 2 and click on "OK." At present column A is very narrow.
- Widen column D, "PAYEE." Select column D past clicking on the header. Hover the cursor over the edge betwixt columns D and Eastward. 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 correct to brand the cavalcade as wide as you want it to be.
- Repeat the same process to widen column Eastward, "Clarification."
half-dozen

Centre the register labels. Select the entire starting time row past left-clicking on the number 1 on the left border of the page. In the upper left hand side of the toolbar on the "Abode" ribbon, click on the "heart" formatting push. This action centers all of the data in the selected cells. Yous will observe that the cavalcade labels are at present centered in their cells.^[9]

1
Input some test data. In order to see the formatting changes you make, enter iv rows of data into the cells. Begin with the opening balance, and and then enter 3 more transactions.
- In jail cell B2, add together a date for the opening balance, for instance ix/27/15. In jail cell D2, which is the "PAYEE" cavalcade, type "Opening Balance." In cell K2, which is the "BALANCE," blazon the amount of money you have in your account every bit of the date y'all entered in jail cell B2.
- Add iii 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 engagement cavalcade may be formatted to read "ix/27/2015" or "27-Sept." The columns in which you entered dollar amounts may accept the wrong number of decimal points. Formatting will clean all of this up.^[10]
2
Format the dates. Brand this cavalcade display the dates in a consistent way. Excel provides dissimilar options for formatting the appointment. Select the i that y'all prefer.^[11]
- Click on the header for column B, "DATE." This selects the entire cavalcade.
- Right-click on the column, and select "Format Cells." The "Format Cells" window will appear.
- Select the "Number" tab. Under "Category" cull "Engagement." Select the format you desire for the date and click on "OK" in the lesser right corner of the window.
- While this column is nonetheless 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.
3
Format the "ITEM #" cavalcade. The data in this cavalcade should be centered. Highlight all of column C by clicking on the header for the column. Click on the "center" 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 "Description." Past default, Excel formats cells so that the information is aligned to the left. This should work well for these columns. Double bank check the sizing of the columns. Now that you have some data in these cells, accommodate the column width to make the columns wider or narrower if necessary.^[13]
4
Format the currency in columns F, H and M, "DEBIT," "CREDIT" and "Remainder FORWARD." The currency should have two decimal points. You tin can choose to testify the dollar sign if you desire. Y'all can also make your debits announced in red font if you desire.
- Select column F. Correct-click on the column and select "Format Cells." The "Format Cells" window will appear. In the "Number" tab, select "Bookkeeping. " Cull "2" in the "Decimal places" pick. Select the dollar sign in the "Symbol" choice.
- 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 cavalcade and select "Format Cells." When the "Format Cells" window appears, select the "Font" tab. In this tab, click the downwardly arrow adjacent to the "Color" option. From the palette, click on red.

Advertising

one
Create a formula to calculate a running balance. Insert a formula into cavalcade K that does the math to summate the running balance. Note that y'all did not need a formula in cell K2. This is where yous entered your opening residue.
- Click on jail cell K3. At present click on the formula bar at the tiptop of the spreadsheet. This is where you will 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 balance (cell K2), and subtract a debit if i exists (cell F3) and add a credit if i exists (cell H3).^[14]
- Suppose your opening balance was $200, and your get-go entry was a bank check you lot wrote for $35.00. The $35.00 is recorded as a debit in cell F3. The formula y'all entered in prison cell H3 takes the opening balance and subtracts the debit, leaving you with a rest of $165.00.
two

Copy the formula. Select cell K3. Correct-click and select "Copy." Select cells K4 and K5. Right-click and select "Paste." The formula is now copied into these cells. You should come across that the running residue has been calculated in column Yard for all the rows of test data that you entered.
3
Create a conditional formula to clean upwardly the running balance column. Yous could copy the above formula into jail cell K6. But since you haven't entered any data in this row, the running balance from cell K5 volition also announced in cell K6. To clean this up, create a conditional formula that leaves the cell blank if no transactions have been entered, just displays a rest if they have been entered.
- In cell K6, enter the formula =IF(ISBLANK(B6),"",SUM(K5-F6+H6)). This tells Excel that if jail cell B6, in the "Date" column, is bare, then prison cell H6 should be blank. But if cell B6 is non blank, then the balance should be calculated.^[15]
4
Extend the formula with AutoFill. The AutoFill automatically fills in formulas in adjacent cells so you lot won't accept to enter the "Rest" formula over and over again.^[xvi]
- Discover the AutoFill handle in the active prison cell. Find the small, dark foursquare in the bottom right corner of the agile jail cell. Hover over it and the cursor will change to the AutoFill cursor, which looks like a thin plus sign.^[17]
- Click on prison cell K6. Hover over the AutoFill handle and the cursor will change to the thin plus sign. Left-click and concord the AutoFill handle. Drag the cursor downwardly to cell K100 (column Chiliad, row 100).
- The formula has now been copied into all of the cells in cavalcade M through row 100. The row and cavalcade numbers in each cell accept been automatically adapted so the formula calculates correctly.^[18]

Advert

i

Categorize transactions. Create categories of transactions to go along runway of how you lot spend your money and the types of income. Categories could be related to income taxes, such as property taxes or charitable giving. You could as well use the categories to create a charts to hands visualize financial action in your account.
ii
Create a "Categories" tab. This is where you lot will store all of the potential income and expense categories for your check annals. Rename 1 of the tabs in your workbook "Categories." Double click on the current title of the tab to highlight the name. The electric current name volition be something similar "sheet2" or "sheet3." When the name of the canvas is highlighted, yous tin can blazon in the new proper name of the tab. Type "Categories." .^[19]
- In cell B4, blazon the word "Categories." Format the prison cell to have bold font and alter the alignment to center.
3
Create income categories. In jail cell B5, type "*** Income ***". Call back of all of the categories of income y'all have or may utilize in the futurity. In prison cell B6 and continuing downward, enter all of your income categories.
- The nigh common income category would be "Wages." You might need multiple wage categories if you have more than than one chore.
- Other income categories y'all might desire to include depend on your financial circumstances. If you own stock, create a category for "Dividends." If you receive kid support, create a category for that. Other categories to add together include "Interest Income," "Gifts" and "Miscellaneous."
4
Create expense categories. Leave a cell blank under your last income category. Motility ane jail cell downward and type "*** Expenses ***". Enter all of the expense categories under this department heading.
- Exist as broad or as narrow as you wish with your expense categories. Expense categories might include "Mortgage," "Rent," "Insurance," "Machine Payment," "Gas," "Electricity," "Phone" and "Entertainment."
v

Name the range of cells containing your categories. Select cell B5. Highlight from cell B5 all the style downwardly through all of your income and expense categories. Detect the cell proper noun box in the upper left hand corner of the window. It is to the left of the formula box. It will say "B5," which is the proper name of the first cell in the highlighted range. Click on the cell name box and type "Categories." This names the range of cells so you can employ it in your check register.^[20]
6
Apply the expense and income categories in the check register. Go back to the tab where y'all created the check annals. You will now add together drop down menus to the "EXPENSE CATEGORY" and "INCOME CATEGORY" columns you created.
- In the bank check annals tab, select prison cell G2. This is the starting time cell in the "EXPENSE CATEGORY" column.
- On the toolbar, select the "DATA" ribbon. Click on the "Data Validation" button. Select "Information Validation" from the drop down menu. This will open the "Data 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." Under "Source" enter "=Categories." Click OK.^[22]
- You will at present see a little arrow side by side to cell G2. Click on the arrow to see the listing of categories. Click on the appropriate category for the transaction in that row.^[23]
- Using AutoFill, copy the formula from cell G2 all the way down to prison cell G100.
- Go to cell J2 to repeat the process in the "INCOME CATEGORY" column.

Advertising

ane

Lock the cells with formulas and protect the worksheet. Protecting the worksheet means that data in locked cells cannot be overwritten. This way you don't have to worry almost the balance forward being incorrectly calculated because the formula was accidentally changed. Yous tin also create a password to further protect your check register from other users.^[24] Just brand sure information technology's something that is easy for you to remember, or you lot write it in a secure place; if you forget the password you lot will be unable to access the worksheet.
2
Unlock cells. By default, once a worksheet is protected, all of the cells become locked. So to begin, y'all need to unlock the cells into which data will entered even afterwards 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 last column, K, which is the "BALANCE" column. Yous are going to need to be able to enter data in these cells even after the worksheet is protected.
- Correct-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.
three
Activate "Protection" for the worksheet. Once "Protection" is activated, this means that all cells which remained locked, including the cells in column One thousand, "BALANCE," cannot be overwritten.^[26]
- Go to the "REVIEW" ribbon on the toolbar. Click on "Protect Sail." The "Protect Canvass" window volition appear.
- If you want to countersign protect your worksheet, add it here. If non, leave this field blank.
- Click on OK. Your check register is no protected.
4

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

Advertizement

Add New Question

Question

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

Darron Kendrick is an Adjunct Professor of Accounting and Law at the University of North Georgia. He received his Masters caste in tax law from the Thomas Jefferson School of Law in 2012, and his CPA from the Alabama Country Board of Public Accountancy in 1984.

Financial Advisor

Expert Respond

Back up wikiHow by unlocking this expert answer.

This could exist done using the Filter choice on the tool bar. The filter volition allow y'all to select the range you wish to report on, fix the parameters, then it volition return all of the cells as designated. This group may then be copied into a report format.

Inquire a Question

200 characters left

Include your e-mail accost to get a bulletin when this question is answered.

Submit

Video

About This Article

Article Summary X

To create a simple checkbook register with Microsoft Excel, open a blank spreadsheet and create column labels that would be in a newspaper register, like Date, Check Number, and Payee. And so, format the column headings then that they are bold and easy to read, and suit the cell formatting to account for currency and dates. Next, create a formula in the Balance cavalcade to decrease a debit or add together a credit from the opening balance. And then, copy that formula into each cell in that column to proceed a running residual. To learn more from our Accountant co-author, like how to countersign-protect your register, keep reading the article!

Did this summary help you lot?

Thanks to all authors for creating a page that has been read 569,348 times.

Did this article aid you lot?