Excel 條件式格式設定教學:自動變色、抓重複、公式自訂一次會

系統需求:任何一版桌面 Excel 都行(Microsoft 365 / Office 2021 / Office 2024 皆可;Excel 2007 以後這些功能就都在了)。Mac 版、網頁版也有,只是選單位置與少數進階選項略有差異。
權限需求:一般使用者即可,不需要系統管理員。
需要工具:Excel 本身,不必安裝任何外掛。
預計耗時:約 15 分鐘(內建規則 3 分鐘上手;公式規則要花點時間理解參照)。
難度門檻:會用滑鼠框選儲存格範圍就能開始;只有最後的公式規則需要一點「絕對參照 / 相對參照」概念,這篇會講到你不會再搞混。

🔍 為什麼你需要這個?

你一定有過這種畫面:一張攤開好幾百列的報表,密密麻麻全是數字,但「哪幾筆該注意」完全看不出來。於是你開始手動一格一格上色——庫存低的標紅、業績達標的標綠。結果下週數字一改,顏色全錯,你又得重上一遍。更煩的是對帳:兩份名單擺在一起,要靠肉眼找出重複或差異,眼睛都花了還會漏。

條件式格式設定就是來解決這件事的。它的邏輯很單純:你先訂一條規則(例如「大於 1000 就變紅」),Excel 就會自己盯著這片儲存格,只要內容符合條件就自動套上你指定的顏色、字體或圖示。資料一改,顏色立刻跟著動,完全不用你重來。它不會改到你儲存格裡真正的數值,只是幫你「把重點視覺化」,讓一張死板的表格自己會說話。搞懂它,你不用寫任何巨集,就能讓 Excel 幫你盯資料。

🛠️ 實戰步驟

步驟一:先搞懂五種內建規則(點一點就會)

先從最快見效的開始。框選你要套用的資料範圍 → 常用(Home)→ 樣式群組 → 條件式格式設定,點下去會看到五個內建類別,涵蓋九成日常需求:

內建規則	在做什麼	適合場景
醒目提示儲存格規則	依「大於 / 小於 / 介於 / 等於 / 包含文字 / 發生日期 / 重複的值」上色	標出達標、逾期、重複
頂端/底端項目規則	自動抓「前 10 項、前 10%、高於平均」等	找出前段班或落後群
資料橫條	儲存格內畫長條,長度代表數值大小	一眼比大小,像迷你長條圖
色階	雙色或三色漸層,顏色深淺代表高低	看整片數據的分布熱度
圖示集	紅綠燈、箭頭、旗標等小圖示	狀態分級、KPI 儀表板

舉個例:選一整欄「本月業績」,套「資料橫條」,每格立刻長出一條和金額成正比的長條,誰高誰低不用比對就看得出來。負值的資料橫條 Excel 還會讓它從儲存格中間往左延伸,正負一目瞭然。

💡 怎麼選? 想「標出符合條件的少數」用醒目提示;想「一整片比大小」用資料橫條或色階;想「分級貼標」用圖示集。不用一次全上,挑一個對應你的目的就好。

步驟二:用「重複的值」五秒抓出重複資料

這招單獨拉出來講,因為太多人還在用肉眼對名單。選好範圍 → 條件式格式設定 → 醒目提示儲存格規則 → 重複的值,重複的項目立刻整批變色。對帳、清名單、找重複下單,幾秒搞定。

要提醒的是:它比對的是「儲存格的值」,不是「整列是否重複」。如果你要判斷的是「整列資料完全一樣」才算重複,那要改用步驟三的公式規則搭配 COUNTIF,單靠這個內建選項會誤判。

步驟三:進階核心——「使用公式來決定要格式化哪些儲存格」

內建規則不夠用時,威力全開的就是這個。路徑是 條件式格式設定 → 新增規則 → 使用公式來決定要格式化哪些儲存格。規則很簡單:你填一條以等號 = 開頭的公式,這條公式對某一格算出來是 True,那一格就套你設定的格式;算出來是 False 就不套。公式如果寫錯(回傳錯誤),Excel 一律不套用、也不會報錯,所以最好先在旁邊空白格試過再貼進來。

這裡有個 90% 的人都卡住的觀念,搞懂就通了:你的公式是「站在套用範圍最左上角那一格」的視角寫的,Excel 會自動幫範圍裡其他每一格平移參照。這正是「相對 / 絕對參照」的關鍵。

相對參照:選 A2:A100,想標「大於 60」,就寫 =A2>60。Excel 會自動把第 3 列看成 A3>60、第 4 列看成 A4>60……每格各自比自己。
鎖欄不鎖列($D2):想「依 D 欄的狀態決定整列要不要變色」,就把欄用 $ 鎖死、列留相對。

整列反白的殺手級技巧:選整張資料表 A2:F100(不是只選一欄),新增公式規則 =$D2="完成"。因為欄鎖死在 D、列是相對的,Excel 逐列檢查 D 欄:只要那一列的 D 是「完成」,整列 A 到 F 就一起變色。這是做進度表、訂單表最實用的一招,一般教學很少講清楚。

💡 記不住 $ 放哪? 在公式裡把游標點在參照上,按 F4 就能循環切換「全鎖 $A$1 → 鎖列 A$1 → 鎖欄 $A1 → 全放」,不用手動打錢字號。

步驟四:用「設定格式化的條件規則管理員」排優先順序

同一片儲存格可以疊很多條規則(Excel 2007 起就拿掉了舊版「最多 3 條」的限制,現在上限只受記憶體限制)。規則一多,就要靠管理員來排順序。路徑:條件式格式設定 → 管理規則。

規則由上到下評估,越上面優先權越高。兩條規則衝突時(例如一條要紅字、一條要綠字),排在上面那條贏。
新增的規則預設會被放到最上面,所以順序要自己留意;用對話框裡的上移 / 下移箭頭調整。
「如果 True 則停止(Stop If True)」核取方塊:勾了之後,某格一旦命中這條規則,就不再往下套其他規則。這原本是為了模擬舊版 Excel 的行為而保留的相容選項,平常做「命中就停、不要再疊」時也很好用。注意:用資料橫條、色階、圖示集的規則不能勾這個選項。

步驟五:清除規則與驗證

清除:條件式格式設定 → 清除規則,可選「清除選取儲存格的規則」或「清除整張工作表的規則」。刪掉規則後,你儲存格裡原本手動設定的格式仍會留著,兩者是分開的。
驗證:隨手改一格數字,看顏色 / 圖示會不會跟著變,會變就代表規則生效了;或打開「管理規則」看清單,確認「套用至」的範圍是你要的那一片。

💡 總結:進階玩法與踩雷經驗

站長我最想先講一個效能雷:別為了省事直接對「整欄」(例如點欄標題選 A:A,那是 1,048,576 列)套規則。條件式格式是「動態計算」的,套在上百萬個空格上,檔案會膨脹、開檔和捲動都會變鈍。正解是只選實際有資料的範圍,或先把資料轉成「表格」(選範圍按 Ctrl + T),規則會隨表格自動往下延伸,新增資料照樣套用,又不會拖累效能。

第二個最常見的雷,還是公式規則的 $。十個人有八個是把錢字號放錯位置——半鎖跟全鎖搞混,結果不是只有第一列對,就是整片亂套。每次寫公式規則前,先問自己一句:「我這條公式,是站在範圍左上角那一格寫的嗎?」想清楚要「鎖欄還是鎖列」,雷就踩不到了。

還有兩個實用習慣:一是複製貼上會把條件式格式一起帶過去,而且會在目的地新增一條規則,貼多了規則會爆炸;真的只想要值,就用「選擇性貼上 → 值」。二是想做跨工作表的公式規則,舊版 Excel 不支援直接引用另一張表,得先用「名稱」或拉個輔助欄過渡。把這幾點記住,條件式格式就從「會用」升級成「用得乾淨又不拖慢檔案」。

❓ 常見問題

Q:套了條件式格式,會不會改掉我原本手動設定的儲存格格式?

不會。條件式格式和手動格式是兩套獨立的東西。當規則成立時,條件式格式的顏色會「蓋過」手動格式顯示;一旦你把規則刪掉,原本手動設的格式又會露出來。它從頭到尾都沒有動到你的手動設定,也沒動到儲存格裡的數值。

Q:我用公式規則,為什麼只有第一格對,其他都不對(或整片都上色)?

九成是相對 / 絕對參照沒設對。記住公式是「以套用範圍最左上角那一格的視角」寫的。如果你要每列各自比較,參照要留相對(例如 =A2>60);要固定看某一欄,就鎖欄不鎖列(=$D2="完成")。把游標點在參照上按 F4 切換,對照步驟三調整就會好。

Q:可以只對部分儲存格顯示圖示,其他不顯示嗎?

可以。在設定圖示集的規則時,把不想顯示圖示的那一級,從圖示下拉選單改成「沒有儲存格圖示」,那一段數值範圍就只保留數字、不放圖示。你也可以混搭不同圖示,自己組一套(例如綠勾、黃燈、紅旗)。

Q:Mac 版和網頁版(Excel for the web)也有條件式格式嗎?

有,核心功能都在,操作邏輯一樣。差別是選單位置略有不同,而且網頁版的部分進階選項(例如某些圖示集細節設定)比桌面版精簡。要做很細的自訂,還是桌面版最完整。

🔗 延伸閱讀

📎 參考資料來源

📖 第一級|廠商官方:

Microsoft Support:Use conditional formatting to highlight information in Excel — 2026-07-01 查證
Microsoft Support:使用條件格式來在 Excel 中標示資訊(繁中) — 2026-07-01 查證
Microsoft Support:Use a formula to apply conditional formatting — 2026-07-01 查證

⚠️ 本文核心事實以第一級為準,第二級為補充。

📅 本文查證戳記:2026-07-01 依據 Microsoft 365 版 Excel 官方文件撰寫。若你在後續版本遇到選單位置變動,歡迎在留言區回報,站長會更新文章。