Parts of a spreadsheet



Working with SpreadsheetsEvergreen’s reports are generated to a spreadsheet. Spreadsheets offer a lot of flexibility in dealing with the report data. However, to take advantage of this flexibility, you have to know at least a little bit about how to use a spreadsheet. There are several major spreadsheet products today. The two most common are Microsoft’s Excel (costs money), and LibreOffice (Free). The chances are that your computer has one or the other. For our purposes, both products do exactly the same thing, but they work a bit differently in a few areas. Here we will be using LibreOffice, and noting the differences in Excel.Parts of a spreadsheetSpreadsheets have CELLS, arranged in Rows and Columns. The Columns (across the top) have capital Letters (A, B, C, etc.)The Rows (down the side) have numbers (1,2,3, etc.)Everything you enter goes into a cellCells can have three types of information – Numbers, Text and Formulas (mostly you enter numbers and text.)You refer to cells by column letter and by row number. The cell circled above is known as “D3”)Evergreen’s reports, on the first row (Row 1), have a Label Header for each column. For instance in the above, Column A, has Titles, Column B has Authors, etc.To do useful work with a report spreadsheet, you should be able to: TOC \o "1-3" \h \z \u 2.Select cells, ranges, columns, rows PAGEREF _Toc535240873 \h 23.Expand or narrow columns (and rows) PAGEREF _Toc535240874 \h 24.Freeze Panes PAGEREF _Toc535240875 \h 35.Format Information in Cells (or Columns) PAGEREF _Toc535240876 \h 36.Hide (and show hidden) columns PAGEREF _Toc535240877 \h 47.Fun Stuff – Sorting PAGEREF _Toc535240878 \h 48.Fun Stuff – Summaries (Pivot tables) PAGEREF _Toc535240879 \h 5We address each of these.Selecting cells, ranges, columns, rowsTo select a single cell, click in the cell.To select a range of cells, click in the *middle* of the first cell (not on the edge), and drag the mouse to the last cell.To select an entire column, click on the column header (the Capital letter in the gray row at the top of the column).Tip: Select a more than one column, by clicking on the first column and dragging your mouse.To select an entire row, click on the row header (the number in the gray are to the left of the row)Expand or narrow columns (and rows)Here, the cells in Column A contain Titles of items. However the cell is not wide enough to see all of the Title.To make the column wider (or narrower), hover the mouse (slowly) across the narrow line to the right of the column (in this example, column A). When the mouse is directly on the line, it turns into a two headed arrow. Then, drag the mouse right (to lengthen the column), or left (to narrow a column).Tip: If you double-click on the two headed arrow, the column automatically becomes wide enough to show the widest cell in the column.Tip: you can also manually set column width. Right-click on the column header and select Freeze PanesReports (and most other spreadsheets have a row at the top with the labels for each of the columns. When you scroll down through the spreadsheet, you will likely lose those headers on your screen. You can set these headers to stay on the screen so when you scroll, they stay on the screen.In LibreOffice, from the View Menu, Select “Freeze Cells”, then “Freeze” First Row. This assumes that the row labels are on the first row of the spreadsheet. If not, select (any) the cell in column A, just BELOW the row with the row labels, then select Freeze Cells, and Freeze Rows and Columns.Tip:. You can also select to freeze columns. Place the cursor just below the row you want to freeze, and just to the right of the columns you want to freeze.Format Information in Cells (or Columns)Occasionally, you may want to format a cell or range of cells differently. Select the cells (or entire column, or entire row), then select “Format Cells” Up comes the formatting screen. Pick the tab at the top, and then make changes.Hiding (and showing hidden) columnsReports often have more information than you need. You can hide those columns.This spreadsheet has a column showing the status of each item. If you don’t want to see that column on the sheet, select the column header (in this example, Column C), then right-slick, then choose “Hide Columns)(Of course, you could choose “Delete Columns”, but that would permanently delete the column from your spreadsheet)In LibreOffice, To hide multiple contiguous columns, select all the columns, then select “Format/Columns/Hide”In Microsoft Excel, select the columns, then right-click and select “Hide” To Unhide columns, select the entire columns on either side of the hidden column. (In this example, column G is hidden)In LibreOffice, select Format/Columns/Show In Microsoft office, right-click and then select “unhide”Fun Stuff – SortingYou can sort the entire spreadsheet by any columns.In Libre Office.Select any *single* cell in the column by which you wish to sort. (In this example, we want to sort by Author)Then, from the Data menu (at the top), select Sort.There are three “Sort Keys”. You may add up to two other sort keys. (for instance, to sort first by Author, then by Title, you simply add “Title” to Sort Key 2.In Microsoft OfficeSelect any *single* cell in the column by which you wish to sort. (In this example, we want to sort by Author)Then, from the Data menu (at the top), select Sort. (or on the Ribbon, select “Sort”)Add “Author” to the first Column. Then select Add level” and insert title.Then select “OK” to sort.Fun Stuff – Summaries (Pivot tables)You often want to get totals and summaries on various parts of your reports. For instance you would like to know how many items are in each of your library’s locations. Here is how:Before you begin:1. Every column must have a label in the top row.2. There can be no blank rows or blank columns in the dataset. (You can have blank cells anywhere, but no entirely blank rows or columns. In Libre Office.Select any single cell anywhere in the dataset, then from the “Data” Menu, select “Pivot Table”, then select “Create”The entire sheet’s background turns blue and the “Select Source” dialog box opens (to “Current selection”. Click “OK”A list of all of the fields appears.Drag “Location” to the box under “Row Fields” (this will give a list of every location). Then drag “Location” (again) into the white box under “Data Fields”.Open the “Options” box and select “Enable drill to details”.Then DOUBLE-Click on “Sum-Location” in the Data Fields boxThen, instead of Sum, click “Count”.(In this case we only want a count of each of the items in location)Then click “OK” twiceThe summary table shows. (If you would like to modify the layout of the summary, right-click on any cell and select “Edit Layout”) In ExcelFrom the Insert menu, select “Pivot Table”(Usually these settings are appropriate).Select “OK”All of the fields are shown on the right. Below are four boxes. Drag the “Location” field down to the “ROWS” box.(This gives a list on the left of every location)Then Drag “Location” down to the “VALUES” box(This adds the total count of items in each of the locations)Note, for both LibreOffice (if you selected “Drill down”) and for Excel, if you double-click on any of the numbers, a separate sheet opens with all of the items in that location. (e.g. if you double click on “Children”, a separate sheet opens with all 735 of the items in the Children location. ................
................

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

Google Online Preview   Download