Use a dedicated (separate) sheet, solely for data entry. In this note, I name the sheet 'DATA'. You can use other name.

Use cells in row 1 to contain data titles. Initially, leave blank a few rows below row 1. Later, we will put transaction data in these rows. We call these rows as Data Rows. One row immediately below the Data Rows, we will use it as bottom border indicator. Cells in this row should always be left blank. One row immediately below the bottom border row, we call it Total Row. In some cells in this row, we will put formulas to get certain totals. One row immediately below the Total Row, we can call it Cut-Off Date row, because in one of the cell, we will state a cut-off date. One row immediately below the Cut-Off Date row, we can call it Last Row Indicator row, because in one of the cell, we will put a formula to get the last row of the Data Rows. One row immediately below the Last Row Indicator row, we can call it Reminder row, because we will put notes to remind us of certain things. You can use any rows below the Reminder row for any purpose you wish.

The (suggested) titles in row 1 are (not necessarily in this order) : SOURCE, DATE, MTH, SRC NO., OTHER REF./ NO., NOTE 1, NOTE 2, NOTE 3, NOTE 4, ACCOUNTS RM - DEBIT / (CREDIT), FC - DEBIT / (CREDIT), CHECK ACCOUNT, ON HOLD, BANK STTMNT, DEBT TERM, DEBT DUE DATE, DEBTOR PMT STATUS, DEBT AGE (days), DEBT AGE GROUP, CREDIT TERM, PAYMENT DUE DATE, CREDITOR PMT STATUS, CREDIT AGE (days), CREDIT AGE GROUP, ITEM PURCHASED, ITEM SOLD, QTY, UNIT, PRICE.

The titles starts from column A to AD. Cells in Column AE should be left blank at all times to serve as the right border of the data area.

If a transaction affects 2 Accounts, you need 2 rows, one to record the Debit particulars and the other row to record the Credit particulars. You can record the Debit particulars first, followed by the Credit particulars, or the other way. For better tracing, both must be located one after another. If there are more than 2 Accounts affected, then you need as many rows. Make sure the Credit amounts is stated in negative.

Use a dedicated (separate) sheet, solely for Trial balance. In this note, I name the sheet 'TB'. You can use other name.

The structure in sheet TB is such that, in column A, list down all the Accounts names. In column B, use SUMIF function to get the totals amounts (balances) of each Accounts. The balances is derived from column RM DEBIT(CREDIT) in sheet DATA using SUMIF function. In column C, state the Main Category in the Balance Sheet that the accounts in column A falls into. In column D, state the Sub-Category in the Balance Sheet that the accounts in column A falls into. In column E, state the Category in the Profit and Loss statement that the accounts in column A falls into (if relevant). In column F, state the actual accounts name that will appear in the Profit and loss statement (if relevant).

To ensure there is no duplicate in the Accounts names listed in column A, use COUNTIF function in column G.

If you want to maintain a certain balance in foreign currency, may be you can use SUMIF function in column H.

Use a dedicated (separate) sheet, solely for the Balance Sheet. In this note, I name the sheet 'BS'. You can use other name.

Design a structure for the Balance Sheet report in this sheet.

The figure for items in Balance Sheet is derived from data in sheet TB. Use SUMIF function to extract the amounts.

Use a dedicated (separate) sheet, solely for the Profit and Loss. In this note, I name the sheet 'PL'. You can use other name.

Design a structure for the Profit and Loss report in this sheet.

The figure for items in Profit and Loss report is derived from data in sheet TB. Use SUMIF function to extract the amounts.

You can use a dedicated sheet or a shared sheet to view a single ledger. You use PivotTable operation to create a single ledger.

You can use a dedicated sheet or a shared sheet to view multiple ledgers at same time. You use PivotTable operation to create a multiple ledgers.

You can use a dedicated sheet or a shared sheet to view all ledgers. You use PivotTable operation to view all ledgers.

You can use a dedicated sheet or a shared sheet to accomodate your transaction listing. You can use PivotTable operation or a copy of sheet DATA to create a transaction listing.

You can use a dedicated sheet or a shared sheet to accomodate your other analysis.

You can use a dedicated sheet or a shared sheet to accomodate your monthly analysis.

You can use PivotTable operation or formulas (by applying mostly SUMPRODUCT function) to create monthly analysis.

You can use a dedicated sheet or a shared sheet to accomodate your cash flow report. Design your report in the sheet, then use SUMIF function to extract relevant amounts from sheet DATA into the report.

You can use a dedicated sheet or a shared sheet to accomodate your Fixed Assets schedule.

You can use a dedicated sheet to accomodate your Bank reconciliation statement. In this note, I name the sheet RECON. Create a draft of a Bank Reconciliation Statement here. In column BANK RECON in sheet DATA, you put mark on item that has corresponding entries in the Bank Statement. You then copy the relevant particulars of the unmarked items into the sheet RECON. I hope you can visualize the rest of the steps that you should take.

You can use a dedicated sheet to view your Debtors listing. You can use PivotTable operation to create the listing.

You can use a dedicated sheet to view your Creditors listing. You can use PivotTable operation to create the listing.

You can use a dedicated sheet to view the ageing analysis for debtors or creditors. You can use PivotTable operation or Filtering or formulas to create the analysis.

You can use a dedicated sheet to view the due dates (receipts or payments) listing. You can use PivotTable operation or Filtering to create the listing.

You may need a separate sheet for general purpose.

You can get it by 2 ways :

(1) from the Trial Balance sheet, ie by filtering operation to see only Balance Sheet accounts, then select those accounts and copy to the new file, or

(2) from DATA sheet, ie by using formula to identify Balance Sheet accounts, then select those accounts, and then copy to new file.

Use one file (workbook) to keep data for only one financial year.

There are 3 ways you can do it :

1. Design a format for the Profit and Loss statement for a particular month only in a separate sheet. To extract amounts from sheet DATA, use SUMPRODUCT function.

2. Use PivotTable operation. However, using this method, you have difficulty to design in a certain format.

3. Make a copy of the current file, then remove the data of irrelevant months. Then, use SUMIF function to extract the amounts.

There are 3 ways you can do it :

1. Design a format for the Profit and Loss statement for up to a particular month in a separate sheet. To extract amounts from sheet DATA, use SUMPRODUCT function. Or,

2. Use PivotTable operation. However, using this method, you have difficulty to design in a certain format. Or,

3. Make a copy of the current file, then remove the data of irrelevant months. Then, use SUMIF function to extract the amounts.

To get comparative figures (monthly or yearly)(side by side) in the Trial Balance, Balance Sheet or Profit & Loss Statement, you have 2 options :

1. Copy the data from the previous relevant sheet, or

2. Copy the data in DATA sheet of the previous relevant sheet, then use formulas to extract the figures.

You can use a separate sheet to work on the Deferred Tax computation.

You can use a separate sheet to work on the cash Flow Statement (Or Statement of Source and Application of Funds).

Below are some tips to work more efficiently in this Excel file (if you believe in working with keyboard keys rather than the mouse).

1. Quick navigation to first row - When you are at far away row say 10000, you can quickly go to the first row : Press 'Ctrl' key. While still pressing the key, press 'Home' key. Release all pressings. This is called Ctrl+Home combination.

2. Quick navigation to a particular cell - When you are at certain cell, you can quickly go to a far away cell. You must know the address of the cell or any cell near it. Click menu 'Edit/ Go To'. A dialog with title 'Go To' will appear. In a box under 'Reference' label, write the cell address (eg. A112). Click OK.

Or,

using keyboards, press 'Alt' key (to activate the menu bar), then press 'E' key (for 'Edit'), then press 'G' key (for ‘Go To’), then write the address of the target cell, then press 'Enter' key.

3. To copy the content of the cell above the current cell to the current cell - At the current cell, press 'Ctrl' key. While still pressing the key, press 'D' key. Release all pressings. The content, formula and format of the cell above it will be copied. This is called Ctrl+D combination.

4. To copy content of the cell on the left of the current cell to the current cell - At the current cell, press 'Ctrl' key. While still pressing the key, press 'R' key. Release all pressings. The content, formula and format of the cell on its left will be copied. This is called Ctrl+R combination.

5. To delete a current row - At the current cell, press 'Alt' key (to activate the menu bar), then press 'E' key (for 'Edit'), then press 'D' key (for 'Delete'), then press 'R' key (for 'row'). The whole row of the current cell will get deleted.

6. To save current file - Using keyboards, press 'Alt' key (to activate the menu bar), then press 'F' key (for 'File'), then press 'S' key (for 'Save').

7. To find a text, number or date - Using keyboard, press 'Alt' key (to activate the menu bar), then press 'E' key (for 'Edit'), then press 'F' key (for 'Find'), then write the item you want to find, finally press 'Enter' key. 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' key. While still pressing the key, press 'Shift' key. While still pressing both keys, press the downward arrow key. All cells from the current cell to the last filled cell in the same column will be highlighted. Press upward arrow to unhighlight cells from the bottom. Release all pressings.

9. To make a current cell content ready for copying - At the current cell, press 'Ctrl' key. While still pressing the key, press 'C' key (for 'copy'). Or, alternatively, 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' key. While still pressing the key, press 'V' key. Or, alternatively, press 'Alt' key (to activate the menu bar), then press 'E' key (for "edit'), then press 'P' key (for 'Paste'). The copied cell content will be pasted into the current cell.

11. To move from current sheet to the sheet on the left - At the current sheet, press 'Ctrl' key. While still pressing the key, press 'Page Up' key. The sheet on the left will be on focus.

12. To move from current sheet to the sheet on the right - At the current sheet, press 'Ctrl' key. While still pressing the key, press 'Page Down' key. The sheet on the right will be on focus.

13. To highlight a contiguous range of cells downward, one cell at a time - At the current cell, press 'Shift' key. While still pressing the key, press the arrow-down key. Each additional one pressing of the arrow-down key (while still pressing the ‘Shift’ key) will add another cell into the selection.

14. To highlight a contiguous range of cells downward, one screen at a time - At the current cell, press 'Shift' key. While still pressing the key, press the 'Page Down' key.

15. To highlight non-contiguous cells - Select the first cell. Press 'Ctrl' key. While still pressing the key, click the 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'. A dialog with title 'Go To' will appear. Click on the 'Specials' button. A dialog with title 'Go To Specials' will appear. Select 'Constants'. Click OK.

Or,

by not using mouse : Press 'Alt' key (to activate the menu bar), then press 'E' key (for "Edit'), then press 'G' key (for 'Go To'), then press 'Alt' & 'S' keys simultaneously (for 'Specials), then press 'O' key (for 'Constants'), finally press 'Enter’.

18. To go to the last row in the data area - Press 'Ctrl' key. While still pressing the key, press 'End' key. Release all pressings.

19. To turn positive number to negative and vice versa when pasting a copied number - Say you 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 Specials’. A dialog with title ‘Specials’ will appear. Select ‘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.

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 - 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 a 'Delete' icon, you can avoid many clicks in order to delete a row or a selection of rows. The 'Delete' icon is available under menu 'Tools/ Customize'. When you click this menu, a dialog with title 'Customize' will appear. Click the 'Commands' tab. Under 'Categories' label, select 'Edit' item from the list. Under 'Commands' label, search for the 'Delete Rows' icon. Drag the icon to toolbar area and drop it there. Dismiss 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 dismiss 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 beginning cell. Point your mouse pointer to the cells's bottom-right handle until it change into a plus shape (+). While the mouse pointer is still at that shape, press your mouse's right button. While still pressing, drag your mouse to highlight the range area that you want. Once the range area is highlighted, release the pressing. Once you release the pressing, a list of actions (floating menu) will appear. Select item 'Fill Series'. A series of number will be ready for you.

26. Using 'Paste' icon to paste copied item - Using 'Paste' icon to paste copied item, you can avoid many clicks in order to paste repetitively. The 'Paste' icon is available under menu 'Tools/ Customize'. When you click this menu, a dialog with title 'Customize' will appear. Click the 'Commands' tab. Under 'Categories' label, select 'Edit' item from the list. Under 'Commands' label, search for the 'Paste' icon. Drag the icon to toolbar area and drop it there. Dismiss 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 back the icon into the 'Customize' dialog box.

27. Using 'Select visible cells' icon to select visible cells - By using a 'Select Visible Cells' icon, you can avoid many clicks in order to select visible cells after you filter the rows. The 'Select Visible Cells' icon is available under menu 'Tools/ Customize'. When you click this menu, a dialog with title 'Customize' will appear. Click the 'Commands' tab. Under 'Categories' label, select 'Edit' item from the list. Under 'Commands' label, search for the 'Select Visible Cells' icon. Drag the icon to toolbar area and drop it there. Dismiss 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 back the icon into the 'Customize' dialog box.

28. Using menu ‘Insert/ Copied cell’ - Sometimes we want to copy contents of cells and paste them into cells already has contents and push the cell with content down or to the right. Use menu 'Insert/ Copied cell' after we do copy operation to insert the copied cell. A dialog with title ‘Insert Paste’ will appear. Select either ‘Shift cells right’ or ‘Shift cells down’. The current cell or highlighted cells will be moved downward or right (its content will not be overwritten). This operation is also useful if we want to reorganise positions of our data.

However, if we 'cut' rather than 'copy', menu that appear is ‘Insert/ Cut cells’ and this will leave the 'cut' cells empty.

However, be careful when inserting, because only the highlighted cell is moved. This means if you highlight only part of cells in your data area, it may cause you whole data get disorganised instead of organised.

29. Using 'Custom View' to remember a filtered data - After you filter your data, you can make Excel remember the view for that filtering. Click on menu 'View/ Custom Views'. A dialog with title 'Custom Views' will appear. To capture the current view, click 'Add' button. A dialog with title 'Add View' will appear. Give a descriptive name for the view in the box on the right of 'Name' label. Click OK. The dialog will disappear 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 a view, you can open the 'Custom Views' dialog, then click on a view name, and click '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 updated automatically. One thing however, that after you use the view, Excel will put a freezer in the last row of the view. This require you to use menu 'Window/ Unfreeze panes' to remove 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.

30. Protecting the workbook - (In Excel, a workbook is a file. It contain many sheets).

In a networking environment, protecting a file is important to avoid arbitrary changes to the file by other user.

To protect a workbook, click menu ‘Tools/ Protection/ Protect Workbook’. A dialog with title ‘Protect Workbook’ will appear.

Put a mark in the box at the left of ‘Structure’ label. Better not to mark the ‘Windows’ box. About the ‘Password’ box, if you supply the password, you will have to remember the password to later unprotect the workbook. If you forgot the password, you will not be able to unprotect the file. If you don’t supply the password, you can unprotect the workbook immediately (menu ‘Tools/ Protection/ Unprotect Workbook’). After we protect the workbook, we cannot add or remove a worksheet.

31. Protect cells containing the labels or formulas - We can protect the labels in the structure in the sheet by the following steps :

1. Select all cells in the relevant sheet. You do this by clicking on the cell on the left of column A label and above the row 1 label.

2. Click menu ‘Format/ Cells…’. A dialog with title ‘Format Cells’ will appear. Click on tab ‘Protection’. Unmark ‘Locked’ and ‘Hidden’ boxes. Click OK.

3. Select or highlight the labels that you want to protect. Click menu ‘Format/ Cells…’. A dialog with title ‘Format Cells’ will appear. Click on tab ‘Protection’. Mark ‘Locked’ box but unmark ‘Hidden’ box (If you mark on the ‘Hidden’ box, it will hide the formulas). Click OK.

4. Click menu ‘Tools/ Protection/ Protect Sheet…’. A dialog with title ‘Protect Sheet’ will appear. Put a mark on all three boxes (‘Contents’, ‘Objects’, Scenarios’). Leave ‘Password’ box blank or you can supply the password (If you supply a password, you must remember the password, because if in future you want to unprotect the sheet, Excel will ask you 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 give you a message telling 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 already protect the sheet).

32. Using data validation to inform user that cell should be blank - Let’s say we want to inform 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’.

3. A dialog with title ‘Data Validation’ will appear.

4. Click on tab ‘Settings’. Under ‘Allow’ label, select ‘Custom’. Under ‘Formula’ label, write this formula : =ISBLANK(A3).

5. Click tab ‘Input Message’. Remove marking in the box at left of label ‘Show input message when cells is selected’.

6. Click tab ‘Error Alert’. Make sure there is a mark in the box at left of label ‘Show error alert after invalid data is entered’.Undel label ‘Style’, choose any one. Under label ‘Title’, write a short message to become the title of the error message. Under label ‘Error message’, write a more detail description. Click OK.

7. After this, if you try to write anything to cell A3, a message will appear.

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 glance at them to review and look for cells without formula. Check whether that cell should has a formula.

Click menu ‘Tools/ Options’. Click tab ‘View’. Put a mark in the box at left of label ‘Formulas’. Click ‘OK’. You should see all formulas in cells with formula. Review cells without formula whether they should contain formula.

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 cell address does not change when copying formula - Say we have a data like this :

	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 get the total of the amounts in cells C1 to C3.

The automatic change in cell address in a formula is good 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 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 $ 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 only then we copy, 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, eg $B5, the column reference will not change when we copy the formula to another cell in different column.

If we don’t want both column reference and row number change when we copy a formula, put $ sign in front of both, eg. $B$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 $B$5

Note the position of ( sign, ) sign, & sign and “ sign when using INDIRECT.

To use Excel as complete Accounting system, you need to learn more on : Filtering, Functions and PivotTable operation.

Most probably yes, as long as the spreadsheet has the same facility ie Filtering, Functions and PivotTable operation. It may be called differently.