Blog

11 New Analytical Functions In Google Sheets For 2023

Coming hot on the heels of last year’s batch of new lambda functions, Google recently announced another group of new analytical functions for Sheets.

Included in this new batch are the long-awaited LET function, 8 new array manipulation functions, a new statistical function, and a new datetime function.

Let’s begin with a look at the new array functions. The LET function is at the end of the post.

  1. TOROW Function
  2. TOCOL Function
  3. CHOOSEROWS Function
  4. CHOOSECOLS Function
  5. WRAPROWS Function
  6. WRAPCOLS Function
  7. VSTACK Function
  8. HSTACK Function
  9. MARGINOFERROR Function
  10. EPOCHTODATE Function
  11. LET Function

Continue reading 11 New Analytical Functions In Google Sheets For 2023

How To Create A Google Sheets Drop-Down Menu

In this post, we’ll look at how to create a Google Sheets Drop-Down Menu. Here’s an example of a drop-down menu to record the status of deals in a real estate deal pipeline:

Google Sheets Drop-Down Chips

Drop-down menus are great for data entry and making your Sheets dynamic.

In this post, we’ll explore both of these techniques with examples.

But first, let’s see how to create a Google Sheets drop-down menu.

Continue reading How To Create A Google Sheets Drop-Down Menu

2022 In Review And A Look Forward To 2023

2022 Family Photo
Family at Chimney Rock State Park, NC, October 2022

This is Annual Review number eight!

Eight years working for myself. That’s as long as my first career as a forensic accountant. On balance, I prefer this one.

2022 was my most successful year as a creator. I beat last year’s revenue, sent my newsletter every week, grew my email list to > 50k, and published more content than ever before on this site.

That being said, 2022 was also a strange year. A transition year after two difficult years of the pandemic. We moved forward, but 2022 still had its fair share of ups and downs.

Let’s begin with a review of 2022:

Did I Meet My 2022 Goals?

I set fewer work goals for 2022, and instead focused on a handful of bigger goals:

  • Create 3 new video courses – Not quite. I launched 2 new video courses in 2022.
  • Run cohort 3 of Pro Sheets – No. I did not run a cohort course this year.
  • Send my Google Sheets Tips newsletter every Monday – Yes, it’s one of the favorite parts of my job.
  • Hit 60k newsletter subscribers – Close. I’m at around 54k newsletter subs now (although I’m overdue a list clean).

What about other goals?

I always set some non-work goals too, to ensure I live intentionally. Here’s how 2022 played out:

  • Twelve challenge walks – Yes, I did a lot of great hiking this year and stopped counting after a while. I climbed my local mountain 20 times, walked a marathon distance in a day, and went backpacking along the Appalachian Trail.
  • Family trip to the UK this summer – Yes, we had a fantastic trip!
  • Complete a century bike ride (100 miles) – No, unfortunately. I failed on this one although I had some memorable bike rides of shorter distances.
  • Have another healthy year – Yes, mostly, although it feels like we’ve been stuck in a cycle of sicknesses this fall. We rounded out the year with COVID in December, thankfully it was mild.
  • 10 nights camping this year – Nearly! I finished the year on 7, which is more than 2019, 2020, and 2021 combined. I want to increase this in 2023.
  • Read 20 books – I read 16 books this year.
  • Weekly brainstorming hike with my wife – Yes, when we were healthy. We didn’t manage every week because of illnesses, injuries, etc.

2022 Highlights

1) New Courses

In May 2022, I launched The QUERY Function in Google Sheets course.

The QUERY Function in Google Sheets

This was a really fun course to create because the QUERY function is such an interesting and complex function to play with. It’s a rabbit hole that goes deeper and deeper the more you look.

It’s been a popular course, with 743 students enrolled so far this year.

In October this year, I launched another course, called: Lambda Functions 10-Day Challenge

Google Sheets Lambda Functions 10-Day Challenge Course

This free course covers the 10 new functions introduced to Google Sheets this year.

So far, 2,873 students have enrolled in this course.

2) Improving Business Infrastructure

I spend a considerable portion of Q1 and Q2 working on stuff behind-the-scenes, to get my website and email systems in the best possible shape going forward. Specifically:

  • I migrated from MailChimp to ConvertKit for my emails, and I’m enjoying it a LOT more.
  • With the demise of Google Universal Analytics, in favor of GA4 (which seemed overly complex for my needs), I switched to the simpler Fathom Analytics. It’s perfect for me because I don’t like unnecessary complexity.
  • I made major improvements to the website speed, mostly removing redundant JavaScript code and old plugins, and optimizing large images.
  • Improvements to the onboarding process and site UX.
  • Improvements to SEO on the majority of the posts

3) Google Sheets Tips Newsletter

I sent my weekly Google Sheets newsletter every Monday, except for Christmas week.

It’s the backbone of my business and I love receiving responses from readers each week.

I plan to write a newsletter every week again this year.

4) Website Content

Last year, I listed the website under the challenges category, on account of the falling traffic numbers.

To address this, I improved my site speed (see point 2 above) and I published more content than in any previous year.

Blog Posts Published Per Year

This year, my favorite posts to research and write were:

I also enjoyed writing these posts:

Website traffic has been relatively steady all year, hovering around 300 – 350k pageviews per month. I’m pretty happy with where this stands, especially after seeing some decline in traffic last year.

Fathom Analytics website traffic
My site traffic in the Fathom Analytics dashboard. The first full month was March.

5) Community & Partnerships

I’m grateful and honored to be part of the Google Developer Expert Community for another year. I’ve learned so much through this group and made some great friends along the way.

Google Developer Expert Award

This year, I had meaningful and enjoyable collaborations with Teachable, Coefficient, and Measure Summit. Thank you and I look forward to working together again in the future!

6) Founders Summit

Although I didn’t attend any conferences myself this year, the kids and I did accompany my wife to the Founder Summit Conference in Asheville, North Carolina. It was a family-friendly event, held at a resort center deep in the woods. We were there during peak fall colors, when the woods put on an incredible show:

Wintergreen Falls, Dupont State Forest, NC
Wintergreen Falls, Dupont State Forest, NC

My wife had an amazing week talking with other founders and generating ideas for her new company, They Got Acquired (a media and data company covering company acquisitions in the $100K-$50M range).

Meanwhile, I had a fantastic few days with the kids taking them climbing, hiking, doing art projects, and meeting other families. I was really proud of both boys for taking a real crack at the climbing wall.

Rock Climbing Photo

The conference also provided childcare, so I had some time myself too. I went hiking with my good friend, and fellow course creator, Kevin of Data School, and I did some brainstorming for my business.

7) Non-Work Highlights

Watching my boys grow up and making memories with them. Parenting is the most rewarding (and by far the most challenging!) thing I’ve ever done.

We spent 2.5 weeks in the UK this summer. My brother and his family came from Australia too, so it was the second time all the cousins got together. We had a fabulous trip and spent good quality time with my family. We stayed in the Peak District National Park so we did plenty of hikes and sightseeing. My brother and I did a 26 mile walk along the edges, and I had a great day out with one of my oldest friends, Alistair, scrambling in the Dark Peak.

2022 was a much better year for adventures than the past few years. Things are easier now that my boys are older.

There were lots of standout local adventures this year. The highlights were:

Another hightlight this year has been watching my wife launch and grow her new business: They Got Acquired

2022 Challenges

Burnout

Burnout? Depression? I don’t know where one ends and the other begins, but I’ve been running on an empty tank at times this year, especially in the second half.

At times, it was a struggle to create new work and I didn’t have the energy to run the cohort course in 2022.

The cause? A combination of macro- and micro-factors.

Everything from the doom-and-gloom playing out on the world stage, to the exhaustion of parenting in the ongoing pandemic, to the incontrovertible truth of aging.

In my head, I’m still that 30-year-old athlete free to climb mountains, run ultra-marathons, and cycle across countries, but my creaking, middle-aged body tells a different story. Plagued with knee injuries and having much less time for exercise than at any previous point in my life, my fitness has slowly started ebbing away in the past few years. I’m resolved to work hard this year to claw back some of that lost fitness.

Have I got through the burnout? Yes, I believe so.

I’m excited for 2023, and to get stuck into work again (whilst also keeping my fingers crossed that we avoid getting sick and the boys can stay in school). Doing more outdoor activities away from my computer this fall and winter helped tremendously, so I’ll keep doing that.

Looking Forward To 2023

2023 Work Goals

Like last year, I’m going to set a handful of big goals to focus on exclusively:

  1. Create 2 new video courses (the first of which will be a new Apps Script course in Q1)
  2. Send my Google Sheets Tips newsletter every Monday
  3. Hit 70k newsletter subscribers

Beyond that, I have ideas for more video courses, potentially building a members community, running my live cohort course again, writing a Google Sheets book, or something else entirely!

I have a plan for Q1, but the rest of the year is more open. I have lots of ideas but don’t have a concrete direction yet. So, one of my tasks in Q1 is to figure out the plan for the remainder of the year.

Let me know in the comments if any of these ideas resonate, or if there’s anything you’d like to see here.

Other 2023 Goals

This year, I focused most of my outdoor energy on hiking projects. In 2023, I want to shift to doing more biking and paddle boarding.

  1. Have another healthy year and get fit.
  2. Since I didn’t tick it off last year, I’ll keep the century ride (100-mile ride) on this year’s list. I last did this in 2014 and I want to get back to that level of fitness on the bike again.
  3. Multi-day adventures locally: backpacking, bikepacking, and/or paddle boarding
  4. A C&O canal tour with my boys for 5 or 6 days
  5. 25 nights camping, with at least 1 night out each calendar month
  6. Weekly brainstorming hike with my wife
  7. Read 20 books

Thank You

As always, I like to finish by saying thank you for your support.

Thank you for reading my articles and newsletters, for watching my video courses, or for attending my webinars. Thank you for this opportunity to help you get better at working with Google Sheets and Apps Script.

2022 was a great year on balance and I’m excited for 2023.

Best wishes to all of you for 2023! See you around.

Previous years

XMATCH Function in Google Sheets

The XMATCH function in Google Sheets is a new lookup function in Google Sheets that finds the relative position of a search term within an array or range. It’s an evolution of the original MATCH function.

Here’s a simple XMATCH function that finds the position of the search term “Cho Oyu” in the list of the highest mountains in the world:

=XMATCH(E2,A2:A15)

In the Sheet:

Xmatch Function Google Sheets Simple Example

And here’s how it works:

It looks for the search term from cell E2 (“Cho Oyu”) in the range A2:A15, then returns the position of the search text within this range. Note that the result is relative to the range, irrespective of the row number.

Xmatch Function Google Sheets Explainer

Notice how, unlike a regular MATCH function, you don’t have to specify the “0” search type for an exact match. It chooses the exact match, which is by far the most common use case, by default (in contrast to the MATCH function where you have to add the 0 to explicitly confirm exact matching). More on the search types below.

🔗 Get this example and others in the template at the bottom of this article.

Continue reading XMATCH Function in Google Sheets

Google Sheets Advent Calendar

This year, I created a Google Sheets Advent Calendar, which you can see in action here:

Google Sheets Advent Calendar

It was a fun project with some interesting techniques, which are explored below.

You could easily modify it for your own example, or use these techniques in different scenarios.

Plus, if you’re too cheap to buy a physical advent calendar, this lets you enjoy the fun of opening a door each day to reveal something, but for free!
Continue reading Google Sheets Advent Calendar