Google Sheets – Query And Other Functions

Featured Image- Dominik Scythe

Photo by Dominik Scythe on Unsplash

Google Sheets - Query & Other Functions

IF & IFS Functions

June 15, 2019


Combine Cell Contents

June 15, 2019

  1. =B2&” “&C2
  2. JOIN(delimitervalue_or_array1[value_or_array2, …])
    • =TRIM(JOIN(” “,B12:C12,E12, D12)) Mr. Robert B Haight 
    • TRIM: to eliminate the space
  3. TEXTJOIN(delimiterignore_emptytext1[text2, …])
    • =TEXTJOIN(” “,TURE,B12:C12,E12, D12)) Mr. Robert B Haight 
    • TRUE: to ignore the empty space
  4. SUBSTITUTE(text_to_searchsearch_forreplace_with[occurrence_number])

Apply to: combine first name & last name into Name.

=B2&” “&C2

Combine Data From Multiple Sheets

April 4, 2019

Learn how to Combine Data From Multiple Sheets (Tabs) in Google Sheets using arrays & QUERY Function and make an automatic master sheet with all the data combined into one tab.

Apply to Monthly Expenses:

=QUERY({GoDaddy!B3:L;’Media Temple’!B3:L;Flywheel!B3:L;MailChimp!B3:L;NetFirms!B3:L;’Nuvei-Donation’!B3:L;’Nuvei-Retail’!B3:L;TechSoup!B3:L}, “select * where Col1 is not null or Col2 is not null or Col3 is not null or Col4 is not null or Col5 is not null or Col6 is not null or Col7 is not null or Col8 is not null or Col9 is not null or Col10 is not null or Col11 is not null”,0)

Get to Know Illustrator

Featured Image- Get to Know Illustrator

Photo by Hope House Press on Unsplash

March 16, 2018

常常我會太投入完成老闆給的任務,而忘記自我進修了,一定要勉強自己每天先自修一下,才能不斷累積進步。只想先學會AI修圖的基本,但是每次進入AI都像走進迷宮,讓我一直抗拒它,Creative Cloud Tutorials (點入標題,可連到課程連結) 給很多基本的課程,就從這裡開始。

1. Illustrator Workspace

  • View > Fit All in Window
  • Top: Menu Bar
  • Left: Tool Bar
  • Right: Panels
  • Essentials: Reset Essentials to original state
Adobe Illustrator Tutorial

2. Zoom and Pan

  • Hand Tool: to pan across (上下左右移動) a document
  • Zoom Tool: Zoom in -> Click; Zoom out -> [option] & Click
Adobe Illustrator Tutorial

3. Create a new document

  • File > New
4. Save your work
  • File > Save or File > Save As
  • Format:
    • .ai – Adobe Illustrator
    • .eps  – Illustrator EPS
    • .ait – Illustrator Template
    • .pad – Adobe PDF
    • .svgz – SVG Compressed
    • .svg – SVG

April 13, 2018

  • Draw simple shapes
  • Adobe Illustrator 會斷圖
Illustrator Image Broken Up

May 2, 2018

不恥下問真的是必要的,這個斷圖問題,今天鼓起勇氣向Support Chat尋問,找到答案了,原來是我沒更新作業系統。目前我是macOS High Sierra Version 10.13.3,有V.10.13.4版本待更新我沒做,導至Graphic Cards Drivers outdated了。

Support Chat要我在Rocket Symbol那點一下,uncheck the GPU Performance就把問題解決了。



Illustrator > Preferences > General > Performance > uncheck GPU Performance

May 3, 2018

AI Practice - Create Lines

II-2 Create lines

  • Line Segment Tool
  • Scale Tool > Reshape Tool
AI Practice - Combine Shapes

II-3 Combine shapes

  • Shape Builder Tool – to combine shapes by dragging them
  • Shape Builder Tool – to remove shape by pressing Option + Click the shape

Import Excel into Access

Photo: Silverton Lookout, BC

Import Excel to Access

This tutorial maybe just what I need.  Three hours long.

  1. Comparing Access & Excel Screens 4:56
  2. Why You Need Access 7:06
  3. Compare Tables to Excel Sheets 13:08
  4. Add, Edit, Delete Table Records 19:45
  5. Sorting Records
  6. Basic Table Filters
  7. Create a Table with Primary Key
  8. Add More Fields
  9. Numeric Fields
  10. Modify Table Design
  11. Intro to Access Reports
  12. Using the Report Wizard
  13. Report Controls
  14. Modify Reports and Select Controls
  15. Using the Properties Box
  16. Select Queries / Sorting
  17. Using Criteria in Queries: AND/OR
  18. Using Criteria in Queries: Wildcard
  19. Formulas in Query Criteria
  20. Calculations in Select Queries
  21. Typical Excel Import Problem
  22. Combining Data First
  23. Planning Database Best Practices
  24. Preview of Forms

12.22.17 2:33am

1.Comparing Access and Excel Screens:
2. Why You Need Access

Don’t want to spreadsheet slave



How To Save Web-Optimized Images In Photoshop For WordPress SEO

Sharpen my tool...

How To Save Web-Optimized Images In Photoshop For WordPress SEO

Thanks Bjorn’s well explained tutorial.  Here is the notes I saved for myself in the future and anyone who maybe benefits from it.


How To Save Web-Optimized Images In Photoshop For WordPress SEO


  1. 將解析度(Resolution) 降低到72ppi
  2. 將圖檔裁到實際所需的尺寸(可參考下面的網站寬度建議)
  3. JPGPNG來存檔.通常JPGfile size是比PNG來得小的

通常數位相機或手機的照片的解析度會在300ppi,這樣常常它的檔案大小(file size)會相當龐大(2mb+…)




1    Column = 1080px
3/4 Column = 795px
2/3 Column = 700px
1/2 Column = 510px
1/3 Column = 320px
1/4 Column = 225px


** 原文翻譯來自以下的教學影片

Exact Steps in Photoshop

Step 1:
Open the image in  Photoshop
Photoshop > File > Open  
Choose the image

Step 2:
Go to Image > Image Size

Step 3:
This image’s original size:
Width: 4896px
Height: 2752px        
Resolution: 350ppi

Step 4:
Change the Resolution to 72 ppi.
Once changed to 72 ppi. 
The Width and Height will automatically be changed too.

Step 5: 
Leave the Resolution in 72 ppi.
Change the Width back to be your exact need for the website. In this case, I need this image to be the background for the section, so I used 1280 px for the Width.

Step 6:
Now, save the file.
File > Export > Save for Web (Legacy) 

Step 7:
Often PNG file is smaller than JPEG. So choose JPEG and choose Medium or High for quality. And then <SAVE>

Finally, it’s done.  
We managed to change the file size from 6.4 MB to 435 KB.  

Yeah! Yeah!