99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Make Things Easier In Excel With Lists
Thu 23rd June 2011
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.
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.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
The Brain Tumour Charity
Really enjoyed it. Slightly too fast paced for me but having the forum will help. Would liked to have maybe had more explantion about the concept of project and why / how it can best be used in a variety of situations. But really could have done this work myself beforehand!Thank you!
Sumitomo Corporation Europe Ltd.
Had the most windwhirl 7 hours in the class.
I initially thought it was going to be a long day but the class went so quickly, packed with useful info and good laughs here and there.
Kohlberg Kravis Roberts & Co. Ltd
PowerPoint Intermediate Advanced
Liked Cindy's enthusiasm and that she spoke clearly and could hear her well. Was happy to take questions and went back to some later where appropriate