Beginner

Question 1

MINUS(Date 1, Date 2)

Answer 1

Function which returns the difference between 2 dates. Output can be negative OR positive.

Question 2

DATEDIF(Date 1, Date2, “D” or “M”)

Answer 2

Function which returns the # of whole days (“D”) or months (“M”) between 2 dates. (Note: Date 1 < Date 2)

Question 3

NETWORKDAYS(Date 1, Date 2)

Answer 3

Function which returns the # of working days between 2 dates.

Question 4

WEEKDAY(Date, Type)

Answer 4

Function which returns the day of the week as a number depending on ‘Type’ parameter.

Question 5

What are the 3 Types in WEEKDAY(Date, Type)?

Answer 5

Type 1: Starts from Sunday = 1
Type 2: Starts from Monday = 1
Type 3: Starts from Monday = 0

Question 6

TEXT(Date, “ddd” or “mmm”)

Answer 6

Function which returns the day of the week or the month of the year as 3 letters.

Question 7

Difference between “mmm”, “mmmm”, “mmmmm”?

Answer 7

“mmm” = 3 letters
“mmmm” = entire word
“mmmmm” = 1st letter
(Note: “ddddd” = “dddd”)

Question 8

What is a range?

Answer 8

A collection of cells.

Defined by the upper left cell and lower right cell, separated by a colon.

Question 9

What is a named range? Why is it useful?

Answer 9

A range that has a name assigned to it. You can use that name in place of normal cell references.
They make formulas easier to understand (and debug), simplify complicated spreadsheets, and simplify macros.

Question 10

What is a protected range?

Answer 10

A range that can only be edited by some users.

Question 11

What is Data Validation?

Answer 11

Feature used to control what a user can enter into a cell.

Question 12

How to highlight duplicate entries in a column?

Answer 12

Use conditional formatting:

1. Select column (A) > Conditional Formatting
2. Select Custom Formula from drop-down list
3. Enter =countif(A:A, A1) > 1
4. Edit formatting styles

Question 13

How can you see all conditional formatting rules on your spreadsheet?

Answer 13

Select entire spreadsheet, command+A, or shift+command+space.

Question 14

SPARKLINE(Range)

Answer 14

Function which lets you see the trend of a range of numbers as a mini-chart in a cell.

Question 15

How to freeze header row?

Answer 15

View > Freeze > Choose # of rows/columns
OR
Drag the grey line at the top left corner

Question 16

MAX(Range)

Answer 16

Function which returns the maximum value from a range.

Question 17

MIN(Range)

Answer 17

Function which returns the minimum value from a range.

Question 18

SMALL(Range, n)

Answer 18

Function which returns the nth smallest number from a range.

Question 19

LARGE(Range, n)

Answer 19

Function which returns the nth largest number from a range.

Question 20

COUNTIF(Range, criterion)

Answer 20

Function which returns the conditional count from a range, depending on criterion (ex: “>20”).

Question 21

IF statements

Answer 21

IF(logical_expression, do_this_if_True, do_this_if_False).

Note: do_this_if_False is optional

Question 22

SUM(Range)

Answer 22

Function which returns the sum of a range.

Question 23

Relative Reference

Answer 23

A cell without the $ sign before the row and column coordinates. When a formula with relative cell references are copied to another cell, the reference changes based on a relative position of rows and columns.

Question 24

Absolute Reference

Answer 24

A cell with $ sign before the row and column coordinates. When a formula with absolute cell references are copied to another cell, the reference does not change.

Question 25

How to toggle Absolute/Relative References?

Answer 25

fn + F4

Question 26

Difference between Equal Rank and Average Rank?

Answer 26

Equal rank (RANK.EQ) returns the top rank of a value in a given range, while Average rank (RANK.AVG) returns the average rank.

Question 27

AND Operator

Answer 27

AND(logical_exp_1, logical_exp_2, logical_exp_3, ...) | The function will return TRUE if all expressions are true. It will return FALSE otherwise.

Question 28

What does AND(0, 1, 2) return?

Answer 28

FALSE. Google Sheets treats 0 as false.

Question 29

How to create a simple chart?

Answer 29

1. Select your columns (can include headers). 2. Insert > Chart 3. Edit Chart (type, add series, title, axis, etc.)

Question 30

How to add a trendline on a chart?

Answer 30

Chart Editor > Customize > Series > Trendline

Question 31

FILTER(Range, Condition 1)

Answer 31

Function which returns all the values in a range which follow a certain condition.

Question 32

1. How to return all the dates in August from a group of dates? 2. How to return the # of student absences for each of those days in August?

Answer 32

1. =FILTER(Dates, MONTH(Dates) = 8) | 2. =FILTER(Absences, MONTH(Dates) = 8)

Question 33

What does filtering do?

Answer 33

Filtering refers to displaying only rows or values that meet a specific criteria.

Question 34

How to use filters?

Answer 34

Select the range you want to filter and then click filter icon from toolbar. You can have different filter conditions for different columns.

Question 35

3 Filter Functionalities

Answer 35

1. Filter Feature: Any user can modify these features. 2. Filter View: Each user can have their own filter views. 3. Filter Functions: Filtering using functions such as FILTER, SORT, etc.