Maintaining a List

Adding a record

Adding a record is simple. Just go to the bottom of the spreadsheet with a blank row and start entering the data column by column.

Suppose you are interested in adding the following informtion to your list

Company Name : AGF Management Ltd

Ticker : AGF B

Sector : Finance and Insurance

From our previous section we see that we left the spreadsheet left in sorted on company name on reverse alphabetical order. There are 26 companies listed currently.

currentlistreversed.gif

Go to the bottom of the list and start typing ‘AGF Management Ltd’ under the column B named Company Name and ‘AGF B’ under the column C named Ticker and ‘Finance and Insurance’ under the column D named Sector as shown below. Never mind the serial number for the time being.

lastentry.gif

Now it would help if we sort the list on Company Name ( Column B) in an ascending order i.e. A -> Z. Click on any company name say Husky Energy Inc and click on Sort on tool bar as shown below:

sortafterinsertion.gif

Now AGF management comes to the very top and in the proper ascending alphabetical order.

AGF to the top.gif

Inserting a record in a list

What if you want to insert a record at any point in the list, say above the record entry for Husky Energy. We need a blank row above Husky Energy. The procedure is as follows:

First click on the record entry for Husky Energy to select it.

Then go to left extreme and right click on row number 18 for Husky Energy to display a pop up menu as shown:

insertblankrow.gif

With the menu open click on Insert 1 above to insert a blank row above the entry for Husky Energy Inc. as shown below:

blankrowinserted.gif

In the blank row enter the following details as detailed above:

Company Name : Toromont Industries Ltd

Ticker : TIH

Sector : Real Estate and Rental and Leasing

With the new entry your list looks as follows:

Toromontentry.gif

Notice also that the latest entries for companies AGF Management Ltd & Toromont Industries Ltd are missing the entry for column titled ‘No.'(Column A). Now we are going to fix this. To automatically serialize the numbers for all the companies, we are going to use a feature called ‘autofill’. Type in 1 in column A in the row for AGF Management Inc. and 2 for Atlas Income Trust and 3 for Atco Ltd.

listrenumbered.gif

Now select those numbers 1,2,3 entered just now, by left clicking on number 1 and with out releasing the mouse, move or drag the mouse down until the numbers 2 and 3 are covered and then release it. Now see a small square at the bottom right corner of the high-lighted rectangle as shown below:

autofillsquare.gif

Now hover your mouse pointer above the square until you see ‘ + ‘ above it. Right at that moment click on the square drag the mouse all the way to the bottom of the list covering all companies in the list and then release the mouse with in this column. Now you see the entire list is renumbered as we want it.

renumberedlist.gif

This may need some practice. If you do a mistake, and get unintended results do not panic. Just click on the Undo button once to get back to the previous state and redo this part of the exercise until you get it right.

Search for a record in a list

Sometimes you may vaguely remember a column entry in a record and see if a record exists with this value in a column. One way to search for this record in a list is to go down a list patiently, row by row while at the same time scanning the column entries to look for one record that matches your criteria.

Obviously this is painful, time consuming and error prone.

But there is a better way. Once you have your Google spreadsheet open, press Ctrl + F keys together on your keyboard to open the ‘Find’ dialog box. This box is presented by your browser and works under any web page. Your spreadsheet is actually a webpage so you will not have problems opening this box. Enter the search word in the box and click next and you find the first row with this entry. If there are more than one record with your search word, click next and next and so on until you find the record with this word or you reach the bottom of the page.

In our example of dividend achievers list ( keep this spread sheet open and active by clicking anywhere with in this sheet), suppose you know the company you are looking for is a bank and has the word the word “bank” contained in its full name, all that you need do is open the Find dialog box as mentioned above and type in bank. Then click next. The browser will highlight the first record with the name bank in it. This is as shown below:

Now that you are familiar with searching for a record with in the spreadsheet, let us take this to the next level. Suppose you are looking for the Return on Equity for insurance sector. But you don’t remember which spreadsheet has this information. All that you know for sure is that this information is there in one of your spreadsheets and you remember the key word “insurance”.

This time we will put the power of Google’s search facility to narrow down those spreadsheets that has this keyword “insurance”. Next we will use browser’s ‘Find’ dialog box to look for the record with in these spreadsheets. This two step process is explained below.

Deleting a record

<--BackTo: Some basic List manipulations

Next: Custom and Auto Filters with Google API –>