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 hashtag (#) 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 A₁, atau B₃ atau $A$₁ atau R₁C₁ dan lain sebagainya. Namun, Anda masih bisa menggunakan gabungan nama referensi cell misalnya gabungan nama cell A₁ dan C₃, maka Anda bisa ketik A₁C₁ atau A₁_C₁

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

Download Contoh Cara Memberikan Named Range Excel

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

#1 Menggunakan Name Box

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

Dalam contoh ini, Saya memiliki data Nilai pada Range A₂:C₉. Kemudian silahkan ikuti cara berikut untuk memberikan nama pada Range tersebut:

1. Blok atau pilih range data A₂:C₉.
2. Klik Name Box yang berada disebelah kiri Formula Bar.
3. 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 (A₂:C₉). Jika Anda hanya klik pada Cell A₂ atau B₅ 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:

1. Klik Cell A₁₁, kemudian ketik Fungsi SUM sebagai berikut: =SUM(
2. Ingat! nama Range A₂:C₉ 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. Anda bisa lanjutkan ketik huruf a dan i atau double click pada sugesti Named Range dari Excel tersebut. Kemudian tekan Enter. Berikut hasilnya:

Anda bisa lihat, Excel menjumlahkan semua data yang terdapat pada Range A₂:C₉ 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 file Excel yang sudah Saya berikan sebelumnya. Dalam contoh Saya memiliki sebuah Tabel Transaksi pada Range E₄:H₁₃.

Saya akan memberikan Named Range pada Kolom Pembelian (range G₅:G₁₃) dan Kolom Penjualan (Range H₅:H₁₃).

Kemudian melakukan perhitungan atara kedua Range tersebut. Perhatian tahap-tahap berikut menggunakan named range berikut:

1. Blok atau pilih Range G₅:G₁₃.
2. Klik Tab Formula, kemudian klik Define Name. Atau Anda juga bisa menggunakan Klik Kanan pada Range Data, kemudian klik Define Name.
3. Muncul Window New Name, silahkan isi Nama Range menjadi Pembelian.
4. 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. Klik Ok untuk melanjutkan.
6. Silahkan blok atau pilih Range H₅:H₁₃. 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:

1. Ketik Rumus SUM pada Cell K₆ seperti berikut: =SUM(
2. Masukkan Range G₅:G₁₃ atau Anda juga bisa ketik nama range Penjualan
3. Ketik tanda tutup kurung ) dan ketik tanda kurang – sehingga rumus menjadi =SUM(Penjualan)-
4. Ketik SUM(Pembelian) kemudian tekan Enter. Berikut hasilnya:

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 I₁₄ 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 file Excel yang sudah Saya berikan sebelumnya.

Saya memiliki Tabel Penjualan yang tersusun pada Range A₅:E₁₄. 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 A₆:A₁₄ akan diberi Nama Range Tanggal. Sedangkan Range C₆:C₁₄ 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:

1. Blok atau pilih semua tabel (termasuk header) yaitu Range A₅:E₁₄. Ingat! jangan lupa headernya juga ikut di blok.
2. Klik Tab Formulas, kemudian klik Create Form Selection.
3. 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:

• A₆:A₁₄ : Tanggal
• B₆: B₁₄ : ID_Produk
• C₆:C₁₄ : Nama_Produk
• D₆: D₁₄ : Harga_Modal
• E₆:E₁₄ : 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 (baris paling atas) dari Range data yang Anda pilih.

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 B₅:E₅ akan dibuat menjadi Named Range dengan nama Tanggal. Selanjutnya, Range B₆:E₆ 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:

Jika pajak dihitung dari harga jual, maka untuk menghitung pajak pada Cell F₆ Anda biasanya akan menggunakan rumus berikut: =E6*10%. Sementara untuk pajak pada Cell F₇, 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.

Pertama-tama, silahkan buat Total dari Harga Jual.

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

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

1. Klik Cell E₁₅ (yang menggunakan fungsi =SUM(Harga_Jual)). Kemudian klik Tab Formulas dan Klik Define Name seperti cara sebelumnya.
2. Muncul Window New Name, silahkan masukkan Nama Range pada kolom Name. Dalam Contoh ini, Saya memberikan nama Pajak.
3. 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 F₆. Kemudian Copy-Paste Rumus atau gunakan AutoFill Excel untuk menghitung nilai pada Cell F₇ sampai F₁₄ secara otomatis. Hasilnya sebagai berikut:

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.

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:

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.