21. Fill a highlighted range with the same item
Highlight a range, type the data, then press Ctrl+Enter.
22. Using mouse to click on a cell to fill a formula with the cell address
When you enter a formula and your formula require a cell address, you can use mouse to click on the cell. The address of the cell will be entered into the formula.
23. Using 'Delete' icon to delete row
By using the Delete icon, you can avoid many clicks in order to delete a row or a selection of rows. This icon is available under the menu Tools/Customize.
When you click the Tools/ Customize menu, the Customize dialog box will appear.
Click the Commands tab.
Under the Categories label, select Edit item from the list.
Under the Commands label, search for the Delete Rows icon. Drag the icon to toolbar area and drop it there.
Close the Customize dialog box (press Esc key).
From now on, you can just click on the icon to delete a row. You can remove the icon by dragging the icon back into the Customize dialog box.
24. Use 'Esc' key to dismiss a dialog
Press Esc key to close a dialog, rather than looking for your mouse and clicking on the Cancel button.
25. Fill a range in a column with a series of numbers.
Write a beginning number into the start cell. Point your mouse to the cell's bottom-right handle until it changes into a plus sign (+). Press the right mouse button. While still holding down the button, drag your mouse to highlight the range area that you want. Once you release the mouse, a list of actions (floating menu) will appear. Select item Fill Series. A series of number will be inserted for you.
26. Using 'Paste' icon to paste copied item
By using the Paste icon to paste a copied item, you can avoid many clicks in order to paste repetitively. This icon is available under the menu Tools/Customize.
212
When you click the Tools/Customize menu, the Customize dialog box will appear.
Click the Commands tab.
Under the Categories label, select Edit item from the list.
Under the Commands label, search for the Paste icon. Drag the icon to toolbar area and drop it there.
Close the Customize dialog box (press Esc key).
From now on, you can just click on the icon to paste a copied cell content. You can remove the icon by dragging the icon back into the Customize dialog box.
27. Using 'Select visible cells' icon to select visible cells
By using the Select Visible Cells icon, you can avoid many clicks in order to select visible cells after you filter the rows. This icon is available under the menu Tools/Customize.
When you click the Tools/Customize menu, the Customize dialog box will appear.
Click the Commands tab.
Under the Categories label, select Edit item from the list.
Under the Commands label, search for the Select Visible Cells icon.
Drag the icon to toolbar area and drop it there.
Close the Customize dialog box (press Esc key).
From now on, you can just click on the icon to select visible cells. You can remove the icon by dragging the icon back into the Customize dialog box.
28. Using menu 'Insert/Copied cell'
Sometimes we want to copy contents of cells and paste them into cells that already has content and push the cells with content down or to the right. After copying the cell content, use the menu Insert/Copied cell to insert the copied cell. From the Insert Paste dialog box, select either Shift cells right or Shift cells down. The current cell or highlighted cells will be moved downward or to the right (its content will not be overwritten). This operation is also useful if we want to reorganise positions of our data.
213
However, if we 'cut' rather than 'copy', the menu that appears is Insert/Cut cells and this will leave the 'cut' cells empty.
NOTE
Be careful when inserting, because only a highlighted cell is moved. This means if you highlight some of the cells in your data area, it may cause you whole data to become disorganised.
29. Using 'Custom View' to remember a filtered data
After filtering your data, you can ask Excel to remember that filtered view. Click on the menu View/ Custom Views. The Custom Views dialog box will appear. To capture the current view, click Add button. In the Add View dialog box that appears, give a descriptive name for the view in the Name box. Click OK. The dialog will close and you will see the dialog Custom Views again.
The name you set in Add View dialog will appear in the Custom Views dialog box.
Click Close to close the dialog. Next time, when you want to see the view, you can open the Custom Views dialog, click on a view name, and then the Show button. This way, you can avoid using filtering operation each time you want to see the same filtered data. If you make changes to the data, the view will also be updated automatically.
NOTE
After you use the view. Excel will freeze the last row of the view. This requires you to use menu Window/Unfreeze panes to unfreeze the frozen row.
If you want to delete a view, open the Custom Views dialog, then click on a view name, and click Delete button.
214
Friday, October 9, 2026
Thursday, October 8, 2026
Tips and Shortcuts 1 to 20 pg 209 210 211
28. Tips & Shortcuts
Here, we list some shortcuts and techniques you can use to enable you to work faster especially if you prefer to use keyboard keys rather than the mouse.
1. Quick navigation to first row.
When you are at faraway row, say 10000, you can quickly go to the first row: Hold down the Ctrl key and press Home. This is refered to as the Ctrl+Home combination.
2. Quick navigation to a particular cell.
When you are at certain cell, you can quickly jump to any faraway cell. You must know the address of the cell or any cell near it. Click menu Edit/Go To to open the Go To dialog box. In a box under the Reference label, write the cell address (eg. A112). Click OK.
Оr,
Using the keyboard, press Alt (to activate the menu bar), then press E (for Edit), then press G key (for Go To), then write the address of the target cell, and press Enter.
3. To copy the content of the cell above the current cell to the current cell
At the current cell, press Ctrl. While still pressing the key, press the D key. This is called Ctrl+D combination. When you release the keys, the content, formula and format of the cell above it will be copied.
4. To copy content of the cell on the left of the current cell to the current cell
At the current cell, press Ctrl. While still pressing the key, press the R key. This is called Ctrl+R combination. When you release the keys, the content, formula and format of the cell on its left will be copied.
5. To delete a current row
At the current cell, press Alt (to activate the menu bar), then press E (for Edit), then press D key (for Delete), then press R key (for Row). The whole row of the current cell will be deleted.
209
6. To save current file
On the keyboard, press Alt (to activate the menu bar), then press F (for File), then press S key (for Save). Or, use the Ctrl+S combination.
7. To find a text, number or date
On the keyboard, press Alt (to activate the menu bar), then press E (for Edit), then press F (for Find), then write the item you want to find, finally press Enter. Excel will highlight the cell containing the item.
8. To highlight all filled cells from the current cell to the last filled cell in the same column
At the current cell, press Ctrl+Shift+↓ combination to highlight all the cells from the current cell to the last filled cell in the same column. Press Ctrl+Shift+↑ to highlight cells from the bottom.
9. To make a current cell content ready for copying
At the current cell, press Ctrl+C (for Copy). Or press Alt key (to activate the menu bar), then press E key (for Edit), then press C key (for Copy).
10. To paste into a current cell the content of a cell already copied.
At the current cell, press Ctrl+V. Or press Alt key (to activate the menu bar), then press E key (for Edit), then press V key (for Paste).
11. To move from current sheet to the sheet on the left
At the current sheet, press Ctrl+Page Up keys. The sheet on the left will be in focus.
12. To move from current sheet to the sheet on the right
At the current sheet, press Ctrl+Page Down keys. The sheet on the right will be in focus.
13. To highlight a contiguous range of cells downward, one cell at a time
At the current cell, press Shift+↓ keys. Each additional one pressing of the arrow-down key (while still holding down the Shift key) will add another cell into the selection.
210
14. To highlight a contiguous range of cells downward, one screen at a time At the current cell, press Shift+Page Down keys.
15. To highlight non-contiguous cells
Select the first cell. Press Ctrl key. While still holding down the key, click on the intended cells, one by one.
16. To insert a row at the current cell
At the current cell, press Alt key (to activate the menu bar), then press I key (for Insert), then press R key (for Row).
17. To select all cells which contain no formula.
Make sure the active cell is inside the data area. Click menu Edit/Go To to open the Go To dialog box. Click on the Specials button to open the Go To Specials dialog box. Select Constants. Click OK.
Or,
by using the keyboard: Press Alt (to activate the menu bar), then press E (for Edit), then press G (for Go To), then press Alt+S keys (for Specials), then press 0 key (for Constants), finally press Enter.
18. To go to the last row in the data area
Press Ctrl+End keys, then release all the keys.
19. To turn positive number to negative and vice versa when pasting a copied number
Say you want copy a number 100 and you want it to become 100 when pasted to another cell. Use menu Edit/Copy to copy. Then use menu Edit/Paste Special. In the Special dialog box that appears, select the Substract option. Click OK.
20. Look at the status bar to see totals
When you highlight cells which contain numbers, you can see their total at the status bar.
211
Here, we list some shortcuts and techniques you can use to enable you to work faster especially if you prefer to use keyboard keys rather than the mouse.
1. Quick navigation to first row.
When you are at faraway row, say 10000, you can quickly go to the first row: Hold down the Ctrl key and press Home. This is refered to as the Ctrl+Home combination.
2. Quick navigation to a particular cell.
When you are at certain cell, you can quickly jump to any faraway cell. You must know the address of the cell or any cell near it. Click menu Edit/Go To to open the Go To dialog box. In a box under the Reference label, write the cell address (eg. A112). Click OK.
Оr,
Using the keyboard, press Alt (to activate the menu bar), then press E (for Edit), then press G key (for Go To), then write the address of the target cell, and press Enter.
3. To copy the content of the cell above the current cell to the current cell
At the current cell, press Ctrl. While still pressing the key, press the D key. This is called Ctrl+D combination. When you release the keys, the content, formula and format of the cell above it will be copied.
4. To copy content of the cell on the left of the current cell to the current cell
At the current cell, press Ctrl. While still pressing the key, press the R key. This is called Ctrl+R combination. When you release the keys, the content, formula and format of the cell on its left will be copied.
5. To delete a current row
At the current cell, press Alt (to activate the menu bar), then press E (for Edit), then press D key (for Delete), then press R key (for Row). The whole row of the current cell will be deleted.
209
6. To save current file
On the keyboard, press Alt (to activate the menu bar), then press F (for File), then press S key (for Save). Or, use the Ctrl+S combination.
7. To find a text, number or date
On the keyboard, press Alt (to activate the menu bar), then press E (for Edit), then press F (for Find), then write the item you want to find, finally press Enter. Excel will highlight the cell containing the item.
8. To highlight all filled cells from the current cell to the last filled cell in the same column
At the current cell, press Ctrl+Shift+↓ combination to highlight all the cells from the current cell to the last filled cell in the same column. Press Ctrl+Shift+↑ to highlight cells from the bottom.
9. To make a current cell content ready for copying
At the current cell, press Ctrl+C (for Copy). Or press Alt key (to activate the menu bar), then press E key (for Edit), then press C key (for Copy).
10. To paste into a current cell the content of a cell already copied.
At the current cell, press Ctrl+V. Or press Alt key (to activate the menu bar), then press E key (for Edit), then press V key (for Paste).
11. To move from current sheet to the sheet on the left
At the current sheet, press Ctrl+Page Up keys. The sheet on the left will be in focus.
12. To move from current sheet to the sheet on the right
At the current sheet, press Ctrl+Page Down keys. The sheet on the right will be in focus.
13. To highlight a contiguous range of cells downward, one cell at a time
At the current cell, press Shift+↓ keys. Each additional one pressing of the arrow-down key (while still holding down the Shift key) will add another cell into the selection.
210
14. To highlight a contiguous range of cells downward, one screen at a time At the current cell, press Shift+Page Down keys.
15. To highlight non-contiguous cells
Select the first cell. Press Ctrl key. While still holding down the key, click on the intended cells, one by one.
16. To insert a row at the current cell
At the current cell, press Alt key (to activate the menu bar), then press I key (for Insert), then press R key (for Row).
17. To select all cells which contain no formula.
Make sure the active cell is inside the data area. Click menu Edit/Go To to open the Go To dialog box. Click on the Specials button to open the Go To Specials dialog box. Select Constants. Click OK.
Or,
by using the keyboard: Press Alt (to activate the menu bar), then press E (for Edit), then press G (for Go To), then press Alt+S keys (for Specials), then press 0 key (for Constants), finally press Enter.
18. To go to the last row in the data area
Press Ctrl+End keys, then release all the keys.
19. To turn positive number to negative and vice versa when pasting a copied number
Say you want copy a number 100 and you want it to become 100 when pasted to another cell. Use menu Edit/Copy to copy. Then use menu Edit/Paste Special. In the Special dialog box that appears, select the Substract option. Click OK.
20. Look at the status bar to see totals
When you highlight cells which contain numbers, you can see their total at the status bar.
211
Potential problems pg 207 208
POTENTIAL PROBLEMS
1. Excel may get slower
You may find Excel is getting slower in its reaction if you have entered data in thousands of rows, say more than 15,000 rows. You can notice that it is getting slower that when you press 'Enter' key, the mouse cursor does not move to another cell immediately, but takes more than 1 second. You may also notice that the status bar shows that Excel is still doing some calculation for 2 or 3 seconds. Some people can tolerate delay of 3 or 4 seconds. The delay is caused by so many calculations Excel has to make since it contain so many data which affect the recalculations of formulas.
If you can't stand the little delay, you can avoid it by setting the calculation mode to 'manual'. Steps :
1. Click menu 'Tools/Option'.
2. A dialog box with title 'Options' will appear.
3. Click on 'Calculation' tab.
4. Select 'Manual'.
5. Click 'OK'.
From this point onwards, Excel will not do the calculation automatically. It means, all formulas will not get calculated or updated.
To force calculation of all formulas after you make the above setting , press key 'F9' on the keyboard.
Another way to force calculation when it is set as 'manual' is by using ‘Calculate Now’ button. Follow this step :
1. Click menu 'View/ Toolbars/ Customize'.
2. A dialog box with title 'Customize' will appear.
3. Activate the 'Commands' tab.
4. Under 'Categories' list, select 'Tools'.
5. Under 'Commands' list, find 'Calculate now' button.
6. Drag the 'Calculate now' button into the toolbar area and release at the location you want.
7. The button will stay there (you can remove it later by dragging back in to the dialog box).
8. Click OK.
9. From now on, you can click on that button to force formulas calculations.
This manual calculation affects all other Excel files, not just the file which you set the mode. If you share your computer with others, make sure you reset the calculation to ‘automatic’ calculation, unless you know that they know about ‘manual’ calculation.
2. Missing Formulas
You may have forgotten to copy a formula to cells that should contain the formula. For example, the cells in the DEBT-AGE column should contain formulas to calculate the age of the debt. To enable you to quickly review all the cells that should contain formulas, click on the menu Tools/Options/View/Show formula.
3. Accounts That Do Not Balance
The accounts may not balance. You must trace the cause. The most common reason for this is that the amount in the column RM-DEBIT is not the same as the absolute amount in the column RM-CREDIT. In cell AB5, use this formula: =H5+J5. Copy this formula down until the last row. This formula will produce 0 (zero) if a particular row is balance, otherwise it will produce a non-zero figure. Review this column AB quickly for any non-zero amounts.
208
1. Excel may get slower
You may find Excel is getting slower in its reaction if you have entered data in thousands of rows, say more than 15,000 rows. You can notice that it is getting slower that when you press 'Enter' key, the mouse cursor does not move to another cell immediately, but takes more than 1 second. You may also notice that the status bar shows that Excel is still doing some calculation for 2 or 3 seconds. Some people can tolerate delay of 3 or 4 seconds. The delay is caused by so many calculations Excel has to make since it contain so many data which affect the recalculations of formulas.
If you can't stand the little delay, you can avoid it by setting the calculation mode to 'manual'. Steps :
1. Click menu 'Tools/Option'.
2. A dialog box with title 'Options' will appear.
3. Click on 'Calculation' tab.
4. Select 'Manual'.
5. Click 'OK'.
From this point onwards, Excel will not do the calculation automatically. It means, all formulas will not get calculated or updated.
To force calculation of all formulas after you make the above setting , press key 'F9' on the keyboard.
Another way to force calculation when it is set as 'manual' is by using ‘Calculate Now’ button. Follow this step :
1. Click menu 'View/ Toolbars/ Customize'.
2. A dialog box with title 'Customize' will appear.
3. Activate the 'Commands' tab.
4. Under 'Categories' list, select 'Tools'.
5. Under 'Commands' list, find 'Calculate now' button.
6. Drag the 'Calculate now' button into the toolbar area and release at the location you want.
7. The button will stay there (you can remove it later by dragging back in to the dialog box).
8. Click OK.
9. From now on, you can click on that button to force formulas calculations.
This manual calculation affects all other Excel files, not just the file which you set the mode. If you share your computer with others, make sure you reset the calculation to ‘automatic’ calculation, unless you know that they know about ‘manual’ calculation.
2. Missing Formulas
You may have forgotten to copy a formula to cells that should contain the formula. For example, the cells in the DEBT-AGE column should contain formulas to calculate the age of the debt. To enable you to quickly review all the cells that should contain formulas, click on the menu Tools/Options/View/Show formula.
3. Accounts That Do Not Balance
The accounts may not balance. You must trace the cause. The most common reason for this is that the amount in the column RM-DEBIT is not the same as the absolute amount in the column RM-CREDIT. In cell AB5, use this formula: =H5+J5. Copy this formula down until the last row. This formula will produce 0 (zero) if a particular row is balance, otherwise it will produce a non-zero figure. Review this column AB quickly for any non-zero amounts.
208
Printing
PRINTING
Printing of transaction data and reports can be done as normally. This chapter will only show how to print all ledger balances at one go. We know that in sheet ‘Ledger’, we can get a single account details and balance at a time, thus printing all ledger balances will take a long time and require more work and hundreds of clicks of mouse. If we can combine all ledger details into one sheet, we can print them by just one printer setting and only a few mouse clicks. This is the aim of this chapter, ie to show how to combine all ledger balances into a separate sheet so that we can print them all at once rather than one by one. This is useful for printing for the hard copy after the year end.
Follow these steps :
1. Create a new sheet (Use menu ‘Insert/ Worksheet’).
2. Leave its name at default or you can give it a different name.
3. Copy data titles in column A to column F in sheet ‘Data’ to the new sheet .You can copy to cell A1. The titles are SRC, DATE, MTH, SRC NO., OTHER REF./ NO., NOTE. Add 2 new titles ie ACCOUNT and RM in column G and H respectively.
4. Copy data in column A to column F in sheet ‘Data’ to the new sheet in immediately below the data titles. The titles are SRC, DATE, MTH, SRC NO., OTHER REF./ NO., NOTE.
5. Copy the same data in column A to column F in sheet ‘Data’ to the new sheet in immediately below the data already copied in step 4 above. (we are actually make a copy twice, one below the other).
6. Copy data in column G (ACCOUNT-DEBIT) and H (RM-DEBIT) in sheet ‘Data’ to the new sheet immediately below the data titles ACCOUNT and RM respectively.
7. Copy data in column I (ACCOUNT-CREDIt) and J (RM-CREDIT) in sheet ‘Data’ to the new sheet immediately below the data already copied in step 6 above.
8. Sort the data according to data title ACCOUNT then DATE. (Use menu ‘Data/ Sort’. A dialog with title ‘Sort’ appears. In the dialog, under ‘Sort by’ label, select ‘ACCOUNT’, and under ‘Then by’ label, select ‘DATE’. Click OK).
9. Get the total of each accounts. (Use menu ‘Data/ Subtotals’. A dialog with title ‘Subtotal’ will appear. Under the label ‘At each change in ;’, select ‘ACCOUNT’. Under the label ‘Use function’, select ‘Sum’. Under the label ‘Add Subtotal to’, select only ‘RM’. Make sure ‘Summary below data’ box is checked. Do not check the ‘Page Break between groups’ box so that Excel will make it print continuously, otherwise it will print one account per page. Click OK. Excel may take a few seconds to produce the result).
10. The above step 9 will produce the accounts balances and its details. The data is in ‘Subtotal-mode’. You should see 3 buttons on the upper-left side of the data with label 1, 2, 3 respectively. These buttons are useful to contract and expand the data according to the subtotals.
11. You should see also that the text in ‘total’ lines is in bold and with word ‘Total’ in addition to the account names. You may want to format these total lines further. You can use menu ‘Format/ Conditional formatting’. Let’s say you want to colour the ‘total’ figures with light yellow, so follow these steps : Select all data in column RM because the total figure is in this column. Click ‘Format/ Conditional formatting’. A dialog with title ‘Conditional Formatting’ will appear. In a box under label ‘Condition 1 is’, select ‘Formula is’. Then on the box on its right, write a formula, say : =RIGHT(G1,5)=”Total”. Click ‘Format…’ button. Click ‘Pattern’ tab. Select a colour (Light Yellow). Click ‘OK’. Click ‘OK’ again. You should find that the cells with total figures are now in light yellow.
12. Now you can print the ledger as usual (Use menu ‘File/ Print’).
Printing of transaction data and reports can be done as normally. This chapter will only show how to print all ledger balances at one go. We know that in sheet ‘Ledger’, we can get a single account details and balance at a time, thus printing all ledger balances will take a long time and require more work and hundreds of clicks of mouse. If we can combine all ledger details into one sheet, we can print them by just one printer setting and only a few mouse clicks. This is the aim of this chapter, ie to show how to combine all ledger balances into a separate sheet so that we can print them all at once rather than one by one. This is useful for printing for the hard copy after the year end.
Follow these steps :
1. Create a new sheet (Use menu ‘Insert/ Worksheet’).
2. Leave its name at default or you can give it a different name.
3. Copy data titles in column A to column F in sheet ‘Data’ to the new sheet .You can copy to cell A1. The titles are SRC, DATE, MTH, SRC NO., OTHER REF./ NO., NOTE. Add 2 new titles ie ACCOUNT and RM in column G and H respectively.
4. Copy data in column A to column F in sheet ‘Data’ to the new sheet in immediately below the data titles. The titles are SRC, DATE, MTH, SRC NO., OTHER REF./ NO., NOTE.
5. Copy the same data in column A to column F in sheet ‘Data’ to the new sheet in immediately below the data already copied in step 4 above. (we are actually make a copy twice, one below the other).
6. Copy data in column G (ACCOUNT-DEBIT) and H (RM-DEBIT) in sheet ‘Data’ to the new sheet immediately below the data titles ACCOUNT and RM respectively.
7. Copy data in column I (ACCOUNT-CREDIt) and J (RM-CREDIT) in sheet ‘Data’ to the new sheet immediately below the data already copied in step 6 above.
8. Sort the data according to data title ACCOUNT then DATE. (Use menu ‘Data/ Sort’. A dialog with title ‘Sort’ appears. In the dialog, under ‘Sort by’ label, select ‘ACCOUNT’, and under ‘Then by’ label, select ‘DATE’. Click OK).
9. Get the total of each accounts. (Use menu ‘Data/ Subtotals’. A dialog with title ‘Subtotal’ will appear. Under the label ‘At each change in ;’, select ‘ACCOUNT’. Under the label ‘Use function’, select ‘Sum’. Under the label ‘Add Subtotal to’, select only ‘RM’. Make sure ‘Summary below data’ box is checked. Do not check the ‘Page Break between groups’ box so that Excel will make it print continuously, otherwise it will print one account per page. Click OK. Excel may take a few seconds to produce the result).
10. The above step 9 will produce the accounts balances and its details. The data is in ‘Subtotal-mode’. You should see 3 buttons on the upper-left side of the data with label 1, 2, 3 respectively. These buttons are useful to contract and expand the data according to the subtotals.
11. You should see also that the text in ‘total’ lines is in bold and with word ‘Total’ in addition to the account names. You may want to format these total lines further. You can use menu ‘Format/ Conditional formatting’. Let’s say you want to colour the ‘total’ figures with light yellow, so follow these steps : Select all data in column RM because the total figure is in this column. Click ‘Format/ Conditional formatting’. A dialog with title ‘Conditional Formatting’ will appear. In a box under label ‘Condition 1 is’, select ‘Formula is’. Then on the box on its right, write a formula, say : =RIGHT(G1,5)=”Total”. Click ‘Format…’ button. Click ‘Pattern’ tab. Select a colour (Light Yellow). Click ‘OK’. Click ‘OK’ again. You should find that the cells with total figures are now in light yellow.
12. Now you can print the ledger as usual (Use menu ‘File/ Print’).
Wednesday, October 7, 2026
Bank reconciliation pg 202 203 204
1. Design the reconciliation statement as you like.
2. Use a column in the data entry sheet to mark bank entry that has it corresponding entry in the statement provided by the bank.
3. Ummarked cell means unreconciled items.
4. Bring the unmarked item details into the reconciliation statement. (formula)
25. Bank Reconciliation
Introduction
In the structure of our file i.e. in sheet DATA, we provide a column (with title BANK RECON) for the purpose of helping us in our preparation of Bank Reconciliation. Under that column title, we will identify whether a transaction amount is subject to reconciliation with the statement received from a bank. Clearly, only transactions affecting a bank account (whether in debit or credit side) will give effect.
If a transaction (e.g. Credit sales) is irrelevant to bank reconciliation, we write NR in this column (NR is short for Not relevant). If a transaction is a bank receipt/payment and the receipt/payment does not appear ear in bank statement, we write open. Initially all bank receipt/payment will be marked open. When we receive a bank statement and the receipt/payment appear on the bank statement, we write the month of the bank statement (e.g. "jan", "feb", etc). When we receive a bank statement and we notice that an item in the statement does not appear in our bank transactions, update our transaction and write the month of the bank transaction in the BANK RECON column.
Example
To do bank reconciliation for a particular bank, we need to select all rows in which the cell in column BANK RECON is marked with open and cells in column ACCOUNT DEBIT or ACCOUNT CREDIT contain the name of that particular bank account.
This row selection process require us to use AdvanceFiltering because it uses more than one column. Here are the steps:
1. Set a criteria block similar to the following:
The above criteria reads: Select rows where column ACCOUNT-DEBIT contain item 'BANK A' and column BANK RECON contain item 'open' (both items in the same row), and select also rows where column ACCOUNT-CREDIT contain item 'BANK A' and column BANK RECON contain item 'open' (both items in the same row)
2. Then, activate the sheet that contain our data (sheet DATA).
3. Click menu Data/Filter/AdvancedFilter to open the Advance Filter dialog box.
4. The List range box is automatically filled in with the address of the data area.
5. Fill the Criteria range box with address of the area of your criteria block.
6. Click OK.
7. You should see the data filtered according to the criteria block. You can copy relevant data to your bank reconciliation working sheet.
8. Repeat the process for each of the other bank accounts.
NOTE
You can add another sheet to the file for the purpose of bank reconciliation or you can use a separate file.
Below is a simplified sample of a bank reconciliation format:
203
As you can see, we only copy 4 columns from the filtered data ie. PV No, Cheque No, Payee and Amount (RM).
If your bank balance does not reconcile with the statement from the bank, check the following:
1. Items marked open.
2. Items appearing in the statement but not in the bank account transaction.
3. Items appearing in the bank account transaction but not in the bank statement.
In the subsequent month, some of the unmatched items may match and new unmatched items are available in the sheet DATA. In the reconciliation sheet, delete the matched item and add the new items and adjust the new balances for the bank book and bank statement. Before you do that, it is good to have a back-up copy of the previous month's reconciliation.
204
2. Use a column in the data entry sheet to mark bank entry that has it corresponding entry in the statement provided by the bank.
3. Ummarked cell means unreconciled items.
4. Bring the unmarked item details into the reconciliation statement. (formula)
| K | L | M | N | O | P | |
| 1 | ACCOUNTS | Amounts | Cum. Amounts | Check Accounts | Bank Recon. | Sales Inv. Status |
| 2 | (formula) | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 | (formula) | |||||
| 10 | ||||||
| 11 | ||||||
| 12 |
25. Bank Reconciliation
Introduction
In the structure of our file i.e. in sheet DATA, we provide a column (with title BANK RECON) for the purpose of helping us in our preparation of Bank Reconciliation. Under that column title, we will identify whether a transaction amount is subject to reconciliation with the statement received from a bank. Clearly, only transactions affecting a bank account (whether in debit or credit side) will give effect.
If a transaction (e.g. Credit sales) is irrelevant to bank reconciliation, we write NR in this column (NR is short for Not relevant). If a transaction is a bank receipt/payment and the receipt/payment does not appear ear in bank statement, we write open. Initially all bank receipt/payment will be marked open. When we receive a bank statement and the receipt/payment appear on the bank statement, we write the month of the bank statement (e.g. "jan", "feb", etc). When we receive a bank statement and we notice that an item in the statement does not appear in our bank transactions, update our transaction and write the month of the bank transaction in the BANK RECON column.
Example
To do bank reconciliation for a particular bank, we need to select all rows in which the cell in column BANK RECON is marked with open and cells in column ACCOUNT DEBIT or ACCOUNT CREDIT contain the name of that particular bank account.
This row selection process require us to use AdvanceFiltering because it uses more than one column. Here are the steps:
1. Set a criteria block similar to the following:
A B C D 28 29 ACCOUNT-DEBIT ACCOUNT-CREDIT BANK RECON 30 BANK A open 31 BANK A open 32202
The above criteria reads: Select rows where column ACCOUNT-DEBIT contain item 'BANK A' and column BANK RECON contain item 'open' (both items in the same row), and select also rows where column ACCOUNT-CREDIT contain item 'BANK A' and column BANK RECON contain item 'open' (both items in the same row)
2. Then, activate the sheet that contain our data (sheet DATA).
3. Click menu Data/Filter/AdvancedFilter to open the Advance Filter dialog box.
4. The List range box is automatically filled in with the address of the data area.
5. Fill the Criteria range box with address of the area of your criteria block.
6. Click OK.
7. You should see the data filtered according to the criteria block. You can copy relevant data to your bank reconciliation working sheet.
8. Repeat the process for each of the other bank accounts.
NOTE
You can add another sheet to the file for the purpose of bank reconciliation or you can use a separate file.
Below is a simplified sample of a bank reconciliation format:
A B C D 1 BANK RECONCILIATION 2 BANK A 3 4 Balance as per Bank A book as at 31-05-02 300,000.00 5 Add/Less: Unmatched items 6 PV No. Cheque No Payee/Payer RM 7 PV1 00023456 Sandal Co 200,00 8 PV 7 00023477 Shoes Co 100.00 9 10 Total 300.00 11 12 Balance as per statement from bank A for 31.05.02 300,300.00 13
203
As you can see, we only copy 4 columns from the filtered data ie. PV No, Cheque No, Payee and Amount (RM).
If your bank balance does not reconcile with the statement from the bank, check the following:
1. Items marked open.
2. Items appearing in the statement but not in the bank account transaction.
3. Items appearing in the bank account transaction but not in the bank statement.
In the subsequent month, some of the unmatched items may match and new unmatched items are available in the sheet DATA. In the reconciliation sheet, delete the matched item and add the new items and adjust the new balances for the bank book and bank statement. Before you do that, it is good to have a back-up copy of the previous month's reconciliation.
204
Monday, October 5, 2026
Getting ledger details of an account Explanation pg 195 196 197 19 199 200
Here is a quick explanation of the structure (row by row):
1. Notice the box with a little downward-pointing arrowhead. This is called a Combo Box.
2. Next, we will set the combo box to that when we click on it, a list will appear and we can select an item from the list. In this case, we want to select an account from it. This requires us to have a sheet which contain a list of accounts. This is in sheet TB and the list is in first column in the range A2 to A2000. We want to make the list of accounts in sheet TB appear in the combo box. Here is how:
Right-click on the combo box. A floating menu will appear. Select Format Control to open pen the Format control dialog box. Click on the tab Control. In the box on the right of label Input Range, enter the address where the list of customers is located (in this case, it is TB!A2:A2000).
Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Input Range setting to take effect. Now if you click on the combo, a list will appear and you can click on one item from the list to select it. The list will disappear and the selected item will appear in the combo.
197
3. In cell C4, write this formula: =INDEX(TB!A2:A2000, C3). This formula will retrieve the name of the account we selected from the combo, not from the combo itself but by searching through the list in sheet TB in range A2:A2000 and by relying on the position that appear in cell C3.
4. In cell F4, write this formula: =F7+M7. Later we will know that cell F7 contains the sum of debit amounts whilst cell M7 contains the sum of credit amounts, so the formula will get the net of both amounts.
5. In cell C6, write DEBIT ENTRIES. This serves as a label.
6. In cell J6, write CREDIT ENTRIES. This serves as a label.
7. In cell C7, write TOTAL. This serves as a label.
8. In cell F7, write this formula: =SUM(F9:F50).
9. In cell J7, write TOTAL. This serves as a label.
10. In cell F7, write this formula: =SUM(M9:M50).
11. In cell B8, write 1. This will be used by formulas in cells A9 and B9. It indicates the starting row from which the formulas search for an account name.
198
12. In cell C8, write SRC. This serves as a label.
13. In cell D8, write DATE. This serves as a label.
14. In cell E8, write SRC NO. This serves as a label.
15. In cell F8, write RM-DEBIT. This serves as a label.
16. In cell 18, write 1. This will be used by formulas in cells H9 and 19. It indicates the starting row from which the formulas search for an account name.
17. In cell J8, write SRC. This serves as a label.
18. In cell K8, write DATE. This serves as a label.
19. In cell L8, write SRC NO. This serves as a label.
20. In cell M8, write RM-CREDIT. This serves as a label.
21. In cell A9, write this formula:
=MATCH(INDIRECT("C4"), INDIRECT("DATA!G"&B8+1&": $G30000"),0)
This formula will find the first occurrence of account name available in cell C4 from column G in the data area in sheet DATA starting from row as stated in cell B8 to row 30000. If it finds one, it will return the row position of the account name relative to the range being searched. If there are no matches, it will return #NA.
22. In cell B9, write this formula:
B8+49.
This formula returns the real row position (relative to the sheet) of the found account name. This row position is used by formulas in subsequent columns.
23. In cell C9, write this formula:
=INDIRECT("DATA!A"&B9)
This formula will extract the source from column A in sheet DATA in row as stated in cell B9. This would be the source of the first occurrence of the account name.
199
24. In cell D9, write this formula:
=INDIRECT("DATA!B"&B9)
This formula will extract the date from column B and row as stated in cell B9 in sheet DATA. This would be the date of the first occurrence of the account name.
25. In cell E9, write this formula:
=INDIRECT("DATA!D"&B9)
This formula will extract the source number from column D and row as stated in cell B9 from sheet DATA. This would be the source number of the first occurrence of the account name.
26. In cell F9, write this formula:
=INDIRECT("DATA!H"&B9)
This formula will extract the RM-DEBIT value from column H and row as stated in cell B9 from sheet DATA. This would be the transaction value of the first occurrence of the account name.
27. In cell H9, write this formula:
=MATCH(INDIRECT("C4"), INDIRECT("DATA!I"&18+1&": $130000"),0)
This formula will find the first occurrence of account name available in cell C4 from column I in the data area in sheet DATA starting from row as stated in cell 18 to row 30000. If it finds one, it will return the row position of the account name relative to the range being searched. If there are no matches, it will return #NA.
28. In cell 19, write this formula:
=18+H9
This formula returns the real row position (relative to the sheet) of the found account name in column I ('ACCOUNT CREDIT). This row position is used by formulas in subsequent columns.
29. In cell J9, write this formula: =INDIRECT("DATA!A"&19) This formula will extract the source from column A in sheet DATA in row as stated in cell 19. This would be the source of the first occurrence of the account name in column ACCOUNT-CREDIT.
30. In cell K9, write this formula: =INDIRECT("DATA!B"&19) This formula will extract the date from column B and row as stated in cell 19 in sheet DATA. This would be the date of the first occurrence of the account name in column 'ACCOUNT-CREDIT'.
200
31. In cell L9, write this formula:
=INDIRECT("DATA!D"&19)
This formula will extract the source number from column D and the row is stated in cell 19 from sheet DATA. This would be the source number of the first occurrence of the account name in column ACCOUNT-CREDIT.
32. In cell M9, write this formula:
=INDIRECT("DATA! J"&19)
This formula will extract value from column RM-CREDIT from column J and the row is stated in cell 19 from sheet DATA. This would be the credit transaction value of the first occurrence of the account name in column ACCOUNT-CREDIT.
33. Copy down formulas in cells A9 to M9. The formulas will get the rows containing details of account as stated in cell C4. If you see an #NA in a row, it means there is no more rows containing the account name from sheet DATA.
NOTE
If you don't want to print anything in column A and B, you can hide the column before printing. Or, you can set the Print Area (Use menu File/Print Area/Set Print Area) before you print.
If you understand the mechanics of the formula and functions, you can change the design of the ledger to follow your own structure.
TIP
You can consolidate Credit entry details (in this case, in columns H to M) to appear below the Debit entry details (in this example, in columns A to F) by copying all values (not the formulas) into a separate sheet, and then sort the details by date. This will cause the data to appear in chronological order.
You can also have Credit entries appear below the Debit entries (or vice-versa) in the same column by combining the formulas:
=IF(MATCH(INDIRECT("C4"), INDIRECT("DATA!G"&B8+18": $G30000"),0))=#NA, MATCH(INDIRECT("C4"), INDIRECT("DATA!I"&18+18": $130000"),0)),"")
1. Notice the box with a little downward-pointing arrowhead. This is called a Combo Box.
2. Next, we will set the combo box to that when we click on it, a list will appear and we can select an item from the list. In this case, we want to select an account from it. This requires us to have a sheet which contain a list of accounts. This is in sheet TB and the list is in first column in the range A2 to A2000. We want to make the list of accounts in sheet TB appear in the combo box. Here is how:
Right-click on the combo box. A floating menu will appear. Select Format Control to open pen the Format control dialog box. Click on the tab Control. In the box on the right of label Input Range, enter the address where the list of customers is located (in this case, it is TB!A2:A2000).
Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Input Range setting to take effect. Now if you click on the combo, a list will appear and you can click on one item from the list to select it. The list will disappear and the selected item will appear in the combo.
197
3. In cell C4, write this formula: =INDEX(TB!A2:A2000, C3). This formula will retrieve the name of the account we selected from the combo, not from the combo itself but by searching through the list in sheet TB in range A2:A2000 and by relying on the position that appear in cell C3.
4. In cell F4, write this formula: =F7+M7. Later we will know that cell F7 contains the sum of debit amounts whilst cell M7 contains the sum of credit amounts, so the formula will get the net of both amounts.
5. In cell C6, write DEBIT ENTRIES. This serves as a label.
6. In cell J6, write CREDIT ENTRIES. This serves as a label.
7. In cell C7, write TOTAL. This serves as a label.
8. In cell F7, write this formula: =SUM(F9:F50).
9. In cell J7, write TOTAL. This serves as a label.
10. In cell F7, write this formula: =SUM(M9:M50).
11. In cell B8, write 1. This will be used by formulas in cells A9 and B9. It indicates the starting row from which the formulas search for an account name.
198
12. In cell C8, write SRC. This serves as a label.
13. In cell D8, write DATE. This serves as a label.
14. In cell E8, write SRC NO. This serves as a label.
15. In cell F8, write RM-DEBIT. This serves as a label.
16. In cell 18, write 1. This will be used by formulas in cells H9 and 19. It indicates the starting row from which the formulas search for an account name.
17. In cell J8, write SRC. This serves as a label.
18. In cell K8, write DATE. This serves as a label.
19. In cell L8, write SRC NO. This serves as a label.
20. In cell M8, write RM-CREDIT. This serves as a label.
21. In cell A9, write this formula:
=MATCH(INDIRECT("C4"), INDIRECT("DATA!G"&B8+1&": $G30000"),0)
This formula will find the first occurrence of account name available in cell C4 from column G in the data area in sheet DATA starting from row as stated in cell B8 to row 30000. If it finds one, it will return the row position of the account name relative to the range being searched. If there are no matches, it will return #NA.
22. In cell B9, write this formula:
B8+49.
This formula returns the real row position (relative to the sheet) of the found account name. This row position is used by formulas in subsequent columns.
23. In cell C9, write this formula:
=INDIRECT("DATA!A"&B9)
This formula will extract the source from column A in sheet DATA in row as stated in cell B9. This would be the source of the first occurrence of the account name.
199
24. In cell D9, write this formula:
=INDIRECT("DATA!B"&B9)
This formula will extract the date from column B and row as stated in cell B9 in sheet DATA. This would be the date of the first occurrence of the account name.
25. In cell E9, write this formula:
=INDIRECT("DATA!D"&B9)
This formula will extract the source number from column D and row as stated in cell B9 from sheet DATA. This would be the source number of the first occurrence of the account name.
26. In cell F9, write this formula:
=INDIRECT("DATA!H"&B9)
This formula will extract the RM-DEBIT value from column H and row as stated in cell B9 from sheet DATA. This would be the transaction value of the first occurrence of the account name.
27. In cell H9, write this formula:
=MATCH(INDIRECT("C4"), INDIRECT("DATA!I"&18+1&": $130000"),0)
This formula will find the first occurrence of account name available in cell C4 from column I in the data area in sheet DATA starting from row as stated in cell 18 to row 30000. If it finds one, it will return the row position of the account name relative to the range being searched. If there are no matches, it will return #NA.
28. In cell 19, write this formula:
=18+H9
This formula returns the real row position (relative to the sheet) of the found account name in column I ('ACCOUNT CREDIT). This row position is used by formulas in subsequent columns.
29. In cell J9, write this formula: =INDIRECT("DATA!A"&19) This formula will extract the source from column A in sheet DATA in row as stated in cell 19. This would be the source of the first occurrence of the account name in column ACCOUNT-CREDIT.
30. In cell K9, write this formula: =INDIRECT("DATA!B"&19) This formula will extract the date from column B and row as stated in cell 19 in sheet DATA. This would be the date of the first occurrence of the account name in column 'ACCOUNT-CREDIT'.
200
31. In cell L9, write this formula:
=INDIRECT("DATA!D"&19)
This formula will extract the source number from column D and the row is stated in cell 19 from sheet DATA. This would be the source number of the first occurrence of the account name in column ACCOUNT-CREDIT.
32. In cell M9, write this formula:
=INDIRECT("DATA! J"&19)
This formula will extract value from column RM-CREDIT from column J and the row is stated in cell 19 from sheet DATA. This would be the credit transaction value of the first occurrence of the account name in column ACCOUNT-CREDIT.
33. Copy down formulas in cells A9 to M9. The formulas will get the rows containing details of account as stated in cell C4. If you see an #NA in a row, it means there is no more rows containing the account name from sheet DATA.
NOTE
If you don't want to print anything in column A and B, you can hide the column before printing. Or, you can set the Print Area (Use menu File/Print Area/Set Print Area) before you print.
If you understand the mechanics of the formula and functions, you can change the design of the ledger to follow your own structure.
TIP
You can consolidate Credit entry details (in this case, in columns H to M) to appear below the Debit entry details (in this example, in columns A to F) by copying all values (not the formulas) into a separate sheet, and then sort the details by date. This will cause the data to appear in chronological order.
You can also have Credit entries appear below the Debit entries (or vice-versa) in the same column by combining the formulas:
=IF(MATCH(INDIRECT("C4"), INDIRECT("DATA!G"&B8+18": $G30000"),0))=#NA, MATCH(INDIRECT("C4"), INDIRECT("DATA!I"&18+18": $130000"),0)),"")
Sunday, October 4, 2026
Getting ledger details of an account Sample structure pg 195
24. Get Ledger Details Of An Account
Sometimes we want to get only some details of an account, not all the details available in sheet DATA. Here, we can use a formula in a separate sheet to get some transaction details involving an account. This is useful if we want to review transactions of an account.
We need to have a separate sheet to contain a structure of our ledger account.
Below is a sample structure:
A B C D E F G H I J K L M 1 2 PETTY CASH A ▼ 3 97 4 PETTY CASH A 37,078.02 5 6 DEBIT ENTRIES CREDIT ENTRIES 7 TOTAL 47,238.83 TOTAL (10.160.81) 8 1 SRC DATE SRC No. RM-DT 1 SRC Date SRC RM-CREDIT No 9 17 18 OPENING 01-Jan-01 13,238 308 309 PETTY CASH 06-Jan-01 01/1 (55.00) 10 368 386 BANK A 04-Jan-01 PV-1 5,000 1 310 PETTY CASH 06-Jan-01 02/1 (60.00) 11 9 395 BANK A 27-Jan-01 PV-10 5,000 1 311 PETTY CASH 08-Jan-01 02/1 (779.34) 12 20 415 BANK A 28-Feb-01 PV-10 5,000 1 312 PETTY CASH 08-Jan-01 04/1 (3,927.11) 13 3 418 BANK A 28-Feb-01 PV-13 7,000 1 313 PETTY CASH 12-Jan-01 05/1 (131.20) 14 21 439 BANK A 30-Mar-01 PV-11 7,000 1 314 PETTY CASH 12-Jan-01 06/1 (862.45) 15 22 461 BANK A 22-Apr-01 PV.9 5,000 1 315 PETTY CASH 12-Jan-01 07/1 (304,00) 16 #N/A #N/A #N/A #N/A #N/A #N/A 1 316 PETTY CASH 12-Jan-01 08/1 (93.80) 17 #N/A #N/A #N/A #N/A #N/A #N/A 1 317 PETTY CASH 12-Jan-01 09/1 (575.00) 18 #N/A #N/A #N/A #N/A #N/A #N/A 1 318 PETTY CASH 12-Jan-01 10/1 (19.80) 19 #N/A #N/A #N/A #N/A #N/A #N/A 1 319 PETTY CASH 06-Feb-01 01/2 (100.00) 20 #N/A #N/A #N/A #N/A #N/A #N/A 1 320 PETTY CASH 06-Feb-01 02/2 (1,865.00) 21 #N/A #N/A #N/A #N/A #N/A #N/A 1 321 PETTY CASH 06-Feb-01 02/2 (800.00) 22 #N/A #N/A #N/A #N/A #N/A #N/A 1 322 PETTY CASH 09-Feb-01 04/2 (358.11) 23 #N/A #N/A #N/A #N/A #N/A #N/A 1 323 PETTY CASH 09-Feb-01 05/2 (30.00) 24 #N/A #N/A #N/A #N/A #N/A #N/A 1 324 PETTY CASH 09-Feb-01 06/2 (60.00) 25 #N/A #N/A #N/A #N/A #N/A #N/A 1 325 PETTY CASH 09-Feb-01 07/2 (140.00) 26
Saturday, October 3, 2026
Statement of accounts Managing Combo Box pg 190
How to put a combo box into a sheet:
Click menu View/Toolbars/Form.
A floating toolbar (Form Toolbar) will appear.
Look for the Combo Box tool (sometimes called the 'Drop-down' or 'Drop-Down Combo') and click on it once.
Point your mouse to where you want to put the combo box. Click on the location once. A combo box will appear there.
TIP
You can resize the combo box by clicking on it once and then dragging on a handle that appears. You can relocate the combo box by dragging it.
You can dismiss or close the Form Toolbar for now, so not to clutter your sheet.
Setting Combo Box to load items
To set the combo box to that when we click on it, a list will appear and we can select an item from the list. In this case, we want to select a customer name from it. This requires us to have a sheet which contain a list of customers, let's say in sheet CUSTOMERS and the list is in the range of say A2 to A100. We want to make the list of customers in sheet CUSTOMERS appear in the combo box. Here is how:
Right-click on the combo box. A floating menu will appear. Select Format Control to open the Format control dialog box. Click on the tab Control. In the box on the right of label Input Range, enter the address where the list of customers is located (in this case, it is CUSTOMERS!A2:A100).
Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Input Range setting to take effect. Now if you click on the combo, a list will appear and you can click on one item from the list to select it. The list will disappear and the selected item will appear in the combo.
Show more than 8 items
When you click on the combo, by default only 8 items will be visible at a time. You can set to display more. Right-click on the combo once. A floating menu will appear. Select Format Control To open the Format control dialog box. Click on tab Control. In the box on the right of label Drop Down Lines, enter a number, say 20. Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Drop Down Lines setting to take effect. Now if you click on the combo, the number of items that appear is no longer 8 but 20.
No printing of Combo box
Usually, we don't want the combo box to get printed when we print the sheet because it is ugly. The steps to make it non-printable is as follows:
Right-click on the combo once. A floating menu will appear. Select Format Control to open the Format control dialog box. Click on the tab Properties. In the box on the left of label Print Object, if there is a check-mark, remove it by clicking it once. Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Print Object setting to take effect. Now whenever you print the sheet, the combo and its content will not get printed.
Linking Combo box to a cell
In this example, we will use cell Al as a Link Cell to the Combo Box. We will set it so that when we select an item from the combo box, cell A1 will show the position of the item as it appears in the list in the combo box. This position is also pointing to the position of the item in the list in sheet CUSTOMERS. This position number is useful for the formula in next paragraph. To set cell A1 as the Link Cell, the steps are:
Right-click on the combo once. A floating menu will appear. Select Format Control to open the Format control dialog box. Click on the tab Control. In the box on the right of label Cell link, enter: A1. Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Cell link setting to take effect. Now if you click on the combo and select an item, the position of the item in the list will appear in cell A1.
Click menu View/Toolbars/Form.
A floating toolbar (Form Toolbar) will appear.
Look for the Combo Box tool (sometimes called the 'Drop-down' or 'Drop-Down Combo') and click on it once.
Point your mouse to where you want to put the combo box. Click on the location once. A combo box will appear there.
TIP
You can resize the combo box by clicking on it once and then dragging on a handle that appears. You can relocate the combo box by dragging it.
You can dismiss or close the Form Toolbar for now, so not to clutter your sheet.
Setting Combo Box to load items
To set the combo box to that when we click on it, a list will appear and we can select an item from the list. In this case, we want to select a customer name from it. This requires us to have a sheet which contain a list of customers, let's say in sheet CUSTOMERS and the list is in the range of say A2 to A100. We want to make the list of customers in sheet CUSTOMERS appear in the combo box. Here is how:
Right-click on the combo box. A floating menu will appear. Select Format Control to open the Format control dialog box. Click on the tab Control. In the box on the right of label Input Range, enter the address where the list of customers is located (in this case, it is CUSTOMERS!A2:A100).
Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Input Range setting to take effect. Now if you click on the combo, a list will appear and you can click on one item from the list to select it. The list will disappear and the selected item will appear in the combo.
Show more than 8 items
When you click on the combo, by default only 8 items will be visible at a time. You can set to display more. Right-click on the combo once. A floating menu will appear. Select Format Control To open the Format control dialog box. Click on tab Control. In the box on the right of label Drop Down Lines, enter a number, say 20. Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Drop Down Lines setting to take effect. Now if you click on the combo, the number of items that appear is no longer 8 but 20.
No printing of Combo box
Usually, we don't want the combo box to get printed when we print the sheet because it is ugly. The steps to make it non-printable is as follows:
Right-click on the combo once. A floating menu will appear. Select Format Control to open the Format control dialog box. Click on the tab Properties. In the box on the left of label Print Object, if there is a check-mark, remove it by clicking it once. Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Print Object setting to take effect. Now whenever you print the sheet, the combo and its content will not get printed.
Linking Combo box to a cell
In this example, we will use cell Al as a Link Cell to the Combo Box. We will set it so that when we select an item from the combo box, cell A1 will show the position of the item as it appears in the list in the combo box. This position is also pointing to the position of the item in the list in sheet CUSTOMERS. This position number is useful for the formula in next paragraph. To set cell A1 as the Link Cell, the steps are:
Right-click on the combo once. A floating menu will appear. Select Format Control to open the Format control dialog box. Click on the tab Control. In the box on the right of label Cell link, enter: A1. Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Cell link setting to take effect. Now if you click on the combo and select an item, the position of the item in the list will appear in cell A1.
Friday, October 2, 2026
Statement of accounts Explanation pg 188 189 190 191 192 193 194
Here is a quick explanation of the structure (row by row):
1. Notice the box with a little downward-pointing arrowhead. This is called a Combo Box. Here is how to put a combo box into a sheet:
Click menu View/Toolbars/Form.
A floating toolbar (Form Toolbar) will appear.
Look for the Combo Box tool (sometimes called the 'Drop-down' or 'Drop-Down Combo') and click on it once.
Point your mouse to where you want to put the combo box. Click on the location once. A combo box will appear there.
TIP
You can resize the combo box by clicking on it once and then dragging on a handle that appears. You can relocate the combo box by dragging it.
You can dismiss or close the Form Toolbar for now, so not to clutter your sheet.
3. The formulas in cells C8 to C12 expects that the list of customers in sheet CUSTOMERS is structured as below (i.e. titles in row 1, data starts from row 2 in column B to column G):
=INDEX(CUSTOMERS!B2:B100,A1)
This formula will retrieve the name of the customer we selected from the combo, not from the combo itself but by searching through the list in sheet CUSTOMERS in range B2:B100 and by relying on the position that appear in cell A1.
5. In cell C8, write this formula:
=VLOOKUP(A2, Customers!B1:G100,2,FALSE)
This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and then return the item in the 2nd column of the list (which is the full name).
191
6. In cell C9, write this formula:
=VLOOKUP(A2, Customers!B1:G100,3,FALSE)
This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and then return the item in the 3rd column in the list (which is the 1st part of the address).
7. In cell C10, write this formula:
=VLOOKUP(A2, Customers!B1:G100,4,FALSE)
This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and return the item in the 4th column in the list (which is the 2nd part of the address).
8. In cell C11, write this formula:
=VLOOKUP(A2, Customers!B1: G100,5,FALSE)
This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and return the item in the 5th column in the list (which is the 3rd part of the address).
9. This In cell C12, write this formula:
=VLOOKUP(A2, Customers!B1: G100,6, FALSE) formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and return the item in the 6th column in the list (which is the 4th part of the address).
10. In cell C14, write TOTAL.
11. In cell G14, write a formula to sum values of open (unpaid) invoices in cells G17 downward.
12. In cell B16, write the figure 1. This number will be used in the formula in cells A17 and B17. This actually indicates the row number where the search for unpaid invoices starts.
13. In cell C16, write Date. This serves as a label.
14. In cell D16, write Inv No. This serves as a label.
15. In cell E16, write RM. This serves as a label.
16. In cell F16, write Status. This serves as a label.
17. In cell G16, write Open only. This serves as a label.
192
18. In cell A17, write this formula:
=MATCH($A$2, INDIRECT("DATA10"&816+1&":$030000"),0)
This formula will find the first occurrence of the customer name available in cell A2 from column G in the data area in sheet DATA starting from row as stated in cell B16 to row 30000. If it finds one, it will return the row position of the customer name relative to the range being searched. If there are no matches, it will return #NA.
19. In cell B17, write this formula:
=B16+B17
This formula returns the real row position (relative to the sheet) of the found customer name. This row position is used by the formula in subsequent columns.
20. In cell C17, write this formula:
=INDIRECT("DATA!B"&B17)
This formula will extract the date from column B in sheet DATA in row as stated in cell B17. This would be the date of the first occurrence of the customer name.
21. In cell D17, write this formula:
=INDIRECT("DATAID"&B17)
This formula will extract the Invoice Number from column D and row as stated in cell B17 in sheet DATA. This would be the Invoice Number of the first occurrence of the customer name.
22. In cell E17, write this formula:
=INDIRECT("DATA!H"&B17)
This formula will extract the RM value from column H and row as stated in cell B17 from sheet DATA. This would be the RM value of the first occurrence of the customer name.
23. In cell F17, write this formula:
=INDIRECT("DATA!N"&B17). This formula will extract the Status from column N and row as stated in cell B17 from sheet DATA. This would be the invoice status (whether paid or unpaid) of the first occurrence of the customer name.
24. In cell G17, write this formula:
=IF(F17="pd",".", E17)
This formula will extract the RM in cell E17 if it has not been paid (by relying on cell F17). If already paid, it will return "-".
24.
Copy down formulas in cells A17 to G17. The formulas will get the rows containing details of unpaid invoices to customer stated in cell A2. If you see a #NA in a row, it means there are no more rows containing the customer name.
NOTE
If you do not want to print anything in column A and B, you can hide the columns before printing. Or, you can set the Print Area (Use menu File/Print Area/Set Print Area) before you print.
If you understand the mechanics of the formula and functions, you can change the design of the statement to follow your own structure.
1. Notice the box with a little downward-pointing arrowhead. This is called a Combo Box. Here is how to put a combo box into a sheet:
Click menu View/Toolbars/Form.
A floating toolbar (Form Toolbar) will appear.
Look for the Combo Box tool (sometimes called the 'Drop-down' or 'Drop-Down Combo') and click on it once.
Point your mouse to where you want to put the combo box. Click on the location once. A combo box will appear there.
TIP
You can resize the combo box by clicking on it once and then dragging on a handle that appears. You can relocate the combo box by dragging it.
You can dismiss or close the Form Toolbar for now, so not to clutter your sheet.
3. The formulas in cells C8 to C12 expects that the list of customers in sheet CUSTOMERS is structured as below (i.e. titles in row 1, data starts from row 2 in column B to column G):
A B C D E F G 1 NAME FULL NAME ADDRESS-1 ADDRESS-2 ADDRESS-3 ADDRESS-4 2 CUSTOMER A DOL FATAH A20 JALAN 5 MUAR JOHOR 3 CUSTOMER B ABU DAUD A21 JALAN 6 MANTIN NEGRI 4 CUSTOMER C ALI GOOCHI A22 JALAN 7 KLUANG JOHOR 6 CUSTOMER D SITI NOOR A23 JALAN 8 JASIN MELAKA 6 CUSTOMER E MIN TUTU A24 JALAN 9 GOMBAK SELANGOR 74. In cell A2, write this formula:
=INDEX(CUSTOMERS!B2:B100,A1)
This formula will retrieve the name of the customer we selected from the combo, not from the combo itself but by searching through the list in sheet CUSTOMERS in range B2:B100 and by relying on the position that appear in cell A1.
5. In cell C8, write this formula:
=VLOOKUP(A2, Customers!B1:G100,2,FALSE)
This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and then return the item in the 2nd column of the list (which is the full name).
191
6. In cell C9, write this formula:
=VLOOKUP(A2, Customers!B1:G100,3,FALSE)
This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and then return the item in the 3rd column in the list (which is the 1st part of the address).
7. In cell C10, write this formula:
=VLOOKUP(A2, Customers!B1:G100,4,FALSE)
This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and return the item in the 4th column in the list (which is the 2nd part of the address).
8. In cell C11, write this formula:
=VLOOKUP(A2, Customers!B1: G100,5,FALSE)
This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and return the item in the 5th column in the list (which is the 3rd part of the address).
9. This In cell C12, write this formula:
=VLOOKUP(A2, Customers!B1: G100,6, FALSE) formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and return the item in the 6th column in the list (which is the 4th part of the address).
10. In cell C14, write TOTAL.
11. In cell G14, write a formula to sum values of open (unpaid) invoices in cells G17 downward.
12. In cell B16, write the figure 1. This number will be used in the formula in cells A17 and B17. This actually indicates the row number where the search for unpaid invoices starts.
13. In cell C16, write Date. This serves as a label.
14. In cell D16, write Inv No. This serves as a label.
15. In cell E16, write RM. This serves as a label.
16. In cell F16, write Status. This serves as a label.
17. In cell G16, write Open only. This serves as a label.
192
18. In cell A17, write this formula:
=MATCH($A$2, INDIRECT("DATA10"&816+1&":$030000"),0)
This formula will find the first occurrence of the customer name available in cell A2 from column G in the data area in sheet DATA starting from row as stated in cell B16 to row 30000. If it finds one, it will return the row position of the customer name relative to the range being searched. If there are no matches, it will return #NA.
19. In cell B17, write this formula:
=B16+B17
This formula returns the real row position (relative to the sheet) of the found customer name. This row position is used by the formula in subsequent columns.
20. In cell C17, write this formula:
=INDIRECT("DATA!B"&B17)
This formula will extract the date from column B in sheet DATA in row as stated in cell B17. This would be the date of the first occurrence of the customer name.
21. In cell D17, write this formula:
=INDIRECT("DATAID"&B17)
This formula will extract the Invoice Number from column D and row as stated in cell B17 in sheet DATA. This would be the Invoice Number of the first occurrence of the customer name.
22. In cell E17, write this formula:
=INDIRECT("DATA!H"&B17)
This formula will extract the RM value from column H and row as stated in cell B17 from sheet DATA. This would be the RM value of the first occurrence of the customer name.
23. In cell F17, write this formula:
=INDIRECT("DATA!N"&B17). This formula will extract the Status from column N and row as stated in cell B17 from sheet DATA. This would be the invoice status (whether paid or unpaid) of the first occurrence of the customer name.
24. In cell G17, write this formula:
=IF(F17="pd",".", E17)
This formula will extract the RM in cell E17 if it has not been paid (by relying on cell F17). If already paid, it will return "-".
24.
Copy down formulas in cells A17 to G17. The formulas will get the rows containing details of unpaid invoices to customer stated in cell A2. If you see a #NA in a row, it means there are no more rows containing the customer name.
NOTE
If you do not want to print anything in column A and B, you can hide the columns before printing. Or, you can set the Print Area (Use menu File/Print Area/Set Print Area) before you print.
If you understand the mechanics of the formula and functions, you can change the design of the statement to follow your own structure.
Statement of accounts Sample structure pg 188
23. Statement Of Accounts
Usually, a business will issue a statement of accounts to its customers at regular intervals, such as on monthly basis to show amounts still owed by its customers and the details.
We need to have a separate sheet to contain a structure of our statement of accounts.
Below is a sample structure:
188
Usually, a business will issue a statement of accounts to its customers at regular intervals, such as on monthly basis to show amounts still owed by its customers and the details.
We need to have a separate sheet to contain a structure of our statement of accounts.
Below is a sample structure:
A B C D E F G 1 2 OUR LETTERHEAD 2 CUSTOMER B 3 123 Jalan Lumba Lumba 4 KL 5 CUSTOMER B 6 TO 7 8 ABU DAUD 9 A21 10 JALAN 6 11 MANTIN 12 NEGRI 13 14 TOTAL 1,183,058.28 15 16 1 Date Inv No RM Status Open only 17 30 31 01-Jan-01 0 200,000.00 pd 18 248 279 11-Jan-01 0911-1023 332,356.74 pd 19 7 286 05-Feb-01 1005-1030 361,509.41 Open 361,509.41 20 9 295 06-Mar-01 1106-1039 474,081.73 Open 474,081.73 21 8 303 02-Apr-01 1202-1047 347,467.14 Open 347,467.14 22 #N/A #N/A #N/A #N/A #N/A #N/A #N/A 23 24 25 Yours truly 26 27 signed 28 29 SALES MANAGER 30
188
Thursday, October 1, 2026
Monitoring cash in bank pg 184 185 186 187
22. Monitoring Cash Balance
We can extend the use of the file to monitor cash in bank balances.
We can use a separate sheet for this purpose. So, create a sheet for this purpose. You can name it with any name you think fit.
Here is a suggested structure for the sheet:
The explanation of the structure:
Cell A1: The name of the business.
Cell A2: (Blank)
Cell A3: The title of the statement.
Cell A4: Text As at (date).
Cell A5: Text BANK ACCOUNT NAME ..>>>.
Cell A6: Text Current Balance (as per ledger).
Cell A7: Text Payments/Receipts on hold.
Cell A8: Text CASH AVAILABLE.
Cell A9: (Blank)
Cell A10: Text Expected/recurring payments/receipts in the future.
Cell A11: (Blank)
Cell A12: (Blank)
Cell A13: From this cell downwards, list down the expected/recurring payments/receipts in the future.
Cell A14: (Blank)
Cell A15: (Blank)
Cell A16: (Blank)0
Cell A17: This sample assumes that this is the last cell for the list of the expected/recurring payments/receipts in the future.
Cell A18: (Blank)
Cell A19: Text EXPECTED NET RECEIPTS/PAYMENTS.
Cell A20: (Blank)
Cell A21: Text EXPECTED BALANCE (EXCESS/DEFICIT).
Cell B22: Text NOTE. For each cell below it, write any note for an item appearing on the same row on the cell on the left.
Cell C22: Text DUE DATE. For each cell below it, write the due date for each item appearing on the same row on the cell on the left.
Cell D5: In this cell and cells in subsequent columns, write the account names for each bank. Make sure the spelling is exactly the same with what is available in sheet TB. Cell D6: Contains this formula (in one line):
=SUMIF(INDIRECT("DATA!G5:G30000"), INDIRECT("D4"), INDIRECT("DATA! H5: H30000"))+
SUMIF (INDIRECT("DATA!I5:I30000"), INDIRECT("D4"), INDIRECT("DATA!J5:J30000"))
This formula will compute the current balance of the bank name in cell D6 from data on sheet DATA. That's why it is important to ensure the exact spelling.
185
Copy this formula to the next column until the last column containing the bank name (in this sample, you do so in cell E6 and F6).
Cell D7: Contains this formula (in one line):
=SUMIF(INDIRECT("DATA!G5: G30000"), INDIRECT("D4"), INDIRECT("DATA!05:0 30000"))+
SUMIF(INDIRECT("DATA!15:130000"), INDIRECT("D4"), INDIRECT("D ATA!05:030000"))
This formula will compute the receipt/payments on hold as indicated in the column "HOLD" on sheet DATA.
Copy this formula to the next column until the last column containing the bank name.
Cell D8: Contains this formula:
=D6+D7
This formula will compute the current balance plus payments/receipts on hold. Copy this formula to the next column until the last column containing the bank name.
Cell D13: From this cell downwards, state the amount of the expected/recurring payments/receipts in the future. Repeat for each of the next columns until the last bank name.
Cell D17: In this book, it is assumed that this is the last cell for the amount of the expected/recurring payments/receipts in the future.
Cell D19: Contains formula which computes the total amount of expected payment/receipts. Copy this formula to the next column until the last column containing the bank name.
186
Cell D21: Contains formula which computes the expected balance:
=D8+D19
Copy this formula to the next column until the last column containing the bank name. Assume column G is the last column for a bank name.
Cell G5: Write TOTAL. Cell G6: Write this formula:
=D6+E6+F6.
Copy this formula down until row 21.
Once you understand the formulas, you can change the structure to suit your own needs. The most important thing is how the current balance of each bank is captured using the formulas. You then adjust the balance with items you think is logical.
We can extend the use of the file to monitor cash in bank balances.
We can use a separate sheet for this purpose. So, create a sheet for this purpose. You can name it with any name you think fit.
Here is a suggested structure for the sheet:
A B C D E F G 1 SATU DUA (M) SDN BHD 2 3 CASH BALANCE ESTIMATE 4 As at: 5 BANK ACCOUNT NAME..>>> BANK A BANK B BANK C TOTAL 6 Current balance (as per ledger) (126,655.27) (778.279.36) 42.117.17 (862.817.46) 7 Payment/Receipt On Hold 0.00 0.00 0.00 0.00 8 CASH AVAILABLE (126,655.27) (778,279.36) 42.117.17 (862,817.46) 9و 10 Expected/recurring receipt/ payment in the foreseeable future: 11 12 NOTE DUE DATE 13 SI Rental Every 2nd 14 Salary Every 30th 50,000.00 0.00 0.00 50,000.00 15 SI-Hire Purchase Every 25th 700.00 0.00 0.00 700.00 16 Short-term loan due 12 this month 200,000.00 0.00 0.00 200,000.00 17 LC due Due 6/5/02 350,000.00 0.00 0.00 350,000,00 18 19 EXPECTED NET RECEIPT/PAYMENT 601,700.00 0.00 0.00 601,700.00 20 21 EXPECTED BALANCE (EXCESS/DEFICIT) 475,044.73 (778.279.36) 42.117.17 (261,117.46) 22This sample shows the current balance of each bank account and its total. The balances are then adjusted for expected payment or receipts in the foreseeable future so that we can forecast our financial state.
The explanation of the structure:
Cell A1: The name of the business.
Cell A2: (Blank)
Cell A3: The title of the statement.
Cell A4: Text As at (date).
Cell A5: Text BANK ACCOUNT NAME ..>>>.
Cell A6: Text Current Balance (as per ledger).
Cell A7: Text Payments/Receipts on hold.
Cell A8: Text CASH AVAILABLE.
Cell A9: (Blank)
Cell A10: Text Expected/recurring payments/receipts in the future.
Cell A11: (Blank)
Cell A12: (Blank)
Cell A13: From this cell downwards, list down the expected/recurring payments/receipts in the future.
Cell A14: (Blank)
Cell A15: (Blank)
Cell A16: (Blank)0
Cell A17: This sample assumes that this is the last cell for the list of the expected/recurring payments/receipts in the future.
Cell A18: (Blank)
Cell A19: Text EXPECTED NET RECEIPTS/PAYMENTS.
Cell A20: (Blank)
Cell A21: Text EXPECTED BALANCE (EXCESS/DEFICIT).
Cell B22: Text NOTE. For each cell below it, write any note for an item appearing on the same row on the cell on the left.
Cell C22: Text DUE DATE. For each cell below it, write the due date for each item appearing on the same row on the cell on the left.
Cell D5: In this cell and cells in subsequent columns, write the account names for each bank. Make sure the spelling is exactly the same with what is available in sheet TB. Cell D6: Contains this formula (in one line):
=SUMIF(INDIRECT("DATA!G5:G30000"), INDIRECT("D4"), INDIRECT("DATA! H5: H30000"))+
SUMIF (INDIRECT("DATA!I5:I30000"), INDIRECT("D4"), INDIRECT("DATA!J5:J30000"))
This formula will compute the current balance of the bank name in cell D6 from data on sheet DATA. That's why it is important to ensure the exact spelling.
185
Copy this formula to the next column until the last column containing the bank name (in this sample, you do so in cell E6 and F6).
Cell D7: Contains this formula (in one line):
=SUMIF(INDIRECT("DATA!G5: G30000"), INDIRECT("D4"), INDIRECT("DATA!05:0 30000"))+
SUMIF(INDIRECT("DATA!15:130000"), INDIRECT("D4"), INDIRECT("D ATA!05:030000"))
This formula will compute the receipt/payments on hold as indicated in the column "HOLD" on sheet DATA.
Copy this formula to the next column until the last column containing the bank name.
Cell D8: Contains this formula:
=D6+D7
This formula will compute the current balance plus payments/receipts on hold. Copy this formula to the next column until the last column containing the bank name.
Cell D13: From this cell downwards, state the amount of the expected/recurring payments/receipts in the future. Repeat for each of the next columns until the last bank name.
Cell D17: In this book, it is assumed that this is the last cell for the amount of the expected/recurring payments/receipts in the future.
Cell D19: Contains formula which computes the total amount of expected payment/receipts. Copy this formula to the next column until the last column containing the bank name.
186
Cell D21: Contains formula which computes the expected balance:
=D8+D19
Copy this formula to the next column until the last column containing the bank name. Assume column G is the last column for a bank name.
Cell G5: Write TOTAL. Cell G6: Write this formula:
=D6+E6+F6.
Copy this formula down until row 21.
Once you understand the formulas, you can change the structure to suit your own needs. The most important thing is how the current balance of each bank is captured using the formulas. You then adjust the balance with items you think is logical.
Segregation of duties and file sharing pg 182 183
21. Segregation Of Duties & File Sharing
Segregation Of Duties
Sometimes, sources of transactions are handled by different personnel. For example, a bank book, petty cash book, purchases, and sales are separately handled by different personnel. Then, there is one person who maintains the ledger and he/she has to get data from the other staff. Each staff is provided with a computer and works with Microsoft Excel in each computer. The person who maintains the ledger will need to import the data from the other sources. He can work faster if each computer is connected via a computer network and each source uses Microsoft Excel and has the same structure for sheet DATA.
Rather than each staff having a separate file with similar structure, you can just keep one file and everybody works on that file, i.e. that file will be shared. Everybody will update the same file.
To enable sharing of one file:
1. Put the file in a folder in a computer in a network, and set the folder as shared and set the computer as accessible by everyone involved.
2. Then, open the file to enable us to set its sharing attributes.
3. Click menu Tools/Share Workbook... The Share Workbook dialog will appear:
182
4. Click the tab Editing.
5. Put a mark in the box on the left of the option Allow changes by more than one user at the same time...
6. Click the tab Advanced. You will see more options.
7. Under Conflicting changes between users, select the option Ask me which changes win.
8. Under Update changes, select the option When file is saved.
9. Click OK.
Every user will open the same file. So, there must be someone who oversees the file to make sure the structure of the file is intact and data is filled properly and there is no undesirable overwriting of data.
File Sharing Through Networking And The Internet
Through networking, this file can be made available to other interested persons e.g. the Accountant and the General Manager. The file can be set for viewing only, i.e. no changes are permitted. However, they can be allowed to make a copy of the data or sheet or file so that they can make analysis of the data on their own rather than wait for their subordinates to provide them.
The current technology also allows an Excel file to be shared via the Internet, either by opening the file directly from Internet Explorer (or some other web browser) or by converting the file into an HTML format (use menu File/Save As Web Page) or sending the file through e-mail (after compression).
183
Segregation Of Duties
Sometimes, sources of transactions are handled by different personnel. For example, a bank book, petty cash book, purchases, and sales are separately handled by different personnel. Then, there is one person who maintains the ledger and he/she has to get data from the other staff. Each staff is provided with a computer and works with Microsoft Excel in each computer. The person who maintains the ledger will need to import the data from the other sources. He can work faster if each computer is connected via a computer network and each source uses Microsoft Excel and has the same structure for sheet DATA.
Rather than each staff having a separate file with similar structure, you can just keep one file and everybody works on that file, i.e. that file will be shared. Everybody will update the same file.
To enable sharing of one file:
1. Put the file in a folder in a computer in a network, and set the folder as shared and set the computer as accessible by everyone involved.
2. Then, open the file to enable us to set its sharing attributes.
3. Click menu Tools/Share Workbook... The Share Workbook dialog will appear:
Share Workbook Editing | Advanced ☑ Allow changes by more than one user at the same time. This also allows workbook merging. Who has this workbook open now: Authorised Desktop PC User (Exclusive)-6/11/2002 1:30 PM OK Cancel
182
4. Click the tab Editing.
5. Put a mark in the box on the left of the option Allow changes by more than one user at the same time...
6. Click the tab Advanced. You will see more options.
7. Under Conflicting changes between users, select the option Ask me which changes win.
8. Under Update changes, select the option When file is saved.
9. Click OK.
Every user will open the same file. So, there must be someone who oversees the file to make sure the structure of the file is intact and data is filled properly and there is no undesirable overwriting of data.
File Sharing Through Networking And The Internet
Through networking, this file can be made available to other interested persons e.g. the Accountant and the General Manager. The file can be set for viewing only, i.e. no changes are permitted. However, they can be allowed to make a copy of the data or sheet or file so that they can make analysis of the data on their own rather than wait for their subordinates to provide them.
The current technology also allows an Excel file to be shared via the Internet, either by opening the file directly from Internet Explorer (or some other web browser) or by converting the file into an HTML format (use menu File/Save As Web Page) or sending the file through e-mail (after compression).
183
Saturday, July 25, 2026
Friday, July 24, 2026
Front cover
BOOK-KEEPING WITH MICROSOFT EXCEL
You can use Microsoft Excel for all your book-keeping tasks, from recording raw data to preparation of Trial Balance, Balance Sheet, Profit & Loss statement, and other reports. This book shows you how.
ISMAIL HASHIM
You can use Microsoft Excel for all your book-keeping tasks, from recording raw data to preparation of Trial Balance, Balance Sheet, Profit & Loss statement, and other reports. This book shows you how.
ISMAIL HASHIM
Friday, August 30, 2024
Cover page
Excel 95, 97, 2000, XP
Microsoft Excel has long been used to help in book-keeping tasks. Many businesses rely on Excel to manage their book-keeping chores.
There are many different ways to implement it. This book shows one way, may be a better way.
In this book, formulas are used to automate updating of Trial Balance, Balance Sheet, Profit & Loss and other reports. Only a few formulas are used. This books shows what are the formulas. No programming at all. With AutoFilter, CustomFilter, AdvanceFilter and PivotTable operations, we can get the desired listings, summaries, sub-totals and totals immediately.
Just follow the guide in the book to build the data structure in an Excel file, set formulas and functions and you are ready to enter data. If you don't want to build from scratch, use a sample file provided with this book. This book is also serve as an extensive manual as your immediate reference while doing your book-keeping work.
Book-keepers, Accounts Assistants, Accounts Executives, Accountants, Accountig students would appreciate this book. Others with basic knowledge in the concept of debit and credit should be able to understand and use this material.
Microsoft Excel has long been used to help in book-keeping tasks. Many businesses rely on Excel to manage their book-keeping chores.
There are many different ways to implement it. This book shows one way, may be a better way.
In this book, formulas are used to automate updating of Trial Balance, Balance Sheet, Profit & Loss and other reports. Only a few formulas are used. This books shows what are the formulas. No programming at all. With AutoFilter, CustomFilter, AdvanceFilter and PivotTable operations, we can get the desired listings, summaries, sub-totals and totals immediately.
Just follow the guide in the book to build the data structure in an Excel file, set formulas and functions and you are ready to enter data. If you don't want to build from scratch, use a sample file provided with this book. This book is also serve as an extensive manual as your immediate reference while doing your book-keeping work.
Book-keepers, Accounts Assistants, Accounts Executives, Accountants, Accountig students would appreciate this book. Others with basic knowledge in the concept of debit and credit should be able to understand and use this material.
Tuesday, July 30, 2024
Introduction
Introduction
Here I show how to use Microsoft Excel as a complete book-keeping accounting tool.
Useful for freelancers, book-keeping service provider, small businesses, students and for those who want to work from home.
VBA is NOT used at all.
Developing A Book-Keeping System With Microsoft Excel
Microsoft Excel has long been used to help in book-keeping and accounting tasks. Many businesses rely on Excel to manage their book-keeping chores.
There are many different ways to implement it. This book shows one way, perhaps better way It shows how you can use Microsoft Excel to do complete book-keeping tasks. Book-keeping tasks here means recording of data from its sources, preparation of Trial balance, Balance Sheet, Profit & Loss statement, and other reports.
You will see how formulas can be used to automate updating of your Trial Balance, Balance Sheet, Profit & Loss, and other reports. Only a few formulas are used. This book shows you all the formulas. No programming at all is needed. With Excel's AutoFilter, CustomFilter AdvanceFilter and PivotTable operations, we can get the desired listings, summaries, and totals immediately.
Just follow the steps outlined in this book to build the data structure in an Excel file, set the formulas and functions, and you will be ready to enter data. If you do not want to build the file from scratch, you can use the sample file provided with this book. This book also serves as an extensive user's manual which you can use as your immediate reference as you perform your book-keeping tasks.
Book-keepers, Accounts Assistants, Accounts Executives, Accountants, and Accounting students will find this book of interest. Other users, with a basic knowledge of the concept of Debit and Credit should be able to understand and use the material presented in this book.
Ismail bin Hashim
ibh1@live.com
Thursday, July 25, 2024
Some Book-Keeping Functions That Can Be Done Using Microsoft Excel
Some Book-Keeping Functions That Can Be Done Using Microsoft Excel
Here are some of the book-keeping functions that can be performed using Microsoft Excel, and that will be shown in this book:
To record raw data.
To automate updating of the Trial Balance statement.
To automate updating of the Balance Sheet statement.
To automate updating of the Profit & Loss statement.
To generate and update Debtors Ageing report.
To generate and update Creditors Ageing report.
To generate and update monthly Sales summary report.
To generate and update monthly Purchases summary report.
To generate and update monthly Items purchased summary report.
To generate and update monthly Items sold summary report.
To generate data for preparing Statement of account.
To help in preparation of bank reconciliation.
5 To help in cash flow monitoring.
Filtering data to see only list of data that we want to see.
Are these functions sufficient for your needs?
Many other reports, detailed or summary, can be produced as long as we know how to use Excel's Filtering function, PivotTable function, and formulas.
Here are some of the book-keeping functions that can be performed using Microsoft Excel, and that will be shown in this book:
To record raw data.
To automate updating of the Trial Balance statement.
To automate updating of the Balance Sheet statement.
To automate updating of the Profit & Loss statement.
To generate and update Debtors Ageing report.
To generate and update Creditors Ageing report.
To generate and update monthly Sales summary report.
To generate and update monthly Purchases summary report.
To generate and update monthly Items purchased summary report.
To generate and update monthly Items sold summary report.
To generate data for preparing Statement of account.
To help in preparation of bank reconciliation.
5 To help in cash flow monitoring.
Filtering data to see only list of data that we want to see.
Are these functions sufficient for your needs?
Many other reports, detailed or summary, can be produced as long as we know how to use Excel's Filtering function, PivotTable function, and formulas.
Saturday, July 20, 2024
Requirements
REQUIREMENTS
What are required to successfully implement book-keeping with Microsoft Excel ?
1. Initially, an empty Microsoft Excel file (Excel 95, 97, 2000, XP).
2. A basic book-keeping knowledge (particularly understand the concept of Debit and Credit).
3. A basic knowledge and hands-on experience in Microsoft Excel.
What are required to successfully implement book-keeping with Microsoft Excel ?
1. Initially, an empty Microsoft Excel file (Excel 95, 97, 2000, XP).
2. A basic book-keeping knowledge (particularly understand the concept of Debit and Credit).
3. A basic knowledge and hands-on experience in Microsoft Excel.
Friday, July 29, 2011
Why use Microsoft Excel for accounting
Reasons :
From staff view : If allowed and wish, he can bring home the work and finish at home, so the next day, there would be more time for him to do other works.
From Head of Dept view : He can ask for a copy of the file and look for it at home. At home, he can jot any query to be presented to his staff in next day.
If the file is in cloud, both persons ie staff and Head of Accounting Dept can see it live.
If the staff is knowledgable in Excel, he can use Excel for accounting work, thus he can save money for the company he works, ie by using Excel fully rather than buying accounting software which cost thousands of Ringgit, and save cost of periodic maintenance of the software.
From staff view : If allowed and wish, he can bring home the work and finish at home, so the next day, there would be more time for him to do other works.
From Head of Dept view : He can ask for a copy of the file and look for it at home. At home, he can jot any query to be presented to his staff in next day.
If the file is in cloud, both persons ie staff and Head of Accounting Dept can see it live.
If the staff is knowledgable in Excel, he can use Excel for accounting work, thus he can save money for the company he works, ie by using Excel fully rather than buying accounting software which cost thousands of Ringgit, and save cost of periodic maintenance of the software.
Thursday, July 28, 2011
Table of Content
TABLE OF CONTENT
CHAPTER 1 – BOOK-KEEPING FUNCTIONS THAT CAN BE DONE USING EXCEL
CHAPTER 2 – REQUIREMENTS FOR IMPLEMENTATION OF BOOK-KEEPING WITH EXCEL
BUILDING THE STRUCTURE
CHAPTER 3 – STEP 1 – CREATE A FOLDER (DIRECTORY)
CHAPTER 4 – STEP 2 – CREATE AND SET A FILE (WORKBOOK)
CHAPTER 5 – STEP 3 – SET THE WORKSHEETS IN THE FILE
CHAPTER 6 – STEP 4 – SET THE STRUCTURE IN THE SHEET ‘DATA’
CHAPTER 7 – STEP 5 – SET THE STRUCTURE IN THE SHEET ‘TB’
GUIDE TO INPUTTING DATA
CHAPTER 8 – GUIDE TO INPUTTING DATA IN SHEET ‘TB’
CHAPTER 9 – GUIDE TO INPUTTING DATA IN SHEET ‘DATA’
CREATING REPORTS
CHAPTER 10 – CREATING REPORT – TRIAL BALANCE
CHAPTER 11 – CREATING REPORT – BALANCE SHEET
CHAPTER 12 – CREATING REPORT – PROFIT & LOSS
CHAPTER 13 – CREATING REPORT – DEBT-AGEING
CHAPTER 14 – CREATING REPORT – CREDITORS AGEING
CHAPTER 15 – CREATING REPORT – PURCHASES
CHAPTER 16 – CREATING REPORT – SALES
FILTERING DATA
CHAPTER 17 – FILTERING DATA IN THE SHEET ‘DATA’
CHAPTER 18 – FILTERING DATA IN THE SHEET ‘TB’
SEARCHING, REPLACING AND SORTING DATA
CHAPTER 19 – SEARCHING FOR DATA
CHAPTER 20 – REPLACING DATA
CHAPTER 21 – SORTING DATA
REVIEW OF DATA AND STRUCTURE
CHAPTER 22 – REVIEW OF DATA AND STRUCTURE
USING THE FILE
CHAPTER 23 – HOW TO USE THE FILE
CHAPTER 24 – NEW ACCOUNTING YEAR
CHAPTER 25 – MANAGING INCREASING NUMBER OF FILES
BEYOND DATA ENTRY
CHAPTER 26 – MONITORING CASH BALANCE
CHAPTER 27 – STATEMENT OF ACCOUNT
CHAPTER 28 – GETTING LEDGER DETAILS OF AN ACCOUNT
CHAPTER 29 – BANK RECONCILIATION
OTHERS
CHAPTER 30 – POTENTIAL PROBLEMS
CHAPTER 31 – SHORTCUTS AND TECHNIQUES
CHAPTER 32 – FUNCTIONS USED IN THE FORMULAS
CHAPTER 33 – SEGREGATION OF DUTIES, NETWORKING AND INTERNET
CHAPTER 34 – PIVOTTABLE OPERATION PRIMER
CHAPTER 35 – ADVANCE FILTER PRIMER
CHAPTER 36 – BOOK-KEEPING PRIMER
CHAPTER 37 – THIS SYSTEM AND ITS SUITABILITY
CHAPTER 38 – SAMPLE DISK
CHAPTER 39 – CONTACTING THE AUTHOR
CHAPTER 1 – BOOK-KEEPING FUNCTIONS THAT CAN BE DONE USING EXCEL
CHAPTER 2 – REQUIREMENTS FOR IMPLEMENTATION OF BOOK-KEEPING WITH EXCEL
BUILDING THE STRUCTURE
CHAPTER 3 – STEP 1 – CREATE A FOLDER (DIRECTORY)
CHAPTER 4 – STEP 2 – CREATE AND SET A FILE (WORKBOOK)
CHAPTER 5 – STEP 3 – SET THE WORKSHEETS IN THE FILE
CHAPTER 6 – STEP 4 – SET THE STRUCTURE IN THE SHEET ‘DATA’
CHAPTER 7 – STEP 5 – SET THE STRUCTURE IN THE SHEET ‘TB’
GUIDE TO INPUTTING DATA
CHAPTER 8 – GUIDE TO INPUTTING DATA IN SHEET ‘TB’
CHAPTER 9 – GUIDE TO INPUTTING DATA IN SHEET ‘DATA’
CREATING REPORTS
CHAPTER 10 – CREATING REPORT – TRIAL BALANCE
CHAPTER 11 – CREATING REPORT – BALANCE SHEET
CHAPTER 12 – CREATING REPORT – PROFIT & LOSS
CHAPTER 13 – CREATING REPORT – DEBT-AGEING
CHAPTER 14 – CREATING REPORT – CREDITORS AGEING
CHAPTER 15 – CREATING REPORT – PURCHASES
CHAPTER 16 – CREATING REPORT – SALES
FILTERING DATA
CHAPTER 17 – FILTERING DATA IN THE SHEET ‘DATA’
CHAPTER 18 – FILTERING DATA IN THE SHEET ‘TB’
SEARCHING, REPLACING AND SORTING DATA
CHAPTER 19 – SEARCHING FOR DATA
CHAPTER 20 – REPLACING DATA
CHAPTER 21 – SORTING DATA
REVIEW OF DATA AND STRUCTURE
CHAPTER 22 – REVIEW OF DATA AND STRUCTURE
USING THE FILE
CHAPTER 23 – HOW TO USE THE FILE
CHAPTER 24 – NEW ACCOUNTING YEAR
CHAPTER 25 – MANAGING INCREASING NUMBER OF FILES
BEYOND DATA ENTRY
CHAPTER 26 – MONITORING CASH BALANCE
CHAPTER 27 – STATEMENT OF ACCOUNT
CHAPTER 28 – GETTING LEDGER DETAILS OF AN ACCOUNT
CHAPTER 29 – BANK RECONCILIATION
OTHERS
CHAPTER 30 – POTENTIAL PROBLEMS
CHAPTER 31 – SHORTCUTS AND TECHNIQUES
CHAPTER 32 – FUNCTIONS USED IN THE FORMULAS
CHAPTER 33 – SEGREGATION OF DUTIES, NETWORKING AND INTERNET
CHAPTER 34 – PIVOTTABLE OPERATION PRIMER
CHAPTER 35 – ADVANCE FILTER PRIMER
CHAPTER 36 – BOOK-KEEPING PRIMER
CHAPTER 37 – THIS SYSTEM AND ITS SUITABILITY
CHAPTER 38 – SAMPLE DISK
CHAPTER 39 – CONTACTING THE AUTHOR
Create a folder (directory)
Here are the steps to develop a book-keeping system with Microsoft Excel.
Step 1- Create A Folder (Directory)
You should be familiar with the Windows computer operating system and know how to create a folder in a disk. Now, create a new folder in an easy-to-access path, such as in the root directory of disk C, not in a sub-folder. Give it an easy-to-remember name, say: AccountsXL. So, the path would look something like this: C:/AccountsXL.
All your accounts files will be saved into this folder.
You can choose another folder or some other name for the folder if you feel that it is more convenient.
Step 1- Create A Folder (Directory)
You should be familiar with the Windows computer operating system and know how to create a folder in a disk. Now, create a new folder in an easy-to-access path, such as in the root directory of disk C, not in a sub-folder. Give it an easy-to-remember name, say: AccountsXL. So, the path would look something like this: C:/AccountsXL.
All your accounts files will be saved into this folder.
You can choose another folder or some other name for the folder if you feel that it is more convenient.
Create and Set a new blank file (workbook)
Step 2 - Create And Set A File (Workbook)
After you create a folder, open Microsoft Excel (95, 97, 2000 or XP). Then, create a new blank Microsoft Excel file (menu File/New). In Excel, a file is called a workbook. Save the file into the AccountsXL folder (or some other folder of your choice). Give the file this (suggested) name: 0000 Book-Keeping Sample.xls. You can use some other name if you wish.
This file will become the SAMPLE or MASTER copy file for our book-keeping functions.
NOTE By putting zeros (0000) in the front part of the file name, you ensure that the file will always appear first (topmost) in the folder that it is in. Although one zero will suffice, I use 4 zeros to make it more obvious.
(The disk accompanying this book contains a copy of this sample file).
After you create a folder, open Microsoft Excel (95, 97, 2000 or XP). Then, create a new blank Microsoft Excel file (menu File/New). In Excel, a file is called a workbook. Save the file into the AccountsXL folder (or some other folder of your choice). Give the file this (suggested) name: 0000 Book-Keeping Sample.xls. You can use some other name if you wish.
This file will become the SAMPLE or MASTER copy file for our book-keeping functions.
NOTE By putting zeros (0000) in the front part of the file name, you ensure that the file will always appear first (topmost) in the folder that it is in. Although one zero will suffice, I use 4 zeros to make it more obvious.
(The disk accompanying this book contains a copy of this sample file).
Wednesday, June 29, 2011
Initial Sheet in the file (workbook)
- Open a new workbook (This will automatically create a blank workbook).
- Save the new workbook. In the process of saving a new workbook, you will be asked to give a name for the workbook. Give the new workbook a suitable name that reflect what data it will contain. For example, if the name is "AccJan2012.xls" it will contain accounting data for January 2012.
Other than name, you also be asked to state or select which folder you want to put the workbook.
A workbook is same as a file.
A workbook (or a file) consist sheet. Initially you are provided with one sheet. You can add more sheet or delete sheets.
A sheet consist of rectangular boxes. You write into these boxes.
When you firstly open the new file, initially, there is only one sheet in the new file that you have just created.
In Excel, sheet is also called worksheet.
Rename the sheet as : DATA. (This name is used throughout this book and in formulas in the file. Do not use other name unless you are aware of the effect).
Later, when you have more sheets, you can arrange the sheets in any order.
Sheet DATA will contain all your raw transaction data (such as opening balances, sales, purchases, receipts, payments, journals, etc).
TIP Usually we use the mouse to move from sheet to sheet by clicking the intended sheet. You can use a combination of keyboard keys, instead. Use Ctrl+Page Down to move to the sheet on the right (Hold down the Ctrl key, and press the Page Down key. The next sheet will become active.). Use Ctrl+Page Up to move to the sheet on the left. By depending less on the mouse, you can speed up your work, but this requires a good memory.
- Save the new workbook. In the process of saving a new workbook, you will be asked to give a name for the workbook. Give the new workbook a suitable name that reflect what data it will contain. For example, if the name is "AccJan2012.xls" it will contain accounting data for January 2012.
Other than name, you also be asked to state or select which folder you want to put the workbook.
A workbook is same as a file.
A workbook (or a file) consist sheet. Initially you are provided with one sheet. You can add more sheet or delete sheets.
A sheet consist of rectangular boxes. You write into these boxes.
When you firstly open the new file, initially, there is only one sheet in the new file that you have just created.
In Excel, sheet is also called worksheet.
Rename the sheet as : DATA. (This name is used throughout this book and in formulas in the file. Do not use other name unless you are aware of the effect).
Later, when you have more sheets, you can arrange the sheets in any order.
Sheet DATA will contain all your raw transaction data (such as opening balances, sales, purchases, receipts, payments, journals, etc).
TIP Usually we use the mouse to move from sheet to sheet by clicking the intended sheet. You can use a combination of keyboard keys, instead. Use Ctrl+Page Down to move to the sheet on the right (Hold down the Ctrl key, and press the Page Down key. The next sheet will become active.). Use Ctrl+Page Up to move to the sheet on the left. By depending less on the mouse, you can speed up your work, but this requires a good memory.
Tuesday, June 28, 2011
Structure in sheet Data
Where to enter data
1. Use a dedicated sheet.2. Name the sheet appropriately. In this book, its name is "Data". This name is used in formulas in this book.
Sample structure
| A | B | C | D | E | F | G | H | I | J | |
| 1 | SRC | DATE | MTH | SRC. NO. | 2ND SRC. NO. | Note 1 | Note 2 | Note 3 | Note 4 | Note 5 |
| 2 | ||||||||||
| 3 | ||||||||||
| 4 | ||||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | Subtotal | |||||||||
| 10 | Checking | |||||||||
| 11 | Cut-off Date | (date) | ||||||||
| 12 | LastRowDATA | (formula) |
| K | L | M | N | O | |
| 1 | ACCOUNTS | Amounts | Cum. Amounts | Check Accounts | Bank Recon. |
| 2 | (formula) | ||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 | (formula) | ||||
| 10 | |||||
| 11 | |||||
| 12 |
Sample structure
| A | B | C | D | E | F | G | H | I | J | |
| 1 | SRC | DATE | MTH | SRC. NO. | 2ND SRC. NO. | Note 1 | Note 2 | Note 3 | Note 4 | Note 5 |
| 2 | ||||||||||
| 3 | ||||||||||
| 4 | ||||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | Subtotal | |||||||||
| 10 | Checking | |||||||||
| 11 | Cut-off Date | (date) | ||||||||
| 12 | LastRowDATA | (formula) |
(formula)
| K | L | M | N | O | P | |
| 1 | ACCOUNTS | Amounts | Cum. Amounts | Check Accounts | Bank Recon. | Sales Inv. Status |
| 2 | (formula) | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 | (formula) | |||||
| 10 | ||||||
| 11 | ||||||
| 12 |
| Q | R | S | T | U | V | W | |
| 1 | Debt Age | Debt Age Group | Supplier Inv. Status | Credit Age | Credit Age Group | Due Date | Items |
| 2 | (formula) | (formula) | (formula) | (formula) | |||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 | |||||||
| 10 | |||||||
| 11 | |||||||
| 12 |
| X | Y | Z | AA | AB | AC | AD | |
| 1 | Qty | Unit of Measure | Price | BS or PL | Original Order | ||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 | |||||||
| 10 | |||||||
| 11 | |||||||
| 12 |
Explanation for the structure in sheet Data
Explanation for the sample structure
1. Cells in row 1 from A1 to N1 are to contain headings.
2. Column O serves as the right-side border. Colour its cells as visual indicator.
3. Rows for transaction data starts at row 2.
4. Let the range A2:N7 as the initial Data Area. The Data Area is dynamic as it will expand or contract as we add more row/s or delete row/si.
5. Make the row immediately below the Data Area as the bottom-side border. Colour its cells as visual indicator.
7. The bottom-side border row is also dynamic as it will move downward or upward as we add more rows of data or delete a row above it.
8. Designate a row immediately below the bottom-side border as Total Row as we will enter some totalling in certain cells (not in all cells) in this row. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side border row.
9. Designate a row immediately below the Total Row as Checking Row as we will enter some formulas in certain cells (not in all cells) in this row for checking purpose. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
10. Designate a row immediately below the Checking Row as Cut-Off row, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
11. In the sample, we put a label Last Row Data in cell A13. The row having this label is immediately below the Cut-Off row. This label is to indicate that we have a formula in cell C13 that will show the last row in the Data Area. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
Dynamic rows
To recap, the following rows are dynamic (it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row) :BottomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA
Sample formulas in use in sheet Data'
| Cell | Formula |
| J2 | =ROUND(G2*H2,2) |
| K2 | =IF(ISERROR(VLOOKUP(J2,INDIRECT("TB!A2:A"&$Z$1),1,FALSE())),"NOT OK","OK") |
| M2 | =IF(J2=J1,M1+L2,L2) |
| P2 | =IF(O2="open",CutOffDate-B2,"NR") |
| Q2 | =IF(O2="open",IF(P2<30,"0-30",IF(P2<60,"30-60",IF(P2<90,"60-90",IF(P2<120,"90-120",">120")))),"NR") |
| Z1 | =ROW(LastRowTB) |
| G9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| J9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| M9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| V9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| C12 | =ROW(LastRowDATA) |
Certain columns need to be formatted with suitable formatting
| Column | Heading | Format |
| A | Source | Text |
| B | Date | Date (eg dd-mmm-yyyy) |
| C | Month | Text |
| D | Source No. | Text |
| E | Other source and no. | Text |
| F | Note 1 | Text |
| G | Note 2 | Text |
| H | Note 3 | Text |
| I | Note 4 | Text |
| J | Accounts | Text |
| K | Checking Accounts | Text |
| L | Amounts DR/CR | Number |
| M | Cumulative Amounts | Number |
| N | Bank Reconciliation marking | Text |
| O | Sales Invoice Status | Text |
| P | Debt Age | Number |
| Q | Debt Age Group | Number |
| R | Debt/Credit Term | Text |
| S | Due date | Date |
| T | Item purchased/Sold | Text |
| U | Quantity Purchased/Sold | Number |
| V | Unit of measurement | Text |
| W | Price | Number |
| X | BS or PL marking | Text |
| Y | Original order | Number |
Changing the structure
You can design the structure differently as you wish.To recap, the following rows are dynamic (it will move downward or upward as we add more rows of data or delete a row) :
BotomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA
Sample formulas in use
| Cell | Formula |
| J2 | =ROUND(G2*H2,2) |
| K2 | =IF(ISERROR(VLOOKUP(J2,INDIRECT("TB!A2:A"&$Z$1),1,FALSE())),"NOT OK","OK") |
| M2 | =IF(J2=J1,M1+L2,L2) |
| P2 | =IF(O2="open",CutOffDate-B2,"NR") |
| Q2 | =IF(O2="open",IF(P2<30,"0-30",IF(P2<60,"30-60",IF(P2<90,"60-90",IF(P2<120,"90-120",">120")))),"NR") |
| Z1 | =ROW(LastRowTB) |
| G9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| J9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| M9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| V9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| C12 | =ROW(LastRowDATA) |
Certain columns need to be formatted with suitable formatting
| Column | Heading | Format |
| A | Source | Text |
| B | Date | Date (eg dd-mmm-yyyy) |
| C | Month | Text |
| D | Source No. | Text |
| E | Other source and no. | Text |
| F | Note 1 | Text |
| G | Note 2 | Text |
| H | Note 3 | Text |
| I | Note 4 | Text |
| J | Accounts | Text |
| K | Checking Accounts | Text |
| L | Amounts DR/CR | Number |
| M | Cumulative Amounts | Number |
| N | Bank Reconciliation marking | Text |
| O | Sales Invoice Status | Text |
| P | Debt Age | Number |
| Q | Debt Age Group | Number |
| R | Debt/Credit Term | Text |
| S | Due date | Date |
| T | Item purchased/Sold | Text |
| U | Quantity Purchased/Sold | Number |
| V | Unit of measurement | Text |
| W | Price | Number |
| X | BS or PL marking | Text |
| Y | Original order | Number |
Changing the structure
You can design the structure differently as you wish.Explaination on the sample~1. Cells in row 1 from A1 to N1 are to contain headings.
2. Column O serves as the right-side border. Colour its cells as visual indicator.
3. Rows for transaction data starts at row 2.
4. Let the range A2:N7 as the initial Data Area. The Data Area is dynamic as it will expand or contract as we add more row/s or delete row/si.
5. Make the row immediately below the Data Area as the bottom-side border. Colour its cells as visual indicator.
7. The bottom-side border is also dynamic as it will move downward or upward as we add more rows of data or delete a row.
8. Designate a row immediately below the borrom-side border as Total Row as we will enter some totalling in certain cells (not in all cells) in this row. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
9. Designate a row immediately below the Total Row as Checking Row as we will enter some formulas in certain cells (not in all cells) in this row for checking purpose. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
10. Designate a row immediately below the Checking Row as Cut-Off row, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
11. In the sample, we put a label Last Row Data in cell A13. The row having this label is immediately below the Cut-Off row. This label is to indicate that we have a formula in cell C13 that will show the last row in the Data Area. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
Dynamic rows
More explanation for structure in sheet Data Page 6 7 8 9 10
Row 1
In cell Al, type:
TOTAL/SUBTOTAL
This is to indicate that some of the other cells in row 1 are used to accommodate formulas which calculate totals/subtotals.
In cell H1, write this formula:
SUBTOTAL (9, INDIRECT("H5: H30000"))
This formula calculates the total of amounts in cells in column H (from row 5 to row 30,000). (We will see later that cells H5 to H30000 will contain amounts for debited accounts).
In cell J1, write this formula:
=SUBTOTAL(9, INDIRECT("J5:J30000"))
This formula calculates the total of amounts in cells in column J (from row 5 to row 30000). (We will see later that cells J5 to J30000 will contain amounts for credited accounts).
In cell T1, write this formula:
=SUBTOTAL (9, INDIRECT("T5:T30000"))
This formula calculates the total of amounts in the cells in column T (from row 5 to row 30000). (We will see later that cells T5 to T30000 will contain amounts of payment which we still holding, such as cheques we still hold).
In cell Y1, write this formula:
=SUBTOTAL(9, INDIRECT("Y5:Y30000"))
This formula calculates the total of amounts in cells in column Y (from row 5 to row 30000). (We will see later that cells Y5 to Y30000 will contain quantity values).
6
Other cells:
You can use other cells in row 1 for other purpose.
Change of formula:
You can change the formulas if you know the effect.
NOTE Here, you are introduced to the SUBTOTAL function. You can refer to Topic 29 for an explanation of this function as well as other functions used in this book.
Row 2
In cell A2, type Checking. This is to indicate that some of the cells in row 2 are used to accommodate formulas that will check the logic of the results of the other formulas in our file.
For example, cell J2 contains a formula which checks the result of the formulas in cell H1 and J1. The sum of cell H1 and cell J1 should be zero (total Debit amounts minus Total credit amount). The formula is:
=IF(H1+J1=0, "Ok", "Please check")
This formula will show OK if the sum is zero. Otherwise, it will show Please Check to indicate to us to check something. You can read about the IF function in Topic 29.
You can use other cells in row 2 for other purpose.
Row 3
All cells in row 3 should be left blank. This row serves as a border for the data area (rows of data which encompasses row 4 to 30000). If any cell is not blank, some operation will not perform correctly, particularly data filtering and PivotTable functions. We can use data/validation function to help us prevent entry of into the cells. (Refer to Topic 28 on 'Tips And Shortcuts').
7
Row 4
Cells in row 4 are to contain the data titles (or field titles or column titles) for the columns A to AC. The data titles are used to indicate what data should be put in cells under each title.
In cell A4: Write SRC (for SOURCE)
In cell B4: Write DATE
In cell C4: Write NTH (for MONTH)
In cell D4: Write SRC NO (for SOURCE NUMBER)
In cell E4: Write OTHER REF/NO (for OTHER REFERENCE or REFERENCE NUMBER)
In cell F4: Write NOTE
In cell G4: Write ACCOUNT DEBIT
In cell H4: Write RN-DEBIT
In cell 14: Write ACCOUNT CREDIT
In cell 14: Write RM-CREDIT
In cell K4: Write CHECK ACCOUNT DEBIT
In cell L4: Write CHECK ACCOUNT CREDIT
In cell M4: Write BANK RECON (for BANK RECONCILIATION).
In cell N4: Write DEBTOR STTMNT (for DEBTOR STETEMENT).
In cell 04: Write DEBT AGE (days)
In cell P4: Write DEBT AGE GROUP
In cell Q4: Write CREDITOR STTMNT (for CREDITOR STATEMENT).
In cell R4: Write CREDIT AGE (days)
In cell 54: Write CREDIT AGE GROUP
In cell T4: Write HOLD (for PAYMENT/CHEQUE or RECEIPT ON HOLD).
In cell U4: Write TERM (for CREDIT TERM- Ours and Suppliers).
In cell V4: Write DUE DATE
In cell W4: Write ITEM PURCHASED
In cell X4: Write ITEM SOLD
In cell Y4: Write QTY (for QUANTITY)
In cell Z4: Write UNIT (for UNIT OF MEASUREMENT)In cell AA4: Write PRICE
In cell AB4:Write TEMP (for TEMPORARY).
In cell AC4:Write ORIGINAL ORDER.
8
Do not rearrange the order of the titles, because it will affect the formulas and explanations in this book, unless you know the effect.
Do not change the spelling of the titles, because it will also affect the formulas and explanations this book, unless you know the effect.
Row 5
This is where you can start to input data (the first row for data). Data must start at this row.
Some cells in row 5 will contain initial formulas. Later on, when you add more data into cells in rows below it, just copy the formula down.
TIP Use the keyboard key combination of Ctrl+D in a cell to copy into it the contents of the cell above it. (Hold down the Ctrl key and press the D key.)
Be careful not to accidentally delete the cells containing formulas. Keep this book handy for reference.
In cell K5, write this formula:
=IF(ISERROR(VLOOKUP(G5, INDIRECT("TB!A5: A2000"),1,FALSE))," "NOT OK", "OK")
This formula checks whether the account stated in cell G5 below the data title ACCOUNT-DEBIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, you should add the account name into the list, or replace the account name with one that already exists.
Later on, when you add more data into cells in rows below it, just copy the formula down.
You can read more about the functions ISERROR, VLOOKUP and INDIRECT in Topic 29.
9
This formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must change the parameter 2000 in the formula to the actual one.
You should review this column (column K) from time to time to check for non-existent accounts in column G.
In cell L5, write this formula:
=IF(ISERROR(VLOOKUP(IS, INDIRECT("TB!A5: A2000"), 1, FALSE)), "NOT OK", "OK")
This formula checks whether the account stated in the cell below the data title ACCOUNT CREDIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, add the account name into the chart, or replace the account name with one that already exists.
Later on, when you add more data into cells in rows below it, just copy the formula down.
Again, this formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must replace the parameter 2000 in the formula with the actual one.
You should review this column (column L) from time to time for non-existent accounts in column I.
In cell 05, write this formula:
=IF(N5="open", CutOffDate-B5,"NR")
This formula computes the age (in days) of unpaid sales invoice. In cell N5, we will state open if the invoice is still unpaid. The Sales invoice date is supposed to be available in cell B5. Cutoff Date is a name we set for our cut-off date. So, if cells in cell N5 contain open, the formula will subtract the cut-off date from the invoice date. If cell N5 does not contain open, the formula will return HR (for Not Relevant) to the active cell (05).
10
In cell Al, type:
TOTAL/SUBTOTAL
This is to indicate that some of the other cells in row 1 are used to accommodate formulas which calculate totals/subtotals.
In cell H1, write this formula:
SUBTOTAL (9, INDIRECT("H5: H30000"))
This formula calculates the total of amounts in cells in column H (from row 5 to row 30,000). (We will see later that cells H5 to H30000 will contain amounts for debited accounts).
In cell J1, write this formula:
=SUBTOTAL(9, INDIRECT("J5:J30000"))
This formula calculates the total of amounts in cells in column J (from row 5 to row 30000). (We will see later that cells J5 to J30000 will contain amounts for credited accounts).
In cell T1, write this formula:
=SUBTOTAL (9, INDIRECT("T5:T30000"))
This formula calculates the total of amounts in the cells in column T (from row 5 to row 30000). (We will see later that cells T5 to T30000 will contain amounts of payment which we still holding, such as cheques we still hold).
In cell Y1, write this formula:
=SUBTOTAL(9, INDIRECT("Y5:Y30000"))
This formula calculates the total of amounts in cells in column Y (from row 5 to row 30000). (We will see later that cells Y5 to Y30000 will contain quantity values).
6
Other cells:
You can use other cells in row 1 for other purpose.
Change of formula:
You can change the formulas if you know the effect.
NOTE Here, you are introduced to the SUBTOTAL function. You can refer to Topic 29 for an explanation of this function as well as other functions used in this book.
Row 2
In cell A2, type Checking. This is to indicate that some of the cells in row 2 are used to accommodate formulas that will check the logic of the results of the other formulas in our file.
For example, cell J2 contains a formula which checks the result of the formulas in cell H1 and J1. The sum of cell H1 and cell J1 should be zero (total Debit amounts minus Total credit amount). The formula is:
=IF(H1+J1=0, "Ok", "Please check")
This formula will show OK if the sum is zero. Otherwise, it will show Please Check to indicate to us to check something. You can read about the IF function in Topic 29.
You can use other cells in row 2 for other purpose.
Row 3
All cells in row 3 should be left blank. This row serves as a border for the data area (rows of data which encompasses row 4 to 30000). If any cell is not blank, some operation will not perform correctly, particularly data filtering and PivotTable functions. We can use data/validation function to help us prevent entry of into the cells. (Refer to Topic 28 on 'Tips And Shortcuts').
7
Row 4
Cells in row 4 are to contain the data titles (or field titles or column titles) for the columns A to AC. The data titles are used to indicate what data should be put in cells under each title.
In cell A4: Write SRC (for SOURCE)
In cell B4: Write DATE
In cell C4: Write NTH (for MONTH)
In cell D4: Write SRC NO (for SOURCE NUMBER)
In cell E4: Write OTHER REF/NO (for OTHER REFERENCE or REFERENCE NUMBER)
In cell F4: Write NOTE
In cell G4: Write ACCOUNT DEBIT
In cell H4: Write RN-DEBIT
In cell 14: Write ACCOUNT CREDIT
In cell 14: Write RM-CREDIT
In cell K4: Write CHECK ACCOUNT DEBIT
In cell L4: Write CHECK ACCOUNT CREDIT
In cell M4: Write BANK RECON (for BANK RECONCILIATION).
In cell N4: Write DEBTOR STTMNT (for DEBTOR STETEMENT).
In cell 04: Write DEBT AGE (days)
In cell P4: Write DEBT AGE GROUP
In cell Q4: Write CREDITOR STTMNT (for CREDITOR STATEMENT).
In cell R4: Write CREDIT AGE (days)
In cell 54: Write CREDIT AGE GROUP
In cell T4: Write HOLD (for PAYMENT/CHEQUE or RECEIPT ON HOLD).
In cell U4: Write TERM (for CREDIT TERM- Ours and Suppliers).
In cell V4: Write DUE DATE
In cell W4: Write ITEM PURCHASED
In cell X4: Write ITEM SOLD
In cell Y4: Write QTY (for QUANTITY)
In cell Z4: Write UNIT (for UNIT OF MEASUREMENT)In cell AA4: Write PRICE
In cell AB4:Write TEMP (for TEMPORARY).
In cell AC4:Write ORIGINAL ORDER.
8
Do not rearrange the order of the titles, because it will affect the formulas and explanations in this book, unless you know the effect.
Do not change the spelling of the titles, because it will also affect the formulas and explanations this book, unless you know the effect.
Row 5
This is where you can start to input data (the first row for data). Data must start at this row.
Some cells in row 5 will contain initial formulas. Later on, when you add more data into cells in rows below it, just copy the formula down.
TIP Use the keyboard key combination of Ctrl+D in a cell to copy into it the contents of the cell above it. (Hold down the Ctrl key and press the D key.)
Be careful not to accidentally delete the cells containing formulas. Keep this book handy for reference.
In cell K5, write this formula:
=IF(ISERROR(VLOOKUP(G5, INDIRECT("TB!A5: A2000"),1,FALSE))," "NOT OK", "OK")
This formula checks whether the account stated in cell G5 below the data title ACCOUNT-DEBIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, you should add the account name into the list, or replace the account name with one that already exists.
Later on, when you add more data into cells in rows below it, just copy the formula down.
You can read more about the functions ISERROR, VLOOKUP and INDIRECT in Topic 29.
9
This formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must change the parameter 2000 in the formula to the actual one.
You should review this column (column K) from time to time to check for non-existent accounts in column G.
In cell L5, write this formula:
=IF(ISERROR(VLOOKUP(IS, INDIRECT("TB!A5: A2000"), 1, FALSE)), "NOT OK", "OK")
This formula checks whether the account stated in the cell below the data title ACCOUNT CREDIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, add the account name into the chart, or replace the account name with one that already exists.
Later on, when you add more data into cells in rows below it, just copy the formula down.
Again, this formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must replace the parameter 2000 in the formula with the actual one.
You should review this column (column L) from time to time for non-existent accounts in column I.
In cell 05, write this formula:
=IF(N5="open", CutOffDate-B5,"NR")
This formula computes the age (in days) of unpaid sales invoice. In cell N5, we will state open if the invoice is still unpaid. The Sales invoice date is supposed to be available in cell B5. Cutoff Date is a name we set for our cut-off date. So, if cells in cell N5 contain open, the formula will subtract the cut-off date from the invoice date. If cell N5 does not contain open, the formula will return HR (for Not Relevant) to the active cell (05).
10
More explanation for structure in sheet Data Page 11
Later on, when you add more data into rows below it, just copy the formula down.
NOTE
We set the name CutoffDate to refer to a cut-off date by using the menu Insert/Name/Define. When we click this menu, the Define Name dialog box shown below will appear. In the box below the label Names in workbook, write the name Cutoff Date. In the box below the label Refers to, write the date it refers to. Be aware that you must precede the date with "-" (equal) sign. (Also remember that in Excel, you must write dates in this format: Month/Day/Year, i.e. the month, followed by the day, and then the year separated by "/" (forward-slash) and enclosed inside (double-quotes). Click Add. Excel will remember the name and what it refers to. Click OK to close the dialog.
Define Name Names in workbook: CutOffDate OK Close Add Delete Refers to: ="4/30/2002"
In cell P5, write this formula:
=IF(N5="open", IF (05<30,"0-30", IF (05<60, "30-60", IF (05-90, "60. 90", IF (05 120, "90-120", ">120")))),"NR")
11
NOTE
We set the name CutoffDate to refer to a cut-off date by using the menu Insert/Name/Define. When we click this menu, the Define Name dialog box shown below will appear. In the box below the label Names in workbook, write the name Cutoff Date. In the box below the label Refers to, write the date it refers to. Be aware that you must precede the date with "-" (equal) sign. (Also remember that in Excel, you must write dates in this format: Month/Day/Year, i.e. the month, followed by the day, and then the year separated by "/" (forward-slash) and enclosed inside (double-quotes). Click Add. Excel will remember the name and what it refers to. Click OK to close the dialog.
Define Name Names in workbook: CutOffDate OK Close Add Delete Refers to: ="4/30/2002"
In cell P5, write this formula:
=IF(N5="open", IF (05<30,"0-30", IF (05<60, "30-60", IF (05-90, "60. 90", IF (05 120, "90-120", ">120")))),"NR")
11
More explanation for structure in sheet Data Page 12 13 14
This formula will check whether cell NS contains the item open. If so, it will return the debt age group (in days) of the corresponding apes (unpaid) sales invoice. (Remember that in column N, we will state open if the invoice is still unpaid). The debt age is expected to be available in column O. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell NS does not contain open the formula will return NR (for Not Relevant) to the active cell (P5)
Later on, when you add data into cells in rows below it just copy the formula down.
In cell R5, write this formula:
=IF(Q5="open", CutOffDate B5. "MR")
This formula computes the age (in days) of unpaid suppliers invoice. (In cell Q5, we will state open if the supplier invoice is still unpaid). The Supplier invoice date is supposed to be available in cell B5 (Cutoffdate is a name we set for our cut-off date. Refer to the Note above on how to set Cutoffdate) So, if cell Q5 contains open, the formula will subtract the cut-off-date from the invoice date. This results in the number of days unpaid. If cell Q5 does not contain open, the formula will return (for Net Relevant) to the active cell (R5).
Later on, when you add more data into cells in rows below it just copy the formula down.
In cell S5, write this formula:
=IF(Q5="open", IF (R5<30 60.="" 90="" if="">120")))), "NR").
This formula computes the group for the credit age (in days) of unpaid suppliers invoice. In cell Q5, we will state open if the invoice is still unpaid. The credit age is supposed to be available in cell R5. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell Q5 does not contain open, the formula will return NR (for Not Relevant) to the active cell.
Later on, when you add data into cells in rows below it, just copy the formula down.
12
In cell V5, write this formula:
=IF(ISNUMBER(U5), B5+U5, "NR").
This formula computes the due date for payment to supplier/by customer. The formula will add the term (in days, in cell U5) to the invoice date in cell B5. This will produce the due date. If cell U5 does not contain a number type, the formula will write NR to the active cell (V5).
Later on, when you add more data into cells in rows below it, just copy the formula down.
Freeze row 5:
You may want to freeze row 5 so that you can still see the data titles in row 4 when you scroll down.
Row 30000
This is the expected last row for the data area. This row number will appear in many formulas. Why 30000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this row number insufficient, you can increase the number in every formula. Similarly, if your find that row 10000 is enough for your needs, you can change the 30000 in each affected formula to 10000.
To easily find and change every number or character (30000 in this case) in a formula, just use menu Edit/Replace (Refer to topic 27 on 'Shortcuts And Techniques For Working Better And Faster').
Data area
Data area is a rectangular area (bounded by a blank row on top, a blank column on the right, a blank row at the bottom and a filled column in the left) which is expected to contain the data. In this book, the data area
13
starts at cell A5 and ends at cell AC30000. Why 300000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this number insufficient, you can increase the number in every formula. To easily change every number or character in a formula, just use the Edit Replace function. Similarly, if your find that row 10000 is enough for you, you can change every formula to 10000.
Formatting
Certain columns need to be formatted with appropriate format to accommodate proper types of data. Usually, data can be a 'text' type, 'date' type or 'number' type. Columns to accommodate 'date' type must be formatted with 'date' formatting. Columns to accommodate 'number' type must be formatted with 'number' formatting. Columns to accommodate text 'type' must be formatted with 'text' formatting.
This book does not show you how to format a cell since the author expects that you already know how to perform this basic task.
Later on, when you add data into cells in rows below it just copy the formula down.
In cell R5, write this formula:
=IF(Q5="open", CutOffDate B5. "MR")
This formula computes the age (in days) of unpaid suppliers invoice. (In cell Q5, we will state open if the supplier invoice is still unpaid). The Supplier invoice date is supposed to be available in cell B5 (Cutoffdate is a name we set for our cut-off date. Refer to the Note above on how to set Cutoffdate) So, if cell Q5 contains open, the formula will subtract the cut-off-date from the invoice date. This results in the number of days unpaid. If cell Q5 does not contain open, the formula will return (for Net Relevant) to the active cell (R5).
Later on, when you add more data into cells in rows below it just copy the formula down.
In cell S5, write this formula:
=IF(Q5="open", IF (R5<30 60.="" 90="" if="">120")))), "NR").
This formula computes the group for the credit age (in days) of unpaid suppliers invoice. In cell Q5, we will state open if the invoice is still unpaid. The credit age is supposed to be available in cell R5. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell Q5 does not contain open, the formula will return NR (for Not Relevant) to the active cell.
Later on, when you add data into cells in rows below it, just copy the formula down.
12
In cell V5, write this formula:
=IF(ISNUMBER(U5), B5+U5, "NR").
This formula computes the due date for payment to supplier/by customer. The formula will add the term (in days, in cell U5) to the invoice date in cell B5. This will produce the due date. If cell U5 does not contain a number type, the formula will write NR to the active cell (V5).
Later on, when you add more data into cells in rows below it, just copy the formula down.
Freeze row 5:
You may want to freeze row 5 so that you can still see the data titles in row 4 when you scroll down.
Row 30000
This is the expected last row for the data area. This row number will appear in many formulas. Why 30000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this row number insufficient, you can increase the number in every formula. Similarly, if your find that row 10000 is enough for your needs, you can change the 30000 in each affected formula to 10000.
To easily find and change every number or character (30000 in this case) in a formula, just use menu Edit/Replace (Refer to topic 27 on 'Shortcuts And Techniques For Working Better And Faster').
Data area
Data area is a rectangular area (bounded by a blank row on top, a blank column on the right, a blank row at the bottom and a filled column in the left) which is expected to contain the data. In this book, the data area
13
starts at cell A5 and ends at cell AC30000. Why 300000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this number insufficient, you can increase the number in every formula. To easily change every number or character in a formula, just use the Edit Replace function. Similarly, if your find that row 10000 is enough for you, you can change every formula to 10000.
Formatting
Certain columns need to be formatted with appropriate format to accommodate proper types of data. Usually, data can be a 'text' type, 'date' type or 'number' type. Columns to accommodate 'date' type must be formatted with 'date' formatting. Columns to accommodate 'number' type must be formatted with 'number' formatting. Columns to accommodate text 'type' must be formatted with 'text' formatting.
This book does not show you how to format a cell since the author expects that you already know how to perform this basic task.
Subscribe to:
Posts (Atom)