Shulou(Shulou.com)11/24 Report--

Today's Excel class begins.

We know that if you want to re-enter data in Excel, you can make a drop-down menu.

You can enter data directly at the click of a mouse.

But if there is a lot of data, using the drop-down menu is not as efficient as typing?

No way! I have an easier way to make a secondary drop-down menu:

For example, there are six provinces here, and each province corresponds to many cities. If you set up a drop-down menu for the city, there will be too much data.

At this time, use the second-level drop-down menu: when you choose Hubei Province, the corresponding drop-down menu can only choose Wuhan City, Yichang City, Shiyan City, Jingzhou City, Huangshi City.

Is it very cool and practical?

How's it going? Do you want to learn?

If you want to learn, let's take a look at the following illustration.

First-level drop-down menu ❶ select the range of cells that need to be set for the drop-down menu, click the "data" tab-"data validation", and pop up the "data validation" dialog box.

Select "sequence" for the ❷ verification condition; check each province in the table at "Source".

After completing these two steps, when entering data in the "province" column, you can directly use the drop-down menu to select!

The second-level drop-down menu is ready, the first-level drop-down menu is ready, let's take a look at how to make the second-level drop-down menu.

❶ select the provincial and municipal data area, click the "Formula" tab-"create according to the selected content"-- check only "first Line"-"OK" in the pop-up box.

❷ select the range of cells that need to be set for the drop-down menu, click the "data" tab-"data validation", and pop up the data validation prompt box.

Select "sequence" for the ❸ verification condition; enter the formula at "Source":

= indirect (C2)

Through these three steps, you can get the secondary drop-down menu of each city created according to the province.

See here, you may feel confused, do not understand the above operation.

It doesn't matter, Xiao an, let's explain in detail:

To create a name based on the selection and select the first line is to give the city an overall name, which is the content of the first line cell.

For example: Wuhan City, Yichang City, Shiyan City, Jingzhou City, Huangshi City, the five cities are collectively called Hubei Province.

The indirect (C2) function, which refers to the "city" contained in the "province" of the C2 cell.

For example, if you select Hubei Province in the C2 cell, D2 can only drop down to select the provinces that Hubei Province contains.

To sum up, the second-level drop-down menu is based on the first-level drop-down menu, using custom names and indirect functions.

How's it going? have you learned?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: witty Qiu Xiao E

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.