Categories


Authors

Google Sheets Swiss Army Knife

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.

formatting+rows.png

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.

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.

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.

Data Model Template

Data Model Template

Agile Metrics Using Google Sheets

Agile Metrics Using Google Sheets