Data Validation & Conditional Formatting Google Sheets Nang Cao: Huong Dan A-Z Kem Bai Thuc Hanh
Tuân Hoang
Data Validation & Conditional Formatting — hai vu khi ngam bien Google Sheets tu bang tinh don gian thanh he thong quan ly du lieu chuyen nghiep.
Bai viet huong dan chi tiet A-Z cach thiet lap kiem soat nhap lieu, to mau tu dong theo dieu kien, ket hop ca hai de xay workflow hoan chinh tren Google Sheets.
Muc luc:
- 1. Data Validation la gi? Tai sao quan trong?
- 2. 7 loai Data Validation nang cao trong Google Sheets
- 3. Conditional Formatting: Tu co ban den nang cao
- 4. Ket hop Validation + Formatting tao workflow
- 5. Custom Formula — Suc manh thuc su
- 6. 5 bai thuc hanh ung dung thuc te
- 7. Meo nang cao & sai lam can tranh
- 8. FAQ
1. Data Validation La Gi? Tai Sao Quan Trong Khi Dung Google Sheets?
Data Validation (kiem soat du lieu dau vao) la tinh nang cho phep ban quy dinh loai du lieu duoc phep nhap vao mot o hoac vung o trong Google Sheets. Thay vi de nhan vien nhap tu do — dan den sai chinh ta, sai dinh dang, du lieu rac — ban thiet lap quy tac de Google Sheets tu dong kiem tra va canh bao.
Vi du thuc te: ban co cot "Trang thai don hang" — thay vi nhan vien A nhap "Da giao", nhan vien B nhap "da giao", nhan vien C nhap "Giao roi", ban tao dropdown chi cho phep 4 gia tri: Cho xu ly, Dang giao, Da giao, Da huy. Ket qua: du lieu sach 100%, bao cao chinh xac, khong can clean data thu cong.
Thong ke thuc te:
Theo khao sat tu SheetStore tren 500+ doanh nghiep SME Viet Nam: 73% loi bao cao bat nguon tu du lieu nhap sai. Chi can thiet lap Data Validation dung cach, ban giam duoc 80-90% loi nay.
Data Validation khong chi la dropdown don gian. Google Sheets ho tro nhieu loai validation phuc tap ma it nguoi biet — tu custom formula, regex pattern, dependent dropdown cho den kiem tra du lieu cross-sheet. Day chinh la thu phan biet nguoi dung co ban voi nguoi dung nang cao.
2. 7 Loai Data Validation Nang Cao Trong Google Sheets
2.1 Dropdown tu danh sach tinh
Cach pho bien nhat: vao Data → Data validation → Criteria: List of items, nhap cac gia tri cach nhau bang dau phay. Phu hop cho danh sach ngan, it thay doi (trang thai, loai san pham, muc uu tien).
2.2 Dropdown tu dai o (Range)
Chon Criteria: List from a range va tro den mot cot chua danh sach. Uu diem lon: khi ban them/sua/xoa muc trong cot nguon, dropdown tu dong cap nhat. Day la cach lam chuan cho danh sach dai hoac thuong xuyen thay doi — vi du danh muc san pham, ten nhan vien, ten khach hang.
2.3 Dependent Dropdown (Dropdown phu thuoc)
Day la ky thuat nang cao: dropdown o cot B thay doi tuy theo gia tri da chon o cot A. Vi du: cot A chon "Thanh pho" = Ha Noi → cot B chi hien thi cac quan cua Ha Noi. Cong thuc su dung: INDIRECT() ket hop Named Range hoac FILTER() ket hop UNIQUE().
// Dependent dropdown bang INDIRECT
Criteria: List from a range → =INDIRECT(A2)
// Hoac dung FILTER + UNIQUE
Criteria: List from a range → =UNIQUE(FILTER(Sheet2!B:B, Sheet2!A:A=A2))
2.4 Validation theo so (Number)
Gioi han o chi nhan so trong khoang cho phep: between, greater than, less than, equal to. Ung dung: gioi han so luong dat hang (1-1000), gia san pham (>0), phan tram giam gia (0-100%). Neu nhap ngoai khoang, Google Sheets bao loi ngay.
2.5 Validation theo ngay (Date)
Kiem soat ngay nhap vao: is valid date, before, after, between. Ung dung: ngay giao hang phai sau ngay dat hang, ngay ket thuc khuyen mai phai sau ngay bat dau, ngay sinh phai truoc ngay hien tai. Ket hop =TODAY() de tao validation dong.
2.6 Validation bang Custom Formula
Day la loai manh nhat — ban viet cong thuc tra ve TRUE/FALSE. Neu TRUE, du lieu duoc chap nhan; FALSE se bi tu choi. Vi du:
// Chi cho phep email hop le
=REGEXMATCH(A1, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")
// Chi cho phep so dien thoai VN (10 so, bat dau 0)
=AND(LEN(A1)=10, LEFT(A1,1)="0", ISNUMBER(VALUE(A1)))
// Khong cho phep trung lap trong cot
=COUNTIF(A:A, A1)<=1
2.7 Validation voi Checkbox
Google Sheets cho phep tao checkbox (Data → Data validation → Criteria: Checkbox) voi gia tri tuy chinh (thay vi TRUE/FALSE mac dinh). Vi du: checked = "Da thanh toan", unchecked = "Chua thanh toan". Ket hop Conditional Formatting de to xanh hang da thanh toan — cuc ky truc quan.
3. Conditional Formatting: Tu Co Ban Den Nang Cao
Conditional Formatting (dinh dang co dieu kien) tu dong thay doi mau nen, mau chu, font, vien cua o dua tren gia tri hoac cong thuc. Day la cong cu bien bang tinh so lieu kho khan thanh dashboard truc quan — nhin vao la biet ngay dau can chu y.
Truy cap: Format → Conditional formatting. Google Sheets ho tro 2 che do:
Single color
Ap dung 1 dinh dang khi thoa dieu kien. VD: to do o co gia tri < 0, to xanh o co trang thai "Hoan thanh".
Color scale
Gradient mau tu min → max. VD: doanh thu thap = do, trung binh = vang, cao = xanh. Giong heatmap.
3.1 Conditional Formatting voi Custom Formula
Suc manh thuc su nam o che do "Custom formula is". Ban viet cong thuc tra ve TRUE → dinh dang duoc ap dung. Diem quan trong: tham chieu o phai tuong doi (khong co $) de ap dung cho toan bo range.
// To do ca hang neu trang thai la "Qua han"
=$E1="Qua han"
// To vang neu ngay het han trong 7 ngay toi
=AND($D1>TODAY(), $D1<=TODAY()+7)
// To dam hang chan (zebra striping)
=ISEVEN(ROW())
// Highlight o co gia tri trung lap
=COUNTIF(A:A, A1)>1
3.2 Uu tien quy tac (Rule Priority)
Khi nhieu quy tac cung ap dung cho mot o, Google Sheets uu tien quy tac nam tren cung trong danh sach. Ban keo tha de sap xep thu tu. Ngoai ra, tick "Stop if true" de dung kiem tra cac quy tac phia duoi khi quy tac hien tai da khop — tuong tu if/else trong lap trinh.
4. Ket Hop Validation + Formatting Tao Workflow Hoan Chinh
Khi ket hop Data Validation (kiem soat dau vao) voi Conditional Formatting (hien thi truc quan), ban tao ra mot he thong quan ly du lieu gan nhu tu dong. Duoi day la 3 vi du workflow thuc te:
Workflow 1: Quan ly don hang
- Validation: Cot "Trang thai" = dropdown (Cho xu ly / Dang giao / Da giao / Da huy)
- Validation: Cot "So luong" = Number between 1-9999
- Validation: Cot "Ngay giao" = Date after ngay dat
- Formatting: Cho xu ly = nen vang, Dang giao = nen xanh duong, Da giao = nen xanh la, Da huy = nen do + gach ngang
- Formatting: Don hang qua han giao (ngay giao < today ma chua giao) = vien do dam
Workflow 2: Quan ly ton kho
- Validation: Cot "Danh muc" = dependent dropdown theo nhom san pham
- Validation: Cot "So luong ton" = Number >= 0
- Formatting: Color scale cho cot ton kho: do (≤ muc toi thieu) → xanh (du hang)
- Formatting: To do dam + bold neu ton kho = 0 (het hang)
Workflow 3: Cham cong nhan vien
- Validation: Cot trang thai = dropdown (Di lam / Nghi phep / Nghi om / Nghi khong luong)
- Validation: Gio vao = Time, custom formula kiem tra gio vao < gio ra
- Formatting: Di muon (gio vao > 8:30) = to cam
- Formatting: Nghi khong phep = to do ca hang
Neu ban dang van hanh doanh nghiep nho va muon xay dung he thong quan ly hoan chinh tren Google Sheets, hay tham khao bai Xay dung ERP mini tren Google Sheets de hieu buc tranh tong the.
5. Custom Formula — Suc Manh Thuc Su Cua Nguoi Dung Nang Cao
Custom Formula la diem giao giua Data Validation va Conditional Formatting — ca hai deu ho tro. Duoi day la cac cong thuc nang cao ma dan Google Sheets pro thuong dung:
| Muc dich | Custom Formula | Dung trong |
|---|---|---|
| Chan nhap trung | =COUNTIF(A:A,A1)<=1 | Validation |
| Chi cho chu in hoa | =EXACT(A1,UPPER(A1)) | Validation |
| Gioi han ky tu (max 200) | =LEN(A1)<=200 | Validation |
| Highlight qua han | =AND($D1<TODAY(),$E1<>"Hoan thanh") | Formatting |
| Highlight top 10% | =A1>=PERCENTILE($A:$A,0.9) | Formatting |
| To mau hang cuoi tuan | =OR(WEEKDAY($A1)=1,WEEKDAY($A1)=7) | Formatting |
De tim hieu them ve cac ham nang cao nhu ARRAYFORMULA, LAMBDA, hay doc bai ARRAYFORMULA & LAMBDA trong Google Sheets.
6. 5 Bai Thuc Hanh Ung Dung Thuc Te
Bai 1: Tao form nhap don hang co kiem soat
Muc tieu: Sheet nhap don hang voi dropdown san pham, validation so luong (1-999), ngay giao phai sau ngay dat, email khach hang hop le. Conditional formatting to mau theo trang thai.
Ky thuat: List from range, Number between, Date after, REGEXMATCH, Custom formula formatting
Bai 2: Dashboard ton kho voi heatmap
Muc tieu: Bang ton kho su dung color scale hien thi muc ton. Validation ngan nhap so am. Alert tu dong khi ton duoi nguong toi thieu.
Ky thuat: Color scale, Number >= 0, Custom formula =$B1<$C1 (ton < toi thieu)
Bai 3: Bang cham cong voi dependent dropdown
Muc tieu: Chon phong ban → chi hien nhan vien thuoc phong do. To cam neu di muon, to do neu nghi khong phep. Tu dong dem ngay cong cuoi thang.
Ky thuat: INDIRECT + Named Range, Time validation, COUNTIFS
Bai 4: Tracker du an Gantt chart don gian
Muc tieu: Moi task co ngay bat dau, ngay ket thuc (validation: ket thuc > bat dau), trang thai, nguoi phu trach. Conditional formatting ve Gantt bar bang cach to mau cac o ngay.
Ky thuat: Date validation, Custom formula =AND(F$1>=$C2, F$1<=$D2)
Bai 5: CRM mini quan ly khach hang
Muc tieu: Quan ly pipeline ban hang voi dropdown giai doan (Lead → Qualified → Proposal → Closed Won/Lost). Email + SDT validation. To mau theo giai doan va highlight deal gia tri cao.
Ky thuat: List of items, REGEXMATCH, PERCENTILE, multi-condition formatting
Neu ban muon nang cap tu Google Sheets len he thong quan ly chuyen nghiep hon, bai huong dan tao Dashboard Google Sheets se giup ban xay dung giao dien tong quan dep mat va hieu qua.
7. Meo Nang Cao & Sai Lam Can Tranh
Nen lam
- Dat validation truoc khi nhap du lieu — phong benh hon chua benh
- Dung "Reject input" thay vi "Show warning" cho du lieu quan trong
- Viet help text ro rang trong "Validation help text" de nhan vien biet cach nhap
- Sap xep conditional formatting rules theo thu tu uu tien giam dan
- Test validation bang cach nhap co tinh sai de kiem tra
- Su dung Named Range thay vi hard-code range address
Tranh lam
- Tao qua nhieu conditional formatting rules (>20 rules) → sheet cham
- Dung $A$1 (tham chieu tuyet doi) trong custom formula formatting → chi kiem tra 1 o
- Quen "Stop if true" → nhieu rule chong cheo, ket qua kho doan
- Validation dung INDIRECT cross-sheet → khong hoat dong tren mobile
- Ap conditional formatting cho toan bo cot (A:A) → anh huong performance
- Khong viet help text → nhan vien khong biet tai sao bi loi
De tim hieu them cach bao mat du lieu va phan quyen truy cap Google Sheets, doc bai Bao mat Google Sheets: Phan quyen & Protect Sheet.
Can He Thong Google Sheets Chuyen Nghiep?
SheetStore setup san he thong quan ly voi Data Validation + Dashboard hoan chinh — ban chi can nhap lieu va theo doi.
8. Cau Hoi Thuong Gap (FAQ)
Data Validation co hoat dong tren Google Sheets mobile khong?
Co — dropdown va validation co ban hoat dong tot tren app Google Sheets iOS/Android. Tuy nhien, dependent dropdown dung INDIRECT cross-sheet co the khong hien thi dung tren mobile. Khuyen nghi: test tren mobile truoc khi deploy cho team.
Toi da bao nhieu conditional formatting rules tren 1 sheet?
Google Sheets khong gioi han cung, nhung thuc te nen giu duoi 20 rules/sheet de dam bao performance. Neu can nhieu hon, can nhac tach data ra nhieu sheet hoac dung Apps Script de format theo batch.
Lam sao copy Data Validation tu sheet nay sang sheet khac?
Cach 1: Copy o co validation → Paste Special → Paste data validation only (Ctrl+Shift+V, chon "Data validation"). Cach 2: Dung Apps Script voi method getDataValidations() va setDataValidations() de copy programmatically.
Conditional Formatting co anh huong toc do Google Sheets khong?
Co — dac biet khi dung custom formula tren range lon (ca cot A:A). Moi lan edit o, Google Sheets phai evaluate lai tat ca rules. Giai phap: gioi han range cu the (VD: A1:A1000 thay vi A:A), giam so rules, dung "Stop if true".
Co the dung Apps Script de tao Data Validation tu dong khong?
Hoan toan co the. Dung class SpreadsheetApp.newDataValidation() de tao validation rules bang code. Dieu nay dac biet huu ich khi can ap validation cho hang tram o theo pattern nhat quan — xem bai Apps Script cho nguoi moi de bat dau.
Chia sẻ bài viết:
Tuân Hoang
Đội ngũ SheetStore
Bạn thấy bài viết hữu ích?
Đăng ký nhận thông báo khi có bài viết mới.