There are two ways to use PC*MILER|Spreadsheets formulas in Excel: either type them directly into a cell or use the Insert Function option. All formulas will accept strings for city name, and either strings or integers for ZIP codes.
How to Format Cells and Enter a Formula
All city/state entries in Excel must contain a comma between the city name and state/country abbreviation, and—if you are using PC*MILER|Streets—all street addresses MUST be separated from the preceding place name by a semicolon. Example: new york, ny;118 broadway.
TIP: For help with formatting street-level addresses in Spreadsheets, we provide a workbook with examples in the Excel folder of your PC*MILER installation folder (usually C:\ALK Technologies\PCMILERXX\EXCEL). Look in the “Other Examples and Tips” tab of the DEMO_NA.xlsx file.
Columns must be formatted correctly so they can be read. This is especially important when importing postal codes—if your columns are not properly formatted, the default spreadsheet format (“General”) will cause the first zero to be dropped from postal codes that begin with zero. Also, columns containing calculated distances should be formatted so the desired number of decimal places appears. To format columns:
1. Highlight a column you wish to format.
2. Right-click the column and select “Format Cells…”.
3. In the Number tab under Category, highlight Number, Text, Currency, or Special > Zip Code, depending on the type of data the cells will contain. All Number and Zip Code formatting accepts text, too.
TIP: Leading zeros are significant in postal codes in many countries. For that reason, we recommend that you always enter or import numeric postal codes as text strings (format cells as Text) or using Special > Zip Code. If you do enter or import a postal code as an number, be aware that Microsoft Excel will strip off leading zeros before that number gets to PC*MILER.
4. Set the options you want for each category; for example, the number of decimal places in the Number format. Click OK when done.
5. Repeat for all columns that you will be using.
How to Add a Formula with the Excel Insert Function Option
1. Select a cell, then select the Formulas > Insert Function menu command or click the fx speed button above the column headers. Select a formula from the User Defined function category. All PC*MILER|Spreadsheets functions are listed in this category.
2. Enter cell references or other data in the Function Arguments window, and click OK when done. Options need quotes—for example, “prac” as in the screenshot below.
Time-Saving Tips
If you need to copy and paste columns with a large number of calculated values into another worksheet or Excel file, use the Paste Special feature to avoid waiting for values to recalculate:
1. Select the columns you want to copy.
2. Right click on the selection and choose “Copy”.
3. Right click again and choose “Paste Special”.
4. In the Paste Special dialog, select Values and click OK.
5. Paste the selection into another worksheet or Excel file. The calculated values will be pasted without the underlying formulas.
DID YOU KNOW? If you are new to Excel, you may not know that the contents of one or more cells can be copied into multiple cells with one mouse click. Formulas or values can be copied by selecting a cell or group of cells, then clicking on the square in the bottom right corner of your selection and dragging down or across the target column(s) or row(s).
PC*MILER Training Tutorial: Spreadsheets from ALK Technologies Inc. on Vimeo.
