Google Sheets Swiss Army Knife

I often find myself Googling the same how-to stuff whenever I need to create a new spreadsheet, so I decided to put together a “Swiss Army Knife” for Google Sheets. It’s full of tips and tricks for formulas and formats. Consider it as a cheat sheet and quick reference guide of sorts. You can access the spreadsheet here, and to make it your own, just select File > Make a copy. It contains all the necessary information, but I provided some additional information below.

Ratios & Percentages

On the Ratio & Percentages sheet, just enter values into the white cells, and let the spreadsheet do the rest.

Central Tendency

The Central Tendency sheet covers averages/means, medians, and modes. As shown below, this sheet also dives into a specific use case of network latency and P99 & P95 analysis.

Formatting Rows

The Formatting Rows sheet show how to conditionally format entire rows. As shown in the following examples, this sheet covers how to format rows based on the value of one of the cells in that row. In addition, it shows how to automatically format banded rows and how to automatically format banded rows based on changes in values.

ISBLANK & ISERROR Formulas

The ISxxx Formulas sheet shows the basic usage of the ISBLANK, ISERROR, ISERR, and ISNA functions. As shown below, note that ISBLANK does not consider whitespace (spaces, tabs, etc.) as blank.

In addition, notice the nuances of the ISERROR, ISERR, and ISNA functions. For example, the function REGEXEXTRACT("abcd", "xyz") returns “#N/A” representing a “not found”/”no match” result. In this case, ISERROR returns TRUE, but ISERR returns FALSE. Therefore, you might want to use a formula like this:

    =IF(ISERR(REGEXEXTRACT($C2,$D2)),
        "error",
        IF(ISNA(REGEXEXTRACT($C2,$D2)),
           "not found",
           REGEXEXTRACT($C2,$D2)))

COUNTx, COUNTIFS & SUMIFS Formulas

The COUNTx, COUNTIFS & SUMIFS Formulas sheet shows the basic usage of the various COUNTx functions (COUNT, COUNTA, COUNTUNIQUE). In addition, it shows example usage of the COUNTIFS & SUMIFS functions.

Regular Expressions

As you may know, there ain’t nothin’ regular about regular expressions, so this quick reference sheet comes in handy when I need to define a new text search pattern. Not only does the sheet include commonly-used regular expression syntax, but it also shows differences among the REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE functions. In addition, the sheet includes some use cases like extracting a file name from a Windows path. Lastly, the sheet includes reference links to RE2, which is what Google Sheets uses for regular expressions.

INDEX-MATCH Formula

Despite being a better lookup than VLOOKUP, INDEX-MATCH is not as intuitive; therefore, this example sheet is useful as a quick reference guide.

Query

The QUERY Formulas sheet shows the basic usage of the QUERY function, which is similar to but more powerful than the FILTER function. Click on the gray cell to see the QUERY function at work.

Uniqueness

The Uniqueness sheet shows how to highlight duplicates and use the UNIQUE function. To see how to highlight dupes, click on a table cell in the left table, and select Format > Conditional formatting from the menu.

Dates

The Dates sheet shows the differences between the TODAY and NOW functions. In addition, it shows various ways to performance date arithmetic.

Scaling

The Scaling sheet shows how to linearly scale a range of numbers. As shown in the following example, the Net Expense Ratios range from 0.04 to 0.45, and we want to normalize those values into a score from 5 to 1, 5 being the best and 1 being the worst. Using the following equation from Stack Overflow, we arrive at the Scaled Net Expense Ratio shown in the snapshot.

Note that in this case, the scoring/scaling/normalization is inversely proportional (i.e. a lower number equals a higher score). If we wanted direct proportionality, we would simply swap the a and b numbers (i.e. set a=1; b=5).

       (b-a)(x - min)
f(x) = --------------  + a
          max - min