Topics → Member Questions

Includes

50 Tutorials

Free for Members

Better Sheets Member Questions
Questions directly from members answered, for you.

Watch this video to learn how to ask a question you have.

→ Get Sheets Help

These are questions about Google Sheets from members of Better Sheets. Questions directly from members answered, for you. The point of these videos is that one person had a problem, got the solution but also all Better Sheets members can now learn from that one video.

While these are questions FROM Better Sheets members they are all answered by Andrew Kamphey, the resident Google Sheets wizard at BetterSheets.co and you'll get that response from him every time. There's no forum (yet) on Better Sheets. There are searchable directory of Formulas, Tutorials, Templates, and Tools.

If you're not yet a member, and you want to ask a question, there are videos on how to best ask a question. It's best to make a question that is specific but not TOO specific. Not vague, and can be shown. Meaning you have a 1 minute loom recording, or a sheet to share. Sharing a sheet with fungible information is best so I can make a video response.

Don't share private data. Make sure the sheet is abstract enough to get your answer but not specific enough to have any revealing private data.

50 Tutorials

Ken Asks: How can I count true and false? Answer: w/ CountIF()

the answer is if you have a column of true or false you can use the count if statement argument formula. That's just count if, and then you say your range, and then comma, true. Or false, whichever one you want and you'll get a count.

Beginner

COUNTIF()

Can a Google Sheet have DRM?

Are you wondering if you can sell a google sheet and only allow buyers to use the sheet, and not share it? I answer that sort of question, with more questions!

Advanced

Use Row() For Math You Need to Iterate

Create easy to use math iterators with the ROW() formula. It tells you the row your cell is on, but why do you need that? you can iterate math equations! It's neat!

Beginner

ROW()

Iterate Numbers with a Simple Apps Script

You're joining a company and they have some Google Sheets already created, and there's a sheet that has a cell in A1, this number, and you want to iterate it to 3, and B1 is connected to a lot of other stuff, so you don't want to mess with it. All you want to do is iterate a one, one at a time. I think the answer here is an Apps Script.

Beginner

How to Turn Background Color to Text

This tutorial showcases a practical solution to convert visual cues into actionable data in Google Sheets. It's a step-by-step guide on using Google Apps Script to automate the process of reading background colors and converting them into text. The focus is on simplifying data processing tasks and enhancing the utility of spreadsheets for data analysis.

Intermediate

David Asks: About Primary Keys

Create relational databases with index/match. Discover the secret to efficient data management with primary keys! Learn how to sort, filter, and create tables based on unique identifiers in this expert video. Say goodbye to complicated spreadsheets and hello to easy data management.

Intermediate

INDEX() MATCH()

Automatically Copy Spreadsheet Tab to New File with Values Only, No Formulas

Automatically copy a spreadsheet tab to a whole new spreadsheet file, get the url of that new sheet, and make sure the new file is values only. no formulas. A member asked this question and I'm happy to answer it, code it, and let you in on the answer in this short video.

Intermediate

How to Replace Text with a Blank Cell in Google Sheets

Learn the Quick Fix! Replace Text with Blank Cell in Google Sheets Hassle-Free. Say Goodbye to Data Glitches. Super helpful when you need to find and replace in google sheets any text.

Beginner

Sync Many Tabs without ImportRange()

Sync more than two tabs with Apps Script

Advanced

OpenAI API Prompt with Array of Cells

Create a prompt to OpenAI with an array of cells

Intermediate

CONCATENATE()

Rishabh Asks: Conditional Format Whole Row Via Text inside a Cell

Your question is, you have these events and dates and statuses. And you want to turn them green actually you wanna turn them any, some color, the whole entire row based on if in this D column the status is closed.

Conditional Formatting Intermediate

Can I get alerted when a tab name is changed?

Find out in this quick experiment if a tab name can be alerted to the owner of the spreadsheet. Great for owners and operators who want to know if someone is changing the sheet.

How To Duplicate a Tab and Move it to the End in Google Sheets

Learn how to automate creating a template and moving the tab to the far right, or the end of the google sheets tabs.

Intermediate

How to Create a Google Form Automatically

How to create a form programmatically using Google Apps Script. I show you step-by-step how to create a form, set the title, add text items, and set the destination of the form to a specific sheet. This tutorial is perfect for anyone who wants to automate the process of creating forms, whether it's for templates, lead magnets, or recurring forms. No prior coding experience is required.

Intermediate

Scoping Functions in Apps Script

Create multiple functions that run with the result of other functions.

Advanced

Tagging Titles, Create a Comma Separated List of Tags

The video tutorial is about tagging and creating comma-separated lists of tags for a list of titles. The tutorial demonstrates how to use data validation to create a drop-down menu of tags for each title and how to keep track of the number of videos or items in each tag category. The tutorial also shows how to create a comma-separated list of tags for each title using the JOIN function.

Intermediate

TRANSPOSE() COUNTIF() JOIN() FILTER()

Combine Sheets Into One Sheet As It Happens

Jodi asked: ab out Building a sales CRM spreadsheet that has 4-5 tabs at the bottom. Each sales person gets their own sheet and then I want a master spreadsheet that pulls each salespersons Information they enter (customer, job, status etc) into one place.

I’m not sure how to get the master sheet to pull in from the other sheets as they enter the data so that the master sheet shows real-time what’s happening without making one row = a row from another sheet. I just want it to come in one row after the next as it happens.

Other Videos Mentioned in this video:
https://bettersheets.co/tutorials/add-a-timestamp-to-task-lists-without-now-formula
https://bettersheets.co/tutorials/spreadsheet-automation-101-lesson-2-onedit-trigger

Spreadsheet Automation 101: https://bettersheets.co/courses/spreadsheet-automation-101

Intermediate

IMPORTRANGE() SORT()

Zakir Asks: Find First Car in a Row

Find the first value in a row.

Beginner

INDEX() ISBLANK() MATCH() COUNTIF()

Find Column with Header to Bullet Proof your Vlookup

Make your VLOOKUP formulas flexible and your data move-able with this trick.

Intermediate

MATCH() VLOOKUP() HLOOKUP()

FILTER Can Save You From Task Overload

If you have a list of tasks and statuses, the FILTER formula will help keep you from the brink of Overload by allowing you see each status in a different tab.

Great if you're assigning work to VAs or you just became overloaded with tasks in your own task management system.

A great alternative to Conditional Formatting as well.

And a sneaky new little Apps Script that saves the day.

FILTER()

Find a Value in a Range

I'll show you how to easily find a specific value in a range using two powerful formulas: VLOOKUP and INDEX MATCH.

With VLOOKUP, we can search for a value in a column and retrieve information from the same row.

I'll explain how to set it up and why the "false" parameter is important. Then, I'll introduce INDEX MATCH, which allows us to search for a value in one column and retrieve information from another column.

I'll demonstrate how to use it to find a name based on a number and vice versa. This technique is incredibly useful when you need to look up information based on a specific value.

Plus, I'll share a neat trick to make the search even more dynamic. So, let's dive in and master these versatile formulas together! 🚀

Beginner

VLOOKUP() INDEX() MATCH()

Create an Automated Task List

I've created simple automations to make an awesome automatic task list. Add a checkbox when adding a task. Add a timestamp when done. Also emailing every day the top task.

Intermediate

Made a Conference Schedule Better - Member Request!

Go through a fun sheet and make it better.

Advanced

Anika Asks: How to Bring value from other cells | How to Reference an array in a cell

She has a cell, this D one, in which it says, for instance, another array or a row or column in this case, a column, a on sheet one where we're on and she's referencing that from another cell.

Intermediate

INDIRECT() TRANSPOSE() SORT()

How to Count Numbers in a Row

Got this question in the Facebook Group: How to count the number of numbers in a row. I saw two possible solutions mentioned in the comments and wanted to go through why those are okay but there is a much more flexible and better solution.

Beginner

COUNT() COUNTA() COUNTIF() AND() SUM() +1 more

Member Asks: How do I calculate from a set of Form entries?

Use arrayformula to deal with Google form entries on a sheet. Even as they keep getting added.

ARRAYFORMULA()

Get Every Row 5 Times

Find out how to get a formula to get a row, but get it multiple times per row.

Intermediate

What is this Ampersand Doing in Google Sheets?

What is "&" doing in this formula? MONTH(A1&1)
Do you know why this is so cool? It can replace an entire vlookup chart I've been making for years.

DATE() MONTH() CONCAT() VALUE()

Pick a Raffle Winner with Number of Tickets

Pick a raffle winner if you only have the names and number of tickets. And do it in one cell.

Advanced

INDEX() IF() REPT() ARRAYFORMULA() SPLIT() +3 more

Anders Asks: Can I Highlight Whole Row if Certain Columns have text?

Yes, using conditional formatting we can do this.

Conditional Formatting Beginner

Dylan Asks: How to Automatically Delete Rows If Cell Contains Value

Find out how to automatically delete rows based on a cell's value. We do this in Apps Script

Advanced

Kristy Asks How to Get Percentages from Scores

How to figure out a percentage score when our reporting numbers are a formatted in a particular way. In this case we have a score and then a slash and then the total possible score in a single cell. We go through each option of solution and try to derive a solution that is both useful in the exact situation and possibly useful for other problems too.

We find that the Query formula and other solutions are inflexible or time intensive. We want a solution that does actually solve the problem at hand, in a quick way, and also is easy to edit and flexible to solve other problems.

Beginner

IF() ARRAYFORMULA() INDEX() ISBLANK() QUERY() +3 more

Zunaid Asks: How to get Get Meta from URLS? Answer: Use ImportXML()

how would I do the following? In Google Sheets, it's in column A I have a list of URLs. For each of those URLs, I want to pull that URL's featured image and meta description, and put those in columns B and C. Here's how you do it. I just grabbed Wikipedia articles for this example.

Advanced

IMPORTXML()

User Filter a Google Sheet Database based on Dates, Checkboxes, Dropdown!

Search through and filter data to get what you want, when you want it. Tired of sifting through endless rows and columns? Learn how to filter your Google Sheet database with dates, checkboxes, and dropdowns. Simplify your workflow today!

Beginner

IF() FILTER()

How Does Split Work?

We go deep into the syntax and usage of the SPLIT() formula.

Beginner

SPLIT()

Add Team Members to the Quick CRM

We have a question from Rupesh about the quick CRM. He wants to add some team members.

Intermediate

FILTER() IMPORTRANGE()

Anders Asks: Find first and last of filter by date

Useful for calculation or filters by dates.

Beginner

COUNT() INDEX() IF()

Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?

ow do I show the date and time for the last time something was changed in a sheet? So what's interesting is that this This shows you Google Sheets shows an version history up here and shows you the last edit, but sometimes we want to have that data available to us inside of the sheet.

NOW()

Bjarne Asks: Can I show the total number of rows minus the header row?

find out how to show the total number of data rows available in a sheet.

Beginner

COUNTBLANK() SUM() COUNTA()

Alejandro Asks: How do I Sum up Study hours from a Calendar Format?

A member, Alejandro, asks about taking their custom calendar inputs and figuring out study totals for a week. They created a beautiful input and just need to the calculations. I show how to use the data to do the calculations. If you've never used the FLATTEN() formula, this is a good example! And we use double UNIQUE().

FLATTEN() FILTER() SUM() UNIQUE() SORT()

Ben Asks: How Do I Add 1 Month?

Member Ben asks how to add 1 month to a date.

Intermediate

EDATE() TEXT() TODAY() CONCATENATE()

Wallace Asks: How To Find New Names

Got a list of names, need to add new ones to main list.

MATCH() IFERROR() ARRAYFORMULA() IF() ISBLANK()

Ian Asks: How to sort dates by year/month (can't do with filter)

In Excel you actually have these options of sorting by year, by month, by date. But let's do it in Google Sheets.

Pabbly Connect Countdown - "Sheet Improvement!"

Create a countdown timer inside of a google sheet. Unlock the secret to a lifetime deal... TIMEI! Discover their amazing live Google sheet and join the countdown to their exclusive offer. Watch as we recreate and enhance their sheet with stunning colors. Don't miss out on this epic journey. Click now!

Richard Asks: Copy Template Tab and Rename to Date Every Week

Member Richard asks this question. Answer: use Google Script and a Trigger.

How Can I Compare Two Tabs for Differences?

So in this video we're going to compare two tabs and differences. Basically, someone asked actually on AppSumo, someone asked how do I compare two sheets to know what the differences are? This is the answer.

INDEX() IF() ROW() COLUMN() INDIRECT()

Ken Asks: Can We Add Star Ratings?

One common feature that users often request is the ability to add star ratings to their spreadsheets. While this feature is not built into Google Sheets, it is possible to create a star rating system using emojis and data validation.

Beginner

SWITCH() AVERAGE() VLOOKUP()

Benoit Asks: How to Convert Case

How can I use this function, lower, upper, or proper as a conditional formatting? And then the other question is, how can I use this to apply on a bunch of cells without having to double the cells?

PROPER() UPPER() ARRAYFORMULA() LOWER()

How To Select an Image based on Text in a Dropdown Menu

This video is a tutorial on how to create a dynamic weekly planner in Google Sheets with drop-down menus that change images based on selections. Here are three key takeaways:

Avoid Hidden Rows/Columns for Clarity

The speaker advises against using hidden rows or columns in Google Sheets. They suggest that this practice often leads to confusion and inefficiency, especially when sharing sheets with others. As an alternative, they recommend placing such data on a separate sheet.

Dynamic Dropdown Lists and Image Insertion

The tutorial demonstrates how to create dropdown lists where selecting a subject (e.g., English, Math, Study) changes the image displayed in another cell. This is achieved through data validation for dropdowns and using VLOOKUP or INDEX MATCH functions to link the chosen subject to its corresponding image.

Flexibility and Ease of Updates

The approach shown allows for easy updates and additions to the planner. For instance, adding new subjects or changing images is straightforward. The speaker emphasizes the versatility of the INDEX MATCH function over VLOOKUP, as it offers more control and adaptability, particularly when rearranging data.

The video also touches on the specifics of resizing rows for better image visibility, the importance of unique identifiers for dropdown options, and the benefits of expanding dropdown lists for future additions.

INDEX() MATCH() VLOOKUP()

Add A Timestamp to Task Lists (without Now Formula)

How to add a timestamp to done tasks. Learn how to easily add timestamps to your done tasks! Say goodbye to manual formatting and hello to this simple code. Click now to save time!

Advanced

NOW() FILTER()