top of page

Google Spreadsheet

WHAT IS IT?

  • It is an online spreadsheet program.

  • It is capable of doing almost everything a typical of doing almost everything a typical spreadsheet program like Microsoft Excel.

  • It is capable of doing without having to download any software.

  • Features include:

  • Text formatting

  • Formulas charts

  • Scripting

  • Collaborative editing

  • Sharing between computers, devices, and other users

Opening Spreadsheet:

Rename your spreadsheet:

  • New spreadsheets begin with italicized text reading "Untitled spreadsheet" in the upper, left-hand corner of the screen.

  • Rename the title by simply clicking Enter after renaming the file.

Enter your spreadsheet name

Opening an existing spreadsheet from you computer:

  • New spreadsheets begin with italicized text reading "Untitled spreadsheet" in the upper, left-hand corner of the screen.

  • Rename the title by simply clicking Enter after renaming the file.

Open your existing file from your PC

Format cells

Autosaving

Function box

Functions

Format cells, rows or columns:

  • To format an entire row or column, select its corresponding number or letter.

  • After selecting the cell/cells, you may either click the format tab or select from several formatting options.

Organize your data:

  • After selecting your desire dataset from specified cells, rows or columns, you may then sort or filter data by clicking on the Data tab.

Inserting graphics:

  • Various visual representations can be useful when attempting to help others better understand your data.

  • You can find the option to place charts, images, forms and drawings near the bottom of the drop-down menu, after selecting Insert tab.

Perform function:

  • You will find some functions after clicking the function button along with some a More Functions button.

  • Here an example of writing the Sum function by selection the cells.

  • All functions start with "=" sign.

  • Then you can write functions.

  • Google Sheets offers most of the same functions users find in Microsoft Excel,

Write Functions

Download your spreadsheet:

  • If you wish to have a copy of your spreadsheet locally on your computer, select the "Download as" option under the file tab.

  • You can save in several formats as given below.

Share your spreadsheet:

  • You can find a share button on the upper righthand corner of your screen.

  • Enter the email address you want to share.

  • Select the button and you can determine whether others can edit, comment or simply view the document.

1
3
2

A most important feature of Google.

Simultaneously multiple users can edit and share.

SHORT KEY

  • Use keyboard shortcuts in Google sheets to navigate, format, and use in your spreadsheet.

  • To see a list of keyboard shortcuts in Google Sheets, press Ctrl + /.

Common Actions:

Format cells:

VLOOKUP & LOOKUP

  • Vertical lookup searches down the first column of a range for a key and returns the value of a specified cell in the row found.

  • VLOOKUP function in google sheets is designed to perform a vertical lookup.

  • Serch for a key value down the first column in a specified range and return a value in the same row from another column.

=VLOOKUP(search_key, range, index, [is_sorted])

  • HLOOKUP: (Horizontal lookup) Searches across the first row of a range for a key and returns the value of a specified cell in the column found.

  • Here is a sample function which demonstrates the working.

HLOOKUP(10003, A2:Z6, 2, FALSE)

HLOOKUP(search_key, range, index, [is_sorted])

MACROS

  • With macros, a user can create a spreadsheet with custom functions, menu items, and automated procedures.

  • Which is similar to Excel macros.

  • Here are the steps to create macros.

  • Macro uses Google Script code to write programs.

  • Click on Tools > Script editor.

1
2

  • Rename the Code.gs file to macros.gs.

  • After completion of code, click File > Save. Name your new script and click OK.

  •  

Save you GS file with a name.

Write your function here.

function1

function2

  • Click Insert > Drawing to draw a button and assign it a code.

1
2

  • You can select a share by selecting as below example.

Make a BUTTON of your choice.

  • Move your shape button to a specific position and assign your code.

  • To do so, click on 3-dot of button > Assign script.

Click on 3-dot on the button.

1
2

  • We need to assign another function for decreasing the quantity to the antother button.

  • Again create a square shape button and assign a function which will decrease the value of cell C2.

decreaseLine()

funcion will run.

​

increaseLine()

fucion will run.

Click the buttons to change the value of cell C2

bottom of page