Make Things Easier In Excel With Lists

Thu 23rd June 2011

Sorting data can be a complicated matter. In Excel the creation of lists makes it easy to sort and extract information from your database. Here is the process, using a small amount of data.

In the days before the Internet would have made seeking the answer easy, my dad asked me what the significance of this combination of letters and numbers was.

RA15042699

There was a crisp five pound note waiting for me if I could provide him with the correct answer, but despite scouring approximately half the books in the local library, I had to admit defeat. It is, as any keen-eyed television viewer should know, Sergeant Bilko's service number.

So in that vein, but without the fiver prize, let me ask if you know the significance of this combination of letters and numbers.

XFD1048576

The above is actually the final co-ordinate on an Excel 2007 worksheet; the final cell in the vast capacity of 1,048,576 rows by 16,384 columns, creating a staggering 17,179,869,184 cells.

Clearly this level of capacity allows Excel to handle data in volumes that go way beyond those of a few hundred addresses, or details of a list of a couple of thousand members, both of which are like dropping a dried pea into an empty skip as far as capacity used goes.

Despite this enormous capacity, however, one of the great features of Excel is that we can gain an understanding of how to perform functions involving vast amounts of data by exploring and experimenting on our own tiny, home-made worksheets. There is no need to study enormous spreadsheets in order to get an idea of how different functions work; you can grasp the idea with only a few columns and rows. To demonstrate this, here is how to create a small database that looks at using lists.

The first thing to do is create the column headings for our sample spreadsheet. I created one made up of three columns: First (name) Last (name) and Salary. I filled the names in with various characters from cartoon shows, and the salaries I entered were just random numbers between 16,000 and 48,000. I entered the details of ten characters, including some duplicated surnames (Simpson and Griffin).

To create a list, I entered the three column headings as above, followed by the details of the first entry in the database, which in this case was:

Barney Gumble 22,500

One point to make note of here is that any formatting entered in this initial row will be repeated in all subsequent entries in that column. So, for example, typing in the pound sign before entering Barney's salary will instruct Excel to display the rest of that column's values in pounds. I would need only to type in the actual figure. Other formatting, such as displaying the sum in bold, would be applied here.

To create a list I would select all of my tiny database, column headings and that first entry, and the go to Data and click on List from the menu that appeared, and then click on "Create List" (Ctrl + L). This would open up a small dialog box to confirm the whereabouts of the data for my list, and I would ensure that the my list has headers box was checked. I would click on OK and my list would be created.

The data I selected now takes on a different appearance. It is bordered by a blue line and a blue asterisk shows where the next entry on the list will be. When I move my cursor within this blue border, I see that the headers each have small drop down menus arrows contained within the cells.

A quick exploration of the drop down menus shows some of the functions that can be performed at the click of a mouse, such as sorting ascending or descending, but there are more intricate

If I click on the drop down menu in my Salary column and select Custom, this will open up the custom filter dialog box. From here I can pick out a group from my list whose salaries fall within two values. For example, in the first pair of boxes, marked salary, I select "Is less than", and I enter 30,000. I check the "And radio button" and in the second boxes I select "Is greater than" and I enter 20,000. Clicking OK will see the number of entries in the database whittled down to those whose salaries fall between those two figures. To return to my full list I would simply open the drop down menu again and click on "All".

Once again experimenting with a few columns and rows gives us an idea of how an Excel function would work on a much bigger scale. If you imagine a list of hundreds of employees, and you needed to fish out those within a certain wage bracket as I did above, then I'm sure you can appreciate the value of creating lists. Like many Excel features, lists make performing intricate tasks simple. Excel wizards will always be in demand, so why not set off on the road to becoming one? You could be earning more than Barney Gumble in no time.

Author is a freelance copywriter. For more information on microsoft excel courses, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1748-make-things-easier-in-excel-with-lists.html