Category Archives: Excel

條件格式化整列格式 – Excel / Google試算表

目的:

根據某一欄位的值,改變整列的背景顏色

範例:

  1. A2:L42
    範圍要選擇整個資料表,而不是只有一列A2:L2
  2. =$L2=”V”
    1. 開頭要是=
    2. $L2前面要加上$(絕對位置)
    3. “V” 字串前後要加上雙影號

Reference:

  1. https://isvincent.pixnet.net/blog/post/37181644-excel-%E8%BC%B8%E5%85%A5%E8%B3%87%E6%96%99%E5%BE%8C%E6%95%B4%E5%88%97%E8%AE%8A%E8%89%B2
  2. https://support.google.com/docs/answer/78413?co=GENIE.Platform%3DDesktop&hl=zh-Hant

Remove non-breaking space from Excel (Filter Cell Value)

Reference:

Create and Download CSV File on The Fly

Reference:

Keep Leading 0 of Numbers in Excel

By default, the leading 0 will be removed if the value is a number:

Solution:

Reference:

Count In Some Condition

Count only when the cell value if bigger than 0 from M5 to Q5: