Spreadsheets in Your Pocket!

One of the least-used Windows CE applications holds the most promise for improved productivity.

BY JOHN KENNEDY

[This article is primarily about Pocket Excel, a spreadsheet program found on Handheld PCs. However, this article also mentions three Spreadsheet programs available for the Palm-size PC.]

I have the suspicion that many Windows CE device owners are put off by Pocket Excel, thinking it a boring business tool only useful to accountants. If you are one of these people, here's a newsflash: Pocket Excel is probably one of the most underrated programs on the Handheld PC. I use it more than any other application, and it transforms the Handheld PC into a genuinely useful portable computer.

Great for collecting and tabulating data

A spreadsheet is great for tabulating information. Forget Pocket Word ­ Pocket Excel is the best tool for organizing data into columns and rows. It's a cinch to enter long lists of numbers, or to collect data "in the field" for later analysis.

Spreadsheets are essentially a grid of columns (described by a number) and rows (described by a letter). Where the column and row meets is an individual "cell." So the first cell in the top left of the spreadsheet (or "worksheet") is A1; next to it is B1; below A1 is A2; and so on. You can enter numbers, text, formulas, time and more into each cell. Pocket Excel allows you to select the format of the cell. For example, you can explicitly set it up so that a cell (or an entire column or a row) displays numbers as monetary values, complete with currency symbols.

For many users, this is all you need to know: the spreadsheet is merely a handy way to keep facts and figures organized in an easy-to-use table. You can use it for a checkbook, an order form, a shopping list, and more. When you format a spreadsheet to look the way you want it, and do the things you want it to, you may want to save it as a template and use it over and over again. For example, once you've created the perfect shopping list, there's no point re-typing it all every single time.

The power of a formula

A spreadsheet can be used to create a simple list, but its real power is its ability to perform calculations using formulae. For example, instead of entering a fixed value (say, 42) into a cell you can enter a formula (say, =SUM(A5:A10). This tells Pocket Excel that the value of this cell is equal to the sum of all the numbers in the column of cells A5 through A10. The syntax of the cell formula is very strict, but is also very simple. You only need to look at a few examples to get the idea.

Formulas are very handy if you have to work out a large number of similar calculations. The mathematical and logic expressions available for use in Pocket Excel (or any spreadsheet program) means that it's possible to solve some very complex problems. However, we won't cover advanced uses of spreadsheets in this article ­ there are plenty of on-line resources and books that cover those. Instead we'll look at a few simple examples. These examples are very well suited to the portable Windows CE devices: you'll probably be surprised how flexible and useful Pocket Excel will be.

Some simple examples of Pocket Excel spreadsheets

Shopping List ­ There are one or two full-featured shopping list programs available for Windows CE devices. But you can create a simple but functional shopping list with Pocket Excel (see Screen 1). I've placed the title, "Shopping list," in cell A1. Below it, in column A, I've listed the items I've purchased. Column B lists the individual, or "unit price." Column C lists the quantity of each item I purchased. Column D lists the total price for each item (unit price * quantity). At the bottom, I total up the entire contents of Column D. I use formulas in Column D to multiply the unit price by the quantity. So in cell D3 I've entered =(B3*C3), which tells Pocket Excel that the value in this cell = the value in cell B3 multiplied by (*) the value in Cell C3. Once I've entered this formula in cell D3, I simply copy it (press ctrl-C), move to cell D4 and paste it (press ctrl-V). Pocket Excel automatically adjusts the formula to read =(B4*C4). I continue to copy the formula to cells D5, D6, and D7. Finally, I create a formula in cell D8 that will total the prices in column D. The formula reads =SUM (D3:D7). The end total gives you a good idea how much you need to pay when you get to the checkout.

Weight record ­ Spreadsheets are great for storing and organizing lists of numbers, be they ages, attendance records or the number of pieces of fruit appearing on your tomato plants. Having the spreadsheet on your handheld means you can carry it around with you, and then return to base and copy it over to your desktop for further analysis. In this household a spreadsheet keeps track of my wife's weight, which isn't as cruel as it sounds ­ she's pregnant, and it's a great way to keep track of junior's development. Another spreadsheet notes the frequency of baby movements -- another important developmental figures.

Tracking expenses ­ The portability of H/PCs and Palm-size PCs means that they are there with you most of the time. Instead of writing down expenses and the like on scraps of paper and then losing them, why not create a simple spreadsheet to track what you did and how much you spent. Again, you can purchase dedicated expense tracking programs for H/PCs and Palm-size PCs, but a simple spreadsheet might be all you need.

Currency conversion ­ One of my favorite uses for spreadsheets is a currency converter for when I travel to the U.S. (see Screen 2). This spreadsheet lets me enter the current conversion rate for $ U.S. to £ U.K. A simple formula converts the numbers I enter in column B to Pounds Sterling. I've added an extra column to automatically add the sales tax into the figure. I also include a table of standard values (10, 50, 100 and so on) as this helps get a quick feel for how much you are about to spend.

Timetable Planning ­ The table format is often used for creating timetables, and again, Pocket Excel is the ideal tool. It's great for students and essential if you are planning a magazine and trying to keep track of overdue articles from lazy writers. [EDITOR'S NOTE: I do keep such a spreadsheet, but John's name is not found in the "lazy writers" column.] I also use it when planning long trips, as it helps manage flight times.

Sports score cards ­ A spreadsheet can help your leisure time too. A little effort spent making up a score card template makes it easy to keep track of your favorite sports. When you get the figures home you can instantly get all the statistics you need.

I've also found it handy to keep a little spreadsheet table with the current postage costs, depending on the weight of the item. You could also use a spreadsheet for cooking measurements, temperature equivalencies, and all manner of values associated with various hobbies and pastimes.

Staying in synch

The beauty of using Pocket Excel is that you can easily exchange a spreadsheet with your desktop PC and view it in the desktop version of Excel. When you use Windows CE Services to move a Pocket Excel spreadsheet to your desktop PC, the spreadsheet is automatically converted to a format read by the desktop PC version of Excel (and most other desktop PC spreadsheet programs). In fact, you can set Windows CE Services up to automatically synchronize Pocket Excel spreadsheets with their desktop counterpart.

The process of conversion also works in reverse. You can drag a standard Excel spreadsheet (or Lotus 1-2-3 worksheet) from your desktop PC to your H/PC and it will be converted and ready to read by Pocket Excel. Of course, there are a few limitations: the full MS Office Excel has grown into a mammoth program with far too many options to list or include in a smaller program like Pocket Excel. Only the core features are included, but they suffice for most simple day-to-day spreadsheets.

Previous versions of the H/PC ran into problems with Pocket Excel. An H/PC user would create a Pocket Excel spreadsheet, attach it to an e-mail, and send it to an associate with a desktop PC. If that associate did not have Windows CE Services, he or she had no way to convert it to a format they could use. The H/PC Pro version of Pocket Excel fixed that problem. H/PC Pro users can now save a Pocket Excel spreadsheet in the standard desktop Excel format. They can then attach it to an e-mail and send it to an associate without any problems. Likewise, an associate can e-mail an H/PC Pro user a standard Excel spreadsheet.

Most valuable of the least used

I've asked around and I'm amazed that many users haven't even launched Pocket Excel. It's an easy-to-use and extremely useful application, which lets you customize your H/PC to carry the information you need. It's a great application sitting right under your nose, and it doesn't cost you an extra cent ­ check it out!

Screen 1: You can create a simple Shopping List spreadsheet to prepare for shopping and to track expenses.

Screen 2: This currency conversion spreadsheet lets me enter the current conversion rate for $ U.S. to £ U.K.

John Kennedy (johntkennedy @sticky.net) is a technical journalist based in Northern Ireland (although he's open to offers). When he's not writing, he's programming Windows CE applications for Sticky Software.