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:
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
đź’ˇ Learn moreThe 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:
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.
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.
This year, I created a Google Sheets Advent Calendar, which you can see in action here:
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
This is a guest post from Josh Cottrell-Schloemer.
Google Sheets is an incredibly powerful spreadsheet tool for pulling, processing, and presenting data. But many people don’t realize that you can also use it to build interactive dashboards.
With a bit of creativity we can go from this:
To this:
The skills to build this type of dashboard aren’t difficult to learn and you can get started with a basic knowledge of Google Sheets.
Here’s a walkthrough of the dashboard shown above:
Continue reading Making Google Sheets look less like… Google Sheets
đź’ˇ Learn moreThe BYCOL function in Google Sheets operates on an array or range and returns a new row array, created by grouping each column to a single value.
Here’s a simple example, showing a table of exam scores:
The formula in A8 is:
=BYCOL(A2:D6,LAMBDA(c,AVERAGE(c)))The easiest way to think about using BYCOL is to think “what function can I use on a single column?” and then think of BYCOL as just repeating that operation across multiple columns.
BYCOL passes the input array to a lambda function, which takes an input column, called “c”, and calculates the average value for that column. It loops over each column and returns a row array of average values, one for each column.
đź”— Get this example and others in the template at the bottom of this article.
Continue reading BYCOL Function in Google Sheets – LAMBDA Helper Function