Google Sheets Proficiency Certification — Questions and Answers

Question 1: What does the PERCENTILE function return in Google Sheets?

The rank of a value expressed as a percentage
The percentage a number represents relative to the total
The count of values that fall below a percentile
The value at a specified percentile within a dataset (Correct answer)

Correct answer: The value at a specified percentile within a dataset

PERCENTILE(data, percentile) returns the value at the given percentile, e.g., PERCENTILE(A1:A10, 0.9) gives the 90th percentile value.

Question 2: Which term refers to a person's education, income, and occupation?

Sorting
Filters (Correct answer)
Reporting
Statics

Correct answer: Filters

In data analysis, 'filters' are used to display only the data that meets specific criteria, effectively narrowing down a dataset. When examining attributes like education, income, and occupation, applying filters allows users to isolate and analyze subsets of data based on these demographic categories. This helps in quickly identifying patterns, trends, or specific groups within a larger dataset.

Question 3: What does the ARRAYFORMULA function allow you to do?

Delete duplicate rows
Sort an array
Freeze array columns
Apply a formula across an entire range at once (Correct answer)

Correct answer: Apply a formula across an entire range at once

ARRAYFORMULA applies a single formula to a whole range, outputting an array of results.

Question 4: Which of these is NOT a permission role available in Google Sheets sharing?

Viewer
Editor
Commenter
Reviewer (Correct answer)

Correct answer: Reviewer

Google Sheets offers Editor, Commenter, and Viewer roles—'Reviewer' is not one of them.

Question 5: Which option lets validation criteria depend on a formula result?

List of items
Text contains
Custom formula is (Correct answer)
Number between

Correct answer: Custom formula is

'Custom formula is' evaluates any formula returning TRUE to allow the entry.

Question 6: What does =IF(B2>50, "Pass", "Fail") do?

Counts values over 50
Returns Pass if B2 is over 50, otherwise Fail (Correct answer)
Adds 50 to B2
Returns Pass only if B2 equals 50

Correct answer: Returns Pass if B2 is over 50, otherwise Fail

IF tests the condition and returns the first result if true, the second if false.

Question 7: How do you move the legend to the bottom of a chart?

Use Setup > Legend
Drag the chart down
Legends cannot be moved
Set position to Bottom under the Legend section in Customize (Correct answer)

Correct answer: Set position to Bottom under the Legend section in Customize

The Legend section in Customize includes a position dropdown with Bottom.

Question 8: How can you leave a comment directed at a specific collaborator so they receive an email notification?

Add them as a Viewer
Bold their name in a cell
Type '+' or '@' followed by their email in the comment (Correct answer)
Change the cell color

Correct answer: Type '+' or '@' followed by their email in the comment

Using '+' or '@' with an email in a comment assigns it to that person and notifies them.

Question 9: Which Customize option lets you display the actual value labels directly on chart bars or points?

Data labels (Correct answer)
Background color
Gridlines
Legend

Correct answer: Data labels

Enabling data labels prints each value on the chart element.

Question 10: Which statement about macro execution permissions is correct?

Authorization is granted automatically for all scripts
Only the file owner can run any macro
The user may be prompted to authorize the script the first time it runs (Correct answer)
Macros never require authorization

Correct answer: The user may be prompted to authorize the script the first time it runs

Apps Script prompts for authorization when a macro first accesses your data.

Question 11: What does the SORT function do to the original source data?

Permanently reorders the source
Nothing — it outputs a sorted copy elsewhere (Correct answer)
Deletes the source
Hides the source rows

Correct answer: Nothing — it outputs a sorted copy elsewhere

SORT is a non-destructive formula that produces a sorted array without changing the source range.

Question 12: Which statement about copying a cell with data validation to another cell is true?

Validation is never copied
It corrupts the sheet
Only the value copies
Paste normally copies the validation rule along with the value (Correct answer)

Correct answer: Paste normally copies the validation rule along with the value

A standard copy-paste carries the data validation rule to the destination cell.

Question 13: Which symbol locks both the column and row in a cell reference?

A$1
$A1
A1
$A$1 (Correct answer)

Correct answer: $A$1

A dollar sign before both letter and number makes an absolute reference.

Question 14: How can you set an expiration date on a collaborator's access to a spreadsheet?

Delete them after a week manually only
Protect the range
Set the sheet to Viewer
Use 'Add expiration' in the advanced sharing options (Workspace) (Correct answer)

Correct answer: Use 'Add expiration' in the advanced sharing options (Workspace)

Google Workspace lets owners add an expiration date to temporary access for Editors, Commenters, or Viewers.

Question 15: What does the LEFT(text, num_chars) function return in Google Sheets?

The first N characters from the left of a string (Correct answer)
All characters to the left of a space
The leftmost word in a string
The last N characters of a string

Correct answer: The first N characters from the left of a string

LEFT(text, num_chars) returns the specified number of characters starting from the beginning (left side) of a text string.

Question 16: A user wants to highlight all cells in the range B2:E20 that contain duplicate values within that same range. Which custom formula should they apply?

=IF(B2=B1, TRUE, FALSE)
=DUPLICATE(B2:E20)
=COUNTIF(B:E, B2)>1
=COUNTIF($B$2:$E$20, B2)>1 (Correct answer)

Correct answer: =COUNTIF($B$2:$E$20, B2)>1

The formula `=COUNTIF($B$2:$E$20, B2)>1` correctly identifies duplicates. The `COUNTIF` function counts how many times the value of the current cell (B2, which changes for each cell in the range) appears within the entire fixed range (`$B$2:$E$20`). If the count is greater than 1, it means the value is a duplicate, and the formula returns TRUE, triggering the format.

Question 17: After editing a chart, how do you save the changes in the Chart editor?

Changes apply automatically as you edit (Correct answer)
Click a Save button
Press Ctrl+S
Use File > Save chart

Correct answer: Changes apply automatically as you edit

Chart editor changes update the chart live without a separate save step.

Question 18: When customizing a scatter plot or line chart in Google Sheets, what is the primary purpose of adding a trendline?

To highlight the minimum and maximum values within the data series.
To add interactive data labels to each point on the chart.
To connect individual data points with a smooth curve for aesthetic purposes.
To visualize the overall direction or pattern in the data and make predictions. (Correct answer)

Correct answer: To visualize the overall direction or pattern in the data and make predictions.

A trendline is a statistical tool used to show the general pattern or direction of data points. It can be linear, exponential, or polynomial, and it helps in understanding trends and forecasting future values based on the existing data.

Question 19: What does the GEOMEAN function calculate in Google Sheets?

The mean of values plotted along a geometric axis
The median of values arranged in a geometric sequence
The geometric mean of a set of positive numbers (Correct answer)
The arithmetic average of a dataset

Correct answer: The geometric mean of a set of positive numbers

GEOMEAN multiplies all values together and takes the nth root, making it useful for growth rates and ratios.

Question 20: What is the best practice when protecting ranges in a shared collaborative sheet?

Protect nothing
Protect every cell
Protect only critical formula and header ranges, leaving input cells open (Correct answer)
Set a password on the file

Correct answer: Protect only critical formula and header ranges, leaving input cells open

Protecting only key formulas and headers prevents accidental breakage while keeping collaboration smooth.

Question 21: What happens when you transfer ownership of a spreadsheet to another person?

The new person becomes a Viewer
The new person becomes Owner and the original owner becomes an Editor (Correct answer)
The original owner loses all access
The file is duplicated

Correct answer: The new person becomes Owner and the original owner becomes an Editor

After a transfer, the previous owner is downgraded to Editor while the recipient gains ownership.

Question 22: What does SUMIF do that SUM does not?

Averages the values
Adds only values meeting a condition (Correct answer)
Rounds the total
Adds numbers faster

Correct answer: Adds only values meeting a condition

SUMIF totals only the cells that satisfy a specified criterion.

Question 23: If you want the highest-selling product to appear at the top of a pivot table, what should you configure?

Sort the row field by the SUM of Sales, descending (Correct answer)
Apply a color scale
Alphabetize the row field
Add the product to the Filters area

Correct answer: Sort the row field by the SUM of Sales, descending

Sorting the row field by the aggregated Sales value in descending order puts the top seller first.

Question 24: 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?

=AND(ISBLANK(D2)=FALSE, D2<=TODAY()) (Correct answer)
=D2<NOW()
=D2>TODAY()
=D2=TODAY()-1

Correct 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.

Question 25: What does the TIME(hour, minute, second) function create in Google Sheets?

A date serial number
A text label for a time
A fractional day value representing a time (Correct answer)
A timestamp with timezone

Correct answer: A fractional day value representing a time

TIME(hour, minute, second) returns a fractional number between 0 and 1 that represents a time of day within Google Sheets' date system.

Question 26: What does =SUBSTITUTE("2023-2024","-","/") return?

2023/2024 (Correct answer)
2023-2024
20232024
2024/2023

Correct answer: 2023/2024

SUBSTITUTE replaces each hyphen with a slash.

Question 27: What does the CONCATENATE function do?

Removes spaces
Counts characters
Splits text into columns
Joins multiple text strings into one (Correct answer)

Correct answer: Joins multiple text strings into one

CONCATENATE combines several strings into a single text value.

Question 28: A financial analyst is using a pivot table to summarize sales data which includes 'Revenue' and 'Units Sold' columns. To add a new column directly within the pivot table that calculates the 'Average Price per Unit' ('Revenue' / 'Units Sold'), which feature of the pivot table editor should be used?

A Calculated Field (Correct answer)
A Slicer
A Filter
The 'Group' function

Correct answer: A Calculated Field

Calculated Fields are designed to create new fields that perform custom calculations using the data from other fields within the pivot table. In this case, a formula like ='Revenue' / 'Units Sold' would be entered in a new calculated field.

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

3
6
4 (Correct answer)
12

Correct answer: 4

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

Question 30: What does =TODAY() return?

A random date
The current date (Correct answer)
The day of the week as text
The current date and time

Correct answer: The current date

TODAY returns the current date and updates automatically.

Question 31: An operations manager is tracking two different metrics on a monthly basis: 'Total Units Produced' (ranging from 5,000 to 8,000) and 'Factory Uptime Percentage' (ranging from 85% to 98%). They want to display both trends over time on a single chart to see if there is a correlation, but the significant difference in scale makes them difficult to view together. Which chart type is best suited for this scenario?

Waterfall Chart
Combo Chart (Correct answer)
Stacked Area Chart
Bubble Chart

Correct answer: Combo Chart

A Combo Chart is the ideal choice as it allows you to plot different data series using different chart types (e.g., columns and a line) and assign them to different vertical axes (a left and a right axis). This is perfect for comparing two metrics with different units or vastly different scales, such as raw numbers and percentages.

Question 32: We use ____ to lock it if the tax (any value) reference is B11 and you do not want the fill function to change this.

$B11
"=B11"
$(B)11
$B$11 (Correct answer)

Correct answer: $B$11

To create an absolute reference that completely locks both the column and the row, a dollar sign ($) must precede both the column letter and the row number. This ensures that when the formula is copied or filled to other cells, the reference to B11 remains fixed. Therefore, $B$11 is the correct syntax to prevent the fill function from changing the reference.

Question 33: What does the 'Show validation help text' option do?

Highlights the cell red
Displays a message when the cell is selected (Correct answer)
Emails the sheet owner
Blocks all input

Correct answer: Displays a message when the cell is selected

Help text appears as a tooltip guiding the user on acceptable input.

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

A private folder in Google Drive to store the macro.
A Google Apps Script function that replicates the recorded actions. (Correct answer)
An XML file embedded within the sheet's metadata.
A new, separate Google Sheet file containing the macro steps.

Correct 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.

Question 35: What does a Viewer see when they open a shared spreadsheet?

The comment sidebar with the ability to reply
Read-only content with no ability to edit or comment (Correct answer)
Sharing settings
Full editing tools

Correct answer: Read-only content with no ability to edit or comment

Viewers can only read the spreadsheet and cannot make any changes or comments.

Google Sheets Proficiency Certification

The Google Sheets Proficiency Certification assesses competency in spreadsheet creation, data manipulation, formula usage, data visualization, and collaboration within Google Sheets.

Questions

60 min

Time Limit

80%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds

These practice questions were written with AI tools. They are not real exam questions. We have not yet matched this exam's facts to an official source, so confirm fees, dates and rules with the exam body. Not reviewed by a subject-matter expert. Editorial policy