Table of Contents
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
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:
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:
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:
There are shortcuts for the percent and US currency data type changes right in the Edit toolbar as well.
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:
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:
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.
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:
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:
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.
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:
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.
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
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:
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.
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.
To add a comment to your spreadsheet, please follow these steps:
An orange mark now appears in the top-right corner of the cell where you inserted the comment.
To edit a comment inserted in a spreadsheet, follow these steps:
The edited text now appears in the Comment box.
To delete a comment, follow these steps:
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.
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.
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:
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:
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.
Similarly, when exporting a Google Spreadsheets file into an .xls or .ods, validation rules won't be exported.
Useful functions and 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:
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 ».
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.'
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.
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.
=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:
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
Use this function to translate text from and into one of over 40 languages using Google Translate:
=GoogleTranslate("text", "source language","target language")
=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.
In addition to translating, you can also use a function to find out what languages is being used in a cell:
=DetectLanguage("Hola, ¿cómo estás?") would give "es" as a result.
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.
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
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.
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.
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:
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.
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.
Drag and Drop allows you to move selected cells, rows, or columns without having to copy and paste.
To move rows or columns:
To move selection of cells:
To change row height or column width in one click, follow these instructions:
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:
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.
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:
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:
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.
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.
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:
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.
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:
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.
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.
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.
You can also create an in-cell dropdown using a custom list.
Analyze spreadsheet data
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:
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:
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:
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.
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:
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.
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.
• Update as you update data
• Can be saved as a png
• Can be published
• Can be seen by collaborators
• Can be made by users
• Can be inserted into iGoogle pages
• 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:
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:
Creating and editing 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:
To create a pivot table report from data in your spreadsheet, follow these steps:
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.
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.
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.
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:
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.
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.
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.
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.
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.
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.
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:
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:
To paste your pivot table report into a Google document or presentation, follow these steps:
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.
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.
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:
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
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:
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.
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:
To resize your chart, please follow these steps:
Please note: If you don't press Ctrl+Shift while resizing, the chart will not scale correctly to the new size.
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.
You can use the web clipboard to copy and paste charts from a spreadsheet into a document or drawing. Here's how:
Note: You can't paste charts into another spreadsheet or into a presentation.
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:
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:
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.