Sheets

Google Sheets is a free web based spreadsheet program similar to Microsoft Excel

As a web base application, spreadsheets created using Sheets are available with internet connectivity and a full featured browser. Accessibility is a key feature of all the Google apps and Sheets is no different. Create, access or edit sheets from a computer, smart phone or tablet making all your work available when you need it. Sheets can also open, edit, and save Microsoft spreadsheets.

Collaborating on projects? Spreadsheets natively created in Sheets are easily sharable with others users who can work in the Sheet simultaneously. Also there is no save button because changes are saved automatically so there is no need to worry about loosing your data. Read on to learn more of the benefits!

1. Creating a Sheets spreadsheet

To create a Sheets spreadsheet, begin by selecting the drive in which you wish to save it ( your personal "My Drive" or a shared drive). Next click the plus sign and select Google Sheets. If a Sheets spreadsheet was saved as a template, clicking the toggle arrow provides access to select templates.

2. Sheets Overview

Once the Sheet opens, the first thing to do is name the "Untitled spreadsheet" in the upper left. The Sheets work space is divided into two main areas.

The tools section
The data container section

If you have used another spreadsheet program the tools are similar so there is not much of a learning curve.

The power in Google Sheets comes from the creative use of formulas and the processing power of Google. The Google servers can run a very complicated process at a fraction of the time it would take any personal computer.

3. Working with the cells, columns, rows and sheets

Add cells, columns, and rows:

Select the row, column, or cell near where you want to add your new entry.
Right-click, choose Insert. Then select the appropriate location where to insert the new entry based on the choices.

Delete, clear, or hide rows and columns:

Right-click the row number or column letter. Then select Delete, Clear, or Hide.

Delete cells:

Select the cells and right-click. Select Delete and choose either Shift left or Shift up.

Move rows or columns:

Select the row number or column letter and drag it to a new location.

Move cells:

Select the cells.
Point your cursor to the top of the selected cells until a hand appears.
Drag the cells to a new location.

Freeze header rows and columns: Used to keep a row or column in the same place when scrolling.

On the menu bar, click on "View" and select "Freeze" then choose one of the options.

Add, copy or delete or rename a sheet at the bottom of your spreadsheet:

Click the plus sign (Add sheet) to add another sheet.
Click the toggle on the current sheet and select the desired option.

4. Working with Microsoft Excel Files

If the file is a Microsoft Excel spreadsheet, there is the ability to "Save as Google Sheets". This is very helpful when sharing static Excel files with others who may need online editing rights.

To Convert an Excel file to a Google Sheets

Open Drive and double-click a Word document.
Click the button "Open with Google Docs".
Click File --> Save as Google Docs

Sharing Excel spreadsheets

Excel spreadsheets can be shared as a Excel files but to edit, they will need to l be downloaded. If edit rights are given and the link shared, users of that link can only comment, not truly edit.

Click the vertical ellipsis in the upper right
Selecting "Share" will provide a link but the Excel spreadsheet will still need to be downloaded for others to edit.

Remember: files created in a program other than Google Drive retain their program information. This allows users to download and work from the originating program but in order to edit them they need to be downloaded.

5. Formulas

Formulas can be simple or complex and they can apply to a variety of data types and perform wildly different tasks. The most difficult part of a formula is determining which one to use to get the job done.

For statistically based formulas, users may be able to use the XL Miner Analysis Toolpak instructions published on this site.

Everything starts with the Equal

All formulas, start with the equal sign. After that just think of the old phrase "Please Excuse My Dear Aunt Sally".

A formula will perform the work within Parentheses first, then any Exponents, then Multiplication and Division and lastly Addition and Subtraction.

=6+4^3/1*3-2 is very different than =(6+4)^3/1*(3-2) So keep Aunt Sally in mind when making a formula from scratch.

Google Sheets has over 450 formulas already made for use. To access these, click on the Sigma symbol on the toolbar. The most commonly used are at the top.

Accessing help with formulas from within Sheets is not as straightforward as another spreadsheet program you may know... but once a formula is selected a help window will describe its functionality.

6. Using Conditional Formatting

Creating visual formatting rules can make finding data or values stand out.

As an example in the included image, Ready to import is green and Do not include pink. Making text jump out can be extremely helpful!

In Google Sheets, highlight the cell or range of cells that you want to apply the formatting rules to.
Click Format --> Conditional formatting.
In the window that appears, select the "Format rules" to be applied and define the conditions.
Select the "Formatting Style to choose specific text colors and/or cell background colors.
Click Done.

8. Protecting & Unprotecting data, ranges and sheets

It is time to share your spreadsheet. It took a long time to figure out all the formulas and building out the process was complex, the last thing you want is for an accidental corruption of work.

Specific cells, a range of cells or an entire sheet can be protected . Viewers can print, copy, paste, and import and export copies of a protected spreadsheet. Think of it as accident protection, not as security.

If you want to protect a cell or range of content:

1. Select the cell or data range.
2. Right click on the cell or range and choose "Protect Range" .
3. In the window that appears, Click the "Set permissions" button.
4. Select a warning or determine who is able to edit the cell/range.
  - Only you: Only you (and the owner if you’re not the owner) can edit the range or sheet.
  - Only Immaculata: This option allows everyone at Immaculata to edit the spreadsheet.
  - Custom: Only the people you choose can edit the range or sheet.
  - Copy permissions from another range: Reuse the same permissions you set up on a different set of cells or sheet.
5. The click "Done".

If you want to protect the whole sheet:

1. Go to "Data".
2. Select "Protect Sheets and Ranges" from the drop down menu.
3. In the window that appears, Click the "Set permissions" button.
4. Select a warning or determine who is able to edit the cell/range.
  - Only you: Only you (and the owner if you’re not the owner) can edit the range or sheet.
  - Only Immaculata: This option allows everyone at Immaculata to edit the spreadsheet.
  - Custom: Only the people you choose can edit the range or sheet.
  - Copy permissions from another range: Reuse the same permissions you set up on a different set of cells or sheet.
5. The click "Done".

To unprotect cells in a protected sheet:

1. Check the Except certain cells checkbox .
2. Click inside the data range and then highlight the range you want to unprotect.
3. (optional) click the "Add another range" button to add more .
4. Click the "Set permissions" button to save.

Where to turn for help

Immaculata Office of Technology Services offers a one-stop-shop for all of your service requests, including, but not limited to technology-related issues and assistance with Moodle, Microsoft Office, G Suite apps, or other academic technologies. All Google applications are supported and questions regarding the use of this product application should be directed to the Freshservice instructions published in the IU Technology Resource Center site. Click on the image on the right to enter the Immaculata University Support Center site ==>.

Report abuse