Advanced Excel

1.

How to Autocomplete Data Validation Drop Down List in Excel

Mar 30, 2022

The Data Validation feature offers control over the values that can be input into a cell. Instead of entering values as usual into the cell, values must be selected from a given…

How to Autocomplete Data Validation Drop Down List in Excel
2.

Excel Data Validation Drop Down List with Filter (2 Examples)

Mar 30, 2022

Let’s consider a dataset containing area-wise sales data of several fruits. We will create a Data Validation drop-down list of areas mentioned in the dataset and use the list to draw fruit…

Excel Data Validation Drop Down List with Filter (2 Examples)
3.

How to Add Item to Drop-Down List in Excel (5 Methods)

Mar 29, 2022

Adding items will depend on how a drop-down list is created. We will consider the following dataset for this purpose. Method 1 - Add Item to Drop-Down List by Adding Item to…

How to Add Item to Drop-Down List in Excel (5 Methods)
4.

How to Make a Data Validation List from Table in Excel (3 Methods)

Mar 29, 2022

  Here's a sample data set to use for the validation list. We opened the Data Validation option from the Data tab. We selected the List option as Allow and type the…

How to Make a Data Validation List from Table in Excel (3 Methods)
5.

Using VBA to Select a Value from a Drop Down List in Excel – 2 Methods

Mar 29, 2022

Creating a Dropdown List from a Generic List in Excel In the dataset below, there are repeated values (Apple in B7 and B9) . Create a dropdown list with Grapes, Orange, Apple,…

6.

How to Make Dependent Drop Down List with Spaces in Excel

Mar 28, 2022

Let’s say we have 3 Product Lists with two-word names, resulting in spaces among words. We have to generate a drop-down list that allows spaces within dependent names. How to Make a…

How to Make Dependent Drop Down List with Spaces in Excel
7.

How to fix a non-working CTRL+F in Excel: 5 Solutions

Mar 28, 2022

Below is a dataset representing the 5 best-nominated movies in the Best Picture category for the 2022 Oscar Awards. Solution 1 –  Set 'Option' to Look in Values  Steps: Choose the Values…

How to fix a non-working CTRL+F in Excel: 5 Solutions
8.

How to Keep Unique Values in a Drop Down List with Excel VBA – 3 Methods

Mar 27, 2022

Quick View: Sub Drop_Down_List_Unique_Values_At_Least_Once() List_Location = "B3" Data = Range(List_Location).Validation.Formula1 Data = Split(Data, ",") Range(List_Location).Validation.Delete Unique_Data = "" Count = 0 For i = LBound(Data) To UBound(Data)     Unique_Values = Split(Unique_Data, ",")…

How to Keep Unique Values in a Drop Down List with Excel VBA – 3 Methods
9.

How to Copy Filter a Drop-Down List in Excel (5 Methods)

Mar 27, 2022

Method 1 - Using an Advanced Filter Option to Copy a Filter Drop-Down List  We have the products listed in the Product column, but the products are not sorted properly, and some…

How to Copy Filter a Drop-Down List in Excel (5 Methods)
10.

How to Filter Date Range in Excel (5 Easy Methods)

Mar 26, 2022

We'll consider the sample dataset that contains the sales quantity of some electronic products in a shop in January, February, and March. We'll filter the sales based on a date range. Method…

How to Filter Date Range in Excel (5 Easy Methods)
11.

How to Combine Two Scatter Plots in Excel (Step by Step Analysis)

Mar 24, 2022

In Excel, the combination of scatter plots allows you to show and analyze two separate data sets that are connected to each other. A standard chart in Excel typically contains simply one…

How to Combine Two Scatter Plots in Excel (Step by Step Analysis)
12.

How to Remove Duplicates from a Drop Down List in Excel (4 Methods)

Mar 24, 2022

Dataset Overview Let’s assume you have a large Excel worksheet containing information about various fruits and vegetables imported by a country into three different European countries. The dataset includes columns for Product…

How to Remove Duplicates from a Drop Down List in Excel (4 Methods)
13.

How to Remove Used Items from Drop Down List in Excel (2 Methods)

Mar 24, 2022

While working with data validation in Excel, you might need to remove used items from the drop-down list to avoid assigning an item in the list twice. For example, you might have…

How to Remove Used Items from Drop Down List in Excel (2 Methods)
14.

How to Apply Multiple Data Validation in One Cell in Excel: 3 Methods

Mar 24, 2022

Method 1 - Apply Multiple Data Validation in One Cell in Excel with Specific Criteria STEPS: Combine the criteria formulas in a nested formula in cell C13. Copy the nested formula from the…

How to Apply Multiple Data Validation in One Cell in Excel: 3 Methods
15.

How to Create a Data Validation Drop-Down List with VBA in Excel (7 Methods)

Mar 24, 2022

Method 1 – Embedding VBA to Create a Data Validation Drop-Down List  Steps: Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor. In…

How to Create a Data Validation Drop-Down List with VBA in Excel (7 Methods)

Advanced Excel Exercises with Solutions PDF