In this episode, I discuss how to calculate trends over time in Excel for the purposes for forecasting future values. I reference an old post about calculating trends where someone recently left a comment about the counter-intuitiveness on calculating averages of changes in your values. In order to follow along with this episode, I would […]
The post Revisiting Calculating Average Trends Across Time Periods in Spreadsheets appeared first on .
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: [crayon-5e27f05f77883647697834/] […]
The post Dear Analyst Episode 22: Calculate win streaks for a pool of players in Google Sheets appeared first on .
This is my talk from Webflow’s No-Code Conference that took place on November 13th, 2019 in San Francisco. The title of my talk was called Building No-Code Tools and Applications from Spreadsheets. The slides from my presentation are on SlideShare here. Themes from the talk This was my first time talking about my experience with […]
The post Dear Analyst Episode 21: Building No-Code Tools and Applications from Spreadsheets appeared first on .
A few weeks ago I had the opportunity to teach a week-long data analytics course through General Assembly. The course was taught entirely online using Zoom. I discuss some of the topics the students learned in the class, and what the experience was like teaching an online class in real time. The topics we covered […]
The post What it’s like teaching a week-long online data analytics course appeared first on .
A bit of a click-baity title, but I wanted to talk through some of the signs you know you’re using too much Excel. If you are worried about a friend who is using too much Excel, ask him or her if they recognize any of these signs. This is an episode based on a blog […]
The post Dear Analyst Episode 19: Top 5 Signs You’re Using Too Much Excel appeared first on .
Range: Why Generalists Triumph In A Specialized World by David Epstein came on my radar after listening to another podcast. Thought the premise was interesting in that people who “dabble” in various interests and hobbies end up excelling (no pun intended) in life and have a more rewarding career. I read a few excerpts that […]
The post Excerpts from Range by David Epstein and financial modeling appeared first on .
New Excel feature alert! Probably one of the biggest announcements in a long time from the Excel world: a new function to supplant VLOOKUP and HLOOKUP. What is it you ask? The XLOOKUP function. Summary of XLOOKUP benefits I discuss some of the benefits, implications for new and existing users of Excel, and also some […]
The post Dear Analyst Episode 17: Excel introduces new feature XLOOKUP appeared first on .
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.” […]
The post Dear Analyst Episode 16: The New York Times’ data bootcamp appeared first on .
I read a few excerpts from Superforecasting: The Art and Science of Prediction by Philip E. Tetlock and Dan Gardner. What does it mean to be a good forecaster? How can we take lessons from the book to be better modelers? I just started reading the book but already have learned so much about forecasting, […]
The post Excerpts from Superforecasting: The Art and Science of Prediction appeared first on .
This is probably the most click-baity title for a blog post/podcast about Excel, but we don’t get many of these in the Excel world, right? It all started in 2002 with the Dilemma music video by Nelly featuring Kelly Rowland, and Kelly decided to send a text using Excel. The most important text in the […]
The post How Destiny’s Child put Excel on the map and syncing data between Coda docs appeared first on .
In episode 12, I talked about how you can use the IMPORTRANGE() function in Google Sheets to import data form a source Google Sheet to a target Google Sheet. In this episode, I get a little more technical and discuss how you can import data using Google Apps Script. If you want to read the […]
The post How to sync data using Google Apps Script in Google Sheets appeared first on .
Have you ever wanted to share data from one Google Sheet with another Google Sheet? Maybe you are a freelancer and have multiple clients, and keep all the payment data in one Google Sheet but need to send updates to your clients without sharing all your clients’ data. In this scenario, setting up one individual […]
The post How to import data from one Google Sheet to another appeared first on .
Google Sheets or Microsoft Excel? An age-old question since Google Sheets came onto the scene in 2006. Many saw Google Sheets just as a copycat of Excel that lacked most of Excel’s features. Over the years, Google Sheets has caught up with Excel’s many features including the number of formulas, PivotTables, and even macros. To […]
The post Should you ditch Microsoft Excel for Google Sheets? appeared first on .
Given that this is the 10th episode of the Dear Analyst podcast, I thought it would be relevant to reflect on how I got started with KeyCuts and joined various Excel communities. I never thought I’d make it to 10 episodes, but here we are! Excel communities Like many online communities, I got started with […]
The post Why I started contributing to the Excel community appeared first on .
If you want to count the number of cells with blank values in them, you may think it’s as simple as using the COUNTBLANK() formula. Usually you do this if you want to know how much data is missing in your data set. Say you have a list of 10 students, and if a student […]
The post Count the number of blank values in a range in Excel appeared first on .
What are some best practices for data analyst working in the medical field? I review this blog post from the Tableau blog by Barry P. Chaiken, MD about the type of analytics used in the medical field, and the trust that must be garnered between the analyst and the physicians. An interesting aspect of doing […]
The post Best practices for data analysts in the medical field appeared first on .
Should you use VLOOKUP or INDEX/MATCH in Excel? What are the pros and cons of each method? When you lookup data in Excel, you have the option to use either VLOOKUP or a combination of INDEX and MATCH functions. There is a huge debate over which method to use, and I break down the pros […]
The post The debate over VLOOKUP versus INDEX/MATCH in Excel appeared first on .
This is my take on the a blog post written on the Mode blog: How to Develop the Five Soft Skills That Will Make You a Great Analyst. These soft skills are invaluable for all analysts and as Derek mentions in the blog post, helps you move up in your career. The most important soft […]
The post Developing the 5 soft skills to be a good analyst appeared first on .
What are the best ways to learn Excel? How do I get good at Excel? I’ve been asked this question many times in person or online, and even wrote some answers online. I discuss not only the best ways to learn Excel, but also what the core concepts you should learn to be the most […]
The post The best ways to learn Excel appeared first on .
Excel tables are a really powerful feature, but there are pros and cons to the feature depending on if you are an advanced or beginner Excel user. From what I’ve seen, most people who use Excel tables just to get the nice color formatting on the header row and the alternating banded rows color formatting. […]
The post The pros and cons of using Excel tables appeared first on .