Often, an Excel user is faced with the need to fill cells with data arranged in a certain logical sequence. Or, for example, to make a forecast of what the value of a certain indicator will be by a specific moment, if the current trend continues. Now you do not need to know dozens of formulas for all this. A couple of mouse clicks are enough, and the problem is solved. It’s all thanks to autocomplete.
This feature is amazing in its convenience. For example, it allows you to quickly list calendar months or make it so that only the 15th and the last day of each month is displayed (for example, in accounting reports).
How can you take advantage of this great feature?
In the lower right corner there is a square, by dragging it, you can continue the series of values in which there is a certain pattern. For example, if the first day is Monday, then by performing this simple operation, you can put values in the following lines: Tuesday, Wednesday, Thursday, Friday, and so on.
If there is a set of values in a cell like 1,2,4, then by selecting all of them and dragging the box down, you can continue the number series up to 8, 16, 32, and so on. This allows you to significantly reduce time costs.
In the same way, a list of month names is created.
And this is what using autocomplete for an arithmetic progression looks like. In our example, we use two cells with values of 1,3, respectively, and then autocomplete continues the number series.
Moreover, the reception will work even if the number is inside the text. For example, if you write “1 quarter” and drag the box down, you get the following.
Actually, these are all the basics that you need to know. But if you want to master the skills of working with Excel more professionally, you can familiarize yourself with some chips and tricks.
Using the Autocomplete Data List
Of course, making a list of months or days of the week isn’t all Excel can do. Let’s say we have a list of cities where our company has established service centers. First you need to write down the complete list in the “Change lists” item, which can be accessed through the menu sequence File – Options – Advanced – General – Edit lists.
Next, a window will appear with a list of lists that are built into Excel automatically.
There are not many of them here. But this misunderstanding can be easily corrected. To do this, there is a right window in which the correct sequence of values uXNUMXbuXNUMXbis written. Recording can be done in two ways, both with commas and in a column. If there is a lot of data, they can be imported. This way you can save a lot of time.
What is needed for this? First you need to create a list of cities somewhere in the document, and then just make a link to it in the field below.
The list is now created and can be used to fill in all the other cells.
In addition to lists in text format, Excel makes it possible to create numerical sequences, as well as a list of dates arranged according to a certain pattern. At the very beginning of this material, one of the ways to use it was given, but this is a primitive level. You can use this tool more flexibly, like a real ace.
First, we select the required sequence values (one or more) along with the part of the range that will be used for the list. Next, we find the “Fill” button on the top panel and in the menu that appears, click on the “Progression” button.
Next, a window with settings appears.
In its left part there are radio buttons with which you can set the location of the future sequence: by rows or columns. In the first case, the list will go down, and in the second, it will go to the right.
Immediately to the right of the location setting is a panel where you can select the type of numerical sequence. There are several options to choose from:
- Arithmetic. The value in each next cell is a certain number greater than the previous one. Its value is determined by the contents of the “Step” field.
- Geometric. Each subsequent value is several times greater than the previous one. How much exactly depends on which step the user indicated.
- Dates. With this option, the user can create a sequence of dates. If you select this type, additional settings for the unit of measure are activated. They are taken into account when drawing up the sequence: day, working day, month, year. So, if you select the “working day” item, then weekends will not be included in the list.
- Autocomplete. This option is similar to dragging the bottom right corner. In simple words, Excel decides on its own whether it needs to continue the number series or it is better to make a longer list. If you specify the values 2 and 4 in advance, then the next ones will contain the numbers 6, 8, and so on. If before that you fill in more cells, then the “linear regression” function will be used (this is a very interesting option that allows you to build a forecast based on the existing trend).
At the bottom of this dialog box, as you can already see, there are two options: the step size, discussed above, and the limit value.
After completing all the operations, you need to press the “OK” button. In the same way, a list of working days for a certain period is created (for example, until 31.12.2020/XNUMX/XNUMX). And the user does not need to make a huge number of unnecessary movements!
That’s it, setup is complete. Now let’s look at some more professional autocomplete methods.
Using the mouse
This is the most convenient way to autocomplete, allowing you to elegantly perform even the most complex operations. There are two options for how it can be used: using the left mouse button or the right one. For example, the task is to make a list of numbers arranged in ascending order, where each next value increases by one. Usually, for this, a unit is entered into the first cell, and a deuce into the second, after which they drag the box in the lower right corner. But it is possible to achieve this goal by another method – by simply filling in the first cell. Then you need to drag it down from the lower right corner. After that, a button in the form of a square will appear. You need to click it and select the “Fill” item.
You can also use the “Fill only formats” function, with which you can extend only cell formats.
But there is a faster method: holding the Ctrl button while dragging the cell in parallel.
True, it is only suitable for autocomplete sequences of numbers. If you try to pull this trick with data of a different type, then the values uXNUMXbuXNUMXbwill simply be copied into the following cells.
There is a way to speed up the call of the context menu. To do this, drag the box by holding the right mouse button.
Then a set of commands will appear. So, you can call up a dialog box with additional autocomplete settings by clicking on the “Progression” menu item. But there is one limitation. The maximum sequence length in this case will be limited to the last cell.
In order for auto-completion to be carried out up to the required value (a specific number or date), you must press the right mouse button, having previously directed the cursor to the box, and drag the marker down. The cursor then returns. And the last step is to release the mouse. As a result, a context menu with autocomplete settings will appear. Select a progression. Here, only one cell is selected, so you need to specify all the autofill parameters in the settings: direction, step, limit value, and press the OK button.
A particularly interesting Excel function is the linear and exponential approximation. It makes it possible to build a forecast of how the values will change, based on the existing pattern. As a rule, to make a forecast, you need to use special Excel functions or perform complex calculations, in which the values of the independent variable are substituted. It is much easier to demonstrate this example in practice.
For example, there is a dynamics of an indicator, the value of which increases by the same number every period.
It is much easier to understand how values are predicted with a linear trend (when each next indicator increases or decreases by a certain value). Standard Excel functions are suitable for this, but it is better to draw a graph that displays the trend line, the equation of the function, and the expected value for greater clarity.
To find out what the predicted indicator will be in numerical terms, when calculating, you need to take the regression equation as the basis (or directly use the formulas built into Excel). As a result, there will be a lot of actions that not everyone can understand right off the bat.
But linear regression allows you to completely abandon complex formulas and plotting. Just use autocomplete. The user takes a range of data on the basis of which a forecast is made. This set of cells is selected, then the right mouse button is pressed, which you need to drag the range by the required number of cells (depending on the distance of the point in the future for which the predicted value is calculated). A context menu will appear, where you need to select the “Linear Approximation” item. That’s it, a forecast is obtained that does not require special mathematical skills, plotting or deriving formulas.
If the indicators increase during each period of time by a certain percentage, then we are talking about exponential growth. For example, forecasting the dynamics of an epidemic or predicting interest on a bank deposit is based on just such a pattern.
There is no more effective method for predicting exponential growth than the one we have described.
Using the mouse to autofill dates
Often it is necessary to increase the already existing list of dates. To do this, some date is taken and dragged by the lower right corner with the left mouse button. A square icon appears, where you can select the filling method.
For example, if an accountant uses a spreadsheet, the “weekdays” option will suit him. Also, this item will be needed by any other specialists who have to make daily plans, for example, HR.
And here is another option, how to implement automatic filling of dates with the mouse. For example, a company must pay salaries on the 15th and the last day of the month. Next, you need to enter two dates, stretch them down and select the filling method “by months”. This can be done either by clicking the left mouse button on the square in the lower right corner, or by using the right button, followed by an automatic call to the context menu.
Important! The 15th remains, regardless of the month, and the last one is automatic.
With the right mouse button you can adjust the progression. For example, make a list of working days this year, which will still be up to December 31st. If you use autofill with the right mouse button or through the menu, which can be accessed through the square, then there is an option “Instant fill”. For the first time, developers have provided this feature in Excel 2013. It is necessary for the cells to be filled in according to a certain pattern. This will enable you to save a lot of time.
Conclusions
Actually, that’s all. Autocomplete is a very useful feature that helps you automate many workflows and make predictions based on patterns. No additional formulas or calculations are required. It is enough to press a couple of buttons, and the results will appear as if by magic.