Anda disini: M Jurnal » Rumus Excel » 4 Cara Membuat Named Range / Define Name Pada Excel

4 Cara Membuat Named Range / Define Name Pada Excel

Sebelum Anda mempelajari Rumus / Fungsi serta mengolah data menggunakan Excel, Anda perlu mengetahui apa itu Named Range pada Microsoft Excel.

Kenapa begitu ? Karena Named Range merupakan fitur yang sangat berguna untuk membuat pekerjaan Anda pada Excel menjadi lebih mudah.

Mempermudah pekerjaan seperti apa ? Contohnya, Anda bisa membuat Rumus Terstruktur, pengganti table bernama bahkan juga sangat berguna ketika Anda mengolah data menggunakan Pivot Table.

Sebetulnya, ada 4 cara membuat Named Range (Range Bernama) yaitu menggunakan Name Box, Define Name, Create Form Selection dan Named Range pada Rumus.

Saya akan jelaskan satu per satu agar Anda bisa memilih cara yang terbaik menurut Anda.

Apa itu Named Range ?

Sebelumnya, Anda harus tahu lebih dahulu apa itu Named Range ? Jika diartikan kedalam Bahasa Indonesia Named Range berarti Rentang Bernama.

Rentang adalah sekelompok cell. Dengan kata lain, Named Range adalah sekelompok cell yang diberikan nama (identitas).

Jadi, jika Anda memilih (blok) range tertentu pada Excel, maka Anda bisa memberikan nama pada Range tersebut. Kegunaannya adalah untuk mengidentifikasi range tanpa perlu mengulang perintah sama yang cenderung lebih panjang.

Apa maksud mengidentifikasi range ? Contohnya, pernahkah Anda melihat Rumus Excel seperti berikut: =Table_Nilai[@MID]+Table_Nilai[@UAS] ? Ini merupakan Rumus Terstruktur jika Anda menggunakan Format Table Bernama.

Dari rumus tersebut, Anda bisa tahu bahwa Nilai MID pada Table Nilai di tambah dengan Nilai UAS pada Table Nilai. Ini lah yang dimaksud dengan rumus terstruktur dan mengidentifikasi range. Cukup membantu bukan ?

Contoh lainnya Fungsi SUM berikut: =SUM(Nilai). Fungsi SUM ini akan menjumlahkan semua data yang terdapat pada Range yang bernama “Nilai”.

Tentu Fungsi ini lebih mudah di identifikasi dari pada menggunakan Referensi biasa seperti berikut: =SUM(A1:C10).

Selain itu, Anda juga bisa menggunakan rumus / fungsi lainnya seperti Fungsi COUNTIF, Fungsi AVERAGE, dll

Aturan Penggunaan Named Range

Sama seperti menggunakan Fitur Table Bernama, Named Range juga memiliki aturan penggunaannya. Setidaknya ada 4 aturan yang harus Anda perhatikan sebagai berikut:

  1. Karakter Pertama harus berupa huruf A-Z atau underscore (_) atau backslash (\). Jadi Anda tidak bisa menggunakan karakter lain seperti angka 0-9 atau persentase (%) atau hastag (#) atau karakter lain yang tidak Saya sebutkan.
  2. Tidak bisa menggunakan spasi. Jadi jika Nama Range terdiri dari 2 kata, maka gunakan underscore sebagai pengganti spasi. Misalnya Nilai MID menjadi Nilai_MID
  3. Panjang Nama Range hanya bisa sebanyak 255 karakter.
  4. Nama Range tidak boleh sama persis dengan nama referensi cell seperti A1, atau B3 atau $A$1 atau R1C1 dan lain sebagainya. Namun jika Anda masih bisa menggunakan gabungan nama referensi cell misalnya gabungan nama cell A1 dan C3, maka Anda bisa ketik A1C1 atau A1_C1

Kira-kira udah terbayang belum ? Jika belum, coba pelajari contoh memberikan named range berikut untuk pemahaman lebih dalam:

Contoh Memberikan Named Range + Excel Online

Saat ini setidaknya ada 3 cara terbaik untuk memberikan nama pada range. Untuk memudahkan pemahaman Anda, silahkan download atau gunakan File Excel Online berikut sebagai bahan latihan.

Note:
1. Silahkan klik Icon Full Screen Pada Sudut Kanan Bawah File Excel Online berikut untuk menggunakan fitur Excel Online.
2. Klik Icon Download (gambar selembar kertas dengan tanda panah kebawah) pada baris sudut kanan bawah File Excel Online berikut untuk download file.
3. Jika Link Download atau Excel Online Error, silahkan beritahu Saya melalui kolom komentar (Terima kasih 🙂 ).

#1 Menggunakan Name Box

Silahkan buka Sheet Name Box pada Excel Online yang sudah Saya berikan di atas.

Dalam contoh ini, Saya memiliki data Nilai pada Range A2:C9. Kemudian silahkan ikuti cara berikut untuk memberikan nama pada Range tersebut:

Named Range dengan Name Box Excel
  1. Pertama, blok atau pilih range data A2:C9.
  2. Kedua, klik Name Box yang berada disebelah kiri Formula Bar.
  3. Ketiga, silahkan ubah menjadi nama range yang Anda ingin kan (dengan syarat sesuai aturan penggunaan Named Range). Misalnya Saya beri nama Nilai kemudian tekan Enter untuk menyimpannya. Jika sudah, maka Anda sudah berhasil membuat Nama Range.

Note: Named Range hanya akan muncul pada Name Box ketika Anda blok range tersebut (A2:C9). Jika Anda hanya klik pada Cell A2 atau B5 atau cell lain, maka nama Range tidak akan muncul pada Name Box.

Lalu bagaimana cara memanfaatkan Named Range ini ? Coba Anda ikuti tahap menggunakan Fungsi SUM untuk Named Range berikut:

Rumus Terstruktur Named Range Excel
  1. Pertama, klik Cell A11, kemudian ketik Fungsi SUM sebagai berikut: =SUM(
  2. Kedua, Ingat! nama Range A2:C9 adalah Nilai. Coba Anda ketik nama range tersebut secara perlahan. Mulai dari huruf N, kemudian i, kemudian l. Coba Anda perhatikan, Excel memberikan sugesti berupa Named Range Nilai (seperti gambar). Artinya, Excel sudah mengetahui nama range yang Anda buat sebelumya.
  3. Ketiga, Anda bisa lanjutkan ketik huruf a dan i atau double click pada sugesti Named Range dari Excel tersebut. Kemudian tekan Enter. Berikut hasilnya:
Rumus atau Fungsi Terstruktur Excel

Anda bisa lihat, Excel menjumlahkan semua data yang terdapat pada Range A2:C9 atau Range bernama Nilai. Sementara coba Anda lihat struktur Fungsi SUM tersebut: =SUM(Nilai) tentu jauh lebih mudah Anda pahami dari pada Fungsi SUM dengan referensi cell biasa seperti berikut: =SUM(A2:C9).

Note: Jika sebuah range sudah Anda beri nama, tolong jangan mengubahnya pada Name Box. Kenapa ? karena Anda akan membuat nama Range menjadi ganda (double).

Namun jika sudah terlanjur, silahkan hapus Nama Range yang tidak perlu menggunakan Manage Named Range di bawah.

Selanjutnya, Saya berikan contoh yang lebih luar biasa.

#2 Menggunakan Define Name

Silahkan buka Sheet Define Name pada Excel Online yang sudah Saya berikan sebelumnya. Dalam contoh Saya memiliki sebuah Tabel Transaksi pada Range E4:H13.

Saya akan memberikan Named Range pada Kolom Pembelian (range G5:G13) dan Kolom Penjualan (Range H5:H13). Kemudian melakukan perhitungan atar kedua Range tersebut. Perhatian tahap-tahap berikut:

Define Name Excel untuk Memberikan nama
  1. Pertama, blok atau pilih Range G5:G13.
  2. Kedua, klik Tab Formula, kemudian klik Define Name. Atau Anda juga bisa menggunakan Klik Kanan pada Range Data, kemudian klik Define Name.
  3. Ketiga, Muncul Window New Name, silahkan isi Nama Range menjadi Pembelian.
  4. Keempat, Anda bisa mengisi kolom Comment jika perlu. Comment sangat berguna untuk Anda memberikan keterangan lebih lanjut tentang Named Range ini. Tujuanya bisa juga untuk memberikan keterangan agar user lain yang menggunakan data ini bisa mengerti apa tujuan Nama Range tersebut.
  5. Kelima, Klik Ok untuk melanjutkan.
  6. Keenam, silahkan blok atau pilih Range H5:H13. Kemudian ulangi tahap 2 sampai 5. Anda hanya perlu mengubah nama range menjadi Penjualan.

Jika sudah selesai, Saya akan tunjukan bagaimana Rumus Terstruktur menggunakan kedua Named Range tersebut (Pembelian dan Penjualan). Saya akan menghitung total profit dengan rumus Total Penjualan – Total Pembelian. Perhatikan langkah-langkah berikut:

Rumus Terstruktur menggunakan 2 Define name excel
  1. Pertama, Ketik Rumus SUM pada Cell K6 seperti berikut: =SUM(
  2. Kedua, Ketik Blok Range G5:G13 atau Anda juga bisa ketik nama range Penjualan
  3. Ketiga, ketik tanda tutup kurung ) dan ketik tanda kurang sehingga rumus menjadi =SUM(Penjualan)-
  4. Terakhir, ketik SUM(Pembelian) kemudian tekan enter. Berikut hasilnya:
Rumus Terstruktur menggunakan define name excel

Excel memberikan hasil Rp 35,510 yaitu hasil pengurangan dari Total Penjualan dan Total Pembelian.

Note: Saya membuat Total Pembelian dan Total Penjualan pada Baris 14 (berwarna merah). Kemudian mengurangi Total Penjualan dengan Total Pembelian pada Cell I14 sebagai perbandingan.

Hasilnya sudah pasti sama (menggunakan rumus terstruktur named range vs referensi cell manual).

#3 Menggunakan Create Form Selection Untuk Named Range Otomatis

Note: Create Form Selection lebih bagus dari pada Define Name jika Anda ingin membuat banyak Named Range Secara otomatis.

Seperti apa otomatis nya ? Silahkan buka Sheet Create Form Selection pada Excel Online yang sudah Saya berikan sebelumnya.

Lihat Saya memiliki Tabel Penjualan yang tersusun pada Range A5:E14. Terdapat 5 kolom yaitu Tanggal, ID Produk, Nama Produk, Harga Modal, dan Harga Jual.

Setiap kolom, akan Saya beri nama berdasarkan masing-masing header yaitu Tanggal, ID Produk, Nama Produk, Harga Modal, dan Harga Jual.

Misalnya, Range A6:A14 akan diberi Nama Range Tanggal. Sedangkan Range C6:C14 akan diberi Nama Range Nama_Produk. begitu juga dengan kolom lainnya akan diberi nama sesuai header masing-masing kolom.

Untuk contoh ini, Anda hanya perlu melakukan 1x tahap. Pasti lebih mudah dari pada define name sebelumnya. Silahkan ikuti tahap-tahap berikut:

Define Name menggunakan Create form selection untuk named range
  1. Pertama, blok atau pilih semua tabel (termasuk header) yaitu Range A5:E14. Ingat! jangan lupa headernya juga ikut di blok.
  2. Kedua, klik Tab Formulas, kemudian klik Create Form Selection.
  3. Ketiga, muncul Window Create Name Form Selection. Pastikan Anda hanya mencentang Top Row, dan hilangkan centang pada Left Column, Bottom Row, dan Right Column. Kemudian klik Ok untuk menyimpan.

Jika sudah, Anda berhasil membuat Named Renge berdasarkan Create Name Form Selection. Dengan cara ini, Excel akan menambahkan Named Range secara Otomatis sebagai berikut:

  • A6:A14 : Tanggal
  • B6: B14 : ID_Produk
  • C6:C14 : Nama_Produk
  • D6: D14 : Harga_Modal
  • E6:E14 : Harga_Jual

Lalu kenapa pada Window Create Name Form Selection, centang pada Left Column, Bottom Row, dan Right Column dihilangkan ?

Karena patokan nama range dalam contoh ini berada pada Top Row (beris paling atas) dari Range data yang Anda blok.

Jika patokan Named Range berada pada Left Column, maka Excel akan membuat named range secara horizontal (menyamping) dengan Left Column (kolom paling kiri) menjadi Nama Range. Sementara Range data dibuat secara horizontal pula.

Dengan kata lain, Range B5:E5 akan dibuat menjadi Named Range dengan nama Tanggal. Selanjutnya, Range B6:E6 akan dibuat menjadi Named Range dengan nama _01_10_2020 dst..

#4 Trik: Named Range Pada Rumus / Formula

Contoh ke 4 ini sering digunakan oleh para Expert Excel. Anda bisa menggunakan Named Range Khusus pada Cell yang berisi Rumus / Fungsi.

Sebagai contoh, Saya masih menggunakan Sheet yang sama yaitu Create Form Selection.

Saya membuat kolom baru yaitu Pajak 10% dan Profit seperti gambar berikut:

Kolom baru untuk define name excel

Jika pajak dihitung dari harga jual, maka untuk menghitung pajak pada Cell F6 Anda biasanya akan menggunakan rumus berikut: =E6*10%. Sementara untuk pajak pada Cell F7, Anda akan menggunakan rumus berikut =F6*10% dan begitu seterusnya.

Apa ada yang salah dengan rumus tersebut ? Tidak. Tidak ada yang salah dengan rumus tersebut. Hanya saja Rumus tersebut hanyalah sebuah cara lama yang terlalu sering digunakan.

Pada contoh ini, Saya akan tunjukkan kepada Anda cara terbaru yang bisa membuat Anda menjadi seorang yang Expert Excel. Caranya, pertama-tama, silahkan buat Total dari Harga Jual.

Anda bisa menggunakan Fungsi SUM berikut: =SUM(Harga_Jual) pada cell E15 seperti cara sebelumnya. Penampakannya kurang lebih seperti gambar berikut:

Rumus terstruktur menggunakan excel

Selanjutnya, berikan Define Name untuk Total Harga Jual tersebut yaitu Cell E15. Perhatikan tahap-tahap berikut:

Define Name untuk rumus excel - membuat named range pada formula
  1. Pertama, klik Cell E15 (yang menggunakan fungsi =SUM(Harga_Jual)). Kemudian klik Tab Formulas dan Klik Define Name seperti cara sebelumnya.
  2. Kedua, Muncul Window New Name, silahkan masukkan Nama Range pada kolom Name. Dalam Contoh ini, Saya memberikan nama Pajak.
  3. Ketiga, Masukkan Persentase Pajak pada Kolom Refers to. Dalam contoh ini, Saya menggunakan pajak 10%, maka masukkan 10%. Kemudian klik Ok untuk menyimpan Named Range.

Selanjutnya, Anda bisa menghitung nilai pada kolom Pajak 10% menggunakan rumus terstruktur Named Range.

Silahkan masukkan Rumus Berikut: =E6*Pajak pada Cell F6. Kemudian Copy-Paste Rumus atau gunakan AutoFill Excel untuk menghitung nilai pada Cell F7 sampai F14 secara otomatis. Hasilnya sebagai berikut:

Named Range menggunakan rumus

Dengan cara ini, Anda bisa mengetahui Pajak masing-masing item, namun dengan Rumus yang lebih Profesional.

Lalu gimana kalo persentase pajak berubah atau Saya ingin menghapus Named Range ? Silahkan gunakan Name Manager berikut:

Name Manager (Edit, Buat Baru, dan Hapus)

Dengan Manage Named Range, Anda bisa membuat Named Range baru atau mengubah dan menghapus Named Range yang ada.

Cara nya cukup mudah. Silahkan klik Tab Formulas kemudian klik Name Manager.

Kemudian muncul Window Name Manager seperti gambar berikut:

Tambahkan, hapus dan edit named range ( define name ) excel

Pada WIndow Name Manager ini, Anda mengatur Semua Named Range serta Table Name (Tabel bernama) yang ada pada WorkBook ini.

Jika Anda ingin menghapus salah satu Named Range, silahkan klik Named Range tersebut kemudian klik Delete.

Jika Anda ingin menambahkan Nama Range baru, silahkan klik New. Namun jika Anda ingin mengubah Named Range yang sudah Ada, maka silahkan klik Nama Range dan klik Edit.

Anda hanya perlu melakukan hal yang sama seperti contoh sebelumnya. Saya berikan contoh, Saya ubah persentase pajak menjadi 15%.

Silahkan klik Named Range Pajak, kemudian klik Edit. Anda hanya perlu mengubah Refers to menjadi 0.15 atau 15% dan lihat hasil pada kolom Pajak berikut:

Contoh edit named range excel

Benar bukan ? Secara otomatis Excel mengubah perhitungan Pajak masing-masing item menjadi 15% tanpa repot-repot mengubah rumus pada masing-masing cell.

Lalu apa selanjutnya ? Silahkan seduh kopi dan nikmati hasil Anda.

Pintasan Panduan Rumus & Fungsi Excel

  1. Rumus VS Fungsi: Perbedaan rumus dan fungsi serta contoh-contoh rumus (seperti perkalian, pembagian, pangkat dll)
  2. Subtract: Rumus Pengurangan pada Excel
  3. Multiply: Rumus Perkalian pada Excel
  4. Divide: Rumus Pembagian pada Excel
  5. Square Root: Rumus Menghitung Akar dan Kuadrat (Pangkat).
  6. Percentage: Rumus Persentase pada Excel dan Cara menghitung Diskon.
  7. Named Range: (Anda Disini).
  8. Dynamic Named Range: Range bernama dinamis dapat meluas secara otomatis ketika Anda menambahkan Cell baru dalam Named Range.
  9. Paste Options: Beberapa pilihan Paste Spesial Excel (berguna jika Anda menyalin cell yang menggunakan rumus, conditional formatting, data validation, format cell dll)
  10. Not Equal To: Contoh menggunakan rumus “Tidak sama dengan <>”.
  11. Cm to Inches: Cara mengubah satuan centimeter ke inchi.

Komentar Paling Lambat di Balas Pukul 23:59.