Hướng dẫn cách sử dụng định dạng tùy chỉnh cho dữ liệu kiểu số trong Excel

Excel là một công cụ phổ biến được sử dụng trong rất nhiều công việc, ngành nghề khác nhau: Kế toán, tài chính, nhân sự, xây dựng... Do đó chúng ta cần thể hiện rất nhiều dạng khác nhau cho dữ liệu số như: Đơn vị tiền tệ, kế toán, ngày, giờ, phần trăm, thập phân... Trong bài viết này chúng ta hãy cùng Gitiho tìm hiểu về cách tùy chỉnh định dạng cho dữ liệu kiểu số theo ý muốn trong Excel nhé. Bạn cũng có thể tải file bài tập mẫu trong bài viết để thực hành theo nội dung trong bài. Link tải ở phía cuối bài viết. Hãy bắt đầu ngay nào.

Định dạng có số 0 ở trước

Ví dụ như chúng ta cần tạo một mã bao gồm 5 số. Nhưng không phải lúc nào bạn cũng nhập đầy đủ 5 chữ số, thay vì việc phải nhập 00041 vừa tốn thời gian lại mỏi tay, chúng ta chỉ cần nhập số 41 là Excel sẽ tự động hiển thị thành 00041 rồi. 

Và Format Cells chính là công cụ cho phép chúng ta làm điều này. Bạn có thể dễ dàng tùy chỉnh nhiều kiểu dữ liệu, chẳng hạn như định dạng thứ trong Excel và nhiều kiểu khác. 

Để định dạng có số 0 ở trước, ta làm như sau:

• Bước 1. Nhập giá trị 41 vào ô A1.
• Bước 2. Chọn ô A1, nhấn chuột phải, sau đó chọn Format Cells.
• Bước 3. Nhấn chọn Custom.
• Bước 4. Gõ vào ô định dạng số Type 5 chữ số 0: 00000
• Bước 5. Nhấn nút OK.

 

Chú ý: Excel sẽ cho ta xem trước dạng hiển thị của số sau khi được định dạng lại tại phần Sample (ngay phía trên mục Type)

Kết quả thu được như hình dưới đây:

Việc thay đổi định dạng số chỉ làm thay đổi hình thức hiển thị của số mà vẫn giữ nguyên giá trị bản chất của nó (vẫn chỉ có 2 chữ số và giá trị là 41, không phải 5 chữ số). Bạn có thể kiểm tra lại bằng hàm LEN (hàm đếm số ký tự) như sau:

 

Định dạng chữ số phần thập phân

Ta có thể kiểm soát số lượng chữ số phần thập phân (sau dấu ngăn cách phần thập phân - thường được hiểu là số sau dấu phẩy):

• Số 0,0 hiển thị số thập phân có một chữ số.
• Số 0,00 hiển thị số thập phân có hai chữ số,...

Ví dụ: Bạn nhập vào ô A1 giá trị 839.1274 và muốn Excel chỉ hiển thị 2 chữ số phần thập phân mà thôi.

• Nhập vào ô A1 số 839.1274
• Tại ô A1 bạn mở cửa sổ Format Cells, chọn thẻ Custom và nhập vào mục Type là 0.00 (nếu máy tính của bạn sử dụng dấu phẩy để ngăn cách phần thập phân thì hãy nhập 0,00)

Khi đó kết quả thu được là:

 

Tại ô A1 chỉ hiển thị là 839.13, nhưng trên thanh công thức thì hiển thị đầy đủ là 839.1274

Thêm ký tự chữ  cái (văn bản) kèm theo con số

Thông thường khi bạn nhập bất kỳ ký tự chữ cái nào thì Excel sẽ coi nội dung bạn nhập là dữ liệu kiểu Text (văn bản) chứ không phải kiểu Number (số). Do đó để có thể hiển thị một dữ liệu kiểu số mà có kèm theo các chữ cái, bạn cần phải thiết lập trong Custome format của Excel.

Ví dụ như trong minh họa này ta thêm chữ "ft" vào sau số 839,1.

Cách thực hiện như sau:

• Bước 1. Nhập giá trị 839.1274 vào ô A1.
• Bước 2. Mở cửa sổ Format Cells, thẻ Custom, nhập vào mục Type là 0.0 "ft"

Chú ý:

 

• Chữ ft khi nhập phải đặt trong cặp dấu nháy kép, còn số 0.0 để thể hiện số có 1 chữ số phần thập phân
• Thao tác này giúp ta thay đổi hình thức hiển thị của số, tuy nhiên về bản chất nó vẫn là giá trị số. Và ta có thể sử dụng nó để tính toán một cách bình thường.

Định dạng số hàng nghìn, hàng triệu, hàng tỷ bằng chữ cái

Đối với những số quá lớn, bạn có thể:

• sử dụng dấu phẩy ( , ) để hiển thị hàng nghìn,
• sử dụng dấu ( ,, ) để hiển thị số hàng triệu.

Ví dụ:

• Nhập giá trị số vào ô A1, B1, C1 và D1 lần lượt là: 1000000, 2500000, 81000000 và 700000.
• Chọn vùng A1:D1, mở cửa sổ Format Cells, ở thẻ Custom, ta nhập vào mục type là: 0.0,, "M"

Kết quả thu được như sau:

 

Bạn có thể thấy các con số hàng triệu đã chuyển về dạng số kèm theo chữ M thay vì hiển thị đủ 6 chữ số 0

Lặp lại ký tự text bất kỳ

Nếu bạn muốn Excel lặp lại một ký tự text nào đó một cách tự động, bạn có thể định dạng trong Custome format như sau:

Ví dụ bạn muốn Excel sẽ tự động thêm dấu - vào sau bất kỳ đoạn text nào được nhập

 

• Bước 1. Chọn vùng cần thiết lập, sau đó mở cửa sổ Format Cells
• Bước 2. Trong cửa sổ Format Cells, tại thẻ Custom, nhập vào ô Type là: @ *-

Ý nghĩa của ký hiệu này là:

• Ký hiệu @ đại diện cho việc bạn sẽ nhập vào ký tự text
• Ký hiệu * đại diện cho việc lặp lại ký tự
• Ký tự dấu - là ký tự sẽ được lặp lại

Lưu ý: Việc đặt dấu @ sẽ chỉ áp dụng định dạng này khi bạn nhập các dữ liệu là văn bản (text), còn không áp dụng cho những dữ liệu kiểu số (number)

Thay đổi màu sắc của các kiểu dữ liệu

Làm thế nào để Excel tự động nhận dạng kiểu dữ liệu và đổi màu theo ý muốn? Cụ thể:

• Nếu nhập dữ liệu văn bản (text) thì tự động đổi sang màu xanh da trời (Blue)
• Nếu nhập dữ liệu số dương (number >= 0) thì tự động đổi sang màu xanh lá cây (Green)
• Nếu nhập dữ liệu số âm (number < 0) thì tự động đổi sang màu đỏ (Red)
   

Cách làm như sau:

1. Chọn vùng ô cần định dạng
2. Mở cửa sổ Format Cells
3. Tại thẻ Custome, nhập vào mục Type là: [Green]$#,##0_);[Red]$(#,##0);"zero";[Blue]"Text:" @

Kết quả khi bạn nhập các ký tự sẽ hiển thị như sau:

 

Chú ý:

• #,## dùng để thêm dấu phẩy vào các số lớn hơn (tăng dần về phía bên trái)
• " _ " theo sau là một ký tự, dùng để thêm khoảng trắng. Chiều dài của khoảng trắng sẽ là chiều dài của ký tự này. Như trong ví dụ này của chúng ta, ta đã thêm dấu ngoặc đơn " ( ". Kết quả là số dương thẳng hàng đúng với số âm và số âm được đặt trong dấu ngoặc đơn.
• 2 dấu " ; " dùng để tách số dương và số âm.
• 3 dấu " ; " dùng để tách số dương, số âm và số 0. 

Thay đổi cách hiển thị ngày và giờ

Việc hiển thị ngày giờ trong Excel rất đa dạng. Bạn có thể có rất nhiều cách hiển thị khác nhau như:

• Hiển thị ngày trước, tháng sau
• Hiển thị tháng trước, ngày sau
• Hiển thị năm trước, tháng sau, rồi mới đến ngày
• Hiển thị cả thứ trong tuần kèm theo ngày tháng
• ...

Do đó để định dạng ngày tháng hoặc đổi định dạng ngày tháng sang số và chữ theo ý muốn, bạn có thể làm như sau:

• Chọn vùng ô cần định dạng
• Mở cửa sổ Format Cells
• Trong thẻ Number chọn mục Custom
• Nhập vào mục Type định dạng theo ý muốn, dựa trên nguyên tắc sau đây:

Ký tự d đại diện cho Ngày:

• d là ngày có 1 chữ số, nếu số ngày nhỏ hơn 10 thì chỉ hiển thị 1 chữ số (ví dụ 8)
• dd là ngày có 2 chữ số, nếu ngày nhỏ hơn 10 thì có hiển thị số 0 ở trước (ví dụ 08)
• ddd là thứ trong tuần dưới dạng viết tắt theo tên tiếng anh (ví dụ Mon)
• dddd là thứ trong tuần dưới dạng tên viết đủ theo tiếng anh (ví dụ Monday)

Ký tự M đại diện cho Tháng:

• M là tháng có 1 chữ số, nếu số tháng nhỏ hơn 10 thì chỉ hiển thị 1 chữ số (ví dụ 3)
• MM là tháng có 2 chữ số, nếu tháng nhỏ hơn 10 thì có hiển thị số 0 ở trước (ví dụ 03)
• MMM là tên tháng dưới dạng viết tắt theo tên tiếng anh (Ví dụ Jan)
• MMMM là tên tháng dưới dạng tên viết đủ theo tiếng anh (ví dụ January)

Ký tự Y đại diện cho Năm:

• y hoặc yy là hiển thị dạng viết tắt (chỉ hiện 2 số cuối của năm)
• yyy hoặc yyyy là hiển thị đầy đủ (hiển thị đủ 4 chữ số của năm)

Ví dụ chúng ta muốn định dạng cho ngày 30 tháng 4 năm 2017 (chủ nhật) dưới dạng 04/30/2017, Sunday thì định dạng như sau:

Mong rằng với bài viết này sẽ giúp cho các bạn biết thêm nhiều phương pháp định dạng dữ liệu và áp dụng tốt vào công việc. Cảm ơn các bạn đã chú ý theo dõi.

Xem thêm:

        Cách định dạng Ngày Tháng Năm trong Excel chuẩn không cần chỉnh
 

Hướng dẫn cách xử lý lỗi dữ liệu ngày tháng trong Excel

Cách thiết lập chỉ nhập dữ liệu dạng ngày tháng trong ô trên Excel

Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của Gitiho.com. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống: về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.