DIT5101 : COMPuter concepts and applications

GOOGLE SPREADSHEET: EXERCISE 1

Objective:
 To familiarize with the Google spreadsheet.
 To know the basic introduction of Google spreadsheet.
 To learn how to modify columns, rows and cell.

Practice 1: 1. Learn to customize your spreadsheet and data.

Practice 2: 1. Work with rows, columns, and cells.

Add rows, columns, and cells:

1. Select the row, column, or cell near where you want to add your new entry.
2. Right-click the highlighted row, column, or cell Insert choose where to insert
the new entry.

Delete, clear, or hide rows and columns: Right-click the row number or column letter
Delete, Clear, or Hide.

Delete cells: Select the cells and right-click Delete cells 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:

1. Select the cells.

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

Group rows or columns:

1. Select the rows or columns.

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

2. Click Data Group rows or Group columns.

Freeze header rows and columns: Keep a row or column in the same place as you scroll through
your spreadsheet. On the menu bar, click View Freeze and choose an option.

Practice 3: Click Share to share your spreadsheet and then choose what collaborators can do.
They'll also receive an email notification.

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

Practice 4: Collaborate with your team in real time.

Practice 5: Create different versions and copies of your spreadsheet.

Make a copy—Create a duplicate of your spreadsheet. This is a great way to create templates.

Download as—Download your spreadsheet in other formats, such as Excel or PDF.

Email as attachment—Email a copy of your spreadsheet.

Version history—See all the changes you and others have made to the spreadsheet or revert to
earlier versions.

Publish to the web—Publish a copy of your spreadsheet as a webpage or embed your spreadsheet
in a website.

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

Practice 6:

Your most important Excel functions exist in Sheets, too. Here’s a few of the things you can do.

AVERAGE Statistical Returns the numerical average value in a dataset, ignoring text.
AVERAGEIFS Statistical Returns the average of a range that depends upon multiple criteria.
CHOOSE Lookup Returns an element from a list of choices based on index.
COUNT Statistical Returns the count of the number of numeric values in a dataset.
COUNTIF Statistical Returns a conditional count across a range.
DATE Date Converts a provided year, month, and day into a date.
FIND Text Returns the position at which a string is first found within text.
GETPIVOTDATAText Extracts an aggregated value from a pivot table that corresponds to the
specified row and column headings.

IF Logical Returns one value if a logical expression is true and another if it is false.
INDEX Lookup Returns the content of a cell, specified by row and column offset.
INT Math Rounds a number down to the nearest integer that’s less than or equal to it.
LOOKUP Lookup Looks through a row or column for a key and returns the value of the cell
in a result range located in the same position as the search row or column.

MATCH Lookup Returns the relative position of an item in a range that matches a
specified value.
MAX Statistical Returns the maximum value in a numeric dataset.
MIN Statistical Returns the minimum value in a numeric dataset.
NOW Date Returns the current date and time as a date value.
ROUND Math Rounds a number to a certain number of decimal places according to
standard rules.
SUM Math Returns the sum of a series of numbers and/or cells.
SUMIF Math Returns a conditional sum across a range.
TODAY Date Returns the current date as a date value.
VLOOKUP Lookup Searches down the first column of a range for a key and returns the value
of a specified cell in the row found.

Lab Exercise:

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

1. Rename your sheet1 to StudentsID_sheet1.

2. Rename on the sheet tab below as Practice1.

3. Create the following content and set the format/style as shown in figure below in
“StudentsID_sheet1” sheet.
4. Add title as “Sales 2021”. Use Arial, 14, bold. For content, use Arial, 10, bold.

5. Highlight A2 until K2, and click Merge and Center. Fill an orange color to highlight the title.
6. For the Weekly Record (Monday, Tuesday, Wednesday, Thursday, Friday) use the fill handle.
Select B3:K7 and drag the fill handle to continue the series to create border.
7. Use the Borders toolbar to draw the table.

Introduction to Formulas

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

8. By using the same sheet “Practice1”, we will use a simple formula and cell references to
calculate an account.

=max (C3:G3)
= C4-C5 = sum (C4:G4) or
= 0.05 *C6
= C4+D4+E4+F4+G4 =average (C4:G4)

=min (C3:G3)

9. You can solve this question by typing the formula directly into the cell as shown above by
referring to the formula given below:

Modifying Columns, Rows and Cell

By default, every row and column of a new workbook is set to the same height and width. Excel
allows you to modify column width and row height in different ways, including wrapping
text and merging cells.
1. Using the same google spreadsheet file, add new sheet and name it as “Practice 2”.

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

2. Create the following content as shown in Staff Directory Figure below.

3. Insert new column on the left in between D and E. Right click and choose insert 1 left.

4. Name new column as “Join Date”.

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

5. Change data format by clicking to more format and choose date.

6. Explore around with the date format and you shall see the differences.
7. Select format “September 26, 2008”

8. Insert new data and the result table is as shown below:

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

9. Insert new row below Nurain and name the staff as “Suraya”.
10. Sort the No accordingly by highlighting the number and right click, choose sort range.

11. Edited table is as figure below. Brad Pitt should be No 9 by now.

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

12. Hide Column G under Extension and there is an arrow icon to show the column is hidden.

13. To unhide the data, just click at the arrow icon and the data shall reveal.
14. Your final Practice2 should look like figure below with border and center align.

FACULTY COMPUTING AND INFORMATIC

 DIT5101 : COMPuter concepts and applications

FACULTY COMPUTING AND INFORMATIC