You may be filtering and sorting a big dataset in a Google Sheet and want to see that dataset in another Google Sheet without having to copying and pasting the data each time the “source” data is updated. To solve this problem, you need to somehow import the data from the “source” worksheet to your […]
PivotTables have been on my mind lately (you’ll see why in a couple weeks). An issue you may face with PivotTables is how to change the source data for a PivotTable you’ve meticulously set up. You have some new data being added to your source data, and you have to change the PivotTable source data […]
When I worked in FP&A, I discovered that VBA could automate a lot of tedious tasks I was doing in Excel. From creating charts to formatting data, I realized that there possibilities with VBA were endless. As I started using Google Sheets more, I found that Google Apps Script offers similar functionality to extend what […]
In Excel or Google Sheets, text manipulation is usually associated with data cleaning, data cleansing, and data transformation. Sometimes your data is “dirty” and needs to be categorized in a different way or you need to “extract” a piece of text from a another piece of text. In this example, we use a combination of […]
The QUERY() function in Google Sheets gives you the ability to quickly filter and sort your data similar to how you might get data from a database. If you write SQL queries, the QUERY() function feels easy and natural to use. There are a few caveats as I discuss in this episode. If you want […]
I worked on a “small” side project recently to sync data between Google Sheets and tables in Coda. The full blog post tutorial is here, and the GitHub repository is here. I started using Google Apps Script last year and it’s a super powerful way to connect different apps you use in the G Suite […]
Have you ever wondered what an “array-entered formula” is? It’s an intermediate/advanced concept in Excel but in late 2018, Microsoft released dynamic array functions and formulas that “spill” into the cells below your current cell with a function. This makes writing formulas easier and less prone to human error, but there are some tradeoffs to […]
This spreadsheet tip is based on a question I get asked all the time when I teach (well taught) Excel at in-person classes: How do I fill a formula down to the last row of my data set without over-shooting the last row with keyboard shortcuts? This problem occurs with larger data sets where you […]
If you are by your computer, you may want to open this Google Sheet to understand the example discussed in this episode. I walk through a rather long formula involving the FREQUENCY(), COLUMN(), MAX(), and the ARRAYFORMULA() functions in Google Sheets. Here’s the full formula below to calculate win streaks in the Google Sheet: =ARRAYFORMULA(MAX(FREQUENCY(IF(B2:P2=”W”,COLUMN(B2:P2)),IF(B2:P2=”L”,COLUMN(B2:P2))))) […]
A few months ago, The New York Times’ Digital Transition team headed by Lindsey Cook published a blog post about a data bootcamp they led for their reporters and editors. The New York Times frequently publishes amazing data visualizations, and it’s awesome to see this newsroom pushing their reporters into the world of “data journalism.” […]
Excel for the Real World I studentThank you a lot! This video helped me so much with understanding excel. I will move on to the next videos of this series soon.
