LibreOffice Calc is a spreadsheet tool and has a focus on doing calculations, but over time, spreadsheets have also been used to organize lists. Within its limitations, Calc or any spreadsheet tool can help you to keep accurate, up-to-date lists. Each row represents one "record" of the data, and each column is a "field." Some kinds of information can be put into such lists. There are also more complete data storage solutions. This section does not discuss more advanced data management tools. This section concentrates on simple list management with Calc.
When data is consistent and uncomplicated, a spreadsheet can do many useful things. One of the most useful is arranging the data alphabetically or numerically. This section of the guide will concentrate on arranging data. The arranging process is called sorting in computer circles. The reason is that we don't meet people or become friends in alphabetical order. It is most convenient to enter a new contact at the end of a list and then let use the spreadsheet to organize the list.
In this section of the guide, we will look at a personal address book with Calc. You should recognize that there are many other ways to do a contact list or address book. Calc may not even be the best way to handle a list, but a contact list offers us a very good way to see how several useful Calc features work.
The first step is to create a simple list of names so we can see some important issues quickly.
Start LibreOffice Calc and enter the following short list.
It is in reverse alphabetical order by last name.
Name
Terri Zimmer
Jill Carter
Tom Baker
Joe Adams
Your sheet should look something like the image at the right.
Next, we'll arrange (sort) the list alphabetically.
Select/highlight the four cells from A1 through A5.
Click the Data menu and then the Sort... option.
Simply click the OK button in the dialog box. We'll examine details in a moment.
Well, that doesn't look right. It isn't what we wanted or even what we may have expected. What I think we wanted was the list arranged from Adams to Zimmer. Instead, we got the list arranged by first names. Nope, that's not "right." But, the computer program has strict rules, and we need to learn what they are to make the system work for us. The computer rule is to arrange the list by comparing the first letter in each of the cells, then the second letter, then the third, etc. With that rule, we did get the correct result. After all, our data cells began with each person's first name. To get what we wanted instead, we need to split the names into two parts, first and last names for a person in the same row, but not together in the same cell. Each person gets a row.
Change the entries to look like the next image with two columns and the names separated into first and last.
Next, select/highlight both columns, cells A1 through B5. Click the Data menu and the Sort... option and stop.
Before clicking OK, look at the first selector identified as "Sort key 1" and examine the three choices: "-undefined-", "First", and "Last." Choosing "First" would sort the names as we did the first time, but choosing "Last" will do what we really want, arranging by last name. "Ascending" order means regular A to Z alphabetical when dealing with names or other words. "Decending" means arrange from Z to A.
(Choosing -undefined- or leaving it set that way by default, lets us say we don't care about the order for this column at this time.)
To get a really complete sorting done, you also need to deal with the second sort key, because the first name matters. Therefore, choose the first name for this second key. Our short example list didn't have a problem, but we want to avoid a more general problem when several people with the last name are in a list. We want the Smith sisters properly sorted, Sarah Smith to be before Sue Smith, for example. Because we use the first name as the second key, all the names in the list will arrange by last name and then by first name, meaning that all the Smiths will be grouped together and alphabetized by first name. That will make each individual easier for us to locate in a long list of people named Smith.
With only a short list of names, we cannot effectively explore sorting, but asking you to type in your own bigger contact list isn't practical for just doing this guide's practice. Instead, we will use a bigger list with more columns of information which has been pre-built for you.
Download the list and save it to your computer. When you click on the link, the download dialog will probably offer to open the file with LibreOffice, and you can do that if you wish. I think, however, that it might be useful for you to be able to revisit this section of the guide and have the file accessible even if you cannot make an Internet connection.
Note:
There's one more reason to save the file. If you do not, the file is open in your Calc as "Read Only" and that isn't obvious unless you look carefully. A read-only file cannot be changed, just looked at. I hope it is clear that this practice will require you to change the data! So, save the file before continuing.
A read-only file is identified as such in the title bar of the window (but only when you are not looking at the menu in that bar!)
The data file is intentionally shorter than a real contact list. With only 25 names on the list, you will be able to see the results on most computer screens.
To see the data more clearly, click the image for a full-size version (or gaze at it in your own LibreOffice Calc).
This practice data is totally made up. No real people were intended to be included. The phone numbers or email addresses may accidentally be real, so please be responsible and do not try to use them.
Selecting all the data
Enlarge by clicking the image.
It is critical to sort ALL the data. Click and drag from cell A1 to cell N25.
Go to the title bar and select the Data menu and the Sort option. Sort key 1 should be set to "Last" and Sort key 2 set to "First." If you got it right, the list should look like the one in the image above.
Now a couple of questions for you to try to answer and to discuss among friends.
(If you cannot discuss with anybody closer, contact the author, using the email in the page footer.)
Sometimes, especially if the list is huge, we just want to do a quick search without doing a sort/arrangement first.
Calc has a built-in "find" feature which lets you jump through a long list, stopping at every "Jones", perhaps.
Put your cell selector in cell A1 so the search (which is normally forward through the file) has the best change to find a result.
Go to the edit menu and select "Find". If you are becoming a fan of the keyboard shortcuts, use Ctrl+F, instead. Either way, a strip will appear at the bottom of the spreadsheet window.
Then enter the last name "Jones" without the quotation marks into the blank where it says "Find". (The quotation marks are used in this document to be sure you see the name we want to find.)
The actual search begins either when you click the arrow point shown in the illustration or when you tap the Enter key on the keyboard.
If you start nearer the end instead of the top of the list, then you may need to deal with this next dialog.
There are four places in this data file where the five letter sequence we see as "jones" occurs. Maybe this first Jones isn't the one you want. Click the down-pointing arrow to the right of the place you typed. As you move to the next cell with Jones in it, you may find yourself looking at an email address. Remember, you are not actually searching for a name. The data and Calc itself has no idea of what a name is. You are asking Calc to locate the five letters that you see as a name. Calc is just seeing it as five consecutive letters. In fact, Calc will find "Jones" or "jones" equally well, showing you the same results.
Note:
Find is a common function or tool in many/most computer programs. The dialog gets reused in the Writer word processor component of LibreOffice, for example.
© 2013 Algot Runeman email- Shared using the Creative Commons Attribution license.
Source to cite: - filedate: