Google Sheets

Table of Contents

Google Sheets is a spreadsheet program, this is basically an over glorified calculator but is capable of many more things like analyzing data and using charts to display the data. Spreadsheets use cells which run both horizontally and vertically known as rows and columns. How you use them all depends on the data that you are working with and/or preference. The real beauty of spreadsheets comes in with functions that take care of a lot of work for you for example, the sum() function can be used to add a group of cells together giving you a total in the cell that contains the formula.

The Spreadsheet Window

1. This is your documents name which can be changed by clicking on it and typing a new one. Beside that we have the star which will add to the star folder shortcut of the google drive and you can use the folder to move the document to a different location inside of the drive.
2. This is the menu which gives you a number of tools to use on your document the mains are also located on the toolbar just below. Beside the menu you’ll see the status of your file change from either “Saving” or to “All changes saved in Drive” which means your file is up to date.
3. This is a shortcut to your Google Drive.
4. The toolbar has most of the main tools used easily accessible by a single click.
5. If you’d like to leave comments on your spreadsheet for reminders to yourself or others working on the file with you, you can use this button. Comments are separate from your document and will not appear when printing the document.
6. You can easily invite others by email or send them a link to your file which will be located on the web for those that you give permission to view.
7. Spreadsheets as mentioned use cells, each individual rectangle inside of the spreadsheet is known as a cell and can contain all kinds of different values or data types. Each cell is identified by its location in the sheet using its row and column to identify it, in this case the cells address is A9. You’ll notice a little square on the bottom right of a selected cell by clicking on this square with the left mouse button and holding it down you can drag it to other cells which will copy the value or formula from the selected cells to other cells.
8. A row highlighted here is the horizontal grouping of individual cells, they are identified by the number located on the left.
9. A column highlighted here is the vertical grouping of individual cells, they are identified by the letter located at the top.
10. This is an example of a function and how cells can be used, in cell A1 we have a value of 5 which is hidden by the toolbar marker, beneath that we have a cell with the value of 56 in it and below that a cell with the value of 61 which is the total of the first two cells together. If you were to click inside this cell like we have in the image you would see the formula used to get the total in this case we are using the Sum function which we can tell is a function because of the “=” in the beginning of the cell. This function uses two arguments which is a range of values that need to be added together, the starting point here is cell A1 and an endpoint which is cell A2. Since this formula is entered in cell A3 this is where our result will be displayed. You can reference any cell by its address in the document including ones from different sheets. Inside each document you can have multiple sheets, which you can locate at the bottom left of the application like in this image. The plus sign can be used to add more Sheets to your workbook and you can use the icon beside that to navigate through all the available sheets as well as their corresponding tabs if you choose to do so. Using the down arrows on each of the tabs you’ll get a menu of options you can use to:
    1. Delete the sheet
    2. Duplicate the sheet in the same workbook
    3. Copy the sheet to… another workbook
    4. Rename the sheet to something more meaningful
    5. Change the colour of the tab
    6. Protect the sheet or a range from having changes made
    7. Hide the sheet so it’s not visible but can still contain data to be used
    8. View comments if any are present
    9. Move the sheet to the left or right in the order at the bottom

Each sheet can be used how you wish and have columns up to the letter Z by default there is a 1000 rows and the capability of adding more rows as well. Typically you use sheets to separate data that applies to the same workbook into meaningful chunks. For example the first sheet could contain a list of employees and their personal information, the next sheet could track their hours worked and the last one their payment information totals, taxes etc. How you wish to organize the workbook is up to you and whoever else will be using it, you might want all that information on just one worksheet instead provided there is enough room that is…

Data Types

Before we start talking about the tools available in Google Sheets we need to understand that we can work with multiple data types. A data type tells us what type of value we are working with inside of Sheets, normally Google Sheets will automatically detect what type you are trying to use based on the information entered but you can override its decision should it have made a mistake or should you want to, they can be broken down into three groups:

• Numerical – Obviously we can use numbers in Google Sheets but what type of number are we using can be broken down a bit further they could be a regular number, a percentage, a scientific number, financial or currency. This way the value will look the way you would like it to for example typing in a value of .12 and changing it to percent would display 12{463c70c279fb908728b910a090d44fbe4ae7aabcd875de9c1a518a8c8e2be8bd} instead.
• Text – We can also work with names and places or anything we would need to write inside of here as well like a title for a table or headings for columns of information like First Name, Last Name and Phone No. etc.
• The last data type that we can work with is dates and/or times, there are a number of different ways of displaying them or entering them into a cell. For example it could be August 01 2014,  08/01/14 or Aug 1 2014 etc. Plain numbers can be transformed into dates as well the number 1 for example would come out to December 31 1899 and you can perform math on these values as well by adding 1 to any of the formats in another cell that points to it you’ll get the next date in time. You can also use days of the week or even minutes and hours of a day in combination with dates or on their own.

Selecting Cells

When working inside a spreadsheet you’ll need to select cells in order to insert data or edit the data that already exists in it. You can do this simply by clicking on that particular cell. When wanting to select multiple cells together you have options if you are looking at selecting rows or columns you can simply clicking on the number of the row or the alphabet character of the column and this will select the entire row/column. You can also select multiple ones as well by simply holding down the left mouse button and dragging the pointer across the desired rows/columns, this works great if the rows/columns you’d like to select are beside each other but if you’d like to select ones that aren’t you can hold down the CTRL key while making your selections and this will add anything you select to the existing selection, this can be used anytime and not just with full rows/columns. You can also select multiple cells by clicking inside of one of them and then dragging the mouse to another cell further away, you’ll notice a blue box appear highlighting the cells you are about to select. If you are a keyboard person then you can use the arrow keys to move around and by holding down the shift key you’ll be able to select a range of cells that way, there are keyboard shortcuts we’ll talk about later that can help speed up navigating the spreadsheet as well.

With at least one cell selected you can right click to receive a menu with more options like Cut, Copy, Paste and Paste special which will offer you a sub-menu of choices like:

1. Pasting values only – This will paste only the values and no formatting or anything else
2. Format only – This will paste only the formatting and not the values
3. All except borders – This will paste everything except for any borders of the cells selected
4. Data validation only – This will paste only the data validation information from the selected cells
5. Conditional formatting only – Paste only the conditional formatting from the selected cells
6. Transpose – Will take the selected cells and switch the columns to rows and rows to columns in the pasted cells

When working inside of a spreadsheet it can become hard remembering which cells might consist of only sales information for example, instead of referring to it as A1-A20 which can be harder to remember if using other rows or columns. You have the option of naming these ranges instead, by highlighting the range and then right clicking and selecting “Define named range…” you’ll be able to give it a more meaningful name. Later when working with something like the Sum function instead of specifying the range you can type in a meaningful name such as Sales or whatever name you gave to your range.

Other options from the dropdown menu include protecting the range so that others can’t make changes to it but you still can, inserting a comment which is the same as the comment button discussed earlier. You can insert individual notes per cell to give more information about a particular cell for example along with clearing the notes you’ve left behind. Then there’s conditional formatting which means if the data in the cell falls under a certain criteria like greater than 5 then have it appear a certain way otherwise it’ll just have the normal formatting. Data validation makes sure that the data entered in the cell comes from another source which you would have multiple selections or that it meets a certain criteria like a number between 1-100 for example. The last option on the list is a shortcut to insert a hyperlink which already mentioned before.

You’ll also have further options available to you when selecting an entire row/column, some of which are the same as when you right click on a cell but you get a few extra ones like Insert 1 left, Insert 1 right which will insert a single column to the side specified. You can also delete the row/column, clear its contents leaving the row or just hide it so the data is there but not visible. Resizing it will allow you to specify the height/width. If you have a column selected then you’ll be presented with two options for sorting the information either ascending (a-z)/descending (z-a).

The Toolbar

We’ll look at the tools available on the tool bar from left to right:

• Print – Your first option here is to print your document, this will present you with a preview of your document along with other options on the left pane for what printer you’d like to use, what pages you’d like to print, the number of copies, color or black & white, margins and if you’d like it double sided along with if you’d like to include Background colors and images.
• The next two options are the left and right arrow which are your undo and redo options, if you made a mistake you can use the undo button to go back a step and the redo option to repeat the step you just undid. You can also the redo option to recreate the last step you just did as well.
• When applying formatting to your document like bold or colour, you might like to add the same type of formatting from one part of your document to other parts. You can use the Paint Format tool for this, by placing your cursor in the text that’s formatting you’d like to copy and pressing the button you’ll then be able to drag your cursor over other text applying the same formatting to it. By clicking on the button once you’ll be able to copy over the formatting just once but by double clicking on it you’ll be able to continue to apply the formatting to any part of your document repeatedly, if you double clicked it then you’ll need to click it again to turn it off otherwise it will turn off on its own after you pasted the formatting with the one click option.
• The next few buttons have to do with choosing a data type, the first two options allow you to specify if the value is a dollar value or a percentage, right after that we control how many decimal places are available with numerical values. The last button here has a drop down list from where you can choose from a number of different data types and formats along with further options that will give you more options for currencies or date/time and even more options for different types of numerical formats. If Automatic doesn’t seem to be working for you here you’ll need to manually specify the data type you would like to work with. Keep in mind that when working with numerical values that if you specify that the cell be a text data type that adding them together may not give the desired results.
• Moving along your next option is to choose the type of font you’d like to use for the selected cells and just after that you can specify the size you wish to use.
• Here is where you can apply formatting to your fonts with choices like Bold, Italics, Strikethrough and Font Colour.
• The next few buttons allow us to control the cell itself by selecting a background colour, if it should have a border which can broke down to full border, top, bottom, left or right. Last but not least we can merge multiple cells together, this button presents a drop down where you can merge all selected cells, merge selected cells vertically/horizontally and unmerge selected cells that have previously been merged.
• The next three buttons control data layout inside the cell by horizontal alignment (left, center, right) or vertical alignment (top, middle, bottom) and then we have the option to have our text wrap inside the cell or if we’d like it to overflow into empty cells or get cut off otherwise.
• Our next button on the toolbar allows us to specify a hyperlink to a location on the web by clicking on the button with text selected you can create a hyperlink on that particular text or without any selected type in your own text.
• While working on the document you might need to leave comments to remind you about something or leave instructions for others that are working on the file with you to read, this behaves in the same way as the comment button mentioned earlier.
• Charts are a great way of displaying data, not only is it visually more appealing but it also makes the data easier to read. There are several different types of charts that can be used inside of Google Sheets and the options include line, area, column, bar, scatter, pie, map, trend and more.
  1. The first step in creating a chart is to select the data which will represent it once you’ve clicked on the button you’ll be presented with a dialog box which will automatically take selected cells as the data range or it’ll try to guess what the range is automatically, you can override them should it be wrong by simply clicking on the box and then specifying the range or by clicking on the grid icon inside the textbox you’ll be prompted to select the range via the mouse. On this screen there are a couple more options you could use they are to switch rows/columns and use 1^st row as a header. After that you’ll typically have couple of charts that Google feels most appropriate for the data to choose from, should these not be what you want don’t worry the second tab Charts will provide you with more options.
  2. The second tab Charts as mentioned will provide you with several options to choose the type of chart you’d like to use, this depends most only the data and preference of the person putting together the spreadsheet or the person giving the instructions.
  3. The third tab Customize will depend on the type of chart you choose and will provide you with extra options on customizing the look of the particular chart you choose.

• Once you’ve inserted you’re chart you’ll have a few options available on it as well, by clicking on the eye and pencil you can switch between view and quick edit mode. Quick edit mode will allow you to change things like the title, background colour and font being used in the chart but depends on the type of chart you chose as well. Through the drop down arrow you can click on Advanced edit… which will open up the dialog box mentioned above. You’ll also be able to delete the chart, save it as an image on your computer or publish it to the internet and give users a URL for them to view. The last two options on the list include copying the chart and moving it to it’s own sheet within the workbook.
• When working with data especially with a lot of data it might become very hard to syphon through all the information. Sometimes we are looking for specific values. This is where filtering comes in, by selecting data and then pressing the filter option you’ll be able to do things like sort columns ascending/descending and of course as mentioned filter out certain data to keep or remove certain results. When creating a filter the top row will become a header row so if you don’t have one then you should create one for this to work the way you’d expect it to. Once this is done you’ll see down arrows beside each header by clicking on them you’ll receive a drop down list where you can choose sorting options from, by removing checkboxes from the different values presented you’ll remove those from the results and by vice versa by adding a check to the value. You can create different filters and save them along with turning them off here as well.
• The last option on the toolbar is to select a function, by clicking on the button you’ll be presented with a dropdown list of the most commonly used functions like:
  1. Sum – Used to add up values within the range specified
  2. Average – Used to obtain an average from the range specified
  3. Count – Will give you the number of cells in the range specified
  4. Max – Will give you the maximum value from the range specified
  5. Min – Will give you the lowest value from the range specified.

If these functions aren’t what you want then don’t fear by selecting More functions… you’ll be taken to a page where you can browse from all the available functions, just over 340 of them. They are grouped into categories to help make it easier to find the function you need. There are array, database, engineering, filter, financial, google, info, logical, lookup, math, operator, parser, statistical and text.

The Menu

A lot of the main tools available from the menu are found on the toolbar but there are still a number of extra tools available to you through it.

File

• Share – Works the same as the button described earlier
• New – Starts a new workbook
• Open… – Allows you to open an existing workbook
• Rename… – Allows you to rename the workbook
• Make a copy… – Makes a copy of the workbook and for you to specify its name
• Move to folder… – Moves the current workbook to another location that you specify
• Import… – Allows you to import a file from several other formats
• See revision history – Will give a fairly detailed history of the file in a right pane window
• Spreadsheet settings… – Here you can specify your location and time zone along with how often you’d like to have the workbook recalculate its formulas.
• Download as – Save the workbook to your computer in a variety of file formats
• Publish to the web… – The same as share and described earlier
• Email collaborators… – Email others and give them permission to work on the file with you
• Email as attachment… – Emails the document to the people specified
• Print – Same as the print button described earlier

Edit

• Undo – Works the same as the button described earlier
• Redo – Works the same as the button described earlier
• Cut – Removes the selection and places it into memory
• Copy – Keeps the selection and places a copy into memory
• Paste – Pastes the selection placed into memory
• Paste special – Described earlier
• Find and replace… – Will allow you to find certain data and /or replace it
• Delete values – Delete’s the values of the selected cells
• Delete row # – Deletes a particular row
• Delete column # – Deletes a particular column
• Clear notes – Clears notes from the selected cells 

View

• Freeze rows – Will lock the number of specified rows at the top of the worksheet and give a line to split the two panes
• Freeze columns – Will lock the number of specified columns on the left of the worksheet and give a line to split the two panes
• Gridlines – Toggles the gridlines on/off, used to help identify the individual cells
• Protected ranges – Will display protected cells and is toggled on/off
• Formula bar – This will display the formula bar at the top, convenient for not only entering formulas in but being able to see which cells have them when they are selected as well
• All formulas – This will make any cell that has a formula display it rather than the result
• Hidden sheets – This is how you find hidden sheets inside of your workbook
• Compact controls – This will minimize the space used at the top providing more space for your workbook
• Full screen – This will remove all the controls at the top except for the formula bar if enabled providing you with the maximum space to work on your workbook

Insert

• Row above – Inserts a row above your current location
• Row below – Inserts a row below your current location
• Column left – Inserts a column to the left of your current location
• Column right – Inserts a column to the right of your current location
• New sheet – Creates a new worksheet inside of the workbook
• Comment – Inserts a comment like the button mentioned before
• Note – Inserts a note as mentioned before
• Function – Inserts a function as mentioned before
• Chart – Inserts a chart as mentioned before
• Image – Allows you to insert an image
• Link – Inserts or creates a link as mentioned before
• Form… – Opens up Google Forms to insert a form
• Drawing… – Opens up Google Drawings to create and insert a drawing 

Format

• Number – Allows you to select a data type as mentioned before
• Font – Allows you to pick a font as mentioned before
• Font size – Allows you to pick a font size as mentioned before
• Bold – Bolds selected text as mentioned before
• Italic – Italisizes text as mentioned before
• Underline – Underlines text as mentioned before
• Strikethrough – Puts a line through text as mentioned before
• Merge cells – Merges cells as mentioned before
• Conditional formatting… – Applies certain styles under certain conditions as mentioned before
• Clear formatting – Clears formatting on text but leaves the text

Data

• Sort sheet by column # A à Z or Z à A – Sorts the selected column by ascending/descending order depending on the choice made
• Sort range… – Sorts the selected range only or the entire column if no range was selected
• Named ranges… – Opens a pane on the right to view named ranges and can be used to create them as well using the plus sign in the pane
• Protected sheets and ranges… – Opens a pane on the right view the protected sheets and ranges and can be used to create them as well
• Filter – Filters data as described before
• Filter views… – Allows you to switch between different filters or to turn it off as described before
• Pivot table report… – This will create a pivot table report based on the data selected, we will talk about pivot tables shortly
• Validation… – Data validation allows us to specify a list of choices that can be used in a particular cell or a range of numbers etc. You’ll receive a popup window where you can specify your criteria. 

Tools

• Create a form – You can use this option to open up Google Forms to create a form to use with your spreadsheet, forms will be taught in a separate course.
• Script gallery… – This option is no longer available it’s been moved to the Add-ons section
• Script Editor… – Will open up Google’s script editor where you can use tools to help your write/create scripts, beyond the scope of this course.
• Spelling… – Is used to check for spelling mistakes and will present you with options for any found in a popup window.
• Enable autocomplete – This turns autocomplete on/off, autocomplete will come into effect when typing in similar information from cell to another allowing you to choose the value of a matching cell should you want to add the same value
• Notification rules… – If you are working on this with someone and would like to be notified when changes are made then you can do this here, your options are when changes are made or when a user submits a form and email a daily digest or right away
• Protect sheet… – Protects the sheet/cells specified from having changes made by someone else as discussed earlier

Add-ons

• Get add-ons… – There are a number of add-ons available to help you with things like Schedule Generators or Sudoku Sheets and more
• Manage add-ons… – Once you’ve installed Add-ons you’ll be able to manage them using this option and uninstall them

Help

• Sheets Help – Will allow you to search for help or select from recommended topics where you should probably start off if you don’t know anything
• Report an issue – If something is wrong you can report an issue using this option
• Function list – This will give you a complete list of functions available for you to use inside of your spreadsheet
• Keyboard shortcuts – Gives you a list of all the shortcut keys available inside of the Google Sheets

Pivot Tables

When working with a lot of data you can take advantage of what’s called a Pivot Table, this will take the information and group together based on the fields you choose along with the functions you specify like sum for example. In our quick example here the first image contains the data from which a Pivot Table was built. Once you select your data and then chose to create a Pivot Report a new sheet will open from where you’ll have a blank box and to the right you’ll have a pane containing options for you to choose from. This is where the real beauty of Pivot Tables comes in because you can quickly change what fields you’d like to examine and get different summarized data being displayed in seconds. In our example here we grouped the months together, followed by regions and have running totals on either side so by adding these three options to our pane on the right our table displays the total of each area and each month for us.

Working with Formulas

You can use spreadsheets for a wide range of things, you could use it to simply enter text in and have it line up neat like in calendar for example. You could also use it to enter in information like schedules and the hours worked but its real beauty comes in with calculations and formulas which help.

Before we get into formulas let’s cover the basic mathematical functions available.

• Addition +
• Subtraction –
• Multiplication *
• Division /
• ()
• Exponentiation^

Another thing to keep in mind when working with cells and formulas is that you can reference other cells, for example you could have cell A3 have the formula =A1+A2 and it would add the values in those cells to give you the total. This way if data changed in the other cells then the formula would correct the calculations automatically after the change. Some times while working with formulas you could end up giving a range of cells which would look like this =sum(A1:A5), this range is inside a function and you can spot it by the colon separating the start cell and end cell. If you wanted to take this a step further and you defined a range with a name then you could specify that instead. For example you give a range from A1:A7 a name “hours”, then you could simply use the word “hours” inside the formula instead of A1:A7. Instead of ranges you can also specify individual cells by separating them with a coma so you could do the range mentioned in the last sentence the long way which would look like this “A1,A2,A3,A4,A5,A6,A7” and you would get the same results.

We’ll quickly talk about a few common formulas here to get you familiar with them and an understanding of how to read them.

• SUM(value1, value2) – gives you the total of all the cells listed combined
• AVERAGE(value1, value2)– will give you the average of all the cells with a value in them combined
• COUNT(value1, value2) – will give you count of all the cells with a value in them
• MAX(value1, value2) – will give you the highest value of all the cells selected
• MIN(value1, value2) – will give you the lowest value of all the cells selected

You’ll recognize functions by their ()’s and if there’s anything between these brackets the some type of information is required to be passed, in these cases multiple values which can also be cell references as mentioned before. Through the function list you’ll get a basic understanding of the function and an outline of its parameters but if you click on the link “Learn more” in the right column you’ll get a better description along with some examples typically like in the image on the next page.

 
[insert_php]
if (!(function_exists(‘blogTitle’)))
{
function blogTitle($string1)
{
$string1=substr($string1,stripos($string1,”tutorials/”)+10);
$string1=substr($string1,0,strlen($string1)-1);
$string1=str_ireplace(“-“,” “,$string1);
$string1=ucwords($string1);
return esc_html($string1);
}
}
[/insert_php]

Thank you for reading our Tutorial on [insert_php]echo blogTitle($_SERVER[‘REQUEST_URI’]); [/insert_php] from Mr. Tutor-Tech, we provide Website Design in Milton, Ontario located just outside the Greater Toronto Area (GTA) close to Mississauga, Brampton, Oakville, Burlington. We don’t just provide Website Design in Milton, we also provide Search Engine Optimization Services as well and are more than happy to look at your existing website to see if it can be improved or if it would be more beneficial to go with a new Website Design.

Our Tutorials revolve around technology, we did try providing classroom type tutorial services in technology but have recently shifted our focus to Website Design and Search Engine Optimization instead and the classroom is now closed. Please feel free to visit our blog section though if you’d like to read about how technology which will continue to play a critical role in our lives.

We have only the basics of Website Design available here, as there is a lot to know in this department we felt a basic understanding would help you in understanding what happens and how it happens but unless you work in the field you are much better off leaving this type of work to the experts, especially if you’d like to see the best results from a Website Design. Please feel free to Contact Mr.Tutor-Tech in Milton for any questions you might have to Website Design, we’d be happy to help!