Spreadsheeting to the max

Few computer programs are as widely used and as little understood as the humble spreadsheet.Assemble a group of Microsoft Excel users, and ask them how many think they are in the top percentile of savvy users. Almost everyone will raise their hands, said Mbwana Alliy, a Microsoft Office product manager.However, few are aware of all the features a good spreadsheet offers.'People do not realize this, but you do not need to do a particular application for everything,' said Robert Holmes, an Excel instructor at the Graduate School, USDA. 'You can do a lot in Excel. You just need to know how.''In some situations, a spreadsheet can be an easier tool to use than a database system because you can pull the data you need on-the-fly with functions,' said Faithe Wempen, author of numerous books on Microsoft Office, including Special Edition Using Microsoft Word 2007.Wempen is an adjunct instructor of computer technology at Purdue University and the instructor for a set of online classes offered by the Hewlett-Packard Learning Center. 'Spreadsheets are nimble; they do not assume that you want to save every query and calculation for later use.'However, such flexibility does not come naturally to most users. Holmes said. For his classes, he encourages students to think in Excel, which can take some doing.'It's like a language. You can take French lessons for 10 years, but if you never learn to think in French, you are not really effective. Computer applications are the same way. You have to think in that application in order to use it effectively.'GCN can't help you parlez-vous Spreadsheet, but here are a few quick phrases ' that is to say, tips ' that can help you get more from your spreadsheets with comparatively little effort. Some spreadsheets have so many columns that they literally fall off the end of the page. This can be a problem when you are entering data on the far end of the spreadsheet; you might forget what sort of data is supposed to go into each row.Holmes said Excel offers an easy way to build a form that will allow you to input data. While your cursor is anywhere in the table you want to add data to, click on the form option under the data menu item. Excel generates a form, with labels for each column or row, and a space to add the data. After one row or column of data is entered, hit submit, and Excel will populate each cell with the appropriate data. You know where everything is on your spreadsheet, but does that mean others users do, too? One feature to look at is conditional pointers, Holmes said.This feature makes it possible to highlight data ranges in different colors depending on the value entered in a particular cell.Say you have a number of different tax brackets, and each bracket corresponds to a separate row in a table. The user enters his or her tax bracket in one cell, and the row of interest is immediately highlighted.A related feature, conditional formatting, can help in business intelligence, Alliy said. If a calculation reaches a specified threshold, the cell's color can change, alerting someone glancing over the spreadsheet that a critical threshold has been reached. One of the features that goes underused in Microsoft Excel is the ability to pull data directly from the Web using the Web query command.After entering a Web address, Excel returns a copy of the Web page marked with pointers to the parts on the page where data can be downloaded.After you select the fields you want, Excel prompts you to provide the location on the spreadsheet where that data may reside. It then populates the spreadsheet with the data, even providing the column names when possible.OpenOffice has similar functionality, though it only lists the tables on the page from which data may be drawn, rather than visually depicting the page. When your spreadsheets get to a certain size, you can break them into separate worksheets. Then you might want to use the Vlookup function to find things.At its simplest form, Vlookup is a search function available across Excel, OpenOffice and Corel's Quattro Pro. It lets you specify a value that might appear in one column. The function seeks the information that appears in a column adjacent to the data you specify. This can be handy when dealing with information in multiple worksheets. Vlookup can be typed into the cell directly or evoked through a list of functions in the menu in Excel 2003. 'A spreadsheet can be a cost-effective alternative to a database management or business intelligence system,' Wempen said. And PivotTables are one of the primary ways of ramping your spreadsheet to take on such duties.PivotTables allow you to use large amounts of data to see new relationships. You can display your data in different ways, rearrange existing fields, add new fields and remove old fields ' all without touching the original source data.In her online classroom example, Wempen rearranges a spreadsheet that lists student tuitions and balances into a simple chart that better highlights students' names, how much each still owes and how much is already paid. You can easily move columns, rows and fields around with the mouse.You can filter material and even create charts based on PivotTable. OpenOffice's version of PivotTable is called data pilot, and in Corel's Quattro, similar functionality is called cross tab reports. As your spreadsheet expands, you might consider some basic architecture, Alliy said. Organize the worksheet by function. Cordon off the inputs in one worksheet; place the calculations in another worksheet; and have the outputs reside in yet a third section. Macros and documentation might appear in other sections. The worksheet is organized, and it can also limit damage. For example, someone can't accidentally erase a formula while inputting data. Want to speed up calculations? For datasets that cannot be quickly summed by Autosum, you can still label columns and rows with specific names, such as department, Holmes said. When you need to calculate the sum of a row or column, instead of highlighting the range of cells with a mouse or entering the start and ending cell, enter the label, and Excel will know the range of data you want. 'If you say 'January,' it will sum everything under January,' Holmes said.Holmes added that you can name individual cells, which can be handy for finding one piece of information buried in a large spreadsheet. Of course, you will want others to see the result of your calculations. Simply sending your spreadsheet around by e-mail is fraught with peril.All the identical copies you send will occupy room on the e-mail server, and you have no guarantee that the recipients won't change or destroy their copies of the data. Alliy recommends establishing an Excel Services repository as part of a Microsoft SharePoint installation.Excel Services allows users to save the results of their Excel workbooks, such as graphs, charts and calculations, as a separate file that can be viewed by others in Office or as part of a Web page.Excel Services shows the results of the computation in read-only format, though you can link to live data sources, such as a database, so external information in the workbook can be automatically updated. Contrary to popular belief, you do not need a copy of Excel to make an Excel spreadsheet. You could create a simple one using a text editor and HTML, according to one developer on the Washington Perl users' mailing list.Build an HTML page with a table containing the number of rows and columns you want and fill the data into the table. When you are finished, change the extension name from .html to .xls. Excel will open the document.This technique might not be practical for building a spreadsheet, but it would be handy for automatically exporting data from scripts and programs into the Excel format.