See rows of data for a particular item in column A (SRC)

Say you have more than one source name in column A and you want to see only rows of data of a particular item. At the layout dialog, set it like the following :

Please notice what button is in which box.

Below is a sample result :

This sample results shows all details (INV NO, INV DATE, MTH, YEAR, ITEM SOLD, RM), in rows, related to source INV.

See rows of data of only a particular source in a particular month.

Say you want to see data of a particular source in the month of March 2003 (written as '03 03'). The setting in the Layout dialog is like so :

Please notice what button is in which box.

The sample result is like this :

See rows of data of a particular date

Say we want to identify a date from column DATE and only rows of data related to that date will be visible. The sample setting in Layout dialog :

Notice what button is in which box.

The sample result :

See rows of data of a particular period

Say we want to identify a period from column DATE and only rows of data related to that period will be visible.

The sample setting in Layout dialog :

Notice what button is in which box.

The sample result :

We can select what dates to be taken into account :

See rows of data of a particular month

Say we want to select a month from column MTH and only rows of data related to that month is visible.

The sample setting in Layout dialog :

Notice what button is in which box.

The sample result :

See rows of data of a particular 2 months

Say you want to see data for 2 items, eg '01 03' and '02 03' from same column MTH..

The sample setting in Layout dialog :

The sample result :

We can select the months that we want :

See rows of data of a particular Customer

Say we want to see data of a customer.

The sample setting in Layout dialog :

The sample result :

See rows of data of a term in TERM column

Say we want to see only rows of data related to a term.

The sample setting in Layout dialog :

The sample result :

See rows of data of a due date

Say we want to see details of data related to a due date will be visible.  

The sample setting in Layout dialog :

The sample result :

See history of selling prices of an item

Say we want to review prices of a selling item.

The sample setting in Layout dialog :

The sample result :

See data of more than 2 particular months

Say we want to see data of months '01 03', '02 03' and '03 03'.

The sample setting in Layout dialog :

We can choose the months involved :

The sample result :

See data of a particular 2 customers

Say we want to see data of 2 particular customers.

The sample setting in Layout dialog :

We select (tick) the customers :

The sample result :

See rows of data of more than 2 customers

Say we want to see data of 3 customers.>  The sample setting in Layout dialog :

We select the customers :

The sample result :

Get a list of all outstanding (unpaid) sales invoices

We may want to get a get list of all unpaid sales invoices and its total. The sample setting in Layout dialog :

The sample result :

Get a list of unpaid sales invoices to one particular customer

We may want to get a get list of all unpaid invoices to a particular customer and its total. The sample setting in Layout dialog :

The sample result :

Get a list of a particular debt-age group

We may want to get a get list of all data of a particular debt-age group. The sample setting in Layout dialog :

The sample result :

Get a list of particular 2 debt-age groups

Say we want to get data for 2 debt-age groups from column 'DEBT-AGE GROUP'. The sample setting in Layout dialog :

Select the age-group :

The sample result :

Get a list of payment credited into a particular bank

We may want to get a get list of data of a particular bank where we bank-in the payments we received from our customer. The sample setting in Layout dialog :

The sample result :

Get a list of payments received in a particular month

We may want to get a get list of all data of payments we received from our customer in a particular month. The sample setting in Layout dialog :

The sample result :

Get a list of payments received in a particular month and credited into a particular bank

Say we want to see list of a payments credited into a particular bank in a particular month. The sample setting in Layout dialog :

Select the banks we want :

The sample result :

Get a list of payments in 2 particular months

Say we want to see all payments received in only 2 particular months. The sample setting in Layout dialog :

Select the months we want :

The sample result :

Get a list of customers payments in a particular date

We may want to get a get list of all payments received in a particular date. The sample setting in Layout dialog :

The sample result :

Get a list of customers payments in a particular period

Say we want to see payments received from customers in a period of time (from one date to another date). The sample setting in Layout dialog :

Select (tick) the dates :

The sample result :

Other filtering

You can set many more filters on your own using the PivotTable operation. Don't hesitate to do trial and error. It would not harm your data in sheet 'Data' at all.

NOTE

* When you use PivotTable operation to filter data, it is not necessary to create one PivotTable for one filtering task, because you can easily modify the existing PivotTable through the layout dialog. Thus, you can use one PivotTable for many filterings, though you can see only one filtering at a time.

* Filtering using PivotTable is not an alternative to using menu 'Data | Filter'. Instead both are complimentary. Some result that we want could only be obtained using 'Data |Filter' but there are other results that could only be achieved using PivotTable. Yet there are many results that could be achieved using both methods.    

Getting Totals

We can get totals using formulas and/or Pivottables. The following chapters will show you how to use both methods to extract relevant totals from data in sheet 'Data'.

Getting Totals Using PivotTable Operation

We can use PivotTable function to produce a summary totals and/or detailed report. We use menu 'Data | PivotTable and PivotChart Report' to produce the reports. We will utilise the data in sheet 'Data' as the source of the PivotTable reports.

This chapter would not deal with the basic of creating and handling Pivottable. You are expected to already familiar with Pivottable. If not, then a separate chapter is provided for you to learn how to handle PivotTable.

Below are some of the sample totals reports and how we produce it using PivotTable operation.

Total sales

Set the layout dialog like so :

Below is a sample result of the above setting :

Total payments received

Set the layout dialog like so :

Below is a sample result :

Total debtors' balance

Set the layout dialog like so :

Below is a sample result :

Total daily Sales

Set the layout dialog like so :

You should see a result like the following :

Total monthly Sales

Setting at the Layout dialog :

Sample result :

Total Monthly Sales to each customer

At the Layout dialog, set it like so :

The result look like the following :

Total Sales of each items

The setting :

The result :

Total Quantity of each item sold

This Layout :

Produces this result :

Total Quantity Sold to each customer

At the layout dialog :

The result is like this :

Total quantity sold in each month

The sample setting :

The sample result :

What is due for receipt today

The sample setting :

The above setting will show all due dates, as follows :

We can then select one due date only - when we click at the (All) drop down combo box, a list of all available date will appear. Select (click at) one date and then click the OKbutton :

The result will show only invoices (and its particulars) due for receipt in the date we select :

Note : You can hide the row containing the word 'Total'. Right-click at the cell which contain the text. A floating menu will appear. Select Hide. That's it.

>

The result we want :

Selecting more than one due date

In the Layout dialog :

The result :

Initially, all date will be shown. Then, we can filter it. Click at DUE DATE button. A list of date will appear. Put a mark on the date that you are interested and clear the rest.

The result should show only the date that you put a mark.

Total monthly sales to each customer

In the Layout dialog :

The sample result is as follows :

Those are samples of using PivotTable operation to derive summary totals. You can do your own operations.

Getting Totals Using Formula

Getting totals using formula is not an alternative to PivotTables. Both are complimentary to each other. You may can get a total using PivoTable which you cannot achieve it using formula. And you may can get a total using formula which you cannot achieve it using PivotTable. Yet there are totals which you can get by using either one.Then, it is up to you to use which method or you may use both ways.

To get totals using formula, it is better that we use a separate sheet. In this book, we use sheet 'Totals-Formula' for this purpose.

Below are examples of totals that we want to get and the formula we use to get it done.

Total Sales value

Sample result :

The formula :

Here we use SUMIF function in our formula to get the total sales value in year 2003.

Note : In the above function, we estimate that our data in sheet 'Data' would occupy until row 1000. So, replace the 1000 with your own estimation.

Total monthly sales

Sample :

The formula revealed :

Please note that :

*   The function SUMIF is used to compute the total.

*   The cell A9 is set to behave like a drop-down list box. Please read a chapter on how to turn a cell into a drop-down list box.

Total monthly sales (showing all months)

The above sample shows monthly sales by one month at a time. We can also set such that we see all months :

The formulas are exposed below :

Notice that SUMIF function is used.

Total Sales (Quantity)

Sample result :

The formula behind cell B30 :

The formula behind cell C30 :

Notice that SUMPRODUCT function is used.

Monthly Sales (Quantity)

Sample report :

The formula in cell B35 :

The formula in cell C35 :

Monthly sales (Quantity) - See all months :

The above sample shows monthly sales quantity by one month at a time. We can also set such that we see all months :

The relevant formulas in column B :

The relevant formulas in column C :

Sales (RM) to a customer

Sample report :

See the formula :

The above report shows only one customer at a time. We can show all customers :

The formulas are :

Monthly Sales (RM) to a customer

Sample report :

For your info, cells in A75 and B75 are turned into drop-down list boxes.

The formula exposed :

Notice that we use SUMPRODUCT function.

The above method shows only one month and one customer at a time. We can create a format where we show all months and all customers :

The sample formula in column B are :

A slight variation of the report is we show one month and all customers :

The formulas :

Or, we can show all months for one customer at a time :

See the formulas :

Monthly Payments (RM) by Customer

Sample report :

Formula in cell C123 :

We can also set another report with different structure, say showing all months for one customer :

Note that cell B129 is turned into a drop-down list box.

See the formulas below :

The formulas are using SUMPRODUCT function.

Another small variation in the reporting structure that we can make is by showing one month at a time but for all customers :

Notice that cell B149 is set to become a drop-down listbox.

See the formulas below :

SUMPRODUCT function is used to get the sums.

The following structure shows all customers for all months :

By using SUMPRODUCT function we can easily derive the total amounts.

Current Balance (RM) of a customer

Sample report :

The formula in cell B172 :

The above reporting shows only one customer at a time. We can show all customers :

See the formulas below :

Total sales (Quantity in $)

Sample reporting :

The formula in cell B188 :

The formula in cell C188 :

Monthly Sales (Quantity in $ ) - Current Year - one month at a time

To easily select the month in cell A193, turn the cell into a drop-down list.

Using SUMPRODUCT function in cell B193 :

Using SUMPRODUCT function in cell C193 :

Monthly Sales (Quantity in $ ) - Current Year - see all months

Sample report :

Exposing the formulas in column B :

Monthly Sales (Quantity in $ ) to a Customer - Current Year - One customer at a time

Example :

Exposing the formula in cell C218 :

Exposing the formula in cell D218 :

Monthly Sales (Quantity in $ ) to Customer - Current Year - One customer All months

Example :

The formulas for column B :

Monthly Sales (Quantity in $ ) to Customer - Current Year - One month All customers

Sample report :

Formulas in column B :

Monthly Sales (Unit KG in $ ) to Customers - Current Year - All Months All Customers

Sample report :

Below, one formula (ie in cell B264) is shown for you :

Monthly Sales (Unit PCS in $ ) to Customers - Current Year - All Months All Customers

A similar report for unit PCS can be devised :

Total Item Sales (RM) - Current Year

The report :

See the formula at cell C285 :

Total Items Sold (RM) - Current Year - All items

The report :

See the formula exposed below :

Items Sold (RM) - Monthly

The report :

The formula :

Items Sold (RM) - Current Year - All Items All Month

The report :

The formula :

Items sold (In Quantity) - Year Total

The report :

The formula :

Total Items Sold (Quantity) - Year Total - See All Items

The report :

The formula :

Item Sold (Quantity) - Monthly

The report :

The formula :

Items Sold (Quantity) - All Months All Items

The report :

The formula :

Total Payments (RM) by customers - Current Year

The report :

The formula :

Checking outstanding balance

The report :

The formula :

Issue : If accounting period is not from 1 January to 31 December

Most businesses have their 12 months accounting period starts from 1^st January and ends at 31^st December. For example 1^st January 2004 to 31^st December 2004. For this business, every month in the period comes under the same business year, ie 2004.

However, some businesses have their 12 months accounting period starts not from 1^st January. For example, from 1^st March 2004 to 28^th February 2005. For this business, usually the business year is mentioned as the year in which the period starts. In this case the business starts in 2004, so the business year is 2004. Months January and February 2005 comes under business year 2004, not 2005.

Now remember column D (YEAR) and column T (PMT YEAR). This column is meant for business year (accounting year), not calendar year. So if the accounting year in your business is from 1^st March 2002 to 28^th February 2003, and the date of the invoice falls between 1^st January 2003 to 28^th February 2003, make sure you write 2002 instead of 2003 in column D (YEAR). Similarly, any date of payment which falls between 1^st January 2003 to 28^th February 2003, make sure you write 2002 instead of 2003 in column T (PMT YEAR). This way, if you follow the formulas above, the totals for a year would means as the total for the accounting year and, if you do not follow the accounting year for column D (YEAR) and T (PMT YEAR), the totals for a year would means as totals for the calendar year. So, be cafeful a bit here.

Issue : Totals / Balances up to a cut-off date

If you record is up-to-date and then you want to find figures (totals or balances) only up to a certain invoice date (a cut-off date) which is earlier than the current date, you can do that by simply making a copy of the up-to-date file and work on the copy file.

In the copy file, identify dates in column B (INV DATE) which are later than the cut-off date. Delete the rows containing the dates. Then, in the column R (PMT DATE), identify also the dates which are later than the cut-off date. Clear the entry in the cell. Clear also the contents of the relevant cells in column S (PMT MTH), column T (PMT YEAR), column U (PMT RM) and column V (PMT CREDITED INTO). This would make all formulas in the copy file reflect the totals / balances up to that cut-off-date.

A proper naming of the copy file will greatly help you in recognising the file contains data up to what date. For example a name 'Receivables-31 May 2004' will easily tells you that this file contains data up to 31^st May 2004.

Below are the steps how to do :

To identify dates in column B (INV DATE) which are later than the   cut-off date, use menu ' Data | Filter | AutoFilter | Custom '. Set the Custom AutoFilter dialog similar like the following picture :

Clicking OK would filter the data. After the filtering, hide row 1 (use menu 'Format | Row | Hide '). Then, use menu ' Edit | GoTo | Specials | Visible Cells Only ' to automatically highlight the relevant rows. Then, use menu ' Edit | Delete Row ' so that Excel delete the highlighted rows.

Then, select all cells in the sheet by clicking on the rectangle on the left of column indicator (above row indicator) :

Then use again the menu ' Data | Filter | AutoFilter ' to remove the filtering. You should again see row 1 and the rest of the rows. Save your file.

Next, to identify dates in column R (PMT DATE) which are later than the cut-off date, use menu ' Data | Filter | AutoFilter | Custom '. Set the Custom AutoFilter dialog accordingly.

After the filtering, highlight the relevant cells in columns R, S, T, U and V. Then, press ' Delete ' key to delete the highlighted data.

Then use again the menu ' Data | Filter | AutoFilter ' to remove the filtering. Save your file.

The file should now contain only relevant data up to the cut-off date.

If you think that you make a mistake on the operation, you can always create another copy from the currently up-to-date file and work again with the copy file. Or, you can avoid making another copy if you do not immediately save the result of the above operation.

Creating Statement Of Accounts

>Basically, a Statement of Account shows name and address of a customer, the amounts it owe to us and the sufficient details about the amounts.

>The next 2 chapters will shows you 2 alternatives to creating Statement of Accounts : using PivotTable and using Formulas.

Creating Statement Of Accounts Using PivotTable

We are interested to send a Statement of Accounts to each of our customer at certain intervals, usually monthly.

Sample of Statement :

Lets see how the above Statement is built :

* The words "STATEMENT OF ACCOUNT" (on top of the page) and "Please notify us within 14 days after receipt of this statement" (at bottom of the page) are built using Word Art (menu 'Insert | Picture | WordArt') instead of typing the words into the cell. The advantage of using WordArt is that you can move it around and reposition it anywhere you like, and change the font style and size. You can also use 'Text Box' instead (within the Drawing Toolbox). Using the Text Box has the same advantages as the WordArt.

*   The date of the statement in cell H4 is derived by using this function :

=NOW( )

and a suitable date formatting is applied. You can overwrite this cell with something else.

* The cells A5, B5 and C5 are merged into one cell so that it can accomodate long customer names. The top-left cell (A5) address become the address of the merged cells (as you already know). You can change how many cells are merged.

* The merged cells A5, B5 and C5 is turned into a drop-down list. When we activate it, a button will appear on its right side. When we click the button, a list of customers names will appear.

We can set the list so it contains the customer names. This is how to set : Activate the merged cell. Click menu 'Data | Validation'. A dialog with title 'Data Validation' will appear. Activate 'Setting' tab. Under 'Allow' label, select 'List'. Make sure the box on the left of 'In-cell dropdown' label is ticked. Under 'Source' label, write this : =Customers (Make sure this name Customers refers to a range of cells in a separate sheet which contain the names of customers. You can use other name for the range). Click OK. The dialog will disappear. Now you can select a customer name into the merged cell instead of typing it out.

* When we select a customer name fromthe drop-down list in cell A5, the address of the customer will be automatically inserted into cells A6 to A9. This is done by using VLOOKUP function :

In cell B6 we put this formula :

=VLOOKUP($A$5,Customers!$A$2:$E$1000,2,FALSE)

In cell B7 we put this formula :

=VLOOKUP($A$5,Customers!$A$2:$E$1000,3,FALSE)

In cell B8 we put this formula :

=VLOOKUP($A$5,Customers!$A$2:$E$1000,4,FALSE)

In cell B9 we put this formula :

=VLOOKUP($A$5,Customers!$A$2:$E$1000,5,FALSE)

This formula expects that we have a sheet named 'Customers' and it contains customer names in column A (starting from row 2 until 1000), first portion of   the address in column B (from row 2 to 1000), second portion of the address in column C (from row 2 to 1000), third portion of the address in column D (from row 2 to 1000) and the fourth portion in column E (from row 2 to 1000).

After the address, we put summary of ageing. Here we use PivotTable operation to produce it.

We expect that you should have already familiar with steps to produce a PivotTable up to the Layout stage.

Below is a screenshot of part of the layout in the Layout dialog :

Please notice the location of the buttons : CUSTOMER button is in the PAGE box. DEBT AGE button is in the ROW box. BAL button is in the DATA box. There is no button in the ROW box.

Below is the sample result :

After the summary, we want to produce a more details accounts . Below is the setting in the layout dialog :

Please notice the location of the buttons :

CUSTOMER button is in the PAGE box. INV NO, INV DATE, RM, DUE DATE, PMT STATUS, PMT DATE buttons (in that order) are in the ROW box. BAL button is in the DATA box. There is no button in the COLUMN box.

Below is a sample result :

The above sample results shows details about one customer 'CUSTOMER C'.

With the 2 PivotTables, you would notice that the Customer name appear twice ie on both of the PivotTables. For printing purpose, you may want the name to appear once only. Preferably you want the name to appear on the top PivotTable. So, you can hide the row that contain the customer name on the bottom PivotTable.

If you want to prepare for another customer, you have to unhide the row and repeat the process.

Now you can print the Statement to your letterhead and send to your customer. You can do away with letterhead if you want, like many businesses do.

Creating Statement Of Accounts Using Formula

In the previous chapter, we construct a Statement of Account structure in a separate sheet 'Sttmnt-P' where we use PivotTable operation to get the summary and details for the statement.

In this chapter, we will create a structure for a Statement of Account in a separate sheet 'Sttmnt-F' but we use formulas to get the summary and details for the statement.

It is best that we explain how it is structured based on a picture of the sample :

* This structure is not the best. It serves as guidance only. However, it contains important ingredients that should exist in a Statement of Account. You can learn how to use formulas to extract figures into the statement.

* The above structure may be printed into a letterhead. So, you must set the top margin and the left margin of the print area and other relevant print settings using menu 'File | Page Setup'.

* The words "STATEMENT OF ACCOUNT" (on top of the page) and "Please notify us within 14 days after receipt of this statement" (at bottom of the page) are built using Word Art (menu 'Insert | Picture | WordArt') instead of typing the words into the cell. The advantage of using WordArt is that you can move it around and reposition it anywhere you like, and change the font style and size. You can also use 'Text Box' instead (within the Drawing Toolbox). Using the Text Box has the same advantages as the WordArt.

*   The date of the statement in cell I5 is derived by using this function : =NOW( ) and a suitable date formatting is applied. You can overwrite this cell.

* The cells B5, C5 and D5 are merged into one cell so that it can accomodate long customer names. The top-left cell (B5) address become the address of the merged cells (as you already know). You can change how many cells are merged.

* The merged cells B5, C5 and D5 is turned into a drop-down list. When we activate it, a button will appear on its right side. When we click the button, a list of customer names will appear.

We can set the list to contain the customer names. This is how it is set : Activate the cell. Click menu 'Data | Validation'. A dialog with title 'Data Validation' will appear. Activate 'Setting' tab. Under 'Allow' label, select 'List'. Make sure the box on the left of 'In-cell dropdown'label is ticked. Under 'Source' label, write this : =Customers (Make sure this name Customers refers to a range of cells in a separate sheet which contain the names of customers. You can use other name for the range). Click OK. The dialog will disappear. Now you can select a customer name into the cell instead of typing it out.

* When we select a customer name, the address will be automatically inserted into cells B6 to B9. This is done by using VLOOKUP function :

In cell B6 we put this formula :

=VLOOKUP($B$5,Customers!$A$2:$E$1000,2,FALSE)

In cell B7 we put this formula :

=VLOOKUP($B$5,Customers!$A$2:$E$1000,3,FALSE)

In cell B8 we put this formula :

=VLOOKUP($B$5,Customers!$A$2:$E$1000,4,FALSE)

In cell B9 we put this formula :

=VLOOKUP($B$5,Customers!$A$2:$E$1000,5,FALSE)

This formula expects that we have a sheet named 'Customers' and it contains customer names in column A (starting from row 2 until 1000), first portion of   the address in column B (from row 2 to 1000), second portion of the address in column C (from row 2 to 1000), third portion of the address in column D (from row 2 to 1000) and the fourth portion in column E (from row 2 to 1000).

* The range B11 to I12 is used to show the ageing of the debt. The cells B11 to and B12 are merely contain the labels. The range C12 to H12 is where the ageing is shown. We use the SUMPRODUCT function to extract the ageing from sheet 'Data' :

The formula in C12 is :

=SUMPRODUCT((Data!G2:G30=B5)*(Data!X2:X30=C11)*(Data!V2:V30))

The formula in D12 is :

=SUMPRODUCT((Data!G2:G30=B5)*(Data!X2:X30=D11)*(Data!V2:V30))

The formula in E12 is :

=SUMPRODUCT((Data!G2:G30=B5)*(Data!X2:X30=E11)*(Data!V2:V30))

The formula in F12 is :

=SUMPRODUCT((Data!G2:G30=B5)*(Data!X2:X30=F11)*(Data!V2:V30))

The formula in H15 is :

=SUMPRODUCT((Data!G2:G30=B5)*(Data!X2:X30=G11)*(Data!V2:V30))

The formula in I15 is :

=SUMPRODUCT((Data!G2:G30=C8)*(Data!X2:X30=I14)*(Data!V2:V30))

The formula expects that :

- the range G2:G30 in sheet 'Data' contains customer names (you can change the

  G30 accordingly), - cell B5 in the current sheet contains a customer name,

- the range X2:X30 in sheet 'Data' contains debt age groups (you can change the

  X30 accordingly),

- the range V2:V30 in sheet 'Data' contains the amount of outstanding debt (you

  can change the V30 accordingly).  

- the spelling of the age-group must exactly the same like those used in column X

in sheet 'Data'.

The formula in I12 is :

=C12+D12+E12+F12+G12+H12

This simply sums up the amounts of outstanding debt of all debt-age groups.

* In D14 is a formula :

=SUM(D17:D30)

This sums amounts in the range D17:D30. The range contains amounts of invoices addressed to the customers. You adjust the range end accordingly.

* In H14 is a formula :

=SUM(H17:H30)

This sums amounts in the range H17:H30. The range contains paid amounts of invoices addressed to the customers. You adjust the range end accordingly.

* In I14 is a formula :

=SUM(I17:I30)

This sums amounts in the range I17:I30. The range contains amounts of outstanding invoices addressed to the customers. You adjust the range end accordingly.

* In the range B16:K16 is the labels for details of transactions with the particular customer.

* Make sure cell A16 is blank. The formula in cell A17 is dependent on it.  

* The formula in cell A17 is :

=IF(ISERROR(A16+MATCH($B$5,INDIRECT("Data!G"&A16+1&":$G30000"),0)),"", A16+MATCH($B$5,INDIRECT("Data!G"&A16+1&":$G30000"),0))

This formula uses MATCH function to find the row number in column G in sheet 'Data' that consist a customer name that appear in cell C8 (in the current sheet). It will search the range in sheet 'Data' in column G starting from row obtained in cell A16 to row 30000 (you can change this number). If the search is successful, it will return the row number, otherwise a blank string.

* The formula in B17 is :

=IF(A17="","",INDIRECT("Data!E"&A17))

This will extract the Invoice Number from the row indicated in cell A17 from column E in sheet 'Data'.

* The formula in C17 is :

=IF(A17="","",INDIRECT("Data!B"&A17))

This will extract the Invoice date from the row indicated in cell A17 from column B in sheet 'Data'.

* The formula in D17 is :

=IF(A17="","",INDIRECT("Data!L"&A17))

This will extract the Invoice amount (RM) from the row indicated in cell A17 from column L in sheet 'Data'.

* The formula in E17 is :

=IF(A17="","",INDIRECT("Data!N"&A17))

This will extract the Invoice due date from the row indicated in cell A17 from column N in sheet 'Data'.

* The formula in F17 is :

=IF(A17="","",INDIRECT("Data!P"&A17))

This will extract the Invoice payment status from the row indicated in cell A17 from column P in sheet 'Data'.

* The formula in G17 is :

=IF(A17="","",INDIRECT("Data!R"&A17))

This will extract the Invoice payment date from the row indicated in cell A17 from column R in sheet 'Data'.

* The formula in H17 is :

=IF(A17="","",INDIRECT("Data!T"&A17))

This will extract the invoice payment amount from the row indicated in cell A17 from column T in sheet 'Data'.

* The formula in I17 is :

=IF(A17="","",D17-H17)

This compute the unpaid balance of the invoice. The total is in cell I14.

* Compare the total in cell I14 with the total in cell I12. The amount should be the same, otherwise check the formula.

* The formula in J17 is :

=IF(A17="","",INDIRECT("Data!W"&A17))

This will extract the invoice debt age from the row indicated in cell A17 from column W in sheet 'Data'.

* The formula in K17 is :

=IF(A17="","",INDIRECT("Data!X"&A17))

This will extract the invoice debt age group from the row indicated in cell A17 from column X in sheet 'Data'.

* Now the cells in row 17 in range A17:K17 contain the initial formulas which extract the chosen details from sheet 'Data'. Copy down each formula to cells below it to extract more details until blank result is shown in the cell.

* Knowledge of the following matters (at least) will allow you to modify the structure of the sample Statement of Account above :

- Using WordArt / TextBox.

- Merging cells.

- Converting cell or cells into a ListBox.

- Naming a range of cells.

- Using VLOOKUP function.

- Using SUMPRODUCT function.

- Using ISERROR function.

- Using MATCH function.

- Using INDIRECT function.

- Using IF function.

- Using SUM function.

Creating Ageing Report

The next 2 chapters will shows you 2 alternatives to creating Ageing reports : using PivotTable operation and using Formulas.

Creating Ageing Report Using PivotTable Operation

We can easily create an customer debt ageing report using PivotTable operation (menu ' Data | PivotTable and PivotChart Report '). If you are not familiar with using this menu, a chapter is dedicated on this subject for you to read and learn. It is expected that you already know how to arrive at the 'Layout' dialog stage in PivotTable operation. This is the most important stage in the PivotTable operation.

Below are some samples of ageing reports and how to set them in 'Layout' dialog.

Ageing Report for one customer at a time - horizontal view

Setting at the layout dialog :

Notice the location of the buttons : CUSTOMER button is in the PAGE box. DEBT AGE button is in the COLUMN box. BAL button is in the DATA box. There is no button in the ROW box..

Below is the sample result :

In the above sample, CUSTOMER B (hidden) is selected.

You can select for all like so :

To see more details like invoice number and invoice date, simply drag necessary buttons into the layout dialog :

The result would be like this :

Ageing Report for one customer at a time - vertical view

At the layout dialog, the setting is like this :

Notice the location of the buttons : CUSTOMER button is in the PAGE box. DEBT AGE button is in the ROW box. BAL button is in the DATA box. There is no button in the COLUMN box.

Sample result (showing report for CUSTOMER A) :

Similarly you can get the combined total figures for all customers by merely selecting 'All' in the Pivot head :

All customers - more details

We can use PivotTable to show ageing of all customers and we can select which details we want to show. Below is a sample layout in the layout dialog :

Notice that the CUSTOMER button, INV button and INV DATE button are in the ROW box (in that order). The DEBT AGE button is in the COLUMN box while BAL button is in the DATA box.

Result :

Creating Ageing Report Using Formula

Other than using PivotTable function, we can also create ageing report using formula. Below are some samples.

Showing one age group at a time and its amount.

Below you can see the formula in use. Basically it utilises the SUMPRODUCT function.

The formula assumes that the range Y2:Y1000 contains the age groups and the range W2:W1000 contains unpaid amounts.

Please also note that the cell A4 is turned into a drop-down list so that we can make a selection of age groups instead of typing it out into the cell. A separate chapter is provided for you to learn how to turn a cell into a drop-down list.

Showing each and every age groups and the amounts and percentages.

Sample report :

Below you can see the formulas.

Again, the SUMPRODUCT function is the function being applied.

Showing one customer ageing at a time with percentages.

Below is a sample.

(Notice that cell A20 is turned into a drop-down list).

The formula is shown below :

Showing one customer and one age group at a time

(Here the cells A46 and B45 are converted into drop-down list).

The formula is shown below :

Showing all customers and all age groups.

The formulas is not shown. But by using the SUMPRODUCT function, you should be able to return a similar result.

Generating Reports For Ledger Accounting

Usually, accounts people will request certain information on monthly basis for ledger updating. These are the items they usually want :

1. Total Sales in each month.

2. Total Payments in each month.

3. Balances at end of each month (for cross-checking).

Total Sales in each month

The entry in the ledger would be :

DEBIT DEBTORS CONTROL    RMXXXX

CREDIT SALES            RMXXXX

The amount can be easily obtained using PivotTable or formula. Figures are obtained from column RM. Please refer to chapter on ' Getting Totals Using PivotTable ' and ' Getting Totals Using Formula '.

Total Payments by debtors in each month

The entry in the ledger would be :

DEBIT        BANK        RMXXXX

CREDIT      DEBTORS CONTROL   RMXXXX

The amount can be easily obtained using PivotTable or formula. Figures are extractable from column PMT-RM. Please refer to chapter on ' Getting Totals Using PivotTable ' and ' Getting Totals Using Formula '.

Balances at end of each month (for cross-checking)

Once you know the opening balance, total sales and total payments, you should be able to calculate the balance at end of each month.

Reviewing And Checking Data In Sheet 'Data'

A review of data in sheet DATA is necessary to detect any inconsistencies on how you fill the data.   Inconsistencies in our data will render formulas, filtering, sorting and Pivottable functions not producing expected result.

Initially, you may want to review every day, but once you feel comfortable with the way you handle data, you may do it once a month. Never abandon it altogether, however.

We should take notice of the following matters :

Row 1

Cells in Row 1 in columns A to Y must contain data titles.

Data Area

Check data area using menu ' Edit | Go to | Specials | Current Region | OK '. When we click this menu, Excel will highlight the data area. Check the highlighted area whether it is correct (or whether your data area is not correctly recognised by Excel).

If you feel that clicking menu ' Edit | Go to | Specials | Current Region | OK ' takes too many clicks, you can use 'Select Current Region' icon. You can extract this icon and put it in the toolbar so that it is always available. One click on the icon will highlight the data area Excel recognises. If you are not sure how to extract this icon, a separate chapter is provided for you to read about it.

Checking the Outstanding balance.

The total Sales (in column L) minus payments by customers (in column U) should equals the balance outstanding (in column W). But if somehow there is an entry in column U but not in the corresponding cell in column W, the balance would not be correct.

We can use formula to do the checking.

The 'Checking result' should show 0 (zero).

See the formulas :

Making sure that there is no gap in data entry in certain columns

It is important that there is no gap (blank cell between filled cells) in certain columns. This is because these columns are commonly used in Pivottable operation and formulas. A missing entry may render the result of the formula or PivotTable not as we expect. The columns are A (SRC), B (INV DATE), C (MTH), D (ACC. YEAR), G (CUSTOMER) and L (RM). We can make visual review or use formula. Below is a sample of using formula :

The result '0' in column B means no gap is found. Otherwise it will show for example "1" meaning theres is one blank cell.

See the formulas used :

Make sure there is no 'Text' entry in columns B (DATE), N (DUE DATE) and R (PMT DATE)

Remember that we are supposed to enter date instead of text into these columns. We can make visual review of these columns or use formulas. Below is a sample of using formulas :

0 (zero) means there is no 'Text' entry. The formulas :

Make sure that entry in column P (PAYMENT STATUS) is either 'OPEN' or 'PAID' only.

We can make visual review of these columns or use formulas. Below is a sample of using formulas

The result of 0 (zero) in column B means okay, otherwise it will return 1 (one).

Exposing the formula :

If column P (PMT STATUS) contain 'PAID' then columns R (PMT DATE), S (PMT MTH), T (PMT ACC YEAR), U (PMT RM), V (PMT CREDITED INTO) must not be blank.

We can make visual review of these columns or use formulas. Below is a sample of using formulas :

0 (zero) means okay.

Exposing the formulas :

Number of entries in these columns must be the same : A (SRC), B (INV. DATE), C (MTH), D (YEAR), E (INV. NO), L (RM), W (BAL.), X (DEBT AGE), Y (DEBT AGE GROUP)

We can make visual review of these columns or use formulas. Below is a sample of using formulas :

This is to ensure that if one of the column has an entry then every other column must has an entry also.

Showing the formula :

Note :

* The above checkings are not exhaustive. You can devise your own method.

* You may allocate a dedicated sheet for checking using formulas.

s

A few issues may crop up in using the system. The few chapters after this would tackle the anticipated issues.

One File For One Year Or One File For Many Years ?

One worksheet can accomodate data for many years.

The maximum number of rows in each sheet in Excel is 65,536. This means, in sheet 'Data', you can keep record about that many number of invoice. Now, if in average you issue 10 invoices per working day, and each invoice occupies one row, theoretically you can use the sheet for about 6,553 days, ie about 21 years ! (assuming 312 working days per year). More than enough, isn't ?

We have an option whether to use one file for one year data or keep many years data in one file (until all rows are occupied).

The table below shows you a few issues for comparison between the two :

One file for one year record

Keeping one file for one year record means that you would exactly do that, ie only data necessary for a particular year should be in the file, while data not necessary for the year should be kept in respective files.

There are 2 types of data that is not necessary for a particular year :

1. Data that is dated later than end of the year, and

2. Data related to previous year but the invoice has been paid.

In the first year of recording, you would enter data of that year from day to day. At the end of the year, you would ensure that all invoices dated in that year and payments dated in the year is recorded. After that you will make a copy of the file. The copied file will be used for the subsequent year. You would continue recording data in the new file.

In the new file (for the new year), you would eliminate rows which invoices has been paid, leaving only rows with unpaid invoices.

Below is how you can speedily remove the rows containing the records of paid invoices in the new file :

Use menu 'Data | Filter | AutoFilter' to set the data into filtering mode. At cell P1 (PMT STATUS), select item PAID (or whatever marking you use to indicate that an invoice is already paid). As a result, you should see only rows of paid invoices. Rows of unpaid invoices will be hidden. Select one cell in Row 1 and use menu "Format | Row | Hide' (This will hide Row 1). Use menu 'Edit | Go To | Special | Visible Cells Only' (This will effectively select only the visible cells - hidden cells will not get selected). Apply menu 'Edit | Delete | Entire Row' (This will delete the visible rows). Select any cell in the first row. Click again menu 'Data | Filter | AutoFilter'. Now you should see rows that were hidden previously. These rows effectively are the title row and the rows of unpaid invoices. That is it. Save the file.

The above process should take you only about a minute.

If you are not sure whether you would do it correctly the first time, make another copy of the file and experiment it on the copy file.

The process of entering data into a file, then make a copy for a new year, then deleting unwanted rows will repeat every year.

After a few years, you will have many files. For easy searching and remembering the location of a particular file, put all file in the same folder. Give the folder an easy to remember name.

You also prefer that files are placed in chronological order, ie earlier file will appear above the later file when you open the folder. To put this order into effect, just remember the rule that a name with a number will appear above the file name with an alphabet. So, the following file names will make them appear in this order :

Receivables-0103.xls

Receivables-0203.xls

Receivables-0303.xls

Receivables-0403.xls

etc.

So, carefully naming of file will help you in the long run.

One file for records of many years

Keeping records of many years in one file is not recommended due to performance and risk factors.

All examples in this book reflect the method of keeping one year record in one file.

Cut-Off Date

In reality, after you reach a cut-off date (you determine the cut-off date), there may be a little delay in getting all source documents related to the period before the cut-off date. For example, you may receive the last Sales invoice dated 30 December 2003 on 3^rd January 2004 and the last receipt dated 29 December 2003 you receive on 10^th January 2004. Between 3^rd January 2004 and 10^th January 2004 there may be another 2 sales invoices issued dated say 4^th January 2004 and 8^th January 2004. You have two choices, whether you do not record the two invoices or you want to record the two invoices.

If you choose to record the two invoices, then after you make a copy of the current file, you have to delete the record of the two invoices from the old file to reflect that it contain records up to a cut off date.

If you choose not to record the two invoices, then after you make a copy of the current file, you have to do nothing to the old file. You only record it in the new file.

Modifying The File

Though the sample file is ready for immediate use, you can modify the system - adding more feature or removing existing feature. The only important requirement before you indulge in any modification is that (other than the knowledge of simple Excel operations) you must know how to use the following functions : SUM, SUMIF, SUMPRODUCT, INDIRECT, FILTER, PIVOTTABLE etc. However, this list is not the ultimate. You may discover new ways to implement the same thing.

Excel May Perform Slower

You may find Excel is getting slower in its reaction if you have entered data in thousands of rows (say more than 10,000 rows).You can notice it is getting slower when you press 'Enter' key, the cursor does not move to another cell immediately. You may also notice the status bar shows that Excel is still doing some calculation for 2 or 3 seconds. Some people can tolerate delay of 3 or 4 seconds. The delay is caused by too many calculations Excel has to make since it contain many data which affect the recalculations of formulas.

If you can't stand the little delay, you can avoid it by setting the calculation mode to 'manual'.

To set the calculation mode to manual, click menu 'Tools | Options'. A dialog box with title 'Options' will appear. Click on 'Calculation' tab. Select 'manual' and click 'OK'. From this point onwards, Excel will not do the calculation automatically. It means, all formulas will not get calculated or updated immediately.

To force calculation of all formulas when the calculation mode is set at manual, press key 'F9' on the keyboard. Sometimes you need to save the file first ('File | Save') before Excel responds to pressing of F9.

Another way to force calculation when it is set as 'manual' is by using 'Calculate Now' button. This button is not available as an icon on the Toolbar by default. To get this button, follow these steps : Click menu 'View | Toolbars | Customize'. A dialog box with title 'Customize' will appear. Activate the 'Commands' tab. Under 'Categories' list, select 'Tools'. Under 'Commands' list, find 'Calculate now' button. Drag the 'Calculate now' button into the toolbar area and release at the location you desire. The button will stay there. Click OK. From now on, you can use either F9 or click on that 'Calculate Now' icon to force formulas calculations. You can remove the icon by dragging it back into the 'Customize' dialog box).

The manual calculation affects all other Excel files, not just the file that you set the mode. So if you are sharing your computer and your friend does not know about your setting, please let him know.

Back -up

It is reminded again that you must have a back-up copy of all files. Below are some places where you can put your back-up files :

Floppy disk

Separate partition or volume in your hard-disk.

Separate hard-disk on your computer.

Separate computer in a networking.

CD-ROM.

Flash Disk.

On the internet.

Back To Basic

Chapters after this contain material not directly related to Receivables task but would be helpful for us in using Microsoft Excel generally.

Shortcuts / Techniques For Working Faster

Below are shortcuts and techniques you can use to work faster in Excel, particularly in using the Receivables system as shown in this book..

Quick navigation to first row

When you are at 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 will bring you to cell A1.

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 targeted cell or any cell near to 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), press 'E' key (for 'Edit'), press 'G' key (for Go To), write the address of the target cell, press 'Enter' key.

Quick navigation to beginning of a row

Say that currently you are at cell W25. When you press "Home' key, you will be brought to cell A25.

Quick navigation by naming cell

If you name a cell or a range using the Name Box, you can quickly return to the cell from anywhere by clicking the Name Box and then select the cell name from the Name Box.

To copy content of the cell above the current cell to the current cell

Press 'Ctrl' key (use a fingertip of your left hand). While still pressing the key, press 'D' key (use a fingertip of your right hand). Release all pressings. The content of the cell immediately above the current cell will be copied to the current cell. For example to copy content of cell A10 to cell A11.

To copy content of the cell on the left of the current cell to the current cell

Press 'Ctrl' key. While still pressing the key, press 'R' key. Release all pressings. The content of the cell immediately on the left of the current cell will be copied to the current cell. For example to copy content of cell A10 to cell B10

To delete a row using keyboard keys

To delete an active row using keyboards, 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').

To save the current file without using your mouse

Using keyboards, press 'Alt' key (to activate the menu bar), then press 'F' key (for 'File'), then press 'S' key (for 'Save'). Or, press 'Ctrl' + S.

To find a string (text/phrase) in a sheet

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 string you want to find, then press 'Enter' key.

To select all filled cells from the active cell to the last filled cell in the same column

Activate the top-most cell. Press 'Ctrl' key. While still pressing the key, press 'Shift' key. While still pressing both keys, press the downward arrow key. The range will be selected. Release all pressings.

To unselect all filled cells from the active cell to the last filled cell in the same column

Press 'Ctrl' key. While still pressing the key, press 'Shift' key. While still pressing both keys, press the upward arrow key. The range will be unselected. Release all pressings.

To make a cell content ready for copying

Using keyboards, press 'Ctrl' key. While still pressing the key, press 'C' key (for 'copy').

Or, alternatively, press 'Alt' key (to activate the menu bar), press 'E' key (for "edit'), press 'C' key (for 'Copy').

To paste content of a cell already copied into a current cell.

Using keyboards, press 'Ctrl' key. While still pressing the key, press 'V' key.

Or, alternatively, press 'Alt' key (to activate the menu bar), press 'E' key (for "edit'), press 'P' key (for 'Paste').

To move from current sheet to the sheet immediately on the left

Using keyboards, press 'Ctrl' key. While still pressing the key, press 'Page Up' key.

To move from current sheet to the sheet immediately on the right

Using keyboards, press 'Ctrl' key. While still pressing the key, press 'Page Down' key.

To select a contiguous range of cells downward, one cell at a time

Select the cell that you want it to become the top-most in the coming selection. Using keyboard, press 'Shift' key. While still pressing the key, press the arrow-down key once at a time.

To select a contiguous range of cells downward, one screen at a time

Select the cell that you want it to become the top-most in the coming selection. Using keyboard, press 'Shift' key. While still pressing the key, press the 'Page Down' key.

To select non-contiguous cells

Select the first cell. Press 'Ctrl' key. While still pressing the key, click on the intended cells one by one.

To insert a row without using your mouse

Press 'Alt' key (to activate the menu bar), press 'I' key (for "Insert'), press 'R' key (for 'Row').

To select all cells which have 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'. Constants represents non-formula content. Click OK.

or, not using mouse :

Press 'Alt' key (to activate the menu bar), press 'E' key (for "Edit'), press 'G' key (for 'Go To'), press 'Alt' & 'S' simultaneously (for 'Specials), press 'O' key (for 'Constants'), press 'Enter.

To go to the last row in the data area

Press 'Ctrl' key. While still pressing the key, press 'End' key. Release all pressings.

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. Use menu 'Edit | Paste Specials | Substract' when pasting.

Look at the status bar to see totals

When you highlight cells which contain numbers, you can see their total at the status bar.

Entering formula parameter using mouse by clicking on the affected cells.

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.

Using 'Delete' icon to delete row fast

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 (release) 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 back the icon into the 'Customize' dialog box.

Use 'Esc' key to dismiss a dialog

Press 'Esc' key to dismiss a dialog, rather than using your mouse to click on the 'Cancel' button.

Fill a series of number into a range in a column.

Write a beginning number into a cell. Point your mouse pointer to the cells's bottom-right handle until it change into a plus (+). While the mouse pointer is still at that , press your mouse's right button. While still pressing, drag your mouse to highlight the range area that you want to fill. 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.

Using 'Paste' icon to paste copied item

Using a 'Paste' icon, 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 (release) 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.

Using 'Select visible cells' icon to select visible cells

If you filter your data, some rows will be hidden. If you select a range which cover the hidden rows, effectively the cells in the hidden rows are still selected. To actually select the visible cells only, we can use the 'Select Visible Cell' icon. By using the '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 select the visible cells only. You can remove the icon by dragging back the icon into the 'Customize' dialog box.

An alternative to selecting visible cells is by using menu 'Edit | Go To | Specials | Visible Cells Only'.

Inserting copied cell into non-empty cell without overwriting

Sometimes we want to copy contents of cells and paste them into cells already has contents without overwriting the contents of the cells. Use menu 'Insert | Copied cell' after we do copy operation to insert the copied cell. The current cell will be moved downward (its content will not be overwritten). This operation is useful if we want to reorganise positions of our data. However, if we 'cut' rather than 'copy', this will leave the 'cut' cells empty.

Using 'Custom View' to capture a filtered data

After you filter your data, you can make Excel remember the view for that filtering. After you filter your data, click on menu 'View | Custom Views'. A dialog with title 'Custom Views' will appear.

<

To capture the current view, click the 'Add' button. A dialog with title 'Add View' will appear.

Give a name for the view on the box in 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 set 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.

Identifying cells with formula

We may want to identify cells which should contain formula by colouring the background.

Navigating downward to the last used row

If our data has occupied hundreds or thousands of rows, while you are at the top of the sheet, navigating downward to the last occupied row may require you to scroll down either using mouse or keybord key 'Page Down'. There is a faster way, ie by using a combination of 3 keys : 'Ctrl', 'Shift' and the Downward-pointing-arrow key. Before using this combination, make sure that active cell is not a blank cell. It will highlight the cells down to the last non-blank cell. If the identified last cell is not the actual last cell, continue with the combination keys. Once you satisfied, use the mouse to click at the last cell (do not use 'Enter' key).

Giving cell or range of cells a name

Giving a cell or range of cells a name will help in quick navigating to it and also useful in using functions. To give a name to a cell or a range of cells, highlight it and then write the name of your choice into the 'Name' box. And press 'Enter' key once done.

Using Menu ' Insert | Cut Cells '

From time to time, we need to rearrange the position of a row. We want to move a row from one position to another. Highlight the whole of the existing row you want to move by clicking on the row label, then click menu 'Edit | Cut'. Then highlight a whole row that you want to insert the cut row by clicking its row label. Then, click menu 'Insert | Cut Cells'.

Using Menu ' Insert | Copied Cells '

From time to time, we need to enter data into a new row. If we already have a row which contain many similar data, it is faster if we copy the row and insert it into a new row and change only a few relevant data in the inserted row instead of creating a new blank row and typing into the cells in the new row. Highlight the whole of the existing row by clicking on the row label, then click menu 'Edit | Copy'. Then highlight a whole row that you want to insert the copied row by clicking its row label. Then, click menu 'Insert | Copied Cells'.

Using ' Select Current Region ' Icon

It is important to check whether Excel identify our data area correctly. If Excel could not identify it, some of the data may not be included in the PivotTable operation. To quickly let Excel identify the data area, we can use 'Select Current Region' icon. This icon is not available by default. We can get it by doing these steps : Click menu 'Tools | Customize'. We will see a dialog with title 'Customize'. Click 'Command' tab.

Under 'Categories' label, select 'Edit'. Under 'Commands' label, find 'Select Current Region' icon. Drag this icon to the existing toolbar area (choose your position) and drop it there. Click 'Close'. Now the icon should be available in the position where you drop it in the toolbar area.

Activate any cell in the data area in sheet 'Data'. Then click this icon. Excel will highlight an area. Check whether you agree that the area is the data area. If not, then correct your data area.

The 'Select Current Region' icon in the toolbar may look like this (on the immediate left of the familiar Print icon) :

How Microsoft Excel Keeps Date Internally

PivotTable Primer

If we have a sheet with data, we can use PivotTable operation to filter and summarise the data. The operation will produce the result in the same sheet where the data exist or in a separate sheet. We use menu ' Data | PivotTable and PivotChart Report ' to do the operation.

Before we want to use it, we must have a proper data area (or database area) in a tabular manner. A data area is a rectangular area of adjacent cells that is filled with data (A separate chapter about data area is provided. Please read it).

The common steps in using PivotTable operation to produce a report

Below are the common steps in using PivotTable operation to produce a report (Here it is assumed that we use the data in sheet 'Data' in the sample file 'Sample_Receivables') :

Click any non-emptycell in the data area in the sheet 'Data' (because we want to use data in this sheet). Make sure the data is not in filtering mode.

Click menu 'Data | PivotTable and PivotChart Report'. A dialog with title 'PivotTable and PivotChart wizard - step 1 of 3' will appear.

Under 'Where is the data that you want to analyze' label, select 'Microsoft Excel list or database'. Under 'What kind of report do you want to make' label, select 'PivotTable'.

Click 'Next'. A dialog with title 'PivotTable and PivotChart wizard - step 2 of 3' will appear.

In this dialog, Excel automatically identify the address of the data area in the box on the right of Range label. If your data area structure follow the rules, the address of the data area identified by Excel should be correct (Anyway, you can check the range. If the range is not correct, click 'Cancel', correct your data area and start again).

If the address in the "Range" box is correct, click 'Next' to go to next step. A dialog with title 'PivotTable and pivotChart Wizard - Step 3 of 3' will appear.

In this dialog, under 'Where do you want to put the PivotTable' label, you can choose either 'New worksheet' or 'Existing worksheet'. Selecting 'New worksheet' means that the result will be presented in a new worksheet ie Excel will automatically create a new sheet with the result inside it. If you select 'Existing worksheet', we need to identify a existing sheet and the address of the cell where we want to put the report. (A faster way is to click on the intended sheet tab, then click on a cell in the sheet (this cell will be the top-left part of the PivotTable report). The address of the cell we clicked should appear in the box below the 'Existing worksheet' option). (You can click at other cell until you decide which cell is your final destination for your report)).

Click 'Layout' button (though you see that there is a label with message 'Click Finish to create your PivotTable, I recommend not to click at the 'Finish' button, unless you are already familiar with PivotTable ).

After we click the 'Layout' button, if Microsoft Excel cannot identify a proper data area, the following message will appear (please read the content) :

This simply require you to check your data area. Click OK so that you can go checking the data area.

Otherwise, if your data area is alright, a dialog with title 'PivotTable and PivotChart Wizard - Layout' will appear.

This is the place where you 'design' your PivotTable.

The top portion (above the grey line) contain only instructions. Read it.

Slightly in the middle of the layout dialog we can see 4 boxes labelled PAGE, ROW, COLUMN and DATA.

On the right side we can see buttons. These buttons represent the data titles in the data area.  (Because the buttons are not wide enough, the full text of some of the data titles may not appear in full. You can touch your mouse tip at any button and the full text of the relevant data title will be shown).

You need to drag any one button at a time to PAGE box, COLUMN box, ROW box or DATA box on its left and release it there.

If you drag and release the button on to the PAGE box, each unique items available under the title 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 the button out of the PAGE box to revise your selection).

Below is a sample showing the effect after we put MTH button into the PAGE box. We would be able to select one item (month) from many items available.

If you drag and release the button on the ROW box, each unique items available under the title 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 the button out of the ROW box to revise your selection).

Below is a sample showing the effect after we put MTH button into the ROW box. We would be able to select (by ticking) more than one item (month) from many items available.

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

Below is a sample showing the effect after we put MTH button into the COLUMN box. We would be able to select (by ticking) more than one item (month) from many items available.

If you drag and release the button on the DATA box, each total count of items available under the title 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 the button out of the DATA box to revise your selection). (ly we drag button which represent quantity or value into the DATA box, not button which represent text. This is because the DATA box is where totals of count or value will appear).

It is not necessary to put button into every box. You can leave some of these boxes without a button. However, at least, you must have ROW box AND DATA box or COLUMN box AND DATA box filled with button to get a meaningful result.

Some buttons may not be useful for the PivotTable function, so we never drag them into the boxes, for example button that represent data title that we dont want to get its total amount or quantity.

You can put more than one buttons in each box. Example :

It is best to experiment to see the best 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.

After you put necessary buttons into the relevant boxes, click OK. You will get back to the dialog box with title 'PivotTable and PivotChart Wizard - Step 3 of 3'.

Click 'Options'. You will get a dialog box with title 'PivotTable Options'.

In a box in the right of 'Name' label, you can give a name for the PivotTable report that will be generated. The rest are defaults. You can ignore it if you work the first time.

Click OK. You will again get back to the dialog box with title 'PivotTable and PivotChart Wizard - Step 3 of 3'.

Click 'Finish'. A new sheet with the report in it will be presented to you.

Below is a sample report

The layout of the above report is as follows :

Notice the positions of the buttons in the layout and the result.

You can experiment with positioning buttons into the boxes in the layout and see the result. Yes, the best way to discover the PivotTable results is through experimenting yourself. There are too many combinations of buttons in boxes to be covered in this book.

Notes

* Whatever button that is dropped into DATA box in the layout dialog will have its caption added with 'Sum of' text. This is because the PivotTable operation will sum the underlying amounts represented by the button.

Sometimes, instead of the text 'Sum of', the text 'Count of' or others will appear. If this is the case, double-click on the 'Count of RM' (or else) button in the DATA box. A dialog with title 'PivotTable field' will appear. In this dialog, click 'Sum'. Then click 'OK'. You will come back to Layout dialog. The 'Count of RM' should changed to 'Sum of RM' in the layout dialog.

* You will realise that only button representing columns containing figures (digits) are suitable to be dropped into DATA box.

* To give a name for your PivotTable, get the ' PivotTable Option ' dialog. You can get this dialog by right-clicking on the PivotTable report. You should see a floating menu. One of the menu is 'PivotTable Options'. Select it.

* In the above sample, if we click on the little arrow-head on the right of 'ITEM SOLD', a list will appear together with an OK button and a CANCEL button. The list contains all unique items available in the data area under column 'ITEM SOLD'. Automatically all 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 list. Click OK to make the computer accept our selection and dismiss the list. The report will get updated with the selection. Click 'Cancel' to dismiss the list without affecting any change to the report.

* In the above sample, if we click on the little arrow-head on the right of 'MTH', a list will appear together with an OK button and a CANCEL button. The list contains all unique items available in the data area under column 'MTH'. Automatically all 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 list. Click OK to make the computer accept our selection and dismiss the list. The report will get updated with the selection. Click 'Cancel' to dismiss the list without affecting any change to the report.

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

* In the above sample, the months appears on the top side of the report because we drag the MTH button into the COLUMN box (top side) in the layout dialog in step 3 of PivotTable wizard.

* In the above sample, the items sold appears on the left side of the report because we drag the ITEM SOLD button into the ROW box (left side) in the layout dialog in step 3 of PivotTable wizard.

* We can put more than one button into each box. However, you should be interested in the logic of the result. In the layout below, we put QTY button above the RM button in the DATA box :

The result :

The result show the quantity and the value.

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

* Sometimes, when we drag a button into DATA box, the result is a count of item instead of a sum of the item. If we want to find the sum instead of a count : Right-click on any one of the cell in the report. A floating menu will appear.

Select 'Field Setting' (Here 'Field' refers to column title). A dialog with title 'PivotTable Field' will appear :

In the list under the 'Summarise by' label, select 'Sum'. The 'Count of..' should changed to 'Sum of..'. Click OK. The report would now show a sum instead of a count.

* You can format the report and the content of the report in the way you use formatting in Excel. Eg, you can format numbers, font etc.

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

* You can delete the PivotTable to save disk space, if you want. You can always rebuild it again as long as the data is still there. You also can save some computer memory when opening a workbook without many PivotTables, making Excel files load faster.

* If you make any changes in the underlying data, the PivotTable is not automatically updated. To update the report, just click any cell in the report, then right-click once. A floating menu will appear. Select 'Refresh data'. The report will get updated (refreshed).

* If you see a PivotTable report and you want to see its layout, right click on any cell in the report. A floating menu will appear. Select 'Wizard'. You would see the 'PivotTable and PivotChart Wizard - Step 3 Of 3' dialog. Here you will see 'layout' button. Click the button and continue as usual.

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

Please read the message in the dialog. This message dialog appear after you click 'Next' on the step 2 dialog in the PivotTable wizard. You will not get this message dialog when you build the first report. This message simply tells you that Excel has detected that you are working on the same data area which a report has already exist. This dialog is giving you an option whether you want to use 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 your computer memory and will avoid Excel from getting slower and also safe your disk space and keep your Excel file smaller. This is the best choice (selecting 'Yes') in the dialog.

Then, we will see dialog similar to this :

This dialog simply shows the list of existing PivotTables. Just click at the PivotTable name that we already have. Click 'Next'. A dialog with title 'PivotTable and pivotChart Wizard - Step 3 of 3'will appear. This should be familiar to you.

* We can use a shorter way to build a report. Click any cell in the data area from which you want it to be a base for your report. Click menu 'Data | PivotTable and PivotChart Report'. A dialog with title 'PivotTable and PivotChart Wizard - Step 1 of 3' appears. Instead of clicking 'Next' you click 'Finish'. This way, you avoid getting into Step 2 and 3. You will be presented with a new sheet. In the new 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 box with 'Drop Data Items Here' preferably be the last box which you drag a button into. As soon as you drag a button into the 'Drag Data Items Here' box, the port will be ready for you.

* You can move a PivotTable report using Cut and Paste operation.

* You can make a copy of a PivotTable report using Copy and Paste operation.

* You can formatany cell in the PivotTable report with appropriate formatting. Right click at any cell, a floating menu will appar.

Select 'Format | Cells'. A dialog with title 'Format Cells' will appear. You should be familiar with this dialog.

* If you want to amend the report, right click at any cell. A floating menu will appear. Select 'Wizard'. This will bring you to 'PivotTable and PivotChart - Step 3 of 3' dialog. In this dialog, click at the 'Layout' button. Ypu will see another familiar dialog 'PivotTable and PivotChart - Dialog'.

* You can temporarily hide a field from appearing by right clicking on a cell, then select 'Hide' from the floating menu that appear.

This has the same effect as clicking on the combo box and then unmark the unwanted item.

* To delete or remove a PivotTable, use the same way you delete an entry in cells.

* Insert one or two rows above the report so that you can write a title for the report. The PivotTable operation does not create or ask you to create a title for the report it produce. So, you need to create on your own.

* If we put more than one button in ROW box, the result will show total in each row.

You can hide the total row (row with text 'Total') by right-clicking on the cell and select 'Hide' from the floating menu.

Or, you can select 'Field Setting' item where you will see this dialog :

Select 'None' under Subtotals options then click OK. You would not see the unnecessary totals anymore and your table would look more neat.

* It is not necessary that we create many PivotTable in our file. One PivotTable is enough because we can easily modify the existing one to create another form of report.

AdvancedFilter Primer

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

We can access the AdvanceFilter function by clicking menu 'Data | Filter | AdvanceFilter'.

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 in the left and a column in the right. (Please read more about data area in a separate chapter).

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 the titles by writing the data titles in another area (preferably in other sheet). In that area (range), we write the data titles in a row of cell (any row) and must be adjacent to each other. In cells below each titles, we 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 an 'criteria range'.

A simple Criteria range looks like this :

If we fill the Criteria like this :

this means we want to see rows where the row has both item 'xxx' below a data title A AND has item 'yyyy' below the data title B. (ie only rows having both 2 items will get selected, the rest will be hidden).

If we fill the Criteria like this :

This means we want to see rows where each row has item 'xxx' below data title A AND has any other item (including blank) under data title B (ie only rows having item 'xxx' under title A will get selected. Data under title B can consist anything. The rest will be hidden).

If we fill the Criteria like this :

This means we want to see rows where each row has both item 'xxx' below a data title A AND has item 'yyyy' below data title B, AND ALSO every row which has item 'aa' below data title A AND has any other item (including blank) below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.

If we fill the Criteria like this :

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

If we fill the Criteria like this :

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 both item 'aa' below data title A AND has item 'yyyy' below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.

If we fill the Criteria like this :

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

Note By now, you would see that a blank cell in the Criteria represent ANY (including blank). And a row in the Criteria range represents a row in the actual data area.

After you set the Criteria range then you can start using the menu 'Data | Filter |AdvanceFilter'.

You can set more than one Criteria range in one sheet to serve different filtering.

After the Criteria range is ready, activate the sheet which contain the data. Here, click menu 'Data | Filter | AdvanceFilter'. You will see a dialog with title 'Advance Filter' :

On the right of 'List range' is your data area automatically identified by Excel. Check this range. If it is wrong, click 'Cancel' and correct you data setting to follow the rules, and start again. Or, you can fill it manually.

Click on the 'Criteria range' box. Then, 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).

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

Summary

Filtering for one item from one column - Use AutoFilter

Filtering for more than one item from one column - Use 'Custom' in the AutoFilter.

Filtering for more than one item from more than one column - Use AdvanceFilter.

Easier Advance

If you are new in using AdvanceFilter operation, you would tend to create many Criteria range, each one to cater for one filtering. Also, you would tend to delete an existing criteria range and recreate another one for different filtering.

Here I will show you how to create just one criteria range and use it for different filterings. In addition, you will also see the totals of the relevant columns after the filtering.

I assume that the data titles are derived from the sheet 'Data'.

Below are the steps you have to do : The steps may be a bit extensive for the beginning, but once the setting is done, you would save a lot of time in the future.

>• Create a new sheet. You can give it a name for easy identification. Later, we will put the criteria range in this sheet.

• Copy range A1:Y1 in sheet 'Data' into cell A1 in the new sheet. Remember that the range A1:Y1 in sheet 'Data' contains the data titles. Now the same data titles is made available in the new sheet after the copying.

• Leave 3 blank rows after the row 1. This means row 2, row 3 and row 4. Use cells in these rows for filling the criteria. Here we leave 3 blank rows because it is expected that 3 rows are enough for criteria. If you think you need more, you can use additional rows.

• Use a row below the criteria range to get totals or subtotals, ie for columns I (QTY), L (RM), U (PMT(RM)) and W (BAL (RM)). Use SUBTOTAL function instead of SUM. SUBTOTAL function will show totals for visible cells only. This is useful after a filtering is done where SUBTOTAL will show totals of filtered amounts.

• Use a row below the SUBTOTAL row to get totals, ie for columns I (QTY), L (RM), U (PMT(RM)) and W (BAL (RM)). This time we use the SUM function instead of SUBTOTAL. SUM function will show totals whether the cells are visible or not (filtered or not). This is useful after a filtering is done where while SUBTOTAL row above it show totals of filtered amounts, we can still see the real totals for comparison purpose.

Below is a sample of the completed structure :

continued...

Formula in cell I7 is : =SUBTOTAL(9,Data!I2:I65535)

Formula in cell L7 is : =SUBTOTAL(9,Data!L2:L65535)

Formula in cell U7 is : =SUBTOTAL(9,Data!U2:U65535)

Formula in cell W7 is : =SUBTOTAL(9,Data!W2:W65535)

Formula in cell I8 is : =SUM(Data!I2:I65535)

Formula in cell L8 is : =SUB(Data!L2:L65535)

Formula in cell U8 is : =SUB(Data!U2:U65535)

Formula in cell W8 is : =SUB(Data!W2:W65535)

Useful Functions

Some useful functions that are frequently used are listed below :

=SUBTOTAL

This function is used to get totals of figures in other cells which are visible. If figures are not visible (eg due to being filtered or hidden), it will not get totalled. The syntax of this function is : =SUBTOTAL(9,Range address). The '9' refers to (means) 'SUM'. The 'range address' is the address of the range you want to get it totalled, eg A3:Z40. Example : =SUBTOTAL (9, A3:A40). If this function is in cell A1, cell A1 will show the total of all figures from A3 to A40. If cell A20 is hidden due to filtering, figure in it will not get totalled. Please search for 'SUBTOTAL worksheet function' in Excel Help for much more information.

=INDIRECT

This function is used inside other function to make the cell or range address permanent. For example, we want to sum figures in range address A3:A40 in cell A1. In cell A1 we write =SUM(A3:A40). If we delete row 20, the range address will change to A3:A39. To make the range address permanent, we use INDIRECT function, ie =SUM(INDIRECT("A3:A40")). This way, even if we delete rows in the original range, the address will still remains the same. (Notice that there is double-quote mark " before and after the address). Please search for 'INDIRECT worksheet function' in Excel Help for much more information.

=IF

The full syntax of this function is : =IF(Condition, return this part if condition is met, otherwise return this part). For example, in cell A1 we want it to show "Good" if figure in cell B1 is more than 50 and show "Not Good" if less than 50. In cell A1 we would write =IF(B1>50,"Good","Bad").   Please search for 'IF worksheet function' in Excel Help for much more information.

=VLOOKUP

This function can be used to check whether an item exist in a list. The full syntax of this function is : =VLOOKUP(Item to find, area to find, 1, FALSE). (The parameter "1" and 'FALSE" must always be used). For example, we want to check in cell K5 in sheet DATA whether account name "LAND" in cell G5 in sheet DATA exist in data area "A5:G1000" in sheet TRIAL BALANCE. We would write : =VLOOKUP(DATA!G5,'TRIAL BALANCE'!A5:G1000,1,FALSE). If the item exist, the function will return TRUE, otherwise it will return #ERROR. (We will see TRUE in cell K5, otherwise #ERROR). Please search for 'VLOOKUP worksheet function' in Excel Help for much more information.

=ISERROR

This function is usually used together with function IF and one other function to check whether the other function return #ERROR. The full syntax of this function is : =ISERROR(Other function). >  For example, the function VLOOKUP may return #ERROR. We don't want to see #ERROR in the cell where the function VLOOKUP is written. So, if VLOOKUP return #ERROR, we want to see other word which give more meaning (eg "NOT OK"). Thus we can write : =IF(ISERROR(VLOOKUP(G5,INDIRECT("TB!A5:A1000"),1,FALSE)),"NOT OK","OK")

=ISNUMBER

This function is usually used to check whether the content of a cell is a number. The full syntax of this function is : =ISNUMBER(Cell Address). For example, we want to check in cell G5 whether the content of cell A5 is a number or not a number. We would write : =ISNUMBER(A5). If G5 contain a number, we would see TRUE in cell G5, otherwise FALSE.

=SUMIF

This function is used in a cell to sum all numbers in certain area in data area if another area in the same data area contain a particular item. The full syntax of this function is : =SUMIF(Item to sum, Area where the item exist, Area which contain figure to sum). For example, in cell A5 we want to sum each amount in range C5:C3000 for each cell in range D5:D3000 which is the same with the content of cell B5. We would write : =SUMIF(B5,D5:D3000,C5:C3000) in cell A5.

=COUNTIF

We can use this function in a cell to count how many times an item exist in a data area. The full syntax of this function is : =COUNTIF(Data area address, item). For example we want to count in cell A5 how many times the item LAND is duplicated in the range B4:B2000.. We would write :

=COUNTIF(B4:B2000,"LAND") in cell A5. If there are 5 item LAND, we will see 5 in cell A5.

Data Entry methods

Make Excel Goes Straight To A Directory

To make Excel go straight to the directory we want when it starts (open), do the setting using the menu 'Tool | Options'. When we click this menu, we will see a dialog with title 'Options' similar to this :

Click at the tab 'General' (if the tab is not already focused). In the box on the right of 'Default file location' label, write the path of the directory that you want Excel to focus when it opens. In the above example, the directory path is d:\Accounts, simply because the directory contains all my accounts file.

Click OK. Next time when you click at the menu 'File | Open', it will show the names of files available in the directory you specified.

How To Make A Cell Behave Like A List Box

We can set a cell such that when we activate that cell, an arrow head will appear on the right edge of the cell and when we click on that arrow head, a list of items will appear. Then we can click at an item and the item will be entered into the cell. When we leave the cell, the arrow head will be hidden.

Below is a sample : In this sample, when we activate cell G38, an arrow head will appear on its right edge.