Learn how to use a spreadsheet

Learning how to use Excel / google sheets is usually considered unnecessary in the engineering world. You could always spin up a jupyter notebook and crank out code in python using pandas, right? Why resort to the same tool non-engineering folks have been using for decades?

This line of thinking gets in the way of using the right tool for the job. There is wisdom in other peoples habits.

Microsoft Excel is one of the most innovative tools ever developed on a computer. It bought along the idea of reactivity and two-way binding to the masses, and people have done absolute miracles just off spreadsheets. Microsoft Excel powers the most day-to-day finance probably and I could wager is the database of the largest population on planet earth. I see people use it as a task manager, habit tracker, financial planner, status log of events, OKR planner, year planner and many other use cases.

My suggestion is that the next time you get something mathematical that needs to be done quickly. Take a minute and explore how that should be done on a spreadsheet.

Use this as a lookup starter on what you need to learn to get started.

Filtering

You'll have to sort and filter in a sheet. Filters are used for this. Control/command + A to select all. From the menu, select Data > Create a filter.

Pivot

Group by in database language. Select the entire sheet and go to Insert > Pivot table. This will pop up a nice interface on the sidebar. Select the rows and columns, if you want to group by and do some math, add values after adding rows.

VLOOKUP

Joins in databases. Check this video out.

Reference another sheet

Usually there is a constants section, where all the variables are stored. Either you can have it on the same sheet or in another sheet.

If you happen to keep it in another sheet like me, then to reference a cell from that sheet use the exclamation notation. constants!B1 will reference the B1 cell from the sheet named constants.

Cumulative sum

pandas: .cumsum()

The alternative in Google sheets is by using relative referencing. A reference is done in sheets by using a $. For example, if you want to sum up two cells, the formula would be =SUM($C1, $C2).

Now if you have a column of values you want to run a cumulative sum on, then the formula would be =SUM($C$1, $C2). This makes sure that the second argument is relative to the number at which the formula is being run. So as you drag along the formula, it keeps summing up from C1 to the relative cell you are running the formula at. Hence, getting a cumulative sum. The dollar helps you make sure that it's not also incremented relatively like the second C2.