SWiK
» tagged pages
» logout
OpenOffice
Return to OpenOffice

OpenOffice Calc Tips

(or Cancel)

(Editing anonymously: to be credited for your changes, login or register a new account)

other page actions:

Tags Applied to this Topic

1 person has tagged this page:
Tag this Topic | Tagging Details

OpenOffice Wiki Pages

See all 7 Pages
Syndication Feed on http://www.openofficetips.com/index.rdf

My contribution to the OOo community – including a blog with daily tips, templates and scripts for downloading, as well as links to other related websites. Enjoy.

Edit This

Thursday, February 02, 2006

Basic Functions: COUNTBLANK

The COUNTBLANK function allows you to total the number of blank or empty cells in a specified range. It is used as show n below in the example. Cells C3, B5 and D5 are empty - so the COUNTBLANK function...
posted at 10:31 AM. tags: .

Wednesday, February 01, 2006

Conditional Formatting: Comparing two Lists

We will use conditional formatting to identify unique entries between two lists. In the sheet below, we have two lists of US States. We would like to identify the list entries that are unique to each list. This technique can...
posted at 05:41 AM. tags: .

Tuesday, January 31, 2006

Entering Dates Quickly

Here is a useful formula - a long with a variant if you need to enter dates quickly without the delimiters between the days, months and years. In the example below, we enter our shorthand dates in column B. This...
posted at 10:17 PM. tags: .

Monday, January 30, 2006

Returning a Blank rather than Zero or Error

In the spreadsheet below, we wish to apply a formula to the values of a particular column - C and display the results in column D. However, the column is 'sparse' in that not all cells in the column have...
posted at 01:09 PM. tags: .

Monday, January 30, 2006

Toolbar Crazy

I'm not a big user of the OpenOffice Calc toolbars - but I was curious what it would look like if I activated them all... You can enable/disable a particular toolbar with the View - Toolbars menu option. As you...
posted at 01:09 PM. tags: .

Monday, January 30, 2006

Creating a 2-D Lookup Table

By combining INDEX and MATCH functions as shown below, we can create a lookup table whose data can be accessed by specifying the desired row and column values. This is probably best applied where the lookup table is of fixed...
posted at 01:09 PM. tags: .

Friday, January 06, 2006

A Sudoku Solver!

I have been wanting to do this for the longest time - ever since I became aware of this puzzle last year. So for the past week, I've been busy putting together this spreadsheet that will assist you with solving...
posted at 07:58 PM. tags: .

Friday, January 06, 2006

Data Validation: Drop down lists

For each cell, you can define in advance what type of contents is valid for that cell. This allows you to guide users through data entry in OpenOffice.org Calc by restricting cells to receive specific values and ranges. For selected...
posted at 04:28 PM. tags: .

Friday, December 30, 2005

Basic Functions: WEEKDAY

The WEEKDAY function returns an integer that represents the day of the week. The usefulness of this function is in identifying weekends in a series of dates. It is straightforward to use the filldown functionality of Openoffice Calc to create...
posted at 08:49 AM. tags: .

Thursday, December 29, 2005

Basic Functions: WEEKDAY

The WEEKDAY function returns an integer that represents the day of the week. The usefulness of this function is in identifying weekends in a series of dates. It is straightforward to use the filldown functionality of Openoffice Calc to create...
posted at 11:39 PM. tags: .

Wednesday, December 28, 2005

Splitting an address (US) into separate cells

The FIND, LEFT, MID, RIGHT and LEN text functions are extremely useful and versatile. We have seen before how to split a person's name into it's component parts. Here, we take a single cell containing a US address and split...
posted at 08:08 PM. tags: .

Tuesday, December 27, 2005

Financial Functions: Creating a Payment Chart

One of the most useful and widely used financial functions available in OpenOffice Calc is PMT. We have discussed this before. It's use is illustrated below in a simple example showing how to calculate the payments on a loan with...
posted at 12:21 PM. tags: .

Saturday, December 24, 2005

Merry Christmas

Here's wishing you all a Merry Christmas and a prosperous 2006! I have not been updating recently - but I plan to get back into the swing of things as soon as I can after the holidays. Right now, I...
posted at 01:25 PM. tags: .

Saturday, December 03, 2005

Financial Functions : Balloon Loan Example

A balloon mortgage is a short loan, lasting five to seven years, but the payments are based on a term of 30 years. Balloon loans often have a lower interest rate, and can usually be easier to qualify for than...
posted at 05:38 PM. tags: .

Wednesday, November 30, 2005

Converting time to minutes past midnight

To convert a time to the number of minutes past midnight, just multiply by 1440 - as shown below. You also need to make sure the result cell is formatted as a number....
posted at 10:34 PM. tags: .

Wednesday, November 23, 2005

Sheet/Cell Protection 101

There are a number of situations where it is desirable to take advantage of the sheet/cell protectection features of OOo Calc. Perhaps you want to prevent the values for a range of cells from being overwritten. Perhaps, you want to...
posted at 01:18 AM. tags: .

Monday, November 21, 2005

Regular Expressions 101

This is a topic that you will best learn by trying out stuff for yourself - but here I summarize the basics... There are a number of builtin functions that make use of regular expressions. Here, we use COUNTIF to...
posted at 08:01 PM. tags: .

Monday, November 21, 2005

Google Analytics

From the company that seems to do everything right... Google Analytics allows you to take a closer look at the folks that visit your website. One of the coolest features is the ability to create a distribution map - below...
posted at 07:01 PM. tags: .

Tuesday, November 15, 2005

Charting: Modifying the axis scaling

By default, OOo Calc will create a chart with the y-axis starting at 0 (zero). For most purposes, this is adequate, but it is quite straightforward to shift the axis up (or down) where desired. For the Flash version of...
posted at 11:00 PM. tags: .

Monday, November 14, 2005

Naming Multiple Cell Ranges

Consider the simple example below - a table with various columns of data. Any formulas we create would be much more readable if we assigned names to the columns of the table. With the full table selected, choose Insert -...
posted at 11:40 PM. tags: .
Page 1 | Next >>
Username:
Password:
(or Cancel)