Where is autocomplete located in excel. Copying data in adjacent cells

On one of the sheets workbook Excel, contains a database of information about registration data of company cars. The second sheet contains the delegation register, where personal data of employees and vehicles is entered. One of the cars is used repeatedly by employees and each time they enter data into the register - this requires extra time for the operator. It's better to automate this process. To do this, you need to create a formula that will automatically pull up information about a company car from the database.

Autofill cells with data in Excel

To make the example clearer, let’s schematically display the registration database:

As described above, the register is located on a separate Excel sheet and looks like this:

Here we implement autocomplete Excel tables. So notice that the column header names in both tables are the same, just shuffled in a different order!

Now let's look at what needs to be done so that after entering the registration number into the register as a value for the cell in column A, the remaining columns are automatically filled with the corresponding values.

How to autofill cells in Excel:

1. On the “Register” sheet, enter any registration number from column E on the Database sheet.
2. Now in cell B2 on the “Register” sheet, enter the cell autofill formula in Excel:
3. Copy this formula to all other cells in the second row for columns C, D, E on the Register sheet.

As a result, the table was automatically filled with the corresponding cell values.



The principle of operation of the formula for autofilling cells

The main role in this formula is played by the INDEX function. Its first argument specifies the source table located in the car database. The second argument is the line number, which is calculated using the MATCH function. This function performs a search in the range E2:E9 (in this case vertically) in order to determine the position (in this case row number) in the table on the “Database” sheet for a cell that contains the same value that was entered on the “Register” sheet in A2 .

The third argument to the INDEX function is the column number. It is also calculated by the MATCH formula with its other arguments. The MATCH function should now return the table column number from the Database sheet that contains the header name corresponding to the original column title in the Register sheet. It is indicated by a reference in the first argument of the MATCH function - B$1. Therefore, this time the value is searched only in the first row A$1:E$1 (this time horizontally) of the car registration database. The position number of the original value (this time the column number of the original table) is determined and returned as the column number for the third argument to the INDEX function.

This ensures that the formula will work even if the order of the columns is shuffled in the register and database tables. Naturally, the formula will not work if the column names in both tables do not match, for obvious reasons.

Autofill forms is a feature that allows you to automatically enter forms on websites personal information, for example your name, address, email or telephone. Default in beta versions of Yandex.Browser autocomplete included.

Autofill is convenient, but in order to insert your data into forms, the browser needs to store it on your computer. If malware gets onto your computer, an attacker can steal your data. And if during your absence a stranger opens a browser, he will be able to log in to sites under your name or find out your data in another way. Therefore, if you value the security of your data more than convenience, you may want to disable autofill.

1. Remove autofill data
2. Disable form autofill

Save autofill data automatically

Attention. If you want to prevent autofill data from a specific site from being saved in your browser, use Incognito mode. If necessary, you can disable form autofill in your browser.

The first time you enter information into a form (such as an address or phone number), the browser remembers it. At re-entering a list appears in the field from which you can select a previously saved autofill option.

Set autofill data manually

Remove autofill data

Disable form autofill

Autocomplete search query

Requests in the browser's Smart Line

By default, the browser saves your request history. When you enter them again, they are highlighted with a dot to the right.

","hasTopCallout":true,"hasBottomCallout":false,"areas":[("shape":"circle","direction":["top","right"],"alt":"Saved search query ","coords":,"isNumeric":false,"hasTopCallout":true,"hasBottomCallout":false)]))\">

To delete searches saved in your browser:

Inquiries to search bar Yandex

If you are authorized in Yandex, then your requests are saved by default - when you enter them again, they will be highlighted in purple. In addition, sites you visit frequently appear first in the list of search suggestions.

Excel is one of the best editors for working with tables today. This program has it all necessary functions for working with any amount of data. In addition, you will be able to automate almost every action and work much faster. In this article we will look at in what cases and how exactly you can use cell autofill in Microsoft Office Excel.

It is worth noting that such tools are not available in the program. Microsoft Word. Some people resort to trickery. Fill out the table the required values in Excel, and then transfer them to Word. You can do the same.

Setting up automatic numbering is very easy. To do this, just take a few very simple steps.

1. Dial some numbers. Moreover, they must be in the same column or one line. In addition, it is desirable that they go in ascending order (order plays an important role).

1. Highlight these numbers.
2. Hover over the bottom right corner of the last element and drag down.

1. The further you pull, the more new numbers you will see.

The same principle works with other values. For example, you can write several days of the week. You can use both abbreviated and full names.

1. Let's highlight our list.
2. Move the cursor until its marker changes.
3. Then pull down.

1. As a result, you will see the following.

This feature can also be used for static text. It works exactly the same.

1. Write a word on your sheet of paper.
2. Pull the lower right corner down a few lines.

1. You'll end up seeing a whole column of the same content.

In this way, you can make it easier to fill out various reports and forms (advance, KUDiR, PKO, TTN, and so on).

Ready-made lists in Excel

As you can see, you are not asked to download any free add-ons. All this works immediately after installation Microsoft programs Excel.

Creating your own lists

The examples described above are standard. That is, these enumerations are set in Excel by default. But sometimes there are situations when you need to use your own templates. It's very easy to create them. To set up, you need to perform a few very simple manipulations.

1. Go to the "File" menu.

1. Open the Settings section.

1. Click on the “Advanced” category. Click on the "Edit Lists" button.

1. After this, the “Lists” window will open. Here you can add or remove unnecessary items.

1. Add some items to the new list. You can write whatever you want - it's your choice. As an example, we will write a list of numbers in text form. To enter a new template, click on the “Add” button. After this, click on “OK”.

1. To save the changes, click “OK” again.

1. Let's write the first word from our list. You don't have to start from the first element - autocomplete works from any position.

1. Then we will duplicate this content several lines below (how to do this was written above).

1. As a result, we will see the following result.

Thanks to the capabilities of this tool, you can include anything in the list (both words and numbers).

Using progression

If you are too lazy to manually drag the contents of the cells, then it is best to use the automatic method. There is a special tool for this. It works as follows.

1. Select any cell with any value. We will use the cell with the number “9” as an example.
2. Go to the Home tab.
3. Select "Progression".

1. After this you can configure:
   • filling location (by rows or columns);
   • type of progression (in in this case choose arithmetic);
   • step of increment of new numbers (can be enabled or disabled automatic detection step);
   • maximum value.

1. As an example, in the “Limit value” column we indicate the number “15”.
2. To continue, click on the “OK” button.

1. The result will be as follows.

As you can see, if we had specified a limit greater than "15", then we would have overwritten the contents of the cell with the word "Nine". The only negative this method is that the values ​​may fall outside the boundaries of your table.

Specifying the Paste Range

If your progression has gone beyond acceptable values and at the same time overwritten other data, then you will have to cancel the result of the insertion. And repeat the procedure until you find the final progression number.

But there is another way. It works as follows.

1. Select the required range of cells. In this case, the first cell should contain the initial value for autofill.
2. Open the Home tab.
3. Click on the "Fill" icon.
4. Select "Progression".

1. Please note that the “Layout” setting is automatically set to “By Columns” because we selected the cells that way.
2. Click on the "OK" button.

1. As a result, you will see the following result. The progression is filled to the very end without leaving anything out of bounds.

Autocomplete date

You can work with date or time in a similar way. Let's follow a few simple steps.

1. Let's enter any date in some cell.
2. Select any arbitrary range of cells.
3. Let's open the "Home" tab.
4. Click on the “Fill” tool.
5. Select the “Progression” item.

1. In the window that appears, you will see that the “Date” type has been activated automatically. If this does not happen, then you entered the number in the wrong format.

1. To insert, click on “OK”.

1. The result will be as follows.

Autocomplete formulas

In addition to this, in Excel program You can also copy formulas. The operating principle is as follows.

1. Click on any empty cell.
2. Enter the following formula (you will need to adjust the address to the cell with the original value).

=C2*2

1. Press Enter key.

1. Then you will need to copy this expression to all other cells (how to do this was described a little higher).

1. The result will be as follows.

Differences between Excel versions

All the methods described above are used in modern versions Excel (2007, 2010, 2013 and 2016). In Excel 2003, the Progression tool is located in a different section of the menu. In all other respects, the operating principle is exactly the same.

In order to set up cell autofill using progression, you need to perform the following very simple operations.

1. Go to any cell with any numerical value.
2. Click on the "Edit" menu.
3. Select "Fill".
4. Then – “Progression”.

1. After this, you will see exactly the same window as in modern versions.

Conclusion

In this article we looked at various methods for auto-filling data in the Excel editor. You can use any option convenient for you. If suddenly something doesn’t work out for you, perhaps you are using the wrong data format.

Note that the values ​​in the cells do not need to increase continuously. You can use any progressions. For example, 1,5,9,13,17 and so on.

Video instruction

If you have any difficulties using this tool, you can also watch the video from detailed comments to the methods described above.

Few people like to spend a long time and monotonously entering identical or similar data into a table. It's a pretty boring job that takes a large number of time. Excel has the ability to automate the entry of such data. For this purpose, there is an auto-fill function for cells. Let's see how it works.

Autofill in Microsoft Excel carried out using a special fill marker. In order to call this tool, you need to hover the cursor over the lower right edge of any cell. A small black cross will appear. This is the fill marker. You just need to pinch left button mouse and drag to the side of the sheet where you want to fill the cells.

How the cells will be filled depends on the type of data that is in the source cell. For example, if there is regular text in the form of words, then when you drag it using the fill marker, it is copied to other cells of the sheet.

Autofill cells with numbers

Most often, autocomplete is used to enter a large array of numbers that appear in order. For example, in specific cell The number is 1, and we need to number the cells from 1 to 100.

As you can see, after this the entire required range was filled with numbers in order.

But it can be made even simpler. You won't need to call the autocomplete options. To do this, when you drag the fill marker down, in addition to holding down the left mouse button, you need to hold down another button Ctrl on keyboard. After this, filling the cells with numbers in order occurs immediately.

There is also a way to autocomplete the progression series.

Fill Tool

Excel also has a separate tool called "Fill in". It is located on the ribbon in the tab "Home" in the toolbox "Editing".

By using of this instrument you can also fill the cells with a progression.

Autocomplete formulas

One of Excel's main tools is formulas. If present in the table large number For identical formulas, you can also use the autocomplete function. The essence does not change. You need to use the same fill marker method to copy the formula into other cells. Moreover, if the formula contains references to other cells, then by default, when copying in this way, their coordinates change according to the principle of relativity. Therefore, such links are called relative.

If you want addresses to become fixed when autofilling, you need to put a dollar sign in front of the row and column coordinates in the source cell. Such links are called absolute. Then it is produced normal procedure autofill using the fill handle. In all cells filled in this way, the formula will be absolutely unchanged.

Autofill with other values

In addition, Excel provides auto-fill with other values ​​in order. For example, if you enter a date and then use the fill handle to select other cells, the entire selected range will be filled with dates in strict sequence.

In the same way, you can autofill by day of the week (Monday, Tuesday, Wednesday...) or by month (January, February, March...).

Moreover, if there is any number in the text, Excel will recognize it. When you use the fill marker, the text will be copied and the numbers will change in an ascending manner. For example, if you write the expression “4th building” in a cell, then in other cells filled with the fill marker, this name will be transformed into “5th building”, “6th building”, “7th building”, etc.

Adding your own lists

The AutoFill feature in Excel is not limited to certain algorithms or predefined lists, such as days of the week. If desired, the user can add his personal list to the program. Then, when writing into a cell any word from the elements that are in the list, after applying the fill marker, the entire selected range of cells will be filled with this list. In order to add your list, you need to perform the following sequence of actions.

As you can see, autofill in Excel is very useful and convenient tool, which allows you to significantly save time on adding identical data, repeating lists, etc. Another advantage of this tool is that it is customizable. You can add new lists to it or change old ones. In addition, using autofill you can quickly fill cells various types mathematical progressions.

Autofill cells

Formatting cells

• Data Alignment
• Setting font options

Tasks for independent work

Autofill cells

Automatically repeat items already entered in a column

If the first few characters entered into a cell match characters that are already in that column, then MS Excel automatically substitutes the missing part of the characters. Auto completion occurs only when the entry consists of text or text and numbers. This function is not used for entries that consist entirely of numbers, dates, or times. This command allows you to quickly edit and fill out a table.

If it is necessary for the entry to completely match, then as soon as it appears in the cell, you need to press the key Enter. If the entry is not needed completely, then when you reach the desired symbol, press the key Backspace(Fig. 3.1).

Rice. 3.1. Auto completion

Filling data with a fill handle

To fill cells with data MS Excel can automatically continue a series of numbers, combinations of numbers and text, dates and times according to a given pattern. To quickly fill cells with data, you need to select a range of cells and move the fill marker to the desired side (i.e., fill a row or column).

After you move the fill handle, a button appears Autofill options in the form of a square, with which you can select parameters for filling the selected range of cells. So, to fill only cell formats (set style, type, size, font color), you need to select the option Fill in formats only(Fig. 3.3.a), to fill only the contents of the cell - option Fill in values ​​only(Fig. 3.3.b), to copy this range – Copy cells(Fig. 3.3.c).

Rice. 3.3 Selecting commands using the fill handle

Filling the active cell with the contents of an adjacent cell

To fill adjacent cells, you need to select empty cells, capturing in the selection and the cell with data, below, to the right, above or to the left of the cell that contains the data to be filled. On the tab home in Group Editing select a team Fill, and then in the list that opens the command Down, Right, Up or Left(Fig. 3.4).

Note: To quickly fill a cell with data from a cell that is located on the top or left, you can use keyboard shortcuts Ctrl+D or Ctrl+R.

Rice. 3.4. Fill command

Filling cells with a sequence of numbers, dates, or built-in list items

Using a fill handle, you can quickly fill a range of cells with a sequence of numbers, dates, or built-in list items such as days of the week, months, or years. To do this you need to follow these steps:

• enter the initial value into the cell
• enter values ​​in the following cells to set the filling pattern
• select filled cells

For example, if you need to set the sequence 1, 2, 3, 4, 5..., you need to enter the values ​​1 and 2 in the first two cells. If you need the sequence 2, 4, 6, 8... - sequence 2 and 4. If the sequence 2, 2, 2, 2... is required, then the second cell can be left empty.

Drag the fill handle across the range (to the desired cell) that you want to fill.

Note: To fill in ascending order, drag the marker down or to the right. To fill in descending order - up or left.

If the initial value is a date, for example, “Jan.13”, then to obtain a series consisting of the names of the months in the same format, you need to select the command Progression from the menu Fill groups Editing tabs home(Fig. 3.5.) and in the dialog box that appears, put in the field Units switch opposite the value month.

Rice. 3.5.Progression dialog box

Custom autocomplete list

To make it easier to enter certain data sequences, you can create custom autocomplete lists. Such a list is created based on a list already existing on the sheet or entered manually.

Note: A custom list can consist of text only or text combined with numbers. In order to create custom list consisting only of numbers, you first need to create a list of numbers in text format.

To create a list from an existing one, you need to select on the sheet the range of cells that you want to include in the autocomplete list. Press the button MS Office and select a team Excel Options . In the dialog box that appears, select a group Basic, and in it in the region Basic parameters for working with Excel press the button Change lists.

In the dialog box Lists(Fig. 3.6) press the button Import(this field indicates the addresses of the cells that are included in the list). The list items will be automatically added as a new list. When finished, click the button OK.

Rice. 3.6. Lists dialog box

To create new list, you can also enter it in the field List elements dialogue she Lists. Each element is separated from each other by pressing a key Enter. Then click on the button Add and it will be reflected as a list in the field Lists.

To delete a list, you need to select it in the dialog box Lists and press the button Delete. Confirm deletion and click the button OK.

Formatting cells

To format cells: selecting the data format in a cell, placing text in a cell, setting font type, size, color, designing the borders of a cell or table, fill command - use tab commands home groups Font, Alignment, Number or dialog box Cell Format.

Selecting the format for displaying values ​​in a cell

The previous lecture discussed the issue of data types: text, number, formula. By using Number formats you can specify what values ​​the data in the cell accepts, for example, monetary format (2.00 rubles or $ 2.00), date (07/15/13 or July 15, 2013), percentage (20.00%) and others. Also, each format has its own parameters. To do this, you can display a dialog box Cell Format(Fig. 3.7) or use group commands Number tabs home(Fig. 3.8).

Rice. 3.7. Dialog Box Format Cells: Number

Rice. 3.8. Group commands Number tab Home

In addition to commands related to the data format, in this group you can set the format with delimiters (dividing the bit depth, for example, 2000), increase / decrease the bit depth (the number of decimal places, for example, 2.35346 - if you reduce the bit depth to one place, you get 2 ,4).

Data Alignment

You can use group commands to align data Alignment tabs home or dialog box commands Cell Format: Alignment(Fig. 3.9). The alignment area indicates the horizontal and vertical location of the data relative to the cell; in the display area Wrap according to words you can fill out these cells in several lines, Auto width selection– change the font size if the data is larger than the cell width, Merging cells– makes one from several cells; in area Orientation You can specify at what angle the text should be rotated.

Rice. 3.9 Format Cells: Alignment dialog box

Setting font options

Using group commands Font tabs home or dialog box Format cells: Font(Fig. 3.10) you can change the font type, size, style, and color. Using commands Superscript And Interlinear in field Modifications the upper (t o) and lower (t o) indices are set.

Rice. 3.10 Dialog Box Format Cells: Font

Framing the selection and filling it

On the worksheet, the table boundaries are represented as gray lines that are not displayed when printed. To set the desired boundaries, apply a border to the created table using the command Borders groups Font tabs home. Pre-allocated table in MS Excel You can frame it and fill the cells with different colors.

Having previously selected the table, in the dialog box Cell Format: Borders(Fig. 3.11) you can select the line type, set the color, and then in the Separate area, use the mouse to specify only those borders that should be set for the table.

Rice. 3.11 Format Cells: Borders dialog box

To fill a cell or group of cells, use the command Fill from the group Font tabs home or dialog box Format cells:Fill. In the latter, you can specify the type of fill pattern and its color.

Tasks for independent work

1. Launch the program MS Excel.

2. Create a custom one automatic list, consisting of any 4 elements (for example, seasons - winter, spring, summer, autumn).

3. Create a custom list on sheet 1.

4. Rename sheet 2 V Factors.

5. Design the table according to the sample, taking into account the location of the text in the cells, the style, and the established borders for the table:

6. For this table, set the font type to Arial; font size – 12, color – blue.

7. Rotate text in a cell AT 3 at 90 o.

8. On sheet 3 create a custom table that includes fields such as Date (set the desired format), Numeric (set the desired format, for example, numeric with

indicating two decimal places, monetary, percentage). Set borders to this table and copy it to sheet 1.

9. Save this file under the name Formatting.

10. Close the program.