Định dạng Custom format trong Excel

Custom Number Format là gì?

Custom number format là một tính năng định dạng cho phép bạn tạo ra những kiểu định dạng riêng.

Làm thế nào để áp dụng Custom number format

Để có thể sử dụng Custom format, bạn cần phải biết vị trí của nó đã. Đầu tiên, ấn phím tắt Ctrl + 1 để mở hộp thoại Format Cells.

Sau đó, trong mục Number, bạn sẽ thấy Custom trong danh sách Category bên trái:

Có rất nhiều thông tin mới lạ tại đây phải không? Đừng lo lắng, bởi chúng đều chỉ tới những phương thức cụ thể bạn sử dụng để định dạng dữ liệu trong Excel. Sau bài viết này, các bạn sẽ hiểu ý nghĩa của những kí tự trên và đồng thời có thể tự tạo ra một định dạng cho riêng mình.

Tại sao lại sử dụng Custom number format

Trước khi đi sâu vào chi tiết với các ví dụ, hãy giải thích tại sao lại sử dụng Custom number format đã:

Lợi ích lớn nhất ở đây đó là bạn có thể thay đổi cách hiển thị hình thức của dữ liệu nhưng không làm thay đổi nội dung dữ liệu đó.

Bây giờ, hãy bắt đầu tìm hiểu Custom number format.

Custom number format – tùy chỉnh định dạng cho dữ liệu của bạn

Cấu trúc format

Custom number format có cấu trúc cụ thể của nó, chia thành tối đa 4 vùng phân cách bởi dấu chấm phẩy. Cấu trúc này nhìn từ ngoài vào khá phức tạp, tuy nhiên bạn hoàn toàn có thể ghi nhớ như sau:

1. Vùng giá trị dương
2. Vùng giá trị âm
3. Vùng giá trị bằng 0
4. Vùng giá trị text

Không phải tất cả đều bắt buộc

Dù cho một number format có thể bao gồm tới 4 vùng, thì ta chỉ cần có tối thiểu 1 vùng. Theo mặc định, vùng đầu tiên sẽ áp dụng cho số dương, vùng thứ hai cho số âm, vùng thứ ba cho giá trị 0 và vùng thứ tư cho text.

Cấu trúc bị ảnh hưởng được biểu thị như sau:

Nhìn vào bảng trên ta có thể hiểu như sau:

Nếu ta chỉ định dạng 1 vùng, định dạng đó sẽ ảnh hưởng tất cả.

Nếu bạn tạo ra một format với hai vùng, vùng đầu tiên sẽ được sử dụng đồng thời cho số dương, số 0 và text, vùng thứ hai sử dụng cho số âm.

Trong trường hợp có 3 vùng, vùng đầu tiên thu lại chỉ sử dụng cho số dương và text.

Khi đủ 4 vùng, mỗi định dạng trong 1 vùng biểu thị cho một loại dữ liệu.

Để bỏ qua vùng, ta sử dụng dấu chấm phẩy mà không định dạng gì trong vùng đó cả, hoặc sử dụng General như sau:

Định dạng vùng 1; General; General; Định dạng vùng 4

Kí tự hiển thị trực tiếp

Trong Custom Format, có những kí tự được hiển thị trực tiếp, trong khi những kí tự khác cần phải qua xử lý. Dưới đây là danh sách những kí tự hiển thị trực tiếp:

Kí tự thoát

Kí tự thoát là những kí tự không được sử dụng trực tiếp trong custom number format. Chúng chỉ có thể được sử dụng khi được “thoát” bởi kí tự đi kèm. Ví dụ: dấu sao (*), dấu thăng (#), phần trăm (%) sẽ không hiển thị trực tiếp. Chỉ khi đi kèm dấu gạch chéo (\), bạn mới có thể sử dụng trong custom number format trực tiếp:

Placeholders (đại diện)

Một số ký tự có ý nghĩa nhất định trong custom number format, chẳng hạn:

Cụ thể:

Số 0 (0) được sử dụng để hiển thị những số 0 khi giá trị số có ít chữ số hơn định dạng.  Chẳng hạn như định dạng 00000.00000 sẽ hiển thị giá trị 19.58 thành 00019.58000.

Dấu thăng (#) chỉ hiển thị những chữ số cần thiết, còn lại bỏ qua. Nói cách khác, nếu ta định dạng #.# sẽ làm tròn 19.58 thành 19.6.

Dấu hỏi (?) sử dụng để dóng hàng. Cụ thể như ảnh sau:

Dấu chấm (.) đại diện cho phần ngăn cách thập phân trong giá trị số.

Dấu phẩy (,) đại diện cho ngăn cách phần nghìn.

Dấu sao (*) được sử dụng để lặp lại kí tự cho tới khi đạt đủ độ rộng cột.

Gạch dưới (_) sử dụng khi ta muốn thêm khoảng trắng trong định dạng.

Làm tròn tự động

Excel sẽ tự động làm tròn các giá trị mỗi khi định dạng. Tuy nhiên đây chỉ là làm tròn hình thức, còn giá trị thực vẫn không đổi.

Custom number format cho text

Để hiển thị đồng thời cả chữ và số, ta đặt chữ vào trong dấu ngoặc kép (“”). Bạn có thể sử dụng điều này để nối chuỗi vào trong custom number format mà không ảnh hưởng tới kiểu giá trị dữ liệu. Ví dụ: định dạng General “nghìn” sẽ biến giá trị 10 thành 10 nghìn.

Định dạng cho dữ liệu ngày tháng

Ngày tháng trong Excel thực ra là những con số, bởi vậy bạn có thể sử dụng custom number format để thay đổi cách hiển thị. Tham khảo một số định dạng dưới đây:

Định dạng dữ liệu thời gian

Thời gian trong Excel là một phần của ngày, chẳng hạn như 12 giờ trưa là 0.5, 6 giờ tối là 0.75. Bạn có thể sử dụng một số định dạng sau đây:

Lưu ý: định dạng số phút (m) không thể đứng độc lập vì nó xung đột với định dạng tháng (m).

Định dạng cho thời gian trôi qua

Thời gian trôi qua là một trường hợp đặc biệt của thời gian. Để biểu thị ta đặt dữ liệu trong dấu ngoặc vuông. Chẳng hạn, để biết được 3.25 ngày tương đương với bao nhiêu giờ, phút, giây ta làm như sau:

Định dạng màu sắc

Bên cạnh Conditional Formatting và tô màu, Excel cho phép ta sử dụng custom number format cho việc thêm màu vào dữ liệu. Để thêm màu, ta có thể sử dụng mã định dạng [name] với name là tên màu trong tiếng anh hoặc [ColorX] với X là số từ 1 đến 56.

Ví dụ, [red] và [color3] sẽ tô màu đỏ.

Custom number format thay thế Conditional Formatting

Custom number format cho phép tối đa 2 điều kiện được viết trong dấu ngoặc vuông. Ví dụ, để tô màu những giá trị nhỏ hơn 100, ta sử dụng: [red][<100]. Đối với trường hợp nhiều hơn 2 điều kiện, bạn cần chuyển sang conditional formatting.

Để tìm hiểu sâu hơn về Custom Format, bạn hãy đọc các bài viết dưới đây:

Custom Format và những áp dụng hữu ích

Cách định dạng số trong excel đơn giản

Định dạng số tiền với Custom Format

Định dạng dữ liệu sử dụng Custom Format