Tips & Tricks: Google Sheets & Excel

[Andy Black]

I thought it might be helpful to have a thread where we swap tips and tricks.

Someone in the forum has a progress thread for a YouTube channel about Google Sheets. I'll link to that when I find it.


A handy wee tip:

• For those who create Google sheets a lot, you could type in sheets.new in your browser and it should create a new Google Sheet.

A couple of useful new Google Sheets functions I learned over the last two weeks:

• importrange() allows us to import data from another sheet.
• query() allows us to retrieve and filter data.

I've used these two functions to extract data from a master "agency" sheet into separate "client" sheets where only their data is pulled through.


Here's an example where I could put this formula into a cell in the client sheet and pull over CallRail data:

• Column 7 is the "Start Time".

=query(importrange("<sheet ID>", "Calls!A:AB"), "where Col2='<name of client>" order by Col7", 1)



You can just use query() on it's own and pull back data from another tab in the current sheet (useful when you want to hide some columns or rows from other users).

Here I'm selecting various columns by their letter:

=query('Imported Calls'!A:AF, "select K, L, E, M, N, P, Q, AE where D='Y'")


EDIT: Added "Excel" to the title.

 

Dislike ads? Remove them and support the forum: Subscribe to Fastlane Insiders.

[Andy Black]

Here's the progress thread by @Cybom where he's growing a YouTube channel about Google Sheets:

• EXECUTION - Starting a YouTube channel - Trying to build an audience

There's some good videos on this channel:

• https://www.youtube.com/@BetterSheets

And I created threes videos on a channel a bit ago that I promptly forgot about:

• https://www.youtube.com/channel/UCvSMPvpUPPwhmaxN_T6CLLA

 

[Robdavis]

For some Excel tips there is this channel:

https://www.youtube.com/@everydayexcelvba4684/featured

He does a couple of courses on Coursera that I am doing at the moment.

 

[Cybom]

Thank you @Andy Black !
As I'm also learning on the way, I'll follow this thread closely! (I just hope it won't emphasize my impostor syndrom ).

There is something that I think may be worth sharing here and this is about the interaction between Google Sheets and Google Apps Script (VBA for Google Sheets). If you have already tried triggering an Apps Script function from the mobile version of Google Sheets, you most likely noticed it is not possible. Simply because you cannot call Apps Script from the mobile version of Google Sheets.

But there is a tip or I should say a workaround. All you can do is:

1) Set up a trigger which reacts and triggers the function for every modification in the Google Sheets file
2) Include a tickbox in your Google Sheets file
3) Check whether the tickbox is ticked at the beginning of the function and execute the next part of the script only if it is ticked
4) At the end of the function, include a line of code to untick the box

Then your function will be triggered if you tick your tickbox as it would do with a button. The only difference is that it will also work on the mobile app since the function is not directly called from Google Sheets but by the trigger which is reacting to a modification in the file (done either from the web version or the mobile app).

I'm not sure it can be useful for someone (I must admit the video about it did not really have a huge success on the channel) and it may not work with bigger files but I found it cool enough to be shared

 

Dislike ads? Remove them and support the forum: Subscribe to Fastlane Insiders.