Thời gian đọc ước tính: 9 phút
Những điểm chính
- Tự động hóa trực quan: Sử dụng Conditional Formatting để làm nổi bật ngay lập tức các tác vụ hoặc hóa đơn quá hạn.
- Hàm TODAY(): Tận dụng hàm này để Excel tự động cập nhật ngày hiện tại mỗi khi mở file, đảm bảo dữ liệu luôn chính xác theo thời gian thực.
- Linh hoạt trong hiển thị: Có thể tô màu một ô chứa ngày tháng hoặc tô màu cả hàng dữ liệu liên quan.
- Kiểm soát phạm vi: Thiết lập các quy tắc phức tạp như tô màu các ngày trong khoảng 30, 60 hoặc 90 ngày gần nhất.
Việc quản lý thời hạn công việc, lịch trình dự án hay theo dõi hóa đơn trong Excel đòi hỏi sự chính xác cao về mặt thời gian. Một danh sách dài với hàng trăm dòng dữ liệu ngày tháng có thể khiến bạn bỏ sót những mốc thời gian quan trọng. Thay vì phải dò tìm thủ công từng dòng để xem ngày nào đã trôi qua, bạn có thể thiết lập để Excel tự động cảnh báo bằng màu sắc.
Tính năng Conditional Formatting (Định dạng có điều kiện) kết hợp với hàm thời gian là giải pháp tối ưu cho vấn đề này. Bài viết dưới đây sẽ hướng dẫn chi tiết các phương pháp từ cơ bản đến nâng cao để làm nổi bật các ngày trước hôm nay, giúp bạn duy trì sự chủ động trong công việc.
Lưu ý: Các ví dụ trong bài viết sử dụng ngày hiện tại của hệ thống máy tính để làm mốc so sánh. Kết quả hiển thị trên file của bạn sẽ phụ thuộc vào ngày bạn thực hiện thao tác.
1. Sử Dụng Quick Analysis Tool (Công Cụ Phân Tích Nhanh)
Đối với các phiên bản Excel từ 2013 trở lên, công cụ Quick Analysis là cách nhanh nhất để áp dụng các định dạng cơ bản mà không cần điều hướng qua nhiều menu phức tạp. Phương pháp này phù hợp khi bạn muốn thao tác nhanh trên một vùng dữ liệu nhỏ hoặc vừa.
Các bước thực hiện:
Giả sử bạn có một cột chứa ngày hết hạn (Due Dates) và cần đánh dấu những ngày đã trôi qua so với hiện tại.
Bảng dữ liệu mẫu với cột ngày tháng trong Excel
- Chọn vùng dữ liệu: Bôi đen các ô chứa ngày tháng bạn cần phân tích.
- Kích hoạt Quick Analysis: Nhấp vào biểu tượng nhỏ xuất hiện ở góc dưới bên phải của vùng chọn (hoặc nhấn phím tắt
Ctrl + Q).
Nhấp vào biểu tượng Quick Analysis Tool
- Chọn điều kiện: Chuyển sang thẻ Formatting, sau đó chọn Less Than… (Nhỏ hơn).
Chọn tùy chọn Less than trong menu Quick Analysis
- Nhập công thức: Trong hộp thoại hiện ra, bạn nhập công thức sau vào ô giá trị:
=TODAY()Hàm này đại diện cho ngày hiện tại của hệ thống.
Nhập công thức Today vào hộp thoại Less than
- Thiết lập định dạng: Tại menu thả xuống bên cạnh, chọn kiểu hiển thị mong muốn (ví dụ: Light Red Fill with Dark Red Text – Nền đỏ nhạt với chữ đỏ đậm).
Chọn định dạng màu sắc cho ngày quá hạn
- Xác nhận: Nhấn OK để hoàn tất.
Kết quả là tất cả các ô có ngày tháng nhỏ hơn ngày hiện tại sẽ được tô màu tự động.
Các ngày trước hôm nay đã được tô màu đỏ
Lưu ý quan trọng: Công thức =TODAY() sẽ không bao gồm ngày hôm nay. Nếu bạn muốn tô màu cả ngày hôm nay và các ngày trước đó, hãy sử dụng công thức =TODAY()+1 ở bước 4.
2. Sử Dụng Conditional Formatting Trên Thanh Ribbon
Nếu bạn cần nhiều tùy chọn tùy chỉnh hơn hoặc đang sử dụng các phiên bản Excel cũ hơn không hỗ trợ Quick Analysis, việc sử dụng menu Conditional Formatting trên thanh Ribbon là phương pháp tiêu chuẩn. Cách này giúp bạn kiểm soát chi tiết hơn về quy tắc và định dạng.
Tô màu ô chứa ngày tháng
Quy trình này tương tự như Quick Analysis nhưng được thực hiện qua menu chính:
- Chọn vùng dữ liệu: Bôi đen các ô ngày tháng (ví dụ B2:B9).
- Truy cập menu: Tại thẻ Home, tìm nhóm Styles và chọn Conditional Formatting.
Chọn thẻ Home trên thanh công cụ Excel
Nhấp vào biểu tượng Conditional Formatting
- Tạo quy tắc: Di chuột đến Highlight Cell Rules và chọn Less Than….
Chọn Less than trong danh sách quy tắc
- Nhập hàm thời gian: Trong hộp thoại, nhập
=TODAY()vào ô giá trị bên trái.
Nhập công thức Today để xác định ngày quá hạn
- Tùy chỉnh giao diện: Chọn màu sắc hiển thị ở ô bên phải và nhấn OK.
Cài đặt định dạng hiển thị cho các ô thỏa mãn điều kiện
Excel sẽ quét qua toàn bộ vùng chọn và áp dụng định dạng cho các ô thỏa mãn điều kiện nhỏ hơn ngày hiện tại.
Kết quả các ngày quá hạn được làm nổi bật
3. Kỹ Thuật Nâng Cao: Tô Màu Toàn Bộ Hàng (Highlight Entire Row)
Việc chỉ tô màu một ô chứa ngày tháng đôi khi không đủ nổi bật, đặc biệt trong các bảng dữ liệu rộng có nhiều cột thông tin. Kỹ thuật tô màu cả hàng dựa trên giá trị của một ô là một kỹ năng quan trọng giúp bảng tính chuyên nghiệp và dễ đọc hơn.
Phương pháp này đòi hỏi sự hiểu biết về tham chiếu hỗn hợp (mixed reference) trong công thức Excel.
Quy trình thực hiện:
Giả sử bạn muốn tô màu cả dòng (bao gồm Tên tác vụ và Ngày hết hạn) nếu ngày đó đã qua.
- Chọn toàn bộ vùng dữ liệu: Bôi đen từ ô A2 đến B9 (lưu ý không chọn tiêu đề cột).
- Tạo quy tắc mới: Vào Home > Conditional Formatting > New Rule.
Chọn New Rule để tạo quy tắc mới
- Sử dụng công thức: Trong hộp thoại, chọn dòng “Use a formula to determine which cells to format”.
Chọn tùy chọn sử dụng công thức để định dạng
- Thiết lập logic: Nhập công thức sau vào ô “Format values where this formula is true”:
=$B2<TODAY()
Nhập công thức khóa cột B vào trường giá trị
- Định dạng: Nhấn nút Format, chuyển sang thẻ Fill và chọn màu nền (ví dụ: màu vàng).
Chọn nút Format để mở bảng định dạng
Chọn màu vàng trong thẻ Fill
- Hoàn tất: Nhấn OK hai lần để áp dụng.
Giải thích cơ chế hoạt động:
Tại sao lại dùng $B2 mà không phải B2 hay $B$2?
- Dấu $ trước B ($B): Khóa cố định cột B. Khi Excel xét định dạng cho ô A2, nó vẫn nhìn vào giá trị ở cột B (B2) để so sánh. Điều này đảm bảo tất cả các ô trong cùng một hàng đều phụ thuộc vào điều kiện của cột Ngày tháng.
- Không có dấu $ trước số 2: Cho phép dòng thay đổi. Khi Excel kiểm tra xuống dòng 3, công thức tự động chuyển thành
$B3 < TODAY().
Kết quả là toàn bộ hàng sẽ được tô màu nếu ngày ở cột B nhỏ hơn hôm nay.
Toàn bộ hàng dữ liệu được tô màu dựa trên ngày tháng
4. Tô Màu Trong Khoảng Thời Gian (30/60/90 Ngày)
Trong kinh doanh, bạn thường cần theo dõi các hóa đơn đã thanh toán trong 30 ngày qua hoặc các tác vụ vừa hoàn thành gần đây. Việc này giúp phân loại dữ liệu theo các khung thời gian cụ thể thay vì chỉ đơn thuần là “quá khứ”.
Ví dụ: Bạn muốn tô màu các ngày nằm trong khoảng từ 30 ngày trước đến ngày hôm nay.
Dữ liệu mẫu để tô màu ngày trong khoảng 30 ngày gần nhất
Các bước thực hiện:
- Chọn vùng ngày tháng: Bôi đen cột ngày cần định dạng.
- Mở quy tắc Between: Vào Home > Conditional Formatting > Highlight Cell Rules > Between…
Chọn tùy chọn Between để thiết lập khoảng giá trị
- Thiết lập cận dưới: Ở ô đầu tiên, nhập công thức để xác định mốc 30 ngày trước:
=TODAY()-30
Nhập công thức Today trừ 30 ngày
- Thiết lập cận trên: Ở ô thứ hai, nhập công thức ngày hiện tại:
=TODAY()
Nhập công thức Today cho mốc hiện tại
- Chọn màu sắc: Lựa chọn định dạng hiển thị và nhấn OK.
Chọn màu sắc hiển thị cho khoảng thời gian
Kết quả trả về sẽ làm nổi bật các ô có giá trị ngày tháng nằm trong vòng 30 ngày qua. Nếu bạn muốn loại trừ ngày hôm nay, hãy thay đổi công thức cận trên thành =TODAY()-1.
Kết quả hiển thị các ngày trong khoảng 30 ngày qua
5. Những Điều Cần Lưu Ý Khi Làm Việc Với Ngày Tháng Trong Excel
Để việc áp dụng Conditional Formatting diễn ra suôn sẻ và chính xác, bạn cần nắm vững một số nguyên tắc cơ bản về cách Excel xử lý dữ liệu thời gian:
Bản chất của hàm TODAY()
Hàm =TODAY() là một hàm “biến động” (volatile). Giá trị của nó không cố định mà sẽ tự động cập nhật mỗi khi file Excel được mở hoặc khi bảng tính được tính toán lại (recalculated).
- Ưu điểm: Báo cáo của bạn luôn “sống”. Nếu bạn mở file vào ngày mai, các quy tắc tô màu sẽ tự động điều chỉnh theo ngày mới.
- Nhược điểm: Nếu bạn thay đổi ngày giờ hệ thống trên máy tính sai lệch, Excel cũng sẽ hiển thị sai theo.
Kiểm tra định dạng dữ liệu (Date vs. Text)
Một lỗi phổ biến khiến Conditional Formatting không hoạt động là dữ liệu ngày tháng đang được lưu dưới dạng văn bản (Text).
- Dấu hiệu: Ngày tháng căn lề trái (mặc định số và ngày tháng căn lề phải).
- Cách sửa: Sử dụng công cụ
Text to Columnshoặc hàmDATEVALUEđể chuyển đổi về định dạng ngày tháng chuẩn (Serial Number) mà Excel có thể hiểu và so sánh được.
Quản lý các quy tắc (Manage Rules)
Nếu bạn áp dụng nhiều quy tắc tô màu trên cùng một vùng dữ liệu, thứ tự ưu tiên rất quan trọng.
- Truy cập Conditional Formatting > Manage Rules.
- Quy tắc nằm trên cùng sẽ được ưu tiên thực thi trước.
- Sử dụng nút mũi tên lên/xuống để sắp xếp lại thứ tự ưu tiên nếu màu sắc hiển thị không đúng ý muốn.
Sử dụng phím tắt và thao tác nhanh
- Ctrl + ; : Điền nhanh ngày hiện tại vào một ô (giá trị tĩnh, không thay đổi theo thời gian như hàm TODAY).
- Ctrl + 1: Mở hộp thoại Format Cells để kiểm tra định dạng hiển thị của ngày tháng.
FAQ: Câu Hỏi Thường Gặp
Hỏi: Làm sao để xóa bỏ các quy tắc tô màu đã tạo?
Đáp: Bạn hãy bôi đen vùng dữ liệu, vào Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Để xóa toàn bộ quy tắc trong trang tính, chọn Clear Rules from Entire Sheet.
Hỏi: Tôi muốn tô màu các ngày sắp đến hạn (trong tương lai) thì làm thế nào?
Đáp: Bạn có thể sử dụng quy tắc Between. Ví dụ, để tô màu các ngày sắp đến trong 7 ngày tới, cận dưới là =TODAY() và cận trên là =TODAY()+7.
Hỏi: Tại sao tôi làm đúng công thức mà ô không đổi màu?
Đáp: Hãy kiểm tra lại hai khả năng: Thứ nhất, ô dữ liệu của bạn có thể đang ở dạng Text (văn bản) thay vì Date. Thứ hai, hãy kiểm tra lại vùng áp dụng (Applies to) trong phần Manage Rules xem đã bao trùm đúng các ô cần thiết chưa.
Hỏi: Hàm TODAY() cập nhật vào lúc nào?
Đáp: Hàm này cập nhật giá trị ngay khi bạn mở file Excel hoặc khi bạn nhấn phím F9 để buộc bảng tính tính toán lại. Nó lấy thông tin trực tiếp từ đồng hồ hệ thống của máy tính bạn đang sử dụng.
Nguồn tham khảo
Để tìm hiểu sâu hơn về các hàm ngày tháng và định dạng nâng cao, bạn có thể tham khảo các tài liệu kỹ thuật sau:
- Microsoft Support: Hàm TODAY trong Excel – Tài liệu chính thức từ Microsoft giải thích chi tiết về cú pháp và cách sử dụng hàm.
- Microsoft Support: Sử dụng định dạng có điều kiện để tô sáng thông tin – Hướng dẫn toàn diện về Conditional Formatting.
- GcfGlobal: Excel: Conditional Formatting – Bài học trực quan về các ứng dụng của định dạng có điều kiện (Tiếng Anh).
Việc áp dụng thành thạo các kỹ thuật trên không chỉ giúp bảng tính của bạn trở nên chuyên nghiệp, dễ nhìn mà còn giảm thiểu rủi ro sai sót trong việc quản lý thời gian. Hãy bắt đầu thực hành ngay trên dữ liệu của bạn để thấy sự hiệu quả.
