How to enter the same data in several selected cells at the same time

In this short tutorial, we will show you how to enter the same formula or data into several different Excel fields at the same time. This method is useful for formulas to all cells of the same column or to enter the same data in several different cells at the same time. This technique works in versions of Excel from 2016 to 2007 and earlier.

Selecting all fields for data substitution

Here are a few tricks that will help you easily highlight the fields of interest to us in an Excel file.

How to select an entire column

If the information is presented in the form full table, select any field in the column and press Ctrl+Space. If you are working with data range (i.e., if clicking on any cell of the file in the right part of the toolbar does not display the inscription “Working with tables”), proceed in accordance with the algorithm presented below.

It’s important! Unfortunately, Ctrl+Space highlights all cells in a column, regardless of content. That is, the combination will select all fields from C1 up to C100000, even when there is information only in the first hundred.

Move the cursor to the first cell in the column (or the next one if the table contains headings) and use the Shift+Ctrl+End keys to move to the end of the table. While holding Shift, press the left arrow until you select the appropriate column.

This is the most convenient method for selecting all cells in a column, especially if it contains several randomly spaced empty cells.

How to select the entire line

For full-format tables just select any field in the row and use the combination Shift+Space. In case you are working with data range, select the last cell in the row and press Ctrl+Home.

Selecting multiple cells

Hold Ctrl and select cells by clicking LMB.

Selecting an entire table

Use the combination Ctrl + A, and then press it again.

How to select empty fields in a specific area (row, column or table)

Select the desired area (as in the screenshot below). For example, the entire column:

Press F5 to bring up the window “Transition” and click on the button “Highlight».

In the window that opens, select the item “Empty Cells” and click OK.

After that, there will be a transition back to the table, in which all empty cells in the selected range will be selected. Of course, if there are only 3 empty cells, then it is easier to select them manually. But if 10000 empty fields are scattered across the table for 500 cells, then the method described above will allow you to find them much faster.

Insert a formula into a column

Let’s say you have a large table in your hands, and you need to add an additional column with a formula to it. For example, the table is a list of links and you need to extract the domain names from those links.

1. The first step is to convert the data range into a full table. To do this, select any cell within the range and use the Ctrl+T keys to bring up the “Creating a table” (for Excel 2003, the keyboard shortcut will be Ctrl+L). If the table contains headers, don’t forget to check the “Table with headers“. In most cases, Excel recognizes the headings on its own, but if for some reason this does not happen, be sure to do it manually.

2. Insert a new column in the table. It is much easier and more convenient to add columns to tables than to simple data ranges. To do this, simply right-click on any cell in the column next to which you want to add a new one, select “Insert“, and then – “Table column on the right” (or left, depending on the situation).

3. Name the new column.
4. Enter the formula in the 1st cell of the column. As an example, let’s use the formula for copying domain names from links:

=ПСТР(С2,НАЙТИ(«:»,С2,»4″)+3,НАЙТИ(«/»,С2,9)-НАЙТИ(«:»,С2,»4″)-3)

5. After inserting the formula, press Enter. Ready! Excel will fill in the empty fields in the column according to the given formula.

If for some reason you need to convert the full table back to a data range, select any cell of your choice, select the “Work with tables” on the right side of the toolbar and click “Convert to range».

This method only works if all the cells in the column have no content, so it’s great for new columns. The next trick is more general..

Paste information into multiple cells with Ctrl+Enter

Select the desired cells to fill in the Excel table (we already talked about how to quickly and conveniently select cells above).

Let’s say we are talking about a table with customer data. In this hypothetical table, there is a column with the addresses of the sites where these clients came from. And for the convenience of searching and filtering, you need to fill in the empty fields with the value «_unknown_».

1. Find all empty fields in a column.

2. Press F2 to edit the last selected cell and enter data into it. It can be text, a number, or a formula (for example, _unknown_).

3. Use the combination Ctrl+Enter. All selected fields will be filled with the data entered in the previous step.