Microsoft Excel cung cấp rất nhiều công cụ để định dạng dữ liệu theo nhiều cách khác nhau. Tuy nhiên, đôi khi các công cụ tích hợp sẵn lại không hoàn toàn đáp ứng được nhu cầu mong muốn hoặc tốn quá nhiều thời gian để thực hiện. Trong những tình huống như vậy, việc sử dụng định dạng số tùy chỉnh (Custom Number Formatting) là giải pháp hiệu quả để nhanh chóng tạo ra định dạng số phù hợp với yêu cầu cụ thể của bạn.
Mặc dù có một số trường hợp nên ưu tiên sử dụng định dạng có điều kiện (Conditional Formatting) thay vì định dạng số tùy chỉnh, ví dụ như tô màu toàn bộ hàng dựa trên một giá trị nhất định. Tuy nhiên, trong những tình huống mà cả hai cách đều có thể mang lại kết quả tương tự hoặc giống nhau, chúng tôi thường chọn định dạng số tùy chỉnh. Lý do là gì? Bài viết này sẽ đi sâu vào giải thích.
Định dạng số tùy chỉnh (Custom Number Formatting) là gì và hoạt động như thế nào?
Trong Excel, mỗi ô đều có một định dạng số riêng. Bạn có thể xem định dạng này bằng cách chọn một ô và nhìn vào nhóm “Number” trên tab “Home” của dải băng (Ribbon).
Biểu đồ Excel hiển thị ô L1 chứa giá trị phần trăm và định dạng số tương ứng trong nhóm Number trên tab Home.
Một trong những tùy chọn định dạng số mà bạn có thể áp dụng cho một ô là định dạng số tùy chỉnh. Bạn có thể truy cập tùy chọn này bằng cách nhấp vào biểu tượng khởi chạy hộp thoại “Number Format” (mũi tên nhỏ ở góc dưới bên phải nhóm Number), sau đó nhấp vào “Custom” (Tùy chỉnh) trong hộp thoại “Format Cells” (Định dạng ô).
Hộp thoại khởi chạy định dạng số trong Excel được đánh dấu, và tùy chọn định dạng tùy chỉnh trong hộp thoại Format Cells được chọn.
Nếu bạn là người dùng thành thạo và thích sử dụng các phím tắt Excel, hãy nhấn Ctrl + 1 để mở hộp thoại “Format Cells”, sau đó nhấn Tab để kích hoạt menu “Category” và gõ Cus để nhanh chóng đến mục “Custom”.
Khi hoàn thành hướng dẫn này, bạn sẽ có thể sử dụng định dạng số tùy chỉnh để tạo ra các bảng tính trông giống như ví dụ dưới đây, với khả năng hiển thị dữ liệu trực quan và chuyên nghiệp.
Danh sách đội thể thao trong Excel hiển thị tổng điểm hai mùa và phần trăm thay đổi, đã được định dạng bằng định dạng số tùy chỉnh.
Nếu bạn chưa từng sử dụng định dạng tùy chỉnh trước đây, ban đầu nó có vẻ khó hiểu và bạn có thể nghĩ rằng sử dụng định dạng có điều kiện sẽ là lựa chọn tốt hơn. Tuy nhiên, Excel xử lý định dạng số tùy chỉnh nhanh hơn nhiều so với định dạng có điều kiện. Và khi bạn đã hiểu được logic của nó, bạn sẽ thấy nó nhanh hơn và dễ sử dụng hơn rất nhiều.
Một trong những lý do chính khiến chúng tôi ưa chuộng định dạng số tùy chỉnh là mọi thao tác đều diễn ra tại một nơi duy nhất. Bạn không cần phải chuyển đổi qua lại giữa các hộp thoại khác nhau để thêm các quy tắc định dạng riêng biệt, giúp tiết kiệm thời gian và đơn giản hóa quy trình làm việc.
Trước khi đi sâu vào các ví dụ minh họa, hãy cùng tìm hiểu cách thức hoạt động của định dạng số tùy chỉnh.
Các định dạng số tùy chỉnh được nhập vào trường “Type” (Kiểu) trong phần “Custom” của hộp thoại “Format Cells”.
Trường Type trong mục Custom của hộp thoại Format Cells trong Excel, nơi nhập cú pháp định dạng số tùy chỉnh.
Mã bạn cần nhập vào đây tuân theo một thứ tự nghiêm ngặt:
DƯƠNG;ÂM;SỐ_KHÔNG;VĂN_BẢNNói cách khác, đối số đầu tiên trong trường định dạng số tùy chỉnh quy định cách định dạng số dương, đối số thứ hai quy định cách định dạng số âm, đối số thứ ba quy định cách định dạng số 0, và đối số thứ tư quy định cách định dạng văn bản. Lưu ý rằng dấu chấm phẩy (;) được sử dụng để phân tách mỗi đối số.
Ví dụ, khi bạn nhập:
#.00;(#.00);-;"TXT"vào trường “Type”, Excel sẽ hiển thị:
- Một số có hai chữ số thập phân cho số dương (
#.00). - Một số có hai chữ số thập phân đặt trong ngoặc đơn cho số âm (
(#.00)). - Một dấu gạch ngang cho số 0 (
-). - Và chuỗi ký tự “TXT” cho tất cả các giá trị văn bản (
"TXT").
Bảng tính Excel hiển thị các giá trị khác nhau đã được chuyển đổi sang định dạng số tùy chỉnh theo cú pháp `#.00;(#.00);-;"TXT"`.
Nếu bạn bỏ qua bất kỳ đối số nào bằng cách thêm dấu chấm phẩy nhưng không nhập bất kỳ mã nào, các ô liên quan trong bảng tính của bạn sẽ xuất hiện trống.
Ví dụ, khi nhập:
#.00;;;"TXT"vào trường “Type”, Excel sẽ hiển thị một số có hai chữ số thập phân cho số dương, nhưng lại hiển thị ô trống cho số âm và số 0, và hiển thị “TXT” cho tất cả các giá trị văn bản.
Bảng tính Excel với các giá trị được định dạng tùy chỉnh, một số ô hiển thị trống do bỏ qua đối số trong cú pháp.
Mặc dù các giá trị hiển thị trong mỗi ô phụ thuộc vào những gì bạn chỉ định trong trường “Type” của định dạng số tùy chỉnh, giá trị thực tế của các ô vẫn không thay đổi. Trong ví dụ trên, mặc dù ô C2 xuất hiện trống (vì chúng tôi đã bỏ qua đối số cho số âm trong trường “Type”), khi chọn ô đó, thanh công thức vẫn hiển thị giá trị thực của nó.
Ô có giá trị -1 trong Excel hiển thị trống do đã áp dụng định dạng số tùy chỉnh để ẩn giá trị âm.
Điều này có nghĩa là bạn vẫn có thể sử dụng ô này trong các công thức nếu cần.
Ngoài việc sử dụng định dạng số tùy chỉnh để thay đổi giá trị hiển thị trong ô, bạn cũng có thể thay đổi màu sắc của các giá trị và thêm các ký hiệu để trực quan hóa dữ liệu của mình. Hãy cùng tìm hiểu chi tiết hơn.
Thay đổi màu phông chữ bằng Định dạng số tùy chỉnh
Tính năng định dạng số tùy chỉnh của Microsoft Excel cho phép bạn xác định màu sắc của các giá trị trong các ô đã chọn. Hơn nữa, đối với tám màu chính (đen, trắng, đỏ, xanh lá cây, xanh dương, vàng, đỏ tươi và xanh lam), bạn không cần phải nhớ bất kỳ mã nào – bạn chỉ cần gõ tên màu. Màu sắc phải là đối số đầu tiên cho mỗi loại dữ liệu và chúng phải được đặt trong dấu ngoặc vuông.
Hãy xem ví dụ này, hiển thị liệu mười nhân viên có đạt được mục tiêu doanh số của họ hay không. Bạn muốn tất cả các số dương trong cột C có màu xanh lá cây, số âm màu đỏ và số 0 màu xanh dương.
Bảng tính Excel hiển thị dữ liệu so sánh doanh số của nhân viên với mục tiêu đã đặt ra.
Để làm điều này, hãy chọn ô C2, mở hộp thoại “Format Cells” (Ctrl + 1) và chuyển đến phần “Custom”.
Trong trường “Type”, nhập:
[Green]#;[Red]#;[Blue]0;Điều này báo cho Excel giữ nguyên các số dương và âm (#) – nhưng định dạng màu xanh lá và đỏ tương ứng – và số 0 dưới dạng “0” – nhưng định dạng màu xanh dương. Vì không có văn bản trong phạm vi này, bạn không cần chỉ định định dạng số cho các giá trị văn bản, nên chỉ cần ba đối số đầu tiên.
Khi bạn nhấp vào “OK”, bạn sẽ thấy văn bản thay đổi màu sắc tương ứng. Sau đó, bạn có thể kéo và thả tay cầm điền (fill handle) xuống hàng dữ liệu cuối cùng để áp dụng định dạng số tùy chỉnh này cho các giá trị còn lại. Thao tác này sẽ không làm thay đổi giá trị trong cột C, vì chúng đã được tính toán bằng công thức.
Bảng tính Excel hiển thị kết quả doanh số của nhân viên với các số được tô màu xanh lá, đỏ hoặc xanh dương tùy thuộc vào giá trị bằng định dạng số tùy chỉnh.
Nếu bạn muốn sử dụng các màu khác ngoài tám màu tiêu chuẩn có sẵn cho phông chữ, thay vì gõ tên màu trong dấu ngoặc vuông, hãy gõ mã màu tương ứng:
Bảng mã màu có thể sử dụng trong định dạng số tùy chỉnh của Microsoft Excel.
Ví dụ, khi nhập:
[Color10]#;[Color30]#;[Color16]0;sẽ sử dụng phông chữ màu xanh đậm cho số dương, màu đỏ thẫm cho số âm và màu xám cho số 0.
Để hoàn nguyên định dạng số về trạng thái ban đầu, hãy chọn các ô chứa dữ liệu đã định dạng và trong nhóm “Number” trên tab “Home” của dải băng, chọn “General” (Chung) trong danh sách thả xuống.
Tại thời điểm này, hãy dành một chút để đánh giá cao việc đạt được cùng một kết quả bằng định dạng có điều kiện sẽ yêu cầu bạn tạo ba quy tắc riêng biệt. Ngược lại, bằng cách sử dụng định dạng tùy chỉnh, bạn có thể thực hiện những thay đổi này chỉ bằng cách thêm vài ký tự vào trường “Type”.
Hiển thị ký hiệu bằng Định dạng số tùy chỉnh
Tương tự như khi sử dụng định dạng có điều kiện, bạn có thể hiển thị các ký hiệu dựa trên các giá trị tương ứng thông qua định dạng số tùy chỉnh.
Sử dụng ví dụ tương tự như trên, giả sử bạn muốn chuyển đổi các số trong cột “Result” (cột C) thành mũi tên lên cho số dương, mũi tên xuống cho số âm và dấu bằng (=) cho số 0.
Để làm điều này, trước tiên bạn cần tìm các ký hiệu hoặc biết các phím tắt của chúng.
Trong tab “Insert” (Chèn), nhấp vào “Symbol” (Ký hiệu) và chèn các ký hiệu vào một vùng khác của bảng tính để bạn có thể sao chép và dán chúng vào mã tùy chỉnh của mình.
Hộp thoại Symbol trong Microsoft Excel để chèn các ký hiệu đặc biệt vào bảng tính.
Ngoài ra, bạn có thể sử dụng phím tắt Windows Windows + Dấu chấm (.) để mở bàn phím biểu tượng cảm xúc (emoji keyboard) và chèn trực tiếp các ký hiệu này vào trường “Type”.
Chúng tôi biết rằng phím tắt Windows cho ▲ là Alt + 30 và ▼ là Alt + 31, và bạn có thể sử dụng các phím tắt này khi nhập vào trường “Type”.
Vì vậy, hãy chọn ô C2 và trong phần “Custom” của hộp thoại “Format Cells” (Ctrl + 1 > Tab > Cus), nhập dòng mã rất ngắn gọn này:
▲;▼;"=";Điều này báo cho Excel thay thế số dương bằng “▲”, số âm bằng “▼” và số 0 bằng “=”.
Lưu ý: Bất kỳ chuỗi văn bản hoặc ký hiệu được gõ (bao gồm các ký hiệu toán học hoặc bất kỳ ký hiệu bàn phím nào) đều phải được đặt trong dấu ngoặc kép.
Bây giờ, nhấn Enter và sử dụng tay cầm điền để áp dụng định dạng số tùy chỉnh cho các ô còn lại trong cột.
Bảng tính Excel hiển thị kết quả doanh số của nhân viên được biểu thị bằng mũi tên lên, mũi tên xuống hoặc dấu bằng tùy theo giá trị.
Tiếp theo, bạn có thể muốn tô màu các mũi tên để trực quan hóa dữ liệu rõ ràng hơn. Đây là lúc bạn kết hợp mã màu với các ký hiệu mũi tên, nhớ rằng mã màu phải luôn được đặt ở đầu đối số.
Để làm điều này, nhập:
[Green]▲;[Red]▼;"=";vào trường “Type”. Đây là những gì bạn sẽ thấy:
Bảng tính Excel hiển thị kết quả doanh số của nhân viên với ký hiệu mũi tên xanh lá (lên), mũi tên đỏ (xuống) hoặc dấu bằng, có màu sắc theo giá trị.
Kết hợp màu sắc, số và ký hiệu trong Định dạng số tùy chỉnh
Bây giờ, đã đến lúc kết hợp tất cả các bước trên để tạo ra một kết quả hiển thị cả ký hiệu có màu sắc và số cùng một lúc.
Trong trường “Type” cho ô C2, nhập:
[Green]#▲;[Red]#▼;0" =";Trong đó:
- Đối số đầu tiên (
[Green]#▲) làm cho các giá trị dương có màu xanh lá cây, hiển thị cả số và một mũi tên lên. - Đối số thứ hai (
[Red]#▼) làm cho các giá trị âm có màu đỏ, hiển thị cả số và một mũi tên xuống. - Và đối số thứ ba (
0" =") không có định dạng màu (nên sẽ sử dụng định dạng ô thủ công), và hiển thị số “0” theo sau là một khoảng trắng và dấu “=”.
Bảng tính Excel hiển thị kết quả doanh số của nhân viên được định dạng toàn diện bằng định dạng số tùy chỉnh, bao gồm số, màu và ký hiệu.
Để căn chỉnh số sang trái của ô và ký hiệu sang phải của ô, hãy nhập dấu hoa thị (*) và một khoảng trắng giữa ký hiệu và số trong mỗi đối số (ví dụ: [Green]#* ▲;[Red]#* ▼;0* " ="). Dấu hoa thị báo cho Excel lặp lại ký tự theo sau vô hạn (cho đến khi đạt đến ranh giới ô), và vì ký tự theo sau là một khoảng trắng, khoảng cách giữa số và ký hiệu sẽ tăng và giảm theo chiều rộng của ô.
Trong một ví dụ cuối cùng, giả sử bạn đã tính toán phần trăm thay đổi tổng điểm cho các đội thể thao khác nhau và bạn muốn nhanh chóng định dạng các số dương và âm sao cho chúng chuyển sang màu xanh lá cây hoặc đỏ, có mũi tên lên hoặc xuống, bao gồm số, và được coi là phần trăm. Bạn cũng muốn số 0 hiển thị dưới dạng dấu gạch ngang.
Danh sách các đội thể thao trong Excel với tổng điểm qua hai mùa giải và cột phần trăm thay đổi, trước khi áp dụng định dạng tùy chỉnh.
Để làm điều này, trong trường “Type” cho các ô trong cột C, nhập:
[Green]▲#%;[Red]▼#%;"-"Đây là những gì bạn sẽ nhận được cho kết quả:
Danh sách đội thể thao trong Excel hiển thị tổng điểm hai mùa và phần trăm thay đổi, đã được định dạng bằng định dạng số tùy chỉnh.
Sau khi bạn đã thực hành sử dụng định dạng số tùy chỉnh để thêm màu sắc và ký hiệu vào các ô của mình, bạn sẽ nhận ra quá trình này nhanh chóng và dễ dàng đến mức nào – và hy vọng, bạn sẽ sử dụng định dạng số tùy chỉnh thường xuyên hơn trong tương lai!
Đừng bỏ lỡ: Khám phá thêm các thủ thuật Excel chuyên sâu khác để nâng cao kỹ năng làm việc với bảng tính của bạn!
