Conditional formatting trong Excel là gì?

Conditional formatting trong Excel là gì?

- Conditional formatting trong Excel là một tính năng trong nhiều ứng dụng bảng tính cho phép bạn áp dụng định dạng cụ thể cho các ô đáp ứng các tiêu chí nhất định. Nó thường được sử dụng để đổi màu ô trong Excel theo điều kiện, nhấn mạnh hoặc phân biệt giữa dữ liệu và thông tin được lưu trữ trong bảng tính. 

- Conditional formatting trong Excel cho phép người dùng có thể thực hiện việc gây chú ý cho các điểm dữ liệu thông tin quan trọng và đặc biệt có thể chuyển đổi trang tính của bạn từ nơi lưu trữ dữ liệu thành một hệ thống lanh lẹ tin cậy, làm nổi bật các thông tin chính và giúp bạn đạt được khối lượng công việc.

- Nhìn chung, áp dụng định dạng có điều kiện là một cách dễ dàng để giữ cho bạn và các thành viên trong nhóm cập nhật dữ liệu của bạn - kêu gọi sự chú ý trực quan đến các ngày và thời hạn quan trọng, nhiệm vụ và bài tập,...do vậy mà việc sử dụng đúng định dạng sẽ giúp bạn làm việc hiệu quả hơn bằng cách giảm thời gian sử dụng dữ liệu thủ công và giúp xác định xu hướng dễ dàng hơn.

Cách sử dụng Conditional formatting trong Excel

Conditional formatting được sử dụng phổ biến với quy tắc nổi bật áp dụng định dạng màu cho các ô đáp ứng các tiêu chí cụ thể đã xác định. Chúng là loại quy tắc định dạng có điều kiện cơ bản nhất và cung cấp nhiều hàm tô màu trong Excel theo điều kiện.

Tô màu có điều kiện trong excel

Mở một bảng tính trong Excel hoặc nếu chưa có hãy lập một bảng tính mới, ví dụ dưới đây là cho ở bảng và tìm các tháng có doanh số bán < 100, 100 - 300 và > 300.

Ví dụ tô màu có điều kiện trong Excel. Hình 1 

1. Tìm giá trị các ô < 100

Thực hiện các thao tác từ bước chọn vùng các giá trị của dữ liệu và làm theo các bước như trong hình bên dưới:

Ví dụ tô màu có điều kiện trong excel. Hình 2

Trong phần Format cells that are LESS THAN của ô cửa sổ Less Than nhập giá trị 100 và cùng dòng là với nơi chọn màu --> OK.

Ví dụ tô màu có điều kiện trong Excel. Hình 3

Khi OK cho ra được kết quả như trong hình, các giá trị < 100 được bôi đậm màu đỏ:

Ví dụ tô màu có điều kiện trong Excel. Hình 4

2. Tô màu các ô có giá trị 100 - 300

Áp dụng Conditional formatting trong Excel, bạn thực hiện các thao tác như các bước đầu của phần 1 cho các giá trị < 100, nhưng ở thao tác thứ 5 chọn Between:

Ví dụ tô màu có điều kiện trong Excel. Hình 5

Nhập các giá trị trong phần cửa sổ Between, chọn từ vùng giá trị 100 đến 300 và chọn màu đánh dấu khác với nhóm thứ nhất như hình bên dưới đây:

Ví dụ tô màu có điều kiện trong excel. Hình 6

OK và kết quả cho ra được như trong bảng:

Ví dụ tô màu có điều kiện trong Excel. Hình 7

3. Tìm các giá trị > 300

Cũng thực hiện các thao tác như trong các bước của tô màu cho các giá trị thuộc 2 vùng trên, ở mục này chọn Greater Than:

Ví dụ tô màu có điều kiện trong Excel. Hình 8

Nhập giá trị là 300, và lựa chọn màu không trùng lặp với 2 vùng trên cho cửa sổ mới mở ra:

Ví dụ tô màu có điều kiện trong Excel. Hình 9

Chọn ok và cho ra kết quả ở bảng dữ liệu:

Ví dụ tô màu có điều kiện trong Excel. Hình 10

Hơn hết, nếu bạn đọc quan tâm cần tìm hiểu kỹ hơn công cụ tin học văn phòng Excel và muốn làm chủ Excel cùng đăng ký ngay khóa học  "MICROSOFT EXCEL 2013 NÂNG CAO" của giảng viên Nguyễn Quang Vinh trên UNICA để được học nhiều và sâu sắc hơn cho việc sử dụng Conditional formatting. 

 Giới thiệu

Định dạng có điều kiện (Conditional Formatting) là một công cụ mạnh mẽ và có thể thay đổi “diện mạo” của một ô dựa trên giá trị của nó, giúp người xem xác định dữ liệu quan trọng nhanh chóng. Bạn có thể thêm màu sắc, icon, thanh dữ liệu và thang màu vào ô bằng cách tạo quy tắc định dạng có điều kiện.

Đánh dấu các giá trị trùng lặp bằng màu đỏ

Định dạng có điều kiện có thể được sử dụng để làm nổi bật các giá trị trùng lặp, giúp dễ dàng xác định và sửa lỗi trong dữ liệu.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là các ô từ A2: A9.

Bước 2. Chuyển đến Home > Conditional Formatting.

Bước 3.Click vào Highlight Cells Rules > Duplicate Values.

Bước 4. Click OK. Bạn cũng có thể chọn các màu ô khác từ danh sách thả xuống bên cạnh danh sách mặc định.

Sử dụng Data Bars để tạo Progress Bars (thanh tiến trình)

Data Bars có thể được sử dụng để thể hiện dữ liệu bằng đồ thị bên trong một ô. Thanh dài nhất thể hiện giá trị cao nhất và thanh ngắn nhất cho thấy giá trị nhỏ nhất. Data Bars này giúp người dùng phát hiện số lượng lớn và nhỏ dễ dàng trong bảng tính.

Trong ví dụ này, bạn sẽ xem nhanh tiến trình của các dự án.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là B2: B6.

Bước 2. Đi tới Home > Conditional Formatting.

Bước 3. Click vào Data Bars > Blue Data Bar.

Bước 4. Bạn cũng có thể chọn các thanh màu khác nếu muốn.

Xem thêm: Cách tạo Progress bar bằng định dạng có điều kiện trong Excel 2013, 2010 và 2007

Sử dụng các quy tắc Top/Bottom Rules để làm nổi bật 3 sản phẩm hàng đầu

Một cách tuyệt vời khác để sử dụng định dạng có điều kiện là chọn tùy chọn Top/Bottom Rules. Bạn có thể dễ dàng đánh dấu 3 mục hàng đầu trong danh sách. Để xác định ba sản phẩm hàng đầu tạo ra doanh số bán hàng nhiều nhất trong cửa hàng, bạn có thể tạo một quy tắc hàng đầu trong định dạng có điều kiện.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là B2: B8.

Bước 2. Vào Home > Conditional Formatting.

Bước 3. Click vào Top/Bottom Rules > Top 10 Items…

Bước 4. Mặc định là 10 mục nhưng bạn có thể nhập số 3 hoặc sử dụng nút thêm hoặc bớt.

Bước 5. Bạn cũng có thể chọn các màu ô khác từ danh sách thả xuống bên cạnh danh sách mặc định.

Sử dụng Color Scales để tạo ra một bản đồ nhiệt

Bản đồ nhiệt là gì?

Bản đồ nhiệt là bảng mà dữ liệu trong bảng tính được hiển thị bằng màu sắc. Bạn có thể hiển thị giá trị trong ô hoặc không nếu muốn. Ẩn giá trị bằng cách sử dụng định dạng ô.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là B2: D10.

Bước 2. Vào Home > Conditional Formatting.

Bước 3. Click vào Color Scales > More Rules…

Bước 4. Mặc định là 2-Color Scale nhưng bạn có thể chọn 3-Color Scale từ danh sách sổ xuống.

Bước 5. Đối với ví dụ này, màu xanh nhạt, màu xanh và màu xanh đậm được chọn. Khi chọn màu cho bản đồ nhiệt, bạn nên chọn tông màu khác nhau cho cùng một màu. Bởi vì nếu chọn nhiều màu khác nhau sẽ làm người xem nhầm lẫn, khó xác định ô nào có giá trị thấp nhất hoặc cao nhất. Tuy nhiên, khi chọn tông màu khác nhau cho cùng một màu, người xem có thể nhanh chóng nhận ra giá trị cao nhất và thấp nhất.

Ngoài ra, nếu muốn ẩn các giá trị trong bảng và chỉ hiển thị màu trong bản đồ nhiệt, bạn có thể click chuột phải vào bảng tính, click vào Format cells > Custom > Enter “;;;” trong kiểu hộp và click vào OK. Giá trị vẫn ở trong bảng nhưng được ẩn.

Bản đồ nhiệt sau khi ẩn giá trị:

Sử dụng Icon Sets để xác định dữ liệu bị thiếu trong Timesheet

Nhiều ý kiến cho rằng, định dạng có điều kiện chỉ có thể được sử dụng để định dạng ô dựa trên giá trị của nó. Tuy nhiên, bạn có thể sử dụng công thức để nhận giá trị dựa trên các ô trong cùng một hàng và sau đó sử dụng định dạng có điều kiện để nhận kết quả mong muốn.

Hãy xem ví dụ này để hiểu rõ hơn. Trong ví dụ này, chúng ta sẽ thực hiện công thức trong ô E4 để tính số ô đã được điền dữ liệu từ A5 đến D5. Vì tất cả các dữ liệu cần thiết đã được lấp đầy trong hàng 5, nên công thức hiển thị kết quả 4 ô đã được điền giá trị. Tuy nhiên trong hàng 7, chỉ có 1 ô có dữ liệu và 3 ô trống. Do đó, hàng này cần được làm nổi bật bằng cách sử dụng Icon Sets để xác định hàng cần điều chỉnh.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là E5: E9.

Bước 2. Chuyển đến Home > Conditional Formatting.

Bước 3. Click vào Icon Sets > More Rules…

Bước 4. Tùy chọn mặc định ở đây là 3 đèn giao thông, sau đó chọn 3 biểu tượng (hình tròn) từ danh sách thả xuống.

Bước 5. Đánh dấu vào ô “Show Icon only”.

Bước 6. Click vào biểu tượng cuối cùng và thay đổi “Red Cross Symbol” thành “No Cell Icon”.

Bước 7. Click vào biểu tượng thứ 2, thay đổi “Yellow Exclamation Symbol” thành “Red Cross Symbol”, thay đổi giá trị thành 1 và thay đổi Type từ “Percent” thành “Number”.

Bước 8. Đối với biểu tượng đầu tiên là “Green Check Symbol”, thay đổi giá trị thành 4 và thay đổi Type từ “Percent” thành “Number”.

Sau khi hoàn thành các bước trên, nếu có dữ liệu chưa hoàn thành trong bất kỳ ô nào trong cột A-D của cùng một hàng, sẽ có một dấu gạch chéo trong cột E. Điều này sẽ cho phép người dùng nhanh chóng xác định dữ liệu chưa hoàn chỉnh và sửa chữa nó. Khi tất cả bốn ô từ cột A-D được nhập vào cùng hàng, dấu check màu xanh lá cây sẽ xuất hiện. Điều này sẽ cho phép người dùng biết rằng tất cả dữ liệu đã được nhập và không thiếu dữ liệu.

Xóa quy tắc

Xác định các ô có chứa định dạng có điều kiện

Trước khi xóa mọi định dạng có điều kiện, bạn cần xác định ô chứa định dạng có điều kiện. Để làm điều này, chọn tất cả các ô trên bảng tính sử dụng Ctrl + A, click vào Special > Conditional formats > OK.

Thao tác này sẽ cho bạn thấy các ô được đánh dấu từ E5 đến E9 chứa định dạng có điều kiện.

Xóa ô chứa định dạng có điều kiện

Các bước thực hiện:

Bước 1. Vào Home > Conditional Formatting.

Bước 2. Click vào Clear Rules > Clear Rules from Selected Cells.

Xóa nhiều quy tắc trong cùng một trang tính

Ngoài ra, cũng có một cách khác để xóa định dạng có điều kiện. Phương thức này sẽ hiển thị tất cả các ô sử dụng định dạng có điều kiện trên cùng một trang tính và cho phép bạn xóa nhiều quy tắc một cách dễ dàng.

Các bước thực hiện:

Bước 1. Vào Home > Conditional Formatting.

Bước 2. Click vào Manage Rules.

Bước 3. Thay đổi quy tắc Show formatting từ Current Selection thành This Worksheet. Điều này sẽ hiển thị tất cả các quy tắc trong bảng tính này.

Bước 4. Click vào quy tắc bạn muốn xóa → click vào Delete Rule → OK.

Bước 5. Lặp lại Bước 4 đối với các quy tắc mà bạn muốn xóa.

Chúc các bạn thực hiện thành công!

In bảng tính

 1. Thay đổi tùy chọn thiết lập trang:

• Từ menu File > Print > Page Setup như hình dưới:

• Hộp thoại Page Setup xuất hiện, chúng ta có thể thấy có nhiều tùy chọn tương ứng với mỗi tab. Chọn Page:

• Thiết lập định hướng trang in: trong phần Orientation, kích vào các nút chọn Portrait (quay giấy dọc) hoặc Landscape (quay giấy ngang)
• Thiết lập kích thước trang in:trong phần Scaling, chọn Adjust to: nhập tỷ lệ phần trăm (bạn có thể kích nút lên hoặc nút xuống để tăng hoặc giảm kích thước).
• Chọn Fit to: thiết lập độ rộng và chiều cao cho trang.

Thiết lập những lựa chọn khác cho trang in:

Paper size	Trong mục Paper size: lựa chọn khổ giấy từ danh sách xổ xuống
Print quality	Trong mục Print quality: chọn chất lượng in từ danh sách xổ xuống (dpi càng cao– chất lượng càng tốt)
Page numbering	Để bắt đầu đánh số trang, chọn mục First page number: nhập số trang bạn muốn

2. Căn lề trang in:

Căn lề trang in trong Microsoft Excel bao gồm căn chỉnh lề trên, lề dưới, lề trái, phải của trang. Bạn cũng có thể căn chỉnh để dữ liệu đặt ở giữa trang.

Để thay đổi lề trang:

• Trong Page Setup, chọn tab Margins

• Kích lần lượt Top, Bottom, Left, Right và thay đổi các thiết lập.
• Kích vào Header để thay đổi lề đầu trang và Footer để thay đổi lề chân trang.
• Kích OK.

Chú ý là tại phần Center on page, bạn có thể chọn Horizontally (theo chiều ngang) hoặc Vertically (theo chiều dọc) để có được vị trí đặt dữ liệu tốt nhất.

3. Thiết lập Header/Footer:

Thiết lập đầu trang (Header) và chân trang (Footer) rất quan trọng, nó tác động đến cách nhìn tổng quát của một trang in.

Thay đổi tiêu đề trang và chân trang:

• Chọn tab Header/Footer từ hộp thoại Page Setup

• Kích vào mũi tên xổ xuống của phần Header: các kiểu header đã có sẵn trong danh sách, chỉ việc lựa chọn kiểu đặt header phù hợp:

• Kích nút Custom Header hoặc Custom Footer để hiển thị hộp thoại Header (hoặc Footer):

• Trong phần Left section: Nhập dữ liệu để xuất hiện ở lề trái của header hay footer.
• Trong phần Center section: Nhập dữ liệu để xuất hiện ở giữa của header hoặc footer.
• Trong phần Right section: Nhập dữ liệu để xuất hiện ở lề phải của header hoặc footer.

• Bạn cũng có thể lựa chọn các tùy chọn sau:

Format font	Kích vào nút format font (biểu tượng A) sau khi đánh dấu đoạn cần thay đổi font, cỡ và kiểu chữ
Page number	Chèn số vào mỗi trang
Number of pages	Sử dụng tính năng đánh số trang để tạo chuỗi các trang từ trang 1 đến 15
Date	Đưa vào ngày hiện tại
Time	Đưa vào giờ hiện tại
File path	Chèn thêm tên đường dẫn (vị trí file)
File name	Thêm tên vào file workbook
Tab name	Thêm tên vào tab worksheet
Insert picture	Chọn vì trí để chèn ảnh
Format picture	Định dạng chèn ảnh

• Kích OK sau khi kết thúc.
• Tiêu đề trang (chân trang) mới sẽ được hiển thị trong hộp thoại Page Setup.
• Kích OK để đóng hộp thoại Page Setup.

4. Excel Printing – Thiết lập bảng tính:

Trong việc in ấn Excel, thiết lập trang tính rất quan trọng. Nó sẽ kiểm soát trang nào được in và nơi để in. Bởi vậy phải chắc chắn bạn thực hiện những bước sau thật cẩn thận.

Thay đổi các tùy chọn trang tính:

• Từ menu File, chọn Page Setup.
• Hộp thoại Page Setup hiển thị, chọn tab Sheet

• Thay đổi các tính năng sau:

Print area	Nhập bảng tính muốn in, hoặc kích vào biểu tượng bên phải hộp và kéo worksheet xuống để in
Rows to repeat at top	Kích vào biểu tượng bên phải hộp và kéo dòng bạn muốn để lặp lại phía trên cùng của trang. Với cách này thì dòng đó sẽ xuất hiện ở mọi trang tính.
Columns to repeat at left	Kích vào biểu tượng phía bên phải hộp và kéo cột bạn muốn lặp lại phía bên trái của trang. Với cách này, khi in thì cột bên trái xuất hiện ở mọi trang tính
Elements that will print	Tích vào các hộp trong mục Print nếu muốn. Ví dụ Gridlines, Black and White (đen trắng), Draft Quality (in thử), Row and Column Headings (đề mục hàng và cột)
Page order	Trong mục Page order, lựa chọn Down, then over hoặc Over, then down. Bạn sẽ thấy được hiệu ứng này khi dữ liệu trong trang tính không đủ trong một trang in

• Một số đặc tính ở mục Print (khi tích vào những lựa chọn này):

Gridlines	Sẽ in cả phần lưới của bảng tính Excel
Black and white	In dữ liệu theo màu đen và trắng
Draft quality	In với chất lượng thử, không phải chất lượng in thông thường
Row and column headings	Bảng tính được in bao gồm tiêu đề hàng và cột

• Phía bên phải hộp thoại, có 3 nút:

Options	Xem lại việc cài đặt máy in
Print Preview	Xem lại trang tính trước khi in
Print	In trang tính.

Thay đổi cài đặt in trong Excel:

• Từ menu File, chọn Print hoặc bấm phím tắt Ctrl + P.
• Hộp thoại Print hiển thị, thực hiện những thay đổi cần thiết sau:

• Ở phần Printer, mục Name: lựa chọn máy in bạn muốn dùng.
• Trong phần Page range, lựa chọn All nếu bạn muốn in tất cả các trang trong một vùng, hoặc nhập trang bắt đầu in vào From và kết thúc vào To.
• Trong phần Print what, lựa chọn phần muốn in. Ví dụ, Phần được chọn, phần trang tính, hay toàn bộ sổ làm việc.
• Trong mục Number of copies: chỉ định số bản được in ra.
• Kích vào nút Properties để hiển thị hộp thoại Properties.
• Thay đổi các lựa chọn. Lựa chọn này sẽ làm thay đổi kết quả trên máy in.
• Kích OK để đóng hộp thoại.
• Kích tiếp OK để bắt đầu in worksheet.

Chú ý: Trước khi kích nút OK, phải chắc chắn là bạn đã đặt giấy vào khay của máy in. Chúc các bạn thành công!