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

Original title: "Let the leader look silly, Excel three-level drop-down menu, 10 seconds automatically generated!" "

Hello, everyone. I'm Xiaolan who loves and kills each other with Excel.

Cousins who often use Excel must know the artifact of "drop-down menu". With a click of a mouse, you can gently enter data:

▲ first-level drop-down menu its production method is also very simple, with [data verification] function can be directly achieved!

Earlier, we also talked about the production method of the second-level drop-down menu (click here to see it), but a small friend left a message that it is not enjoyable, but also want to see how to do the three-level drop-down menu?

Needless to say, since the students have this request, Xiaolan will certainly satisfy you. Today let's take a look at how the "three-level drop-down menu in Excel" is made.

Here I will introduce two methods:

❶ definition name + Indirect function

❷ square grid.

Let's take a look at the first method.

1. Indirect function method first of all, you need to prepare the data source, as shown in the following figure:

▲ third-level data is more, only part of the content should be noted here, the header of the third-level content (line 7), is connected by the first and second levels.

Everything is ready except Dongfeng. Next, let's do it together.

Convert ▋ to super table, select two tables respectively, and press [Ctrl+T] to convert the table into super table, so that when the source data is modified, added or deleted, the table can automatically expand the area, and the drop-down menu will be refreshed synchronously.

▋ definition name Select two tables respectively, click the "Formula" tab, find the "defined name", and click "create based on selection":

In the pop-up window, check the first line and click OK.

In this way, both the secondary content and the tertiary content are given an overall name, which is the content of the [first line] cell.

For example, Shijiazhuang, Handan, Zhangjiakou and Hengshui are collectively called Hebei Province.

Xinhua District, Zanhuang County, Xingtang County, Luquan County, Lingshou County, the four districts and counties are collectively known as Shijiazhuang City, Hebei Province.

Open the name Manager and we can see:

▋ creates a drop-down menu ❶ first-level drop-down menu, as shown in the figure:

Select the desired range of cells, where A16:A23 is selected, then click the data tab, locate the data tools, and click data validation.

In the pop-up window, select allow sequence, select A1:C1 from source, and click OK.

In this way, the first-level drop-down menu is set.

❷ level 2 drop-down menu, as shown in the figure:

The same as the first-level drop-down menu, except that the Indirect function is used when selecting the source:

= Indirect (A16) knock on the blackboard:

The Indirect function is an indirect reference function that returns the reference specified by the text string.

For example, the A16 cell is referenced here, but the result returned is the value in the A2:A5 cell. That is, refer to the municipal level contained in the provinces in column A.

❸ three-level drop-down menu, as shown in the figure:

The previous steps are the same as above, except for Indirect:

= Indirect (A16&B16) that is: both column An and column B are referenced.

Done. Done!

Wait a minute... It's not over yet. Let's take a look at whether the drop-down menu can update ↓↓↓ synchronously after adding data.

All right! The method of using Indirect function to make three-level drop-down menu is over. Have you learned it?

It doesn't matter if you don't learn it for a while. Let's take a look at the square grid method, which is simpler and easier to master.

2. Square grid method first, you have to install a square grid plug-in:

Second, prepare the data source, as shown in the following figure:

Note: here the header of the third level (line 7) is the content of the second level.

And then convert it to a super table:

Once all is ready, the next content is very simple!

❶ select any cell, click the "Square" tab, find "Edit", and click [add insert]-[insert drop-down menu].

❷ in the pop-up window, click "three-level drop-down menu", set the data source according to the example, and so on.

For example, what I fill in here is:

Click "OK" and pop up the following window, which indicates that ↓↓↓ is set up.

Click [OK]-[exit] again to exit the box setting interface.

❸ selects the cells that have been set up, and drops down to fill them in other areas.

All the extra text can be deleted.

In this way, the three-level drop-down menu is done, is it very simple!

3. Sum up. Finally, let's sum up. This article talks about two ways to make three-level drop-down menus with Excel:

❶ Indirect function method, the advantage is good compatibility, will not be limited by the software version; the disadvantage is that the operation is a little bit troublesome.

❷ square grid method is more simple and convenient, as long as your computer is equipped with this plug-in.

Tell me, which method do you prefer?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhu Lan

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.