Advance Filter primer

32. AdvanceFilter Primer

A Beginner's Guide On AdvanceFilter

We use the AdvanceFilter function when we want to filter our data in the data area (database) based on more than one column title. If we want to filter our data in the data area (database) based on one column title only, we use AutoFilter.

We can access the AdvanceFilter function by clicking on the menu Data/Filter/AdvanceFilter.

1. Valid Data Area

Before we use this function, we must have data in a proper data area (database). A data area is a rectangular area of adjacent cells boundaried by a row on top, a row at the bottom, a column on the left and a column on the right. Refer to topic 31 for more information on valid data areas.

2. Setting Criteria

When we want to use AdvanceFilter, we need to identify what columns titles (or data titles) we want to use as basis of filtering. We identify them by writing the relevant data titles in another area (preferably in other sheet). In that other sheet, we write the data titles in a separate cell in a row (any row) and must be adjacent to each other. In cells below each titles, write the item that we want to see as a result of the filtering. (It could be more than one cell filled with item under each title). The item must be one that is actually available in cells below that title in the actual data area.

The area where we write the selected data titles and the selected items below it is called a CRITERIA. Let's take a look at a few simple examples:

Example 1:

	A			B

1	Title A		Title B
2	xxxx		yyyy
3

This criteria means we want to see rows where each row has item 'xxxx' below a data title A and has item 'yyyy' below data title B. Only rows having these 2 items will be selected, the rest will be hidden.

246

Example 2

	A			B
1	Title A		Title B
2	xxxx
3

This means we want to see rows where each row has item 'xxxx' below a data title A and has any other item under data title B. Only rows having item 'xxxx' under title A will be selected (Cells under title B can contain anything). The rest will be hidden.

Example 3:

	A			B
1	Title A		Title B
2	XXX			yyyy
3	aa
4

This means we want to see rows where each row has item 'xxx' below a data title A and has item 'yyyy' below data title B, and also each row which has item 'aa' below a data title A and has any other item below data title B. Only rows satisfying these conditions will be selected. The rest will be hidden.

Example 4:

	A			B
1	Title A		Title B
2	xxxx
3	aa			yyyy
4

247

This means we want to see rows where each row has item "sooox' bellow a dasta title A and has any item below data title B, and also each row which has item 'aa' below a data title A and has item 'yyyy below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.

Example 5:

	A			B
1	Title A		Title B
2				XXX
3	aa			yyyy
4

This means we want to see rows where each row has item 'xxx' below a data title B and has any item below data title A and also each row which has item 'aa' below a data title A and has item 'yyyy' below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.

Example 6:

	A			B
1	Title A		Title B
2	xxxx
3				yyyy
4

This means we want to see rows where each row has item "xxxx below a data title A and has any item below data title B and also rows which each row which has item "yyyy below a data title B and has any item below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.

By now, you would see that a blank cell in the 'Criteria' block represents ANY. A row in the Criteria represents a row in the actual data area.

248

3. Use AdvanceFilter

After you set the Criteria then you can start using the menu Data/Filter/AdvanceFilter. You can set more than one Criteria in one sheet to serve different filtering.

1. After the Criteria is ready, activate the sheet which contains the data you want to filter.

2. Click menu Data/Filter/AdvanceFilter. You will see the Advance Filter dialog box.

3. On the right of the List range label is your data area automatically identified by Excel. (Check this range. If it is incorrect, click Cancel, correct you data setting, and start again).

4. Click on the Criteria range box. Click at the sheet where the criteria that you want to use reside. Select the criteria range. Automatically the Criteria range box will be filled with the address of your selection area (criteria range).

5. Click OK. Your data in the data area will get filtered accordingly (You see only data that you want to see).

Summary

To filter data based on one item in one column, use AutoFilter operation.
To filter data based on more than one item from one column, use Custon in the AutoFilter menu.
To filter data based on more than one item from more than one column, use AdvanceFilter operation.

249

Pivot Table Building another report based on existing report pgv244 245

Creating A PivotTable Based On An Existing report

When we create a PivotTable based on data in a data area, usually we will not use (drag) all the buttons (which represent the column titles of the data area) into the PivotTable layout. However, the remaining buttons are effectively available in the PivotTable we created, though hidden. So, we can use the same PivotTable report to build a new PivotTable which uses any hidden button.

1. At a cell in the existing PivotTable, right-click once. A floating menu will appear.

Available choices in the floating menu are :

	Format Cells...
	Format Report...
	PivotChart
	Hide
	Wizard...
	Refresh Data
	Select
	Group and Outline ▸
	Formulas
	Order
	Field Settings...
	Table Options...
	Show Pages

Select Wizard...

2. The PivotTable and PivotChart Wizard appear. Step 3 of 3 dialog box will appear :

PivotTable and PivotChart Wizard - Step 3 of 3			?x

Where do you want to put the PivotTable?
	New worksheet
	Existing worksheet
		ItemSold!$A$32

Click Finish to create your PivotTable.

?	Layout...	Options...	Cancel		Back		Finish

3. Under the label Where do you want to put the PivotTable?, select either New worksheet or Existing ting worksheet. If you select New worksheet, the resulting PivotTable will appear in cell A2 in a new worksheet. If you select Existing worksheet, you can click at any cell and the address of the cell will automatically enter the box below the label (The resulting PivotTable will appear starting at that cell. So make sure you don't click on any cell which will render its content be overwritten).

4. Then, click Layout button. From this step onwards you can continue as normal.

Pivot Table Building another report based on same data area pg 243 244

Building Another Report Based On Same Data Area

If you want to create another report, you may get this dialog:

Microsoft Excel

Your new report will use less memory if you base it on your existing report [Book2]Sheet2!PivotTable3, which was created from the same source data. Do you want your new report to be based on the same data as your existing report?

If you click Yes, you will save memory and your workbook file will be smaller.

If you click No, the two reports will be separate.

	Yes		No

This message dialog appears after you click Next in the step 2 dialog in the PivotTable wizard. You will not get this message dialog when you build the first report. This message simply means that Excel has detected that you are working on the same data area for which a report already exists.

You are given the option of using the current report as a basis for your new report. If you use the current report, it means Excel will use the same underlying data in the new report. This will save memory and keep your Excel file smaller. This is the best choice (selecting Yes) in the dialog.

We can use a shortcut way to build a report. Click any cell in the data area which you want use as the base for your report. Click menu Data/PivotTable and PivotChart Report. The PivotTable and PivotChart Wizard box appears. Click Finish. You will be presented with a new sheet. In the new Step 1 of 3 dialog sheet you will have a few boxes. You will see boxes with the following instructions:

Drop Page Fields here.
Drop Row Fields here.
Drop Column Fields here.
Drop Data Items Here.

You also see dialog box containing buttons representing the column titles on the underlying data area. You can drag one button at a time into a box to form a scheme of your report (the same way you do in the Layout dialog). The Drop Data Items Here box should preferably be the last box you drag a button into. As soon as you drag a button into the Drag Data Items Here box, the report will be ready for you.

Pivot Table report Editing pg 242

Editing Your PivotTable Report

You can format the report and the content of the report in the normal way you use formatting in Excel. For instance, you can format numbers, font etc.

You can also change or edit the content (numbers and words) in the report the normal way we do in Excel. The report does not contain any formulas.

You can delete the sheet and the Report to save disk space, if you want. You can always rebuild it again.

If you make any changes in the underlying data, you may want to update the report (if you do not delete the report). Just click any cell in the report, then right-click once. A floating menu will appear. Select Refresh. The report will be updated.


242

PivotTable Report using PivotTable operation Sample result pg 240 to 242

Below is a sample result and its explanation:

	A	    B			C		D		E		F		G
2		
3					MTH
4 	ITEM SOLD  Data		        1		2		3		4		Grand Total
5 	Model A	   Sum of QTY		249,048		203,897		282,726		49,324		784,995
6		   Sum of RM CREDIT	(1,323,990)	(1,099,449)	(1,521,920)	(260,878)	(4,206,237)
7 	Model B    Sum of QTY		162,610		235,906		252,330		159,215		810,061
8	           Sum of RM-CREDIT	(865,699)	(1,249,328) 	(1,376,298)	(849,610)	(4,340,934)	
9 	Model C	   Sum of QTY		136,341		198,838		247,709 	344,895		927,783
10		   Sum of RM CREDIT	(722,783)	(1,077,941)	(1,364,610)	(1,779,477)	(4,944,811)	
11	Total Sum of QTY		547,999		638,641		782,765		553,434		2,522,839
12 	Total Sum of RM-CREDIT		(2,912,472)	(3,426,718)	(4.262.827)	(2,889,964)	(13,491,982)
13

In the above sample, ITEM SOLD is a title in the underlying data area. What we see below it (Model A, Model B and Model C) are the unique items available under the title ITEM SOLD in the underlying data area. It appears on the left side of the report because we dragged the ITEM button into the ROW box (left side) in the Layout dialog in step 3 of the PivotTable Wizard.

If we click on the little arrowhead button on the right of ITEM SOLD, a list will appear together with the OK and CANCEL buttons. The list contains all the unique items available in the data area under column ITEM SOLD.

Automatically, all the unique items will appear in the report. We can select which unique item can appear or not appear in the report by marking or unmarking the box at the left of the unique item in the above list.

Click OK to get the computer accept our selection and dismiss the list. The report will be updated with the selection. Click Cancel to dismiss the list without effecting any changes to the report.

We can change the actual spelling of the unique items in the report without affecting the actual spelling in the underlying data.

---

In the above sample, MTH is also a title in the underlying data area. What we see in a row immediately below it (1, 2, 3, 4) are the unique items available in data under the title MTH in the underlying data area. It appears on the top side of the report because we dragged the MTH button into the COLUMN box (top side) in the Layout dialog in step 3 of the PivotTable Wizard.

240

Automatically, all the unique items will appear in the report. We can select which unique item can appear or not appear in the report by marking or unmarking the box at the left of the unique item in the above list.

Click OK to get the computer accept our selection and dismiss the list. The report will be updated with the selection. Click Cancel to dismiss the list without effecting any changes to the report.

We can change the actual spelling of the unique items in the report without affecting the actual spelling in the underlying data.

---

In the above sample, the word Data on the right of ITEM SOLD refers to the total or sum of numbers (quantity or values). Actually it is a bit misleading. The better word may be is Total, Sum or Count.

The repeated items below it are the items that are added up in the report. Sum of QTY means that values under the title QTY in the underlying data are summed. Sum of RM-CREDIT means that values under the title RM-CREDIT in the underlying data are summed.

Sum of QTY appears on the right side of ITEM SOLD but below MTH because we dragged the QTY button to the DATA box in the Layout dialog in step 3 of PivotTable Wizard.

Sum of RM-CREDIT appears on the right side of ITEM SOLD because we dragged the RM-CREDIT button to the DATA box but below ITEM SOLD button in the Layout dialog in step 3 of PivotTable Wizard. It appears below Sum of QTY in the report because we dragged the RM-CREDIT button to below the QTY button in the DATA box in the Layout dialog.

We can change the spelling of 'Sum of QTY' and 'Sum of 'RM-CREDIT' (in this case) without affecting the underlying data.

---

If we click on the little arrowhead button on the right of Data, a list will appear together with the OK and CANCEL buttons. The list contains all the unique items we dragged into the DATA box. We can select which unique item can appear or not appear in the report by marking or unmarking the box at the left of the unique item in the above list.

241
Click OK to get the computer accept our selection and dismiss the list. The report will be updated with the selection. Click Cancel to dismiss the list without effecting any changes to the report.

If we select only one item from the list, the Data portion of the report will disappear (only values will remain).

---

Sometimes, when we drag a button into DATA box, the default in the result is a count of items instead of a sum of the items.

If we want to find the sum (which is usually the case): Right-click on the report. A floating menu will appear. Select Wizard. We will get the step 3 of the PivotTable wizard. Click Layout to open the Layout dialog box.

Look at the buttons inside the DATA box. If you find the words Count of on a button and you want to change to Sum of. To do this, double-click on the relevant button. A list will appear. Under Summarize by, select Sum from the list. Click OK. You will return to the Layout dialog box.

Now notice that the word Count is replaced with Sum. Click OK. You will return to the step 3 of the PivotTable wizard. Click Finish. The report will be updated and the figure in the report is now a sum instead of a count.

PivotTable Generating Report using PivotTable operation 8 to 12 pg 237 to 239

8. The top portion (above the grey line) contains simple instructions only. On the right side are buttons representing titles in the title row of your data area. You can drag any one button at a time to either the PAGE, COLUMN, ROW or DATA box on the left and release it there.

If you drag and release the button in the PAGE box, each unique title item available in the data area will appear as the main selection in the top portion in the PivotTable report that will be generated. (You can always drag a button out of the PAGE box to revise your selection).

237

If you drag and release the button in the COLUMN box, each unique title item available in the data area will appear in the top rows (in columns) in the PivotTable report that will be generated. (You can always drag button out of the COLUMN box to revise your selection).

If you drag and release the button in the ROW box, each unique title item available in the data area will appear in the left-most columns (in rows) in the PivotTable report that will be generated. (You can always drag button out of the ROW box to revise your selection).

If you drag and release the button in the DATA box, each total count of items available under the title row in the data area which appear under both COLUMN box and ROW box will appear as the count or value in the PivotTable report that will be generated. (You can always drag button out of the DATA box to revise your selection).

TIP
Normally we drag a button which represents a quantity or value into the DATA box, not a button which represents text. This is because DATA box is where totals of count or value will appear.

It is not necessary to put a button into PAGE box. You can leave this box without a button. At least, you must have ROW and DATA boxes, or the COLUMN and DATA boxes filled with a button, or both.

Some buttons may not be useful for the PivotTable function, so we never drag them into the boxes.

You can put more than one buttons in each box. It is best to experiment to see the result. Only after you do few experiments (say 10), you will be able to predict what form of report will be produced by the PivotTable functions.

238

9. After you put necessary buttons into the boxes, click OK. You will return tp the PivotTable and PivotChart Wizard Step 3 of 3 dialog box. Click Options to open the PivotTable Options dialog box.

PivotTable Options										?x

Name:	BalanceSheet

Format options
	Grand totals for columns			Page layout:	Down, Then Over
	Grand totals for rows
	AutoFormat table					Fields per column:	0
	Subtotal hidden page items
	Merge labels						For error values, show:
	Preserve formatting					For empty cells, show:	0
	Repeat item labels on each printed page		Set print titles
	Mark Totals with

Data options							External data options:
	Data source options:					Save password
		Save data with table layout			Background query
		Enable drilldown					Optimize memory
		Refresh on open
		Refresh every: 0

									OK	Cancel

10. In the box on the right of the Name label, you can give a name for the PivotTable report that will be generated. The other options are defaults. You can ignore them for now.
11. Click OK. You will return to the PivotTable and PivotChart Wizard Step 3 of 3 dialog box.
12. Click Finish. A new sheet with the report in it will be presented to you.

PivotTable Generating Report using PivotTable operation 1 to 7 pg 235 to 237

Generating PivotTable Report

PivotTable is used to summarize data in the data area. It can find totals (values or quantities) of a unique item in the data area and combinations of more than one unique item in the data area.

284

NOTE

You can construct a data area with following details: If you want to try the following steps, make sure you have a data area ready.

Fill adjacent cells in a row with titles: Date, Mth, Account-Debit, RM-Debit, Account-Credit, RM-Credit, Item Purchased, Qty purchased, Item sold, Qty sold. Fill data in rows below the titles.

1. We must first activate a cell in the data area. This would let Excel recognise the data area (behind the screen).

2. Then click the menu Data/PivotTable and PivotChart report. The PivotTable and PivotChart wizard Step 1 of 3 dialog box will appear.

PivotTable and PivotChart Wizard - Step 1 of 3		? x

Where is the data that you want to analyze?
	Microsoft Excel list or database
	External data source
	Multiple consolidation ranges
	Another PivotTable or PivotChart

What kind of report do you want to create?
	Pivot Table
	PivotChart (with PivotTable)

?	Cancel		Back		Next		Finish

3. Under Where is the data that you want to analyze, select Microsoft Excel list or database (usually, this is automatically selected). Under What kind of report do you want to create, select PivotTable (usually this is automatically selected).

235

4. Click Next. The PivotTable and PivotChart wizard appear. Step 2 of 3 dialog box will

PivotTable and PivotChart Wizard - Step 2 of 3

	Where is the data that you want to use?

	Range:	$A$4:$AC$30000			Browse...

? Cancel	Back		Next		Finish

5. Under Where is the data that you want to use?, enter the address of the data area. Usually, this is automatically selected. It is good idea to check the range identified by Excel. If the range address is not as you expect, cancel the operation, correct the data area and start all over again. (If you follow the guidelines on the data area, it should be okay).

6. Click Next. The PivotTable and PivotChart wizard appear. Step 3 of 3 dialog box will

PivotTable and PivotChart Wizard - Step 3 of 3

	Where do you want to put the PivotTable?
		New worksheet
		Existing worksheet

	Click Finish to create your Pivot Table.

? 	Layout...	Options...	Cancel	Back	Next	Finish

236 7. Click Layout. If Microsoft Excel cannot identify a proper data area, the following message will appear:

Microsoft Excel

The PivotTable field name is not valid. To create a PivotTable, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

			OK

Otherwise, the PivotTable and PivotChart Wizard appears. Layout dialog box appears.

PivotTable and PivotChart Wizard - Layout			?x

Construct your PivotTable by dragging the field buttons on the right to the diagram on the left.

	PAGE	COLUMN			SRC			ACCOUNT		BANK REC
							DATE		RM-DEBIT	DEBTORS
							MTH			ACCOUNT		DEBT AGE
		ROW      DATA  		SRC NO.		RM-CRED		DEBT AGE
                            OTHER RE	CHECK A		CREDITOR
                            NOTE		CHECK AC	CREDIT A


						Help	OK		Cancel

PivotTable operation primer - Valid data area pg 233 234

31. PivotTable Operation Primer

A Beginner's Guide On PivotTable Operations

The PivotTable function is accessible from the menu Data/PivotTable and PivotChart report. This function can be used to generate summaries of our database.

Valid Data Areas

Before we are able to use it, we must have a proper data area (or database area). A data area is a rectangular area of rows and columns. The topmost row must contain titles in columns. Data must start immediately below the row of titles. Row of titles can start at any row. The titles in the row must be adjacent to each other (not be separated by any blank cell). The last row in the data area is not fixed. It is the last cell in any column in which all the cells above it contain data without any blank cell in between. If a row contains all blank cells, the last row of the data area is the first row above it.

Example 1:

Here is a sample of a valid data area:

	A	B	C	D	E	F	G	H
1
2	X	X	X	X	X	X	X
3	X
4	X
5	X
6	X
7

In the above, the data area is A2:G6 (X represent data)

233

Example 2:

In the following example, there are two data areas: A2:C6 and E2:G5 (X represent data). A blank column (column D) acts as the separator between the two different data areas.

	A	B	C	D	E	F	G	H
1
2	X	X	X		x	x	x
3		X				x
4		X				x
5		X				x
6		X	
7

Example 3:

In the following sample, there are two data areas i.e. A2:F4 and D6:D8. A blank row (row 5) acts as the separator:

	A	B	C	D	E	F	G	H
1
2	X	X	X	X	X	X
3				X
4				X
5
6				X
7				X
8				X
9

Not completed yet

This blog is not completed yet

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

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.

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

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.

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.

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.

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

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.

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

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.

Structure in sheet Data

Where to enter data

1. Use a dedicated sheet.
2. Name the sheet appropriately. In this book, its name is "Data". This name is used in formulas in this book.

Sample structure

	A	B	C	D	E	F	G	H	I	J
1	SRC	DATE	MTH	SRC. NO.	2ND SRC. NO.	Note 1	Note 2	Note 3	Note 4	Note 5
2
3
4
5
6
7
8
9	Subtotal
10	Checking
11	Cut-off Date		(date)
12	LastRowDATA		(formula)

	K	L	M	N	O
1	ACCOUNTS	Amounts	Cum. Amounts	Check Accounts	Bank Recon.
2				(formula)
3
4
5
6
7
8
9				(formula)
10
11
12

Sample structure

	A	B	C	D	E	F	G	H	I	J
1	SRC	DATE	MTH	SRC. NO.	2ND SRC. NO.	Note 1	Note 2	Note 3	Note 4	Note 5
2
3
4
5
6
7
8
9	Subtotal
10	Checking
11	Cut-off Date		(date)
12	LastRowDATA		(formula)

(formula)

	K	L	M	N	O	P
1	ACCOUNTS	Amounts	Cum. Amounts	Check Accounts	Bank Recon.	Sales Inv. Status
2				(formula)
3
4
5
6
7
8
9				(formula)
10
11
12

	Q	R	S	T	U	V	W
1	Debt Age	Debt Age Group	Supplier Inv. Status	Credit Age	Credit Age Group	Due Date	Items
2	(formula)	(formula)		(formula)	(formula)
3
4
5
6
7
8
9
10
11
12

	X	Y	Z	AA	AB	AC	AD
1	Qty	Unit of Measure	Price	BS or PL	Original Order
2
3
4
5
6
7
8
9
10
11
12

Explanation for the structure in sheet Data

Explanation for the sample structure

1. Cells in row 1 from A1 to N1 are to contain headings.
2. Column O serves as the right-side border. Colour its cells as visual indicator.
3. Rows for transaction data starts at row 2.
4. Let the range A2:N7 as the initial Data Area. The Data Area is dynamic as it will expand or contract as we add more row/s or delete row/si.
5. Make the row immediately below the Data Area as the bottom-side border. Colour its cells as visual indicator.
7. The bottom-side border row is also dynamic as it will move downward or upward as we add more rows of data or delete a row above it.
8. Designate a row immediately below the bottom-side border as Total Row as we will enter some totalling in certain cells (not in all cells) in this row. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side border row.
9. Designate a row immediately below the Total Row as Checking Row as we will enter some formulas in certain cells (not in all cells) in this row for checking purpose. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
10. Designate a row immediately below the Checking Row as Cut-Off row, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
11. In the sample, we put a label Last Row Data in cell A13. The row having this label is immediately below the Cut-Off row. This label is to indicate that we have a formula in cell C13 that will show the last row in the Data Area. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.

Dynamic rows

To recap, the following rows are dynamic (it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row) :

BottomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA

Sample formulas in use in sheet Data'

Cell	Formula
J2	=ROUND(G2*H2,2)
K2	=IF(ISERROR(VLOOKUP(J2,INDIRECT("TB!A2:A"&$Z$1),1,FALSE())),"NOT OK","OK")
M2	=IF(J2=J1,M1+L2,L2)
P2	=IF(O2="open",CutOffDate-B2,"NR")
Q2	=IF(O2="open",IF(P2<30,"0-30",IF(P2<60,"30-60",IF(P2<90,"60-90",IF(P2<120,"90-120",">120")))),"NR")
Z1	=ROW(LastRowTB)
G9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
J9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
M9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
V9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
C12	=ROW(LastRowDATA)

Certain columns need to be formatted with suitable formatting

Column	Heading	Format
A	Source	Text
B	Date	Date (eg dd-mmm-yyyy)
C	Month	Text
D	Source No.	Text
E	Other source and no.	Text
F	Note 1	Text
G	Note 2	Text
H	Note 3	Text
I	Note 4	Text
J	Accounts	Text
K	Checking Accounts	Text
L	Amounts DR/CR	Number
M	Cumulative Amounts	Number
N	Bank Reconciliation marking	Text
O	Sales Invoice Status	Text
P	Debt Age	Number
Q	Debt Age Group	Number
R	Debt/Credit Term	Text
S	Due date	Date
T	Item purchased/Sold	Text
U	Quantity Purchased/Sold	Number
V	Unit of measurement	Text
W	Price	Number
X	BS or PL marking	Text
Y	Original order	Number

Changing the structure

You can design the structure differently as you wish.

To recap, the following rows are dynamic (it will move downward or upward as we add more rows of data or delete a row) :

BotomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA

Sample formulas in use

Cell	Formula
J2	=ROUND(G2*H2,2)
K2	=IF(ISERROR(VLOOKUP(J2,INDIRECT("TB!A2:A"&$Z$1),1,FALSE())),"NOT OK","OK")
M2	=IF(J2=J1,M1+L2,L2)
P2	=IF(O2="open",CutOffDate-B2,"NR")
Q2	=IF(O2="open",IF(P2<30,"0-30",IF(P2<60,"30-60",IF(P2<90,"60-90",IF(P2<120,"90-120",">120")))),"NR")
Z1	=ROW(LastRowTB)
G9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
J9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
M9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
V9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
C12	=ROW(LastRowDATA)

Certain columns need to be formatted with suitable formatting

Column	Heading	Format
A	Source	Text
B	Date	Date (eg dd-mmm-yyyy)
C	Month	Text
D	Source No.	Text
E	Other source and no.	Text
F	Note 1	Text
G	Note 2	Text
H	Note 3	Text
I	Note 4	Text
J	Accounts	Text
K	Checking Accounts	Text
L	Amounts DR/CR	Number
M	Cumulative Amounts	Number
N	Bank Reconciliation marking	Text
O	Sales Invoice Status	Text
P	Debt Age	Number
Q	Debt Age Group	Number
R	Debt/Credit Term	Text
S	Due date	Date
T	Item purchased/Sold	Text
U	Quantity Purchased/Sold	Number
V	Unit of measurement	Text
W	Price	Number
X	BS or PL marking	Text
Y	Original order	Number

Changing the structure

You can design the structure differently as you wish.

Explaination on the sample~1. Cells in row 1 from A1 to N1 are to contain headings.
2. Column O serves as the right-side border. Colour its cells as visual indicator.
3. Rows for transaction data starts at row 2.
4. Let the range A2:N7 as the initial Data Area. The Data Area is dynamic as it will expand or contract as we add more row/s or delete row/si.
5. Make the row immediately below the Data Area as the bottom-side border. Colour its cells as visual indicator.
7. The bottom-side border is also dynamic as it will move downward or upward as we add more rows of data or delete a row.
8. Designate a row immediately below the borrom-side border as Total Row as we will enter some totalling in certain cells (not in all cells) in this row. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
9. Designate a row immediately below the Total Row as Checking Row as we will enter some formulas in certain cells (not in all cells) in this row for checking purpose. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
10. Designate a row immediately below the Checking Row as Cut-Off row, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
11. In the sample, we put a label Last Row Data in cell A13. The row having this label is immediately below the Cut-Off row. This label is to indicate that we have a formula in cell C13 that will show the last row in the Data Area. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.

Dynamic rows

More explanation for structure in sheet Data Page 6 7 8 9 10

Row 1

In cell Al, type:

TOTAL/SUBTOTAL

This is to indicate that some of the other cells in row 1 are used to accommodate formulas which calculate totals/subtotals.

In cell H1, write this formula:
SUBTOTAL (9, INDIRECT("H5: H30000"))

This formula calculates the total of amounts in cells in column H (from row 5 to row 30,000). (We will see later that cells H5 to H30000 will contain amounts for debited accounts).

In cell J1, write this formula:

=SUBTOTAL(9, INDIRECT("J5:J30000"))

This formula calculates the total of amounts in cells in column J (from row 5 to row 30000). (We will see later that cells J5 to J30000 will contain amounts for credited accounts).

In cell T1, write this formula:

=SUBTOTAL (9, INDIRECT("T5:T30000"))

This formula calculates the total of amounts in the cells in column T (from row 5 to row 30000). (We will see later that cells T5 to T30000 will contain amounts of payment which we still holding, such as cheques we still hold).

In cell Y1, write this formula:

=SUBTOTAL(9, INDIRECT("Y5:Y30000"))

This formula calculates the total of amounts in cells in column Y (from row 5 to row 30000). (We will see later that cells Y5 to Y30000 will contain quantity values).

6

Other cells:

You can use other cells in row 1 for other purpose.

Change of formula:

You can change the formulas if you know the effect.

NOTE Here, you are introduced to the SUBTOTAL function. You can refer to Topic 29 for an explanation of this function as well as other functions used in this book.

Row 2

In cell A2, type Checking. This is to indicate that some of the cells in row 2 are used to accommodate formulas that will check the logic of the results of the other formulas in our file.

For example, cell J2 contains a formula which checks the result of the formulas in cell H1 and J1. The sum of cell H1 and cell J1 should be zero (total Debit amounts minus Total credit amount). The formula is:

=IF(H1+J1=0, "Ok", "Please check")

This formula will show OK if the sum is zero. Otherwise, it will show Please Check to indicate to us to check something. You can read about the IF function in Topic 29.

You can use other cells in row 2 for other purpose.

Row 3

All cells in row 3 should be left blank. This row serves as a border for the data area (rows of data which encompasses row 4 to 30000). If any cell is not blank, some operation will not perform correctly, particularly data filtering and PivotTable functions. We can use data/validation function to help us prevent entry of into the cells. (Refer to Topic 28 on 'Tips And Shortcuts').

7

Row 4

Cells in row 4 are to contain the data titles (or field titles or column titles) for the columns A to AC. The data titles are used to indicate what data should be put in cells under each title.

In cell A4: Write SRC (for SOURCE)
In cell B4: Write DATE
In cell C4: Write NTH (for MONTH)
In cell D4: Write SRC NO (for SOURCE NUMBER)
In cell E4: Write OTHER REF/NO (for OTHER REFERENCE or REFERENCE NUMBER)
In cell F4: Write NOTE
In cell G4: Write ACCOUNT DEBIT
In cell H4: Write RN-DEBIT
In cell 14: Write ACCOUNT CREDIT
In cell 14: Write RM-CREDIT
In cell K4: Write CHECK ACCOUNT DEBIT
In cell L4: Write CHECK ACCOUNT CREDIT
In cell M4: Write BANK RECON (for BANK RECONCILIATION).
In cell N4: Write DEBTOR STTMNT (for DEBTOR STETEMENT).
In cell 04: Write DEBT AGE (days)
In cell P4: Write DEBT AGE GROUP
In cell Q4: Write CREDITOR STTMNT (for CREDITOR STATEMENT).
In cell R4: Write CREDIT AGE (days)
In cell 54: Write CREDIT AGE GROUP
In cell T4: Write HOLD (for PAYMENT/CHEQUE or RECEIPT ON HOLD).
In cell U4: Write TERM (for CREDIT TERM- Ours and Suppliers).
In cell V4: Write DUE DATE
In cell W4: Write ITEM PURCHASED
In cell X4: Write ITEM SOLD
In cell Y4: Write QTY (for QUANTITY)
In cell Z4: Write UNIT (for UNIT OF MEASUREMENT)In cell AA4: Write PRICE
In cell AB4:Write TEMP (for TEMPORARY).
In cell AC4:Write ORIGINAL ORDER.


8

Do not rearrange the order of the titles, because it will affect the formulas and explanations in this book, unless you know the effect.

Do not change the spelling of the titles, because it will also affect the formulas and explanations this book, unless you know the effect.

Row 5

This is where you can start to input data (the first row for data). Data must start at this row.

Some cells in row 5 will contain initial formulas. Later on, when you add more data into cells in rows below it, just copy the formula down.

TIP Use the keyboard key combination of Ctrl+D in a cell to copy into it the contents of the cell above it. (Hold down the Ctrl key and press the D key.)

Be careful not to accidentally delete the cells containing formulas. Keep this book handy for reference.

In cell K5, write this formula:

=IF(ISERROR(VLOOKUP(G5, INDIRECT("TB!A5: A2000"),1,FALSE))," "NOT OK", "OK")

This formula checks whether the account stated in cell G5 below the data title ACCOUNT-DEBIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, you should add the account name into the list, or replace the account name with one that already exists.

Later on, when you add more data into cells in rows below it, just copy the formula down.

You can read more about the functions ISERROR, VLOOKUP and INDIRECT in Topic 29.

9

This formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must change the parameter 2000 in the formula to the actual one.

You should review this column (column K) from time to time to check for non-existent accounts in column G.

In cell L5, write this formula:

=IF(ISERROR(VLOOKUP(IS, INDIRECT("TB!A5: A2000"), 1, FALSE)), "NOT OK", "OK")

This formula checks whether the account stated in the cell below the data title ACCOUNT CREDIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, add the account name into the chart, or replace the account name with one that already exists.

Later on, when you add more data into cells in rows below it, just copy the formula down.

Again, this formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must replace the parameter 2000 in the formula with the actual one.

You should review this column (column L) from time to time for non-existent accounts in column I.

In cell 05, write this formula:

=IF(N5="open", CutOffDate-B5,"NR")

This formula computes the age (in days) of unpaid sales invoice. In cell N5, we will state open if the invoice is still unpaid. The Sales invoice date is supposed to be available in cell B5. Cutoff Date is a name we set for our cut-off date. So, if cells in cell N5 contain open, the formula will subtract the cut-off date from the invoice date. If cell N5 does not contain open, the formula will return HR (for Not Relevant) to the active cell (05).

10

More explanation for structure in sheet Data Page 11

Later on, when you add more data into rows below it, just copy the formula down.

NOTE

We set the name CutoffDate to refer to a cut-off date by using the menu Insert/Name/Define. When we click this menu, the Define Name dialog box shown below will appear. In the box below the label Names in workbook, write the name Cutoff Date. In the box below the label Refers to, write the date it refers to. Be aware that you must precede the date with "-" (equal) sign. (Also remember that in Excel, you must write dates in this format: Month/Day/Year, i.e. the month, followed by the day, and then the year separated by "/" (forward-slash) and enclosed inside (double-quotes). Click Add. Excel will remember the name and what it refers to. Click OK to close the dialog.

Define Name Names in workbook: CutOffDate OK Close Add Delete Refers to: ="4/30/2002"

In cell P5, write this formula:

=IF(N5="open", IF (05<30,"0-30", IF (05<60, "30-60", IF (05-90, "60. 90", IF (05 120, "90-120", ">120")))),"NR")

11

More explanation for structure in sheet Data Page 12 13 14

This formula will check whether cell NS contains the item open. If so, it will return the debt age group (in days) of the corresponding apes (unpaid) sales invoice. (Remember that in column N, we will state open if the invoice is still unpaid). The debt age is expected to be available in column O. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell NS does not contain open the formula will return NR (for Not Relevant) to the active cell (P5)

Later on, when you add data into cells in rows below it just copy the formula down.

In cell R5, write this formula:

=IF(Q5="open", CutOffDate B5. "MR")

This formula computes the age (in days) of unpaid suppliers invoice. (In cell Q5, we will state open if the supplier invoice is still unpaid). The Supplier invoice date is supposed to be available in cell B5 (Cutoffdate is a name we set for our cut-off date. Refer to the Note above on how to set Cutoffdate) So, if cell Q5 contains open, the formula will subtract the cut-off-date from the invoice date. This results in the number of days unpaid. If cell Q5 does not contain open, the formula will return (for Net Relevant) to the active cell (R5).

Later on, when you add more data into cells in rows below it just copy the formula down.

In cell S5, write this formula:

=IF(Q5="open", IF (R5<30 60.="" 90="" if="">120")))), "NR").

This formula computes the group for the credit age (in days) of unpaid suppliers invoice. In cell Q5, we will state open if the invoice is still unpaid. The credit age is supposed to be available in cell R5. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell Q5 does not contain open, the formula will return NR (for Not Relevant) to the active cell.

Later on, when you add data into cells in rows below it, just copy the formula down.

12

In cell V5, write this formula:

=IF(ISNUMBER(U5), B5+U5, "NR").

This formula computes the due date for payment to supplier/by customer. The formula will add the term (in days, in cell U5) to the invoice date in cell B5. This will produce the due date. If cell U5 does not contain a number type, the formula will write NR to the active cell (V5).

Later on, when you add more data into cells in rows below it, just copy the formula down.

Freeze row 5:
You may want to freeze row 5 so that you can still see the data titles in row 4 when you scroll down.

Row 30000

This is the expected last row for the data area. This row number will appear in many formulas. Why 30000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this row number insufficient, you can increase the number in every formula. Similarly, if your find that row 10000 is enough for your needs, you can change the 30000 in each affected formula to 10000.

To easily find and change every number or character (30000 in this case) in a formula, just use menu Edit/Replace (Refer to topic 27 on 'Shortcuts And Techniques For Working Better And Faster').

Data area

Data area is a rectangular area (bounded by a blank row on top, a blank column on the right, a blank row at the bottom and a filled column in the left) which is expected to contain the data. In this book, the data area

13

starts at cell A5 and ends at cell AC30000. Why 300000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this number insufficient, you can increase the number in every formula. To easily change every number or character in a formula, just use the Edit Replace function. Similarly, if your find that row 10000 is enough for you, you can change every formula to 10000.

Formatting

Certain columns need to be formatted with appropriate format to accommodate proper types of data. Usually, data can be a 'text' type, 'date' type or 'number' type. Columns to accommodate 'date' type must be formatted with 'date' formatting. Columns to accommodate 'number' type must be formatted with 'number' formatting. Columns to accommodate text 'type' must be formatted with 'text' formatting.

This book does not show you how to format a cell since the author expects that you already know how to perform this basic task.

Entering transactions Data in sheet Data

Where to enter transactions data

Transactions data must be entered in the Data Area. The Data area begins from row 2 until the row above bottom-side-border row (dynamic).

What are the sources for entering data

The sources for entering data are documents such as sales invoice, supplier invoice, payment voucher, etc.
Upon receiving the source document, you record its particulars into the sheet Data.

What are the particulars we want to record?

Usually the minimum details we want to record are the name of the source, date of the source, the reference number of the source, what accounts to debit, what accounts to credit, the debit amounts, the credit amounts, etc.

Where to start entering data

Start entering data at row 2 from column to column, guided by the column headings as to what particular to enter.

How to enter Opening Balances

Opening balances are list of balances carried forward from end of prevoius accounting period. One item requires only one row.

Preferably all opening balances are grouped together (located in rows adjacent to each other) and put on top portion of the sheet.

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	OB	1.1.12	0
3	OB	1.1.12	0
4	OB	1.1.12	0
5	OB	1.1.12	0
6	OB	1.1.12	0
7	OB	1.1.12	0
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
2	CAPITAL	-5000	(formula)	(formula)
3	RETAINED PROFITS	-1000
4	SUPPLIER ABC	-2000
5	CUSTOMER EFG	3000
6	FIXED ASSETS	2000
7	CASH IN BANK	3000
8
9		(formula)
10
11
12

Note :

- OB means Opening Balance.

- As to the date of the Opening Balances, you can put the date of the beginning of the accounting period, or you can put the original date. For example, for the item Capital you can put the original date it first get recorded or the date of the beginning of the current accounting period.

- For Opening Balances, put the month (MTH) as zero (0), no matter what date you put in column DATE.

- If one opening balance has potential to be splitted into more detail, you may choose to show more detail rather than only one row. For example, one opening balance for a creditor may consist of 5 outstanding invoices, so you may show all the 5 invoices particulars, each invoice in a separate row.

How to enter Sales Invoices particulars

- Sales invoices is one of the source documents.
- One invoice will involves at least 2 accounts, one debit and one credit. So one invoice affecting 2 accounts will need 2 rows, one row to record the debit particulars and the other row to record the credit particulars. If the invoice affects 3 accounts then we need 3 rows. Most of the particulars are repeated or duplicated in the rows.

Shall we enter source data based on the date order?

No. Ignore the date order at this stage because we can use Excel to later sort data in date order if you want to.

Can we group data according to source?

You can enter data as the source come one by one from row to row. into rows there is no need to group data according to source of transactions

Must we enter data row by row ?

Can we skip row or rows?

Rules

1. Do not allow any blank cell in column A when entering transaction data.
2. Ensure all cells in column Z are blank.
3. Ensure all cells in Bottom-side border are blank.

What to enter below the headings

How to enter Debit and Credit particulars

Use a row to record a debit entry particulars and another row to record a credit entry particulars. Yes, some data on the debit row are duplicated on the credit row.

2. Use minus sign to indicate credit amount (eg -6000).

Insert and Delete Row

You can insert or delete row but be careful not to delete the total row or the LastRowData if you don't want to reinsert the row.

Automatic update

Sample

General guide to entering data in sheet Data

GENERAL GUIDE TO INPUTTING DATA IN SHEET 'DATA'

Once the structure of DATA sheet are ready, you can start inputting data in sheet ‘DATA’.

Data that you want to input is based on the financial transaction document we have on hand, such as invoice, vouchers, journals, etc. Enter the data available in each document in the appropriate cell under an appropriate column title in the same row.

A row containing data of a document in its appropriate column is called ‘Transaction row’. Sometimes, data in a row is called a ‘record’.

Data should start at row 5 and continue until row 30000. If your data exceed row 30000, change all references to “30000” in all formula in all sheets.

Entering data in sheet Data column SRC

Filling cells under column title ‘SRC’

Under the column title 'SRC' (for Source), enter a name for your source of transaction. The source refers to a group of documents having similar function that you have, such as invoices, vouchers, journals etc.

Below are some of the common sources :

1. Opening Balances

If this is the first time you use this system, or in a new accounting year, opening balances is one source of transaction. You may write 'OB' or other term of your choice to identify Opening Balance source.

2. Audit Adjustment (previous accounting year)

It is good to identify Auditor's adjustment as a separate source, rather than group them under journals. You may write 'AUDIT ADJ' or other term to identify Audit Adjustment.

3. Provisions for expenses

It is good to identify provisions for expenses as a separate source. Usually, we use journals to be our source for provisions. In this case, we identify journal for provision separate from other journals. You may write 'PROV' or other term to identify Provisions.

4. Purchase (Supplier) Invoices

This refers to invoices from local suppliers. You may write 'INV' or other term to identify Purchase Invoices.

5. Import Invoices

This refers to invoices from oversea suppliers. You may write 'IMPORT' or other term to identify Import Invoices.

6. Salary-Production

This refers to salary summary for production workers. Accounts staff may get the summary from payroll system.

It is good to obtain separate salary summary for production staff from administrative staff. You may write 'SALARY-P' or other term to identify Salary summary for production staff.

7. Salary-Admin

This refers to salary summary for administrative staff. Accounts staff may get the summary from payroll system. It is good to obtain separate salary summary for administrative staff from production staff. You may write 'SALARY-A' or other term to identify Salary summary for administrative staff.

8. Bonus-Production

This refers to bonus summary for production staff. Accounts staff may get the summary from payroll system. It is good to obtain separate bonus summary for production staff from administrative staff. You may write 'BONUS-P' or other term to identify bonus summary for production staff.

9. Bonus-Admin

This refers to bonus summary for administrative staff. Accounts staff may get the summary from payroll system. It is good to obtain separate bonus summary for production staff from administrative staff. You may write 'BONUS-A' or other term to identify bonus summary for administrative staff.

10. Sales Invoices

This refers to our sales invoices. You may write 'SALES' or other term to identify sales invoices.

11. Sale-Subsidiary XX

This refers to sales invoices to one of our subsidiary with name XX. It is good to identify sales to each subsidiary as separate source. You may write 'SALE-X' or other term to identify sales to company X (a subsidiary).

12. Sale-Associate co. XX This refers to sales invoices to one of our accociate company. It is good to identify sales to each associate company as separate source. You may write 'SALE-X' or other term to identify sales to company X (an associated company).

13. Sale-Holding company

This refers to sales invoices to our holding company. It is good to identify sales to holding company as separate source. You may write 'SALE-H-X' or other term to identify sales to company X (holding company).

14. Petty Cash

This refers to one of petty cash transactions (receipts and payments). It is good to identify each petty cash float as separate source. You may write 'PC-X' or other term to identify Petty Cash float managed by staff X.

15. Banks

This refers to one of banks transactions (receipts and payments). It is good to identify each one bank as separate source. You may write 'BANK X' or other term to identify Bank X transactions.

16. Interest on HP

This refers to interest on hire purchase of one item. It is good to identify each item under hire purchase as a separate source. Usually, we use journals to be our source for interest on Hire Purchase. It is good to identify interest on hire purchase as separate source from general journals. You may write 'INT-HP-X' or other term to identify Hire Purchase Interest for item X.

17. Depreciation

This refers to depreciation of one type or category of fixed assets. It is good to identify depreciation of each one type of assets as separate source rather than lump them all together. You may write 'DEPR-X' or other term to identify Depreciation for item X.

18. Journal

This refers to general journals, ie journal which could not be conveniently identified as separate source. As we see, normally depreciation and interest on hire purchase will lump under journals. But since we can conveniently identify them as separate source, we do separate it. You may write 'JOURNAL' or other term to identify journals.

19. Closing Stock-Raw material

This refers to closing stock of raw material. Accounts staff may get the summary from store system. You may write 'CS-RM' or other term to identify Closing Stock of Raw Material.

20. Closing Stock-Finished Product

This refers to closing stock of finished product. Accounts staff may get the summary from store system staff. You may write 'CS-FP' or other term to identify Closing Stock of Finished Product.

21. Closing Stock-Semi-Finished Product

This refers to closing stock of semi-finished product. Accounts staff may get the summary from store system. You may write 'CS-SF' or other term to identify Closing Stock of Semi-Finished Product. Variances in source identified. You may identify sources differently eventhough the above samples are most common. Below are some of the differences :

Some company file their suppliers invoices in a file for each supplier, rather than ALL supplier invoice in ONE file. If this is the case, it is more appropriate if the source is written such as "INV-SUPPLIER XXX", "INV-SUPPLIER YYY", and so on. The sample in this book assume that ALL supplier invoices are kept in ONE file.

Some company file their sales invoices in the separate file for each customer, rather than ALL sales invoice in ONE file. If this is the case, it is more appropriate if the source is written such as "INV-CUST-XXX", "INV-CUST-YYY", and so on. The sample in this book assume that ALL sales invoices are kept in ONE file.

However, some companies generate more than one internal copies for sales invoice. They keep one copy in a master file and another copy in the respective customer file. If this is the case, you can use either way as source.

Why we want to identify sources ?

Apparently, source can be said best as a group of document that get filed together. Proper identification of source will help you easily refer back and search to your physical (paper) document.

Entering data in sheet Data column SRC Notes on Opening Balance

Notes On Entering Opening Balance Data

No double entry

Note that the opening balance data does not involve double entry (Debit and Credit) transactions. It is merely a listing of balances from closing of previous accounting year.

When you enter a Debit item data, there is no corresponding Credit account and amount to write. So, just leave blank the Credit account column and Credit amount column. For the remaining cells in the row, just write NR (for Not Relevant).

Similarly, when you enter a Credit item, there is no Debit account and amount to write. So, just leave blank the Debit account column and Debit amount column. For the remaining cells in the row, just write NR.

However, instead of a blank, you can create an account name such as OPENING BALANCE.

Put Debit and credit items on different rows

Do not write a Debit item data on the same row as a Credit item data. This is because you will encounter difficulties when you want to add a note (comments) to both items since there is only one note column. Also, if sales invoice is pitted against purchase invoice, both will chase for only one 'due date' column.

However, if you can manage these problems, you can put Debit and Credit items data on the same row. This can save rows.

More details

It is better that the opening balances items data contain more details. For instance, instead of one figure for a customer, list down all unpaid invoices for that customer. Instead of one figure for whole staff advance, list down all unpaid staff advances for every staff. Also, instead of one figure for a supplier, list down all unpaid purchase invoices for the supplier, etc.

If you are transferring data from other system, you may need lots of time to record the details, especially if the system is not compatible with Excel.

If you already using this system, transferring details to the following year file is very easy. This is because you will have a ready list of unpaid invoices etc row by row. It is just a matter of selecting and transferring (or copying and deleting) the data.

Check data

After you finish entering the Opening Balances items data, check that its absolute total debit amounts agree with total credit amounts. Just look at the cells H1 and J1.

42

Entering data in sheet Data column B C D E page 31

Filling cells under column title ‘DATE’ (Column B).

Under the column title 'DATE', enter the date of the transactions as appear on the document. The date of transactions is usually typed on the document.

Remember that to enter date, use / (forward-slash) sign to separate month, day and year. Remember also that Excel expect you to enter month first, followed day and then year. Internally, Excel keep date by month first, followed by day and year. So, if you enter 1/2/2002, it will mean 2nd January. It can avoid mistake by Excel if you enter year in 4 digits rather than 2 digits.

Make sure the cell that contain the date is formatted appropriately. If you choose format 'm/d/yyyy', then you enter date 1/2/2002, it will appear the same order ie 1/2/2002 (Internally, this read as 2 January 2002). But if you choose format 'd/m/yyyy', then you enter date 1/2/2002, you will see different order ie 2/1/2002 (though this still read 2 January 2002, internally). So, be careful with formatting of date.

Filling cells under column title ‘MTH’ (Column C)

Under the column title 'MTH', enter the month of the transactions. Use numbers to indicate month, such as 1, 2, 3 etc. This can save hard disk space.

This column is useful if you later want to analyse data according to month.

You can use formula to extract month from the date in column DATE, but I do not recommend it. This way (not using formula), eventhough the date say 2/28/2002 (Month :February), you may want to include this transaction under month 3 (March) when you analyse data or in your report, for whatever reason.

Use 0 (zero) to indicate month of the opening balances items.

Filling cells under column title ‘SRC NO’ (Column D)

Under the column title 'SRC NO', enter the serial number or reference number of the source document. For example : Sales Invoice number, Purchase Invoice number, journal number, voucher number, etc. If there is no reference number, leave blank or you can replace with some other notes.

Filling cells under column title ‘OTHER REF./ NO.’ (Column E)

(OTHER REF./NO. refers for OTHER REFERENCE OR NUMBER)

Under the column title 'OTHER REF./NO.', enter the other serial or reference number of the source document or other document relevant to the source document. For example : You have enter sales invoice number under SRC NO, so you may want to enter the PO number under OTHER SRC NO. If there is no other reference number, leave blank or you can replace with some other notes.

Entering data in sheet Data column ‘NOTE’ (Column F G H)

Filling cells under column title ‘NOTE’ (Column F)

Under the column title 'NOTE', enter any note you want regarding the transaction. If there is no note to write, you can leave blank.

Filling cells under column title ‘ACCOUNT-DEBIT’ (Column G)

Under the column title 'ACCOUNT-DEBIT', enter the account name that you want it to be debited. The account name must already exist in the Chart of account (in TB sheet). If the account name not yet exist, update the Chart of account (in TB sheet).

Excel is very sensitive about spelling. A single dot or a single space can mean a different item. For example, 'My World" with "My World" is considered different, because of more spaces in the latter. "MyWorld" with "My.World" is also different, because of a dot in the latter. Another example, 'My World" with "My World " is considered different because of the additional space after the end of the latter word . This is difficult to trace since we cannot see the space character.

To check whether there is an additional space at the end of the account name, activate the cell and press F2. Look at the position of the caret. You will notice whether there is any space before the caret.

Or, the formula in cell under column title 'CHECK ACCOUNT-DEBIT' will flag you if the account does not yet exist (may be due to slight different in spelling). However, small letter or capital letter is not treated as different.

Filling cells under column title ‘RM-DEBIT’ (Column H)

Under the column title 'RM-DEBIT', enter the amount of the account name that you debited. For example, 1000. If you format appropriately, it will appear as "1,000.00" or "RM 1,000.00" etc.

Entering data in sheet Data column ACCOUNT CREDIT (Column I)

Entering Data In The ACCOUNT-CREDIT Column (Column I)

In the ACCOUNT CREDIT column, enter the account names that you want to be credited. The account name must already exist in the Chart of accounts (in TB sheet). If an account name does not yet exist, update the Chart of accounts (in TB sheet).

As explained at the top of the page, excel is very particular about spelling. A single dot or a single space can mean a different item.

33
Entering Data In The RM-CREDIT Column (Column J)

In the RM-CREDIT column, enter the amounts of the account names that you credited. For example, -1000. Precede the credit amount with minus sign. The minus sign indicates a credit amount. If you have formatted the column appropriately, it will appear as "(1,000.00)" or "(RM 1,000.00)" etc.

Entering Data In The CHECK ACCOUNT-DEBIT Column (Column K)

In the CHECK ACCOUNT DEBIT column, cell K5 should already contain the following formula:

=IF(ISERROR(VLOOKUP(G5, INDIRECT("TB!A5: A2000"), 1, FALSE)), "NOT OK", "OK")

If there are more entries in the cells below it, just copy down the formula. The formula will return NOT OK if the account name in the column ACCOUNT DEBIT is not listed in sheet TB. The formula will return OK if the account name in column ACCOUNT-DEBIT is listed in column A in sheet TB.

Review this column to check for non-existent account names.

Entering Data In The CHECK ACCOUNT-CREDIT Column (Column L)

In the CHECK ACCOUNT CREDIT column, cell 15 should already contain the following formula:

=IF(ISERROR(VLOOKUP(IS, INDIRECT("TB!A5: A2000"), 1, FALSE)), "NOT OK", "ОК")

If there are more entries in the cells below it, just copy down the formula. The formula will return NOT OK if the account name in the column ACCOUNT CREDIT is not listed in sheet TB. The formula will return OK if the account name in column ACCOUNT CREDIT is listed in sheet TB

Review this column to check for non-existent account names.

34

Entering data in sheet Data column BANK RECON (Column M) Page 35

Entering Data In The BANK RECON Column (Column M)

In the BANK RECON column, state the month in which each amount of bank receipt and payment appear in the statement received from bank. For example, you can write 1, 2, 3 etc. or jan, feb, mar, etc

If the amount of Debit or Credit is not related to a bank account (and not subject to reconciliation), state NR (for Not Relevant or other term you want to use).

If the amount of Debit or Credit is related to a bank account (and subject to reconciliation), state open (until it is reconciled).

This column will help us prepare a Bank reconciliation statement.

Entering Data In The DEBTORS STTMNT Column (Column N)

In the DEBTOR STTMNT column, put a mark to indicate whether our sales invoice has been paid. Use pd to mark the paid invoice. Use open to mark invoice still unpaid. If the source is not related to our sales invoice (and not subject to our monitoring of payment), mark NR (for Not Relevant).

This column is useful to find out paid and unpaid invoices by our customers.

Entering Data In The DEBT AGE Column (Column O)

In the DEBT AGE (days) column, cell 05 should already contain the following formula:

=IF (NS="open", CutOffDate-85,"NR")

Just copy the formula to the cells below it.

Please refer to the note on page 11 on how to set the 'Cut OFF Date'.

35

Entering data in sheet Data page 36

Entering Data In The DEBT AGE GROUP Column (Column P)

In the DEBT AGE GROUP column, cell P5 should already contain the following formula

=IF(N5="open",IF(O5<30,"0-30",IF(O5<60,"30-60",IF(O5<90,"60-90",IF(O5<120,"90-120",">120"))))),"NR")

Just copy the formula to the cells below.

Entering Data In The CREDITOR STTMNT Column (Column Q)

In the CREDITOR STTMNT column, put a mark to indicate whether our suppliers invoice has been paid. Use pd to mark the paid invoice. Use open to mark invoice still unpaid. If the source is not related to our suppliers invoice (and not subject to our monitoring of payment), mark NR (for Not Relevant) or leave blank.

This column will help us locate unpaid suppliers invoices.

Entering Data In The CREDIT AGE Column (Column R)

In the CREDIT AGE (days) column, cell R5 should already contain the following formula:

=IF(Q5="open", CutOffDate-B5, "NR")

Just copy the formula to the cells below.

Entering Data In The CREDIT AGE GROUP Column (Column S)

In the CREDIT AGE GROUP column, a cell S5 should already contain the following formula:

=IF(Q5="open",IF(R5<30,"0-30",IF(R5<60,"30-60",IF(R5<90,"60-90",IF(R5<120,"90-120",">120"))))),"NR")

Just copy the formula to the cells below it.

36

Entering data in sheet Data page 37

Entering Data In The HOLD Column (Column T)

In the HOLD column, state the amounts of payments or receipts already recorded in the column RM-DEBIT or RM-CREDIT but not effectively released or received.

If the amount of Debit or Credit is not related to bank receipt or payment, or not subject to holding, just mark NR (for Not Relevant).

This column will help determine the actual true cash still available in hand or in bank.

Entering Data In The TERM Column (Column U)

In the TERM column, state the credit terms given to our customers or by our suppliers. The term should be stated in term of days, e.g. 30, 60, 90 etc. If data is not related to credit term, write NR (for Not Relevant).

Entering Data In The DUE DATE Column (Column V)

In the DUE DATE column, cell Q5 should already contain the following formula:

=IF(P5="NR","NR", B5+P5)

Just copy the formula to the cells below.

This column will help us to monitor dates of payment/receipt due.

Entering Data In The ITEM PURCHASED Column (Column W)

In the ITEM PURCHASE column, write the names of the items we purchase. Usually this is stated in the supplier's invoice.

If the document is not related to purchase of an item, just write NR (for Not Relevant).

This column will help us to monitor the items we purchase.

37

Entering data in sheet Data page 38

Entering Data In The ITEM SOLD Column (Column X)

In the ITEM SOLD column, write the names of the items we sell. Usually this is stated in our sales invoice.

If the document is not related to selling of an item, just write MR (for Not Relevant).

This column is useful if we want to monitor items we sell.

Entering Data In The QTY Column (Column Y)

In the QTY column, write the quantities of the items we sell or purchase.

If the document is not related to item that we sell or purchase, just write HR (for Not Relevant).

This column will help us to monitor the quantities of items we sell and items we purchase.

Entering Data In The UNIT Column (Column Z)

In the UNITT column, write the measurement unit of the quantity of the item we sell or purchase.

If the data is not related to item that we sell or purchase, just write NR (for Not Relevant).

This column serve to indicate the unit of measurement we use to quantify the items (eg KG, MT, etc)

Entering Data In The PRICE Column (Column AA)

In the PRICE column, write the prices of the items we sell or purchase. If the data is not related to item that we sell or purchase, write NR (for Not Relevant).

This column will help us to monitor the prices of items we sell and items we purchase.

38