Sheets

Overview

Google Sheets is an online program that allows users to create complex spreadsheets to graphically display large amounts of data in the form of schedules or graphs and which is integrated with the Google Drive app as the central storage system. Like all Google applications, the easiest way to open the App is through the Google home page (www.google.com). In the top right hand corner, there will be an icon that I refer to as the Google waffle because it has three rows of three cubes resembling a waffle. This is a quick way to access any native Google app. It does not work for apps that are not created by Google.

Opening Google Sheets

Clicking on the Google waffle, opens up a list of Google Apps that are available on your user account. If the app that you are looking for does not show up, it is possible that it is on a secondary page. Click on More at the bottom of the selection. Additional apps will display.

It is possible to rearrange the order of the Apps in a fashion that is more convenient for your usage. Simply drag the icon (click and hold on it with your mouse/trackpad) and move it to the desired spot. It is possible to move apps from the lower section to the upper section.

Open Google Sheets by clicking on the icon. Another way to navigate to Google Sheets is by typing the website in directly (sheets.google.com). It is also possible to bookmark the Sheets homepage as a shortcut.

Google Sheets Start Screen

The Sheets start screen will resemble the photo above (Google has modified this visually recently). On the left hand corner, there are three horizontal bars. Clicking this will allow you to quickly navigate to other Google Apps such as sheets, slides and forms. Next to that is the Sheets search bar which allows you to quickly find a previously created document simply by typing in the name or a keyword that might be contained in the document. On the right hand side of the screen is the Google Waffle similar to the Google home page. Two icons over from that is your Google Account avatar identifying the account that is being used. Below that are a variety of templates that can be used to create documents for various purposes. Clicking on the up and down arrows next to Template Gallery opens up additional templates. To create a blank document from scratch, click on the Blank document that has the '+' symbol on it. Below this section are a list of recent documents that have been opened (not shown). This list can be further filtered by owner or by action last performed and files can be portrayed in a list or a grid view.

Start by opening a new document.

Sheets Toolbar

The Google Sheets app features both a menu bar as well as a toolbar. Below is a description of the various tools in the toolbar. The items in the menu bar will be covered in a later section. The tool bar is meant to allow access to frequently used tools whereas the menu bar is meant for more complex tasks.

Above the tool bar is the Chat button. This only appears if the document is being accessed by someone else. In this example, the avatar of the user that is online is shown. Clicking the Chat button opens up a Chat Box in which participants can type notes to each other. These notes do not affect the slide presentation but serve as a way to communicate to one another. Next to the chat button is the Present button to start the presentation. Clicking the pull down menu allows you to select presentation options: present with notes, present from the beginning or present on another screen. Next to that are two important features of all Google apps - the Comments button and the Share button. The comments button is typically used as a collaborative tool when there are multiple authors of a document. It allows a user to communicate an idea to other collaborators located in a different location when they are not online at the same time. The share button is an easy way to invite others to collaborate on a document. Go to the Share section to learn more about how to use this feature that is common to all Google apps.

To find out what a particular tool does, you can always hover the mouse cursor over the icon - after a short period of time, a ToolTip will appear with the name of the tool. From left to right, the tools are: Print, Undo, Redo, Paint format, Zoom, Currency, Percent, Decrease decimal places, Increase decimal places, More number formats, Font, Format Size, Bold, Italics, Strikethrough, Text colour, Fill colour, Borders, Merge cells, Horizontal align, Vertical align, Text wrapping, Text rotation, Insert link, Insert comment, Insert chart, Filter, and Functions. Additional clarification on some of the tools is listed as follows:

Paint format: This tool allows the user to quickly copy the formatting of one area of the document to one or more areas. To use it, highlight the part that contains the formatting that is to be applied to other areas. With the areas highlighted still, click the icon and then highlight the areas that the formatting will be applied to. Double clicking the icon will lock the paint format feature and every text subsequently clicked will have the new formatting applied to it.

Zoom: Select the level of zoom for the document. The default is set at 100% but can be set all the way up to 200%. Alternatively, the zoom level can be specified manually by typing the number in the box, as long as it lies in between the minimum of 50% and the maximum of 200%.

More formats: Clicking the pull down menu will reveal additional formatting options for the cell which includes plain text, number, percent, scientific, accounting, financial, currency, currency rounded, date, time, date time, duration and even more formats.

Font: Clicking the pull down menu reveals additional font styles that are available including the more fonts choice at the bottom of the selection which brings up an insane amount of fonts.

Font size: Clicking the pull down menu reveals additional font sizes. Alternatively, type in the font size manually by entering a digit in the box.

Text colour: It is possible to change the text in any part of the document to a specific colour for distinction or for aesthetics. On the bottom of the colour palette is the option of conditional formatting which means that the user can specify rules for formatting specific cells. For example, this is a quick method of colouring cells that have no data, cells that have a specific value or a values within a given parameter. Rather than having to manually comb through large amounts of data, conditional formatting is a useful tool to identify and change the text colour or text format based on given criteria.

Fill colour: Closely related to text colour, fill colour is highlighting the cell in a specific colour to identify cells that differ from other cells for example. Fill colour can be applied to singular cells or to several cells by selecting multiple cells. Conditional formatting can also be applied to fill colour. In addition, there is the option of selecting alternating fill colour. This is useful in a large spreadsheet to easily distinguish between one row or column and the adjacent ones. When using alternating fill colour, the user can select the two colours and then apply it to a cell selection. Click on the Remove alternating colours button at the bottom of the screen to return to regular formatting.

The Borders tool adds border to one or more cells. If no borders are added, the spreadsheet will appear without grid lines. Using borders is an easy way to show separation of data. Borders can be added above, below, in the middle of a group of cells, around the outside of a group of cells, to the right or left of a cell or multiple cells. To remove borders, select the group of cells and choose the no borders option inside the tool. Finally, borders can be customized to reflect different line styles and/or colours by selecting the two icons on the far right of the border tool.

The Merge tool combines (or unmerges) two or more cells. Multiple cells that contain data will retain only the top-leftmost value. Similiarly, merging cells that have different formatting will remove the formatting for all cells except for the top-leftmost value. Cells can be merged horizontally or vertically.

The next section addresses how text is positioned within cells.

The first icon represents justification and positions text horizontally to the left, centered or to the right.

The second icon represents vertical alignment and positions text to the top, middle or the bottom.

Finally, the last icon represents text wrapping and determines how text that is longer than what will fit in the cell will be displayed. The first option if overflow where text will be displayed on the adjacent cell only if it is blank otherwise it will appear cut off. Text Wrapping is where the entire body of text is made to fit within the width of the cell but the cell height will automatically be increased to accommodate the additional text. Clip is where the text is kept within the confines of the cell even if the text is longer than the cell width. In this case, only part of the text will appear.

Google Sheets is able to rotate text in a variety of directions. Simply select the cell or range of cells and select the direction desired within the pull down menu. Note that the cell dimensions may need to be altered for the changes to appear correctly.

Filter is a tool that allows viewing only specific amounts of data based on criteria that is provided. Here are some ways to filter:

Filter by condition: Choose from a list of conditions or write your own. For example, if the cell is empty, if data is less than a certain number, or if the text contains a certain letter or phrase.
Filter by values: Uncheck any data points that you want to hide and click OK. If you want to choose all data points, click Select all. You can also uncheck all data points, by clicking Clear.

One of the primary uses of Sheets is to compute large amounts of data. When used in this manner, the Formula tool is an easy way to select commonly used calculations such as sum, average, count, and so forth. Select the range of cells to apply the formula to and select the function to be applied. The result will be tabulated in the adjacent cell. Alternatively, select the cell below or next to a range of cells and select the function. Sheets will prompt the user to select which cells are to be included in the calculation. Select the cells by dragging across a range (sequential cells), or select multiple non-sequential cells by clicking each cell while keeping the control (command on a Mac) depressed.

Sheets Menu Bar

The menu bar sits above the tool bar and consists of eight different menus that when clicked will open up further actions. A 9th item is the help menu which allows users to find solutions to different issues or to quickly search for a feature. Instead of needing to know which menu bar contains the feature, simply type in the name of the feature in the menu search bar (under help). The ten menus are File, Edit, View, Insert, Format, Data, Tools and Add-Ons. A more thorough description of each menu is listed below.

File Menu

The first item in the Menu Bar is the File menu. All of the items in this section relate to document access through the Google Drive.

The first command is Share which allows multiple users to collaborate on a document either asynchronously or synchronously. If users are synchronously editing a document, you will see their account name appear at the top of the screen in a certain colour. You may also see a coloured cursor that corresponds to their account name appear somewhere in the document and text may appear. This is because Google Sheets constantly updates itself and therefore it truly is a collaborative experience even though users may not be in the same location. See Sharing section to learn more.

The next set of commands relate to accessing a document and allow you to create a new file (the submenu allows the user to open up a new document, spreadsheet, presentation, form or drawing in a new browser tab), open an existing document, importing a spreadsheet from a different format such as Microsoft Excel or making a copy of an existing spreadsheet.

The next section relates to exporting options and provides the ability to download the file (in a variety of different file formats including an Excel Spreadsheet (.xlsx), an OpenDocument format (.ods), PDF, plain text (.txt), a web page (.html), comma separated values or tab separated values.), emailing the file as an attachment or previewing all of the different versions of the document regardless of whether they were made by you or by a collaborator. Users are able to create a Version History by naming it. This allows them to see all of the changes that have been made in the document's lifetime and possibly restore changes that were made and would otherwise have been lost.

The next section relates to making the document accessible to others which includes publishing to the web or inviting collaborators to work on the document.

The final section is a collection of general document tools such as displaying basic information about the spreadsheet, changing the settings and printing the document.

Edit Menu

The next section in the Menu Bar are the Editing commands. In this section, one finds all of the basic commands used regularly in editing a document such as undo, redo, cut, copy, and paste.

Paste special refers to retaining specific characteristics of a cell or a range of cells. For example, it is possible to paste just the value, formatting only, paste without border, column widths, formula only, data validation only, conditional formatting only, or transposed.

Find and replace is a quick tool to automatically replace values or text within a spreadsheet.

Finally, the edit menu offers the choice to delete a range of information including the value, the row, the column, the cell (specify how the remaining data will be shifted to fill in the void). If checkboxes have been created, the last option removes those checkboxes.

View Menu

The view menu provides options relating to the display of the spreadsheet.

Particularly in a large spreadsheet, it is useful to freeze portions of the screen. This is so that when scrolling either vertically or horizontally, certain frozen rows or columns will remain regardless of how far the screen is scrolled. This is useful for example in viewing titles.

The view screen also allows gridlines to be seen, to protect ranges from being overwritten, to view the formula bar, to show formulas, to view hidden sheets, or to set the zoom level.

Insert Menu

The Insert Menu inserts rows, columns or cells and determines where they will be added. The menu presents options of adding the following objects:

charts
images
drawings
forms

Finally, the insert menu is also where the commands for inserting functions, links, checkboxes , comments, notes, or new sheets can be found.

Format Menu

The format menu is concerned with all things related to formatting such as number and text formatting.

Number formatting refers to the type of value of the data such as plain text, number, percent, scientific, accounting, financial, currency, currency rounded, date, time, date time, duration and even more formats. and the document. Number formatting affects how formulas are applied.

Text formtting includes includes making text bold, italicized, underlined, or adding a strikethrough.

Formatting also refers to the alignment of a cell or range of cells that includes both horizontal and vertical placement. Cell merging combines (or unmerges) two or more cells. Multiple cells that contain data will retain only the top-leftmost value. Similiarly, merging cells that have different formatting will remove the formatting for all cells except for the top-leftmost value. Cells can be merged horizontally or vertically.

Text wrapping determines how text that is longer than what will fit in the cell will be displayed. The first option if overflow where text will be displayed on the adjacent cell only if it is blank otherwise it will appear cut off. Text Wrapping is where the entire body of text is made to fit within the width of the cell but the cell height will automatically be increased to accommodate the additional text. Clip is where the text is kept within the confines of the cell even if the text is longer than the cell width. In this case, only part of the text will appear.

Text rotation changes the direction of the text. Simply select the cell or range of cells and select the direction desired within the pull down menu. Note that the cell dimensions may need to be altered for the changes to appear correctly.

Conditional formatting allows users to specify rules for formatting specific cells. For example, this is a quick method of colouring cells that have no data, cells that have a specific value or a values within a given parameter. Rather than having to manually comb through large amounts of data, conditional formatting is a useful tool to identify and change the text colour or text format based on given criteria.

Using alternating fill colour easily distinguishes between one row or column and the adjacent ones. When using alternating fill colour, the user can select the two colours and then apply it to a cell selection.

Clear formatting removes any previously applied format.

Data Menu

The Data Menu relates to how information within a spreadsheet is handled. This includes:

sorting (changing the order of one or more columns)
Filtering to view only specific amounts of data based on criteria that is provided
Data validation restricts the data that can be entered into a cell or range of cells. It can also be used to create drop down lists for quick selection of items.
Pivot tables allow users to easily analyze large amounts of data
Randomizing a range allows reordering of data in a random fashion which may be useful for a class list as an example
naming a range (to identify what those groups of cells represent)
protecting a sheet or a range from being overwritten
splitting a range of text into columns (for example a list of names into two columns by first and last name)

Tools Menu

The Tools Menu provides additional functionality to sheets and includes the following items:

script editor: an advanced function to add custom menus, dialogues, and sidebars to Google Sheets
macro editor (record, import or edit macros which is a sequence of functions)
spelling check: checks for spelling errors
autocomplete on/off: toggles autocomplete on or off (based on information from other cells)
notification rules: sends notifications by email notifying when any changes are made or when a user submits a form
protect sheet: prevent users from changing data
Accessibility Settings: a new feature within Docs, this provides additional access to those who have visual impairments and allows text to speech functionality and braille support. There is also support for third party screen magnifier software (Mac and ChromeOS only).

Add-Ons Menu

Add-ons are additional tools that have been created by third party developers to make Google Sheets even more functional. These tools offer functionality beyond what is provided within the app. Most add-ons are free and cover a diverse range of topics. Click on Get add-ons to search the selection. Add-ons can be removed by clicking on Manage add-ons.

Once add-ons have been loaded to Google Sheets, they are listed in the menu and can be accessed by clicking on them.

Here is a list of useful Add-ons for Google Sheets.

Google Sites

Report abuse