Docs

Chapter 4: Spreadsheets

Table of Contents

Spreadsheets in the classroom

Enter data into spreadsheets

Edit a cell

Change cell data type

Copy and paste

Add links in a spreadsheet

Add images in a spreadsheet

Repeat or extend patterns with auto-fill

Keyboard Shortcuts

Share and collaborate with spreadsheet data

View and edit spreadsheets together

Prevent others from editing the same cell

Chat with other collaborators

Prevent others from editing specific sheets

Stay informed when others modify a spreadsheet

Ensure entered data is the right format

Useful functions and formulas

About formulas

Shortcuts for entering formulas

Quick sum

Import data from webpages

Translate text and detect language in cells

Split cell text into multiple cells

Combine text into a single cell

Manage your spreadsheet

About sheets

Move or copy sheets

Hide sheets

Adjust rows and columns

Show, hide, and freeze rows and columns

Merge cells horizontally or vertically

Spreadsheet formatting and rules

Copy cell formatting with format painter

List view for simple spreadsheet display

Check spelling

Cell data validation

Analyze spreadsheet data

Basic sorting

Filter data

Advanced sorting

About charts

Use gadgets to visualize data

Creating and editing pivot table reports

Overview of pivot table reports

How to create a pivot table report

Navigate the report editor

Categories and fields

Sort, summarize, and filter data

Format and edit a pivot table report

Create a sample pivot table report

Create and publish charts

Create charts from spreadsheet data

Chart multiple ranges

Edit chart data settings

Customizing charts

Copy and paste charts

Publish and export charts

Spreadsheets in the classroom

Spreadsheets in Google Docs allows teachers and students to easily aggregate, organize, and analyze information in one place. With advanced tools for sorting, formatting, creating charts, visualizing information with gadgets, and entering formulas, shared, online spreadsheets can be used in a variety of settings.

Some examples of how teachers can use online spreadsheets:

Some examples of how students can use online spreadsheets:

Enter data into spreadsheets

Edit a cell

Google spreadsheets looks and acts similar to other spreadsheet software. To get started entering content into a cell, just click a cell and start typing.

Please note: Each spreadsheet can be up to 256 columns, under 400,000 cells, or 100 sheets, whichever is reached first, to be converted to Google Docs format. There's no limit on rows. Uploaded spreadsheet files that are converted to Google spreadsheets format cannot be larger than 20MB.

To edit a cell that already has content, follow these steps:

  1. Choose one of the options below to place the cursor in the cell:
  1. Edit the cell's content.

You can create lines (new lines, or line breaks) within a cell while editing; this is helpful when you want to improve the look of text that involves distinct lines, such as addresses. Here's how:

  1. Place the cursor in the appropriate cell.
  2. Use one of these browser-specific options to create lines:

 

Change cell data type

There are several different data types a cell can be, including various currencies, date, time, percentages, and rounding

The data type of a cell applies to the whole cell. For example, you cannot have one part of the cell be a currency and the other a percent.

To select a different data type for a cell, follow these steps:

  1. While editing the spreadsheet, select the cell/s you'd like to change
  2. Open the 123 drop-down menu.
  3. Select the desired data type.

There are shortcuts for the percent and US currency data type changes right in the Edit toolbar as well.

Copy and paste

Google Docs is designed to let you copy and paste text and images between all of your online documents – even if you're going from one document type to another.

This can be useful for collecting information across a variety of media or the process of outlining and drafting in a document, while moving content over to a presentation.

And since the copying and pasting takes place online, you can copy on one computer, then sign in and paste on another one. Here are some examples of things you can do:

There are two ways to copy and paste: keyboard shortcuts and the server clipboard menu.

Keyboard shortcuts: Ctrl-C and Ctrl-V For most copying and pasting, you can use the familiar keyboard shortcuts, Ctrl-C (Copy) and Ctrl-V (Paste). This even works when going from one doc type to another. But there are a couple of exceptions in which keyboard shortcuts don't work as well (yet). In these cases, you should use the server clipboard menu:

Paste special

Copying and pasting in Google spreadsheets copies all properties of a cell into the cell you're pasting into, including text, formatting, and data validation. The paste special option gives you more control about what properties you're copying and pasting into a range of cells. To use one of the paste special options, click the Edit menu, navigate to Paste Special..., and select from the following options:

The server clipboard menu

There are a few specific cases in which the best way to copy and paste is using the server clipboard menu. When you copy a selection using this menu, the content you copy is stored and associated with your school Apps Account. That means you can copy more than one selection and then choose which one to paste later; it also means you can copy something on one computer and then paste it on another.

To copy a selection using the web clipboard menu, follow these steps:

  1. Select what you'd like to copy.
  2. Click the Web clipboard menu that appears in the toolbar of your doc.
  3. Click Copy selection to web clipboard.
  4. In the destination document, click the web clipboard menu; you'll see the selection that you previously copied. If you copied multiple things, you'll see a list of the items you've recently copied.
  5. Place the cursor where you want to paste the content.
  6. Click the Web clipboard menu.
  7. Select what you want to paste. Depending on your selection, you'll see different formats that you can choose from to paste what you've copied (for example, HTML or plain text).
  8. Select a format.

Please note: You can't use the web clipboard to copy and paste regular text and images in Google Docs presentations yet. You can select entire shapes on a single slide, and if the shape is a text shape, then the text will be copied to the server clipboard.

How content on the server clipboard is stored and protected

You can access the content copied to the web clipboard only by signing in to the same Google Account you used to copy the content originally.

Content you copy to the web clipboard is stored on Google's servers and remains there until 30 days have passed since you last took action on (for example, copied) a given content selection. Even if a document is deleted, anything you copied from that document to the web clipboard will still exist on Google's servers for that 30-day period. You can delete all items stored on the web clipboard by clicking the drop-down menu and selecting Clear all items.

Add links in a spreadsheet

Since Google Spreadsheets are created and accessed online, you can include hyperlinks to websites just as you might when creating a webpage.

Links in your spreadsheet can be useful for directly referring to your data sources. By keeping information in a spreadsheet, you maintain the ability to sort and analyze your information while including easy access to your references.

For example, if you were collecting research from all around the web in a structured format, a spreadsheet could store the data and you could add a links to data in a “reference” column. Unlike listing entries in a word processing document, with a spreadsheet you can sort all the entries and perform analysis (such as averages, sums, etc). Anyone viewing your spreadsheet can view your data sources.

Please note: Like the other types of data and cell formatting, links in spreadsheets must be assigned to an entire cell, you cannot have just part of the text inside a cell be hyperlinked.

There are two ways to enter links into a spreadsheet:

  1. Enter the full URL into a desired cell, and the entire cell will become a hyperlink. This is the quickest way to add a URL to your spreadsheet. With this method, the text inside the cell is the URL.
  2. Enter the hyperlink formula, and you can specify the URL but assign separate text. Instead of displaying the full link URL in the cell, it can display the specific text you enter.

In this example, the first two links are using the hyperlink formula, and the last has just a URL pasted in.


With either method, whenever this cell is selected, a small "pop-out" icon will appear to the left of the cell. Click on this icon to open a new browser window linking to the included URL.

To use the hyperlink formula, follow these steps:

  1. Navigate to the cell you wish to edit.
  2. Double-click the cell, click the cell and press Enter, or click the cell and press F2 to edit.
  3. Use the hyperlink formula in this format: =hyperlink(“website URL”, “cell text”).

Add images in a spreadsheet

Users can now upload images directly into to Google spreadsheets. With this feature, you can upload an image already stored on your computer, search for an image online, or add personal photos directly from one of your Picasa Web Albums. The possibilities are endless!

To upload an image, go to the
Insert menu, select Image. Next, choose an image file to upload into your spreadsheet.

Insert and modify an image with formulas
When inserting a publicly accessible image inside of a cell in your spreadsheet, you have some options for changing the way the image appears through the use of formulas.

There are four options for modifying the appearance of images in a spreadsheet

The image in the cell can be aligned in many ways, just as text in a spreadsheet cell can be by using the alignment controls in the toolbar.

Check it out for yourself in this template.

Repeat or extend patterns with auto-fill

Auto-fill in spreadsheets allows you to repeat information with the drag of a mouse. However, with Google Docs, it goes one step further. It is able to complete a pattern of content in a spreadsheet based on knowledge of commonly known series, genres, and data sets.

For example, Auto-fill can be be used for entering a series of odd numbers. Start entering odd numbers in a few cells, then select the set and try Auto-fill. Google will then complete the pattern, listing odd numbers just as you did before. The same can go for counting with other patterns.

Or you can try it with words, such as entering the days of the week, you can enter the first 3 days and then use the Auto-fill function to extend the pattern for the next 4. If you go beyond the 7 days of the week, Google will simply repeat the pattern. The same functionality would apply to names of the month.

Auto-fill can save time in entering these types of patterns.

To use auto-fill in your spreadsheet, follow these steps:

  1. Enter your content. Auto-fill usually needs about 3 cells of data to learn the pattern.
  2. Highlight the cells. A small blue box appears in the lower-right corner.
  3. Click the blue box and drag it to select additional cells where you'd like your content to repeat. (When you place your cursor over the blue box, the cursor arrow changes to an x-y axis.)

Please note: When dragging the blue box, you can only select those cells that are directly above, beneath, or beside the highlighted cells.

If Auto-Fill recognizes a pattern in the content of the selected cells, it fills in the remaining items to complete the pattern. In the example shown here, it finished the pattern by auto-filling Thursday, Friday, Saturday, etc. If Auto-Fill doesn't recognize a pattern, it will just repeat the content that was highlighted.

Keyboard Shortcuts

Although shortcuts have long been available in your Google spreadsheets, we've added a shortcuts menu to spreadsheets to make them easier to find -- saving you even more time. Hit Ctrl+/ (or Cmd+/ on a Mac) to pull up the list of shortcuts and learn new ways to sharpen your spreadsheet skills. Remember that shortcuts can vary depend on the operating system and browser you're using -- the example below is the menu you’d see if you were using a Chrome browser on a Mac.

Share and collaborate with spreadsheet data

View and edit spreadsheets together

To learn how to share spreadsheets with individuals or groups, review previous chapter on sharing »

Spreadsheets and documents have the same sharing interface.


If you and another collaborator are editing the same spreadsheet at the same time, a box with the name of the collaborator appears at the top of the screen. Additional collaborators that join will also be listed in this box

Click the arrow to the right of the names to open a tab where you can chat with other editors within the spreadsheet


Please note: A total of 50 people can edit a spreadsheet simultaneously. You can share a spreadsheet with 200 collaborators and/or viewers.

You can also send collaborators or those with viewing access a direct link to a sheet within your spreadsheet. This is useful if you have several sheets in a spreadsheet but want to direct viewers to a specific sheet of interest (i.e. the summary sheet instead of a sheet with all the data).

You don't need to do anything special to generate a link to a sheet. Just navigate to the sheet you want to link to, copy the URL from your address bar, and send that link to someone who has access to your spreadsheet. The link should look like this, with the '#gid=' parameter at the end of it:

https://spreadsheets.google.com/a/myschool.org/ccc?key=1234567890#gid=5

Please note: Those who click a direct-sheet link need to be signed in to their school Apps account with access to the spreadsheet for the link to work. Review document visibility settings to learn how to make your spreadsheet available to anyone in your school.

Even if a school has enabled the ability to share outside of the domain, users outside of the domain will still need to sign in with a Google Account. If they attempt to access a direct-sheet link without signing in, they will be asked to sign in, after which they will be re-directed to the first sheet in the spread sheet. 

Prevent others from editing the same cell

If you have multiple collaborators on a spreadsheet at the same time, it’s important to know where others are editing so you don’t overwrite their data.

With the cell-presence indicator, you and your collaborators can avoid editing a cell at the same time. Just look for a uniquely colored cell border: the cells that you're working in will continue to have a blue border, but the borders of the cells that your collaborators are working in will be marked by one of ten different colors.

View and edit comments in cells

Comments in spreadsheets are a helpful way to leave notes to other collaborators about your spreadsheet data. Comments can only be added to individual cells.

Both collaborators and viewers can see comments that have been added to a spreadsheet. Collaborators can edit or delete comments as well as view them, while viewers can only see comments.

Insert comments

To add a comment to your spreadsheet, please follow these steps:

  1. From the "Edit" screen, click the cell where you'd like to insert a comment.
  2. Choose one of the following methods to insert a comment:
  1. A Comment box appears with your username included.
  2. Enter the text of your comment.
  3. To save your comment and close the Comment box, click another cell in the spreadsheet.

An orange mark now appears in the top-right corner of the cell where you inserted the comment.

Edit coments

To edit a comment inserted in a spreadsheet, follow these steps:

  1. Hover your cursor over the orange comment indicator in the cell of the comment you'd like to edit.
  2. The Comment box appears and shows the text of your comment.
  3. Click the Comment box and enter your desired edits.
  4. To save and close the Comment box, click another cell in the spreadsheet.

The edited text now appears in the Comment box.

Delete comments

To delete a comment, follow these steps:

  1. Hover your cursor over the orange comment indicator.
  2. Click the Comment box and choose one of the following options to delete:
  1. Click another cell in the spreadsheet.
  2. The orange comment indicator disappears from the cell since your comment was deleted.

Chat with other collaborators

The chat feature built-in to Google Docs is a convenient way to see who else is viewing and editing a spreadsheet and quickly discuss changes that are being made in real-time.

The chats are not saved and do not interrupt the content of the spreadsheet.

When more than one person is viewing a spreadsheet, a box with the number of collaborators viewing the document appears at the top of the screen. Additional viewers that join will also be listed in this box

Click the arrow to the left of the names to open a tab where you can chat with other editors and viewers within the spreadsheet.

If you do not have the chat window open and someone sends a message, the box with viewers will be highlighted the color of the viewer sending you the message.

Prevent others from editing specific sheets

If you want to restrict your collaborators from editing specific sheets of your spreadsheet, you can protect those sheets in your workbook.

This feature is useful if you have a spreadsheet with information on sheets divided for different classes and you want only those in a class to edit their specific spreadsheet. You can then protect the sheets from the wrong people making changes. The same can be done for any spreadsheet in which you want to control who has access to which sheets.

To protect a sheet, click the sheet's tab at the bottom of your spreadsheet (next to the Add sheet button) and select Protect sheet.

On the window that appears, select one of these options:

Only owners can set editing permissions for individual sheets. Your collaborators and viewers will still be able to view the data on the protected sheet, but depending on the permission you set for each sheet, they won't be able to make any changes or delete a protected sheet.

If a collaborator duplicates the protected sheet, copies the workbook, or uploads a new version, sheet protection will be disabled. Similarly, if a viewer creates a copy of the spreadsheet, the data on the protected sheet can be edited in the copy. Both of these scenarios are creating a separate version of the spreadsheet, though, and your original, sheet-protected spreadsheet will remain secure.

Stay informed when others modify a spreadsheet

You can set notifications to find out when your collaborators have modified your spreadsheets, and learn what sheets or cells they've modified.

In the notifications, collaborators will see the usernames of people who have made changes. Viewers can set notifications but can't see usernames.

You won't be sent notifications about your own changes. You'll only receive notifications when your collaborators make changes.

You can also choose how often you'd like to be notified.

Here's how you can enable notifications:

  1. Click Tools from the top menu and select Notification Rules...
  2. In the window that appears, select when and how often you want to receive notifications:
  1. Click Save.

Ensure entered data is the right format

Data validation helps you control what data you and your collaborators enter in your spreadsheets. If you have specific requirements for how your content should appear (for sorting or other analysis), you can make sure other collaborators follow your format.

This is useful to make sure everyone entering information is using the correct input. With data validation you can:

Please note: Data validation doesn't support boolean operators.

To set-up cells to have specific formats, follow these steps:

  1. Highlight cells to select a range you want to apply data validation to. A new window appears.
  2. Go to the Data menu and select Validation...
  3. Select your validation criteria. Your cell range should already be filled in if you highlighted the range of data beforehand. A help tip is generated as you type.

    The
    Reset link that appears when you edit the help text is designed to make sure the text gets automatically generated.
  4. If you choose to allow invalid data with a warning, then people can enter invalid data, but they'll see a warning whenever the data doesn't match the criteria.
  5. Click Save.

To remove data validation, simply select the range of cells and click Remove validation. If you'd like to remove validation from the entire worksheet, select the whole sheet by clicking the upper left corner of the sheet. Then, select Data > Validation, and click the Remove validation button.

Please note:

Similarly, when exporting a Google Spreadsheets file into an .xls or .ods, validation rules won't be exported.

Useful functions and formulas

About formulas

One of the primary differences between a table you create in a word processing document to segment data and a spreadsheet is the ability to evaluate data that is entered into cells with formulas. These can include the typical mathematical formulas (add, subtract, multiple, divide) to comparisons (greater than, less than) to more complex statistical and engineering concepts. You can use formulas with text, too, for things like finding a character, splitting the text in a cell, forcing the cell uppercase or lowercase, etc.

To add formulas to your spreadsheets, follow these steps:

  1. Double-click an empty cell.
  2. Click the Formulas button on the toolbar.
  3. Select a formula from the list that appears.
  4. Click the More functions link to see additional formulas that can be hand typed.

The formula is inserted into the cell you selected in step 1 and you can begin to enter symbols and attributes to see the formula computation.

If you're using Google Chrome, Internet Explorer or Safari, you may have noticed that cells referenced by formulas get highlighted when you edit the cell with the formula in it.

For a full list of available functions, browse this page ». 

Shortcuts for entering formulas

Besides inserting formulas from the Insert menu, you can also add formulas by entering them directly in the cell, starting with the = sign.

If you're tired of typing long formulas, or you don't remember the exact syntax, you can use the auto-complete feature. Just enter the first few characters of the formula you want to use, and a list of relevant formulas automatically appears below the cell you're typing in. To use a formula, just select it from the list and enter the desired values.

The formula autocomplete also tells you the necessary values and format to complete the function.

Additionally, for some formulas, auto-complete selects values based on the data in your spreadsheet. In this example, auto-complete is adding the following text from the spreadsheet: 'settlement,' 'maturity,' and 'frequency.'

Quick sum

One of the most common functions in spreadsheets is to add up values in cells. If you just want to see a sum of a few cells but don’t need to record the answer in a formula, you can use the Quick sum feature.

Quick sum lets you see the sum of selected cells right in the spreadsheet, without entering any functions or formulas.

To use this feature, first go to File > Spreadsheet settings and make sure that your spreadsheet's Locale setting is United States. Then, highlight the cells of data that you'd like to total:

The sum appears in the bottom-right corner of the page:

Please Note: The quick sum feature is enabled based on your location and may not work in number/currency formats other than English. However, if you chose a non-English language in the Spreadsheets language settings, you can still use the quick sum feature when you're working with a spreadsheet that's in English. Additionally, the quick sum feature can't be used for more than 1000 cells.

Import data from webpages

Some webpages have statistics or data that you might want to use in your spreadsheet. Instead of having to manually copy and paste all the information over, you can use a feature in spreadsheets lets you get information from filetypes such as xml, html, csv, tsv, as well as RSS and Atom feeds that you might read today in Google Reader.

Please note: The functions listed here can only read publicly available files by default. Data in other spreadsheets can only be referenced after the relevant spreadsheet has been published. If a file is behind a corporate firewall, the firewall administrator will need to provide Google with access using Secure Data Connector.

Additionally, the limit on functions per spreadsheet is 50. However, after you import the data, you can also choose to select the new information, and then Paste values only. Then you are no longer using a formula but still have the data imported into your spreadsheet.

Functions:

=importXML("URL";"query")

=importData("URL")

=ImportHtml(URL; "list" | "table"; index). This imports the data in a particular table or list from an HTML page. The arguments to the function are as follows:

=ImportFeed(URL; [feedQuery | itemQuery]; [headers]; [numItems]). This function imports an RSS or ATOM feed, just as you can in Google Reader. The arguments to the function are as follows:

Translate text and detect language in cells

You can use functions in your Google spreadsheet to translate text and detect the language in any of the cells.

The translation appears as a comment for the cell. One way you might use this is to study a selection of foreign language phrases

GoogleTranslate function

Use this function to translate text from and into one of over 40 languages using Google Translate:

=GoogleTranslate("text", "source language","target language")

For example:

=GoogleTranslate("Hola, ¿cómo estás?","es","en") translates into "Hi, how are you?"

Please note: You can leave out the target language (in this case, "en") and the function automatically selects the default language of your spreadsheet.

DetectLanguage function

In addition to translating, you can also use a function to find out what languages is being used in a cell:

=DetectLanguage("text")

For example:

=DetectLanguage("Hola, ¿cómo estás?") would give "es" as a result.

Split cell text into multiple cells

The SPLIT function is useful for modifying both text and number entries.

It takes two arguments (the first is a string, and the second is a delimiter) and breaks up the string using the delimiter. If you have ever used a spreadsheet to open a comma-separated value document, it works much in the same way; separating text based on a given delimiter.

The delimiter for this function does have some restrictions: it can't be a number or date, but can be anything that would be parsed as a string in your system. For example, things like "," or ";" or even characters like "a" will work.

Using the Split function can be useful to parse out information that may exist in a single cell into multiple for sorting or other purposes. For example, you could split a “Name” cell into first and last names using a space as a delimiter. Or you could separate an area code from a phone number using a parenthesis delimiter.

Some things to note about the Split function:

Here are some examples:

The formula is in A1. Notice in this example, the function split the string based on the placement of the “!” and entered the 2 parts in 2 cells, A1 and B1.

The formula is still in A1. There is nothing in B1 because the delimiter (a comma) doesn't appear in the string "hello".

The formula is in A1. Here the string is split based on the placement of the semicolon, returning 5 cells of data instead of the original 1.

Here A1 is text. The formula is B1, with a function to split the phrase in A1 by spaces. B1 and C1 are the split product.

This type of format can be useful for other types of strings such as names, addresses, phone numbers, etc.

Combine text into a single cell

If instead of splitting cell data you want to combine them, you can use the concatenate function or the & operator.

Using these will bring values together into a single cell. This can be useful if you want to take information that is separate, like an address, and build it into a single cell with a mailing address label.

If you have a spreadsheet with many email addresses that you will be sending a message, you could concatenate the email address with a comma so you can easily copy and paste the list into your email message window.

To use the concatenate function, enter =CONCATENATE(value_1, value_2) into a cell, where value_1 and value_2 are text or numbers you wish to combine.

Please note: If you want to have spaces between your values, you will need to add that in as well.

For example: =CONCATENATE(firstname, “ “, lastname) would take the first value, add a space, then insert a second value. The final text would appear in the cell with the formula.

To insert a line break with the CONCATENATE function, you can use CHAR(10).

For example, to assemble a mailing address based on individual cells.

The formula in A1 is =CONCATENATE(B1, " ", C1, CHAR(10), D1, CHAR(10), E1, ", ", F1, " ", G1).

This puts B1 and C1 on the same line, then a line break, then D1 on its own line, then finally E1, F1, and G1 separated either by spaces or commas.

You can also use the & operator to the same effect, i.e. =value_1&value_2

To have multiple values, you can continue to use multiple & operators, =value_1&value_2&value_3 etc.

Manage your spreadsheet

About sheets

Sheets in a spreadsheet can help you organize and manage different sets of data in a single spreadsheet.

By default, a new spreadsheet has only one sheet. You can easily add additional sheets by clicking the plus button on the bottom right of your spreadsheet.

To rename a sheet, click on the button at the bottom-left of your screen for the particular sheet and select Rename, then enter the new name for the sheet.


To delete a sheet, click on the button of that sheet in the bottom-left of your screen and select the
Delete option. Click on this option and a window asking you to confirm your intention to delete will be opened. In this window, click "OK" to confirm, and the selected sheet will be deleted.


You can also quickly jump to one sheet in your spreadsheet by clicking the menu button in the bottom right of your spreadsheet. This will display a menu with the list of all the sheet names. Click on the desired sheet name.

Move or copy sheets

If you're working on a spreadsheet that contains more than one sheet, you can change the order of these sheets. The placement of each individual sheet, represented by a button along the bottom of the page, can be adjusted by clicking on this button. Select "Move right" or "Move left" after you've clicked on the button corresponding to the appropriate sheet. The order of your multiple sheets will be changed accordingly. Alternately, you can simply drag and drop the sheet into the order you prefer.


Clicking on these sheet buttons will also allow you to delete or rename the selected sheet.

  1. On the bottom-left side of your spreadsheet, click the tab of the sheet you'd like to copy (i.e. "Sheet1").
  2. Select Duplicate from the pop-up menu.

A duplicate sheet (which contains the exact formatting) now appears in a tab beside the original sheet; it's labeled Copy of Sheet.

Sometimes you want to have a copy of a sheet in a different spreadsheet. Creating a copy of the sheet means that the data won’t be updated, but it’s an easy way to copy data from one spreadsheet to another.

To copy a sheet to another spreadsheet in your Docs list, follow these steps:

  1. Click the down arrow next to the sheet name.
  2. From the menu, click Copy to...
  3. Choose the destination spreadsheet from the list.
  4. Click Select.

The sheet you copy will be duplicated into the destination spreadsheet and positioned as its first sheet. If there's already an existing sheet with the same name, the copied sheet will be named "Copy of sheetname," using the same logic as the current Duplicate option.

Hide sheets

Sometimes you want to hide sheets from view in a workbook. Fortunately, this is easily accomplished. You can click on a sheet tab and select “hide sheet” to remove a sheet from view.

The sheet can still be seen in the list of sheets and any editor can click on this sheet to bring this back to view. Sheets can also be accessed from the Hidden sheets option in the View menu. Learn more about sheet hiding here.

Adjust rows and columns

Drag and Drop allows you to move selected cells, rows, or columns without having to copy and paste.

To move rows or columns:

  1. Select the row or column you would like to move by clicking on its number/letter.
  2. Grab the row by pressing on the left mouse button and drag it to its new location.

To move selection of cells:

  1. Select the cell(s) you'd like to copy and move.
  2. Point your cursor to the top of the selected cell(s). The pointer turns into a hand.
  3. Drag the data to its new location, and release the mouse to drop it there.

To change row height or column width in one click, follow these instructions:

  1. Place your cursor at the outlying border, next to either the number or label, of the row or column you'd like to re-size.
  2. Adjust your cursor so that it rests on the dividing line between the row or column you'd like to change, and the next row or column. When this is done, your cursor will turn into a double-sided arrow.
  3. Click your mouse and drag the arrow in the direction you'd like your row or column to expand or contract.

You can also select multiple rows and columns and the changes will affect each that was selected.

Another way to resize rows or columns is using the pull down menu that appears next to the row or column name or right-clicking somewhere in the selection. Follow these instructions:

  1. Make a selection of rows or columns by clicking on the row or column label (ex: Column A or Row 12) and dragging your cursor to where you'd like to end the selection. Alternatively, you can click on a row or column label and use the Shift + arrow keys to complete the selection.
  1. Using your mouse, right-click anywhere in the selection and select Resize columns... or Resize rows... from the menu. You can also access this menu by selecting the pull down menu that appears next to row and column names. Accessing the menu of one of the columns/rows will affect all selected columns/rows.
  2. Enter a new size in the Input box, and click OK.

Show, hide, and freeze rows and columns

Sometimes you have data in rows and columns that is needed for formulas or charts, but is not necessary to see in the sheet. You can choose to hide one or more of these rows/columns from view in spreadsheets.

Freezing rows and columns makes it so that the frozen areas remain constant for sorting. They also stay in place while scrolling left or right and can be useful if you have a large sheet spanning many rows and columns and want to see the identifying names of the rows/columns.

Rows

To hide a row, right-click the number of the row to highlight the entire row. Select Hide row from the drop-down menu.

An icon appears over the hidden row, and the row's number is no longer visible.

To unhide a row, just click the icon and the row reappears on the spreadsheet.

There are two methods to freeze rows in a spreadsheet:

Columns

To hide a column, right-click the letter of the column to highlight the entire column. Select Hide column from the drop-down menu. You can also click the menu available in the top of the column next to the letter name.


An icon appears, showing the letter of the column that's been hidden. Note: If you have two or more adjacent columns hidden, the icon will show the range of letters, such as B-E.

To unhide a column, click the letter icon and the column reappears.

There are also two methods to freeze columns in a spreadsheet:

Multiple rows or columns

To hide multiple, adjacent rows or columns all at once, follow these steps:

  1. Click the row number or column letter of the first row/column you want to hide. The row/column is highlighted.
  2. Press and hold Shift on your keyboard while clicking the last row number or column letter you want to hide.
  3. All rows or columns in the range are highlighted.
  4. Right-click anywhere in the highlighted range, and choose Hide rows from the drop-down menu.

To unhide several hidden rows or columns all at once, just highlight the range and select Unhide rows/columns. For example, if you have columns C and H hidden, you can select columns B-I, right-click anywhere in the selected range, and choose Unhide columns. All hidden columns in the range will reappear.

Merge cells horizontally or vertically

To merge cells horizontally or vertically in a spreadsheet, select the cells you'd like to merge and click the Merge icon in the toolbar.

From the Merge drop-down menu, select from the following options:

If you'd like to unmerge a selected range of cells, select the merged cell(s) and click the Merge icon in toolbar.

Spreadsheet formatting and rules

You can format data in your spreadsheets in a variety of ways. You'll find the standard formatting options on the Edit toolbar.

In your spreadsheet, just point your cursor to an icon on the toolbar to see a message describing what that option can do.

Formatting is performed on only a complete cell. It is not possible to format only parts of a cell (for example, bolding one word in the cell).

Here are the main formatting options:

In addition to general formatting, you can also set specific rules for formatting cells based on values. By setting text and background colors with rules, you can create styles that offer a visual summary of complex or changing data.

To setup formatting rules for your spreadsheet, follow these steps:

  1. Highlight the cell or range of cells that you want to apply formatting rules to.
  2. From the toolbar, select the background-color palette button (shown below), or right-click the cell or selected range of cells.
  3. In the palette drop-down menu or the right-click menu, select Conditional formatting...
  4. In the window that appears, define the conditions for specific text colors and/or cell background colors.
  1. The rules shown above indicate that cells containing "x" will be formatted as cells with a red background. This is an attendance sheet and will help visually identify the truancies.

You can choose from multiple rules and select up to five for a given cell or range. To remove a rule, just click the "X" to the right of that rule.

Please note:

Copy cell formatting with format painter

You can apply formatting or conditional formatting rules you've used on one set of cells to another separate set of cells using the Paint Format tool, where "paint" is used figuratively to describe copying the formatting rules.

The types of formats that you can paint are style, fonts, number formats and conditional formatting.

Please note: If the range of cells you're painting the formatting across is larger than the source area, the formatting will tile across the target area.

To paint formatting from one area across another area, follow these steps:

  1. Select the range of cells that contain the formatting and/or conditional formatting rules you'd like to paint across another range of cells.
  2. Click the Paintbrush toolbar icon.
  1. Select the range of cells you'd like the formatting to be copied across. The cell formatting will then be applied to the range of cells you've selected.

List view for simple spreadsheet display

If you are using a spreadsheet for basic sorting and editing, you may want to try the spreadsheet List view.

List view is a simple, fast-loading view of a spreadsheet, with basic editing, sorting, and filtering capabilities.

Having a list available for others to edit allows more structure than an empty sheet. It can be helpful if you’re collecting basic data.

Here's what you can do with list view:

When you're on a slow Internet connection, you'll see a prompt to use list view. If you're trying to access a spreadsheet with more than 50 users currently viewing it, you'll be automatically redirected to list view.

To access list view from your desktop computer, go to the View menu in your spreadsheet and select List view.

If your phone has a Webkit-based browser (iPhone, Android, Nokia S60, Palm Pre), Opera Mobile, or Opera Mini, you'll be able to access list view and edit your spreadsheets from your mobile phone. Just go to your mobile Docs list at http://docs.google.com/m.

You can bookmark or share any list view URL for quick access later. All editing and viewing permissions set in the Share menu apply to list view just as they do to normal spreadsheet view.

List view provides a few basic ways to manipulate data:

In list view you can't select cells, rename sheets, format data, use auto-fill, edit formulas, add comments, add new collaborators and viewers, or insert images, charts and gadgets. But you can always switch back to normal spreadsheet view by clicking the Go to spreadsheet view link.

Check spelling

Correct spelling is an essential part of document creation. Fortunately, Spreadsheets has the ability to check the spelling throughout a document so your data will look perfect!

To launch the spell checker, go to Tools > Spelling. The tool will go through all the cells on your sheet, highlighting each cell which has a misspelled word in red. Words that might be misspelled in each cell are underlined in red and can be changed by clicking on them and selecting the right spelling.


Cell data validation

Data validation makes it easy to constrain the values of an individual cell to a specific range or list. For example, if you are building a spreadsheet gradebook, you can now limit the options in the grade column to a select set of values via dropdown lists.

In-cell dropdowns also make input easier by reducing unnecessary typing and errors in processing. You can create dropdown lists in individual cells through the data validation tool by validating against a range of cells in your sheet or by creating a custom list.

Follow these steps to create an in-cell dropdown list through validation against a range.

  1. Enter data into a range of cells.
  1. Select the cell(s) you would like to validate.
  2. Under the Data menu, select Data Validation...
  3. Change the Criteria to ‘Items from a list.’
  4. Click the button next to the ‘Create list from range’ option and select the range of cells you entered data in during Step 1.
  5. Click Save and the cell you chose to validate will have a dropdown arrow in it with the data in your cell range as the potential input values. If you want, you can set a cell to allow invalid data.

You can also create an in-cell dropdown using a custom list.

  1. Under the Data menu, select Data Validation...
  2. Change the Criteria to ‘Items from a list.’
  3. Click the button next to the 'Enter list items' option.
  4. Enter a set of custom values, separated by commas. For example, you could enter "A, A-, B+, B, B-"
  5. Click Save and the cell you selected will have a dropdown list with the values you entered as potential input values

Analyze spreadsheet data

Basic sorting

One of the primary benefits to entering data in spreadsheets is the ability to quickly and easily sort information. Spreadsheets in Google Docs has a couple of options for performing basic sorting on your data, by either using  the commands available from the View and Data tabs

With the View tab, you can do the following:

Filter data

Collecting data is often much easier than analyzing or understanding it, especially for large data sets. The Filter feature in Google spreadsheets makes it easier to analyze and view your data by hiding the data that you temporarily want to take out of view. When you want all of your data to be visible again, all you have to do is disable the filter.

Please note: When you enable the Filter feature, it will filter the data for all of your spreadsheet’s viewers and collaborators. Any editor has the ability to disable the filter.

Enabling and disabling a filter

To enable autofilter, follow these steps:

  1. Select the range of cells you’d like to apply the filter to.
  2. From the Data menu, select Filter. Alternatively, you can select the Filter toolbar icon
  3. The filter will be applied to your selected range of cells.
  4. To help you see what cell ranges have a filter applied, the column and row labels are colored green. The filter icon will also turn to a bold black.

  5. Clicking the drop down icon
  6. From the drop-down menu, you can uncheck the data points that you want to hide from view and check data points that you want to keep in view. Once you’ve done so and clicked OK, the icon in the header row will change to a filter.

Note: Only one filter is allowed per spreadsheet in order to prevent collaborators from overwriting each other’s data. The filter will show for all editors and viewers, and it can be added or removed by any person with editing rights.

To disable autofilter, follow these steps:

  1. From the Data menu, select Filter. Alternatively, you can deselect the Filter icon
  2. The filter will be removed from your selected range of cells. Drop-down menus
  3. All filtered rows will be visible again, but data you’ve sorted will remain sorted.

Filtering your data

Filtering will temporarily hide some of the rows in your spreadsheet, allowing you to view only the data you’d like to see. Once you’ve enabled the filter, click the drop down icon

If you want to complete a bulk action, you can click Select all or Clear to either check or uncheck all of your data points in a given column.

You also have the ability to search for particular data points within a column that has a filter applied. Typing "P," for example, will shorten the list to just the names that start with P. You can click on Select all or Clear to perform bulk actions on the visible items. For example, clicking Clear would deselect Cindy, Mary & Mike.

Sorting data with Filter enabled

When you have a filter applied to a range of cells, you can also sort the data. When you sort your data using the drop-down menu, only the data contained in the filter range will be sorted. You can sort a particular column by selecting the drop-down menu and clicking either Sort A → Z or Sort Z → A.

Advanced sorting

If you wish to sort your spreadsheet by different columns in varying priority, you can take advantage of the advanced sorting options. You can sort a range of cells according to rules set for one or more columns.


For example, you might want to have your gradebook sorted by Class period, then by grade, then by last name. You could set up each of these rules by following the steps below.

To sort your data, follow these steps:

  1. Highlight the group of cells that you'd like to sort. To sort the entire sheet, click the top left-hand corner of the sheet to select all cells.
  2. From the Data menu, select Sort range...
  3. Tick the Data has header row if your columns have titles.
  4. Select the column that you'd like to be sorted first and whether you would like that column sorted in ascending or descending order.
  1. Click Sort and your range will be sorted.

About charts

Charts are an easy way to understand the data in your spreadsheet and can also be shared in a variety of ways. You can insert a chart onto a sheet, create a chart on a separate sheet, or save a chart as an image and insert it into a document.

With Google Docs, you have a wide variety of charts to choose from:

A list of all the different chart types and how to create them are located at this link.

Review the next section for more details about using charts in spreadsheets »

Use gadgets to visualize data

Gadgets are another way to visualize data in your spreadsheet. Although you can insert charts and gadgets in the same way, gadgets offer even more possibilities.

Both:

Update as you update data

• Can be saved as a png

• Can be published

• Can be seen by collaborators

Gadgets:

• Can be made by users

• Can be inserted into iGoogle pages

Charts:

• Generally used within a spreadsheet.

Some examples of education-friendly gadgets you can add to a spreadsheet:

To add these gadgets, or browse other gadgets you can use in your spreadsheet, follow these steps:

  1. Select Insert > Gadget. The Add a Gadget window appears, where you can select gadgets in a number of categories, including charts, tables, and maps.
  2. Click Add to your spreadsheet to insert a gadget.

When you click a gadget in your spreadsheet, a gray border appears around the gadget, along with a Gadget drop-down menu. Click Gadget to open the menu and select Edit, Delete, Publish, or Add to iGoogle.

If you prefer, you can have a gadget move to its own sheet instead. Here's how to do it:

  1. Click the grey triangle in the upper-right corner of your gadget to open a drop-down menu.
  2. Select Move to own sheet from the menu.
  3. Your gadget will now become a sheet in a spreadsheet.

Creating and editing pivot table reports

Overview of pivot table reports

A pivot table report is a dynamic table that lets you interpret data in different ways without ever having to enter a formula. Pivot table reports are particularly useful when you want to narrow down a large data set or analyze relationships between data points.

In just a few clicks, you can summarize and analyze a large set of data through different categorizations and calculations. For example, from a list of students in a college class, you can get a quick understanding of the the age distribution of and number of science majors in the class.

Here are some tips to keep in mind when you're building a pivot table report:




          Watch a video on pivot table in Google Docs

How to create a pivot table report

To create a pivot table report from data in your spreadsheet, follow these steps:

  1. Open the spreadsheet that contains the data you'd like to use for your pivot table. If you don't have a data set to use, you can practice using the data in the pivot table report template and instructions in the "Creating a sample pivot table report" section of this guide.
  2. From the Data menu, select Pivot table report.
  3. A new sheet named "Pivot Table 1" opens in your spreadsheet with the Report Editor open to the right. This sheet contains an empty pivot table report.
  4. In the Report Editor, select the fields you'd like to appear in the Rows, Columns, Values, or Filter categories of your pivot table report.
  1. To change how data is arranged in the table, you can drag fields to a different category. Click the X in the top right of a field to remove it from your pivot table report.

Pivot table reports have built-in settings to make it easier for you to analyze your data and to prevent you from breaking the pivot table report:

If you've created a pivot table in Microsoft Excel in the past, you can upload that spreadsheet to Google spreadsheets. This allows you to continue to analyze your data using Google Docs no matter where you're signed in. To upload a Microsoft Excel spreadsheet that contains a pivot table, click the File menu, and select Import... Select from the import options, and click the Import button.

Navigate the report editor

When you create a pivot table report, the Report Editor automatically appears on the right side of the sheet that includes the pivot table report. Read this section to learn how to use the Report Editor.

Opening and closing the Report Editor

To close the Report Editor, click outside of the borders of the pivot table report, or click the X in the top right of the Report Editor.

To open the Report Editor and edit the pivot table report, click any of the cells within the borders of the table.

Updating pivot table report data

Changes made in the Report Editor can show in the pivot table report in real-time or when you manually update the table. Set your preference at the bottom of the Report Editor by selecting from the following options:


If your pivot table report is set to update manually, click the
Update table button at the bottom of the Report Editor to update the pivot table report. If your pivot table report is set to update automatically, this option will be grayed out.

Categories and fields

Categories

The Report Editor contains several categories. Add fields to the following categories to build a pivot table report.

You can't add the same field to multiple categories of your pivot table report, as it would create duplicate calculations in the table.

Fields

A field is a subset of your data contained in a spreadsheet column. The column header is the name of the field. Adding fields to categories in the Report Editor will populate data into your pivot table report. To add a field, follow these steps:

  1. Click the Add field link next to the name of each category to see a menu of all available fields.
  2. Select the field that you'd like to insert into the pivot table report.
  1. Repeat steps 1 and 2 for each additional field.

Note: The field shown at the top of each category will be the first field of data presented in the pivot table report. Any additional fields added to the category will be broken down within this primary field.

Sort, summarize, and filter data

After you've entered fields into your pivot table report, you can sort, summarize, filter, or add formulas to the data contained in your pivot table report. Expand one or more of the menus below to learn about these data analysis options.

Sorting data

Fields in the Rows and Columns categories can be sorted in ascending (A-Z) or descending (Z-A) order.

To change how data is sorted in your pivot table report, click the drop-down menu at the bottom of a field in the Rows or Columns category. Then, select how you'd like to sort your data.

Summarizing data

Fields in the Values category can be sorted by functions like number, sum, and average.

To change how data is sorted in your pivot table report, click the drop-down menu at the bottom of the a field in the Values category. Then, select how you'd like to sort your data.

If the Values category contains more than one field, data can be displayed in either rows or columns. Switch between row and column display by clicking the as: Columns drop-down menu at the top of the Values category.

Filtering data

Adding a field to the Filter category lets you hide data points that you don't want to show in your pivot table report.

To filter data in your pivot table report, click the drop-down menu at the bottom of a field in the Filter category. Then, use the checkboxes in the drop-down menu to select the data points you want to show or hide in your pivot table report.

Using formulas

You can analyze your data even more by entering formulas outside of the borders of the pivot data report. The formula will treat pivot table values as regular data cells, and can be dragged to expand across a row.

If you make changes to the source data spreadsheet after adding a formula, the pivot table report and formula results will update with new values.

Format and edit a pivot table report

After you build your pivot table report, you can go back at any time to format or edit your pivot table report. Expand one or more of the options below to learn how to edit the data range for your pivot table report, get an overview of formatting options, or find instructions about how to copy and paste a pivot table report.

Editing the data range

The fields listed in the Report Editor depend on the data range selected for your pivot table. Each column header in the selected data range will appear as a field in the Report Editor. To change the data range, follow these steps:

  1. Click the edit range link the top of the Report Editor.
  2. In the dialog that appears, type a new data range, or click the tab with the full data set and manually select a new range of cells.

Cell borders, colors, and fonts

Pivot table reports have the same formatting options as the rest of your spreadsheet. Here are some of the things you can do:

Be sure to make all formatting changes after you're done adding and sorting fields. Formatting changes, including conditional formatting, won't be applied to new cells in your pivot table report.

You can't change row and column headers in your report, as they are populated from the fields you've added to the Row and Column categories. To make a change, go to your source spreadsheet and adjust the column header. Doing so will change the titles of the corresponding fields in your pivot table report.

Copying and pasting

To paste your pivot table report into another spreadsheet or sheet tab, follow these steps:

  1. Select the cells you want to copy from your pivot table report.
  2. In the spreadsheet or sheet where you'd like to paste your report, select the top-left cell to paste these values.
  3. From the Edit menu, select Paste values only.

To paste your pivot table report into a Google document or presentation, follow these steps:

  1. Select the cells you want to copy from your pivot table report.
  2. Click the web clipboard icon in the toolbar.
  3. Select the Copy cell range (N rows x N columns) option.
  4. In the text document or presentation where you'd like to paste the report, click the web clipboard icon.

Create a sample pivot table report

For this sample pivot table report, put yourself in the shoes of a university physics professor. The data set in the template shows the names, class levels, and other details about each student in the class.

First, click the link to the pivot table report template, and press the Use this template button. This will create a personal copy of the spreadsheet for you, and it will appear in your Documents List.

Let's start building the sample pivot table report. In building this pivot table report, you want to get a better understanding of your students and their majors in order to build a curriculum for the semester. First, let's see how many students are Freshmen, Sophomores, Juniors, or Seniors.

  1. From the Data menu, select Pivot table report.
  2. A new tab named "Pivot table 1" will open.
  3. In the Report Editor to the right of your spreadsheet, check that the detected data range is correct. It should read 'Class Data'!A1:F31.
  4. In the Rows category of the Report Editor, click the Add field link and select Major.
  5. In the Columns category of the Report Editor, click the Add field link and select Class level.
  6. In the Values category of the Report Editor, click the Add field link and select Major.
  7. In this field, click the drop-down arrow next to Summarize by: and select COUNTA.

Note that you have more Freshmen students in your class than any class level.

Next, you want to understand how many of your students are taking your class as a required course for their Physics major. You want to see this information within class levels.

  1. In the Filter category of the Report Editor, click the Add field link and select Major.
  2. In the Major field, click the drop-down arrow next to Show: 0 items and select Science.

The pivot table report shows that you have 7 Physics majors. Of the 7 Physics majors, 3 students are Freshmen, but you also have two Sophomores, one Junior, and one Senior.

Click the X in the top right of the Filter: Major field to return to the full view of your pivot table report.

Now, you want to format your pivot table to share it with other professors in your department. This will make it easy for your colleagues to understand the data at a glance. Here's how to format your data:

  1. Change the background colors of the rows and columns using the text background color menu in the toolbar.
  1. Highlight the grand total of Physics majors in cell F5 by changing the background color to orange.
  2. Center all other values in your pivot table. Highlight cells B2 to F6 and click the Center alignment icon in the toolbar.

Your pivot table is ready to share with your colleagues! Press the Share button in the upper right of your spreadsheet and enter their email addresses.

Create and publish charts

Create charts from spreadsheet data

Charts are a convenient way to analyze spreadsheet data visually and come built-in to Google Docs. There are 8 standard types of charts to choose from: line, area, column, bar, scatter, pie, map, and trend. In addition, there are even more options to choose from under More in the Chart Editor.

Data displayed in charts is selected from the spreadsheet and updated whenever the spreadsheet is modified. This way your chart always has the most accurate information.

To create a chart in your spreadsheet, follow these steps:

  1. From the Edit page of your spreadsheet, select the cells of data you'd like to include in the chart.

    Please note: It helps to label the data in your spreadsheet before creating a chart. For example, if you want to chart your expenses, you might have a row of numbers labeled 'Rent' and another labeled 'Groceries.' Then you might label columns by month or week, etc. These labels will appear automatically in the window where you create and preview your chart.
  2. Go to the Insert menu and select Chart. The 'Chart Editor window appears.
    In the
    Data section, make sure the range of data is correct and specify if you want to switch rows/columns. You can even select multiple ranges.

    Note: If you included labels in your spreadsheet, you can specify that you want to use the second row and first column as labels.
  3. Under the 'Charts' tab, select the type of chart you'd like to create: line, area, column, bars, scatter, etc.
  4. Enter labels for your chart in the following fields: Chart title (title of your chart), Layout of the legend, Horizontal (what the horizontal axis represents), Vertical (what the vertical axis represents). Here, you can also specify the Features and Style of various chart components.
  5. Preview your chart and edit as necessary.
  6. Click Insert. The chart appears in your spreadsheet.

Chart multiple ranges

In charts, you are also able to graph multiple ranges. Take this chart depicting time vs distanced traveled.

You can add extra ranges by clicking on “Select ranges...” and “add another range.” You can also manually add new ranges separated by commas like “Charts!B5:B12, Charts!D5:D12, Charts!F5:F12.”

In this example, here is what the chart with the above data selected looks like.

Edit chart data settings

After you create a chart, you can always go back and change all of the settings - the data selection, chart type, names, etc.

Please be aware that any user added as a collaborator will be able to edit, add, and delete charts.

Once you've created a chart in your spreadsheet, you can modify it by following these steps:

  1. Click anywhere in the chart.
  2. A Chart drop-down menu appears in a gray border around the chart.
  3. Select Chart > Edit chart.
  4. The "Edit chart" window appears. Here, you can modify your chart's type, labels (including the chart title), and data range(s).
  5. Click Update to apply your changes.
  6. The modified chart appears in your spreadsheet.

To resize your chart, please follow these steps:

  1. Place your cursor at the corner of your chart. You'll know it's positioned correctly when it becomes a diagonal, two-way arrow.
  2. While pressing Crtl+Shift, click and drag the chart's corner to make the chart larger or smaller.

Please note: If you don't press Ctrl+Shift while resizing, the chart will not scale correctly to the new size.

Customizing charts

Each chart type has a unique set of customization options that allow you to build a chart that meets your needs. Options available in the Customize tab of a chart change depending on the chart type.

Once you've entered data into your spreadsheets and selected the range of cells you'd like to display in your chart, go to the Insert menu and select Chart. In an already existing chart, click your chart, and select Edit from the chart menu. Click the Customize tab of the charts dialog box, where you'll be able to scroll through and set advanced options for your chart.

Data and text customization options

The following options are available in most chart types:

Charts with axes have the following customization options:

Customize individual data series

You can also customize each data series separately. Scroll to the last section in the Customize tab to change the color, line thickness or point size for your data series. Click here to read about customizing slices in a pie chart.

Chart customization options

Customize your entire chart in the following ways. Specific chart types may not support all of these features or show them in the Customize tab.

If you're creating a chart from multiple data ranges, the Combine ranges horizontally/vertically setting will be automatically enabled. This setting tiles your ranges together. For example, if you created a chart from Columns A, C and E from a large data set, this setting would place all of those columns next to each other without gaps.

Copy and paste charts

You can use the web clipboard to copy and paste charts from a spreadsheet into a document or drawing. Here's how:

  1. Click the chart name in the upper left of the chart to open the drop-down menu of actions.
  2. Select Copy chart.
  3. From your Documents List, open the document or drawing where you’d like to insert the chart, and click where you’d like the chart to be inserted.
  4. Click the web clipboard icon in the toolbar , and select your chart from the drop-down menu.
  5. Your chart will be pasted into the document or drawing as an image, which you can resize and realign.

Note: You can't paste charts into another spreadsheet or into a presentation.

Publish and export charts

You can export any chart and save it as a .png file. You can then upload this file to any document or presentation in Google Docs, email it to others, or save it for other purposes.

To save your chart as a .png, please follow these steps:

  1. Click the Chart drop-down menu in the top-left corner.
  2. Select Save image.
  3. In the window that appears, select Save to disk and click OK.
  4. Your chart will be saved to your local computer.

Instead of exporting and saving a chart to your hard drive, you can also publish a chart to make it viewable like a webpage. Here are two methods you can use to publish a chart:

To publish a chart by copying and pasting its HTML, follow these steps:

  1. Publish the sheet that contains your chart by clicking Share > Publish as a web page.
    Review how to publish documents »
  2. In your spreadsheet, click the chart to activate the Chart drop-down menu at the upper-right of the chart's frame.
  3. Select Chart > Publish chart, or right-click the chart and select Publish chart.
  4. A dialog box appears, asking if you want to let anyone at your domain see the spreadsheet. This step is required to access the code for the image; if you don't want others to view your spreadsheet, you can simply not provide them with the URL. If you do share the URL, please note that anyone accessing your published spreadsheet will see its content along with the chart.
  5. Click OK in the dialog box.
  6. In the window that appears, copy the HTML code and paste it into the code of your page or blog.
  7. Click Done to close the window.

To publish a chart's sheet and include that link in your webpage or blog, follow the steps below. You can even remove the gridlines so they won't appear when others view the published or printed sheet.

  1. Click the File dropdown menu and select Publish to the Web....
  2. A dialog box will open. Click Start publishing.
  3. At the bottom of the dialog box, you’ll now see a link to your published spreadsheet.
  4. Click the link and type "&gridlines=false" at the end of the URL.
  5. Copy and paste this link in the HTML of your webpage or blog. When viewers click the link on your page, they'll see your published chart.