Performing common Excel tasks in 2.0's Calc

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 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! 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 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, 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 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.

By Greg Shultz

Greg Shultz is a freelance Technical Writer. Previously, he has worked as Documentation Specialist in the software industry, a Technical Support Specialist in educational industry, and a Technical Journalist in the computer publishing industry.