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.
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.
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 ISBLANK & ISERROR Formulas sheet shows the basic usage of the ISBLANK and ISERROR functions. As shown below, it also shows a nuance of the ISBLANK function:
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.
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.
Despite being a better lookup than VLOOKUP, INDEX-MATCH is not as intuitive; therefore, this example sheet is useful as a quick reference guide.
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.
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.
The Dates sheet shows the differences between the TODAY and NOW functions. In addition, it shows various ways to performance date arithmetic.