এক্সেল ড্রপ-ডাউন লিস্ট মास্টার করুন: তৈরি, এডিট, রিমুভ, ফিল্টার ও প্রোটেক্ট

ডেটাসেটটি অনলাইন শপিং তথ্য প্রদর্শন করে: 'Order ID', 'Customer Name', 'Device', 'Order Date', 'Delivery date', এবং 'Payment Method'।

এক্সেলে ড্রপ-ডাউন লিস্ট কী?

ড্রপ-ডাউন লিস্ট হল একটি ফিচার যা ব্যবহারকারীদের অপশনের একটি লিস্ট থেকে একটি এলিমেন্ট সিলেক্ট করতে দেয়।

এক্সেলে ড্রপ-ডাউন লিস্ট কীভাবে তৈরি করবেন?

১. সেল রেঞ্জ থেকে ড্রপ-ডাউন লিস্ট তৈরি

কলাম B-এর ভ্যালুতে ভিত্তি করে একটি ড্রপ-ডাউন লিস্ট তৈরি করতে:

• একটি সেল সিলেক্ট করুন (C18)।
• Data ট্যাবে যান এবং Data Validation সিলেক্ট করুন।

• Data Validation-এ, Setting-এ যান।
• List-এ, Allow চুজ করুন।
• Source-এ একটি রেঞ্জ ডিফাইন করুন ($B$6:$B$15)।
• OK ক্লিক করুন।

এটি আউটপুট।

• C19-এ নিচের ফর্মুলা দিন যাতে Order ID সিলেকশন বেসড কলাম C-এর ভ্যালু मिलে।

=VLOOKUP($C$18,$B$6:$G$15,ROW(B19)-ROW($B$18)+1,FALSE)

• ভ্যালু এক্সট্রাক্ট করতে Fill Handle ব্যবহার করুন।

২. টেবিল থেকে ড্রপ-ডাউন লিস্ট তৈরি

• একটি সেল রেঞ্জ সিলেক্ট করুন এবং টেবিল তৈরি করতে Insert ট্যাবে যান।
• Table ক্লিক করুন।

• My table has headers চেক করুন।
• OK ক্লিক করুন।

• C18-এ Customer Name-এ ডেটা দিয়ে ড্রপ-ডাউন লিস্ট তৈরি করতে, Allow-এ List সিলেক্ট করুন।
• Source-এ INDIRECT ফাংশন দিয়ে নিচের ফর্মুলা দিয়ে OK ক্লিক করুন।

=INDIRECT("Table1[Customer Name]")

• C19-এ নিচের ফর্মুলা দিয়ে কাস্টমার নাম সিলেকশন বেসড ডিভাইস নাম এক্সট্রাক্ট করুন।

=INDEX(Table1,MATCH(C18,Table1[Customer Name],0),3)

এটি আউটপুট।

৩. নেমড রেঞ্জ দিয়ে ড্রপ-ডাউন লিস্ট তৈরি

• একটি সেল রেঞ্জ সিলেক্ট করুন এবং Formulas ট্যাবে যান।
• Define Name ক্লিক করুন।

• Name-এ একটি নাম (ID) সেট করুন এবং Refers to-এ একটি রেঞ্জ ডিফাইন করুন।

• C19-এ অর্ডার আইডির ড্রপ-ডাউন লিস্ট তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

• C19-এ নিচের ফর্মুলা ব্যবহার করুন অর্ডার আইডি সিলেকশন বেসড কাস্টমার নাম পাওয়ার জন্য।

=INDEX(B6:G15,MATCH($C$18,ID,0),2)

• C20-এ ডিভাইসের নাম পাওয়ার জন্য নিচের ফর্মুলা ব্যবহার করুন।

=INDEX(B6:G15,MATCH($C$18,ID,0),3)

এটি আউটপুট।

৪. রঙসহ ড্রপ-ডাউন লিস্ট তৈরি

Conditional Formatting দিয়ে ড্রপ-ডাউন লিস্টের প্রতিটি ভ্যালুর জন্য কাস্টম রঙ ডিফাইন করুন।

• Allow-এ List সিলেক্ট করুন।
• Source-এ রঙের নামগুলো কমা দিয়ে আলাদা করুন: (Black,White,Silver,Gold)।

• ড্রপ-ডাউন সিলেক্ট করুন এবং Home ট্যাবে Conditional Formatting-এ যান।
• Highlight Cell Rules-এ যান এবং Equal To… ক্লিক করুন।

• Equal To-এ রঙের নাম দিন (Black)।
• Custom Format সিলেক্ট করুন রঙের নামের জন্য রঙ সেট করতে।

• Fill-এ একটি রঙ বেছে নিন।

• Font-এ ফন্ট রঙও পরিবর্তন করতে পারেন। এখানে সাদা।

• OK ক্লিক করুন।

• অন্য অপশনগুলোেও রঙ সেট করুন।

ড্রপ-ডাউন লিস্ট সেল ভ্যালু বেসড রঙ প্রদর্শন করে।

সিলেকশন বেসড রঙ অটোমেটিক্যালি পরিবর্তন হবে।

• Fill Handle ব্যবহার করে ড্রপ-ডাউন AutoFill করুন।

৫. ইউনিক ভ্যালু দিয়ে ড্রপ-ডাউন লিস্ট তৈরি

কলাম D এবং E-র ইউনিক ভ্যালু দিয়ে ড্রপ-ডাউন লিস্ট তৈরি করুন।

• B22-এ নিচের ফর্মুলা ব্যবহার করুন ইউনিক ব্র্যান্ড নাম দেখতে।

• C5-এ ইউনিক ভ্যালু দিয়ে ড্রপ-ডাউন লিস্ট তৈরি করতে নিচের ফর্মুলা Source-এ দিন।

• C22-এ কলাম E-র ইউনিক ভ্যালু দেখতে নিচের ফর্মুলা দিন যা C5-এ ব্র্যান্ডের সাথে ম্যাচ করে।

=FILTER(E9:E18,C5=D9:D18)

• Data Validation-এ যান।
• Source-এ নিচের ফর্মুলা ব্যবহার করুন।

এটি আউটপুট।

৬. অন্য শিটের ডেটা বেসড ড্রপ-ডাউন লিস্ট তৈরি

মোবাইল ব্র্যান্ড এবং তাদের ফোন মডেল PhoneList ওয়ার্কশিটে আছে।

ব্র্যান্ড নাম এবং ফোন মডেল ব্যবহার করে অন্য একটি ওয়ার্কশিট (AnotherSheet) এ দুটি ড্রপ-ডাউন লিস্ট তৈরি করুন।

• D6-এ PhoneList ওয়ার্কশিটের ডেটা দিয়ে ব্র্যান্ড নামের ড্রপ-ডাউন লিস্ট তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

• Fill Handle ব্যবহার করে কলাম D-এ একই ড্রপ-ডাউন লিস্ট তৈরি করুন।
• INDIRECT ফাংশন দিয়ে Source-এ নিচের ফর্মুলা ব্যবহার করুন যাতে কলাম D-এ ব্র্যান্ড নাম সিলেকশন বেসড PhoneList ওয়ার্কশিট থেকে ফোন মডেল ড্রপ-ডাউন-এ আসে।

ছবি বড় করতে এখানে ক্লিক করুন।

এটি আউটপুট।

৭. অন্য ওয়ার্কবুকের ডেটা বেসড ড্রপ-ডাউন লিস্ট তৈরি

AvailablePaymentMethods ওয়ার্কবুকে অনলাইন শপিং-এর পেমেন্ট মেথডের একটি লিস্ট আছে।

• Formulas-এ যান এবং Define Name সিলেক্ট করুন।

• New Name-এ, Name-এ একটি নাম (PayType) ডিফাইন করুন।
• Refers to-এ একটি রেঞ্জ (Sheet1!$B$3:$B$7) ডিফাইন করুন।
• OK ক্লিক করুন।

• drop-down in Excel workbook-এ যান এবং একটি নেমড রেঞ্জ সেট করুন (PayType)।
• [AvailablePaymentMethods.xlsx]Sheet1!PayType দিয়ে AvailablePaymentMethods ওয়ার্কবুকের নেমড রেঞ্জ ডিফাইন করুন।
• G6-এ নেমড রেঞ্জ দিয়ে ড্রপ-ডাউন লিস্ট তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

• আপনি ড্রপ-ডাউন লিস্ট থেকে একটি পেমেন্ট মেথড সিলেক্ট করতে পারেন।

• Fill Handle ব্যবহার করে ড্রপ-ডাউন লিস্ট AutoFill করুন।

নোট

i. ড্রপ-ডাউন তৈরি করার সময় দুটি ওয়ার্কবুক খোলা থাকতে হবে।
ii. অন্য ওয়ার্কবুক থেকে একাধিক ডিপেন্ডেন্ট ড্রপ-ডাউন লিস্ট তৈরি করবেন না।

৮. ডিপেন্ডেন্ট ড্রপ-ডাউন লিস্ট তৈরি

• D6-এ স্বাধীন ব্র্যান্ডের ড্রপ-ডাউন লিস্ট তৈরি করতে Source (Data Validation)-এ নিচের ফর্মুলা দিন।

• E6-এ ডিপেন্ডেন্ট ফোন মডেলের ড্রপ-ডাউন লিস্ট তৈরি করতে INDIRECT ফাংশন দিয়ে Source-এ নিচের ফর্মুলা দিন।

ছবি বড় করতে এখানে ক্লিক করুন।

স্বাধীন ড্রপ-ডাউন লিস্ট থেকে ব্র্যান্ড সিলেকশন বেসড ডিপেন্ডেন্ট ড্রপ-ডাউন লিস্ট তৈরি হয়েছে।

• Fill Handle ব্যবহার করে ড্রপ-ডাউন লিস্টগুলো AutoFill করুন।

৯. সার্চেবল ড্রপ-ডাউন লিস্ট তৈরি

• নতুন কলাম (G এবং H) ইনসার্ট করুন ইউনিক ব্র্যান্ড এবং মডেল নাম পাওয়ার জন্য। G6-এ নিচের ফর্মুলা ব্যবহার করুন।

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• C19-এ ব্র্যান্ড নাম দিয়ে সার্চেবল ড্রপ-ডাউন লিস্ট তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

আপনি C19-এ ব্র্যান্ড নাম সার্চ করতে পারবেন।

• H6-এ সার্চেবল ড্রপ-ডাউন-এ ব্র্যান্ড সিলেকশন বেসড ফোন মডেল দেখতে নিচের ফর্মুলা দিন।

=UNIQUE(FILTER(E6:E17,C19=D6:D17))

• C20-এ ফোন মডেল নাম দিয়ে সার্চেবল ড্রপ-ডাউন তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

আপনার স্বাধীন এবং ডিপেন্ডেন্ট সার্চেবল ড্রপ-ডাউন লিস্ট রেডি।

১০. ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি (নতুন এন্ট্রি যোগ ও অটো আপডেট)

আইডি বেসড ভ্যালু এক্সট্রাক্ট করতে।

ছবি বড় করতে এখানে ক্লিক করুন।

• একটি আলাদা কলাম ইনসার্ট করুন এবং L6-এ নিচের ফর্মুলা দিন কলাম B-এর ভ্যালু বেসড ইউনিক আইডি দেখতে।

=UNIQUE(OFFSET(B6,0,0,COUNTA(B:B)-3,1))

ছবি বড় করতে এখানে ক্লিক করুন।

• J5-এ ইউনিক আইডি দিয়ে ড্রপ-ডাউন লিস্ট তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

ছবি বড় করতে এখানে ক্লিক করুন।

• J6-এ সিলেক্ট করা আইডির জন্য কাস্টমার নাম দেখতে নিচের ফর্মুলা দিন।

=VLOOKUP($J$5,B:G,ROW(I6)-ROW($I$5)+1,FALSE)

• বাকি ভ্যালুগুলো পেতে Fill Handle ব্যবহার করে AutoFill করুন।

ছবি বড় করতে এখানে ক্লিক করুন।

• এক্সট্রাক্ট করা ডেটাগুলো ইন্টিজার নাম্বার হিসেবে দেখাবে। Number Format-এ Short Date বা Long Date এ পরিবর্তন করুন।

• অতিরিক্ত আইডি এবং তাদের সংশ্লিষ্ট ভ্যালু যোগ করুন। সেগুলো ড্রপ-ডাউন লিস্ট এবং কলাম L-এ অটোমেটিক্যালি আপডেট হবে।

ছবি বড় করতে এখানে ক্লিক করুন।

এটি আউটপুট।

১১. সার্চেবল, ডাইনামিক, এবং মাল্টি-ডিপেন্ডেন্ট ড্রপ-ডাউন লিস্ট তৈরি

• L6-এ নিচের ফর্মুলা দিন।

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• J5-এ ব্র্যান্ড নামের স্বাধীন সার্চেবল ড্রপ-ডাউন লিস্ট তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

• J5-এ ড্রপ-ডাউন লিস্ট থেকে ব্র্যান্ড সিলেক্ট করে ফোন মডেল ফিল্টার করুন। নিচের ফর্মুলা ব্যবহার করুন।

=UNIQUE(FILTER(E:E,J5=D:D))

• J6-এ ব্র্যান্ড নাম ডিপেন্ডেন্ট সার্চেবল ফোন মডেল নামের ড্রপ-ডাউন লিস্ট তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

• J6-এ ড্রপ-ডাউন লিস্ট থেকে মডেল সিলেক্ট করে চিপসেট ফিল্টার করুন। নিচের ফর্মুলা ব্যবহার করুন।

=UNIQUE(FILTER(F:F,J6=E:E))

• J7-এ মডেল নাম ডিপেন্ডেন্ট সার্চেবল চিপসেটের ড্রপ-ডাউন লিস্ট তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

• J8-এ সিলেক্ট করা ভ্যালুগুলো ম্যাচ করে প্রোডাক্টের প্রাইস খুঁজতে নিচের ফর্মুলা দিন।

=INDEX(G:G,MATCH(1,(J5=D:D)*(J6=E:E)*(J7=F:F),0))

যেহেতু সব ড্রপ-ডাউন লিস্ট ডাইনামিক, আপনি নতুন এন্ট্রি যোগ করলে ড্রপ-ডাউন লিস্টগুলো আপডেট হবে।

১২. কাস্টম মেসেজসহ ড্রপ-ডাউন লিস্ট তৈরি

• Method 7-এ বর্ণনা অনুযায়ী ফোন মডেলের একটি ডিপেন্ডেন্ট ড্রপ-ডাউন লিস্ট তৈরি করুন।

• Data Validation-এ, Input Message-এ যান।
• Show input message when cell is selected চেক করুন।
• Title-এ একটি টাইটেল এবং Input message-এ একটি কাস্টম মেসেজ সেট করুন।
• OK ক্লিক করুন।

সেল সিলেক্ট করলে কাস্টম মেসেজ প্রদর্শিত হবে।

১৩. এরর এলার্ট মেসেজসহ এডিটেবল ড্রপ-ডাউন লিস্ট তৈরি

• G6-এ নিচের ফর্মুলা দিয়ে ব্র্যান্ড নামের একটি ডাইনামিক কলাম তৈরি করুন।

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• C19-এ ব্র্যান্ড নামের ডাইনামিক সার্চেবল ড্রপ-ডাউন লিস্ট তৈরি করতে Source-এ নিচের ফর্মুলা দিন।

• এডিটেবল ড্রপ-ডাউন লিস্ট তৈরি করতে Error Alert ট্যাবে যান।
• Show error alert after invalid data is entered বক্স চেক করুন।
• ভুল সার্চ ভ্যালুর জন্য একটি স্টাইল, টাইটেল, এবং এরর মেসেজ সেট করুন এবং OK ক্লিক করুন।

• Model-এ ভ্যালু দিয়ে একটি ড্রপ-ডাউন লিস্ট তৈরি করুন।

যদি আপনি সেই এডিটেবল ড্রপ-ডাউন লিস্টগুলোর যেকোনোতে ভুল ইনপুট দেন, তবে রিট্রাই বা ক্যান্সেল অপশনের সাথে এরর এলার্ট প্রদর্শিত হবে।

এক্সেল ড্রপ-ডাউন লিস্টে আইটেম কীভাবে যোগ বা সরাবেন?

Source-এ রেঞ্জ ডিফাইন করার সময় সেল যোগ বা সরিয়ে আপনি আইটেম যোগ বা সরাতে পারেন।

এক্সেলে ড্রপ-ডাউন লিস্ট কীভাবে প্রোটেক্ট করবেন?

C18-এ কাস্টমার নামের একটি ড্রপ-ডাউন লিস্ট তৈরি করা হয়েছিল।

• পুরো টেবিল সিলেক্ট করুন এবং Home ট্যাবে যান।
• Alignment ক্লিক করুন এটিকে এক্সটেন্ড করতে।

• Format Cells-এ, Protection ট্যাবে Locked চেক করুন।

• টেবিল প্রোটেক্টেড। Review ট্যাবে যান এবং Protect Workbook-এ ক্লিক করুন।

• একটি পাসওয়ার্ড দিন এবং Select locked cells চেক করুন।
• OK ক্লিক করুন।

• পাসওয়ার্ড রি-এন্টার করুন।

টেবিল এবং ড্রপ-ডাউন লিস্ট উভয় প্রোটেক্টেড।

এক্সেলে ড্রপ-ডাউন লিস্ট কীভাবে ডিলিট করবেন?

• ড্রপ-ডাউন লিস্ট সিলেক্ট করুন এবং Data ট্যাবে যান।
• Data Validation ক্লিক করুন।

• Data Validation-এ, Clear All ক্লিক করুন।
• OK ক্লিক করুন।

ড্রপ-ডাউন লিস্ট রিমুভ হয়ে যাবে।

এক্সেলে ড্রপ-ডাউন লিস্ট ফিল্টার ও সিলেকশন বেসড ডেটা এক্সট্র্যাক্ট কীভাবে করবেন?

• একটি টেবিল তৈরি করুন।

• কলাম D-এ একটি ড্রপ-ডাউন লিস্ট তৈরি করুন।

• সিলেকশন বেসড পুরো ডেটা এক্সট্রাক্ট করতে নিচের ফর্মুলা দিন।

=FILTER(Table3,Table3[Device]=$C$18,"No Information Available")

• ড্রপ-ডাউন লিস্টের ভ্যালু পরিবর্তন করুন এবং ডেটা এক্সট্রাকশন অটোমেটিক্যালি আপডেট হবে।

এক্সেল ড্রপ-ডাউন লিস্টের সমস্যা কীভাবে সমাধান করবেন?

১. ড্রপ-ডাউন লিস্ট ভিজিবল নয়

সমাধান:

• In-cell dropdown চেক করুন ড্রপ-ডাউন লিস্ট ভিজিবল করতে।

২. ড্রপ-ডাউন লিস্টে ব্ল্যাঙ্ক দেখাচ্ছে

সমাধান:

• ব্ল্যাঙ্ক ইগনোর করতে ড্রপ-ডাউন লিস্ট রেঞ্জ ডিফাইন করুন।

৩. বৈধ এন্ট্রি ড্রপ-ডাউন লিস্টে অ্যালোড নয়

সমাধান:

• বৈধ এন্ট্রি দিতে वक्त প্রপার কেস ব্যবহার করুন।

৪. অবৈধ এন্ট্রি ড্রপ-ডাউন লিস্টে অ্যালোড হচ্ছে

যেহেতু সোর্স রেঞ্জ আসল ভ্যালু লিস্টের চেয়ে বড়, সেই সেলগুলোতে এন্টার করা ভ্যালুগুলো লিস্টেড হতে পারে, যদিও সেগুলো অবৈধ।

সমাধান:

• অবৈধ এলিমেন্টের এন্ট্রি বন্ধ করতে ড্রপ-ডাউন লিস্ট রেঞ্জ ডিফাইন করুন।

ডাউনলোড প্র্যাকটিস ওয়ার্কবুক
প্র্যাকটিস ওয়ার্কবুক ডাউনলোড করুন।

এক্সেল ড্রপ-ডাউন লিস্ট: নলেজ হাব

• Create drop-down List in Excel
• Edit drop-down List in Excel
• drop-down List with Filter in Excel
• Excel Dependent drop-down List
• How to Remove drop-down List in Excel
• How to Link a Cell Value with a drop-down List in Excel
• Excel drop-down List Not Working
• IF Statement to Create Drop-Down List in Excel
• Extract Data Based on a drop-down List Selection in Excel
• Populate List Based on Cell Value in Excel
• drop-down List in Multiple Columns in Excel
• Blank Option to drop-down List in Excel
• Select from drop-down and Pull Data from Different Sheet
• [Fixed!] drop-down List Ignore Blank Not Working in Excel
• Autocomplete Data Validation drop-down List in Excel

<< Go Back to Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!