Formatting cells in OpenOffice.org Calc

I've seen spreadsheets that are basically interactive tutorials, and many more loaded with what Edward Tufte refers to as "chartjunk" -- embellishments that do nothing to make the presentation of information more effective. Yet, generally, spreadsheets are treated pragmatically. Certainly, few people worry about their layout than the layout of text documents. Still, even if you share this attitude, learning the basic formatting options for cells in OpenOffice.org Calc can be worth your time. Many of the options directly effect how you interact with spreadsheets, and even the purely visual ones can make your lists and calculations easier to read at a glance.

If you just want a quick splash of color, you can format an entire sheet by selecting Format > Autoformat, or the Choose Theme icon (which seems to have disappeared in the default settings, but is retrievable via Tools > Customize > Toolbars).

However, although you can save your own autoformats, for versatility, nothing beats using cell styles and creating your own default template. Styles aren't as much a necessity in Calc as they are in Writer, since you can still access features even if you don't use them, but they are just as much time-savers, especially if you continually use the same designs.

As in Writer, styles in Calc are available by selecting Format > Styles and Formatting or by pressing F11. If you insist on manual formatting, the same options are available using Format > Cells. The options for each style include both visual and functional formatting, although the style window, unfortunately, isn't organized with those distinctions in mind.

What options should you choose for your cell styles? Without marching through every single option, here are some you should consider whenever you design a spreadsheet.


Visual Formatting

The cell style window focus largely on visual formatting. Visual options are available on five tabs: Font, Font Effects, Alignment, Borders, and Background, all of which you'll find elsewhere in OpenOffice.org.

Tastes differ, but, for me, the most essential of these tabs is Alignment. In a holdover from the earliest days of the personal computer, Calc, like any spreadsheet application, defaults to displaying only a limited number of characters. Having come late to spreadsheets in my computer interactions, I much prefer to adjust column widths and use the Alignment tab to enable automatic text wrap and hyphenation -- or, very occasionally, shrinking the text to fit the cell. The Alignment tab also includes options for setting text vertically or on an angle, which can be useful for headers in narrow columns, but these are secondary, less essential options so far as I'm concerned.

After the Alignment tab, the Font tab should be your next stop. Spreadsheets tend to be online documents, so choosing a sans serif or slab serif font will usually make for easier reading. You may also want to use a different font size for headers or sub-total and total labels. Again, remembering what generally works best online, you should choose a bold weight instead of an italic or oblique one. Choices from the Font Effects should be kept to a minimum, although you might want to change the default black font color. Mostly, the other Font Effects will add little to the effectiveness of your spreadsheets.

The remaining tabs are less essential. The Background tab may be useful if you need to brand your spreadsheet for corporate use, or possibly as another way to emphasize headers or totals. Probably the least useful tab for visual formatting is Borders, although you might want to give it some attention if you plan to print your sheet and believe that a spreadsheet should look like a spreadsheet even when it's printed. For both background and border options, you may prefer to use a page style if you want the same settings for an entire sheet. If that's not what you want, the cell setting overrides the page setting.


Functional formatting

The most important of the functional tabs is Numbers. This tab sets how input to a cell is interpreted and displayed. The default category is Numbers, which interprets any numerals you enter into the cell as numbers and displays them on the bottom right of the cell -- behavior that has puzzled more than one spreadsheet novice. If you don't want that formatting, then Text is a better choice for you. You may also want to create styles for displaying Date, Time, and Currency. The Numbers tab also has a wide variety of other categories that you might need, including Percent, Fraction, Scientific, and Boolean, all of which come with their own selection of format options.

The Numbers tabs also includes settings for the number of decimal places and leading zeroes in your calculations. The decimal place setting overrides the general setting in Tools > Options > OpenOffice.org Calc > Calculate, although you should select Precision as Shown in the same general setting Tab to avoid any potential problems.

The other functional tab is Cell Protection. The name is actually somewhat misleading, since the tab does not set cells to right-only, but sets options for hiding their contents. On Cell Protection, you can select whether to hide all content in cells that use the style, or only those that contain formulas. You also have the option of hiding the cell when printing the spreadsheet.


Setting up and using styles

Calc comes with five pre-defined cell styles: Default, Heading, Heading1, Result and Result2. If your needs are simple, you may not need to create others. Instead, you may prefer to tweak the pre-defined styles to your liking.

Another option is to create a separate style for each different formatting option or combination of options that you might want to use -- for example, Red Background, Currency or Default, Borders, Protected. You can save time if you remember that cell styles are hierarchical, and that you can make one style the child of another by entering its parent in the Linked with field on the Organizer tab.

Within the spreadsheet, applying the formatting is simply a matter of selecting the cells, then clicking the cell style to apply to it in the Styles and Formatting window. However, all this planning takes time, and you won't want to repeat it, so when you are done, don't forget to use File > Template > Save so that you can easily use the same design on other spreadsheets. As with any office application styles, the more you use similar documents, the more time you'll save in the long run by taking time at the outset to get yourself organized.


Bruce Byfield is a computer journalist who writes regularly for the Linux Journal and NewsForge websites.

______________________

--
Bruce Byfield (nanday)

Comments

Comment viewing options

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

Formating cells.

Anonymous's picture

Can I format a cell in calc to show degrees? For example if I type in 380° it would show 020°. I trying to make a calculator that could help me out with finding out the right degree when you calculate the difrenc between magnet pole and the north pole for example.

help!

melissaDee's picture

we've just gotten an openoffice cal for use...how to get the column/row to just 'write' what is written and not compute it...is it the same as ms?

awesome, thanks

Justin Cook's picture

This is exactly the info I was looking for!!! Can anyone tell me if the formatting is preserved when a spreadsheet is uploaded to Google Spreadsheets?

Problem

skrypty pozycjonowanie's picture

I have a working formula as follows
='Serial Numbers'.D19
If I try to edit the cell by even just deleting the digit 1 and typing 1 again, the cell just displays the formula in it, not the value it was supposed to look up. The only way to get it to work again is to copy and past from another working cell below it.
Even deleting and typing it in from scratch does not work.

Problem solved

programy php's picture

I got it..Cell seems to be formatted as text, changing the column over to "number format" worked just fine :)
Now, let's say I want to add the following to a new row in that column....0334872739.....it will drop the first 0 because the column is formatted as a number..

Good tips

Anonymous's picture

I'm using Openoffice from 5 months,only to view the spreadsheet price lists I receive by email, but after reading this article I could be start to use it for other purposes.

RE

Games Cheat's picture

I also started using it for basic spreadsheet stuff, but I'm now using more functionalities

OpenOffice.org Calc Date Styling

Anonymous's picture

I want to create a template style for displaying dates in headers and/or footers like this: 2007-01-03, dynamic or static.

Would that be possible?

OOo Questions

jJonathan's picture

You'll get more and faster answers (many from experts) by posting (emailing) your questions to users@openoffice.org.

If you do not wish to subscribe to this fairly active list, it's good to include a request for answers to be copied to your email address (as well as to the list). That way you'll see the answers without revisiting the list online.

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