Home > Articles > Home & Office Computing

  • Print
  • + Share This
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:

  1. 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.
  2. 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

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:

  1. 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.
  2. 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.
  3. 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

    Figure 6.10 Auto-Fill in action: the contents of the first six cells will be pasted into the six cells below them.

  4. 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

    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

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

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

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.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020