- 140
- 428 841
Sheets Help
United States
เข้าร่วมเมื่อ 2 ก.ค. 2016
Welcome to Sheets Help. This channel offers comprehensive help for Google Sheets. Learn how to organize and analyze any spreadsheet with hundreds of functions and other advanced features.
Work faster in Google Sheets with our add-ons - College Compare, StaySorted, TIMEDIF, TripTally, and more at sheetshelp.com.
-- Adam Steinfurth
Work faster in Google Sheets with our add-ons - College Compare, StaySorted, TIMEDIF, TripTally, and more at sheetshelp.com.
-- Adam Steinfurth
Learn How to Use Event Chips in Google Sheets
Learn how Google Sheets integrates with Google Calendar with a type of Smart Chip called an Event Chip.
Website: sheetshelp.com/event-chips/
Timestamps:
0:00 Overview
0:42 Use the at sign
1:34 Use the menus
2:12 Link from Calendar event
3:07 Functionality
3:39 Data extractions
6:42 Use with TripTally
6:24 Next Video - Smart Chips
#googlesheets #smartchips #spreadsheet
Website: sheetshelp.com/event-chips/
Timestamps:
0:00 Overview
0:42 Use the at sign
1:34 Use the menus
2:12 Link from Calendar event
3:07 Functionality
3:39 Data extractions
6:42 Use with TripTally
6:24 Next Video - Smart Chips
#googlesheets #smartchips #spreadsheet
มุมมอง: 139
วีดีโอ
How to Use Place Chips in Google Sheets
มุมมอง 234หลายเดือนก่อน
Learn how Google Sheets integrates with Google Maps with a type of Smart Chip called a Place Chip. Website: sheetshelp.com/place-chips/ Timestamps: 0:00 Overview 0:15 Use the at sign 1:00 Use the menus 1:16 Link from Maps 2:13 Appearance and functionality 4:51 Use with TripTally 6:24 Next Video - Smart Chips #googlesheets #smartchips #spreadsheet
How to Install the TripTally Google Sheets Add-On
มุมมอง 64หลายเดือนก่อน
Follow the steps in this video to get the up and running.
Use the INT Function to Convert Decimals to Whole Numbers
มุมมอง 2292 หลายเดือนก่อน
Learn how the INT function removes decimals to create whole numbers in Google Sheets. See how it treats negative numbers also, which is different from other rounding functions. Timestamps: 0:00 Intro 0:08 Positive decimal value 0:52 Negative decmial value 1:30 Filtering for only positive numbers 2:38 Next Video - Rounding #googlesheets #spreadsheet
Find the Remainder With the MOD Function in Google Sheets
มุมมอง 1582 หลายเดือนก่อน
The MOD function returns the remainder of a division equation. See examples of how to use the ability in a formula. Blog: sheetshelp.com/mod/ StaySorted: bit.ly/POstaysorted Timestamps: 0:00 Overview 0:21 Remainder of a division problem 1:05 Is a dividend divisible by a divider 2:08 Is a number odd or even 3:11 Perform an operation on odd rows 4:08 StaySorted add-on #googlesheets #spreadsheet #...
Learn How to Use the TEXT Function in Google Sheets
มุมมอง 1742 หลายเดือนก่อน
The TEXT function in Google Sheets is powerful and flexible. It converts a number to a specific format, such as dates and times, an integer with a thousands separator, or even a number rounded to a significant digit. Blog: sheetshelp.com/text/ Custom Formats: customformats.com TimeDif: bit.ly/timedif Timestamps: 0:00 Overview 0:11 Two Decimal Places 0:37 Warning - Not a Number 1:27 Dates 2:26 C...
Learn How to Take a Stratified Random Sample in Google Sheets - Two Methods
มุมมอง 1282 หลายเดือนก่อน
Learn how to take a stratified sample from a data population in Google Sheets. We will look at a manual method with formulas and an automated method using the Random Sampler add-on. References: Written Tutorial: sheetshelp.com/stratified-sample/ Pricing: sheetshelp.com/random-sampler/#pricing Radom Sampler Add-On: workspace.google.com/marketplace/app/random_sampler/64538579874 Timestamps: 0:00 ...
Google Sheets Tables: A Comprehensive Guide
มุมมอง 4132 หลายเดือนก่อน
Learn how to leverage Tables in Google Sheets to work faster and more accurately. Article: sheetshelp.com/tables/ Table references: th-cam.com/video/mnAzwzVWq4k/w-d-xo.html Timestamps: 0:00 Comparison to unstructured data 0:40 Whole Table concepts 1:22 Group view 2:42 Column type 3:36 Filter controls 3:42 Add/delete rows and columns 5:00 Table references #googlesheets #spreadsheet
Keep Your Data Continuously Sorted in Google Sheets - StaySorted Add-On
มุมมอง 2412 หลายเดือนก่อน
bit.ly/POstaysorted - Learn how the StaySorted add-on keeps your spreadsheet sorted as new entries are added. Install the StaySorted add-on: bit.ly/POstaysorted Pricing: 7-day Free Trial, $59 one-time fee #googlesheets #spreadsheet #addons
How to Reference Data in Google Sheets - Beginner to Advanced
มุมมอง 3882 หลายเดือนก่อน
Learn how to use different types of data references to power up your Google Sheets formulas. We'll start with basic cell references and work all the way to the new table references. Timestamps: 0:00 Intro 0:39 Cell reference 2:04 Range reference 3:56 Multiple cell or range references 5:46 Relative reference 7:09 Fixed reference 8:05 Mixed reference 8:54 Infinite reference 10:46 Same sheet 11:11...
How to Install the Sheet Indexer Add-On
มุมมอง 1032 หลายเดือนก่อน
Learn how to install Sheet Indexer from the Google Workspace Marketplace. workspace.google.com/marketplace/app/sheet_indexer/251619513648 #googlesheets #spreadsheet #add-on
How to Use the Sheet Indexer Add-On
มุมมอง 1532 หลายเดือนก่อน
Learn how the Sheet Indexer creates a table of contents for your spreadsheet, complete with AI-generated summaries and links to each sheet. workspace.google.com/marketplace/app/sheet_indexer/251619513648 #googlesheets #add-ons #spreadsheet
How to Use Table References in Google Sheets
มุมมอง 1.5K3 หลายเดือนก่อน
Learn how table references work and how to use them to access the data in your Google Sheets tables. sheetshelp.com/table-references/ Timestamps: 0:00 Overview 0:25 Parts of a table 0:41 Syntax 2:13 Difference from regular cell references 3:49 Using table references with formulas 4:00 Formulas inside of a table 5:12 Formula outside of a table #googlesheets #spreadsheets
Count Amounts by Day of the Week in Google Sheets
มุมมอง 4213 หลายเดือนก่อน
Learn how to count the totals of different items according to the day of the week. Then, you'll show the totals next to each day.
5 Reasons to Use Calculated Fields in Google Sheets Pivot Tables
มุมมอง 2953 หลายเดือนก่อน
This video shows five reasons to level up a standard pivot table with the flexibility of calculated fields. Article with sample spreadsheet: sheetshelp.com/5-reasons-calculated-fields/ Timestamps: 0:00 Overview 1:37 Reason 1 - Combine Fields 3:38 Reason 2 - Less Common Functions 4:54 Reason 3 - Conditional Calculations 6:25 Reason 4 - Cleanliness and Readability 6:53 Reason 5 - Increased Reliab...
How to Take Systematic Sample in Google Sheets - Two Methods
มุมมอง 1673 หลายเดือนก่อน
How to Take Systematic Sample in Google Sheets - Two Methods
Taking a Random Sample in Google Sheets: Two Methods
มุมมอง 5224 หลายเดือนก่อน
Taking a Random Sample in Google Sheets: Two Methods
Learn How to Install the Random Sampler Google Sheets Add-On
มุมมอง 404 หลายเดือนก่อน
Learn How to Install the Random Sampler Google Sheets Add-On
How to Use the Random Sampler Google Sheets Add-On
มุมมอง 3364 หลายเดือนก่อน
How to Use the Random Sampler Google Sheets Add-On
How to Install the College Compare Google Sheets Add-On
มุมมอง 4035 หลายเดือนก่อน
How to Install the College Compare Google Sheets Add-On
Finding the Smallest Number in Google Sheets - MIN or MINA
มุมมอง 2015 หลายเดือนก่อน
Finding the Smallest Number in Google Sheets - MIN or MINA
Finding the Largest Number - MAX or MAXA | Google Sheets
มุมมอง 5015 หลายเดือนก่อน
Finding the Largest Number - MAX or MAXA | Google Sheets
How to Use the Travel Formulas Google Sheets Add-On
มุมมอง 8736 หลายเดือนก่อน
How to Use the Travel Formulas Google Sheets Add-On
How to Install the Travel Formulas Google Sheets Add-On
มุมมอง 5097 หลายเดือนก่อน
How to Install the Travel Formulas Google Sheets Add-On
How To Use The Driving Vacation Planner Spreadsheet - Google Sheets
มุมมอง 3547 หลายเดือนก่อน
How To Use The Driving Vacation Planner Spreadsheet - Google Sheets
Compare Colleges with a Spreadsheet Add-On
มุมมอง 8119 หลายเดือนก่อน
Compare Colleges with a Spreadsheet Add-On
Learn How to Install the OmniPivot Google Sheets Add-On
มุมมอง 1329 หลายเดือนก่อน
Learn How to Install the OmniPivot Google Sheets Add-On
How to Create a Pivot Table from Multiple Ranges in Google Sheets
มุมมอง 3.5K10 หลายเดือนก่อน
How to Create a Pivot Table from Multiple Ranges in Google Sheets
How to Install the StaySorted Google Sheets Add-On
มุมมอง 74611 หลายเดือนก่อน
How to Install the StaySorted Google Sheets Add-On
Find Durations in Hours, Minutes, etc. in Google Sheets | TIMEDIF Add-On
มุมมอง 1.4Kปีที่แล้ว
Find Durations in Hours, Minutes, etc. in Google Sheets | TIMEDIF Add-On
Thank you so much. But whats the name of the process if I need to do the exact oppsit.
You could use QUERY on each destination sheet. The trick would be getting the parameters right so it knows what data goes on each sheet.
@@SheetsHelp thank you so much. Will experiment with it. Appreciate it
Excellent explanation of calculated fields. I really liked the end of the video that described several reasons why I should choose a calculated field.
Glad you liked it! Calculated fields are great but hard to understand.
Shouldn't the cost calculation per impression be (Cost/Impressions) !!
Details schmetails!
I've been trying to do this with multiple rows for days, and your video helped me do it! Finally! Thank you!
Alright! Glad to hear it.
I have two tee times for eight players. How do I generate a random list of four players on tee time 1 and four players on tee time 2. So four 1's and four 2's. I would also need same if there were enough players for three tee times (12 players) - four 1's, four 2's and four 3's. Etc.
Hmm. You could use RAND() to generate random numbers for each player. Then copy paste those numbers as values. Then, sort them by those random numbers. Viola. They're in a random order, then just put the first four in group 1, etc.
Thankyou!... However, what would I type if I wanted to count cells that didn't have "sun" or "part" ?
I ended up using chatgpt to help me.. and it gave me =ARRAYFORMULA(SUM((NOT(REGEXMATCH(A1:A5,"sun|part")))*(A1:A5<>""))) It works, but doesn't count blank cells (which in my application worked fine, but surely there's a simpler way.
Hello! Is there a way for the cell containing the xlookup formula to remain editable if no match is found? For instance, I have set up my spreadsheet so that xlookup fills in a corresponding price if a certain item is selected from the drop-down. However, sometimes a "one-off" item is entered which I've allowed as an "invalid" entry. Is it possible to edit the "price" cell as one would normally instead of being blocked by an error message in these unique cases?
Hey there, thanks for the question. It can't be done the way you described it. If you can, try using the not found message as described here: sheetshelp.com/xlookup/#Example_2_8211_Using_a_Not-Found_Message
thanks for this! How can I do the exact same things but from another sheet? I know how to reference another sheet cell by =SheetName!Cell-ID but what if I want to reference a table from another sheet?
The table reference works from any sheet to any sheet. Table names have to be unique amongst all the sheets.
@@ProlificOaktree yeah! Just discovered that haha thanks a lot!
I get it. I didn’t realize it until another person asked me about it after this video.
Great info. Once you have a number of sum if formulas, giving you a list of dollar amounts (in my case). Can you then sort those columns by total spend?
Well....maybe! If those results don't change and your formulas are all referencing their own row or using fixed references then....probably! It really depends on how your sheet is setup but SUMIF doesn't preclude sorting.
MVP! Thanks for a concise video that explains clearly!
Thanks, glad you liked it!
Is there anyway to reference the table column from outside the same sheet? For example if you have data in a table in Sheet1 and want to add a formula in Sheet2 to take the sum of the values in columnA of Table1, would that not be =SUM(Sheet1!Table1[columnA])?
Turns out that Table1 will be recognized across worksheets so the formula remains =SUM(Table1[columnA]) regardless of which cell it is placed in.
Agreed! I just test it by typing equals on another sheet then highlighting the table column. That's what it gave me. It makes sense because you can't reuse table names in a spreadsheet.
=QUERY({sheetname!A:X2000;sheetname!A:X2000},''
I love you boss, i will watch your all video❤
Oh yeah!
Not sure why there isnt more comments here... answered all of my questions better than any other video on YT. Thanks for the insight
Much appreciated, thank you. (I am a CPA, after all!)
Hello, could I write to you?
adam@sheetshelp.com
What if you don't have a specific text you are trying to include or uninclude? Just getting a count of any cells that do not have text data?
A combination of the ISTEXT and COUNTIF functions should do it for you. ISTEXT will see if the data type is text. COUNTBLANK could do the job if you want to count any non-blank.
Exactly what I needed. Thanks!
Glad it helped!
i tried to instal this and it is not working
Even with many quick access to AI prompt for quick tips, still prefer learning from hands-on guy like you, voice with demo. Thanks.
Thank you! The day may come when AI can make videos like this, but I hope that's far away.
Please add Flight as a route option
Great suggestion, but the API only offers driving, cycling, walking, and transit.
I love your google sheet classes 😀
Glad you’re watching!
Hi there, how can I get the number of the week within the month, not a year. Weeknum provides the number of weeks within the year. I just need something within the month, so it would only display values 1 up to 5 Thanks!
Great question. I don't know of a built-in function to do that.
Perfect! Thank you
NP, glad it helped!
Hi, I have entered your formular exactly as you have but it's counting all the cells including the blank ones. How can I avoid this ?
That sounds like it's working as expected. To avoid that, there is a ISBLANK function you could wrap it in. Perhaps combine that with the NOT function.
Why it didn’t work on my google sheet? Is their anything to do so I can use it? I already did the instruction seen in the vid. Thanks for the helps
I can't tell without seeing your sheet.
hello could this allow me to recover the titles of public TH-cam videos and also to recover data from my Amazon seller account new: Do you use no code software like make? In fact I'm asking myself questions to try to understand the differences between custom GPT, chrome and WordPress extensions, automation software like browser automation studio and make I had an idea of creating an SEO and image editing but I don't know how to go about it I thought of several solutions either with make or by creating a Chrome or WordPress plugin or by using a existing plugin but I don't think it exists to do what I want or even with a custom GPT seo: OK and what do you think about rank math? I understand that this extension allows me to scan my pages and find the keywords to replace, from my back end, have I understood correctly? And I also understood that this does not allow me to ask it to scan my entire website? I mean if I understood correctly it scans the pages but one by one and I have to do everything by hand? This is why I wondered if I could not create an extension to which I would give the URL of my website and have it scan all the pages and tell me which keywords to replace And I even wondered if I wouldn't be able to make a robot with custom GPT or even with no code software like make in the case where an extension wouldn't be able to allow me to do it, does this idea seem good to you? do you also create chrome or WordPress extensions? I'm asking you this question because I'm trying to understand what I could do with custom GPT, could I do more with a chrome WordPress extension? Does it exist or not and finally what more can I do with it? make compared to its other methods Amazon, jungle scout I would like to use the jungle scout api but at the moment I am looking into the subject of artificial intelligence and I realized that there are several ways to use APIs and so I would like to try first to understand what is the difference between using the jungle scout api in a dent custom gpt action and I realized that I can also use the JS api in a make scenario Could you tell me what the difference is, what could I get, using either of these two methods? And also with the Amazon API? I understand that Amazon has several APIs? Do you also sell on several marketplaces? could you give me your opinion could I send you an email
It won't grab anything that you can't also get with a browser.
@@SheetsHelpI don't understand, I would like to retrieve the titles of public TH-cam videos
I don’t know if that would work
@SheetsHelp but I don't understand what you wanted to tell me in your first answer
@@chrisder1814 I thought you wanted to retrieve deleted information since you used the word "recover."
Thank you!
Glad you liked it!
Hello, I am working on the small inventory management graph for my company where I move inventory between locations. I used Sumifs formula. I create transaction in one sheet and mark it ordered, it goes to Location 1 column in another sheet, then I mark it shipped it moves to Location 2 column, then I mark it received it moves to 3rd column. Which is perfect. But now I am stuck because I have to move full amount all the time. If I ordered 100 pc, I have to ship and receive 100pc but if I want to ship to different location only 20pc and location 1 will have 80pc left I can not do it. Could you please help me with that? Do you think it is possible to create it in Google sheets or it is too complicated?
Hi Elena. I wish I could provide a more in-depth answer, but I would have to log into your spreadsheet and discuss the process with you. Generally, basic inventory management is a good fit for spreadsheets but it's hard to say how you would get your specific use case to work. Best of luck.
Thanks for the great video. A helpful resource while documentation on this is so scarce right now
Thank you. Yeah, there's not too much out there on these yet. If you look at the Excel documentation, it pretty different so you can't use that either.
our lectures are amazing! I'm always learning something new, and you keep it interesting. Thank you
Thank you, glad you liked it.
I love how easy it is to learn and catch up on the material on your short videos. Thank you!
Glad you like them!
can I somehow use two values for the key? For example: I have a table like in your example at 2:36, but I have two different people named Jim, one from South and one from North. I want to lookup for both their specialities by entering the name and region. How can I accomplish this?
Consider using the FILTER function instead. I made a video about comparing the two on my Prolific Oaktree channel. See if this helps: th-cam.com/video/zFo9H0Uw4i8/w-d-xo.html
We're in a remote, multi-user set up with the sheet on a shared google drive. Does the add on need to be resident on all user computers to function on users computers?
Yes, the sorting only occurs when a user adds a row and they are running the add on.
Thank you!!!
You're welcome!
Where do you get the Option the extract specific data i only have summary and URL
I have a paid Google account. Those show more options.
Perfect explanation. Perfect conciseness of speech and coverage of "edge cases". You have a talent for teaching these.
Thank you, I appreciate the feedback. It takes a lot of time to make these videos!
but sometimes the importrange data is always error/reff. any solution?
Make sure you give it permission to do the import.
Cant see the side panel
Do you see the toggle in the lower right hand corner like it is in the video? If not, maybe you are using a mobile version of the browser?
What if I want to compare two lists and only return mutually INCLUSIVE results. So only data that appears in both lists/columns
This may help, not sure: th-cam.com/video/QBuME5VrrhM/w-d-xo.html
Amazing!
Thanks!
In range a:a I have the words None in some cells, and then emails in other cells. If I want to count just the # of emails I have, how can I do that?
Try =COUNTIF(A:A,NOT("None"))
Thank you for this! Super helpful and straightforward!
Glad it was helpful!
=TEXT(A1,"ddd") & =TEXT(A1,"dddd") work also for text form i.e. Sun and Sunday.
That's great. It appears to be better than the CHOOSE formula I used. I'll put this on my list of revisions and may change the video.
do you know how to fix the number generated ? so when I update the sheet it doesn't automatically change
The easiest thing to do is copy formulas and paste them as values.
Sheets Help, Your videos always make me happy, so I subscribed!
Well, your comment made my day!
Thank you very much for the video! Your explanation was very helpful and easy to understand!
Alright, glad you appreciated it!
Thank you sir.
You're welcome!
Lovely
Thank you!
I noticed it can only do approx 50 postcodes in a single run. And even after mutliple attempts of 3 times which is approx 150 searches it says limit has reached.
The connection to Google Maps has imitations that the add-on must abide by. One of them is a time-out limitation as you are describing. You will need to process your addresses in smaller batches.
@@SheetsHelp is there an alternative suggestion to handle larger batches?
No, it’s an inherent limitation of the add-on’s interaction with Maps. My users just run the addresses in the largest batches they can get away with.
Would be awesome if it was free.. $60/year is steep for an add on
It's a one-time fee, not per year. However, yeah, it's got to be worth it to you. I've priced the add-on to make sense for users saving hours by using it.
We are piloting a $3 per month price right now FYI.