Sunday, February 25, 2018
IP Calculator Functions for Spreadsheet
IP Calculator Functions for Spreadsheet
Spreadsheet (e.g. Microsoft Excel) is a very powerful tool for network engineers. I use spreadsheet to build network configuration scripts, especially for large scale deployment/migration. For example, how about crating 100 VLANs and their corresponding SVIs with HSRP?
Due to the format of IP address were accustomed to, its not very easy to build configuration scripts with native spreadsheet functions. For example:
Given a IP subnet "10.2.0.0/22", what is the last host IP in the subnet? Or what is the wildcard of the subnet?
Ive tried different tools and add-ons and settled for "IP Calculator for Excel" (http://trk.free.fr/ipcalc/) due to the following reasons:
1) Integrated and Unified - it was built with VBA macros. You may use the functions just the same way as the native Excel functions. e.g. "=ipAdd(A1,2)" takes the IP address from cell A1 and returns an IP address with addition 2. Thus you may build formulas the same way you do in any regular Excel spreadsheet.
2) Light weighted - the macro is less than 40k. Thus it wont add too much weigh on your spreadsheet.
3) No EXE. It is embedded into spreadsheet as macro. Thus it doesnt trigger any security or policy alerts. Of course, you still have to enable macros from Excel though.
4) IPv4 and IPv6 support.
If youre a network engineer who works on large scale network, take a look at this tool. Itll save you tons of time and human errors.
