Spreadsheets are tested extensively across multiple courses in the Google Data Analytics Certificate. Both Google Sheets and Excel are covered, though Google Sheets is used in most examples. This guide covers every formula, feature, and function you'll encounter — with practical examples for each.
Essential Formulas
VLOOKUP: Looks up a value in the first column of a range and returns a value from a specified column in the same row. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
SUMIF / SUMIFS: Sums values in a range that meet one or more conditions. =SUMIF(range, criteria, sum_range)
COUNTIF / COUNTIFS: Counts cells that meet specified criteria. =COUNTIF(range, criteria)
IF: Returns one value if a condition is true, another if false. =IF(logical_test, value_if_true, value_if_false)
IFERROR: Returns a specified value if a formula results in an error. Essential for clean data.
LEN / TRIM / CONCATENATE: Text functions used extensively in data cleaning tasks.
Pivot Tables
Pivot tables are one of the most important and most-tested features. Know how to create one, change the aggregation type (sum, count, average), add filters, and sort results.
Data Cleaning Functions
Several data cleaning operations come up repeatedly: removing duplicates (Data → Remove Duplicates), splitting text into columns (Data → Split text to columns), using TRIM() to remove extra spaces, using PROPER()/UPPER()/LOWER() to standardize text case, and FIND & REPLACE for bulk corrections.
Conditional Formatting
Conditional formatting lets you visually identify patterns or outliers in your data without writing formulas. Know how to apply it based on cell value, text content, date rules, and custom formulas. This feature is tested in the data cleaning and visualization courses.