Adding contents automatically in Calc

Spreadsheets are labor-intensive documents. Usually, their contents is entered carefully, one sheet at a time, at an input rate far below a text document. However, like most spreadsheets, OpenOffice.org has several tools for removing some of the drudgery from input.

The most basic ability is to drop and drag the contents of one cell to another with a mouse. However, Calc also includes several other tools for automating input, especially of repetitive material. They include the Fill tool, selection lists, and the ability to input information into multiple sheets of the same document.

Using the Fill tool on cells

In Draw, OpenOffice.org's graphics program, fill tools automatically fill an object with a color or gradient, saving you the effort of filling it in by hand. In much the same way, Calc's Fill tool does instantly basic work that would take much longer for you to enter manually.

At its simplest, the Fill tool is a way to duplicate existing content. Start by selecting the cell to copy, then drag the mouse in any direction to select the cells to fill and/or overwrite, followed by Edit -> Fill and the direction in which you want to copy: Up, Down, Left or Right. Choices that are not available are grayed out, but you can still choose the opposite direction from what you intend, which could cause you to overwrite cells accidentally unless you are careful.

A more complex use of the Fill tool is to use the fill series defined in Tools -> Options -> OpenOffice.org Calc -> Fill Lists. The default lists are for the full and abbreviated days of the week and the year, but you can create your own lists as well. To add a fill series to a spreadsheet, select the cells to fill, and, choosing Edit -> Fill -> Series, enter an item in any series you have defined. The selected cells will then add the other items on the list sequentially, beginning from the top when they reach the end of the list.

You can also use Edit -> Fill -> Series to create a one-time fill series for numbers by entering the starting and end values as well as the increment. For instance, if you entered the starting and end values of 1 to 7 with an increment of 2, then you would get the sequence of 1, 3, 5, 7.

In all these cases, the fill tool creates only a momentary connection between the cells. Once they are filled, the cells have no further connection with one another.

Using selection lists

Selection lists resemble autosort lists. They are available only for text, and are limited to using only text that has already been entered in the same column.

To use a selection list, select a blank cell and press Ctrl + D (in earlier versions of Calc, you could also make a selection from the right-click menu, but that item has been removed from recent versions). A drop-down list of appears of any cell in the same column that either has at least one text character or whose format is defined as Text.

Methods of sharing content between sheets

At times, you might want to enter the same information in the same cell on multiple sheets. For example, you might want to set up standard listings for a group of individuals or organizations. Instead of entering the listings over and over, you can enter it in all the sheets at once by selecting all the sheets, then entering the information in the current one. Be aware, however, that this technique will overwrite any information that is already in the cells on the other sheets -- and without any warning. For this reason, when you are finished, be sure to unselect all the tabs, so that each sheet can be edited without affecting any others.

Alternatively, you can use the Fill tool to copy contents automatically to another sheet. To do so, select the sells to copy, or, if you want to copy the entire sheet, press Ctrl + A or clicking the box in the upper left corner of the spreadsheet. Then select the tab of the sheet to which to copy, followed by Edit -> Fill -> Sheets. The default is to copy all formatting, but you can select the formatting to include from the dialog window. You can also choose whether to add, subtract, multiply or divide values in the first sheet by those in the sheets to which you are copying. Other options are to skip empty cells in the copying, transpose or switch the position of columns and rows on the target sheet in a kind of simplified datapilot creation, or create a link so that changes on the original sheet are mirrored on the target sheets.

Conclusion

If you choose, you can enter information on your spreadsheets entirely by hand -- just as some people stubbornly refuse to use styles and persist in using manual overrides of formatting instead. And, in some cases, you may have no particular advantage in using the input automating tools.

Still, none of these tools are difficult to learn, and the Fill tool even includes the ability to create custom lists that you can re-use. Take a few minutes to learn them, and the next time you have some repetitious information to input, you can save yourself both time and boredom.

Bruce Byfield is a computer journalist who writes regularly for Datamation, Linux.com, and the Linux Journal web site.

______________________

--
Bruce Byfield (nanday)

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

I this that it is great idea

Lucas's picture

I this that it is great idea with adding content automaticly via Calc. It is very helpful and usseful for many users. I can agree with Your following conclusion:
"If you choose, you can enter information on your spreadsheets entirely by hand -- just as some people stubbornly refuse to use styles and persist in using manual overrides of formatting instead. And, in some cases, you may have no particular advantage in using the input automating tools."

Best Regards
Lucas Discussion and Tax return site owner!

nice

Fling's picture

This should be used with caution. Inserting a new line does not work correctly - it will appear in the current sheet, but not any of the others selected. Likewise the Paste Special Move cells option only actually moves the cells on the current sheet, not the rest. Both of these flaws put the sheets out of sync, leading to possible data loss. There may be other common actions which are not mirrored accross all sheets.

Paste Special strangely (and

Anonymous's picture

Paste Special strangely (and annoyingly) greys out the "Down" option under "Shift", so that cells can only be moved to the right. This makes for easier editing in Vim than OOo.

Interesting

wbb's picture

Seems to be a interesting and useful feature for me, thx.

Multiple sheet entry

Huw's picture

"Instead of entering the listings over and over, you can enter it in all the sheets at once by selecting all the sheets, then entering the information in the current one."

This should be used with caution. Inserting a new line does not work correctly - it will appear in the current sheet, but not any of the others selected. Likewise the Paste Special Move cells option only actually moves the cells on the current sheet, not the rest. Both of these flaws put the sheets out of sync, leading to possible data loss. There may be other common actions which are not mirrored accross all sheets.

"... when you are finished, be sure to unselect all the tabs, so that each sheet can be edited without affecting any others."

I think the method for deselecting other sheets all at once, useful when you have dozens selected, is still undocumented. It is certainly not obvious to a lot of users. It's simple though... Shift+Left-mouse-click on the current tab.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState