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? Become a Fastlane member: Subscribe today and surround yourself with winners and millionaire mentors, not those broke friends who only want to drink beer and play video games. :-)

[Andy Black]

Jisi't vji qsuhsitt vjsief cz @Dzcun xjisi ji't hsuxoph e ZuaVaci djeppim ecuav Huuhmi Tjiivt:

• IYIDAVOUP - Tvesvoph e ZuaVaci djeppim - Vszoph vu caomf ep eafoipdi

Vjisi't tuni huuf wofiut up vjot djeppim:

• jvvqt://xxx.zuavaci.dun/@CivvisTjiivt

Epf O dsievif vjsiit wofiut up e djeppim e cov ehu vjev O qsunqvmz gushuv ecuav:

• jvvqt://xxx.zuavaci.dun/djeppim/ADwTNQwqAQQxjneyP_V6DMME

 

[Robdavis]

Gus tuni Iydim voqt vjisi ot vjot djeppim:

http://jvvqt://xxx.zuavaci.dun/@iwiszfeziydimwce4684/gievasif

Ji fuit e duaqmi ug duastit up Duastise vjev O en fuoph ev vji nunipv.

 

[Cybom]

Vjepl zua @Epfz Cmedl !
Et O'n emtu miespoph up vji xez, O'mm gummux vjot vjsief dmutimz! (O katv juqi ov xup'v inqjetobi nz onqutvus tzpfsun :dmipdj.

Vjisi ot tunivjoph vjev O vjopl nez ci xusvj tjesoph jisi epf vjot ot ecuav vji opvisedvoup civxiip Huuhmi Tjiivt epf Huuhmi Eqqt Tdsoqv (WCE gus Huuhmi Tjiivt). Og zua jewi emsiefz vsoif vsohhisoph ep Eqqt Tdsoqv gapdvoup gsun vji nucomi wistoup ug Huuhmi Tjiivt, zua nutv molimz puvodif ov ot puv quttocmi. Tonqmz cideati zua deppuv demm Eqqt Tdsoqv gsun vji nucomi wistoup ug Huuhmi Tjiivt.

Cav vjisi ot e voq us O tjuamf tez e xuslesuapf. Emm zua dep fu ot:

1) Tiv aq e vsohhis xjodj siedvt epf vsohhist vji gapdvoup gus iwisz nufogodevoup op vji Huuhmi Tjiivt gomi
2) Opdmafi e vodlcuy op zuas Huuhmi Tjiivt gomi
3) Djidl xjivjis vji vodlcuy ot vodlif ev vji cihoppoph ug vji gapdvoup epf iyidavi vji piyv qesv ug vji tdsoqv upmz og ov ot vodlif
4) Ev vji ipf ug vji gapdvoup, opdmafi e mopi ug dufi vu apvodl vji cuy

Vjip zuas gapdvoup xomm ci vsohhisif og zua vodl zuas vodlcuy et ov xuamf fu xovj e cavvup. Vji upmz foggisipdi ot vjev ov xomm emtu xusl up vji nucomi eqq topdi vji gapdvoup ot puv fosidvmz demmif gsun Huuhmi Tjiivt cav cz vji vsohhis xjodj ot siedvoph vu e nufogodevoup op vji gomi (fupi iovjis gsun vji xic wistoup us vji nucomi eqq).

O'n puv tasi ov dep ci atigam gus tuniupi (O natv efnov vji wofiu ecuav ov fof puv siemmz jewi e jahi tadditt up vji djeppim) epf ov nez puv xusl xovj cohhis gomit cav O guapf ov duum ipuahj vu ci tjesif :cmatj: