ในหลักการบันทึกข้อมูลที่ดีต้องควบคุมการบันทึกค่าซ้ำให้เกิดขึ้นได้เท่าที่จำเป็นเท่านั้น เช่นในงานบันทึกข้อมูลสินค้าที่ขาย จะมีตารางบันทึกรายละเอียดของสินค้าเอาไว้ จากนั้นเมื่อต้องการรายละเอียดของสินค้าไปใช้ที่เซลล์อื่น ไม่จำเป็นต้องพิมพ์รายละเอียดของสินค้านั้นๆซ้ำอีก ค่าที่จำเป็นต้องพิมพ์ซ้ำมีเพียงรหัส พอพิมพ์รหัสลงไปแล้วให้ใช้สูตรดึงข้อมูลของรหัสสินค้านั้นมาแสดง โดยใช้สูตร VLookup, Match, Index, หรือสูตรอื่นๆดึงรายละเอียดของรหัสมาแสดง
อย่างไรก็ตามมีบางงานที่ผู้ใช้ Excel อาจเผลอพิมพ์ค่าซ้ำ โดยเฉพาะงานที่ต้องกระจายให้พนักงานหลายๆคนช่วยกันบันทึกข้อมูล เครื่องมือที่ใช้ช่วยเตือนทันทีที่มีการบันทึกค่าซ้ำได้แก่ คำสั่ง Data Validation เพื่อป้องกันหรือเตือนการบันทึกซ้ำและ Conditional Formatting เพื่อเปลี่ยนสีเซลล์ที่มีค่าซ้ำ โดยใช้งานร่วมกับสูตร CountIF
ไม่ควรรีบใช้คำสั่ง Remove Duplicates จนกว่าจะตรวจสอบพบตำแหน่งรายการซ้ำเพื่อทราบลักษณะของการซ้ำและสาเหตุที่ทำให้เกิดรายการซ้ำแล้วเท่านั้น
หากต้องการสั่งให้ Excel เตือนเมื่อมีการพิมพ์ค่าซ้ำ ให้เริ่มจากเลือกพื้นที่ตารางตั้งแต่ B3:B15 ซึ่งเตรียมไว้สำหรับบันทึกข้อมูล จากนั้นสั่ง Data > Data Validation > Data Validation เลือก Allow แบบ Custom แล้วพิมพ์สูตร =COUNTIF($B$3:$B$15,B3)=1 ลงไปในช่อง Formula โดยไม่ต้องใส่เครื่องหมาย $ ในส่วนของ B3 ที่อยู่ด้านท้ายในวงเล็บของสูตร เพื่อทำให้ Excel ใช้ตำแหน่งถัดไปในการตรวจสอบให้เอง
ถ้าต้องการเปลี่ยนสีเซลล์ที่มีการบันทึกค่าซ้ำ ให้เริ่มจากเลือกพื้นที่ตารางตั้งแต่ B3:B15 แล้วสั่ง Home > Conditional Formatting > New Rule โดยเลือกใช้เงื่อนไขแบบ Use a formula to determine which cells to format แล้วใช้สูตร =COUNTIF($B$3:$B$15,B3)>1 ซึ่งถ้าสูตรนี้คืนค่าเป็น True ย่อมแสดงว่าสูตร CountIF นับข้อมูลแล้วพบว่ามีค่าซ้ำจริง
โปรดสังเกตว่าถ้าใช้ Data Validation จะใช้สูตร =COUNTIF($B$3:$B$15,B3)=1 แต่เมื่อใช้กับ Conditional Formatting ต้องเปลี่ยนสมการจากเท่ากับ 1 เป็นมากกว่า 1 แทน ด้วยสูตร =COUNTIF($B$3:$B$15,B3)>1 เพราะ Data Validation จะยอมให้บันทึกค่าต่อเมื่อนับแล้วมีเพียงค่าเดียวคือนับแล้วเท่ากับ 1 ค่าเท่านั้น ส่วนการใช้ Conditional Formatting จะเปลี่ยนสีเพื่อเตือนเมื่อมีค่าซ้ำคือเมื่อนับแล้วที่ค่ามากกว่า 1
Data Validation เป็นเครื่องมือที่ควรนำมาใช้ร่วมกับ Conditional Formatting เสมอ เนื่องจาก Data Validation จะช่วยตรวจสอบค่าซ้ำให้ต่อเมื่อเกิดค่าซ้ำขึ้นจากการพิมพ์ลงไปในตารางเท่านั้น แต่จะไม่ตรวจสอบหากมีการ copy ค่าซ้ำวางลงไปแต่อย่างใด ดังนั้นเมื่อเกิดค่าซ้ำขึ้นเมื่อใดก็จะเห็นสีในเซลล์เปลี่ยนแปลงต่างไปจากเดิมจากคำสั่ง Conditional Formatting เป็นปราการด่านสุดท้ายของการตรวจสอบอีกชั้นหนึ่ง
ในภาพต่อไปหากต้องการเปลี่ยนสีเซลล์เพื่อช่วยแสดงตำแหน่งข้อมูลรายการที่ต้องการให้เห็นชัดเจนขึ้น
ตารางด้านซ้ายมือเป็นตารางฐานข้อมูล ให้เลือกพื้นที่ตารางเฉพาะส่วนของรายการทั้งหมดตั้งแต่ B3:E20 แล้วสั่ง Home > Conditional Formatting > New Rule โดยเลือกใช้เงื่อนไขแบบ Use a formula to determine which cells to format แล้วใช้สูตร =$C3=$G$3
เซลล์ G3 เป็นเซลล์ที่ใช้กรอกรหัสที่ต้องการใช้สีบอกตำแหน่ง ดังนั้นในสูตร =$C3=$G$3 ที่ใช้นี้จึงกำหนดตำแหน่งแบบคงที่ $G$3 เอาไว้ ส่วนเซลล์ C3 เป็นเซลล์แรกของรายการหัส ID ซึ่งในสูตรนี้กำหนดให้เลื่อนตำแหน่งไปเรื่อยๆใน column C จึงต้องใช้ $C3
