6. =ISNUMBER
This function is usually used to check whether the content of a cell is a number. The full syntax of this function is:
=ISNUMBER(Cell Address)
Example:
We want to check in cell G5 whether the content of cell A5 is a number or not a number. At cell G5, We would write:
=ISNUMBER(A5)
If G5 contains a number, we would see TRUE in cell G5, otherwise FALSE.
7. =SUMIF
This function is used in a cell to sum all numbers in certain area in data area if another area in the same data area contain a particular item. The full syntax of this function is:
=SUMIF(Item to sum, Area where item exists, Area which contain figures to sum)
Example:
In cell A5, we want to sum each amount in range C5:C30000 for each cell in range D5:D30000 which is the same with the content of cell B5. We would then write the following in cell A5:
=SUMIF(B5, D5:D30000,C5:C30000)
8. =COUNTIF
We can use this function in a cell to count how many times an item exists in a data area. The full syntax of this function is:
=COUNTIF(Data area address, Item)
Example:
We want to count in cell A5, how many times the item LAND is duplicated in the range B4:B2000. In cell A5, we would write:
=COUNTIF(B4:B2000, "LAND")
If LAND occurs 5 times, we will see 5 in cell A5.
224
9. =MATCH
The full syntax of the function is:
=MATCH(Item being searched, Column Range to search, 0)
The parameter 0 must always be stated. This function will return the relative row position (not the actual sheet row position) of the first item in the searched column range that match the item being searched.
Example:
In cell A1, we want to find the first occurrence of an account name 'LAND' in range D5:D100. So we write in cell A1 this formula:
= MATCH("LAND",D5:D100)
The function will search for "LAND" in range D1:D100 from top to bottom. If it finds one, it will return the relative row position of the item in the range. (If the position it finds is the 8th, the actual sheet row is 17). If it can't find, it will return #NA.
10. =SUMPRODUCT
This function is used to get the sum of a certain item satisfying more than one criteria. For example, we want to sum the amounts in range G4:G2000 where the account name in range B4:B2000 is 'Stationery' and the month in range D4:D2000 is '4'. We will write the formula like this:
=SUMPRODUCT((B4:B2000="Stationery")*(D4:D2000="4")*(G4:G2000))
In this example, we have 2 criteria. If you have more than 2 criteria, just add the criteria in brackets after the asterisk symbol.
You can arrange the criteria in any order. For example, the above can be rearranged to:
=SUMPRODUCT((D4:D2000="4")*(G4:G2000)*(B4:B2000="Stationery"))
225
The asterisk (*) sign is a multiplication sign in Excel. The ranges containing the criteria and the amounts to be added up must have the same dimension. The range containing the amounts to sum must all contain numbers, otherwise the function will return an error #VALUE.
In the above example, Excel will search row by row from row 4 to 2000. For example, at row 4, if it find that the criterion B4="Stationery" is met, it will return 1. Next, if it find that the criterion D4="4" is met, it will return 1. Let's say that the amount in cell G4 is 1000, so the effect is 1*1*100 which is 100. If any one of the criteria is not met, it will return 0. Let's say the first criteria is not met, the effect is 0*1*100 which results in 0. Excel will make this computation row by row, and will sum all the results.
226
Monday, October 12, 2026
Functions used in this book 1 2 3 4 5 pg 221 222 223
29. Functions Used In This Book
Functions Used In Formulas
The file uses the functions listed here. You will notice that they are not many, and it is easy for you to master them.
1. =SUBTOTAL
This function in a cell is used to get totals of figures in other cells which are visible. If figures are not visible (e.g. due to being filtered), it will not be included in the total. The full syntax of this function is:
SUBTOTAL(9, Range address)
The figure 9 represents SUM. The Range address is the address of the range you want to total, e.g. A3:A40.
Example: SUBTOTAL (9, A3:A40)
If this function is in cell A1, cell Al will show the total of all the figures from A3 to A40. If cell A20 is hidden due to filtering, the figure in it will not be included in the sum.
NOTE : For more information, you can search for 'SUBTOTAL worksheet function' in Excel Help.
2. =INDIRECT
This function is used inside another function to make the cell or range address permanent. For example, in cell A1, we want to sum figures in range address A3:A40. So, in cell Al, we write =SUM(A3:A40). If we subsequently delete row 20, the range address under normal conditions will change to A3:A39. To make the range address permanent, we use INDIRECT function, i.e. =SUM(INDIRECT("A3: A40")). This way, even if we delete rows in the original range, the address will still be the same. (Notice that there is a double-quote mark" before and after the address).
221
The syntax is:
=INDIRECT("cell or range address")
NOTE : For more information, you can search for 'INDIRECT worksheet function' in Excel Help.
3. =IF
The full syntax of this function is:
=IF(Condition, return this part if condition is met, return this part if condition is not met)
Example:
In cell A1 we want it to display "Good" if the figure in cell B1 is more than 50 and show "Not Good" if it is less than 50. Then in cell A1, we would write:
=IF(B1>50, "Good", "Bad").
NOTE : For more information, you can search for 'IF worksheet function' in Excel Help.
4. =VLOOKUP
This function can be used to check whether an item exists in a list. The full syntax of this function is:
=VLOOKUP(Item to find, area to find, 1, FALSE)
The parameter "1" and "FALSE" must always be used.
Example:
In cell K5 in sheet DATA, we want to check whether the account name "LAND" in cell G5 in sheet DATA exists in the data area "A5:G1000" in sheet TRIAL BALANCE. We would then write:
=VLOOKUP(DATA!G5,'TRIAL BALANCE'!A5:G1000, 1, FALSE)
If the item exists, the function will return TRUE, otherwise it will return #ERROR. We will see TRUE or #ERROR in cell K5.
NOTE : For more information, you can search for 'VLOOKUP worksheet function' in Excel Help.
5. =ISERROR
This function is usually used together with function IF and one other function to check whether the other function returns #ERROR. The full syntax of this function is:
=ISERROR(Other function)
Example:
The function VLOOKUP may return #ERROR. We don't want to see #ERROR in the cell where the function VLOOKUP is written. So, if VLOOKUP returns #ERROR, we want to see some other more meaningful word(s) such as "NOT OK". Thus we can write:
=IF(ISERROR(VLOOKUP(G5,INDIRECT("TB!A5:A1000"),1,FALSE)), "NOT OK", "OK")
Functions Used In Formulas
The file uses the functions listed here. You will notice that they are not many, and it is easy for you to master them.
1. =SUBTOTAL
This function in a cell is used to get totals of figures in other cells which are visible. If figures are not visible (e.g. due to being filtered), it will not be included in the total. The full syntax of this function is:
SUBTOTAL(9, Range address)
The figure 9 represents SUM. The Range address is the address of the range you want to total, e.g. A3:A40.
Example: SUBTOTAL (9, A3:A40)
If this function is in cell A1, cell Al will show the total of all the figures from A3 to A40. If cell A20 is hidden due to filtering, the figure in it will not be included in the sum.
NOTE : For more information, you can search for 'SUBTOTAL worksheet function' in Excel Help.
2. =INDIRECT
This function is used inside another function to make the cell or range address permanent. For example, in cell A1, we want to sum figures in range address A3:A40. So, in cell Al, we write =SUM(A3:A40). If we subsequently delete row 20, the range address under normal conditions will change to A3:A39. To make the range address permanent, we use INDIRECT function, i.e. =SUM(INDIRECT("A3: A40")). This way, even if we delete rows in the original range, the address will still be the same. (Notice that there is a double-quote mark" before and after the address).
221
The syntax is:
=INDIRECT("cell or range address")
NOTE : For more information, you can search for 'INDIRECT worksheet function' in Excel Help.
3. =IF
The full syntax of this function is:
=IF(Condition, return this part if condition is met, return this part if condition is not met)
Example:
In cell A1 we want it to display "Good" if the figure in cell B1 is more than 50 and show "Not Good" if it is less than 50. Then in cell A1, we would write:
=IF(B1>50, "Good", "Bad").
NOTE : For more information, you can search for 'IF worksheet function' in Excel Help.
4. =VLOOKUP
This function can be used to check whether an item exists in a list. The full syntax of this function is:
=VLOOKUP(Item to find, area to find, 1, FALSE)
The parameter "1" and "FALSE" must always be used.
Example:
In cell K5 in sheet DATA, we want to check whether the account name "LAND" in cell G5 in sheet DATA exists in the data area "A5:G1000" in sheet TRIAL BALANCE. We would then write:
=VLOOKUP(DATA!G5,'TRIAL BALANCE'!A5:G1000, 1, FALSE)
If the item exists, the function will return TRUE, otherwise it will return #ERROR. We will see TRUE or #ERROR in cell K5.
NOTE : For more information, you can search for 'VLOOKUP worksheet function' in Excel Help.
5. =ISERROR
This function is usually used together with function IF and one other function to check whether the other function returns #ERROR. The full syntax of this function is:
=ISERROR(Other function)
Example:
The function VLOOKUP may return #ERROR. We don't want to see #ERROR in the cell where the function VLOOKUP is written. So, if VLOOKUP returns #ERROR, we want to see some other more meaningful word(s) such as "NOT OK". Thus we can write:
=IF(ISERROR(VLOOKUP(G5,INDIRECT("TB!A5:A1000"),1,FALSE)), "NOT OK", "OK")
Sunday, October 11, 2026
Tips and Shortcuts 33 34 pg 218 219 220
33. Highlighting cells with formula
Sometimes, we want to ensure that cells that should contain formula must have formula in it. We may forget to copy down formula, for example. With the following steps, we can see formulas in the cells with formula, so we can quickly look for cells without formula. Check whether that cell should contain a formula.
Click menu Tools/Options. Click tab View in the dialog box that appears. Put a mark in the box at left of label Formulas. Click OK. You should see all formulas in cells with formulas. Review the cells without formulas to check whether they should contain formulas.
Once done, click menu Tools/ Option again. And click tab View. This time, clear the box on left of label Formulas. Click OK. You will see figures instead of formulas in the relevant cells.
34. Making sure cell address does not change when copying formula
Say we have the following data:
In cell A4, we will write a formula =SUM(A1:A3) to get a total of the amounts in cells A1 to A3. If we copy the formula to cell B4, the formula in cell B4 will automatically become =SUM(B1:B3) which will get the total of the amounts in cells B1 to B3. If we copy the formula in cell A4 or B4 to cell C4, the formula in cell C4 will automatically become =SUM(C1:C3) which will compute the total of the amounts in cells C1 to C3.
218
The automatic change in cell address in a formula is fine for the above situation. But consider the following data:
70
In cell C2 we will calculate the percentage of sales value of Item A. We use this formula: =(B2/B5)*100 . If we copy this formula to cell C3, the formula in cell C3 will become = (B3/B6)*100. If we copy this formula to cell C4, the formula in cell C4 will become = (B4/B7)*100. The formulas in cell C3 and C4 are not what we want because it refers to row 6 and 7 in column B instead of row 5.
To make sure the row number in cell address B5 in the formula in cell C2 remains as is when we copy the formula to cells C3 and C4, put a dollar sign ($) in front of the row number in cell address B5 in the formula in cell C2, like this: =(B2/B$5)*100 before we copy.
If we the copy the formula, the formula in cell C3 would appear correctly as =(B3/B$5)*100 and the formula in cell C4 would appear correctly as =(B4/B$5)*100 .
Here we learn that by putting a $ sign in front of a row number of a cell address in a formula, the row number will not change when we copy the formula to another cell. This is useful in case like above.
If we put a $ sign in front of a column reference of a formula, e.g. $B5, the column reference will not change when we copy the formula to another cell in different column.
219
If we do not want either the column reference or the row number to change when we copy a formula, put $ sign in front of both, eg. $8$5.
An alternative of using $ sign is to use INDIRECT function. Just replace the $ sign with word INDIRECT, eg.
220
Sometimes, we want to ensure that cells that should contain formula must have formula in it. We may forget to copy down formula, for example. With the following steps, we can see formulas in the cells with formula, so we can quickly look for cells without formula. Check whether that cell should contain a formula.
Click menu Tools/Options. Click tab View in the dialog box that appears. Put a mark in the box at left of label Formulas. Click OK. You should see all formulas in cells with formulas. Review the cells without formulas to check whether they should contain formulas.
Once done, click menu Tools/ Option again. And click tab View. This time, clear the box on left of label Formulas. Click OK. You will see figures instead of formulas in the relevant cells.
34. Making sure cell address does not change when copying formula
Say we have the following data:
A B C 1 20 15 30 2 30 25 40 3 40 35 10 4
In cell A4, we will write a formula =SUM(A1:A3) to get a total of the amounts in cells A1 to A3. If we copy the formula to cell B4, the formula in cell B4 will automatically become =SUM(B1:B3) which will get the total of the amounts in cells B1 to B3. If we copy the formula in cell A4 or B4 to cell C4, the formula in cell C4 will automatically become =SUM(C1:C3) which will compute the total of the amounts in cells C1 to C3.
218
The automatic change in cell address in a formula is fine for the above situation. But consider the following data:
A B C 1 Product Sales-RM % 2 Item A 25 3 Item B 35 4 Item C 10 5 TOTAL
70
In cell C2 we will calculate the percentage of sales value of Item A. We use this formula: =(B2/B5)*100 . If we copy this formula to cell C3, the formula in cell C3 will become = (B3/B6)*100. If we copy this formula to cell C4, the formula in cell C4 will become = (B4/B7)*100. The formulas in cell C3 and C4 are not what we want because it refers to row 6 and 7 in column B instead of row 5.
To make sure the row number in cell address B5 in the formula in cell C2 remains as is when we copy the formula to cells C3 and C4, put a dollar sign ($) in front of the row number in cell address B5 in the formula in cell C2, like this: =(B2/B$5)*100 before we copy.
If we the copy the formula, the formula in cell C3 would appear correctly as =(B3/B$5)*100 and the formula in cell C4 would appear correctly as =(B4/B$5)*100 .
Here we learn that by putting a $ sign in front of a row number of a cell address in a formula, the row number will not change when we copy the formula to another cell. This is useful in case like above.
If we put a $ sign in front of a column reference of a formula, e.g. $B5, the column reference will not change when we copy the formula to another cell in different column.
219
If we do not want either the column reference or the row number to change when we copy a formula, put $ sign in front of both, eg. $8$5.
An alternative of using $ sign is to use INDIRECT function. Just replace the $ sign with word INDIRECT, eg.
INDIRECT("B")&5 This is equivalent to $B5
INDIRECT(B&"5") This is equivalent to B$5
INDIRECT("B5") This is equivalent to $8$5
220
Saturday, October 10, 2026
Tips and Shortcuts 30 to 32 pg 215 216 217
30. Protecting the workbook
NOTE
In Excel, a workbook is a file. It may contain one or more sheets.
In a networking environment, protecting a file is important to avoid arbitrary changes to the file by other users. After we protect a workbook, we cannot add or remove a worksheet. To protect a workbook:
1. Click menu Tools/Protection/Protect Workbook. The Protect Workbook dialog box will appear.
2. Put a mark in the box at the left of the Structure option. It is better not to mark the Windows box. If you type a password in the Password box, you will have to remember the password to later unprotect the workbook. If you forget the password, you will not be able to unprotect the file.
3. To unprotect the workbook, click the menu Tools/Protection/Unprotect Workbook.
215
31. Protect cells containing the labels or formulas
We can protect the labels in the structure in a sheet:
1. Select all the cells in the relevant sheet. You do this by clicking on the button situated on the left of column A label and above the row 1 label.
2. Click menu Format/Cells... The Format Cells dialog box will appear. Click on tab Protection. Unmark the Locked and Hidden boxes. Click OK.
3. Select or highlight the labels that you want to protect. Click menu Format/Cells... The Format Cells dialog box will appear. Click on tab Protection. Mark the Locked box but ;leave the Hidden box unmarked. (If you mark the Hidden box, it will hide the formulas). Click OK.
4. Click menu Tools/Protection/Protect Sheet... The Protect Sheet dialog box will appear. Mark all three boxes: Contents, Objects, Scenarios. Leave the Password box blank or you can supply a password (If you supply a password, you must remember it, because if in future you want to unprotect the sheet, Excel will ask you for the password. You may want to unprotect to make changes to the labels). Click OK.
5. After this, if you or someone else try to change the protected labels, Excel will not allow it and will display a message to say that the label is protected. You also cannot delete the relevant row or column.
6. To unprotect, click menu Tools/Unprotect Sheet (This menu will not appear unless you have protected the sheet).
32. Using data validation to inform user that cell should be blank
Let's say we want to inform the user that cell A3 should be left blank. Follow this steps:
1. Activate the cell (in this case, A3). Make sure there is no single character in it, even a space.
2. Click menu Data/Validation.
216
3. The Data Validation dialog box will appear.
4. Click on tab Settings. Under the Allow label, select Custom. Under the Formula label, write this formula:
=ISBLANK(A3)
5. Click on the tab Input Message. Remove the marking in the box at left of label Show input message when cells is selected.
6. Click on the tab Error Alert. Make sure there is a mark in the box at left of label Show error alert after invalid data is entered. Under the label Style, choose any option. Under the label Title, write a short message for the title of the error message. Under the momo label Error message, write a more detailed description. Click OK. 10
7. After this, if you try to write anything to cell A3, a message will appear.
217
NOTE
In Excel, a workbook is a file. It may contain one or more sheets.
In a networking environment, protecting a file is important to avoid arbitrary changes to the file by other users. After we protect a workbook, we cannot add or remove a worksheet. To protect a workbook:
1. Click menu Tools/Protection/Protect Workbook. The Protect Workbook dialog box will appear.
Protect Workbook Protect workbook for Structure Windows Password (optional): OK Cancel
2. Put a mark in the box at the left of the Structure option. It is better not to mark the Windows box. If you type a password in the Password box, you will have to remember the password to later unprotect the workbook. If you forget the password, you will not be able to unprotect the file.
3. To unprotect the workbook, click the menu Tools/Protection/Unprotect Workbook.
215
31. Protect cells containing the labels or formulas
We can protect the labels in the structure in a sheet:
1. Select all the cells in the relevant sheet. You do this by clicking on the button situated on the left of column A label and above the row 1 label.
2. Click menu Format/Cells... The Format Cells dialog box will appear. Click on tab Protection. Unmark the Locked and Hidden boxes. Click OK.
3. Select or highlight the labels that you want to protect. Click menu Format/Cells... The Format Cells dialog box will appear. Click on tab Protection. Mark the Locked box but ;leave the Hidden box unmarked. (If you mark the Hidden box, it will hide the formulas). Click OK.
4. Click menu Tools/Protection/Protect Sheet... The Protect Sheet dialog box will appear. Mark all three boxes: Contents, Objects, Scenarios. Leave the Password box blank or you can supply a password (If you supply a password, you must remember it, because if in future you want to unprotect the sheet, Excel will ask you for the password. You may want to unprotect to make changes to the labels). Click OK.
5. After this, if you or someone else try to change the protected labels, Excel will not allow it and will display a message to say that the label is protected. You also cannot delete the relevant row or column.
6. To unprotect, click menu Tools/Unprotect Sheet (This menu will not appear unless you have protected the sheet).
32. Using data validation to inform user that cell should be blank
Let's say we want to inform the user that cell A3 should be left blank. Follow this steps:
1. Activate the cell (in this case, A3). Make sure there is no single character in it, even a space.
2. Click menu Data/Validation.
216
3. The Data Validation dialog box will appear.
Data Validation Settings Input Message Error Alert Validation criteria Allow: Custom Ignore blank Data: between Formula: =ISBLANK(A1) Apply these changes to all other cells with the same settings Clear All OK Cancel
4. Click on tab Settings. Under the Allow label, select Custom. Under the Formula label, write this formula:
=ISBLANK(A3)
5. Click on the tab Input Message. Remove the marking in the box at left of label Show input message when cells is selected.
6. Click on the tab Error Alert. Make sure there is a mark in the box at left of label Show error alert after invalid data is entered. Under the label Style, choose any option. Under the label Title, write a short message for the title of the error message. Under the momo label Error message, write a more detailed description. Click OK. 10
7. After this, if you try to write anything to cell A3, a message will appear.
217
Friday, October 9, 2026
Tips and Shortcuts 20 to 29 pg 212 213 214
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
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
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
Subscribe to:
Posts (Atom)