Section 3: Working with Table Groups

The concept of a list in Excel

A list is an Excel table that consists of one or more columns. The list columns are assigned unique field names, which are entered in the first row of the list. All cells in a column have the same data format, so all lines, or records as they are also called, are of the same type.

Surname	Age	Floor
Petukhova
Petrov
Zaitseva
Morev
Ivanov
Petrova

Rice. 1. Excel list example

In the above Excel table, the data in the first and third columns is in text format, and the data in the second column is in numeric format. The names of the list fields must be placed in one cell. The list data and other data on the same sheet must be separated by at least one empty cell, that is, they must not touch each other. Excel list is a typical database and applies to it large number specific operations. Most of the tables that Excel users work with are lists or can be converted to list form. So, if the table shown in Fig. 2 was created for each group of students, then they could be combined into one table, which would also be a list, by adding another “Group” field.

Group	Surname	Age	Floor	Height	Weight
99-l-3	Petukhova
99-l-3	Petrov
99-l-3	Zaitseva
97-l-1	Popov
97-l-1	Kozlov

Fig.2.Combining tables into a list

Working with lists in Excel

Most operations designed to work with lists are concentrated in the “Data” menu. If the list is created correctly, then just select one of the cells inside the list and click the desired command in the “Data” menu. Excel will automatically determine the boundaries of your list.

Rice. 2. Expanded “Data” menu

The “Sorting” item allows you to sort by selected criterion by one or, in order of priority, by two or even three list fields.

Fig. 3. Two-stage sorting of a list

“Filter” makes it possible to show only those entries in the list that meet some criterion. Thus, setting a custom autofilter, shown in Fig. 3, will display in the list only surnames ending with the letter “v”.

Fig3. Using a custom autofilter

The “Form” item allows you to quickly enter data into the list. The “Results” item allows you to summarize the data under each group in the list. Figure 4 shows the summary dialog box, and Figure 5 shows the result of this operation.

Fig.4. Summing up

Group	Surname	Age	Floor	Height	Weight
99-l-3	Petukhova
99-l-3	Petrov
99-l-3	Zaitseva	19
99-l-3 Total
97-l-1	Popov
97-l-1	Kozlov	19
97-l-1 Total
Grand total

Fig. 5. Result from summing up

“Consolidation” allows you to summarize results for several tables of the same type. The capabilities of the “Consolidation” and “Totals” items are completely covered by the powerful mechanism for building Excel pivot tables.

The “Group and Structure” item is applicable not only to Excel lists and allows you to change the levels of detail in the presentation of information to Excel sheet. To create a simple structure on a sheet, you can select several columns or rows and click “Group”.

Fig.6.Creating a structure

An additional bar with a square icon will appear above the sheet. Clicking on this symbol will hide the columns that have a black line over them. If you select the columns under the black line and select “Group” again, a two-level structure will be created. You can group rows in a similar way.

Fig 7. Result of the grouping operation

Creating a structure makes it possible to view large Excel tables on one screen, hiding data that is not needed at the moment.

Excel Pivot Tables

Pivot tables allow you to perform group operations on data located either in lists, or in several consolidation ranges, or in external databases data. When you click on the “Pivot Table” item in the “Data” menu, the first dialog box of the Pivot Table Wizard appears (see Fig. 8).

Fig.8. Pivot Table Wizard - step 1.

To process the list, you need to select the first of the proposed options and go to the second step of the wizard. In the second step, you need to select the list to be processed and proceed to the third step. At the third step of the wizard (see Fig. 9.) a constructor for processing list data will appear.

Fig. 10. Pivot table wizard - step 3.

To construct a group operation on one of the list fields, you need to drag the name of the corresponding field into the “Data” area with the mouse. So, to get the total weight, you need to drag it into the “Data” area. The “Row”, “Column” and “Page” areas allow you to obtain, in addition to the total application of a group operation on all records in the list, partial group operations on records with the same values of the fields selected in the area. So, to get not only the total weight of all people, but also the weight of people in each group, you need to drag it into the “Row” area. In Fig. Figure 11 shows the result of the described dragging.

Fig. 11. Constructing a group operation using the “Weight” field

To remove any field from the design area, you just need to drag it with the mouse and drag it out of the given area.

By clicking on the “Next” button, you can go to the fourth step of the wizard. In the fourth step, you need to select the sheet in which the pivot table will be placed. Here you can choose to create a pivot table on a new sheet and complete the wizard. The resulting summary table is shown in Fig. 12.

Fig. 12. Pivot table obtained as a result of the wizard’s work

Use the button in the PivotTables panel to return to the PivotTable Wizard to edit the batch operation query. Let's change the query by adding the "Last Name" field to the data area. The summary table will take the form shown in Fig. 13.

Fig. 13. Pivot table with group operation for the Last Name field

Let's go back to the PivotTable Wizard and add the "Gender" field to the "Column" area and the "Age" field to the "Page" area. The resulting summary table is shown in Figure 14.

Fig. 14. Resulting pivot table

The “Page” area differs from the “Row” and “Column” areas in that it allows you to either perform a group operation on all field values at once, or on one selected value. In the example shown, the value "19" was selected.

Exercise

Convert the table shown below to an Excel list or lists. Based on the summary tables, determine:

1. Area of enterprises subject to reconstruction by region;

2.Number of enterprises subject to reconstruction by type of activity;

3. Total area and number of enterprises by area;

4.All of the above in one summary table.

List of enterprises subject to reconstruction
Airport
Household services
	Organization	Type of activity	Square
	LLC Success	Dry cleaning
	CJSC Udacha	Salon
	LLC Player	Dry cleaning
Trade
	Fialka LLC	Flowers
	CJSC Bublik	Bread
	Iris LLC	Flowers
Falcon
Household services
	LLC Boot	Shoe repair
	Volos LLC	Salon
Trade
	LLC Moroz	Household appliances
	JSC Kalach	Bread
	LLC Roman	Books
	JSC Herring	Fish	Good afternoon, dear reader! In this article, I would like to talk about what a drop-down list in a cell is, how to make it, and, accordingly, what is it for? This is a list of fixed values that are only available from a specified range of values. This means that the cell you specify can only contain data that corresponds to the values of the specified range; data that does not correspond will not be entered. In a cell, you can select the values that a fixed list in the cell offers. Well, let's look at creating drop-down lists and why it is needed: I personally use the dropdown list all the time for all 3 reasons. And it greatly simplifies my work with data; I deliberately reduce the possibility of entering primary data to 0%. Well, here are 2 questions, what and why, I told you, but we’ll talk about how to do this below. And we will create a drop-down list in a cell in several stages: 1. Determine the range of cells in which we will create a fixed list. 2. Select the range we need and select the item in the menu “Data” - “Data check”, in the context window that appears, select the item from the specified selection "List". 3. In the line unlocked below, indicate the range of data that should be in our drop-down list. Click "OK" and the job is done. In older versions of Excel, there is no way to create a drop-down list in a cell using data from other sheets, so it makes sense to create lists in the same sheet and hide them. Also, if necessary, you can create a vertical list - a horizontal one using the feature. And that's all for me! I really hope that all of the above is clear to you. I would be very grateful for your comments, as this is an indicator of readability and inspires me to write new articles! Share what you read with your friends and like it! The progress of mankind is based on the desire of every person to live beyond his means Samuel Butler, philosopher When filling out a table in Excel, the data is often repeated. For example, you write the name of the product or the full name of the employee. Today we will talk about how to make a drop-down list in Excel so that you don’t constantly enter the same thing, but simply select the desired value. There is the easiest way to cope with the task. You right-click on the cell under the data column to open the context menu. Here we look for the “Select from drop-down list” item. The same action can be easily performed using the combination Alt + down arrow. That's just this method will not work if you need to make such an object in Excel in another cell and in 2-3, etc. before and after. If necessary, use the following option. Traditional way Select the area of the cells themselves from which you will create a drop-down list, then proceed: Insert/Name/Assign (Excel 2003) In the latest versions (2007, 2010, 2013, 2016) go to: Formulas/Defined Names/Name Manager/New Enter any name and tap OK. Then select the cells (or several) where you plan to insert the list of specified fields. Go to the menu: Data/Data Type/List In the “Source” section, enter the previously written name, simply mark the range. You can copy the resulting cell to any place; it will already contain a menu of specified table elements. Additionally, you can stretch it to create a range. By the way, if the information in it changes, the list information will also change; it is dynamic. How to make a drop-down list in Excel: using management methods When you use this option, you insert a control that represents the data range itself. To do this: firstly, look for the “Developer” section (Excel 2007/2010), in other editions you activate it yourself through the “Customize Ribbon” parameters; secondly, go to the section, click “Insert”; thirdly, select “Field...” and click on the icon; draw a rectangle; right-click on it and click Format Object; look for “Form…”, select the required boundaries; mark the area where you want to install serial number element in the list; Click OK. How to create a drop-down menu in Excel: using ActiveX elements The steps are similar to those described above, but we are looking for “Field with ActiveX”. The main differences here are that the ActiveX special element can be in 2 variants - debugging mode, which allows you to change parameters, and input mode, which only allows you to select information from it. You can change the mode using the key Design Mode in section Developer. Using this method, you can customize the color, font, and perform quick searches. Additional features Excel allows you to create a linked drop-down list. That is, when you select a value, you can select the parameters it needs in another column. For example, you choose a product, and you need to mark the unit of measurement, for example, gram, kilogram. First of all, you should make a sign with the lists themselves, and then separate windows with the names of the products. For the second, we activate the information check window, but in the “Source” item we write “=INDIRECT” and the address of the 1st cell. Everything worked out. After this, so that the windows below are supplemented with the same properties, select the upper section and drag everything down with the mouse pressed. Ready. How to make a drop-down list in Excel? As you can see, this is easy to do using any of the above methods. Just choose the most optimal one for yourself. And the choice will depend on the purpose of creation, purpose, area of use, amount of information and other things. Users who quite often work in Excel and maintain their databases in this program probably often need to select a cell value from a predetermined list. For example, we have a list of product names, and our task is to fill each cell of a certain column of the table using this list. To do this, you need to create a list of all items, and then implement the ability to select them in the required cells. This solution will eliminate the need to write (copy) the same name manually many times, and will also save you from typos and other possible errors, especially when it comes to large tables. You can implement the so-called drop-down list using several methods, which we will consider below. The simplest and most understandable method is to first create a list elsewhere in the document. You can place it next to the table, or create a new sheet and make a list there, so as not to “clutter” the original document with unnecessary elements and data. In the auxiliary table we write a list of all names - each on a new line in a separate cell. The result should be one column with filled data. Then we mark all these cells, right-click anywhere in the marked range and in the list that opens, click on the “Assign a name..” function. The “Create a name” window will appear on the screen. We name the list whatever we want, but with the condition that the first character must be a letter, and the use of certain characters is not allowed. It also allows you to add a note to the list in the corresponding text field. When ready, click OK. Switch to the “Data” tab in the main program window. We mark the group of cells for which we want to set a selection from our list and click on the “Data Validation” icon in the “Working with Data” subsection. The “Checking entered values” window will appear on the screen. While in the “Parameters” tab, in the data type, select the “List” option. In the “Source” text field, write the equal sign (“=”) and the name of the newly created list. In our case – “=Name”. Click OK. Everything is ready. To the right of each cell of the selected range, a small icon with a down arrow will appear, by clicking on which you can open the list of items that we have compiled in advance. By clicking on the desired option from the list, it will immediately be inserted into the cell. In addition, the value in a cell can now only correspond to the name from the list, which will eliminate any possible typos. You can create a drop-down list in another way - through developer tools using ActiveX technology. The method is somewhat more complicated than described above, but it offers a wider range of tools for customizing the list: you can set the number of elements, size and appearance the list box itself, the need to match a value in a cell with one of the list values, and much more. First of all, these tools need to be found and activated, since they are turned off by default. Go to the “File” menu. In the list on the left, find the “Options” item at the very bottom and click on it. Go to the “Customize Ribbon” section and in the “Main Tabs” area, check the box next to “Developer”. Developer tools will be added to the program ribbon. Click OK to save the settings. Now the program has new tab called “Developer”. We will work through it. First, we create a column with elements that will be the sources of values for our drop-down list. Switch to the “Developer” tab. In the “Controls” subsection, click on the “Insert” button. In the list that opens, in the “ActiveX Controls” function block, click on the “Combo Box” icon. Next, click on the desired cell, after which a list box will appear. We adjust its dimensions along the cell boundaries. If the list is selected with the mouse, “Design Mode” will be active on the toolbar. Click on the “Properties” button to continue setting up the list. In the parameters that open, find the line “ListFillRange”. In the column next to us, separated by a colon, we write the coordinates of the range of cells that make up our previously created list. Close the window with parameters by clicking on the cross. Then right-click on the list window, then click on the “ComboBox Object” item and select “Edit”. As a result, we get a drop-down list with a predefined list. To insert it into several cells, move the cursor over the lower right corner of the cell with the list, and as soon as it changes its appearance to a cross, hold down the left mouse button and drag down to the very bottom line in which we need a similar list. Users also have the ability to create more complex interdependent (linked) lists. This means that the list in one cell will depend on what value we have selected in another. For example, we can set kilograms or liters in product units. If you select kefir in the first cell, in the second you will be offered two options to choose from - liters or milliliters. And if in the first cell we choose apples, in the second we will have a choice of kilograms or grams. To do this, you need to prepare at least three columns. The first will contain the names of the goods, and the second and third will contain their possible units of measurement. There may be more columns with possible variations of units of measurement. First, we create one general list for all product names, selecting all the rows of the “Name” column, through context menu allocated range. Give it a name, for example, “Food”. Then, in the same way, we create separate lists for each product with the corresponding units of measurement. For greater clarity, let’s take as an example the first position – “Bow”. We mark the cells containing all units of measurement for this product and, through the context menu, assign a name that must completely match the name. In the same way, we create separate lists for all other products in our list. After this, we insert a general list of products into the top cell of the first column of the main table - as in the example described above, through the “Data Check” button (the “Data” tab). We indicate “=Nutrition” as the source (according to our name). Then click on the top cell of the column with units of measurement, also go to the data verification window and indicate the formula in the source “ =INDIRECT(A2)“, where A2 is the number of the cell with the corresponding product. The lists are ready. All that remains is to stretch them all the rows of the table, both for column A and column B. Hello everyone, dear friends and guests of my blog. And again I am with you, Dmitry Kostin, and today I would like to tell you more about Excel, or rather about one wonderful feature that I now always use. Have you encountered the situation? when you fill out a table and in some column you need to constantly enter one of several values. Uhhh. Let me tell you better with an example. Let’s say, when I created a computer equipment accounting table (a long time ago) at my work, in order to make the whole work process more convenient and faster, I made a drop-down list in certain columns and inserted certain values there. And when I filled out the column " operating system“(But it’s not the same on all computers), then I entered several values (7, 8, 8.1, 10), and then simply selected it all with one click of the mouse button. And thus, you no longer need to type the version of Windows into each cell, or copy from one cell and paste into another. In general, I won’t bore you, let’s get started. Let me show you how to create a dropdown list in excel using data from another sheet. To do this, let's create some kind of table to which we can apply this. I will do this in the 2013 version, but the process is identical for other versions, so don't worry. Preparation Basic steps Now work with graphs in exactly the same way "Name of specialist" And "Result of elimination", then return to the main sheet again and start working fully with the table. You will see for yourself how cool and convenient it is when you can select data from available pre-prepared values. This makes routine filling of tables easier. By the way, in such documents, for more convenient display, it is better. Then everything will be cool. Well, I’m finishing my article for today. I hope that what you learned today will be useful to you when working in Excel. If you liked the article, then of course do not forget to subscribe to my blog updates. Well, I’ll be looking forward to seeing you again on the pages of my blog. Good luck and bye-bye! Best regards, Dmitry Kostin Related articles Review of the free version of AdwCleaner AdwCleaner: what is it How to turn your Android device into a wireless gamepad Wireless router Asus RT-N11P How to connect a new router asus rt n11p Navigation Banks Beeline Money Megaphone Motive MTS Telecom operators Tele2 Yandex Money Latest Posts Fly Tornado Slim review: the thinnest smartphone in the world has arrived in Russia Fly Tornado Slim Tuner for tuning guitar Guitar Tuner from MuzLand 3 string processing functions Updating Yandex browser to the latest version Animation layers. What is animation About money and financial literacy Advertising on the site Contacts Site map × Close Banks Beeline Money Megaphone Motive MTS Telecom operators Tele2 Yandex Money