This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with Data
Earlier in this chapter, you saw how easy it is to get some data into your spreadsheet: simply click inside a cell and type. And while that gets you started with Google Docs spreadsheets, this section takes you to the next level. Learn how to import existing spreadsheet data into Google Docs, get up to speed with data-entry tips and tricks, work with rows and columns, sort data, and move data from Google Docs to another spreadsheet program. It's all covered here.
Importing Data into Google Docs
If you've been working with spreadsheets for a while, you've probably got data that you'd like to move into Google Docs—and you definitely want to do that as quickly and painlessly as possible. When you have spreadsheet data that lives outside of Google Docs, you can create a new spreadsheet from that data by importing the info into Docs.
Before you import a spreadsheet, keep in mind that imported spreadsheets must be 1MB or smaller and in one of these formats:
- Microsoft Excel (.xls)—If you have spreadsheets in Excel, you can import them directly into Docs.
- OpenDocument Spreadsheet (.ods)—This is the format used by the spreadsheet programs of OpenOffice.org and StarOffice, among others.
- Comma-separated values (.csv)—This format contains pieces of data separated by commas. Most spreadsheet programs let you export or import data using this format.
- Tab-separated values (.tsv)—This is like a CSV file except that the pieces of data are separated by tabs rather than commas.
- Text file (.txt)—As its name suggests, this kind of file holds text: unformatted letters and numbers. If you've got a text file set up like a table (one record per row with tabs between each record's individual pieces of information), you use the text file to create a new spreadsheet. If you want to import a text file, start the import from the spreadsheet editor; otherwise, Google will interpret the file as a word-processed document, not a spreadsheet.
The steps for importing a file depend on your starting point:
- From the Google Docs home page—Click the Upload button. On the page that opens, click the Browse button. This opens a new window; find and select the file you're importing and then click Open. Back in Google Docs, click Upload File.
- From the spreadsheet editor—Click File, Import. This opens the Import File dialog box. Click Browse and in the window that opens, select the file you want and click Open.
Whichever method you use, Google imports the data, using it to create a new spreadsheet. The Docs spreadsheet has the same title as the file you imported.
Exporting Data from Google Docs
Just as you can import data from other programs into a Google Docs spreadsheet, you can also transfer data from a Docs spreadsheet into another program. This is called exporting, and it's useful when you want to download the spreadsheet to your computer and then work on it offline, using a program such as Microsoft Excel or OpenOffice.org Calc.
When you export spreadsheet data from Google Docs, you can save it in one of these formats:
- Microsoft Excel (.xls)
- OpenDocument Spreadsheet (.ods)
- Comma-separated values (.csv)
- Text file (.txt)
- Hypertext markup language (.html)
- Portable document format (.pdf)
The exporting process varies, depending on the format you choose to export the file. The next three sections explain.
Exporting as an XLS or ODS File
If you want to export the spreadsheet's data into Excel, OpenOffice.org Calc, or StarOffice Calc, open the spreadsheet you want and click File, Export. From the menu that appears, select .xls (for Excel) or .ods (for Calc).
Your Web browser opens a dialog box asking how you want to handle the file. There, choose to save the file to your computer (in the format you chose) or to open it in the appropriate program. Your computer downloads the file and then, depending on what you chose, either saves it or opens it.
Exporting as a CSV, HTML, or TXT File
When you export spreadsheet data in one of these formats, Google converts the file to the format you choose and opens it in a new browser window. (For this reason, you can export just one sheet at a time when you choose one of these formats).
Open the spreadsheet you want (if the spreadsheet has multiple sheets, select the sheet whose data you're exporting). Click File, Export and then choose one of these options:
- .csv Sheet Only
- .html Sheet Only
- .txt Sheet Only
Your Web browser opens the spreadsheet data in a new window. How it looks depends on the format you chose:
- CSV shows one record per line with commas separating pieces of information. This kind of file does not preserve your spreadsheet's formatting, just its data.
- HTML looks like a table and shows your spreadsheet's formatting.
- TXT shows one record per line with tabs separating pieces of information.
After the exported file has opened in a new window, use your Web browser's File menu to save the file. Then you can reopen it in the program you want.
Exporting as a PDF
PDF stands for portable document format, and what it means in practice is that your document's formatting gets preserved no matter what platform you use to create it or to view it (such as Windows XP or Vista, Mac, or Linux). Simply open the document in a PDF reader such as Adobe Reader, Adobe Acrobat, or Foxit, and you can read it as it was formatted. And as you'll see in "Printing a Spreadsheet" later in this chapter, the first step in printing a spreadsheet is exporting it in this format.
When you want to export spreadsheet data as a PDF, open the spreadsheet and choose File, Export, .pdf. This opens the Export to PDF dialog box shown in Figure 6.9.
)
Figure 6.9 When you export spreadsheet data as a PDF, choose the data you want to export and how you want it displayed.
The dialog box has four sections for you to fill out:
- What Parts? Choose whether you're exporting just the current sheet or all sheets.
- How Big? Choose Fit to Width (which shrinks or expands the text to suit the size of the page) or Actual Size (which, for large spreadsheets, may overrun the page). Also in this section is a checkbox labeled Repeat Row Headers on Each Page, which is checked by default. If your spreadsheet will run to multiple pages, it's a good idea to leave it checked.
- Which Way? Select Landscape (horizontally oriented) or Portrait (vertically oriented).
- What Paper Size? You've got three choices:
- Letter (8.5 inches ? 11 inches)
- Legal (8.5 inches ? 14 inches)
- A4 (210 mm ? 297 mm)—If you're not up on the metric system, that's about 8.25" ? 11.7."
After you've made your selections, click Export. Your Web browser opens a dialog box asking whether you want to save the file or open it in an appropriate program. Make your selection, and your computer downloads the PDF file.
Entering Data
Anyone who's ever worked with spreadsheets knows that entering data can get awfully repetitive. You can speed up your work and lower the boredom factor by using the techniques in this section to enter data more efficiently.
Using Auto-Fill
Auto-Fill is a helpful feature when you're repeating a set of data and you don't want to have to type the same thing over and over (and over) again. For example, imagine you have a spreadsheet that tracks, on a weekly basis, when your organization's meetings rooms are in use. At the start of a new week, you don't want to have to type in all the meeting rooms' names all over again. When you use Auto-Fill, you don't have to. Just choose a range of cells and use Auto-Fill to copy their contents into an adjacent group of cells.
Here's how to use Auto-Fill:
- Select the range of cells you're copying. Notice the small blue box (called a handle) that appears in the lower-right corner of the range.
- Put your mouse pointer right on top of the handle. When the cursor becomes a cross hairs, click and drag in the direction you want to Auto-Fill.
- As you drag, a dashed gray line shows the Auto-Fill area—the cells to which Google will copy your original selection. Figure 6.10 shows what this looks like.
Figure 6.10 Auto-Fill in action: the contents of the first six cells will be pasted into the six cells below them.
- When the dashed gray line surrounds the cells you want to fill, let go of the mouse button.
)
Like magic, Google pastes the contents of the cells you originally selected into the Auto-Fill area in order and repeated as many times as necessary to fill in the Auto-Fill area.
Auto-Fill can do better than just copying what you've already typed. It can also recognize common patterns—as long as you give it enough information to recognize the pattern. Say you're typing the names of the months of the year across your spreadsheet, one month per column. If you type January in column A, February in column B, and March in column C, you can stop typing right there. Select the three months you've typed so far, and use the lower-right handle to drag the Auto-Fill area nine columns to the right. When you let go of the mouse button, Google fills in the other months of the year across the spreadsheet.
Copying Data
Auto-Fill is fast and easy when you're copying data to next-door-neighbor cells, but you might want to copy cells' content to other places, as well, such as another part of the spreadsheet or a different sheet.
To copy the contents of a cell or range of cells, select what you want to copy and then use one of these methods:
- Right-click (Control-click on a Mac) to open a context menu. Select Copy.
- Select Edit, Copy.
- On the keyboard, press Ctrl+C (Cmd-C on a Mac).
Next, go to where you want to paste in the cell contents you copied. You can select a range of cells or just click inside the first cell in the range. Use one of these methods to paste what you copied into the new location:
- Right-click (Control-click on a Mac) and select Paste from the context menu.
- Select Edit, Paste.
- On the keyboard, press Ctrl+V (Cmd-V on a Mac).
Google pastes the data into its new home. Note that what you paste into a cell overwrites the cell's current contents (if any).
Copying Down or Right
When you copy down or copy right, it means that you copy the contents of a single cell to a range of cells. To do this, select the cell whose contents you want to copy. With that cell selected, expand the range to include the cells you want to copy to. You can expand the range in any of these ways:
- By dragging the mouse.
- By holding down the Shift key as you use the down or right arrow key.
- By holding down the Shift key as you click the last cell in the range.
When you've selected the cell you're copying and the range of cells you're copying to, press Ctrl+D (Cmd-D on a Mac) to copy down or press Ctrl+R (Cmd-R) to copy right. Google pastes the contents of the cell into the range you selected.
Clearing Data
You can easily clear the data from a single cell or an entire spreadsheet. The ability to clear data is useful when, for example, you've made a duplicate of a spreadsheet and want to keep its formatting but not its contents.
To clear data from a cell or a range of cells, select the cell or range you want to clear. Use any of these methods to clear the contents from your selection:
- Right-click (Control-click on a Mac) and choose Clear Selection from the context menu.
- Select Edit, Clear selection.
- On the keyboard, press Delete.
Working with Rows and Columns
As you work on a spreadsheet, you'll probably find that you need to adjust its columns and rows. Maybe you need to insert a new column between two existing ones, or perhaps you want to hide some rows or columns to give a more focused view of the data. This section explains your options for working with rows, columns, and their data.
Adding a Row or Column
When you want to insert a row or column into a spreadsheet, select a row or column next to the spot where you want to insert the new one. (To select a row, click the number on its left; to select a column, select the letter at its top.) Then take one of these actions:
- Right-click (Control-click on a Mac). From the context menu shown in Figure 6.11, choose Insert 1 above or Insert 1 below (when you've selected a row) or choose Insert 1 left or Insert 1 right (when you've selected a column).
Figure 6.11 Select a column and then right-click it to get this menu of options.
- Click Insert. From the Insert menu, choose Row Above or Row Below (when you've selected a row) or choose Column Left or Column Right (when you've selected a column).
)
Google inserts a row or column according to what you chose.
Deleting a Row or Column
To delete a row or column, select the row or column you want to remove from the spreadsheet. Then choose a deletion method:
- Right-click (Control-click on a Mac). The control menu shown in Figure 6.11 appears; choose Delete Row or Delete Column.
- On the menu bar, click Edit. From the Edit menu, select the row, column, or range you want to delete.
Whichever method you choose, Google doesn't ask for confirmation before it deletes the row or column (and all its data), so watch what you're doing. If you make a mistake, click the Undo button immediately.
Moving a Row or Column
You can move a row or column by cutting it from its present location and pasting it elsewhere, but that's not the quickest way.
First, make sure that you've got room to move the row or column to: Insert a row or column (see earlier section) at the location where you're moving the data.
Next, select the row or column you want to move. Hover the mouse pointer over the selection's border. When the cursor changes to a pointing hand, click and drag the selection to its new location. Let go of the mouse button to drop the row or column into place.
Hiding a Row or Column
Sometimes you want a narrower view of the data. You might have a spreadsheet listing customer contacts, for example, that lists name, job title, address, phone number, email address, product interest, and notes. But right now, you're making phone calls, so all you need to see are names and phone numbers. You can hide everything except the information you need to see.
To hide a row or column, select what you want to hide. Right-click (Control-click on a Mac) to see the context menu shown back in Figure 6.11. From the menu, select Hide Row or Hide Column. Google hides the row or column you chose, putting in a marker to indicate that a hidden row or column occupies that spot of the spreadsheet. The column and its data are still part of your spreadsheet; they're just not displayed in the current view. (To display the row or column again, click the marker.) Figure 6.12 gives you an idea of what a spreadsheet looks like with some rows and columns hidden.
)
Figure 6.12 When a row or column is hidden, Google puts in a marker (circled).
Sorting Data
You enter data as it comes to you: Three new employees join your company, so you add them to the employee register. Or you got five new DVDs for your birthday, so enter them in the spreadsheet that tracks your movie collection. As the data in your spreadsheet grows, however, it can be hard to find a particular employee or DVD title in all that information. And that's where sorting comes in handy. Sorting lets you organize the information in your spreadsheet so you can answer questions about the data (do you have all the Hitchcock movies yet?) or find a particular piece of information.
When you sort data, you simply arrange the data in your spreadsheet in a particular order, either ascending (from A to Z or from the lowest number to the highest) or descending (from Z to A or from the highest number to the lowest). For example, say you're looking for information about an employee named Mary Zimmerman. Because Zimmerman begins with the letter Z, it'd be easiest to find Mary if you start at the end of the alphabet—that is, if you sort employees by last names in descending order so that names starting with Z appear at the top of the spreadsheet.
The example uses last name as the basis for sorting, but you can sort the data in your spreadsheet by any column. For example, you might sort employees by department, job title, or employee ID.
To sort a spreadsheet's data, select the column you're sorting by and then use one of these methods:
- Right-click (Control-click) the column. From the context menu that appears, select Sort A, Z (for ascending order) or Sort Z, A (for descending order).
- On the menu bar, click Tools. From the Tools menu, select Sort by column x A, Z (for ascending order) or Sort by column x Z, A (for descending order). In the Tools menu, x will be replaced by the letter of the column you chose.
Google rearranges your spreadsheet's records according to the kind of sort you selected.
Sorting Data by Using the Sort Bar
Google offers a super-quick shortcut for sorting the data in your spreadsheet. It's called the Sort Bar, and you can see it at the top of a spreadsheet between rows 1 and 2, as shown in Figure 6.13. (You can also move the Sort Bar, as the next section explains, but for now, we'll just work with the Sort Bar where it is.)
)
Figure 6.13 Use the Sort Bar to quick-sort your data. Here, the data will be sorted on Job Title.
When you want to sort by a particular column, hover the mouse pointer over the Sort Bar in that column—and you'll see why it's called the Sort Bar. The color of that segment of the bar changes to orange, and the word Sort appears, along with a downward-pointing arrow. Click the arrow and choose either A, Z or Z, A to select ascending or descending order for your sort.
But sorting isn't all the Sort Bar can do. As the next section explains, you can also use the Sort Bar to freeze rows, keeping them out of any sorts of the data.
Freezing Rows and Columns
You're probably wondering already: What if I don't want to include everything in the sort? A good example of something you don't want to sort is column headings: you need those at the top of your spreadsheet to make it clear what kind of data each column holds. And when you do a sort, you find that Google doesn't sort the headings. That's because in any new Docs spreadsheet, row 1 (the row that holds column headings) is frozen by default. Frozen simply means that the row (or column) doesn't participate in a sort; it stays right where it is while the sort rearranges the spreadsheet's data.
You can freeze up to ten rows and up to five columns in a spreadsheet. This can be helpful when, for example, you have column subheadings that you want to stay in place. Figure 6.14 shows an example of a frozen row and a frozen column.
)
Figure 6.14 Drag the Sort Bar and the Column Bar to freeze rows and columns, respectively. In this example, Column A and Row 1 are both frozen.
To freeze a row or column, click Tools in the menu bar. From the Tools menu, select the number of rows or columns you want to freeze. Google freezes that number: for rows, it freezes the top x number of rows; for columns, it freezes the leftmost x number of columns.
Freezing Rows by Using the Sort Bar
To freeze a row using this method, place your mouse pointer on the far-left end of the Sort Bar so that the cursor changes to a four-way arrow. Click and drag the Sort Bar downward, positioning it just below the row you want to freeze. When you let go of the mouse button, the Sort Bar jumps to its new location, freezing any and all rows above it.
To unfreeze a row, simply move the Sort Bar above that row.
Freezing Columns by Using the Column Bar
As Figure 6.14 shows, a narrow gray bar separates frozen columns from the rest of the spreadsheet. Google calls this the Column Bar, and it freezes columns in the same way that the Sort Bar freezes rows.
To freeze a column, place your mouse pointer over the highest part of the Sort Bar. When the pointer becomes a four-way arrow, click and drag the Column Bar to the right. Let go of the mouse button to drop the column bar into place and freeze all columns to its left. If you want to unfreeze a column, drag the Column Bar to the left side of that column.