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’).