Excel's validation capability is great, but it doesn't do one important thing (without the following trick): automatically add a new entry to the list that is used as the source for the validation list.
If you've worked with validation before, you know that this is a very clever feature. Perhaps most impressive is its ability to add a list to any cell where the user can select values. It would be great if when you enter a new name in a validated cell, Excel automatically adds it to the list. This is possible thanks to the following trick.
Let there be a list of names in the range A1:A10 (Fig. 2.24).
These names represent the employees of the company. It's not uncommon for new employees to be added to a list like this, but right now the only way to do this is to add new names at the end of the list and then select them from the list in the checked cell. To work around this limitation, do the following. In cell A11, enter the following formula and copy it down to line 20 (Fig. 2.25) - note the relative reference to cell A10: =IF(OR($D$1="";COUNTIF($A$1:A10;$D $l));"x";$D$l) $D$1) .
Now select the command Formulas> Assign a Name (Formulas> Define Name) and in the Name field (Names in workbook) enter MyNames. In the Formula field (Refers to), enter the following formula (Figure 2.26), click the Add button, then click the OK button: =OFFSET(Sheet1!$A$1;0;0;COUNTIF(Sheet1!$A:$A ;"x");1) , in the Russian version of Excel: =CMESCH(Sheet1!$A$1;0;0;COUNTIF(Sheet1!$A:$A;"x");1) .
Select cell D1 and choose Data > Validation. In the Data type (Allow) field, select List (List), and in the Source (Source) field, enter =MyNames; make sure the List of Valid Values (In-Cell) check box is selected. Click the Error Alert tab and clear the Show error alert after invalid data is entered check box. Click the OK button. You will see the result as in Fig. 2.27.
Right-click the sheet tab and select View Code from the context menu. Enter the code shown in listing 2.5.
| 1 2 3 4 5 6 7 8 | // Listing 2.5 Private Sub Worksheet_Calculate() On Error Resume Next Application. EnableEvents = False Range("MyNames" ) = Range("MyNames" ) . value application. EnableEvents = True On Error GoTo 0 End Sub |
// Listing 2.5 Private Sub Worksheet_Calculate() On Error Resume Next Application.EnableEvents = False Range("MyNames") = Range("MyNames").Value Application.EnableEvents = True On Error GoTo 0 End Sub
To return back to Excel and save the workbook, close the window. Now select cell D1, type any name not in the list, and press the Enter key. Select cell D1 again and take a look at the list. Now the list should contain a new name (Fig. 2.28).
If you want to add more than 10 new names to the list, copy the formula below line 20.
A drop-down list in Excel is perhaps one of the most convenient ways to work with data. You can use them both when filling out forms and creating dashboards and large tables. Drop-down lists are often used in applications on smartphones, websites. They are intuitive to the average user.
Click on the button below to download the dropdown list examples file in Excel:
Video tutorial
How to create a dropdown list in excel based on the data from the list
Imagine we have a list of fruits:
To create a dropdown list, we need to do the following steps:
- Go to tab “ Data” => section “ Working with data ” on the toolbar => select the item “ Data validation “.
- In the pop-up window Validation of input values ” on the tab “ Parameters” in the data type select “ List “:
- In field " A source” enter a range of fruit names =$A$2:$A$6 or just put the mouse cursor in the value input field “ A source” and then select the data range with the mouse:
If you want to create dropdown lists in multiple cells at once, then select all the cells in which you want to create them, and then follow the steps above. It's important to make sure cell references are absolute (for example, $A$2), rather than relative (for example, A2 or A$2 or $A2 ).
How to make a dropdown list in Excel using manual data entry
In the example above, we entered the list of data for the drop down list by selecting a range of cells. In addition to this method, you can enter data to create a drop-down list manually (it is not necessary to store them in any cells).
For example, let's say we want to display two words "Yes" and "No" in a drop-down menu. For this we need:
- Select the cell in which we want to create a dropdown list;
- Go to tab “ Data” => section “ Working with data ” on the toolbar => select the item “ Data validation “:
- In the pop-up window Validation of input values ” on the tab “ Parameters” in the data type select “ List “:
- In field " A source” enter the value “Yes; No".
- Press " OK “
After that, the system will create a drop-down list in the selected cell. All items listed in the field “ A source“ separated by a semicolon will be reflected in different lines of the dropdown menu.
If you want to create a drop-down list in several cells at the same time, select the required cells and follow the instructions above.
How to create a dropdown list in Excel using the OFFSET function
Along with the methods described above, you can also use a formula to create dropdown lists.
For example, we have a list with a list of fruits:
In order to make a dropdown list using a formula, you need to do the following:
- Select the cell in which we want to create a dropdown list;
- Go to tab “ Data” => section “ Working with data ” on the toolbar => select the item “ Data validation “:
- In the pop-up window Validation of input values ” on the tab “ Parameters” in the data type select “ List “:
- In field " A source” enter the formula: =OFFSET(A$2$;0;0;5)
- Press " OK “
The system will create a drop-down list with a list of fruits.
How does this formula work?
In the example above, we used the formula =OFFSET(reference,line_offset,column_offset,[height],[width]).
This function contains five arguments. In the argument " link” (in the example $A$2) indicates which cell to start the offset from. In arguments “line_offset" and “offset_by_columns”(the value “0” is indicated in the example) – how many rows/columns should be shifted to display the data. In the argument " [height]” is set to “5”, which indicates the height of the range of cells. Argument " [width]” we do not specify, since in our example the range consists of one column.
Using this formula, the system returns to you as data for the dropdown list a range of cells starting at cell $A$2, consisting of 5 cells.
How to make a dropdown list in Excel with data substitution (using the OFFSET function)
If you use the formula in the example above to create a list, then you create a list of data that is fixed in a certain range of cells. If you want to add any value as an element of the list, you will have to adjust the formula manually. Below you will learn how to make a dynamic dropdown list that will automatically load new data for display.
To create a list you will need:
- Select the cell in which we want to create a dropdown list;
- Go to tab “ Data” => section “ Working with data ” on the toolbar => select the item “ Data validation “;
- In the pop-up window Validation of input values ” on the tab “ Parameters” in the data type select “ List “;
- In field " A source” enter the formula: =OFFSET(A$2$,0,0,COUNTIF($A$2:$A$100;”<>”))
- Press " OK “
In this formula, in the argument “[ height]” we specify as an argument denoting the height of the list with data - a formula that calculates in a given range A2:A100 the number of non-empty cells.
Note: For the formula to work correctly, it is important that there are no empty lines in the list of data to be displayed in the drop-down menu.
How to create a dropdown list in Excel with automatic data substitution
In order for new data to be automatically loaded into the drop-down list you created, you need to do the following:
- We create a list of data to display in the dropdown list. In our case, this is a list of colors. Select the list with the left mouse button:
- On the toolbar, click " Format as a table “:
- From the drop-down menu, select the table style:
- By pressing the key " OK” in the pop-up window, confirm the selected range of cells:
- Then, select the range of table data for the drop-down list and give it a name in the left margin above column “A”:
The data table is ready, now we can create a drop-down list. For this you need:
- Select the cell in which we want to create a list;
- Go to tab “ Data” => section “ Working with data ” on the toolbar => select the item “ Data validation “:
- In the pop-up window Validation of input values ” on the tab “ Parameters” in the data type select “ List “:
- In the source field, enter =”name of your table” . In our case, we named it “ List “:
- Ready! The drop-down list has been created, it displays all the data from the specified table:
- In order to add a new value to the drop-down list, simply add information to the next cell after the data table:
- The table will automatically expand its data range. The drop-down list will be updated accordingly with the new value from the table:
How to copy a dropdown list in Excel
In Excel, it is possible to copy the created drop-down lists. For example, in cell A1 we have a drop down list that we want to copy to a range of cells A2:A6 .
To copy the dropdown list with the current formatting:
- click the left mouse button on the cell with the drop-down list that you want to copy;
- CTRL+C ;
- select cells in a range A2:A6 where you want to insert the dropdown list;
- press the keyboard shortcut CTRL+V .
So, you will copy the drop-down list, keeping the original list format (color, font, etc.). If you want to copy/paste the dropdown list without saving the format, then:
- click the left mouse button on the cell with the drop-down list that you want to copy;
- press the keyboard shortcut CTRL+C ;
- select the cell where you want to insert the drop down list;
- press the right mouse button => bring up the drop-down menu and click “ Special insert “;
- In the window that appears, under " Insert” select item “ conditions on values “:
- Click " OK “
After that, Excel will copy only the data of the drop-down list, without preserving the formatting of the original cell.
How to select all cells containing a drop down list in excel
Sometimes, it is difficult to understand how many cells in an Excel file contain drop-down lists. There is an easy way to display them. For this:
- Click on the tab “ home” on the Toolbar;
- Click " Find and select ” and select “ Select a group of cells “:
- In the dialog box, select " Data validation “. In this field, it is possible to select the items “ All" and " the same “. “All” will select all drop-down lists on the sheet. Paragraph " the same” will show drop-down lists similar in content to the data in the drop-down menu. In our case, we choose " all “:
In this article, we will show you some more useful options that the tool is rich in. Special insert, namely: Values, Formats, Column Widths and Multiply/Divide. With these tools, you can customize your spreadsheets and save time formatting and reformatting data.
If you want to learn how to transpose, remove links, and skip blank cells with the Paste Special(Paste Special) refer to the article Paste Special in Excel: skip blank cells, transpose and remove references.
Paste only values
Take, for example, a spreadsheet showing profits from sales of cookies at a bakery charity sale. You want to calculate how much profit was made in 15 weeks. As you can see, we used a formula that adds up the amount of sales that were made a week ago and the profit made this week. See in the formula bar =D2+C3? Cell D3 shows the result of this formula − $100 . In other words, in a cell D3 value is displayed. And now it will be the most interesting! In Excel using the tool Paste Special(Paste Special) You can copy and paste the value of this cell without formula or formatting. This feature is sometimes vital, and I'll show you why later.
Let's say that after you've been selling cookies for 15 weeks, you need to submit a general report on the results of the profits received. You may want to just copy and paste the line that contains the grand total. But what happens if you do this?
Oops! Is this not at all what you expected? As you can see, the usual action copy and paste as a result copied only the formula from the cell? You need to copy and paste the value itself. So we will do it! We use the command Paste Special(Paste Special) with option values(Values) so that everything is done right.
Notice the difference in the image below.
Applying Special insert > Values, we are inserting the values themselves, not the formulas. Great job!
Perhaps you have noticed something else. When we used the command Paste Special(Special Paste) > values(Values), we've lost the formatting. See how the bold and number format (dollar signs) weren't copied over? You can use this command to quickly remove formatting. Hyperlinks, fonts, number format can be quickly and easily cleaned up and you are left with just the values without any decorations that might get in the way in the future. It's great, right?
Actually, Special insert > Values is one of my favorite tools in Excel. It is vital! I am often asked to create a spreadsheet and present it at work or community organizations. I'm always worried that other users might mess up the formulas I've entered. After I'm done with formulas and calculations, I copy all my data and use Paste Special(Special Paste) > values(Values) on top of them. Thus, when other users open my spreadsheet, the formulas can no longer be changed. It looks like this:
Pay attention to the contents of the formula bar for the cell D3. It no longer has a formula =D2+C3, instead the value is written there 100 .
And one more very useful thing with regards to the special paste. Let's say I want to keep only the bottom line in the cookie giveaway profit spreadsheet, i.e. delete all lines except week 15. See what happens if I just delete all these lines:
This annoying error appears #ref!(#REF!). It appeared because the value in this cell is calculated by a formula that refers to the cells above. After we removed those cells, the formula had nothing to refer to and reported an error. Use commands instead Copy(copy) and Paste Special(Special Paste) > values(Values) on top of the original data (as we already did above), and then remove the extra lines. Great job:
Paste Special > Values: Highlights
- Highlight data
- Copy them. If the data is not copied, but cut, then the command Paste Special(Paste Special) will not be available, so be sure to use copy.
- Select the cell where you want to paste the copied data.
- Click Paste Special(Special insert). It can be done:
- by right-clicking and selecting from the context menu Paste Special(Special insert).
- tab Home(Home), press the small triangle under the button paste(Insert) and from the drop-down menu select Paste Special(Special insert).
- Check option values(Data).
- Click OK.
Insert only formats
Special insert > Formats this is another very useful tool in excel. I like it because it allows you to easily customize the appearance of the data. There are many uses for the tool Special insert > Formats but I will show you the most remarkable. I think you already know that Excel is great for working with numbers and for performing various calculations, but it also does a great job when you need to present information. In addition to creating tables and counting values, you can do all sorts of things in Excel, such as schedules, calendars, labels, inventory cards, and so on. Take a closer look at the templates that Excel offers when creating a new document:
I came across a pattern Winter 2010 schedule and I liked the formatting, font, color and design in it.
I don't need the schedule itself, much less for winter 2010, I just want to remake the template for my purposes. What would you do in my place? You can create a draft table and manually repeat the template design in it, but this will take a very long time. Or you can delete all the text in the template, but that will also take a lot of time. It's much easier to copy the template and do Paste Special(Special Paste) > Formats(Formats) on a new sheet of your workbook. Voila!
Now you can enter data, keeping all formats, fonts, colors and designs.
Paste Special > Formats Highlights
- Highlight the data.
- Copy them.
- Select the cell where you want to paste the data.
- Click Paste Special(Special insert).
- Check option Formats(Formats).
- Click OK.
Copying column widths to another sheet
Have you ever wasted a lot of time and energy circling around your table and trying to adjust column sizes? My answer is yes, of course! Especially when you need to copy and paste data from one table to another. Existing column width settings may not work, and even though automatic column width adjustment is a handy tool, in places it may not work as you would like. Special insert > Column Widths is a powerful tool that should be used by those who know exactly what they want. Let's take a look at the list of the best US MBA programs as an example.
How could this happen? You can see how neatly the column width has been adjusted to fit the data in the figure above. I copied the top ten business schools and put them on another sheet. See what happens when we just copy and paste the data:
The content is pasted, but the column widths are far from appropriate. You want exactly the same column widths as the original worksheet. Instead of configuring it manually or using autofit column width, just copy and paste Paste Special(Special Paste) > Column Widths(Column Widths) to the area where you want to adjust the column widths.
See how simple it is? Although this is a very simple example, you can already imagine how useful such a tool would be if an Excel sheet contains hundreds of columns.
In addition, you can adjust the width of empty cells to format them before manually entering text. Look at the columns E and F in the picture above. In the picture below, I used the tool Special insert > Column Widths to expand columns. So, without too much fuss, you can design your Excel sheet the way you want!
Paste Special > Column Width Highlights
- Highlight the data.
- Copy the selected data.
- Place the cursor on the cell whose width you want to adjust.
- Click Paste Special(Special insert).
- Check option Column Widths(column widths).
- Click OK.
Paste Special: Divide and Multiply
Remember our cookie example? Good news! A giant corporation found out about our charity event and offered to increase our profits. After five weeks of sales, they will invest in our charity, so that the income will double (become twice as much) compared to what it was at the beginning. Let's go back to the spreadsheet we used to record the profits from the cookie charity sale and recalculate the profits to account for the new investment. I've added a bar showing that after five weeks of sales, profits will double, i.e. will be multiplied by 2 .
All income starting from the 6th week will be multiplied by 2 .To show the new numbers, we need to multiply the corresponding cells of the column C on 2 . We can do this manually, but it will be much nicer if Excel does it for us using the command Paste Special(Special Paste) > Multiply(Multiply). To do this, copy the cell F7 and apply command on cells C7:C16. The totals have been updated. Great job!
As you can see, the tool Special insert > Multiply can be used in a variety of situations. The same is true with Paste Special(Special Paste) > Divide(Divide). You can divide an entire range of cells by a specific number quickly and easily. Do you know what else? With help Paste Special(Special Paste) with option Add(Add) or Subtract(Subtract) You can quickly add or subtract a number.
Paste Special > Divide/Multiply: Highlights
- Select the cell containing the number you want to multiply or divide by.
- Copy the selected data.
- Place the cursor on the cells where you want to multiply or divide.
- Click Paste Special(Special insert).
- Check option Divide(Split) or Multiply(Multiply).
- Click OK.
So, in this tutorial, you have learned some very useful features of the tool. Special insert, namely: they learned to insert only values or formatting, copy the width of columns, multiply and divide data by a given number, and also add and remove a value immediately from a range of cells.
Paste or Ctrl+V is perhaps the most efficient tool available to us. But how well do you own it? Did you know that there are at least 14 different ways to insert data into Excel sheets? Surprised? Then read this post to become a paste master.
This post has 2 parts:
- Basic pasting techniques
- Insertion via data processing
1. Insert values
If you just want to paste values from cells, press the Z, M, and Z keys in sequence while holding down the Alt key, and finally press the enter key. This is necessary when you need to get rid of formatting and work only with data.
Starting with Excel 2010, the Paste Values feature appears in the pop-up menu when you right-click
2. Insert formats
14. What's your favorite pasting method?
There are many more hidden ways to insert, such as inserting XML data, images, objects, files, and so on. But I'm wondering what interesting insertion techniques you use. What is your favorite insertion method?
Everyone knows the ability to copy and paste data through the operating system's Clipboard.
Hot key combinations:
Ctrl+C - copy
Ctrl+X - cut
Ctrl+V - Paste
The Paste Special command is a universal variant of the Paste command.
Paste Special allows you to separately paste the attributes of the copied ranges. In particular, you can paste only comments, only formats, or only formulas from the copied range into a new worksheet location.
To make this command available, you must:
As a result, the Paste Special dialog box will appear on the screen, which will be different depending on the source of the copied data.
1. Inserting information from Excel
If a range of cells was copied in the same application, then the Paste Special window will look like this:
Switch group Insert:
All - Selecting this option is equivalent to using the Paste command. This copies the contents of the cell and the format.
Formulas - selecting this option allows you to insert only formulas as they were entered in the formula bar.
Values - selecting this option allows you to copy the results of calculations using formulas.
Formats - when using this option, only the format of the copied cell will be pasted into the cell or range.
Notes - if you need to copy only the notes to a cell or range, then you can use this option. This option does not copy the contents of the cell or its formatting attributes.
Value conditions - if a data validity criterion was created for a specific cell (using the Data | Check command), then this criterion can be copied to another cell or range using this option.
No border - often there is a need to copy a cell without a border. For example, if you have a table with a border, then copying the border cell will also copy the border. To avoid copying the frame, you can select this option.
Column Widths - You can copy column width information from one column to another.
Advice! This function is convenient to use when copying a finished table from one sheet to another.
Often, after pasting a copied table onto a new sheet, you have to adjust its size.
Sheet 1 Source table
Sheet 2 Insert
To avoid this, use the Column Widths insert. For this:
- Copy the original table.
- Go to a new sheet and select the cell to insert.
- Open the Paste Special dialog, check the Column Widths option, and click OK.
As a result, you will get an exact copy of the original table on a new sheet.
The Ignore Empty Cells option prevents the program from erasing the contents of the cells in the paste area, which can happen if there are empty cells in the copied range.
The transpose option reverses the orientation of the copied range. Rows become columns and columns become rows. You can read more about this option in the Transpose Excel Chip.
Switches from the Operation group allow you to perform arithmetic operations.
Let's look at examples of performing mathematical operations using the Paste Special dialog box.
Task 1. Add 5 to each value in cells A3:A12
- We enter the value "5" in any cell (in this example - C1).
- Copy the value of cell C1.
- Select the range A3:A12 and open the Paste Special dialog box
4. Select the Add operation and click OK.
As a result, all values in the selected range will be increased by 5.
Task 2 . Reduce by 10% the prices of goods in the E4:E10 range (without using formulas).
- Taking into account the discount, the new price will be 90% of the previous one, therefore, we enter the value 90% in any of the cells (in this example, F2).
- Copy the contents of cell F2 to the clipboard.
- Select the range E4:E10 and open the Paste Special dialog box
