Cara menggunakan copy down google sheets

Google Spreadsheet adalah Excel buatan Google. Dimana kita bisa menyimpan data di cloud google, yaitu Google Drive. Google Spreadsheet bukan produk baru. Namun pada kesempatan ini Saya mau berbagi tips sederhana untuk menampilkan dropdown list pada Google Spreadsheet.

Apa sih kelebihan menggunakan Google Spreadsheet?

Menurut Saya, kelebihan menggunakan Google Spreadsheet adalah dapat diakses melalui berbagai media. Karena tersimpan dalam Google Drive, maka kita bisa melakukan pengubahan informasi melalui laptop, tablet, bahkan handphone. Asalkan terkoneksi dengan internet dan memiliki akun Gmail.

Apa itu Drop Down List?

Drop Down List adalah fitur untuk menampilkan dua atau lebih opsi inputan yang distandarisasi. Drop Down List digunakan untuk membuat nilai informasi dari suatu kolom menjadi standar, tidak ada proses mengetik manual. Untuk beberapa kasus, pengetikan yang tidak distandarisasi akan menyebabkan kesulitan dalam melakukan analisa data. Misalkan dalam memasukkan informasi Bulan Kegiatan jika tidak distandarisasi pengguna dapat memasukkan informasi dalam bentuk yang beragam, seperti : Jan, Januari, JANUARI, JAN, dsb. Dengan dibuatkan drop down list, pengguna tinggal memilih dari opsi yang disediakan.

Cara Membuat Drop Down List

Lalu bagaimanakah cara membuat drop down list? Nah, berikut ini adalah langkah singkatnya yang lebih sederhana dibandingkan pembuatan drop down list pada Microsoft Excel.

Mula-mula, buatlah worksheet baru untuk menyimpan daftar opsi drop down list.

Lalu, detailkan data opsi drop down list.

Setelah itu, klik kanan pada cell / kotak yang ingin dibuatkan drop down list. Lalu pilih “Validasi Data”.

Setelah klik validasi data, akan muncul window baru yang perlu diisi dengan informasi mengenai drop down list yang akan dibuat.

Selanjutnya, klik rentang data untuk mendefinisikan sumber data untuk drop down list.

Setelah itu akan muncul pertanyaan untuk menentukan lokasi sumber data.

Maka selanjutnya lakukan blok pada sumber data drop down list.

Untuk menolak input data dengan melakukan pengetikan, pilihlah opsi “tolak masukan”.

Selanjutnya, jika dibutuhkan kita dapat memilih untuk menampilkan petunjuk penggunaan drop down list kepada pengguna spreadsheet.

Terakhir, klik tombol “simpan”. Dan selesailah proses pembuatan drop down list.

Berikut ini hasil akhir yang akan kita dapatkan.

Bagaimana Cara Menyalin Drop Down List?

Mudah sekali. Cukup dengan copy kolom yang berisi drop down list lalu paste pada kolom yang diinginkan.

Bagaimana Cara Menghapus Drop Down List?

Caranya sangat mudah. Klik kanan pada kotak drop down list. Lalu pilih “Validasi data”. Nah, pada window “Validasi Data”, klik pada tombol “hapus validasi”.

We can create a multi-row dynamic dependent drop-down list in Google Sheets without using any Google Apps Script. This tutorial explains how.

I am just going to use only built-in Google Sheets functions to create a multi-row dynamic dependent drop-down list.

Why Dynamic Dependent Drop-Down Lists Are Useful?

I will explain to you, why a dynamic dependent drop-down list in Google Sheets is a must.

Imagine you are running a bookstore. Here you can create a drop-down list that contains all the authors’ name and their book titles.

When you get a bulk order inquiry, normally from educational institutions, you can send this drop-down list to the customer via email. So that they can easily understand the available book titles with you.

Also with a few clicks, they can select the author as well as book titles from the drop-down and send them back to you.

There are plenty of such situations where you can use dynamic dependent drop-down lists.

A. Create a Simple Drop-Down List in Google Sheets

Anyone with limited spreadsheet exposure can easily create a simple drop-down list, not dynamic. How?

I think I should touch on this part first before going to our tutorial, i.e., Dynamic Dependent Drop-Down List in Google Sheets. This is the essence of the tutorial.

Your first drop-down menu in Google Sheets is just a click away. To create a simple drop-down list do as follows.

In cell A2, you can see a drop-down list. This drop-down list allows you to pick any item from the range in C2:C8 from within A2.

Steps:

Here the active cell is A2 in “Sheet2.” Go to the menu Data > Data Validation. Set the rules as below.

Our earlier tutorial Restrict People from Entering Invalid Data on Google Doc Spreadsheet also shed more light on data validation.

Recommended Reading: The Best Data Validation Examples in Google Sheets.

Now let us move to more complex forms of the drop-down list.

B. How to Create a Dependent Drop-Down List in Google Sheets

To create a dependent drop-down, needless to say, first, there should be a drop-down list. It works like this.

You can see the drop-down list in cell A1 and its dependent in cell B1 in the above screenshot.

You select “Leo Tolstoy” in cell A1. Then all his books will be available to select in cell B1.

When you pick another author from the list, the same author’s books should be available for selection in cell B1.

Now let us learn how to create a dependent drop-down list in Google Sheets.

There are the names of two authors in the range C2:D2. This range we can use to create a drop-down list.

See the list of their books in the range C3:D10 that we can use to create a dependent drop-down list.

We can use the data in cell C2:D2 for the drop-down as below. This is the same as the simple drop-down list detailed under title A.

It’s like this. Make sure that cell A2 is the active cell. Then go to Data > Data Validation.

The “Criteria” is “List from Range” and Criteria Range is C2:D2. That’s all. We have now created a drop-down list in cell A2 as below that contains two of the authors’ names.

In cell B2, we are going to create a dependent drop-down. It’s sometimes called a Dynamic Dependent Drop Down List in Google Sheets.

Here we are using Google Sheets Named Ranges. What is the purpose of Named Ranges in Google Sheets?

With the Named Ranges feature, we can name a range, e.g., C3:C9, to something like sales, total, like any name, and use the same in formulas instead of the range.

Similar: A Drop-down Menu in Google Sheets to View Content from Any Sheets in the Current Sheet

Named Range Is Not a Must Here. Then Why Are We Using It?

The main reason, we can use Named Ranges in formulas instead of a range. By doing so the formula becomes more readable in the future.

Here we have two authors in the drop-down in cell A2. So we require two named ranges pointing to their book titles.

Create the first named range as below.

Go to the menu Data > Named Ranges and click “Add Range”. Here I’ve given the name Helen_Keller as a Named Range name for the range C3:C9. I’ve used underscore as Named Ranges won’t accept spaces.

Similarly above, add another name Leo_Tolstoy for the range D3:D10. So we have two named ranges now!

Formula Part in Google Sheets Dynamic Drop-Down List

We have already a drop-down list in cell A2. Now we should write a formula connecting the drop-down and the list. In cell E2 we can write the formula.

Formula:

=if(A2=C2,indirect("Helen_Keller"),Indirect("Leo_Tolstoy"))

I am just doing a logical test with Google Sheets IF logical function here.

The formula compares the value in cell A2 with C2. If it matches, the formula would populate the value from the range C3:C9 (Helen_Keller), otherwise, it would populate the range D3:D10 (Leo_Tolstoy).

We can’t independently use Named Range in the logical test. You should use it with the Indirect Function as above.

What is the benefit of using the Indirect and named range combo here? Actually, it’s equal to the below formula.

=ArrayFormula(if(A2=C2,C3:C9,D3:D9))

With Named Range and Indirect combo, we can just avoid the ArrayFormula. Also when the number of lists is large, using an Indirect and Named Range combo can make your formula cleaner.

Now when you select an item from the drop-down in A2, accordingly the values populate in cell E2:E, where we applied the formula. Now we can move to the final step, which is creating the dependent drop-down list.

To do that, go to cell B2, then go to the menu Data > Data Validation.

Set the criteria range E2:E10 and voila! Your dynamic drop-down list in Google Sheets is ready.

From this point, we can create a multi-row dynamic dependent drop-down list in Google Sheets.

We just want to modify the formula for this. But again I’m repeating the whole process with more authors and books.

C. How to Create a Multi-Row Dynamic Dependent Drop-Down List in Google Sheets

Now let us create a multi-row dynamic drop-down list.

As said above, we are following the same steps under title B. But a few more additional steps are required, which I will explain as and when required.

In cell A1, first, we should create a drop-down list with the author’s name. In order to do this;

Go to Menu Data > Data Validation. Select the criteria range as C1:F1, which is the authors’ names.

Here we have a list of authors as a drop-down in cell A1. From this, we can select any of the four authors. Now we need a dependent drop-down list in cell B1 to select the book related to the author in cell A1.

Let’s do that part.

Named Ranges: See the set of named ranges for this purpose. If you have any doubt about creating Named Ranges, just scroll up and see the steps under Title B.

Formula Part in Multi-Row Dynamic Dependent Drop-Down List in Google Sheets

Apply the below formula in Cell G1.

=if(A1=C1,indirect("Agatha_Christie"),if(A1=D1,indirect("Sir_Arthur_Conan_Doyle"),if(A1=E1,indirect("Helen_Keller"),INDIRECT("Leo_Tolstoy"))))

Now in B1, we can create a dynamic dependent drop-down list. Again I am telling you all the steps are already detailed under Title B above.

Go to B1 and then select the Data menu Data Validation. Set the criteria range G1:G15.

Now you can select any author from under the drop-down list in cell A1. Then you can select the corresponding author’s book from cell B1.

Up to here, the steps are similar to that provided under Title B. Now we want the same list in a multi-row.

I mean we are creating a Multi-Row Dynamic Dependent Drop Down List in Google Sheets.

Here just copying and pasting the drop-down lists in A1 and B1 to the adjoining cells below won’t work.

We should modify the above formula in cell G1 as below.

=ArrayFormula(if(len(A1:A),transpose(if(transpose(A1:A)=C1,indirect("Agatha_Christie"),if(transpose(A1:A)=D1,indirect("Sir_Arthur_Conan_Doyle"),if(transpose(A1:A)=E1,indirect("Helen_Keller"),INDIRECT("Leo_Tolstoy"))))),""))

Now go to cell B1 and modify the data validation range from G1:G15 to G1:T1.

A Very Important Update on 02-Feb-2021:

Make sure that, in the data validation, you use absolute reference in A1 and relative reference in B1. To do that;

Click cell A1, go to Data > Data validation, and make sure that the range is =$C$1:$F$1 not =C1:F1.

Then click cell B1 and go to Data > Data validation and make sure that the range is =G1:T1 not =$G$1:$T$1.

Now copy and paste the lists in cell A1 as well as cell B1 down as far as you want.

Update on 02-02-2021: The below step is not required!

After that, in cell B2, B3, B4, etc. change the data validation range as G2: T2, G3: T3, G4: T4 respectively. This’s because while copying and pasting a data validation list, there is no option in Google Sheets to change the range automatically.

That’s all.

We have successfully created our first-ever Multi-Row Dynamic Dependent Drop Down List in Google Sheets. Hope you enjoy it!

Jelaskan langkah langkah membuat drop down dalam Spreadsheet?

Apa itu Drop Down di Google Form?

Bagaimana cara wrap text di Spreadsheet?

Bagaimana cara menambahkan warna ke daftar drop down di Spreadsheet?