≡ Menu

20 OpenOffice Calc Spreadsheet Tips and Tricks

OpenOffice works on both Linux and Windows. On Ubuntu, OpenOffice is the default office suite. On Windows, instead of spending money on Microsoft Office, download and use Openoffice.

OpenOffice Calc offers pretty much most of the functionalities that are in Microsoft Excel. The following is the list of some Openoffice calc spreadsheet tips and tricks.

1. Autoformat Tables

Instead of manually formatting your tables with different font colors and background, you can use one of the builtin table autoformat as shown below.

Select the table in the spreadsheet, and click on Menu -> Format -> Autoformat.

2. Conditional Formatting

Using conditional formatting, you can format a specific cell or row only based on a certain condition. For example, if the cell value is “Technology”, you can set a custom background, and font to the cell or row.

You can also combine multiple conditions using OR/AND conditions. To use this, click on Menu -> Format -> Conditional Formatting.

3. Create List of Values

To create list of values based on the data from the columns, select the column, click on Menu -> Data -> Validity. From “Criteria” tab, select “List” in the drop-down for “Allow”. In the Entries text box, enter the list of values that you want to be created as part of the list of values.

Once you’ve created the list of value, click on any cell in this column, which will show the drop-down list with the values you just created as shown below.

4. Protect Sheet or Document

You can protect the current sheet of the whole document, by clicking on Menu -> Tools -> Protect Document -> Sheet (or Document).

The following shows the protect sheet dialogue. Enter the password to protect the sheet.

Once you protect a sheet, if you try to modify the content of the cells, it will give the message “Protected cells cannot be modified”

5. Charts

Just like you would expect, you can create various types of charts. Click on Menu -> Insert -> Chart, and get started.

6. Change Status Bar Values

When you select range of cells that contain numeric values, you’ll see the sum of them on the right side of the status bar. You can change this to other values (for example, average) by clicking on the status bar and changing it as shown below.

7. Play Game Inside Calc

You can play games inside the OpenOffice calc by typing =GAME(“StarWars”) in any of the cell. This will launch the following starwars game. We mentioned this earlier in our Linux Easter Eggs.

8. Meet the OpenOffice Developers

Type =starcalcteam() in any of the cell, which will display the picture of OpenOffice development team members.

9. Use Functions

There are several standard functions available in calc that you can just insert it to a cell and use it. There are almost 100 different functions available that are grouped into more than 10 categories.

To use the functions, click on the cell where you want the function, and Menu -> Insert -> Functions.

10. Navigator

Navigator windows gives a quick way of navigating around the spreadsheet.

To launch the navigator, Menu -> View -> Navigator.

11. AutoFilter

You can automatically create a filter using the 1st row (header row). Click on Menu -> Data -> Filter -> Auto, which will create the auto-filter as shown below. Using this filter, you can filter the rows based on the data in it.

12. Standard Filter

Using standard filter, you can filter data based on 4 conditions. You can combine these conditions using AND / OR conditions.

To use standard filter, click on Menu -> Data -> Filter -> Standard Filter, and provide your values as shown below.

13. Advanced Filter

Using advanced filter you can add 8 filter conditions. The advantage of this method is that you can add the conditions in the spreadsheet itself as shown below.

To use advanced filter, click on Menu -> Data -> Filter -> Advanced, and select the range of cells in the spreadsheet that contains the filter information.

14. Filter Using Regular Expression

You can also filter data using regular expressions. To do this, click on Menu -> Data -> Filter -> Standard Filter -> More Options -> select the “Regular Expression” check-box.

15. Insert -> Hyperlink

You can insert different types of hyperlinks to a cell. Apart from the standard http:// hyperlink, you can also link to another document as shown below.

16. Format Data

For format cells (or rows, or columns), right mouse click and select “Format Cells”, which will display the following format dialogue.

17. Create and Use Range

You can select a range of cells, and assign a name to them. Later when you use the range name, all those cells will automatically be selected.

To create a range, select the cells, click on Menu -> Data -> Define Range and give the range name. To select the range, you can use “Navigator”, or Menu -> Data -> Select Range.

18. Create Subtotal

You can automatically create subtotals. Click on Menu -> Data -> Subtotals, which will display following window. From here select the “Group by” column, and the subtotal column. In this example, I want subtotal of salaries of all the employees who belong to a certain department.

Following is the final output where subtotal of salaries (grouped by department) are automatically displayed.

19. Outline

Click on Menu -> Data -> Group and Outline -> AutoOutline, which will display the outline on the left-side of the cells. From here you can click outline indicator to expand or collapse certain group of rows as shown below.

20. Record and Play

Using Macros, you can Record any activities you perform inside the spreadsheet, and replay those activities anytime you want it.

To Record: Click on Menu -> Tools -> Macros -> Record Macros -> [Do all your work] -> Click “Stop Recording” -> Save the macros as shown below.

To Replay: Click on Menu -> Tools -> Macros -> Run Macros -> Pick you macros name -> Run.

Add your comment

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

Comments on this entry are closed.

  • John Piers Cilliers October 31, 2011, 2:53 am

    Correct me if I am wrong, LibreOffice is now the default Office Suite in Ubuntu 11.04 and 11.10.
    Thanks for the article, you have an excellent website, I come here quite often!

    Thanks, Piers.

  • rameshkumar October 31, 2011, 2:59 am

    simple and legible !!..thanks for sharing your valuable time and knowledge ..

  • Simon October 31, 2011, 5:43 am

    Ramesh, considering OpenOffice nearly died through mismanagement by Oracle and the project is now in incubation with the Apache Foundation with an uncertain future (https://blogs.apache.org/OOo/), I would have thought promoting the LibreOffice fork (http://www.libreoffice.org/) would be the way to go or at least mention it is available as an alternative. Since they have forked from OO the project the base code has been cleaned up, the program now runs substantially faster and new functionality incorporated . For the record, LibreOffice is office package that is now incorporated into Ubuntu (http://www.ubuntu.com/ubuntu/features/office-applications), not OpenOffice. OpenOffice is only included by default on old versions of Ubuntu.

  • ali November 1, 2011, 11:24 am

    HI
    thanks fore good articles
    from 11 version of ubuntu LibreOffice is the default

    there’s another tutorial in full-circle magazine too: here.

  • prabha May 26, 2012, 1:29 am

    I want to create a form in open office in the spread sheet itself and around more than 150 products. Is it possible?

  • Oran Kangas July 22, 2012, 2:27 am

    I am running version 3.4.0 of Apache OO calc.
    When I enter a formula into the Input line, say =count(A2:A4), everything is fine until I hit Enter. At that point the cell displays Err:509. When I click on the cell, I find the formula has been changed to =COUNT(‘2:’4).

    That happens for every cell, any formula: all letter A’s are being replaced by single quotes.

    Can you give me a clue as to how I fix this?

    Thanks,
    Oran

  • Stewart October 17, 2012, 8:32 pm

    Hi everyone.

    I have created a drop down box which works great. Is there a way that I can restrict users to only being able to enter text from the drop down box?

    I have tried protecting the cell but then it stops access to the drop down box.

  • Rick Walker November 30, 2012, 8:26 am

    I want to create a calendar pop-up in a cell using openoffice calc. Can you help?
    Thanks

  • Karthick January 24, 2013, 7:27 am

    Can anyone help me to create macro for Standard Filter

  • Judy Dahlem May 5, 2013, 3:58 pm

    I am desperate to find a way to duplicate the “accounting” format in Excel for OpenOffice. This cell format allows me to enter checks and deposits in my personal spreadsheet without having to enter the decimal. If I enter 17542 into a thus formatted cell, in Excel,the result is 175.42 when I hit enter.. The decimal is inserted two places to the left for me. Since I enter all of my checks, debits and deposits in prep for tax time, this format saves me a stroke for every entry. It’s like using the add2 setting on a calculator. I’ve been looking for this ever since I learned about OpenOffice a number of years ago. Thank you. JD

  • Abhishek September 4, 2013, 8:28 am

    I installed open-office packet, but when i trying to open a file from this it’s not working.

    I have tried some basics that are :

    $ whereis ooffice
    ooffice: /usr/bin/ooffice

    sooffice& file.xlsx
    ooffice& file.xlsx
    ooffice file.xlsx
    soofice file.xlsx

    Please give me any suggestion

  • Tim January 14, 2014, 2:00 pm

    When it comes to productivity suites, Office is the worst one in the world, except for all the others. Anyone who says that OpenOffice Calc, or even LibreOffice Calc, comes anywhere close to matching Excel for functionality really isn’t doing much in Excel. Lists (now called Excel tables), conditional formatting, automation, error tracking, and pivoting and cube functions are just some of the areas where Excel leaves LibreOffice for dead. Even Excel 2003 is better than the latest version of Calc, whose UI is stuck in the nineties (and this from the Ribbon’s biggest hater). And Wine breaks enough of Excel’s functionalities to make that a dead end, too.

  • Anonymous February 1, 2014, 11:51 pm

    Thank you the article is very helpful.

  • rajiv April 27, 2014, 2:24 am

    Sir,
    Able to add new button and submenu in the calc menubar bar modifying CalcCommands.xcu & menubar.xml files , when cursor moves on the new menubar button the green background comes & added sub menu items are displayed but the green background does not comes when cursor moves on the sub menu button. The task is attach a functionality to sub menu button like on clicking the button a document or image gets open. Plz guide approach how to approach the problem…….
    thanx in advance
    regards,
    Rajiv
    PUCSD
    Pune Uni,
    India

  • venkatesh May 18, 2014, 6:26 am

    i want drop down calendar list

  • Drazen May 20, 2014, 4:57 am

    Having problem vith subtotals.
    After subtotaling and colapsing all groups to better see and compare all subtotals, I can’t remove all subtotals to get clear source list again. What I get are hidden all records after first line. Setting: Data-Subtotal-Group_by to -none- gives that above.

  • Jave October 30, 2014, 3:57 am

    i didnt get the game and team function. I tried applying it but failed

  • sunny January 20, 2015, 2:11 am

    I have below decimal numbers in excel
    0.15
    2.30
    0.55
    0.25
    0.15
    1.00
    1.40
    2.00
    2.00
    This is basically the total time spent, If i sum up these using the sumation in the excel i got the result as 9.80. But actual sum value is total 11.00 hours.

    How can i get this sumation value as per hours and minutes

  • Debbie Shaw January 28, 2015, 8:09 pm

    In excel, I use to be able to cut and insert that row of data before another row by left clicking my mouse or click a row of data, then the “move” symbol would show and I could move that row of data in front of another row.
    How do I do this now in this program

  • Richard Goeloe July 10, 2015, 8:40 pm

    I am using Openoffice 4.1.1.
    I tried hard and long to protect a cell (range) by means of a formula. I tried everything and looked everywhere for advice. I am now convinced that this option does not work with conditional formating using formula.
    Maybe you can help me by giving me an (g00d) example, or if this is a bug in Openoffice Calc please send me the address where i can find the solucion.
    Thank you

  • osman T Sesay July 30, 2015, 10:31 am

    thanks very much i have gain more knowledge in spreadsheet that before with options given. once more thanks

  • jennifer March 3, 2016, 8:44 pm

    what i am looking for is a way to make a time sheet that is for technicians that will keep track of there time at each location. for instance time in, time out for that job and when they get to the next job a time in and time out and so forth. then to add up the time at the end of the day taking in account the time from am to pm during noon. i am having trouble making it recognize the 12:00 time as being pm instead of am and adding the time instead of subtracting the hours from an am time. that might sound confusing.. but whenever i put in a starting time that is after noon it puts it in as an am time and i can’t seem to be able to change that. I know i can do it with using military time but i am wanted to use regular time since military time for most people is a time waster of counting to what time it is in military time.

  • Christopher Burke June 28, 2016, 5:38 am

    Not that it matters much, but the Easter Eggs don’t seem to work in the latest version of Open Calc. I tried theGAME=(”Star Wars”) and the =starcalcteam() one and just got NAME? error message for both.

    They must have taken them out. Sad!
    Chris