Originally published at: AutoComplete in Excel sheets explained | ONLYOFFICE Blog
Have you ever thought about how to make it easier to add data in Excel sheets? If your answer is yes, then you need to try the AutoComplete feature. Read the article below to learn more about AutoComplete in Excel and discover how you can save your time and effort using this tool.
What is AutoComplete in Excel?
AutoComplete is a feature that automatically fills in data as you type text when working on Excel sheets. It predicts the rest of a phrase or word based on what you have already typed. In other words, if you type something that begins with the same characters as another text entry in the Excel column, you will be suggested the possible rest of the word or phrase.
The AutoComplete feature is designed to anticipate what you are going to type. If the suggested text is what you need, then you can accept it by pressing the Enter key on the keyboard or by clicking on the required suggestion in the drop-down list. This way, you can save a lot of time and effort when working with similar data.
For example, imagine that you have a long table with a lot of duplicates. If the AutoComplete feature is on, when you start typing in a cell, it will automatically fill in the rest of the information, which significantly speeds up the process of data entry.
Let’s take a look at how AutoComplete works in Excel sheets and consider some practical examples using ONLYOFFICE Spreadsheet Editor.
Example 1: populate cells with text values
Let’s imagine that we have a text value that we want to copy to complete a series of cells. To do so, we need to use the Fill handle tool, which is an indispensable part of the AutoComplete feature. It’s a tiny square that appears in the lower right corner of the selected cell or range.
To fill in a series of cells with the same text values, let’s follow these steps:
- Open your Excel file in ONLYOFFICE Spreadsheet Editor;
- Select a cell or range of cells that contains the required text;
- Move the mouse cursor over the fill handle in the right lower corner of the selected cell or range of cells. The mouse cursor will turn into a small black cross;
- Drag the small black cross vertically or horizontally.
As soon as you release the mouse button, the selected cells will be filled with the same text.
Example 2: populate cells with numeric values
Another popular example of how to use the AutoComplete feature in Excel sheets is to complete a series of cells with numbers. This can include currencies, times, dates, days of the week, months, etc.
Let’s imagine that we need to continue a sequence of integers starting from 1. First of all, we enter the first two integers into the starting cells, move the mouse cursor over the fill handle and grab the fill handle to fill in the specified range with the required numeric data.
The AutoComplete feature can recognize various numeric patterns, so you can complete cells and ranges in different ways. For example, you can get number sequences with different parameters. Don’t forget that the difference between numbers must be constant.
Imagine that you want to get a number sequence where the difference between numbers equals 3. So it will look like this: 1, 4, 7… Just enter the first three values to make it possible for the AutoComplete feature to identify the required pattern and use the fill handle to get what you need.
Example 3: populate cells with the days of the week and months
Starting from version 7.5, ONLYOFFICE Spreadsheet Editor can also auto-populate days of the week and months. Let’s take a look at the example below.
Imagine that you want to complete some cells with months. You don’t have to start with January, you can choose any month you need. For example, if you want to start with March, you just need to fill in the first cell and perform the basic AutoComplete operations described above. All the months will appear in the corresponding order starting from the month you specified.
The same principle works for the days of the week. One of the best things about the AutoComplete feature is that it can recognize abbreviations so you are allowed to use “Mon” instead of “Monday”, “Jan” instead of “January” and so on.
Example 4: populate multiple rows or columns
It’s a piece of cake for the AutoComplete feature to copy text and numerical values in separate rows and columns. However, is it capable of dealing with data in more than one row or column? The answer is yes.
Let’s imagine that we have a table with some employees and their days off. Let’s pick several rows and columns, drag the fill handle and see what we will get. All the required data has been successfully copied.
Example 5: populate cells using the required value from a drop-down list
If a column in your Excel spreadsheet contains several text values, you can easily replace any of them or fill in the next blank cell by selecting one of the already existing options. To do so, you need to choose the Select from drop-down list option.
For the sake of practice, let’s replace one of the values in our sheet. For example, we want to replace Yellow with Blue. To do so, we need to select Yellow, right-click it to access the contextual menu, choose Select from drop-down list and pick Blue from all the available options. Yellow will be immediately replaced with Blue.
Example 6: populate empty cells
The AutoComplete feature also allows you to fill in data with empty cells. You just need to select the required values and the empty cells you want to copy and use the fill handle in the usual way.
Example 7: populate cells in different directions
In all our examples above, we populated cells vertically. However, it’s not a problem for the AutoComplete feature to fill in data horizontally. You do the same things but the fill handle should be dragged to the left or the right depending on the location of the selected range of cells.
Let’s build simple number sequences starting from 1 in different directions:
Use the AutoComplete plugin in ONLYOFFICE Spreadsheet Editor
In ONLYOFFICE Spreadsheet Editor, there is a special plugin that can make it easier to enter large text values. The AutoComplete plugin is an input assistant that suggests possible options as you type a word.
When this plugin is enabled, it automatically suggests all the words that contain the letters that you have already typed. That means that you don’t have to type an entire word. You just need to enter the first few letters and choose the option you need with a mouse click from the drop-down list.
For example, if you want to type “Monday” in a cell, it’s enough to type “Mond”. When you work with large volumes of text information, this tool can save you a little time. Moreover, you can find it useful when you are not sure about the correct spelling of a word.
The AutoComplete plugin is not available by default so you need to install it. It can be easily done via the built-in Plugin Manager in ONLYOFFICE Spreadsheet Editor. You can find it on the Plugins tab:
When you open the Plugin Manager, you will see all available plugins. Find AutoComplete and click Install. That’s it.
AutoComplete is a system plugin so it doesn’t have an icon. When installed, it always remains enabled. If you don’t want this feature, you will need to remove the plugin. You can do it via the Plugin Manager. Just find AutoComplete and click the Remove button.
Read this article or watch the video below to learn more about how to install and delete plugins in ONLYOFFICE:
Get ONLYOFFICE Spreadsheet Editor
Populate cells the way you like with the AutoComplete feature in Excel sheets online in your web browser by creating a free ONLYOFFICE DocSpace account or by downloading the ONLYOFFICE free desktop app for Windows, Linux and macOS:
Watch this video to discover what’s new in the latest version of the ONLYOFFICE suite, v7.5: