Using Flash Fill in Excel

Flash Fill in Microsoft Excel is one of the features that have appeared in recent versions of the program. Its main purpose is to analyze the abbreviations that the user uses when duplicating the value of one cell into another. One example is shortening the full names in the list to the required format, which I will consider in the next instruction.

How to Fill Cells Instantly in Microsoft Excel

To begin with, Excel needs to know the regularity on the basis of which it will instantly fill the cells. For example, you have a list of months with full names, but you want to shorten them to three letters. So, in the cell opposite, you should write “Jan” and activate instant filling for the entire table. The program will immediately understand exactly how you want to shorten the spelling, so it will also leave only the first three letters for all other lines.

A more complex option is just to use the abbreviations of the surname and patronymic name, as already mentioned in the introductory paragraph. Let me look at it in more detail so that you understand how the tool works.

  1. Open the table and start from the first row, typing the abbreviation you want to appear in the rest of the list. Pay attention to the following screenshot to understand which path I chose in my example.
  2. Move to the next line and enter the first letter. Autocomplete, enabled by default in Excel, should suggest suggestions for all table cells at once. If it doesn’t, stay on the first line with the abbreviation and press Ctrl + E to apply the flash fill option.
  3. Look at the result and make sure that you managed to cope with the task. Feel free to experiment with different abbreviations to test the tool in more detail.
  4. In the block with instant filling, a button will appear that is responsible for opening a panel with additional actions. This allows you to undo the filling, accept prompts for data entry, or highlight all changed cells.

If the proposed option does not suit you, for example, the keyboard shortcut does not work or the options for filling do not appear, you can go a slightly different way. For this option, there is a button on the toolbar that activates the instant fill action.

  1. Create the first line with the shortened version and go to the Data tab .
  2. Click the button labeled Instant Fill .
  3. Note that the tool has taken effect, the table is full, and you have saved a significant amount of time.

Solving some problems

In conclusion, I will quickly tell you what to do if, when editing cells, options for instant filling do not appear, or for some reason this tool does not work even by clicking on the corresponding button. The most common reason is a disabled feature in the settings. Its verification is performed as follows:

  1. Click the “File” tab .
  2. Open the list of program options by clicking on the line with the appropriate name.
  3. Select the “Advanced” section and make sure that in the category  Editing Options” checkboxes are checked next to the items “Autocomplete cell values” and “Automatically perform flash filling” .

If you did not find an item with instant filling, then you are using an old version of Microsoft Excel and this tool is simply not available. Install the update if possible.

Another problem is that the program cannot figure out exactly how you want to use Flash Fill. If so, an error will appear on the screen with the corresponding content. Do not close it immediately, but read the description and do everything as the developers write.

Use Flash Fill in Microsoft Excel to your advantage, streamlining and dramatically speeding up your workflow. If you don’t need the original data after reduction, simply delete it to save space in the spreadsheet.

Leave a Reply

Your email address will not be published.