Switching from Microsoft Excel to Openoffice Calc

[Andrew]

I thought I'd post my current experience of switching from Microsoft Excel spreadsheet to Openoffice Calc spreadsheet. I've only done some basic stuff in it so far. I'll make more posts as I run into some things.

So I had a 3.17mb spreadsheet with around 120,000 cells and many formulas and 18 tabs (worksheets).

So far openoffice takes about 10-11 seconds to open the spreadsheet. Excel takes about 1-2 seconds. The programs are not currently running when I open the spreadsheet (both seem to take about 1-2 seconds to start).

Since time to implement is important for me, I took my 2007 excel spreadsheet and opened it in Calc, then saved it as is in odf format. I don't have time to retype every formula and piece of info, so it would be impractical to start a blank spreadsheet. I simply delete the data from previous year, leaving all formulas and fonts/columnwidths/etc intact

It looks to have converted the formulas correctly. Switching say "!" to "." and "," to ";" and calculating everything.

One feature I am currently missing that is not available in Calc is the Accounting text format. This made reading dollar numbers much easier and cleaner. I found out this was not possible by googling within 5 minutes.

Another difference is when I was deleting stuff and pressing the "delete" key a confirm dialog would always popup. This did not occur in Excel. Googling this nuisance I found an answer within 1 minute (first result).

I found out that using the backspace key deletes the data without confirmation box. I was happy.

I'm still learning some of the formula differences such as Calc using ";" instead of Excel ","

Although this should be easy since Calc already converted all my formulas, so I know how to do them (look at how it was done, then copy it).

One thing I noticed was in Excel I was using this in 4 out of 180 similar formula (typos):

=SUMIF(C!$X$28:$X$1700,'W Data'!$B28,C!$E$27:$E$1700)

Excel correctly (I presume :P) summed the data anyway.

In Calc it gave an error. This showed me that the formula itself had an error as I had accidentally put $X$28 instead of the correct $X$27 I was putting in the other parts of the worksheet. Excel does not show anything wrong, which I guess is ok since it worked, but I thought it was neat that Calc would not calculate this (or bad). Now this may become a problem in the future if I ever need to use different rows within that type of formula, but I'm not sure that I would.

Several things work similar such as freeze pane (both are found in window->freeze).

I've still got about 10 hours worth to get the spreadsheet up to date and maybe 40 hours to add a crapload of more calculations I've been wanting to add for years. Only problem is the size of the spreadsheet grows and it slows down (I solved this problem a year ago by purchasing a new computer). I've tried splitting the spreadsheet into several different spreadsheets (files) using excel, but the formulas gave errors and I couldn't figure out how to get the two spreadsheets to work together. Most simple formulas worked, but some of the ones I needed did not work. I already do have spreadsheets taking info from main spreadsheet and calculating 10s of thousands of numbers, but I havn't updated that in a year or two and it needs a complete overhaul.

======================

There is one formula in Excel (and I presume Calc as well) That I could not figure out and am pretty sure not possible by the software.

Say I have

1. =COUNTIF(A12:A1602,A1) with a12:1602 pointing to formulas using

2. =IF('C Data'!AA27 >50, 'C Data'!AA27,"")  With 'C Data'!AA27 pointing to

3. =IF(I27=$F$2,DATEVALUE(H27&" "&G27&$A$2),"")  which is pointing to a date. (aka May 7, 2007 and converting to numeric value)

So to summarize formula 1 outputs a date in numeric format (eg 39248)

Now I have about 10 formulas using

=COUNTIF(A12:A1602,A1)  which is basically counting all the formula 1 and the if statement being A1 which is in a cell of its own:

<=39370 which is supposed to be todays date (back then) which I have to manually update each day (or whenever I want formulas to recalculate).

For whatever reason Excel does not allow

=COUNTIF(A12:A1602,<=today())  with the <= and function today allowed in a formula. I've tried to figure this out for hours last year, but could not figure it out. So I opted to simply make formulas point to one cell which I would manually update whenever I needed up to date info to compare this year to previous years.

======================

So far openoffice calc works fine (although slow to start), I'll just have to relearn how it does a couple things. Hope I don't run into any show stoppers. Good thing now is I can open my spreadsheet in any operating system since openoffice will run in windows/mac/linux, and run it off my usb sticks if needed.

Openoffice 3.0 Beta was released today. I'm guessing it is the first of many betas since the final release is not until September.

3.0 beta features.

I havn't tried it yet. Will probably wait for a couple more betas.

And sorry for using formulas instead of formulae. Guess I'm not used to saying that word :)

[Andrew]

Yesterday I quickly started a new blank spreadsheet and put some table information on a single page. I printed it and it printed a header with the filename and a thing at the bottom saying page 1 of 1.

I'm not sure where the option to disable calc from printing this header and footnote that I did not tell to print. It's kinda like when you print from notepad it adds the header/footnote

EDIT:

found a solution at Print A Spreadsheet - Headers and Footers

Found at googles 4th link.

Glad an option is easily available.

[D2k Sardaukar]

I'm also slowly switching to Calc at the home PC. There indeed sometimes things that go a little different in Calc than Excel, but most of the stuff you need both have.

[Dunenewt]

I'm holding out for OpenOffice 3's full release.  I have OpenOffice 2.something on my home PC, but I'm waiting until September for my laptop. 

[Andrew]

So I have created some notes (excels equivalent is comments).

Once created, and I want to edit it I have to right click and "show note". Then I double click on the note that stays showing. Once done editing the note stays showing and does not go away until I select "show note" again.

This is annoying and there must be a way to edit a note without having to select show note twice.

I've read at one blog a keyboard shortcut to edit a comment is ctrl+F1, but that only temporarily shows the comment and I cannot edit it (it disappears when I click anywhere).

[Andrew]

Just had my first crash. NOOOO.

I was doing some formulas and stuff and accidently double clicked on a cell to edit it but sometimes it tries to select multiple cells or soemthing which then makes it impossible to click into a cell (This happens often and I have no idea what does this). I usually just undo (ctrl+z) and can then edit cells again, this time it caused calc to crash.

It went to recovery mode and saved what I was working on, then restarted calc and recovered the document. It seemed to do an ok job. I only lost about 2 minutes worth of work (I think 2 operations which were copy/paste). I sent a bug report that popped up.

I'm working on a part of my spreadsheet I created last year and have no idea how I came up with it. So complex and messy. Trying to compare numbers from previous years to current year (ahead/behind comparing same date (year to date)). EDIT: figured out how to do it again and the results show I am on par with last year. I was expecting to be behind, but thanks to this I can now monitor the year to date differences.

[Andrew]

I just found a possible bug with a formula.

I have for example

=IF(a$1>SUM(b$1:b$1);"text";"")

Now I want to get rid of the $ in last E$502 so that it looks like

=IF(a$1>SUM(b$1:b1);"text";"")

so that I can copy/paste formula over 25 cells so that it expands. But when I try to remove the $ it auto inputs it back when I press enter. So no way to remove the last $.

It will allow me to do

=IF(a$1>SUM(b1:b1);"text";"")

It seems to be a bug when the a$1:a$1 are the same cell.

Hmm, it actually seems that if I put

=IF(a1>SUM(b$1:b1);"text";"")

It inserts $ into both spots in front of cell number.

Really messed up and a really important feature since I have several thousands cells using this type of formula. Can anyone duplicate it? It happens in new spreadsheets too!

As an example use:

=IF(A$1>SUM(B$1:B1);"text";"") 

Put into say cell C1 and see if a $ is input next to last B1

Anyone have OOo 2.4.x installed? try it on that and see what happens.

I'm looking through

openoffice issue tracker

with no luck.

[Andrew]

I decided to check 3.0.1 release schedule and then checked the show stopping bugs that must be fixed, and the bug I was having is one of them!

Cannot mix relative and absolute addressing in one-cell range references.

And it is shown as fixed.

Hurray opensource!

[Andrew]

A really annoying bug I noticed this summer was if I put a "note" on a cell in calc, then insert more rows above the cell that the note was created, the note sticks with the row #, and does not move down where the new row is.

To test:

start new spreadsheet. Add a note to cell c20. select row 2-15 and right click->insert rows.

Look at the note and notice how it has not moved, it just made a long line down to where the cell is.

Looks like it was reported in 2007, but not fixed. Although a similar problem was fixed

It's a pretty obvious bug.

boo opensource.

EDIT:

After doing more research, the specific bug is listed here and reported in December 2005. On oct 15 it was reported that a fix has been made and it will be released with openoffice 3.1

The bug was marked an "enhancement". Kinda like Firefox memory consumption in 2.x series?...

Sadly it only mentions sorting in the fix, nothing about adding rows. So who knows!

It is listed as a severe bug at 2008 Q2 Review of Spreadsheet Project

Hurray opensource in 6 months...

I'm going through my spreadsheet and the source of the note is on cell A802, yet the note is all the way back up at row 676. Real pain moving them.

[Andrew]

For future reference here is a decent "fork"? of OOo which has more features and bugfixes.

Go-OO!

Apparently it already has the bug Cell notes do not move when cells are sorted in Calc fixed which apparently won't be seen in OOo until 3.1 in March.

The changelog of fixes is found here (warning, big list)

[Andrew]

So, I just received a .XPS file from someone. Openoffice can't open it.

Looking around since I had no idea what .xps is, supposedly it is a proprietary microsoft .pdf competitor. Apparently vista/office 2007 is where this comes from (hurray more vendor lockin).

When I try to open it in ubuntu, it opens with file archiver and shows lots of files in it. Sounds like a pretty bad file format.

http://en.wikipedia.org/wiki/XML_Paper_Specification

Apparently Okular can view the files, but I'm not installing 40mb of software for a single file. Guess they'll have to send a .pdf

EDIT:

OOo 3.1 should be released in 2-3 weeks

http://wiki.services.openoffice.org/wiki/OOoRelease31

[Dragoon Knight]

I had a look around on Google - apparently, this file will open under Vista no problem, as it has an XPS reader built in.  You should also be able to view it in XP by opening Internet Explorer - if you have a machine with Windows on it, you might want to give that a shot, though older versions of IE may still have problems.

Also note that Firefox, Opera, etc. won't open this file type.  Hope this helps.

[Andrew]

Thanks. The person simply dropped off a printed copy of the file :)

What word processing program exports to .xps by default?

I would have thought the person would be using vista/office 2007 and would have .docx

Not sure what the person was using (older person, probably not technically inclined).