July’s Front Counter Mechanics discussed the Microsoft Office suite of products. The article was published not because we at Import Service own lots of Microsoft stock (Don’t we wish!), but because Office has become the standard of business information exchange.
So, what exactly is ‘business information exchange?’ The way this article comes to you is one example. I’m writing it using a database text editor, but I’ll convert it and e-mail it to Karl Seyfert at Import Service as a Microsoft Word document. As it’s edited, and e-mailed back and forth for review, Word will automatically color-code each phrase that’s changed and annotate the change with the individual editor’s name, the date and the time. Once we’ve kicked it back and forth enough, it goes off to the pre-press vendor in yet another format.
Here’s another example: If you are a Mitchell Information System subscriber, your updates may be in Microsoft Access database format. Mitchell also uses the Access format to ship its Vehicle Configuration Database (as described in “Can We Talk? Open Standards Update” in last September’s Front Counter Mechanics).
The Vehicle Configuration Database is also the current basis for a new industry standard developed by representatives from Mitchell, ALLDATA, CCI-Triad, NAPA, Dana, Federal Mogul, Hunter, Snap-on and others. This group is working closely with the Automotive Aftermarket Industry Association to create a single open standard for vehicle data exchange. When it’s ready, the new standard will be available in Microsoft Access format.
For examples closer to your immediate needs, say you have a mailing list you wish to give to a direct marketing consultant, or a business brochure you’ve designed or a web page idea for your Internet site. Chances are very high, the consultant, printer or ISP you deal with can use one of the Office product formats.
This month’s Front Counter Mechanics provides a step-by-step guide on how to create a simple cus-tomer list using Microsoft Excel. Excel is a spread-sheet program; that is, it keeps track of facts and fig-ures in ‘cells’ arranged in rows and columns.
Creating and keeping a customer list uses only a frac-tion of Excel’s capacity; its true power lies in the ways it can work with the numbers to provide the answers to ‘what-if?’ questions. A budgeting spreadsheet, for example, automatically recalculates totals and ratios whenever a number in one of its cells is changed.
Simple though it is, creating a customer list famil-iarizes you with Excel and avoids overcomplicating this introduction. Office programs can be overwhelm-ing at first, especially if you try to explore all of their features. Learning them is a little like being a kid again, sitting in a messy bedroom you have to clean up: There’s so much, you don’t know where to start, so you either put off doing anything, or you start play-ing in it and add to the mess. Excel is a comprehensive program with extensive capabilities. It’s a big room cluttered with a lot of neat stuff. This article will be an exercise in ignoring most of that stuff, however neat. Creating a customer list is like only picking up your socks from the floor — it’s not much, but it is a start.
This article assumes you have Excel installed on your computer and have some familiarity with Windows, so Step 1 is opening Excel:
Click the Windows Start button, then select Programs, then click the Excel icon. You’ll see a screen like Figure 1b — although, depending upon your com-puter’s Display settings, you may see more or fewer rows and columns. You might also see “Clippit,” the animated Office Assistant. If you get tired of Clippit’s moronic nattering as I did, toggle him off by clicking first Help, then “Hide the Office Assistant.”
Excel shares its Title Bar and Menu Bar format with other Windows-based programs and its Toolbar format with other Office programs. The fea-tures unique to Excel are the Name Box, the Formula Bar and the Workbook Window.
The Name Box displays the column and the row of the active cell. The active cell is where the words show up when you start typing. In this example, the active cell is Column A, Row 1. The Name Box has other functions as well, but there’s no need to clut-ter your brain with them now.
The Formula Bar, like the Name Box, is capable of multiple functions, but in this example, it will only dis-play the contents of whichever cell is currently active.
The Workbook Window is where customer names and addresses are entered and displayed.
Cell A1, the one in the upper left-hand corner, should be the one that’s active. If not, put the mouse pointer on cell A1, then left-click once. (You can also make a cell active by typing its column and row number in the Name Box and then pressing Enter.)
Let’s start by typing the column headings. Type the words First Name. Then press the Tab key. B1 becomes the next active cell. Then type Last Name and press the Tab key. Similarly, type Address, then City, then State, then ZIP, pressing the Tab key each time to move the active cell one column to the right.
Notice that the last bits of First Name and Last Name are cut off by the edges of the cells. Let’s drag the cells wider to make them readable. Place the cursor over the “A” above the first column, then left-click the mouse to highlight the entire column.
Move the cursor to the right-hand border of Column A until it turns into a double-sided arrow. Then left-click and hold the mouse button down as you drag the column to 20 characters wide. Repeat this step for each column. Make the Last Name, Address, City, State and ZIP columns 20, 25, 20, 5 and 10 characters wide, respectively.
Now is a good time to save your work. Left-click the “floppy disk” icon on the Toolbar (Figure 1). The “Save” window will pop open. The cursor will be in the File Name field, and “Book1.xls” will be highlighted. Just type in the word Customers and press Enter. The spreadsheet is now saved.
Next start entering your customer list the way you entered the titles. Press Tab to move one cell to the right and Enter to move down a row. As you enter names on the second and subsequent rows, you’ll see Excel’s AutoComplete feature at work. For each column except the ZIP column, AutoComplete gives suggested values based upon prior entries. AutoComplete won’t work with ZIP Codes because Excel thinks they are numbers.
We want to alphabetize the list (don’t try it yet!) First, tell Excel Row 1 is the title row. Otherwise, the titles will sort with the names. Create titles by left-clicking the “1” next to Last Name to highlight all of Row 1. Then click the “B” button to the right of the Formatting Toolbar (Figure 1) to boldface the titles.
Alphabetize the list by clicking on any cell in the Last Name column (except the “B” column heading!), then clicking the A/Z icon on the Toolbar (Figure 1). Caution: If you click the “B” column heading, Excel will alphabetize only that column, scrambling your data!
The last step is left-justifying the ZIP Codes to make them line up under the title. Put the cursor on the “F” column heading above Zip, then left-click to highlight the entire column. Then click the Align Left icon on the Tool Bar (Figure 1). Click the Save icon to save the alphabetized list.
If you like ‘extra-credit’ assignments, try playing with the drop-down menu that appears when you right-click a cell. The “Copy,” “Paste,” and “Pick From List” features are particularly useful. Copy and Paste work for blocks of cells as well as for individual cells. To Copy, first highlight the area to be copied by dragging the mouse, then right-click and select Copy. To Paste, place the cursor on the cell you want to paste to, then right-click and select Paste.
That’s it for this month’s Front Counter Mechanics. Future articles will describe how to import records from shop management databases into your Excel customer list and how to use Office Small Business Tools to create direct mailings.