General - Partners Bridging the Digital Divide



Module Five: Lists in ExcelWelcome to the fifth lesson in the PRC’s Excel Spreadsheets Course 1. This lesson introduces you to some basic database concepts. Excel uses the term “list” when referring to a database stored in a spreadsheet. You will explore several pre-built sample spreadsheets and make minor modifications to each sample. The spreadsheets can be found on thecomputer hard ics TOC \t "Section Hdr,1" Database Concepts5. PAGEREF _Toc490194659 \h 2Database Terms5. PAGEREF _Toc490194660 \h 3Set Up a Database5. PAGEREF _Toc490194661 \h 4Sort Data in a Spreadsheet5. PAGEREF _Toc490194662 \h 4Filter Data in a Spreadsheet5. PAGEREF _Toc490194663 \h 6Split Window5. PAGEREF _Toc490194664 \h 8Freeze Titles5. PAGEREF _Toc490194665 \h 9Print Selected Areas5. PAGEREF _Toc490194666 \h 11Exercises TOC \t "Exercise Hdr,1" Exercise A - Sort the Auto Mileage Spreadsheet5.14Exercise B - View the Budget Spreadsheet5. PAGEREF _Toc492397834 \h 16Exercise C - Filter the Movies Spreadsheet5. PAGEREF _Toc492397835 \h 18Summary5. PAGEREF _Toc492397836 \h 18ObjectivesSelect and sort data in a spreadsheet Split the spreadsheet window and freeze column and row titlesPrint selected portions of a spreadsheet Database ConceptsA spreadsheet is a collection of rows and columns. In Microsoft Excel 2007 a spreadsheet can contain 16,384 columns and 1,048,576 rows of information. The columns are lettered A, B, C, etc., ending with XFD. The rows are numbered 1 through 1048576. Every cell in the spreadsheet has an address created by combining the column letter with the row number. In the example shown in Figure 5.1, cell A1 is selected, the first column and the first row.Figure 5.1 An empty spreadsheet layoutA database is a collection of related information that is organized for a particular purpose. Examples of databases might include your address book, a store’s inventory, information about a collection (art, music, coins, baseball cards) or a club membership list. It is no coincidence that the layouts of an empty spreadsheet and an empty database should look so similar. Figure 5.2 An empty database layoutDatabase TermsThe various pieces of information that relate to one individual or one item are called a database record. A record consists of multiple fields. If the database is designed for use as an address book, the field names might be First Name, Last Name, Address, City, State and Zip. The data entered in each field is called an entry. Figure 5.3 A database recordFor example:Database:Friends AddressesRecord:Barrow/Ed/319 N Washington/Hinsdale/IL/60521Field Name:FirstNameField or Entry:EdList:All the entries in a column, ends in an empty cellTable:All the entries in all the columns which touch each otherIt is important to remember that although Excel can do many tricks acting like a database, at heart it’s still a spreadsheet. It only understands you want to use it as a database because of certain patterns it senses as you enter data. The most important of these patterns is the creation of a header row.Look closely at Figure 5.3. How does Excel know that Street is a Field Name and not an Entry? How does Excel know that Hinsdale is an Entry and not a Field Name? You tell Excel by formatting the first row in a way which is significantly different from the rest of the entries. In Figure 5.3, Field Names are Bold and Red while Entries are Plain and Black.The next most important pattern is how you tell Excel what the boundaries of the database are. This is done by using empty cells. Excel first reads fields from left to right. When it encounters a completely empty column of cells it interprets that as the right edge of the database. Then it reads top to bottom. When it reaches a row of completely empty cells it interprets that as the bottom edge of the database. So you tell Excel about the size of the database by how you locate empty cells – empty cells are good for something after all!Microsoft has to walk a thin line with databases because it sells a dedicated database program called Access. This drives some behavior that is otherwise inexplicable. Suppose your database has only one column in it (kind of a funny database – just play along for a minute). This is a one-dimensional database, topped with a column header and bottomed with an empty cell. Excel calls this a List. Now suppose your database has several columns. This is a two-dimensional database, topped with a header row, bound on the right side by an empty column, bottomed by an empty row. Excel calls this a Table. You need to know these terms if you ever have to query Help. If you try to look in Help for “database,” you will find a lot of material about how Excel and Access work together, material that probably won’t help you at all.Set Up a DatabaseBefore creating a database, first you have to determine the purpose served by the information, and then you need to set the categories (Field Names) you will need in reports or that you want to use to sort or select specific records. The purpose limits the information relevant to an individual or item in the database. Think of yourself for a moment. There is address information that can be associated with your name. But there is also financial, physical, work history, and lots of other kinds of information associated with your name. If all of it is put into a single database, each kind or type of information adds its dimension. Multi-dimensional databases can get very complicated in Excel. Excel is good for two-dimensional Tables – it needs some limits in order to be its most effective.Setting categories depends on the purpose. For some purposes it is enough to have a field name called Name. For other purposes you may want to have fields for First Name, Last Name, Title, etc. For example, if you wish to be able to use only the first name in the salutation of a letter, you would set up your database with separate first and last name fields. While it is possible to retreat and correct errors in setting Field Names, it is often very time consuming. A really useful database reflects good planning.Sort Data in a SpreadsheetThe information in a database can be sorted and records can be selected that match certain criteria. Sorting the information in a spreadsheet is very easy. First, select the range of cells you want to sort. Second, select the Sort &Filter button on the Home tab, Editing group or the Sort command on the Data tab, Sort & Filter group menu. Figure 5.4 The Ascending and Descending Sort/Filter button on the Home tabFigure 5.5 The Sort command on the right click MenuThere is one important caution to mention, it is best illustrated with an example. Consider a name and address table like that shown in Figure 5.3. It is often convenient to keep such a table sorted in alphabetical order by last name then by first name. If you sort only column A without sorting the rest of the table you will decouple last name from the other elements that make up the complete name and address – the table will become useless. This error occurs so often, and has such dire consequences, that Excel will issue a warning before it executes the sort.Figure 5.6 Sort Warning in cases of incomplete data selectionMost likely you want to sort all the columns of information together. Accepting the default option to expand the selection is probably what you want. But Excel will only expand the selection to include columns containing data which are touching (contiguous). If your table contains a column which is empty, Excel interprets that column as a boundary of the table and the expansion will not include data in columns on the far side of that empty column. Thus, if you get this warning it is recommended that you Cancel the sort, reconsider what your intent is, and reselect the data to be sorted.An empty column within a table causes problems. If you select a single cell in the table and then initiate the sort from the Data tab, Sort & Filter group instead of one of the Sort Data/Filter button on the Home tab, Excel will highlight its interpretation of the table. It’s a quick visual check to see if Excel is about to give you what you really want.With all the cautions out of the way, you are almost ready to sort your information. Figure 5.7 The Sort dialog boxWhen you created the table, did you also create column headers (Field Names)? When you selected the information to sort, did you also select the column headers? If the answer to these is yes, you probably do not want to sort the titles with the information and you should accept the option that “My data has headers.” If the formatting of the header row is the same as the rest of the table then Excel will assume there is no header row and the default offered will be ho header row. You will need to change that option if, in fact, your selection has included a header row.Figure 5.7 shows the Field Names Last Name and First Name, both to be sorted in Ascending sequence. Multiple levels of sort are offered any time Sort is invoked. The most important, or profound, sort is at the top of the dialog box. If the sort offered here were executed, the table would be sorted first name within last name just as a phone book is sorted. If there were no column headers used then the levels of sort would be based on Column A and Column B instead of column headers. To add additional levels of sorting, click on the “Add level” button.If the sort did not turn out as expected, remember you can always select the Edit Menu and then select the Undo command to return the list to its original order.Filter Data in a SpreadsheetWith more than 1,000,000 rows, a spreadsheet can potentially hold a large amount of data. It would be good if Excel provided a way by which you could get at some portion of that data without tripping over the stuff that is not of interest. In fact, there are two methods, filtering the data and splitting the window.A filter highlights, or lets you see, a portion of the data based on a selection criteria. You set the filter from the Sort & Filter button under the Home tab, Editing group or the Data tab, Sort & Filter groupFigure 5.8.1 Data tab, Sort & Filter group Figure 5.8.2 Home tab, Editing group, Sort & Filter Button SelectedThe Advanced Filter is too advanced for this course, but the AutoFilter is very handy. It’s a toggle switch, selected (on) or not selected (off).Figure 5.9 Database with AutoFilter selectedWhen it’s selected it puts a drop down menu at the top of every column in the database. Figure 5.9 shows a database of movies with AutoFilter selected. If you click on any of the drop down arrows the menu that drops down contains all the different values Excel finds in that column. You can then select from that menu to display only the records containing a particular value.Figure 5.10 Filtered databaseFigure 5.10 shows the movie database filtered for the Star “Lucille Ball.” Note that the row numbers where “Lucille Ball” is found are also shown.Split WindowThe other method of working your way through large spreadsheets is by splitting the Excel window into panes. This is done by clicking and dragging on either or both split handles located above the vertical scroll bar and to the right of the horizontal scroll bar. Figure 5.11.1 Excel split boxesYou can also create the splits in your spreadsheet by going to the View tab, Window group. Clicking on the Split command will create a split above and to the left of the current cell selected in your worksheet.Figure 5.11.2 View tab, Window group – Split commandAlthough it can be done, splitting a window into four panes is unusual. More frequently, two panes are used, Figure 5.12 Four panes in one windowone in which data is entered and one in which the result of entering data is monitored. For example, a spreadsheet might contain monthly accounting information with a totals column on the right (or left). Unless the column widths are very narrow or the font very small, it’s difficult to fit category column, twelve monthly columns, and a total column within a single window. So the window is split in such a way that both the current month’s entries can be made and the total column can be watched.Note the scroll bar arrangement in Figure 5.12. Although there are four scroll bars, each scrolls two panes. For example, the bottom left scroll bar controls the horizontal movement of both the upper left and lower left panes. This might or might not be what you need – it tends to be a disadvantage in using four panes as opposed to using just two panes. There is one other variation on splitting panes that helps with this problem.Freeze TitlesOften when you are working several rows or columns deep in a spreadsheet you cannot see the titles of columns and rows. It would be helpful to “freeze” the row and column titles in their respective places and use them as a guide when you are entering data. Excel provides a command called Freeze Panes located on the View Tab, Window group. Figure 5.13 A sample spreadsheet showing row and column titles.You will see a horizontal and vertical solid line in your spreadsheet. These lines are a visual clue that the titles will not scroll out of sight.Figure 5.14 The sample spreadsheet with titles frozenTo remove the freeze on the titles, select the Freeze Panes button located on the View Tab, Window groupand the titles will be unfrozen. The Freeze Panes command is another example of a toggle, on/off command.Print Selected AreasA problem similar to actively working with large spreadsheets can occur when it comes to printing; that is, the need to print just a portion of the spreadsheet. For example, you may only want to print Personal Phone Numbers, but the spreadsheet contains much more. You can print a specific area of a spreadsheet by using the Set Print Area command under the Page Layout Tab, Page Setup group, Print Area button. Figure 5.15 A phone book sample spreadsheetFirst, select the area to be printed by clicking and dragging to highlight the cell range. In this example the cell range for Personal Phone Numbers is A8 through E12. Then select the Page Layout tab, Page Setup group, Print Area button and click on Set Print Area. Figure 5.16 The Page Layout tab Set Print Area selectedYou should preview the print area prior to the actual printing of it by selecting the Print Preview command under the Office Button, Print section. .Figure 5.17 The Print Preview command under the Office ButtonPreviewing a print before the fact is recommended for a number of reasons, but in this case it is because the print area remains in force until cleared. Clearing the print area is also executed by clicking on Page Layout, tab Page Setup group, Print Area, Clear Print Area. (see Figure 5.16, above).This method works well when the area you want to print can be selected as a single block (a rectangular range). There is another way to tackle the problem when working with a large database. Go back and look at Figure 5.10 again. This is the example where “Lucille Ball” was the value of a filter used against the database of Movies. If the spreadsheet were printed at this point, only the records shown in Figure 5.10 would be printed. So another method is to set the values of filters in such a way that only the records you need to print are displayed by the spreadsheet. Doing a Print Preview before the actual print is still a smart idea. ____________________________________The following exercises will help solidify the topics taught in this lesson. The sample spreadsheets can be found on your student folder. Explore the samples and have fun making changes.Exercise A - Sort the Auto Mileage SpreadsheetIn this exercise you will continue working on the auto mileage spreadsheet. You will sort the spreadsheet using several criteria. The spreadsheet can be found on your as 1.5auto.xls. Make changes to the spreadsheet and save it as 1.5auto_rev.xls.1Close all open Excel documents.2Using the Office Button | Open sequence, open 1.5auto in the Excel 1 2007 Files folderTo preserve the original spreadsheet, save the spreadsheet using a new name.3Using the Office Button | Save As… sequence, save the document as 1.5auto_rev in the same folderThe auto mileage spreadsheet should now be open..Set the Field NamesCan you determine, by looking at the spreadsheet, whether or not the labels in rows 6 and 7 qualify as Field Names? The answer is no, in spite of looking like Field Names, but there’s an easy way to check4Click in cell F8 to select it5Select the Home Tab and then select the Sort & Filter button within the Editing group. Select Custom Sort command to initiate a sort. Do not actually execute the sort.Look at the top right of the Custom Sort dialog at the checkbox next to “My data has headers” and see if it’s selected. Look at the cells highlighted behind the Sort dialog, rows 6 and 7 are not selected to be a part of the sort. These are the indications that Excel has sensed table heading information for the sort.Look again at the cells highlighted behind the Sort dialog. Does the range include the complete table (you might have to drag the dialog off to one side to get a good look)?6Cancel out the sort7Click on the column C header. Delete column C using the Edit | Delete sequence (right click)8Click in cell E8 to select it9Use the Home tab, Editing group, Sort & Filter Button, Custom Sort sequence to initiate a sort. Do not actually execute the sort yet.Look again at the range of cells highlighted behind the Custom Sort dialog box. Does the range include the complete table? The answer should now be yes.10Click on the drop down arrow in the Sort by portion of the dialogNote that Field Names are the headers immediately above the table in row 7. Excel ignores the labels in row 6. So although it might be clearer to break up a header, and put it into two cells stacked one above the other, for purposes of the database the Field Names in the header row must be unique. For example, suppose A6 = Departure and B6 = Destination. That would leave the Field Name “City” in both A7 and B7. That’s a no-no because the name is used more than once.11Select the Departure City as the first sort.12Double check that Values is selected in the “Sort on” dropdown menu and A to Z is selected in the “Order” dropdown menu, and that My data has headers is checked in the upper right area of the dialog. Click OK.Akron should now be at the top of the table, and Topeka at the bottomSort some of the table by Miles in descending sequence13Select cells A13 through cell F56 by clicking and dragging across the cells14Use the Data tab, Sort & Filter group, Sort button sequence to initiate a sort.15Click on the drop down arrow in the Sort by: selection box. Choose Column C, which is the Miles column (ensure the “My data has headers checkbox is not selected this time)16Click on the Largest to Smallest option 17Click on the OK buttonDallas to El Paso at 633 miles should now be in row 13Sort the spreadsheet by Hours on the Road in ascending sequence19Click in cell F8 to select it20Click on the Sort & Filter button on the Home tab, Editing group , then Custom Sort command. Sort by Hours on the Road.Honolulu should now be at the top of the table, and Dallas at the bottomQuick, wasn’t it. You are responsible for correctly setting up the table if you use the sort buttons on the toolbar because the sort is all done in the blink of an eye. If things don’t turn out the way you want, Undo can come to the rescue.23Press Ctrl+Home to return to A1. Save your work.Your spreadsheet should look like the one in Figure 5.18 Figure 5.18 Exercise A completed spreadsheetCongratulations! You have successfully completed Exercise AExercise B - View the Budget SpreadsheetIn this exercise you will work with the budget spreadsheet. You will split the window into panes and freeze a pane. The spreadsheet can be found on your computer as 1.5budget.xls. You will not be making any changes to the spreadsheet.1Close all open Excel documents.2Using the Office Button, Open sequence, open 1.5budget in the Excel 1 2007 Files folder.3Maximize the Excel window, then maximize the spreadsheet within the windowSplit the window4Click on the split box at the far right end of the horizontal scroll bar. The mouse pointer will look like Figure 5.19 when it’s on top of the split box.Figure 5.19 Pointer symbol when over the split box5Drag it to the left to overlay the border between Columns C and D6Using the horizontal scroll bar for the right pane, scroll the pane to bring Column N into view7Enter 4000.00 into cell B4 and observe the effect on Monthly Average in cell N188Click on the Undo button on the Quick Access toolbar9Drag the split box back to the right edge of the window and release the mouse button. The vertical split bar should disappear when you have successfully parked the split boxFreeze panes10Restore (downsize) the spreadsheet within the Excel window11Resize the spreadsheet such that Columns A through G and Rows 1 through 20 are in view12Click in cell B413Select the View tab, Window group and then select the Freeze Panes command in the Freeze Panes dropdown menu.The titles in Column A are now frozen, along with the material in Rows 1 – 3. Note that the split boxes have disappeared14Using the arrow keys on your keyboard or the horizontal scroll bar, scroll to the right and view the information for December15Using the arrow keys on your keyboard or the vertical scroll bar, scroll down and view the information for Income TotalUnfreeze panes16Select any cell17Select the View tab, Window group then select Freeze Panes button. Within the button you will now see Unfreeze Panes command. Click it to unfreeze the panes.Print only the first six months’ expenses18Select the Page Layout tab, Page Setup group 19Click on Orientation and select Landscape 20Select just the area to print: click and drag through the cell range A1:G1821Select the Print Area command in the Page Setup group on the Page Layout tab. Click on Set Print Area22Click on the Print Preview command on the Print Menu in the Office Button23Verify that just Expense information is displayed then click the Close button24Press Ctrl+Home to return to cell A1Removing a print area25Select the Clear Print Area command in the Print Area button on the Page Layout tab, Page Setup group.Congratulations! You have successfully completed Exercise BExercise C - Filter the Movies SpreadsheetIn this exercise you will work with the movies spreadsheet. You will print a portion of the database using a filter criteria. The spreadsheet can be found on your computer as 1.5movies.xls. You will not be making any changes to the spreadsheet.1Close all open Excel documents.2Using the Office Button | Open sequence, open 1.5movies in the Excel 1 2007 Files folder3Maximize the Excel window, then maximize the spreadsheet within the windowFind all the Horror movies4Select the Home tab, select Filter button in the Sort & Filter group.5Click on the drop down arrow in the Genre Field Name6Click on Horror in the drop down menu7Select the Page Layout tab. In the Page Setup group, click on Orientation and select Landscape. 8Click on the Print Preview command on the Print Menu in the Office Button Your Print Preview should look like Figure 5.21Figure 5.21 Completed Exercise C9Click on CloseCongratulations! You have successfully completed Exercise BSummaryNow you can...Understand basic database conceptsSelect, filter and sort data in a spreadsheetDivide the spreadsheet window into panes and freeze themPrint selected areas from the spreadsheet using Set Print area or data filtersNOTES ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download