OpenOffice.org Function Toolbar Buttons. In all applications of OpenOffice.org, the function toolbar is the most alike. It appears at the top of the OpenOffice.org application window and has basic commands that can be used for a variety of tasks. Use this handy guide for help. Enhance Apache OpenOffice. Pick your Template. Get OpenOffice Search templates. Search templates; Highest rated; Most popular; Most recent; Filter. Spreadsheets are used for What-if calculations. Change one number in a spreadsheet and all the calculations in a large spreadsheet will re-calculate, will automatically change. Open A Spreadsheet. IF on the desktop, click All Programs OpenOffice.org 2.3 OpenOffice.org Calc.
Do you need to make a database, but fear it’s too much of a pain or you don’t have the right tools? Don’t worry: it’s easy, free, and useful, too. Use the free OpenOffice.org office suite to get your data in shape for mail merges, queries, or useful analysis of your business data.
Not everyone needs a database. But sooner or later, anyone who has to keep track of and retrieve important information is going to benefit from one. Databases are for storing data, of course, but their benefit comes in letting you get at the data you want, and in displaying or printing it the way you want. For instance, once you have a database, you can, of course, do mail merges—whether it’s a letter to each of the 500 people you met at a free software convention or printing labels for each of the 870 legal DVD backups you own. Databases are also necessary for setting up data entry or data viewing forms; creating relations between tables so you can print connected data; or for creating a list that shows the average number of items your customers bought per purchase during the holiday shopping season.
Not everyone needs a database. But sooner or later, anyone who has to keep track of and retrieve important information is going to benefit from one
Once you’ve decided to create a database, the way ahead looks simple. You just choose:
File→New→Database
However, then one last question remains: what type of database do you create? Do you connect straight to your Oracle database? (This is best only if you are or have access to an Oracle guru.) Do you investigate some of the simpler choices such as spreadsheets, or if you are well informed or psychic, do you choose the entirely invisible option of importing existing data?
If you’re a database god and enjoy spending time deep in a database—well, you can do anything you want. If you want the most results for the least trouble, here’s what I recommend.
- Get your data in a spreadsheet, then create a database file that can read the spreadsheet. This is simple and if you don’t need advanced database features, it’s all you need. See Creating a simple database that connects to a spreadsheet.
- Or get your data in a spreadsheet, and paste it into Base to create a native Base database. This involves slightly more work but gives you a great deal more power. See Creating a native OpenOffice.org Base database.
Creating a simple database that connects to a spreadsheet
The simplest approach is to simply create an OpenOffice.org database file that points to a spreadsheet chock full of data. The spreadsheet doesn’t have the power to do mail merges and the other database-related features I mentioned earlier, but the database file pointing to it can. It acts as a middle man saying “The data’s over there, with these fields—go get it”.
The simplest approach is to simply create an OpenOffice.org database file that points to a spreadsheet chock full of data
You either have your data in a spreadsheet already, or you can get it into a spreadsheet pretty easily. You’ll want to have it set up to look something like this, with field labels across the top, and with each piece of data separate. See figure 1.
Now you’re ready to create the database. It’s pretty simple.
1. Choose
File→New→Database
.2. In the first window, select Existing Data Source, and Spreadsheet type. Click Next. See figure 2.
3. In the next window, point to the full path (I’ve just shown the filename here) of the spreadsheet you want to use. Click Next. See figure 3.
4. Leave all the checkboxes marked. You don’t need to edit the database but the first time, at least, it’s good to take a look at what the main database window looks like. Click Finish. See figure 4.
5. Name the database. This name will show up when you do mail merges, and it’s the name and location you’ll look for when you want to do things with the database (like create queries or reports). Click Save. See figure 5.
6. You’ll see the database. Click the Tables icon at the left, and select a table name. Each sheet containing data in your spreadsheet will become a table. Now, on the right side, instead of None, select Document. You’ll see the data in the spreadsheet. See figure 6.
There’s nothing else you have to do. Here’s a little more information, though.
Updating the data
When you have more data, just add it to the spreadsheet. When you want more tables, just add the data to another sheet in the spreadsheet.
When you have more data, just add it to the spreadsheet
What you can’t do with this type of database
There are some fairly cool features that you might or might not need that you won’t have available. You can’t, for instance, use the functions that can tell you: for every invoice, what was the average total amount on the invoice. You can’t create data entry forms. You can’t join two tables to relate the data. But you can create queries, and reports, and data view forms. (Click the Forms icon on the left side in your database editing window, and use the wizard to create the form.) See figure 7.
If you want all the power that Base can provide, and you don’t have Access or Oracle or another engine sitting out there to connect to, then you’ll want to create a native database. This is similar to start off with, but with a lot of extra steps to define the tables, and a lot of extra steps to bring the data in.
If you want all the power that Base can provide, and you don’t have Access or Oracle or another engine sitting out there to connect to, then you’ll want to create a native database
Creating the database
1. Choose
File→New→Database
.2. In the first window, choose to create a new database. Click Next. See figure 8.
3. Leave the checkboxes with the default selections and click Finish. See figure 9.
4. Just name the database and click Save. See figure 10.
5. The database appears. See figure 11.
Creating tables and entering data by pasting
There is a long, traditional way to create tables. You can do it in design view, or using the wizard. See figure 12.
This article is about telling you how to get the most bang for your effort buck
But this article is about telling you how to get the most bang for your effort buck. Mi pc suite for mac. So, I’m skipping that and going straight to the easy, invisible way. This way is an advantage only if you have the data out there somewhere already: in another database, in some CSV file, in an Excel or Openoffice.org Calc spreadsheet, or even in a big HTML table. And that is the case most of the time.
1. Get the data into a spreadsheet. You can export the data from your database to a spreadsheet or CSV, or copy it from an HTML table if it’s not there already.
- From HTML: Copy the table, then click in one cell of a spreadsheet and paste.
- From CSV (comma-separated files): In OpenOffice.org choose
File→Open
. In the File Type list of the Open window, select Text CSV (click in that list and type T four times). Select the CSV file and click Open. In the window that appears, verify that the settings are correct for the data, then click OK. - From Excel: Just open the spreadsheet in OpenOffice.org. See figure 13 and Figure 14.
2. Make sure the data is set up right. Make sure there are field headings, that the data is granular enough, etc. If you don’t have a unique primary key field such as an ID, don’t worry; you can create one on the fly as you add the data.
3. In the spreadsheet, copy all the data including the headings.
4. Go to the database file and click the Tables icon at the left side. Urdu logo maker software, free download.
5. In the Tables area, right-click and choose Paste. See figure 15.
6. In the window that appears, type the table name you want, and choose to add both the data and the definition, or just the definition if you want to significantly edit the table definition. You can also choose to create a new field that will function as the primary key. It’s a good idea to paste just the definition first, if you want to set one of the fields you’re pasting in as the primary key. Then paste again and just paste data.
Click Next. See figure 16.
7. Add all the fields that you want in the table, then click Next. See figure 17.
8. Make any changes to the field definitions. Click Create. See figure 18.
9. The table will appear in the database. Select the table, and from the right side choose Document rather than None, and you’ll see the data. Close the database, saving changes. See figure 19.
You’re done—you’ve got a fully functioning native Base database with a table. You can create views, relations, data entry forms, reports, write SQL queries on the data, and anything else that Base provides.
Adding more tables or pasting data into a table definition
Just repeat the pasting process with another set of data.
Changing the table definition
Right-click on the table name and choose Edit. See figure 20.
You’ll then see design view, where you can change some aspects of the definition. For instance, to make a field auto-value, just make it an Integer type, then choose Yes under AutoValue at the bottom of the work area. See figure 21.
If you want to change the primary key, right-click on the field you want to make the primary key and select Set Primary Key. See figure 22. Note: This works only if you are pasting just the definition, not the definition and the data.
Updating the data
To add data, paste the new data again the same way you did before—select the data and choose Paste. In the window that appears, just choose to Append, and make the same choices you did last time. See figure 23.
Alternately, double-click the table name and change or add the data just by typing.
Another approach, if you’re going to need to do a lot of updating, is to create a data entry form. Click the Forms icon on the left side of the database editing area and use the forms wizard to create the form. Be sure to state, when prompted, that the form is for both viewing and entering data.
I should emphasize that with all data, be sure that it is set up correctly in the spreadsheet before you paste it in. Be sure columns names are correct and that the data is granular enough. Also, it looks like creating the primary key in the first window of the wizard, when prompted, is the only way to do it. If you want to change it later, you can’t because the table already contains data. A way around this might be to paste only the definition first, don’t set the ID as the primary key when prompted, and then set the appropriate field as the primary key by editing the table definition. Then, paste just the data.
This process reinforces a general theme about OpenOffice.org advanced features—basically, that many things are possible but they are not obvious. If you don’t see a button or a checkbox for something in the interface, that doesn’t mean it’s not possible. Keep looking.
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.
Openoffice Calc
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.
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”
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.
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.
Openoffice Spreadsheet Download
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
Openoffice Spreadsheets Sum
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
Openoffice Spreadsheet Header
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.
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
How To Wrap Text In Openoffice Spreadsheet
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.
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.
Next post: TCP/IP Protocol Fundamentals Explained with a Diagram
Previous post: How to Create and Delete VLAN on Cisco Catalyst Switch