2 MS Excel Tips

When I learn something cool in regards to technology – I love sharing it. My daily work involves a lot of administrative tasks – where I have to work with MS Office, especially Excel and Word, and work miracles with it sometimes.

Last week I discovered a cool new tool for MS Excel; but first let me explain how I came to need it. At my company, each new client receives several weeks of our people at their site. Before they arrive, they need to receive something called an implementation kit that contains all of the tools that they’ll need during this time. There has always been a system of logging where the kits are. However it was pretty tedious; where you had to go to the UPS or FedEx website and go in, track the package, and then update the spreadsheet. Royal pain.

I took charge of the process and initially, I was wondering if there was a way I could type the tracking number into Excel, and then put in a hyperlink that would take me straight to the shipper’s site, with the tracking number automatically populated. What I found was much cooler than that. I found ShipTrack, an add-in for Excel that adds a new formula function that will give you the shipping details of a tracking number. Here’s the end result:

MS Excel ShipTrack add-in

Installation of the add-in was a breeze – you didn’t even have to go into Excel’s options (which are sort of confusing – since 2007 version) and enable it. However I did have an issue with trying to manually type in the function – =ShipTrack (tracking#,carrier), and had to select it from the formula menu (I’m not sure what difference it made). Also in order to get the most current information (for packages in-transit) and refresh the data, you have to copy & paste the formula again. For example, if I want an update on Montevista’s kit, I just copy the tracking information for Richardson, and paste it into Montevista’s cell (there is probably another way to tell MS Excel to recalculate the cell, but I find it much quicker to just  copy and paste the formula again!).

The next MS Excel tip is one that I’ve known for a while, but figured I would share here anyway. It involves the security features of MS Excel – and the ability to lock and protect documents. In short, use these options sparingly; preferably for documents being produced and used on your home machine and network. Why? Well because it has been my experience that there is a 30% chance that other users of this file will run into problems with it. Encryption and password protection does not work very well across various versions of MS Excel and almost never works when non-Excel programs use it.

So what’s the fix here? Well, if you do not want others to modify the document, send it as a .pdf file. You can do this in a couple of ways. #1 – If you have Adobe Acrobat installed, you should see ‘Adobe PDF’ as a printer in your printer list. Sort of like this:

Print to Adobe pdf in Excel

After “printing”, you’ll see the .pdf file open and a pop-up asking you where you want to save the file. You can also do the standard ‘Save As’ and choose ‘.pdf’ as the file extension. However I like the former way better because it’s a bit quicker. However the latter works just as well for people who don’t have Adobe Acrobat on their machines.