Cara Membuat Nomor Otomatis di Excel (Isi Otomatis dan Fungsi SEQUENCE)

Cara membuat nomor otomatis di Excel yang paling sederhana adalah mengetik 1 dan 2 di dua sel pertama, memilih keduanya, lalu menyeret kotak kecil di pojok kanan bawah pilihan ke bawah. Excel meneruskan polanya menjadi 3, 4, 5, dan seterusnya. Untuk nomor yang ikut menyesuaikan saat data berubah, kamu bisa memakai rumus ROW, dan di Excel versi baru cukup satu rumus SEQUENCE untuk seluruh daftar.

Perlu kamu tahu dulu, berbeda dengan program Microsoft 365 lainnya, Excel memang tidak punya tombol khusus untuk memberi nomor data secara otomatis. Halaman bantuan Microsoft sendiri menyebut hal ini, lalu menawarkan dua jalan: menyeret gagang isian atau memakai fungsi ROW. Panduan ini membahas kedua jalan itu, ditambah beberapa cara lain untuk kasus yang lebih rumit, seperti data yang sering difilter atau baris kosong di tengah tabel. Semua nama menu mengikuti halaman bantuan resmi Microsoft di bagian Sumber.

Pilih cara membuat nomor urut yang cocok

Tidak ada satu cara membuat nomor otomatis di Excel yang paling benar untuk semua situasi. Daftar absen 30 siswa yang jarang berubah cukup dinomori dengan seret biasa. Sebaliknya, daftar stok barang yang tiap hari ditambah, dihapus, dan difilter akan lebih awet kalau nomornya berasal dari rumus. Tabel berikut membantu kamu memilih sebelum mulai.

Saran kami, mulailah dari gagang isian. Cara ini bekerja di semua versi Excel dan paling mudah diingat. Begitu kamu mulai sering mengurutkan, menghapus, atau memfilter data, barulah pindah ke cara berbasis rumus. Langkah ringkas gagang isian per perangkat ada di bawah ini.

Cara membuat nomor otomatis di Excel dengan gagang isian

Gagang isian adalah kotak kecil di pojok kanan bawah sel atau rentang yang sedang kamu pilih. Menurut halaman bantuan Microsoft, gagang ini aktif secara default dan dipakai untuk menyalin data atau mengisi sel yang berdekatan dengan serangkaian data. Kuncinya ada di dua angka pertama, karena dari situlah Excel membaca polanya.

Halaman bantuan Microsoft memberi contoh yang jelas: untuk rangkaian 1, 2, 3, 4, 5, ketik 1 dan 2 di dua sel pertama. Untuk 2, 4, 6, 8, ketik 2 dan 4. Kalau kamu hanya mengetik 2 di sel pertama lalu menyeretnya, hasilnya justru 2, 2, 2, 2. Inilah penyebab paling umum keluhan "kok angkanya sama semua".

Ada beberapa perilaku gagang isian yang berguna untuk penomoran:

• Arah seret menentukan urutan. Seret ke bawah atau ke kanan untuk urutan menaik, ke atas atau ke kiri untuk urutan menurun.
• Pratinjau muncul saat menyeret. Excel menampilkan nilai yang akan terisi di setiap sel yang kamu lewati, jadi kamu tahu kapan harus berhenti.
• Klik kanan untuk pola lain. Seret gagang isian sambil menahan tombol kanan mouse, lalu pilih pola dari menu yang muncul.
• Tombol Opsi Isi Otomatis. Setelah selesai menyeret, tombol kecil Opsi Isi Otomatis muncul untuk memilih cara pengisian. Di halaman bantuan Excel untuk Mac, pilihannya antara lain Salin Sel dan Isi Tanpa Pemformatan, yang berguna bila warna atau garis sel pertama tidak ingin ikut tersalin.

Pola tidak harus kelipatan satu. Halaman Microsoft tentang seri linear mencontohkan pilihan awal 1 dan 3 yang diteruskan menjadi 5, 7, 9, serta 100 dan 95 yang diteruskan menjadi 90, 85. Jadi, untuk nomor ganjil atau hitung mundur, cukup ubah dua angka awalnya.

Gagang isian tidak muncul? Aktifkan dulu

Kalau kotak kecil itu tidak terlihat, kemungkinan fiturnya pernah dimatikan. Di Excel untuk Windows, buka File > Opsi, pilih kategori Tingkat Lanjut, lalu di bawah Opsi pengeditan centang Aktifkan gagang isian dan seret dan letakkan sel.

Di tempat yang sama ada kotak centang Peringatan sebelum menimpa sel. Biarkan tetap tercentang. Kotak itu membuat Excel memperingatkan kamu sebelum gagang isian menimpa data yang sudah ada, dan peringatan ini sering menyelamatkan kolom yang tidak sengaja terseret terlalu jauh.

Satu kelemahan penting: nomor hasil gagang isian adalah angka biasa. Microsoft menegaskan angka ini tidak diperbarui otomatis saat kamu menambah, memindahkan, atau menghapus baris. Kalau urutannya rusak, pilih dua angka yang masih benar, lalu seret gagang isian lagi sampai akhir daftar.

Membuat ribuan nomor sekaligus dengan perintah Seri

Menyeret gagang isian sampai baris ke-2.000 itu melelahkan, apalagi dengan touchpad laptop. Untuk daftar panjang, Excel untuk Windows punya perintah Seri yang bisa berhenti tepat di angka yang kamu tentukan. Langkahnya menurut halaman bantuan Microsoft:

1. Ketik angka pertama, misalnya 1, lalu pilih sel itu. Sel awal harus berisi nilai pertama seri.
2. Pada tab Beranda, di grup Pengeditan, klik Isian, lalu klik Seri.
3. Pilih Kolom untuk mengisi ke bawah, atau Baris untuk mengisi ke samping.
4. Di kotak Nilai langkah, isi selisih antarnomor, misalnya 1.
5. Di bawah Jenis, pilih Linear.
6. Di kotak Nilai hentikan, isi nomor terakhir, misalnya 2000. Seri akan berhenti di angka itu.

Pada seri linear, nilai langkah ditambahkan ke nilai awal, lalu ke setiap nilai berikutnya. Jenis Pertumbuhan bekerja dengan cara mengalikan, jadi jangan dipilih untuk nomor urut. Di beberapa halaman bantuan, tombol Isian ini juga ditulis Isi; keduanya merujuk ke tombol yang sama di grup Pengeditan.

Menurut kami, perintah Seri adalah cara yang paling sering terlupakan, padahal hasilnya paling rapi untuk daftar panjang. Kamu tidak perlu menebak di mana harus berhenti menyeret, dan jumlah barisnya pasti pas.

Nomor otomatis dengan rumus ROW

Fungsi ROW mengembalikan nomor baris dari sel yang dirujuknya. Misalnya, =ROW(A1) menghasilkan 1 dan =ROW(C10) menghasilkan 10. Sifat inilah yang dipakai untuk penomoran:

1. Di sel pertama kolom nomor, ketik =ROW(A1).
2. Seret gagang isian sampai akhir daftar. Rujukan A1 bergeser menjadi A2, A3, dan seterusnya, sehingga hasilnya 1, 2, 3.

Kelebihannya, nomor dari rumus ROW ikut diperbarui saat kamu mengurutkan data. Tapi Microsoft juga mengingatkan urutannya bisa terganggu kalau kamu menambah, memindahkan, atau menghapus baris. Baris baru yang disisipkan di tengah tidak otomatis berisi rumus, jadi tetap perlu diisi lagi dengan gagang isian.

Varian ROW() tanpa rujukan

Kalau ROW() ditulis tanpa isi di dalam kurung, hasilnya adalah nomor baris tempat rumus itu berada. Kalau judul kolom ada di baris 1 dan data dimulai di baris 2, rumus =ROW()-1 di baris 2 menghasilkan 1, di baris 3 menghasilkan 2, dan seterusnya. Ubah angka pengurangnya sesuai jumlah baris di atas data kamu.

Nomor berformat kode, misalnya 000-001

Untuk nomor dokumen seperti nomor pesanan, Microsoft mencontohkan gabungan ROW dan TEXT. Ketik =TEXT(ROW(A1),"000-000") di sel pertama, lalu seret gagang isian. Hasilnya 000-001, 000-002, 000-003. Pola di dalam tanda kutip bisa kamu sesuaikan, misalnya "000" untuk 001, 002, 003.

Fungsi SEQUENCE: satu rumus untuk seluruh daftar

SEQUENCE membuat daftar angka berurutan dalam satu rumus, tanpa diseret. Sintaksnya =SEQUENCE(baris,[kolom],[mulai],[langkah]). Hanya argumen baris yang wajib. Argumen lain yang dikosongkan bernilai 1, jadi =SEQUENCE(10) menghasilkan angka 1 sampai 10 ke bawah.

Beberapa contoh yang berguna:

Hasil SEQUENCE "meluap" (spill) ke sel di bawahnya. Kamu cukup mengetik rumus di satu sel, menekan Enter, dan Excel mengisi sendiri sel-sel berikutnya. Untuk mempelajari argumennya lebih jauh, buka penjelasan lengkap rumus SEQUENCE kami.

Ada tiga hal yang perlu kamu perhatikan:

• Versi. Halaman bantuan SEQUENCE berlaku untuk Excel untuk Microsoft 365, Excel 2024, dan Excel 2021 (Windows dan Mac), serta Excel untuk iPad, iPhone, dan Android. Excel 2019 dan 2016 tidak tercantum.
• Sel di bawahnya harus kosong. Kalau rentang luapan terhalang isi lain, muncul kesalahan #SPILL!. Pilih sel tersebut, klik peringatan yang muncul, lalu pilih Pilih Sel Penghalang untuk menemukan sel yang menghalangi.
• Tidak bisa di dalam tabel Excel. Microsoft menyebut rumus larik yang meluap tidak didukung di tabel Excel. Taruh SEQUENCE di rentang biasa, bukan di kolom tabel.

Supaya jumlah nomor mengikuti jumlah data, SEQUENCE bisa digabung dengan COUNTA, fungsi yang menghitung sel tidak kosong. Contohnya =SEQUENCE(COUNTA(B2:B500)) di sel A2. Tambahkan nama di kolom B, dan daftar nomornya ikut memanjang. Catatan kecil yang jarang dibahas: COUNTA juga menghitung sel berisi rumus yang menghasilkan teks kosong (""), jadi pastikan kolom B memang hanya berisi data ketikan.

Nomor yang ikut bertambah saat data baru diketik

Kalau daftar kamu terus bertambah di bagian bawah, misalnya buku kas atau daftar pendaftar, cara paling awet adalah mengubah rentang data menjadi tabel Excel. Microsoft menyebut, bila kamu memakai fungsi ROW dan rentangnya diubah menjadi tabel, semua baris yang ditambahkan di akhir tabel dinomori secara berurutan.

Cara membuat tabelnya di Excel untuk Windows:

1. Klik salah satu sel di dalam data kamu.
2. Pilih Beranda > Format sebagai Tabel, lalu pilih gaya tabel.
3. Di kotak dialog Buat Tabel, periksa rentang selnya dan centang bila tabel punya judul kolom (header).
4. Klik OK.

Setelah itu, ketik rumus nomor di sel pertama kolom nomor, lalu tekan Enter. Tabel Excel punya fitur kolom terhitung: rumus yang kamu ketik di satu sel otomatis diisikan ke seluruh kolom, tanpa perintah Isi atau Salin. Tambahkan satu baris di bawah tabel, dan nomornya langsung muncul.

Jujur saja, banyak orang menghindari tabel Excel karena tampilannya berwarna. Padahal warnanya bisa diganti ke gaya polos, sementara manfaatnya (rumus dan nomor yang ikut memanjang) tetap ada. Kalau kamu juga sering membuat tabel di dokumen, panduan membuat tabel di Word membahas versi dokumennya.

Nomor hanya untuk baris yang terisi

Kadang daftar punya baris kosong sebagai pemisah, dan kamu tidak ingin baris kosong itu ikut bernomor. Gabungkan IF dan COUNTA:

=IF(B2="","",COUNTA($B$2:B2))

Artinya, kalau sel B2 kosong, tampilkan kosong. Kalau terisi, hitung berapa sel terisi dari B2 sampai baris ini. Tanda dolar pada $B$2 mengunci awal rentang, sedangkan B2 di belakangnya bergeser saat rumus diseret ke bawah. Teknik mengunci sel ini dibahas lebih lengkap di panduan rumus absolut Excel.

Hasilnya, baris berisi nama mendapat nomor 1, 2, 3 tanpa loncat, dan baris kosong dibiarkan polos. Cara ini cocok untuk daftar hadir yang dikelompokkan per kelas atau per divisi.

Nomor tetap urut setelah data difilter

Masalah klasik daftar panjang: setelah difilter, nomornya jadi 3, 8, 15, 21, karena baris lain hanya disembunyikan. Fungsi SUBTOTAL bisa mengatasinya. Ketik rumus ini di baris pertama data, lalu seret ke bawah:

=SUBTOTAL(103,$B$2:B2)

Kode 103 berarti COUNTA yang mengabaikan baris tersembunyi. Menurut halaman bantuan Microsoft, SUBTOTAL juga mengabaikan semua baris yang tidak masuk hasil filter, apa pun kodenya. Jadi setelah difilter, baris yang terlihat tetap bernomor 1, 2, 3. SUBTOTAL dirancang untuk kolom data (rentang vertikal), dan memang begitulah kolom nomor biasa disusun.

Cara membuat angka otomatis di Excel untuk Mac

Di Excel untuk Mac, prinsipnya sama. Ketik dua angka awal, pilih keduanya, lalu letakkan kursor di pojok kanan bawah sampai berubah menjadi tanda plus (+), dan seret ke bawah. Tombol Opsi Isi Otomatis juga muncul setelah kamu selesai menyeret.

Perbedaannya ada di letak pengaturan. Kalau gagang isian tidak muncul, buka menu Excel > Preferensi, klik Edit, lalu centang Izinkan gagang isian dan seret dan jatuhkan sel di bawah Opsi Edit. Untuk menyalin rumus nomor ke bawah tanpa menyeret, pilih sel berisi rumus beserta sel-sel di bawahnya, lalu tekan Ctrl+D. Pintasan yang sama berlaku di Excel untuk Windows, dan pintasan Excel lainnya bisa kamu lihat di daftar shortcut Excel.

Cara membuat nomor otomatis di Excel lewat HP

Di aplikasi Excel untuk Android dan iPhone, perintah pengisian ada di menu Edit. Ketik nilai awal dan nilai berikutnya, ketuk sel pertama, lalu seret gagang pemilihan sampai mencakup angka kedua. Ketuk lagi untuk membuka menu Edit, ketuk Isian, lalu seret panah isian ke bawah.

Layar ponsel sempit, jadi menyeret ratusan baris di HP cukup merepotkan. Untuk daftar panjang, kami sarankan memakai SEQUENCE, yang juga tercantum berlaku di Excel untuk iPhone dan Android. Cukup ketik =SEQUENCE(100) di satu sel. Belum punya aplikasinya? Unduh Excel hanya dari toko resmi berikut.

Masalah yang sering muncul saat membuat nomor urut

Soal pemisah rumus, ini penting untuk pengguna di Indonesia. Microsoft menjelaskan pemisah daftar dalam rumus bisa berbeda menurut setelan lokal sistem operasi dan Excel, dan yang paling umum adalah koma serta titik koma. Semua contoh di artikel ini memakai koma. Kalau Excel kamu menolaknya, tulis =SUBTOTAL(103;$B$2:B2) dan seterusnya dengan titik koma. Kesalahan rumus lain yang sering muncul dibahas di panduan rumus Excel tidak berfungsi.

Sebagai pegangan cepat, ringkasan di bawah ini merangkum cara mana yang dipakai untuk situasi apa.

Setelah tahu cara membuat nomor otomatis di Excel, langkah berikutnya biasanya belajar rumus lain. Kalau kamu baru mulai, kumpulan rumus Excel kami bisa jadi titik awal, dan penjelasan fungsi COUNTA membantu memahami cara Excel menghitung sel yang terisi. Panduan lain seputar Excel, Word, dan aplikasi kantor ada di kategori Office dan Google Workspace.