Відео

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 :)

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!

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.