Get half off by entering the following discount code:
Year end half off
Google Spreadsheets is a full featured spreadsheet application, much like Excel. However, it is web based, so you can access your spreadsheet from any location, and share and collaborate securely with people you invite to collaborate on the spreadsheet.
This hour long course covers the skills you'll need to be able to use the advanced features that are unique to Google Spreadsheet. You'll learn how pivot tables and charts work inside of Google Spreadsheet, and the filtering and views options so you can analyze your data without altering the view for your other collaborators.
Take this course if you are looking to use Google Spreadsheet to collaborate with other people at the same time on the same document. If you are an employee in a large company that is using Google Apps, or you work at small start up that isn't using Excel, this course will help you be productive using Google Spreadsheet.
Introduction to Google Sheets. Spreadsheets are one of the most important tools in a business. Spreadsheets can be used to calculate data to help make accurate business decisions, track inventory, or assign tasks. Google Sheets has all of the most important functions of legacy spreadsheet programs, such as Microsoft Excel, while providing easy sharing and collaboration with other members of your team.
The sharing and collaborating features for all Google documents were covered in the Google Drive Training course. Google Sheets Training will cover only the collaborating features unique to Google Sheets. For more information on importing data into your spreadsheet by using a Google Form, please see the lesson on Google Forms in Google Drive Training.
Offline access is available for Google Drive, including your Google spreadsheets. Ask your administrator or see the Google Help site for detailed instructions on how to enable offline access.
In this course, you will develop the skills to use Google Sheets to enter, calculate, analyze, format, and chart data to be able to make solid business decisions.
Keyboard shortcuts greatly increase your productivity, reduce repetitive strain, and help you stay focused. Press Ctrl+/ (or Cmd+/ on a Mac) at any time to see a list of all the keyboard shortcuts for Google Sheets.
When opening the menus, the keys in keyboard shortcuts slightly differ between operating systems and browsers. When using the Chrome browser on a Windows or Linux computer, use the Alt key. When using other browsers on Windows or Linux, press Alt+Shift. On a Mac, press Ctrl+Option.
When you open a menu with a shortcut, each command in the menu will show an underlined letter. Once the menu is open, press the letter on your keyboard to use that command. For example Alt+E opens the ‘Edit’ menu. The letter ‘U’ will then apply the ‘Undo last action’ command.
Common commands, such as ‘Undo’ also have their own keyboard shortcut combinations for even faster execution. Press Ctrl+Z to undo the last action without opening the ‘Edit’ menu. There are keyboard shortcuts for editing, formatting, movement, selection, view, and more. Note that shortcuts that use the Ctrl key in Windows or Linux should be replaced with the Cmd key on a Mac.
Some of the most used keyboard shortcuts are Ctrl+C to copy, Ctrl+V to paste, Ctrl+Y to repeat the last action, Ctrl+Z to undo the last action, and Ctrl+F to search. Review the keyboard shortcuts list to find your most used commands and their shortcuts.
Add data into Google Sheets. Data can be added to a Google spreadsheet in a number of different ways: direct input, copy and paste, importing an existing spreadsheet, or referencing data from another spreadsheet with a function. For information on collecting data with Google Forms, please see the lesson on Google Forms in Google Drive Training. In this lesson, you will develop the skills to add data into a Google spreadsheet, including images and hyperlinks.
To create a new Google Spreadsheet, from the Google Drive home page, click ‘Create’ then ‘Spreadsheet’. Enter a title for the spreadsheet.
Google Sheets, like all spreadsheet applications, is comprised of cells that are identified by their row and column. To enter content into an empty cell, simply click the cell and start typing. Press Enter or Return on your keyboard to finish editing the cell. To edit a cell that already has content, double-click the cell you want to edit, or press Enter or Return on your keyboard to edit the highlighted cell.
To create a spreadsheet that is easy to understand, enter titles in the rows and columns, then insert your data into the cells. You may want to spend a few moments considering how to best insert your data so it will be easy to analyze and understand. Entering formulas and charts will be covered later in this course.
To create a line break within a cell while editing, place your cursor in a cell and press Ctrl+Enter (or Cmd+Return on a Mac). You can use this command to improve the look of text that requires line breaks, such as addresses or long titles.
To select multiple cells that are not next to one another, click and hold Ctrl on your keyboard (or Cmd on a Mac) as you select the cells. Selecting non-adjacent cells is useful when formatting and when selecting cells for a formula.
To add an additional sheet to your spreadsheet, click the ‘Add sheet’ icon. Double-click the name of a sheet to rename it, then press Enter or Return on your keyboard. Click and drag a sheet to change its position in your sheet tabs list. To organize your sheet tabs using colors, click the menu button on a sheet tab. Select ‘Change color’, and pick a color.
You can copy a sheet to another spreadsheet. Click the menu button on the sheet tab that you want to copy, and select ‘Copy to…’. Click on the spreadsheet you want to copy to, then click ‘Select’. A copy of your sheet is created in the target spreadsheet.
Copying and pasting in Google Sheets copies all properties of one cell into another cell, including text, formatting, data validation, and more. For most cases, the recommended method to copy and paste is to use keyboard shortcuts: Ctrl+C, and Ctrl+V on a PC. Cmd+C, and Cmd+V on a Mac.
The Chrome browser is required in order to use the ‘Cut,' 'Copy,' and 'Paste' options available in the right-click menu and the 'Edit' menu. Also make sure you have the Drive app for Chrome installed.
When you cut or copy data, you'll see a dotted line around the cell or cells to help you remember where you cut or copied the data from.
Use the ‘Paste special’ option when you want to have more control over what properties you are copying and pasting into a range of cells. ‘Paste special’ works only within a single spreadsheet. In other words, you cannot copy cells from one spreadsheet and use ‘Paste special’ in a spreadsheet you have open in another tab.
Select the data you want to paste, then press Ctrl+C (or Cmd+C on a Mac) to copy the data. Next, click the ‘Edit’ menu, and hover over ‘Paste Special’. Then select from one of the pasting options, such as ‘Paste values only’, or ‘Paste format only’. Use ‘Paste transpose’ to paste a rotated version of the copied cells. For example, if you copy a column of cells and use paste transpose, it will paste them into a row. You can even use paste transpose to rotate an entire table of data.
Use the keyboard shortcut Ctrl+Y (or Cmd+Y on a Mac) to repeat your last action in another section of your spreadsheet, as many times as necessary. You can save time using this shortcut for re-applying formatting or redoing an operation, such as inserting a row or column. Note that it does not work with all actions that may be complex or need extra information, for example printing a spreadsheet or inserting a chart.
You can import data into Google Sheets from existing spreadsheets that were created with other software, or by copy and pasting the data from those spreadsheets directly.
Google Sheets can import data from a spreadsheet in most major file formats. Password-protected files cannot be imported. To import a spreadsheet, click ‘File’, then ‘Import’. Choose the file you want to upload, and select one of the available import options. Note that not all file types will have all six import options.
If you are importing a plain text file like .csv or .txt, you will have the option to select a separator character, like a comma or tab, or to choose a customized separator character. When ready, click ‘Import’. Your data is imported to Google Sheets.
You can also import data into Google Sheets by copying data from another source, such as a webpage or email, and pasting it into Google Sheets.
To replicate data and copy it from one sheet to another within a single spreadsheet, enter an equals sign, then the sheet name, and, an exclamation mark before the cell being copied. Note that if a sheet name contains spaces or other non-alphanumeric symbols, you must include single quotes around the sheet name. You can do this automatically by entering an equals sign into your cell, then viewing the other sheet and selecting the cell you want to reference. Then press Enter or Return.
To import data from another spreadsheet, use the IMPORTRANGE function. Enter an equal sign into your cell, then type ‘IMPORTRANGE’ followed by an open parenthesis and a quotation mark. Then enter the entire URL of the spreadsheet you are importing from. Close with another double quotation mark. Add a coma, and a quotation mark, then the sheet name, and, an exclamation mark before the cell being copied. Close parenthesis, and press Enter or Return on your keyboard. The data is imported to your spreadsheet.
The first time a spreadsheet pulls data from another spreadsheet, the person inserting the IMPORTRANGE function will be asked to grant the spreadsheet access to the data in the other spreadsheet. Once access is granted, all other viewers and editors of the spreadsheet will have access to the data being imported from the other spreadsheet by the IMPORTRANGE function.
If you're simply looking to find something within a spreadsheet, press Ctrl+F (or Cmd+F on a Mac) and type a word or phrase that you want to find. As you type, your search term is highlighted everywhere that it appears in the currently active sheet. If multiple results are found in your sheet, click on the down arrow in the find bar to jump to the next result, or press Enter or Return on your keyboard. Click the up arrow to view the previous result, or press Shift and Enter on a PC, or Shift and Return on a Mac.
If you're looking to replace a specific term, use the ‘Find and replace’ tool. Click the ‘Edit’ menu and select ‘Find and replace’. Then type the terms into the text box next to 'Find’. Use one or more search options to narrow your search.
Click the drop-down menu next to ‘Search’ and select where to conduct your search. To find and replace items inside of formulas, instead of only their calculated values, check the box next to ‘Search within formulas’.
To replace a word or expression in your document, type the text you want to replace in the box next to 'Replace with' and then click the ‘Replace’ button. If you want to replace all the selected words at once, click the ‘Replace all’ button.
You can add images to your Google spreadsheet in the most common file types. Images must be less than 2 MB.
To insert an image into your spreadsheet, click the ‘Insert’ menu and select ‘Image’. Then select a source to insert your image from. Once you have found the image you want, click the image, then click the ‘Select’ button. Your image is inserted on top of the cells of your spreadsheet.
To use a different image, or to delete this one, click on the image, then click the arrow in the top-right corner of your image. Select ‘Edit image...’ to use a different image, or ‘Delete image’ to delete it.
To resize your image, click on the image, then hover over the edge or corner of it. When the pointer turns into a double arrow, click and drag to resize. Hold down the ‘Shift’ key on your keyboard as you resize to maintain the aspect ratio. To reset to the original size, click the arrow in the top-right corner of your image, then select ‘Reset size’.
You can also insert an image inside of a specific cell using the IMAGE function. Enter the IMAGE function into the desired cell, then enter the URL of the image you want to use. Finally, enter a number one through four to specify which sizing mode you wish to use. ‘1’ scales the image to fit inside of the selected cell. ‘2’ stretches the image to fit inside of the selected cell. ‘3’ inserts the image into the cell at its original size. And ‘4’ customizes the size of the image by specifying the height and width of the image in pixels. The height and width parameters are required for this option.
If you want to include a link in your spreadsheet, simply enter the URL in the cell, then press Enter or Return on your keyboard.
If you want to link a specific URL to text within a cell of your spreadsheet, click the cell in your spreadsheet where you want the link to appear. Then click the ‘Insert link’ icon in the toolbar. In the ‘Text’ field that appears, type or edit the text that you want displayed in the cell containing the link. Leave this field blank if you want the full URL to be displayed in your spreadsheet. In the ‘Link’ field, either paste a URL or email address, or type in the field to begin a search of relevant links across the web and within your Google Drive files. When ready, click ‘Apply’.
In this lesson, you have developed the skills to enter data into a Google spreadsheet, import and reference data from existing spreadsheets, copy and paste, find and replace, and insert additional sheets, hyperlinks, and images. Use these skills to gather the information you need when building useful spreadsheets.
Change viewing options. Like most spreadsheet applications, Google Sheets offers a number of features that allow you to adjust how you and your collaborators view the information in your spreadsheet. In this lesson, you will develop the skills to freeze rows and columns, and hide different elements of your spreadsheet.
Freezing rows and columns allows you to keep some of your data in the same place as you scroll through the rest of your spreadsheet. This is particularly useful if you have headings or labels that you want to keep in one place as you scroll through a large dataset so you can keep track of the titles on the rows or columns.
Use the dark grey lines on the top-left of your spreadsheet to freeze rows and columns. To freeze rows, place your mouse over the horizontal grey line. When the pointer becomes a hand, click and drag the grey line downwards below the last row you wish to freeze. A grey border is added to the spreadsheet below the rows you have frozen. You can now scroll down while the frozen rows remain in view. Click and drag the vertical grey line to the right of the last column you wish to freeze. You can now scroll to the right while the frozen columns remain in view. Notice that the frozen rows also remain in view.
Drag the grey lines to new positions to change the number of rows or columns you want to freeze. To unfreeze all rows and columns, drag the grey lines above row one, and to the left of column A.
If you want, you can click the ‘View’ menu, then ‘Freeze rows’ or ‘Freeze columns’ and select the number of rows or columns you’d like to freeze.
You can hide specific rows and columns in your spreadsheets. Note that Google Sheets still reads the data in hidden rows and columns, even though they cannot be seen.
To hide rows, click and drag to highlight the row numbers you want to hide. Then right-click on your selection, and select ‘Hide row’ from the drop-down menu. The selected rows are hidden, and an icon appears in their place. To unhide the rows, click the icon and the rows reappear on the spreadsheet. Use these same steps with columns to hide or unhide columns.
Google Sheets allows you to take individual sheets out of view by hiding the sheet. This feature is particularly useful if you have individual sheets that are old, rarely used, or are placeholders for calculations used by other sheets.
It’s important to note that hiding a sheet is not the same as protecting a sheet, which is discussed later in this course. People with view-only permission will not be able to view hidden sheets. However, all editors on the spreadsheet will be able to unhide and view the hidden sheets.
To hide a sheet in your spreadsheet, click the menu button on the sheet’s tab that you want to hide, then select ‘Hide sheet’. Your sheet is now hidden. To unhide a sheet in your spreadsheet, click the ‘View’ menu, then select ‘Hidden sheets’. An additional menu expands that displays all of your hidden sheets. Select the sheet that you no longer want hidden. If your spreadsheet doesn’t contain any hidden sheets, the ‘Hidden sheets’ option will be greyed out.
Alternatively, you can click the ‘All sheets’ button. Hidden sheets will be shown in grey. Select the sheet that you no longer want hidden. Notice that your sheet now shows with the rest of your sheet tabs at the bottom of your spreadsheet.
Note that if you, your spreadsheets viewers, or spreadsheet editors make a copy of the spreadsheet, the hidden sheets will be preserved in the copy, but others will be able to unhide them. Additionally, data contained in hidden sheets will be accessible via Google Apps Script and the Google Data API. To protect data in your spreadsheet, make sheets or ranges of cells protected, as explained later in this course.
Hidden sheets will remain hidden if you export or import your spreadsheet to or from most major file formats, or if you publish your spreadsheet using the Publish feature.
When opening a spreadsheet with protected ranges, individuals will see ranges they don’t have edit access to displayed with a checkered background. If the background pattern makes it difficult to read spreadsheet content, you can hide protected ranges. Click the ‘View’ menu, then uncheck ‘Protected ranges’.
You can hide the gridlines on a sheet to enhance the look of your data before sharing or presenting it to others. Click the ‘View’ menu, then uncheck ‘Gridlines’. The gridlines on this sheet are now hidden, leaving only the borders that were added through formatting.
To see the formula that is in a cell, select the cell; the formula for that cell is displayed in the formula bar. Use the formula bar to make sure that the calculations you are performing are correct. Hide the formula bar to provide more space for the spreadsheet.
To hide the formula bar, click the ‘View’ menu, then uncheck ‘Formula bar’. The formula bar is no longer displayed.
Sometimes it is helpful to see all of the formulas that make up your spreadsheet. To show all the formulas in your spreadsheet, click ‘View’, then select ‘All formulas’. The cells now display the formula text instead of the formula calculations. Select ‘All formulas’ again to exit formula view mode.
When you need to maximize the number of cells on your screen, use ‘Compact controls’ or ‘Full screen’ view. Click ‘View’, then select ‘Compact controls’. The Google Sheets menu now reaches the top of the browser window. Select ‘Compact controls’ again to exit ‘Compact controls view. For an even fuller workspace, click ‘View’, then select ‘Full screen’. The formula bar now reaches the top of your browser window. Press ‘Esc’ on your keyboard to exit ‘Full screen’ view.
In this lesson you have developed the skills to freeze rows and columns, and hide main parts of a spreadsheet, including rows and columns, sheets, protected ranges, gridlines, and the formula bar. Use these skills to enhance how you and your collaborators work with your spreadsheet.
Collaborate with Google Sheets. Google Sheets offers the same collaborative tools that make collaboration on all Google documents simple and efficient. In addition, Google Sheets has several tools that allow collaborators to work on a spreadsheet without modifying data that should remain untouched. In this lesson, you will develop the skills to share the information you want to share while protecting the data you do not want collaborators to modify.
If you want to restrict your collaborators from editing a specific sheet in your spreadsheet, you can protect the sheet. Click the menu button on the sheet's tab at the bottom of your spreadsheet and select ‘Protect sheet...’.
A side panel opens for protecting sheets and ranges with the ‘Sheet’ button selected. Enter a description for the other collaborators. The sheet you chose to protect is listed in the drop-down menu. If you want to protect a different sheet, click the drop-down menu and select a different sheet.
To change users’ viewing, commenting, and editing permissions, click ‘Set permissions’. The spreadsheet’s collaborators are listed in the window that appears. Adjust their permissions as needed.
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. Only owners can set editing permissions for individual sheets. Click ‘Done’ when finished.
To edit the sheet’s protection settings, click the menu button on the sheet’s tab, ‘Protect sheet...’, then the edit icon. Click the trash can icon to remove protection from the sheet, then click ‘Remove’ to confirm.
It is important to note that if an editor 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 new copy since it is an entirely different file.
Click the ‘X’ to close the ‘Protected sheets and ranges’ window.
If you want to restrict your collaborators from editing a specific range of cells within a sheet, you can protect the range. Select the cell or group of cells you want to protect. Click the ‘Data’ menu and select ‘Protected sheets and ranges’. A side panel opens for protecting sheets and ranges with the ‘Range’ button selected.
Your selected range should appear in the range box. To change or enter a new range, type the range manually, or click on the spreadsheet grid icon to the right of the range box and highlight the range in the spreadsheet. Click ‘Ok’.
To change users’ viewing, commenting, and editing permissions, click ‘Set permissions’. The spreadsheet’s collaborators are listed in the window that appears. Adjust their permissions as needed. Click ‘Done’.
Edit or delete a protected range just like you would edit or delete protection for a specific sheet. Keep in mind protecting a specific range does not stop people from copying, downloading, or printing the spreadsheet along with the protected range.
Naming ranges is a way to assign a name to a cell or a group of cells so that they are easier to keep track of. For example, instead of using B2:B8 to reference a range of cells, you could name the range ‘Direct_sales’ so that formulas like =SUM(B2:B8, D2:D8) can be simpler and more intuitively written as =SUM(Direct_sales, Reseller_sales). Once ranges are given names, creating formulas becomes easier and more efficient.
To name a range, select the cell or range of cells that you want to define. Click the ‘Data’ menu, and ‘Named ranges’. In the side panel that appears, name your range in the first text field. A named range can only contain letters, numbers, and underscores, but no spaces or punctuation. See the Tips document in the Tools section of this course for detailed guidelines on creating range names.
Your selected range should already be defined in the second text field. If you want to make changes or enter a new range, click on the spreadsheet grid icon to the right of the range box and highlight the range in the spreadsheet. Click ‘Ok’. You can also type the range manually into the range box. When finished, click ‘Done’. A list of your named ranges is displayed.
To edit or delete a named range, click the ‘Data’ menu, ‘Named ranges...’, and click the the ‘Edit’ button next to the named range you want change. Edit the details and click ‘Done’, or click the trash can icon to delete the named range. When you delete a named range, any formulas referencing this named range will no longer work. However, protected ranges that reference a named range will swap out the named range for the cell values themselves and continue to be protected.
Enable data validation if you want to limit the type of information you or your collaborators can enter into specific cells. You can restrict what values are possible for cells that contain numbers, dates, and text; show people a warning if they enter invalid data, or prevent them from entering invalid data in the first place; or, allow people to select a cell's contents from a drop-down list.
To start using data validation, click on the ‘Data’ menu and select ‘Validation’. Alternatively, highlight and then right-click a range of cells and select ‘Data validation...’. The selected range of cells is listed. Click the spreadsheet grid icon to select a different range of cells.
Then, select validation criteria from the drop-down menu. These are the criteria that must be met when entering data in the selected range of cells.
Next, choose how Google Sheets should respond if data is entered that does not meet the criteria. ‘Show warning’ will allow invalid data entry, but with a warning message. ‘Reject input’ will only allow data that meets your criteria. Check ‘Show help’ if you want users to see help text when they hover over the cell. Google Sheets offers default help text which you can modify. Click the ‘Reset’ link to reset the help text to the default message for your criteria.
If you set criteria based on a list, you can allow users to select valid entries for a cell from a drop-down menu. To enable the drop-down list, check ‘Display in-cell button to show list’. An arrow button will appear in each cell, which people can click to reveal the drop-down menu.
When finished, click ‘Save’.
To remove data validation, select a range of cells, click the ‘Data’ menu, select ‘Validation’, then click the ‘Remove validation’ button. If you want to remove validation from the entire worksheet, click the upper left corner of the sheet to select the whole sheet. Then click ‘Remove validation’.
When using data validation, keep in mind that collaborators with edit permission can change validation rules. Also, cells with data validation are skipped in spreadsheets that gather data through a form.
You can set notifications to find out when other people have made changes to your spreadsheets, and learn what they have changed.
To set notifications, open the spreadsheet where you want to set notifications. Click the ‘Tools’ menu, then ‘Notification rules’. In the window that appears, select when you want to receive notifications, such as if any changes are made, and how often you want to receive notifications. Click ‘Save’. Your new notification rules are saved, and now appear in the list of notification rules for this spreadsheet. Click ‘Edit’ or ‘Delete’ on a set of rules to edit or delete them. When finished, click ‘Done’.
When receiving notifications, you and other editors will see the usernames of people who have made changes. People limited to view-only access can also set notifications, but they cannot see usernames. You will not be sent notifications about your own changes.
In this lesson, you have developed the skills to set up protection for sheets and ranges, name ranges, enable data validation, and set notification rules for a spreadsheet. Use these skills to optimize your spreadsheets for efficient collaboration.
Use formulas and functions. Many businesses use spreadsheet formulas to conduct a wide variety of business activities, such as finance, inventory management, sales records, business analysis, project management, and more. In this lesson, you will develop the skills to enter formulas and functions in a spreadsheet using Google Sheets.
Formulas are instructions for calculating values, whether simple arithmetic such as as 2+2 or complex algorithms for finance, math, science, and more.
Add a formula to any cell in a spreadsheet by typing an equal sign followed by calculation instructions, then press Enter or Return. Notice that the cell displays the calculated value from the formula and not the formula itself. To view the formula, click on the cell. The formula appears in the formula bar. Double-click on the cell, or press Enter or Return, to edit the formula.
To integrate the contents of your spreadsheet into your calculations, use cell coordinates in your formulas. For example, the formula =(A3+B3)/C3 will add and divide the values in those cells. When you reference other cells in a formula, Google Sheets colors the cell references, and highlights the cells themselves in contrasting colors to help you more easily build your formula. When you double-click on a cell that contains a completed formula, the referenced cells are also highlighted, making it easier to understand what the formula is calculating. In addition to cell references, a formula may also include one or more functions, discussed later in this lesson.
When you fill a formula down or across cells using the autofill feature, the cell references in the formula will automatically adjust based on the location of the filled cell. If you want the cell references to stay the same as you fill cells, you can make the cell references absolute.
When editing your formula, place the cursor in the cell reference you wish to make absolute, then press F4 on your keyboard. A dollar sign appears before the column and row number of the cell, indicating that neither the column nor row number will adjust if you fill or copy & paste this formula. If you want just the row or just the column to be absolute, press F4 a second or third time to place a dollar sign only in front of the row number or column letter. Press F4 a fourth time to revert to a relative cell reference.
A function is a preset formula included in Google Sheets. For example, the SUM function finds the total value of a specified set of numbers. Instead of entering the formula =A1+A2+A3+A4, the SUM function can total the values in the range A1:A4.
Click the ‘Function’ icon in the toolbar to show a list of common functions. Click on a function to enter it into your formula. You can also use the Quick Sum feature at the bottom of your spreadsheet to instantly total the cells you have selected.
Click the Quick Sum button to see other calculations for the selected cells, such as Average and Count. Click one of the other common functions in Quick Sum to set it as your Quick Function whenever you highlight cells in your spreadsheet.
From the ‘Function’ icon in the toolbar, click ‘More functions...’ to see a list of all the functions available in Google Sheets. There are hundreds of functions that can be used to
manipulate, modify, change, or view the data in your spreadsheet. Search the function database with keywords, or pick a category from the drop-down menu. Some of the most common function categories are Financial, Lookup, Math, Statistical, and Text.
Many businesses run their daily operations through the functions in spreadsheets. Use the functions list to learn when to use each function, and how to use them together to achieve the desired results. Google Sheets simplifies using functions with the function help box, discussed next in this lesson.
If you don’t remember the exact name of a function, use the autocomplete feature to help you enter it correctly. Enter the first few characters of the function you want to use and a list of relevant functions automatically appears. Hover over a function name to see a brief description. Click on a function name to insert it into your formula, or press Enter or Return on your keyboard. Once a function is entered into your formula with an open parenthesis, the function help box appears. The help box makes entering functions an easy-to-follow, step-by-step process.
In between the parenthesis of a function are the instruction elements, called arguments, that the function needs for performing its operation. The help box highlights the argument that you are editing, based on the location of your cursor. An additional highlight appears on that argument in the help box example. An arrow also appears in the help box next to the syntax for the argument you are editing, to help you enter the correct information. For example, for the argument ‘range’, the help box says ‘The range which is tested against criterion’. This tells you to enter the range on your spreadsheet that you want the function to compare your criterion with.
As you proceed to the next argument, the highlighting and arrow features progress with you until you complete the editing process.
Notice that the arguments in a function need to be separated by commas, and that some arguments must be surrounded by double quotation marks. If you need more information, click the ‘Learn more’ link at the bottom of the help box to open a full article.
Click the up arrow to minimize the help box, and the ‘x’ to close it. To reopen the help box, click the question mark that appears when editing your function.
A function used inside of another function is called a nested function. Nested functions allow you to eliminate intermediate steps by combining multiple functions into a single calculation.
Without using nested functions, we can find the sum of the data in our spreadsheet using two steps. First, we enter the SUM function and select the range to sum. Then, in another cell, we enter the ROUND function to round the total values to the nearest whole number.
Mathematically, this process works fine. However, we had to use an extra row of data to achieve our results. The extra information can be distracting from the important data, and may take up needed cell space in the spreadsheet. Nested functions let you enter all the steps to a calculation in a single formula and eliminate intermediate steps, yet still achieve the same results.
To make a nested function, enter your function as one of the arguments inside the parenthesis of the surrounding function. Keep in mind that Google Sheets will calculate the innermost function first. In our example, we want to calculate the total values with the SUM function, and then round them with the ROUND function. We enter the ROUND function, and nest the SUM function within it.
You can also create formulas with multiple levels of nested functions. Use nested functions, to make more efficient formulas and simpler spreadsheets.
The SUM function may be the most commonly used function in spreadsheet applications. Use the SUMIFS function to make your SUM function smarter and only add together cells that meet customized criteria.
In our example, we want to sum values from the sales range based on their type of sales channel. We enter the SUMIFS function, and select the range of cells containing our values to be summed. Next we select the range of cells that might match our criterion. Lastly, we define the criterion that must be matched in order to sum. This can be a string of text, as in our example, or a logical operation such as ‘greater than 10’. We now have customized sums for each sales channel.
You can also use COUNTIFS and AVERAGEIFS to count and find averages based on custom criteria.
Use the NOW function to automatically display the date and time that the spreadsheet was last recalculated. Note that there are no arguments in this function, just empty parenthesis. Each time a new calculation is made anywhere in the spreadsheet, the now function updates. Although this can be useful, keep in mind that it can also hinder spreadsheet performance.
If you want to display just the date, use the TODAY function.
In addition to updating after a recalculation, you can also set the NOW and TODAY functions in your spreadsheet to update every so often. Click the ‘File’ menu, then ‘Spreadsheet settings’. Under recalculation, click the drop-down menu and select a time interval. This spreadsheet will now update the NOW and TODAY functions after every recalculation, and after the selected time interval.
In this lesson, you have developed the skills to create formulas, use cell references, enter functions with the function help box, and create nested functions. Use these skills to develop powerful spreadsheets that enhance productivity for your organization.
Sort and filter data. Many times the true value of a list of data is only found once it is properly organized. Filtering out extraneous data can suddenly reveal crucial information for a business. Sorting a list of data can bring meaningful patterns for analysis and decision making. In this lesson you will develop the skills to sort and filter data in Google Sheets.
To sort an entire column, click on the column letter, then click ‘Data’, and ‘Sort sheet by Column’. Select to sort either ascending, A through Z, or descending, Z through A. The entire sheet is re-oriented to sort your selected column.
You can sort a range of cells according to rules set for one or more columns. To sort your data, highlight the range of cells you want to sort. To sort the entire sheet, click the top left corner of the sheet to select all cells. When ready, click the ‘Data’ menu, then ‘Sort range...’. Select ‘Data has header row’ if your columns have titles. Select the column you'd like to be sorted first and whether you would like that column sorted in ascending or descending order. Click ‘+Add another’ if you want to add another sorting rule. Sorting is prioritized according to the order of your rules. Click the ‘x’ next to a rule to remove it. Click ‘Sort’ and your range will be sorted.
The Google Sheets filter will temporarily hide some of the data in your spreadsheet, allowing you to view only the data you wish to see. When you want all of your data to be visible again, simply disable the filter.
Select the range of cells you want to apply the filter to. Click on the ‘Filter’ icon in the toolbar. The filter will be applied to your selected range of cells. To help you see what cell ranges have a filter applied, the column and row labels are colored green.
Click the drop-down icon in the header row to select one of the filtering options. From the drop-down menu, you can uncheck the data points that you want to hide from view and check data points that you want to keep in view. If you want to complete a bulk action, click ‘Select all’ or ‘Clear’ to either check or uncheck all of your data points in a given column.
Use the search box to search for particular data points within a column that has a filter applied. Typing ‘S’, for example, will shorten the list to just the items that start with S. When finished, click ‘OK’. Your data is now filtered, and the icon in the header row is now a filter.
Note that only one filter is allowed per spre.adsheet 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 permission. Use filter views, next in this lesson, to create a filter that does not disrupt the data for other users.
Use the ‘filter views’ feature to create, name, and save personal filters that you can apply to data at any time without disrupting how others are viewing the spreadsheet.
To create a filter view, open the spreadsheet where you would like to create a filter view. Click the down arrow next to the filter icon and select ‘Create a new filter view’. The filter view is signified by dark grey highlighting on your column and row headers, as well as a line above the column headers with the name and range of your filter.
Using the down arrows in the column headers, sort and filter the data to fit your desired view, as you would with a normal filter. Your filter view will be saved as you make changes. Click on the name of the filter view to rename it. To close your new filter view, click the ‘X’ in the top-right corner of the spreadsheet.
You can also save a regular filter as a filter view so that you can apply it again later. When a regular filter is applied, click the down arrow next to the filter icon, then select select ‘Save as filter view’. Your filter view is now saved.
To apply an existing filter view, click the down arrow next to the filter icon, then select the name of the filter view you want to apply. Your filter view will now be applied to your data without disrupting how others view the spreadsheet.
To delete or duplicate a filter view, click the gear icon in the top right corner of the filter view, then select ‘Delete’ or ‘Duplicate’. Deleting a filter view will stop filtering your data with the filter view, and delete it from the list of saved filter views for this spreadsheet. If you duplicate a filter view, a copy of your filter view will be saved to the spreadsheet with the words ‘Copy of’ added to the name.
In this lesson, you have developed the skills to sort and filter data, including Google Sheets’ unique Filter views feature. Use these features to hone in on crucial information and create meaningful lists of data in your spreadsheets.
Create 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 quickly analyze large lists of data in a way that reveals significant relationships between different fields and values. In just a few clicks, you can summarize and analyze a large set of data through different categorizations and calculations. In this lesson, you will develop the skills to create pivot table reports from your data, and tailor them for effective presentation.
To create a pivot table report, open the spreadsheet that contains the data you want to use for your pivot table. If you do not manually select a range of cells, Google Sheets automatically detects a range of cells for you. From the ‘Data’ menu, select ‘Pivot table report’. A new sheet named ‘Pivot Table 1’ opens in your spreadsheet with the Report Editor open to the right. This sheet contains an empty pivot table report. The pivot table report has three categories that are used for analyzing your data: Rows, Columns, and Values. A fourth category, Filters, is used to hide data from selected fields.
The range of cells used to generate the pivot table report is displayed in the Report Editor. You can edit the data range at any time. Click ‘Edit range…’. Then, click on the sheet name that contains the range of cells you wish to use. Highlight the appropriate range of cells, and click ‘OK’. The pivot table report will adjust the reported data to reflect the new range, as listed in the Report Editor.
To close the Report Editor, click the ‘X’ in the top right of the Report Editor, or click outside of the borders of the pivot table report. To open the Report Editor, click any of the cells within the borders of the table.
You can create multiple pivot table reports from the same set of data. Simply go back to the data source and create a new pivot table report. The new pivot table report will be created on a separate sheet.
To begin adding data into your pivot table report, click ‘Add field’ in one of the categories. A list of fields from your dataset is displayed. These fields are based on the column headers in your dataset. The data contained in each field is then drawn from the items in each column of your dataset. For this reason, it’s a good idea to always organize your data into columns when creating a pivot table report.
Select the field you wish to display. The items from the field now appear in the chosen category of your pivot table report, and the name of the field is listed in the Report Editor under the category you chose. Click ‘Add field’ to add additional fields to this category, or to another category. Click the ‘X’ in the top right of a field to remove it from your pivot table report.
The pivot table report displays data from the categories in the following ways: the Rows category uses the chosen field to generate one row in the pivot table report for each item from that field. The Columns category generates one column for each item from another chosen field. The Values category fills each cell with a number value or text, based on the row and column it belongs to. These values will allow you to compare, contrast, and draw conclusions from the fields in your Row and Column categories.
The pivot table report will automatically add the values of each row and column into a grand total. Grand total summaries will appear at the right and to the bottom of your pivot table report. To hide the totals, deselect the ‘Show totals’ option.
By default, the Value category uses a SUM function to show the sum of the values in your pivot table report. In our example, the Value category shows the sum of licenses sold for each app in each region. Click on the drop-down menu to calculate the values using a different function, such as ‘AVERAGE’. The Value category now shows the average number of licenses sold per sale, for each app in each region.
Filters allow you to hide certain data points that you don't want to appear in your pivot table report. To add a filter, click ‘Add field’ in the ‘Filters’ category. Select the field you wish to filter. Then click the drop-down menu in the field you wish to filter. Uncheck the data points that you want to hide from view and check the ones that you want to keep in view. If you want to complete a bulk action, click ‘Select all’ or ‘Clear’ to either check or uncheck all of your data points in a given field.
Use the search box to search for particular data points within the field. Typing ‘E’, for example, will shorten the list to just the words that start with E. When finished, click ‘OK’. The pivot table report now displays only the selected items for the field being filtered. Click the ‘X’ on a field to remove it from the Filters category.
What makes pivot tables so useful is how easily you can rearrange, or pivot, the information in the table. This allows you to quickly look at your data from different perspectives to reveal significant relationships between the fields and items in your dataset.
When two or more fields are added to a category, the field shown at the top of the 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. Click on the hide icon next to an item to hide the secondary field for that item. Click the show icon to show the secondary field.
Hover the mouse over a field in the Report Editor. When the mouse turns into a four-point arrow, click and drag the field into a new position in the category, or move it into a different category. Your pivot table report instantly rearranges the data.
If the Values category contains more than one field, data can be displayed in either rows or columns. To switch between row and column display, click the ‘as: Columns’ drop-down menu at the top of the Values category. Then click ‘Rows’.
You cannot edit cell values by manually typing new values or by changing formulas in the pivot table report. Doing so would break the connection between the pivot table report and your original dataset. To change data in your pivot table report, edit the source data directly. Your pivot table report will update instantly.
You can analyze your data even more by creating formulas with your pivot table report. To use a formula with a pivot table report, enter the formula outside of the borders of the pivot table report. The formula will treat pivot table values as regular data cells, and can be dragged to expand across a row or column.
If you make changes to the source data after adding a formula, the pivot table report and formula results will both update automatically with new values.
Use the order and sort features in the Report Editor to give your data a more meaningful sequence. First, identify which information you wish to sort by. In our example, we want to sort the rows of our table by Region name, and alphabetically. By default, the Report Editor sorts the data in your pivot table report according to the selected field. You can also sort by a field from the Values category, as we will see later. Next, click the drop-down menu for ‘Order’ and select ‘Ascending’ or ‘Descending’. The rows of your pivot table will be sorted in ascending or descending order according to the selected field.
To sort by a field from the Values category instead, click the drop-down menu for ‘Sort by’ and select the Values field that you wish to sort by. Then select which column of values you wish to sort by. If you want to sort rows by ‘Grand total’, be sure to have the ‘Show totals’ option selected in the Columns category, and vice versa. The rows in your table are now sorted by the selected column of values. Finally, select ‘Ascending’ or ‘Descending’.
In this lesson, you have developed the skills to create pivot table reports from your data, and have learned to summarize, filter, rearrange, and sort the data. Use these skills to build insightful reports and analysis for your organization.
Add formatting. High quality spreadsheets not only contain valuable data, but also make the information clear and easy to comprehend. In this lesson, you will develop the skills to add formatting to a spreadsheet, including basic and advanced conditional formatting.
You can format data in your spreadsheets in a variety of ways using the options in the spreadsheet toolbar. Highlight the cells you want to format. Then select a formatting option from the toolbar. Hover over an icon in the toolbar to see a description of what that option can do. You can also find a number of these actions in the menus, or use keyboard shortcut to quickly apply formatting.
Use conditional formatting rules to get a visual summary of complex or changing data. You can format cells to change their text or background colors if they meet certain conditions, for example if the cells contain specific words or numbers.
To apply conditional formatting, highlight the cell or range of cells that you want to apply formatting rules to. Then click the ‘Format’ menu and select ‘Conditional formatting…’. In the window that appears, click the drop-down menu to choose one of the condition types. Conditions are based on text, time, or number value. You can also make a custom formula as your formatting condition. Custom formulas are taught next in this lesson.
Select a condition from the list. Then, define the condition that must be met for your formatting to apply. Finally, set the text colors and/or cell background colors to apply when the condition is met. Use the range box to adjust the range of cells that the conditional formatting applies to. When finished, click ‘Save rules’. Your conditional formatting rule is applied.
To edit a conditional formatting rule, highlight the cells that have conditional formatting, then open the conditional formatting window. Edit the rule, then click ‘Save rules.’ The formatting for the respective cells adjusts accordingly.
To add another conditional formatting rule, click ‘+ Add another rule’. There are no limits to the number of rules you can make per cell or range. Note that when you specify multiple rules for the same cell or range of cells, the rules are evaluated in the order listed. The first rule found to be true will define the format of the cell or range. To remove a rule, click the ‘X’ to the right of that rule.
You can apply conditional formatting using a custom formula. This allows you to apply formatting to a cell or range of cells based on the contents of other cells. To format with a custom formula, highlight the cells you want to add conditional formatting to, then click the ‘Format’ menu and select ‘Conditional formatting...’. Click the drop-down list of conditions, and select the ‘Custom formula’ option.
Next, add in the relevant formula. Be sure to always begin with an equals sign when using custom formulas. In our example, we want to highlight any regions that made a total of less than 1,000 sales. We use the value of our Grand Totals from column F to create a formula: “If cell F1 is less than 1,000, apply pink background.”
Next, we enter the range of cells that we want to highlight if they meet the condition. In our example, we will use cells A1 through A8, which contain the names of our sale regions.
It’s important to note that the cell reference “F1” is not a fixed reference. Google Sheets will read our custom formula as “F1” only when considering formatting cell A1. When Google Sheets considers formatting cell A2 or A3, Google Sheets actually changes the “F1” in our custom formula to “F2” or “F3”. In this way, Google Sheets maintains the relationship between each region name in column A, and its grand total in column F. Cell references in custom formulas is discussed again later in this lesson.
After saving our new rule, pink background now highlights the region whose total sales are less than 1,000.
In this lesson, you have developed the skills to add standard formatting and conditional formatting to a spreadsheet. Use these skills to effectively communicate the valuable information in your spreadsheets.
Work with charts. You can add a variety of charts and graphs into a spreadsheet, including line, bar, and map charts. A chart can be inserted into a document, presentation, or webpage. In this lesson, you will develop the skills to create, customize, and present charts using Google Sheets.
To create a chart or graph, select the cells with data that you want to include in the chart. It helps to label the data in your spreadsheet before creating a chart. These labels will appear automatically in the chart editor where you create and preview your chart, as long as the labels are the first row and column of your selected range of cells. Next, click the chart icon in the menu bar. The chart editor appears with a tab for each area of the chart editor: Start, Charts, and Customize.
In the Start tab, you can edit the range of cells to be included in your chart, select basic layout settings, and view recommended charts for your dataset. A preview of your chart is displayed on the right, and will update as you make changes throughout the chart editor. Click ‘Insert’ at any time to insert the chart as it is. You will still be able to edit all aspects of your chart after inserting it into your spreadsheet.
Use the chart editor to manually edit the range of cells for your chart, or, click the spreadsheet grid icon to select cells directly from your spreadsheet. Place the mouse near the top edge of the range box, then click and drag to move it. Select a range of cells, and the range box updates according to your selection. To enter additional ranges, press and hold Ctrl on your keyboard (or Cmd on a Mac) as you select another range of cells, or click ‘Add another range’. When finished, click ‘Okay’.
If using multiple ranges, select whether to combine the ranges vertically or horizontally on your chart.
Click the ‘Switch rows and columns’ option to reverse the row and column data presented in your chart, and use the preview to see how this option will look in your chart. Select whether or not to use the content of your first column as headers on your chart.
Google Sheets automatically recommends chart types that match your selected data. Click on a chart type to test it in the preview. If you decide that none of the recommended charts is what you had in mind for your data, or if you want to see more chart options, click ‘More’ or click the Charts tab. From the Charts tab, select a chart type, then click on one of the sub types to preview it.
Some charts will not work with your selected range of cells and will be grayed out. If you click on a greyed out chart, Google Sheets will show you a message in the preview box with an example of how to structure your data so that the selected chart will work.
Use the Customize tab to fine-tune each detail of your chart. Scroll down through the left side of the chart editor to find the element of your chart you wish to customize. Then make the appropriate adjustments. Your changes are shown instantly in the preview.
Enter a title for your chart and the desired formatting. Change the position of the legend, the font for text in your chart, and the background color. Select ‘Maximize’ to make your chart fill the entire chart canvas. Select ‘Compare mode’ to add tooltips that show the values of each data series when you hover the mouse over a given point in your chart. If you do not see an element of your chart, be sure to click the drop-down menu in that section and select the appropriate element you wish to edit.
When you are ready to create your chart, click ‘Insert’. The chart appears in your spreadsheet.
To edit a chart, first click on it. The border expands and two buttons appear on the top-left corner of your chart: ‘View’ mode and ‘Quick Edit’ mode. Hover and click specific areas of your chart that you wish to modify:
To resize the whole chart, hover the mouse over the border of the chart area until the pointer changes to a double arrow. Then click and drag to a new size. To reposition the chart on your spreadsheet, hover the mouse over the upper border of the chart. When the pointer becomes a hand, click and drag the chart to a new position.
When you are done making changes, click ‘View mode’ to lock the chart from quick edits. Click anywhere on your sheet to close the editing options.
‘Quick Edit’ is not available for all chart types. Charts that don’t allow ‘Quick Edit’ will not show the two buttons in the top-left corner, and can be edited only by opening ‘Advanced Edit mode’. Click the arrow in the top-right corner of your chart, then select ‘Advanced edit...’ to make further changes. Notice that this brings up the same Customize tab you may have used when initially creating your chart. Apply the desired edits to your chart, then click ‘Update’ to save changes.
Sometimes the best way to display a chart is with its own dedicated sheet within your spreadsheet. To create a sheet that displays your chart, click on your chart, then click the arrow in the top-right corner of your chart, and select ‘Move to own sheet...’. Your chart is transferred to a new sheet and expands to fill the entire sheet. The chart options menu is displayed at the top of the sheet as buttons, including the ‘View’ mode and ‘Quick Edit’ mode buttons. Double-click the name of the sheet to rename it.
To insert a chart into a Google Doc or Presentation, click on your chart, then click the arrow in the top-right corner of your chart, and select ‘Copy chart’. From the Google Doc or Presentation, click where you want the chart to be inserted. Then press Ctrl+V on a PC, or Cmd+V on a Mac. Your chart is pasted into the Google Doc or Presentation as an image, which you can resize and realign.
Note that you cannot paste charts into another spreadsheet. You can, however, save them as static image files and insert the images into another spreadsheet.
To save your chart as an image, click your chart, then click the arrow in the top-right corner of your chart, and select ‘Save image’. Choose a location to save to on your computer, then click ‘Save’.
You can insert an interactive or a static version of your chart into a webpage in just a few clicks. Click on your chart, then click the arrow in the top-right corner of your chart, and select ‘Publish chart...’. A window with some code appears. Click the drop-down arrow and select whether you want to insert an interactive chart or a plain image of your chart. Highlight the code, then copy and paste it into your web page’s HTML.
Boost eLearning is creator of Google Apps Training, the world's most complete training application for Google Apps. Boost eLearning creates training that is used by Fortune 500 companies.
Boost eLearning content is created with experts in each subject area. Our editing of the video content is the highest quality: we spend about 30 minutes of editing to produce 1 minute of finished video. Boost eLearning knows training and adult learning; our content is delivered in short, clear, step-by-step modules. Professional voice over artists make listening and understanding the content simple.