/

How to Autofill Data in LibreOffice Calc [Complete Guide]

2211 views

Learn how to autofill data in cells and columns in LibreOffice Calc sheet with different methods with examples.

Inputting data into a spreadsheet can be a tedious and time-consuming task. However, Calc offers several features that can help alleviate some of the drudgery associated with data entry. There are a few ways by which you can easily autofill data in LibreOffice Calc.

Using Autofill in LibreOffice

Drag handle

Perhaps the easiest way for a quick autofill with the same text or numbers is a “drag handle”. What is a drag handle?

If you move your pointer to the “right-bottom” of a cell, you should see your mouse cursor change to “[+]”. While using it, you can hold & drag your mouse upwards, downwards, left or right. When you use this method on a cell which has a value, LibreOffice Calc fills it up with the same value until the place you drag.

Example of drag handle in LibreOffice for Autofill
Example of drag handle in LibreOffice for Autofill

You can also use this to autofill multiple selected rows and columns.

Autofill using drag handle in LibreOffice
Autofill using drag handle in LibreOffice

Note: If your selected cell contains a number, a series will be created by incrementing the number. For example, in the above image, you can see Col3 is incremented to Col4. You can prevent this incrementation by holding CTRL while dragging. It will then copy the cells.

Pattern matching auto input

LibreOffice Calc can identify similar texts from all cells in a column and can suggest you autofill. To do that, ensure you have Tools > Autoinput checked in the menu.

Start typing the text in a call, and LibreOffice Calc will automatically suggest you the matching items as shown below. You can press ENTER to accept the autocomplete and go to the next cell.

Autocomplete with auto input
Autocomplete with auto input

If you need to see more options that begin with the same letters, you can use the following key combinations: Ctrl+Tab to move forward, or Ctrl+Shift+Tab to move backwards.

To display a list of all the available AutoInput text items for the current column, press Alt+↓ (down arrow) on your keyboard. This will show a small menu with a unique list of items that are available in that particular column.

CTRL+ down arrow can show you available items
ALT+ down arrow can show you available items in a small pop-up menu

Using Quick Copy with CTRL+D

At any time, if you press CTRL+D (fill Down) in any cell, the cell will automatically populated by the data from the above cell. This includes texts, numbers and formulas.

This way you can update your Calc spreadsheet faster.

Using Fill Cells tool

  • For filling up with incrementing series, repeated values across thousands of rows or columns, you can use the Fill Cells option. The options are available under Sheet > Fill Cells.
  • Select the entire range of cells and then choose any of “Fill Down”, “Fill Up”, “Fill Left”, or “Fill Right”. LibreOffice Calc will automatically fill up with the data from the first cell.
  • See the below example.
Various Fill Cell demo
Various Fill Cell demo

Filling incrementing or growth series

  • If you want more control over the number generation, such as you want to fill, say, 1 to 10,000 in rows or columns, you can use the “Fill Series” option. Here’s how.
  • From the menu, select Sheet > Fill cells > Fill Series.
  • It will open up a separate dialog. You can enter the start value, end value, increment value and fill up the cells. Before using this, make sure to select the range of cells.
Fill Cell menu
Fill Cell menu
Fill Series with custom increment
Fill Series with a custom increment

Using Autofill series with pre-defined values

  • Using the Sort List feature, you can define your custom list in LibreOffice Calc. Then, use the sort list to autofill using the drag handle.
  • The option is available under Tools > Options > LibreOffice Calc > Sort LIst.
  • In the Sort List window, you should see some pre-defined values, such as weekdays and months pre-loaded.
  • You can also click on new and define your custom list or copy from a series of cells.
Custom sort list in Libreoffice Calc
Custom sort list in Libreoffice Calc

Example of custom list for autofill

Say, I want to define a custom list for all the states of the US. To do that, I put the names in the sort list or copy from another spreadsheet.

An example of custom list
An example of custom list

And then, you can type any name of states and use the drag handle to autofill in cells.

Custom list autofill
Custom list autofill

Wrapping Up

In this tutorial, you learned various ways to autofill data in cells or columns in the LibreOffice Calc sheet. Using these methods, you can speed up your data entry and ensure proper data validations.

Join our Telegram channel and stay informed on the move.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments