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.
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)
Fast/Flexible Linux OS Recovery
On Demand Now
In this live one-hour webinar, learn how to enhance your existing backup strategies for complete disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible full-system recovery solution for UNIX and Linux systems.
Join Linux Journal's Shawn Powers and David Huffman, President/CEO, Storix, Inc.
Free to Linux Journal readers.Register Now!
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- Client-Side Performance
- Tibbo Technology's Tibbo Project System
- Sony Settles in Linux Battle
- Peppermint 7 Released
- Libarchive Security Flaw Discovered
- Maru OS Brings Debian to Your Phone
- The Giant Zero, Part 0.x
- Profiles and RC Files
- Git 2.9 Released
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide