Mixed Deck — All Google Sheets Test Topics Flashcards

In a QUERY WHERE clause, how should a text value be enclosed?

Answer: In single quotes, e.g. WHERE B = 'Active'

Text literals inside the query string are wrapped in single quotes so they don't clash with the outer double quotes.

In a color scale, what does the 'Midpoint' setting control?

Answer: An optional middle color and its threshold value

The midpoint adds an optional third color at a chosen value type such as percentile, number, or percent.

What does the MEDIAN function return for a dataset?

Answer: The middle value when data is sorted in order

MEDIAN returns the middle value after sorting; for an even count it averages the two middle values.

Which chart type stacks categories to compare both totals and component parts across groups?

Answer: Stacked column chart

A stacked column chart shows segment contributions and overall totals per group.

Which Google Sheets function returns a number (1–7) representing the day of the week for a given date?

Answer: WEEKDAY()

WEEKDAY(date, [type]) returns the day of the week as a number, with the type argument controlling which day is numbered 1.

To return the LAST matching value instead of the first, which XLOOKUP argument do you adjust?

Answer: Set search_mode to -1

search_mode -1 searches from last to first, returning the final match instead of the first.

What does adding a trendline to a chart help you see?

Answer: The general direction of the data

A trendline summarizes the overall direction or pattern of a data series.

Can you protect an entire sheet while leaving certain cells editable?

Answer: Yes, by adding exceptions for specific cells

You can protect a whole sheet and specify 'Except certain cells' to keep some editable.

A user has a data range from A1 to B13. Rows 2 through 12 contain monthly sales data, and cell B13 contains a SUM formula to show the grand total for the year. The user selects the entire range A1:B13 to create a line chart. What is the most likely outcome of including the 'Grand Total' row in the data source for the line chart?

Answer: The chart will show a dramatic, out-of-scale spike at the final data point, distorting the view of the monthly data.

Including a summary or total row in the source data is a common mistake. The total value is typically much larger than the individual data points, which causes the chart's vertical axis to scale up significantly. This flattens the appearance of the other data points and creates a large, misleading final point, skewing the visual representation of the trend.

What is the correct QUERY function clause keyword to filter rows?

Answer: WHERE

QUERY uses SQL-like syntax, and WHERE filters rows by condition.

Which function converts a number to a text string using a specified format in Google Sheets?

Answer: TEXT()

TEXT(number, format) converts a numeric value to text using a format pattern like "#,##0.00" or "MM/DD/YYYY".

When you record a macro in Google Sheets, what is automatically generated in the background?

Answer: A Google Apps Script function that replicates the recorded actions.

Recording a macro in Google Sheets automatically creates an Apps Script function. This function contains the JavaScript code that corresponds to the series of user interface interactions you recorded. This script is stored in a file named `macros.gs` bound to the spreadsheet.

A collaborator reports they can open the sheet but the edit tools are greyed out. What is the likely cause?

Answer: They were shared with Viewer access, not Editor

Greyed-out editing tools indicate the person has Viewer (read-only) permission.

What will =AVERAGE(2, 4, 6) return?

Answer: 4

The average of 2, 4, and 6 is 12 divided by 3, which equals 4.

Which function determines whether a condition is true or false before calculating the sum of values in a range?

Answer: SUMIF

The `SUMIF` function in Google Sheets is designed to conditionally sum values within a specified range. It calculates the sum of numbers only if they meet a single, defined criterion or condition. This function is highly useful for performing targeted calculations, such as summing sales figures for a particular product category.

Which situation would make a pivot table the wrong tool compared to a simple formula?

Answer: You need a single total of one column with no grouping

For a single ungrouped total, a SUM formula is simpler and faster than building a pivot table.

Where do you set permissions after clicking 'Set permissions' on a protected range?

Answer: A dialog to restrict who can edit

'Set permissions' opens a dialog to choose warning mode or restrict specific editors.

You are tracking task due dates in column D. You want to highlight any task that is overdue (the due date is before today) or is due today. Which custom formula will correctly accomplish this for a range starting at D2?

Answer: =AND(ISBLANK(D2)=FALSE, D2<=TODAY())

The formula `=AND(ISBLANK(D2)=FALSE, D2<=TODAY())` is the most robust option. `D2<=TODAY()` correctly identifies dates that are in the past or are the current date. The `AND(ISBLANK(D2)=FALSE, ...)` part is crucial to prevent the rule from incorrectly formatting empty cells, which Google Sheets can evaluate as being less than today's date.

What is the effect of using =AND(A1>0, B1<100) as a custom conditional formatting formula?

Answer: Formats only when both conditions are true

AND requires every argument to be TRUE, so both conditions must be satisfied for the format to apply.

What does =IFERROR(A1/B1,0) return when B1 is empty?

Answer: 0

IFERROR replaces the division error with the specified value 0.