Skip to content

TechRepublic

  • Top Products Lists
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • TechRepublic Academy
  • Innovation
  • Cheat Sheets
  • Big Data
  • Tech Jobs
  • TechRepublic Premium
  • Top Products Lists
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • TechRepublic Academy
  • Innovation
  • Cheat Sheets
  • Big Data
  • Tech Jobs
  • See All Topics
  • Sponsored
  • Newsletters
  • Forums
  • Resource Library
TechRepublic Premium
Join / Sign In
Software

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

By greg shultz July 30, 2007, 7:24 AM PDT

Image
1
of 10

157149.jpg
157149.jpg
Performing common Excel tasks in OpenOffice.org 2.0’s Calc

DataPilot provides you with an easy way to ferret out information hidden in a spreadsheet

DataPilot provides you with an easy way to ferret out information hidden in a spreadsheet

This gallery is also available as a TechRepublic article.

Many folks may be a bit apprehensive about making the switch from Microsoft Office to OpenOffice.org because of the learning curve. Sure the cost difference, several hundred bucks verses free, is very, very attractive! But is that savings going to be eaten away by training costs and lost productivity? Fortunately, the answer is no! OpenOffice.org 2.0’s cadre of open source developers has spent a great deal of time in an attempt to make the two office suites as alike as possible in both the feature set and more importantly in the user interface. And by and large, they’ve succeeded!

Realistically speaking, it will take some time to make the transition as many features and settings are named differently and are in different locations, but the names and locations aren’t too far of a stretch of the imagination and it’s easy to make the connections. As such, it won’t take nearly as much time as you might think for seasoned Microsoft Office users to train themselves how to use OpenOffice.org 2.0.

Of course every business generates data of some sort and since the early days of Lotus 1-2-3, business people have been have been entering and manipulating data in electronic spreadsheets. When it comes to this application, OpenOffice.org 2.0’s Calc provides you with everything you need and then some. With this in mind, let’s take a look at performing some common Excel tasks in OpenOffice.org 2.0’s Calc.

Massaging numbers with the DataPilot

While the object of putting numbers into a spreadsheet is to make it easier to analyze the data, often times important trends or even problems can become buried deep with the numerous rows and columns of a huge spreadsheet. In order to help you ferret out that hidden information, filter it, and then explore it from a number of angles Calc provides you with a feature called DataPilot, which of course is designed to be emulate Excel’s PivotTable feature.

To see how DataPilot works let’s suppose that you have a spreadsheet that contains sales data for 26 products in a catalog. Each line in the spreadsheet contains the product name, the product category, product price, quantity sold, the sales representatives name, and the total sales figure for that product. While this spreadsheet is very informative as it is designed, suppose that you want to calculate the sales total for each rep?

With DataPilot, you can extract this information easily. To begin, select all of your data including the column titles. Next, pull down the Data menu and select the DataPilot | Start command. When you see the Select source dialog box, just click OK. At this point you’ll see the Data Pilot dialog box, which contains a layout template and buttons labeled the same as your spreadsheet’s column titles, as shown above. Now, drag the Sales Rep button and drop it on the Row Files section of the template, Then, drag the Total Sales button to the Data Fields section.

When you click OK, Calc will generate a new table, based on the layout configuration, that contains a list of each of the sales reps and their respective sales totals, as shown above. This information is in the larger spreadsheet, but it isn’t easily discernable.

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

The Filter dialog box allows you to dig deeper into the data that you've extracted from the larger spreadsheet

The Filter dialog box allows you to dig deeper into the data that you've extracted from the larger spreadsheet

If you want to see other trends in this new table, you can click the Filter button right above the table and use the settings in the Filter dialog box to extract other information. For example, you might want to find out who had the most $25 sales, as shown above.

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

The AutoFormat Chart wizard makes creating charts in Calc a snap

The AutoFormat Chart wizard makes creating charts in Calc a snap

Creating charts

As any spreadsheet guru will tell you, being able to translate your data into a visual display allows you to make an instant impact when presenting your analysis to colleagues. By starting out with a chart, the actual numbers seem to be more intriguing and creating Charts in Calc is a snap. Unfortunately Calc’s graphics rendering engine leaves a little to be desired when compared to Excel, but it gets the job done.

You’ll begin by selecting all of your data including any column and row titles. Next, pull down the Insert menu and select the Chart command to display the AutoFormat Chart wizard, as shown above. If you want Calc to use the values from the first row and column as labels for the chart’s X- and Y-axis, select the First Row As Label and the First Column As Label check boxes. In order to have as much room as you can for the chart, use the Chart Results In Worksheet drop-down to select an available sheet.

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

The AutoFormat Chart wizard provides a preview pane so you can keep tabs on your chart

The AutoFormat Chart wizard provides a preview pane so you can keep tabs on your chart

As you progress through the AutoFormat Chart wizard you can select from a number of standard chart types, choose a variant of that chart type, and add a title. As you work through this initial design phase, the AutoFormat Chart wizard will display a preview of your chart, as shown above, so that you have an idea what the end result will look like as you select options. When you finish, you’ll have a rather small, basic chart that you can begin spicing up. To begin with, you can use the handles to stretch the chart horizontally and vertically to make it larger.

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

With the options in the 3D Effects dialog box, you can create a very nice looking chart to display your data

With the options in the 3D Effects dialog box, you can create a very nice looking chart to display your data

With the chart selected you can pull down the Format menu and let loose with the plethora of options for customizing the axis, the grid lines, the walls, the floor, as well as the background. If you’ve selected a 3D chart variant, then you’ll want to select the 3D Effects command and investigate all the options the 3D Effects dialog box has to offer. Here you can adjust geometry effects, as well as shading and illumination, just to name of few.

Keep in mind that tinkering with all these settings can take a while, but the end result can be quite nice, as shown above.

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

Using grid lines makes it easier to keep track of data on a printed copy

Using grid lines makes it easier to keep track of data on a printed copy

Printing spreadsheets

Once you’ve developed a spreadsheet in Calc, chances are good that you’ll need to print it for inclusion in a paper report. When you do so, you’ll probably want to include grid lines, customize the footers, as well as have your column titles printed on each page.

To include grid lines, you’ll pull down the Format menu, select the Page command, and when the Page Style dialog box appears, select the Sheet tab. Then, in the Print section, select the Grid check box, as shown above.

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

You use the Custom Footer buttons to add such things as document title and page numbers to the footer

You use the Custom Footer buttons to add such things as document title and page numbers to the footer

Keep in mind that if your spreadsheet is wider than a single piece of paper, you can use the Left To Right, Then Down setting in the Page Order section to print the first section of columns, across all the rows and then print the next section of columns.

To customize the footer, select the Footer tab and click the Edit button. You can then use the Custom Footer buttons to place items in the three area panels, as shown above.

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

Clicking the Shrink button allows you to easily access the spreadsheet and select the column titles

Clicking the Shrink button allows you to easily access the spreadsheet and select the column titles

To specify that your column titles are printed at the top of each page, pull down the Format menu, select the Print Ranges command, and when the Edit Print Ranges dialog box appears, click the Shrink button in the Rows to Repeat section, as shown above. Once the Edit Print Ranges dialog box shrinks, select the cells or rows containing your column titles. Then click the Shrink button again.

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

As you preview the printed page, you can access the Page Style dialog box by clicking the Page button

As you preview the printed page, you can access the Page Style dialog box by clicking the Page button

To see how your spreadsheet will look before you actually print it, click the Page Preview button on the toolbar. If, as you study the mock-up, as shown above, you decide that you want to set other options, just click the Page button and the Page Style dialog box will appear right over top of the Preview window. You can then easily toggle back and forth as you customize the printed copy of your spreadsheet.

Performing common Excel tasks in OpenOffice.org 2.0’s Calc

Calc makes it easy to export your spreadsheet as a PDF file and attach it to an e-mail message all in one step

Calc makes it easy to export your spreadsheet as a PDF file and attach it to an e-mail message all in one step

Exporting to PDF format

If you want to electronically share the spreadsheet that you’ve generated in Calc with colleagues and don’t want to allow anyone to alter the data, Calc provides you with a feature, that at this point, Excel doesn’t provide–the ability to save the spreadsheet as a standard Portable Document Format (PDF) file. Just click the Export Directly As PDF button on the standard toolbar, type a filename in the Export dialog box, and click the Save button. You can then copy the PDF file to any location you want.

If you’ll be e-mailing the PDF, save yourself a step and select the Send | Document as PDF command from the File menu. You’ll then see the PDF Options dialog box, as shown above, and can optimize the PDF file for sending via email. Calc will then launch your email application, create blank new message, and then attach the PDF.

  • Software
  • Account Information

    Share with Your Friends

    Performing common Excel tasks in OpenOffice.org 2.0’s Calc

    Your email has been sent

Share: Performing common Excel tasks in OpenOffice.org 2.0's Calc
Image of greg shultz
By greg shultz
My first computer was a Kaypro 16 \"luggable\" running MS-DOS 2.11 which I obtained while studying computer science in 1986. After two years, I discovered that I had a knack for writing documentation and shifted my focus over to technical writing.
  • Account Information

    Contact greg shultz

    Your message has been sent

  • |
  • See all of greg's content

TechRepublic Premium

  • TechRepublic Premium

    OneDrive Cloud Usage Policy

    This OneDrive Cloud Usage Policy, courtesy of TechRepublic Premium, defines the practices and behaviors organization representatives must follow when using any Microsoft OneDrive cloud file storage account to execute organization services, process organization data, or access or store organization information. From the policy: No organization representative should ever install a personal OneDrive account on any ...

    Downloads
    Published:  October 1, 2023, 4:00 PM EDT Modified:  October 2, 2023, 9:00 AM EDT Read More See more TechRepublic Premium articles
  • TechRepublic Premium

    Hiring Kit: Video Game Designer

    One of the most common and most requested jobs listed by the video game industry is the job of video game designer. Depending on the company, the platform, the project and the company’s specific development process, the job of a video game designer can mean many different things. But at its base level, a game ...

    Published:  October 1, 2023, 4:00 PM EDT Modified:  October 2, 2023, 9:00 AM EDT Read More See more TechRepublic Premium articles
  • TechRepublic Premium

    Environmental Policy

    For businesses, sustainability means maintaining the ecological, economic and holistic well-being of present and future clients. This entails taking into account the demands and necessities of individuals and society without jeopardizing the growth or wellness of the generations that follow. As an accountable business entity, this policy from TechRepublic Premium defines the company’s commitment to ...

    Downloads
    Published:  October 1, 2023, 4:00 PM EDT Modified:  October 2, 2023, 8:58 AM EDT Read More See more TechRepublic Premium articles
  • TechRepublic Premium

    Best of Confluent Current 2023: The State of Data Streaming

    TechRepublic Premium was at Confluent’s Current 2023 event, held in San Jose, California, September 26-27. Our coverage of the event comprises an analysis of data streaming, interviews, the role of stream governance and a look at Apache Flink. From the download: Confluent used the Current 2023 ‘next generation of Kafka Summit’ event in San Jose ...

    Downloads
    Published:  September 27, 2023, 4:00 PM EDT Modified:  September 28, 2023, 1:00 PM EDT Read More See more TechRepublic Premium articles

Services

  • About Us
  • Newsletters
  • RSS Feeds
  • Site Map
  • Site Help & Feedback
  • FAQ
  • Advertise
  • Do Not Sell My Information
  • Careers

Explore

  • Downloads
  • TechRepublic Forums
  • Meet the Team
  • TechRepublic Academy
  • TechRepublic Premium
  • Resource Library
  • Photos
  • Videos
  • TechRepublic
  • TechRepublic on Twitter
  • TechRepublic on Facebook
  • TechRepublic on LinkedIn
  • TechRepublic on Flipboard
© 2023 TechnologyAdvice. All rights reserved.
  • Privacy Policy
  • Terms of Use
  • Property of TechnologyAdvice