![](/style/images/good.png)
![](/style/images/bad.png)
Google Sheets for Managers
source link: https://chase-seibert.github.io/blog/2021/04/26/google-sheets-for-managers.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Spreadsheets “for managers” is a tongue-in-cheek way of saying that none of these features are particularly impressive to have mastered. But, they will help you greatly in almost any knowledge worker role.
This blog post has a companion Google Sheet playground, with examples. Also, I’m not attempting to be exhaustive with any of these topics; merely to provide an introduction and basic syntax examples.
Understanding Cell ReferencesPermalink
A cell reference in a spreadsheet is a variable that points to another cell. For example formula =A1
would copy the value from cell A1
to the current cell. Bonus: you can give a cell an easy-to-reference name via Data → Named ranges
.
Copy FormulaPermalink
When a range of cells is selected, there is a box handle in the lower right of the range that you can use to copy the formula to other cells. This is mostly used to “fill in” the rest of a column (or occasionally a row) with the same formula.
Note: you can quickly fill the rest of a column with a given formula by holding down COMMAND
(on a Mac) and double-clicking on the copy formula box handle. Alternatively, you can script this with ARRAYFORMULA.
When copying a formula, it’s often useful to keep either the source column number or source row number (or both) fixed. For example, if you are a value in one specific cell, such as the dollar cost average per engineer, you may want to reference that value in another formula. In this case, you would use an absolute reference like $A$1
, which would always point to A1
, no matter where you copied the formula.
It’s also possible to use a column-relative or row-relative absolute reference, such as A$1
or $A1
.
Text MungingPermalink
These cover the majority of basic text manipulation.
Official documentation is here:
Honorable mentions: REGEXMATCH, REGEXREPLACE, and COUNTIF.
Import & Export CSVPermalink
Spreadsheets allow for easy import and export of CSV data. See File → Import
. Importing CSV from some data source, manipulating it, and then doing a pivot table is like 20% of being a manager. 😉
You can use awk
, sed
, and sometimes just grep
to do some basic pre-processing of a CSV file.
Filters and SlicersPermalink
Selecting an entire sheet and doing Data → Create a filter
is a handy single-stop shop to a bunch of easy sorting and filtering.
The resulting filters on each column are especially useful for filtering OUT rows that are not relevant. For smallish datasets, you can just un-select the values you want to filter out.
One common issue with filters is that they apply globally to a shared document. This is only what you want about half the time. The rest of the time, you DON’T want a filter that one user applies to their view of the spreadsheet to apply for everyone else. For example, if that user is filtering down to just rows that are relevant to them, such as their direct reports.
In this case, you can use a Slicer
to define filters that do not modify the original document for other people.
Pivot TablesPermalink
Think of a pivot table as a GROUP BY
clause in SQL. It’s a general-purpose way to aggregate data and displays things like counts, averages, and sums by one or more dimensions.
First, select the data you want to pivot on and go to Data → Pivot table
.
Then use the editor to Add
rows and values. Here, I’m summing Widgets by Team.
VLOOKUPPermalink
If Pivot Tables are analogous to GROUP BY
in SQL, then a VLOOKUP is like a JOIN
. You can join to data in another sheet in the current Google Sheet document, and you can also join to external sheets.
This is great for at least two use cases:
- You want to treat one sheet as a source-of-truth enum-like mapping, i.e. a translation table.
- You want to reference another dataset either to not repeat data or because you know that the source-of-truth sheet will change regularly, i.e. a list of employees and their current manager.
Note: I always have to look up the syntax of this, and do some debugging. 😉 The key is to remember that you’re looking up the value in the first column of the range.
The arguments of the vlookup
function are:
- The value to search for, i.e.
A1
- Where to do the lookup, again it’s going to look at the first column in this range
- You can provide a range in the local sheet like
A:C
- You can provide a range in another sheet in the current document like
'Filters and Slicers'!A:C
(you must use single quotes if the name of your sheet contains spaces) - You can provide a range in another Google Sheet document like
importrange("https://docs.google.com/spreadsheets/d/1YH6-gXcIix5Pql97nqVaENxN8rUbL-JarOrpnH9DNZT0/edit#gid=1511625096","employees!A:G")
- You can provide a range in the local sheet like
- The index of the value in the range to return
- Returns exact matches only when set to
False
(you probably want this)
Conditional FormattingPermalink
Conditional formatting is just styling cells based on their contents.
Common use cases:
- Use background color as a heat map to denote high/low values
- Use background color to highlight rows that are not filled out, i.e. you’re asking others to fill them in
- Alternating row background colors for readability
Start by selecting a range, and going to Format → Conditional formatting
Example of highlighting empty cells:
Example of a heat map:
ConclusionPermalink
If you use a tool long enough, you may begin to assume that everyone knows it as well as you do. I’m certainly not a spreadsheet master, but I’ve come to realize that some people barely use them at all. For me, they are the primary tool I turn to in many different contexts. It’s been said that spreadsheets could perform the function of 80% of custom software, and I would agree. For me, basic functional spreadsheet knowledge is right up there in terms of utility with writing SQL, or coding itself.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK