![Ben Collins](/img/default-banner.jpg)
- 45
- 1 333 937
Ben Collins
United States
Приєднався 29 жов 2009
Learn how to master Google Sheets so you can work more effectively with your data.
I create online courses that teach you data analysis and automation, using Google Sheets and Apps Script. Over 50,000 professionals from 1,000+ companies have enrolled in my training courses, including employees from PwC, Netflix, Verizon, Google, Capital One, and other Fortune 500 companies.
Google recognized me as a Google Developer Expert for Google Workspace Technology in 2019.
📩 Newsletter
Join 50,000+ others and get my free, weekly Google Sheets Tips newsletter. It's your Monday morning espresso, in spreadsheet form:
www.benlcollins.com/google-sheets-tips/
I create online courses that teach you data analysis and automation, using Google Sheets and Apps Script. Over 50,000 professionals from 1,000+ companies have enrolled in my training courses, including employees from PwC, Netflix, Verizon, Google, Capital One, and other Fortune 500 companies.
Google recognized me as a Google Developer Expert for Google Workspace Technology in 2019.
📩 Newsletter
Join 50,000+ others and get my free, weekly Google Sheets Tips newsletter. It's your Monday morning espresso, in spreadsheet form:
www.benlcollins.com/google-sheets-tips/
Count Specific Days Between Two Dates in Google Sheets
In this video, you'll learn how to quickly count days between two dates in Google Sheets, using the NETWORKDAYS and NETWORKDAYS. Afterwards, you'll be able to easily answer questions like "How many Mondays occur in February?". Knowing these functions will make project planning that bit easier.
📚 Additional resources:
- Template to follow along: docs.google.com/spreadsheets/d/1n1hqPhtE6HPMMfRJ10j-6E0GgmX_DJN6oCJg7FNFxe0/edit?usp=sharing
✉️ Google Sheets Tips Newsletter, my free weekly newsletter:
www.benlcollins.com/google-sheets-tips/
🎓 Join 68,000+ professionals in my courses: courses.benlcollins.com/
#googlesheets #googlesheetstutorial #spreadsheet
📚 Additional resources:
- Template to follow along: docs.google.com/spreadsheets/d/1n1hqPhtE6HPMMfRJ10j-6E0GgmX_DJN6oCJg7FNFxe0/edit?usp=sharing
✉️ Google Sheets Tips Newsletter, my free weekly newsletter:
www.benlcollins.com/google-sheets-tips/
🎓 Join 68,000+ professionals in my courses: courses.benlcollins.com/
#googlesheets #googlesheetstutorial #spreadsheet
Переглядів: 1 459
Відео
How To Create Formula Pie Charts in Google Sheets
Переглядів 1,9 тис.6 місяців тому
In this video, you'll learn how to create miniature pie charts inside a single cell in Google Sheets. Using a combination of modern function - BYROW, LAMBDA, and IMAGE - you'll create a named function that can be easily used in other Sheets. 📚 Additional resources: - Template to follow along: docs.google.com/spreadsheets/d/1jPv_m3PoHE6hkw0fuCOdX-du52F39nBVSyevhwuBBOI/edit?usp=sharing - Formula ...
How To Add Superscript And Subscript Characters in Google Sheets
Переглядів 14 тис.6 місяців тому
In this tutorial, you’ll learn how to use create superscript and subscript characters in Google Sheets. Unfortunately, there is no built-in format option for superscript and subscript characters like there is in Google Docs, so we have to use alternative methods. This video covers three different ways to create these special characters. 📚 Additional resources: - Template to follow along: docs.g...
How to use the powerful MAP Function in Google Sheets
Переглядів 5 тис.6 місяців тому
The MAP function in Google Sheets is a powerful function for working with ranges (arrays) of data. It takes array(s) of data as an input and "maps" each value to a new value based on a custom LAMBDA function. MAP is a more modern, functional approach to array formula type problems. 📚 Additional resources: - Template to follow along: docs.google.com/spreadsheets/d/1U-fFTxXlDAdastdtYk5UAvGnleeJ9k...
Smart Chips in Google Sheets 🧠
Переглядів 7 тис.6 місяців тому
Learn how to use Smart Chips in Google Sheets. Smart Chips allow us to add richer information to our Sheets, beyond the standard data contained in cells. They help us work more seamlessly by bringing information from external sources into our Sheets. 📚 Additional resources: - The Complete Guide to Smart Chips in Google Sheets: www.benlcollins.com/spreadsheets/smart-chips-in-google-sheets/ - Dro...
My Favorite Google Workspace Workflow! Forms ➡️ Sheets ➡️ Slides
Переглядів 1,9 тис.6 місяців тому
In this video, I share one of my all time favorite Google Workspace workflows: Google Forms ➡️ Google Sheets ➡️ Google Slides It's a fantastic workflow for any kind of survey work. It's easy to setup and seamless to use. After watching this video, you'll know how to set it up so that new responses flow through to your presentation charts in Slides without manually copy-pasting new copies of you...
The Chess Game of the Century in a Single Google Sheets formula
Переглядів 1,1 тис.6 місяців тому
In 1956, a 13-year old Bobby Fischer announced his chess genius to the world, winning one of the finest games in chess history - a game Chess Review called “The Game of the Century“. In a game full of beauty and surprise, Fischer overcame his much older, more experienced opponent, the International Master Donald Byrne. Learn how I used the MAKEARRAY, LET, CHAR and other functions to create a si...
How to create Barcodes In Google Sheets
Переглядів 8 тис.7 місяців тому
In this video, you’ll learn how to create barcodes in Google Sheets in 2 easy steps. Barcodes are a way to represent data in a visual way that is readable by machines. Typically, they consist of thick and thin lines with varying widths between them. They’re super easy to create in Google Sheets as you’ll see in the video. 📚 Additional resources: - Template to follow along: docs.google.com/sprea...
How To Filter Dates in the QUERY Function
Переглядів 2,8 тис.7 місяців тому
Learn how to filter dates in the QUERY function. In this video tutorial, you'll learn how to use the "date" keyword in your WHERE clause filters, so you can use the QUERY function to return data based on dates. 📚 QUERY Function Resources: - www.benlcollins.com/spreadsheets/query-dates/ - www.benlcollins.com/spreadsheets/google-sheets-query-sql/ 🗂 QUERY Function Language Resource: - developers.g...
Common Formula Errors in Google Sheets and How To Fix Them
Переглядів 7 тис.7 місяців тому
Understanding formula errors in Google Sheets is a crucial step to formula mastery. In this video we look at all the different error types in Google Sheets, including what causes them and how to fix them. In addition, we'll learn about formulas that help you identify errors and how you can use conditional formatting to highlight errors. 📚 Additional resources: - Template to follow along: docs.g...
Advanced Conditional Formatting in Google Sheets
Переглядів 20 тис.7 місяців тому
In this video, we do a deep dive into the advanced features of conditional formatting, focussing on the "Custom formula is" rule. Learn how to use conditional formats across entire rows when a condition is met. Then we'll look at conditional formats down columns, identify duplicate entries, multi-condition rules, date rules, search rules, look ups to other sheets, and more. 📚 Additional resourc...
Basic Conditional Formatting in Google Sheets
Переглядів 1,7 тис.7 місяців тому
In this video, we do a deep dive into the essential features of conditional formatting. We cover all the rules except for "Custom formula is" (check out the advanced video for that one!) as well as how to copy rules and even turn off the conditional rules but keep the formatting. Chapters: 0:00 - Introduction 1:28 - Empty / Not empty 3:48 - Text conditions 5:55 - Date conditions 9:15 - Number ...
Fill Down Blank Cells in Google Sheets
Переглядів 10 тис.7 місяців тому
In this video, we'll see how to fill blank rows based on data contained in cells above. We'll see how to do it manually with shortcut keys, how to use filters and formulas to scale, and finally how to create a dynamic formula to account for expanding data. Learn how to save time and master this essential data cleaning technique today! 📚 Additional resources: - Template to follow along: docs.goo...
The Ultimate Guide to Creating Lists in Google Sheets
Переглядів 2,9 тис.7 місяців тому
In this video, we'll create 30 different types of list, including numbered lists, date lists, text lists, emoji lists, and row count lists. We use the SEQUENCE function, array formulas, and even some of the modern LAMBDA style functions to create the lists. 📚 Additional resources: - Template to follow along: docs.google.com/spreadsheets/d/1MT2eHlnUIQ3n20tr9U6NdU7F3ivBIUqaOC9g0mUIdJA/edit?usp=sh...
How to Use XLOOKUP in Google Sheets
Переглядів 3,5 тис.7 місяців тому
The XLOOKUP function is the most powerful and flexible spreadsheet lookup function. In this tutorial, you’ll learn how to use the XLOOKUP function in Google Sheets with 7 examples, from simple to complex. We’ll look at how to return values to the left of the search column, how to do approximate matching, how to use the built-in error handling, do wildcard matching, and much more. 📚 Additional r...
Create QR codes in Google Sheets, Docs, and Slides
Переглядів 10 тис.7 місяців тому
Create QR codes in Google Sheets, Docs, and Slides
How to build your own RSS Reader in Google Sheets
Переглядів 1,3 тис.7 місяців тому
How to build your own RSS Reader in Google Sheets
Elevate Your Spreadsheet Dashboards With This One Simple Technique
Переглядів 1,2 тис.7 місяців тому
Elevate Your Spreadsheet Dashboards With This One Simple Technique
How To Use Column Names in the QUERY Function (Google Sheets Tutorial)
Переглядів 5 тис.8 місяців тому
How To Use Column Names in the QUERY Function (Google Sheets Tutorial)
The QUERY Function Versus Pivot Table: Similarities + Differences + When To USE
Переглядів 4,2 тис.2 роки тому
The QUERY Function Versus Pivot Table: Similarities Differences When To USE
Automated ConvertKit List Growth Report In Google Sheets
Переглядів 7642 роки тому
Automated ConvertKit List Growth Report In Google Sheets
VLOOKUP Function with TRUE for Approximate Matching
Переглядів 4,9 тис.3 роки тому
VLOOKUP Function with TRUE for Approximate Matching
The New Google Apps Script IDE (2020)
Переглядів 19 тис.3 роки тому
The New Google Apps Script IDE (2020)
Google Tables: First Impressions and How I Use It
Переглядів 80 тис.3 роки тому
Google Tables: First Impressions and How I Use It
Google Sheets Formula Tips & Techniques
Переглядів 23 тис.4 роки тому
Google Sheets Formula Tips & Techniques
How to remove duplicates in Google Sheets
Переглядів 148 тис.5 років тому
How to remove duplicates in Google Sheets
Google Sheets Filter Function - 8 actionable examples
Переглядів 64 тис.6 років тому
Google Sheets Filter Function - 8 actionable examples
Great tutorial! Thanks for posting this. Quick question. Is it possible to apply border as a formatting style, instead of, say cell color. E.g. I have a GOOGLEFINANCE function that pulls "all" historical fields for a given stock over a period of a month. The resulting 22x6 (no weekend rows) table for August would be easier to read if the Friday row ( =WEEKDAY($A2:$A)=6) ) had buttom border applied. I'm not able to work this out.
I have improvised Ben's approach to simplify the query formula entry by creating two helper rows with the header names and the "Col#" reference derived from the XMATCH equation. Instead of calling out individual headernames in the select statement, the formula simplifies to: =query(RangeName,"select "&TEXTJOIN(", ",TRUE,A2:F2)&"") where A2:F2 stores the "Col#" results.
Ben Collins, you are amazing! You have provided the most elegant, simplest, and easy to implement workaround to one of the epic fails in gSheets. I hope that you can influence Google Product team to address this obvious blunder where one cannot simply query using header names.
AWESOME!!!! THANK YOU o/
This was really useful! I've been using formulas and wish I'd known this sooner. Where can I find the following video that you mention, where you explain how to use pivot tables? Thanks!
Great tutorial, this helped simplify a rostering system I built for a non-profit - well done!
Is there a way to create electronic zip codes?
is it possible to order by a column and if 2 values are the same go by a second column?
Is there a way to highlight a cell with a certain number for example... anytime you enter 47 or 79 or 98 the cell will turn a certain fill color?
this is great! thank you so much for your video. Question - what's the frequency it refreshes with new information and how can I keep the old information? or will it disappear when it refreshes? thanks!
god bless!
QUESTION: USE CASE: I have a bunch of old google sheets from the pre-smart chip era. They all have the same data/cell format. They all contain customer data of clients who purchased services in the past. GOAL: I'd like to extract data from these and compile into a customer database. QUESTION: Can I use smart chips to help me extract the data and populate the new database?
Very helpful and simple. Thank you!
It's interesting, the best the content, the lowest the number of the subscribers. Your sparkline hour was priceless :)
Thanks! 🙏
Hi , is there a way where you can email the qr code as a png instead of putting it in a docs file?
Great, this helped me create this crazy function and jus tstarting =QUERY(Invoices_Extracted_on_2024.07.21!A1:L,"SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header)&" LABEL"&" Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&"'Disciount Amount', Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&"'Paid sum'") It's a SHAME, the query function doesn't return Table header if you refer to the TABLE by name, I still have to use Range :/
aamazing thanks
Thank you so much for such useful video!
Thank you! I'm amazed at the number of wrong answers I found first: all of them suggesting that Format > Text > Superscript is a thing; it isn't.
Thank you! is there any way to export the image?
As I recall, It has a built in function, go to format then text then choose what you want!
In Google Docs, yes. In Google Sheets, no.
Thanks
This is SO cool! Thank you for this tutorial! You made it so clear and answered a question I've been having for so long! It felt so good to automate this in my spreadsheet!
This was incredibly helpful and exactly what I was looking for for my google sheet formatting, thank you!
Hey, I have 3 columns of numbers I am trying to conditionally format for the ones who have the highest number to be highlighted a certain color. How do I do this across 2 columns?
Ean128 gs1
8 years has passed and this video still useful as f*ck
Very helpful. Thank you very much!
EXACTLY what I needed! Thank you!
Hi. How can I get a cell, in a group of cells, to mimic the format from another group of cells? This is also to consider that the 2nd group can be changed at any time.
Thanks, Ben! Do you know if Google plans to allow users to extract more Data from Google Docs than the currently allowed?
I'm trying to use map lambda with sparkline since sparkline can't use arrays, it's quite tricky.
Great video! Ok, now that I have created barcodes how do I do to use them for my inventory?
Nice and very important tips. Without knowing each of these techniques, it would be very hard to use google sheet efficiently. The onion is necessary, as there is no way to evaluate formula step by step. On the other hand, this video helped me, to add line breaks in the formula. It is very important for long formulas.
Hello Sir, is this work with importrange formula from another spreadsheet file?
You rock! Thank you
Unfortunately the barcodes created this way will not be scannable( For Libre 39 for instance you need to wrap your code in * in order to make it scannable. Not sure how to handle Libre 128. So if you have the following code 123456789 you need to write asterisk_sign123456789asterisk_sign and assign Libre 39 font
thank you so much!! I was already searching for hours on something and thanks to this video, I could accomplish it 😘
your videos are super helpful, thank you. Q: what is the best way to “attach” the row count numbers to the full row or make them IDs for sorting/filtering?
I need to be able to have a script save the images to a drive folder. The images need to be named from another cell. Any direction on that?
Grate video! what about aggregative formula such as max? if I want to bold a cell that is the max out of the Colum?
Whn i try to make this formular =COUNTIF(F4:F500,">0") /COUNTA(F4:F500) It Says Error How do i fix that?
Great tutorial! Is there a way to include a thumbnail image or image URL? Thanks!
Wow you fixed what Google Sheets were missing on QUERY :D
Thank you:)
That was a great video... and thank you for the named function!
Thanks you bro very interesting.
A tip for those of us on a Windows machine: pressing Win+. (Windows Key + Period Key) will bring up the “emoji window”; on its top row you can switch between emojis, ASCII-smileys, and Symbols; Once switched to Symbols, the bottom row has them categorized. The “Math Symbols” category (∞) has superscript and subscript numerals, as well as fraction glyphs, roman numerals (both upper and lowercase) as well as superscript numerals within parentheses or followed with a dot (both towards the bottom of the list). This is a rather unrelated-to-GSheets tip, as it is built-in to Windows 10 and should work everywhere.
This is an excellent demonstration of what can be done with the MAP function, though I think it is worthwhile to add a discussion about the differences between it and ARRAYFORMULA and what can be achieved by either. As far as the initial example of the x*2 case (1:28), the same results can be achieved with an ARRAYFORMULA(array*2) function, through a much shorter syntax (as you’ve acknowledged in 9:35). However, the “x of y” example (7:44) is an excellent testcase to show the differences in the inner logics of the two. In your example, you’ve referenced the original cell (A3) as both the source for the SEQUENCE value as well as the “tail-out” applied through the LAMBDA function, concatenating it after el&“ of ”. Suppose we’d like to use the SEQUENCE itself as the reference for the tail-out. Using the ARRAYFORMULA syntax, this can be achieved with the following: = ArrayFormula(Sequence(A3) & " of " & Max(Sequence(A3))) Or, to use a similar logic as to what’s going on with a LAMBDA function, one could even utilize the same instance of the SEQUENCE function by using LET as follows: = Let(el,Sequence(A3),ArrayFormula(el & " of " & Max(el))) However, if we’ll adjust the MAP example you’ve used accordingly - = Map(Sequence(A3),Lambda(el,el & " of " & Max(el))) The result will be different than expected: instead of getting “x of y”, where x iterates through the array and y stays constant - we’ll be getting an “x of x” result, where x still iterates but seemingly with no constant y. This difference in behaviour seems to me as crucial to understand and make proper decisions as to when to implement the MAP function vs. ARRAYFORMULA. ARRAYFORMULA allows us to apply values from an array to be mass-processed by _non-array functions._ This is done through using _the array as a single instance,_ and is limited as to how it behaves with functions that are _designed to work with arrays_ to begin with - for example, try using ARRAYFORMULA with the TEXTJOIN or CONCATENATE functions and it’ll either issue an error or give unexpected results. MAP, however, takes an array and _iterates its values_ as it passes them along to the LAMBDA function, essentially making a separate dedicated input for each instance to be calculated. Thus, one can iterate the original array also in a manner which can be used with array functions, as only the limited, relevant set of values needed for the specific iterated output to be calculated will be passed. Back to the “x of y” example - when using ARRAYFORMULA, the result is being calculated with the array input _as a whole_ - so the MAX() command always has the full sequence array as defined through A3 to consider. But once MAP is used to process the sequence, each result has _only the single value of its dedicated iteration;_ essentially, it iterates over multiple instances of arrays each holding a single value. For the 5th result, for example, it deals with an input “array” of the single value of “5” - hence “5” is also the array’s MAX value, and we’ll get “5 of 5” before moving to the next iteration - a single-cell array with the value of “6”, resulting in the output “6 of 6”. Therefore, one of the initial things to consider in judging whether a certain task is more fitted for a MAP or for ARRAYFORMULA - and separately from the issue of whether the functions applied are compatible with ARRAYFORMULA usage to begin with - is whether the intentions are to apply a process to _an array as a whole_ - meaning, as a complete _set_ of values - or whether the array is being used as a method to group (or filter) the values to be processed, with the actual aim for carrying out a process on each “record” _within_ the set, but rather independently of the set as a whole.