Here are some great tips that can be applied to all versions of CDXZipStream:
1. Stop using CDXZipStream
Just kidding! But what we mean is you don’t need to click on the CDXZipStream button, or access the right-click menu, to get data from CDXZipStream. If you’ve already applied CDXZipStream in your worksheet and need to use it in another data area, just copy and paste the existing equations to the new area and the formulas will get the data automatically. This also works in cases like route optimization where the data is output as an array; just make sure to copy the entire array when applying it to a new area. Here’s a step-by-step description of how to copy a CDXZipStream array to a large data set.
2. Don’t scroll through your data
When you’re dealing with a large set of addresses (and getting lots of data back from CDXZipStream), it can be slow and frustrating using the Excel scrollbar to navigate through it all. Fortunately there are some very useful shortcuts that can quickly get you exactly where you need to go in your worksheet. They’re easy to learn with a little practice, and even if you’re an occasional Excel user, we guarantee it’s worth the effort:
Ctrl – Arrow: To quickly skip to the boundaries of your data, hold down the Ctrl key and one of the directional arrows on your keyboard at the same time. (These arrows are usually located on the right side of your keyboard, and may also be on the number pad on the far right.) For instance, to go to the bottom of your data set, use the cursor to select a cell somewhere within the data, then hold down the Ctrl and Down-Arrow keys at the same time. The cursor will move to the very last row of contiguous data. You can easily move to the top, right, left, and right boundaries of your data using the appropriate arrow keys, and you can continue to press the arrow key to navigate to other data sets. After skipping through all the data you’ll eventually reach the very last row or column of the worksheet.
Ctrl – Shift - Arrow: To select a large area of your data, hold down the Ctrl and Shift keys, and one of the directional arrows on your keyboard at the same time. For example, to select the first column of your data, select the top left data cell with your cursor, then hold down the Ctrl – Shift – Down arrows together. Then to select the entire data set, hold down Ctrl – Shift – Right arrows together. With a little practice you’ll find that this by far the easiest way to select a contiguous area of data, regardless of its size.
F5: Sometimes you just need to go to one particular cell. Hit the F5 function key at the top of your keyboard, and you’ll see an input box where you can enter the cell address. Then just press the enter key, and you'll instantly arrive at the desired cell.
3. Speed up Excel
If you have thousands of equations (or more) in your worksheet, you may notice that Excel starts to slow down. That’s because every time a cell in the worksheet is changed, Excel by default recalculates all equations. This will also occur with the custom function formulas CDXZipStream uses to obtain data. To speed up your worksheet, you have a couple of options:
The first is to turn off automatic recalculation. In Excel 2010, from the File tab, select “Options”, then “Formulas”, then under “Calculation options” select “Manual”. (A slightly different procedure is required for Excel 2003 and 2007.) You can now control when Excel recalculates by pressing F9 on your keyboard or by selecting “Calculate Now” on the Formula tab. Since recalculation only impacts existing formulas, you can continue to get new data from CDXZipStream. Just remember to turn recalculation back on when you’re done with your workbook.
Another easy approach is to replace the equations with their values. Select the range of equations in your worksheet, and in Excel 2010 from the Home tab, select “Copy” from the Clipboard group. Then click on the arrow under “Paste” and select the first “Paste Values” icon on the left. (This can also be done in Excel 2003 and 2007.) The equations will now be gone, but the values will remain. And you’ll notice that Excel is much faster and more responsive.
Note: If you'd like to create a toolbar shortcut for pasting values, which is one of the most common tasks in Excel, please follow these instructions.
4. Use cell references with right-click functions
When accessing a right-click function like CDXDistance or CDXRouteMP, you have the option of inputting an actual value (like the zip code “38472”) or the cell reference where the data is located (such as “A1”). If the value is located somewhere in the worksheet, you should almost always use the cell reference. Not only is it easier in most cases, but the resulting CDXZipStream equation in the worksheet will now be ready to copy and paste to other data areas without need for modification. Since “A1” is considered a relative cell reference, Excel will automatically change this reference when moved to the new data area. In cases where an absolute (unchanging) cell reference is required (when calculating distances to a single location), you can either input the actual value or use absolute reference notation with the dollar symbol, like this: “$A$1”.
5. Rev up CDXZipStream using Auto Fill
We’ve talked about auto fill before in a previous blog, but it bears repeating because it can really speed up CDXZipStream when you’re working with a lot of data (> 10,000 calculations).
Let’s say you need data for a very long list of zip codes. First, use the CDXZipStream button to get custom formulas for only the first zip code in your list – to do this you will need to insert a blank row after the first zip code. Then delete the blank row and use the Excel auto fill feature for the remaining items in the list. Please refer to this Microsoft article about auto fill – just remember you can automatically fill a formula downward, for all adjacent cells, by double-clicking the fill handle of the first cell – dragging the handle is difficult for long data lists. Not only is auto fill faster, but Excel will also show you the progress of the calculations. When auto fill is finished, you can now use tip number 3 to manage all those new formulas.
You may have noticed that a few of these tips work well with any large set of data, whether you're using CDXZipStream or not. We hope these can significantly help improve your experience while using both CDXZipStream and Microsoft Excel.