What is an IP address?

An IP address (aka Internet Protocol address) is a unique numerical label assigned to a device connected to a network that uses the Internet Protocol for communication. IP addresses are used to uniquely identify devices and provide the device's location in the network.

How many IP addresses are there?

There are 4,294,967,296 unique IP addresses on IPv4 which is the most widely used protocol for internet traffic.

IPv4 is Internet Protocol version 4. It is a set of rules that allows devices to communicate on the internet. IPv4 assigns a unique 32-bit IP address to each device.

ASN stands for Autonomous System Number and is a unique identifier assigned to a group of IP addresses managed by a specific network.

Lookup IP address locations for free in a big spreadsheet

Explore a simple spreadsheet template that maps IP addresses to locations. This template IP address lookup converts IP addresses to integers so you can easily find the geographic location of IP addresses and map IP address to country, state, city, latitude, and longitude. Since Row Zero is 1000x more powerful than traditional spreadsheets, you can do bulk IP address lookups in a spreadsheet and join IP address databases with your own datasets. Explore for free in Row Zero, a next-gen spreadsheet built for big data.

Map IP addresses to locations in a spreadsheet

Overview of the template and datasets
How to lookup IP address location in a spreadsheet
How to convert IP addresses to integers
Choosing an IP address database

Overview of the template and datasets

The purpose of this IP address spreadsheet template is to demonstrate how to map your list of IP addresses to geographic locations using an IP address database. You can use any IP address database and follow the same steps. With any dataset, you'll need to convert IP addresses to integers to enable the IP address lookup. This template spreadsheet uses Row Zero's built-in Python spreadsheet window to create a pre-made python function to convert IP addresses to integers.

Here is information about the free IP address databases used in this demonstration:

IP to ASN database: Download at https://iptoasn.com/. The license for this dataset is PDDL. See also https://github.com/sapics/ip-location-db/tree/main/iptoasn-country/
dbipcity database: Download at https://github.com/sapics/ip-location-db/tree/main/dbip-city/. This IP address database is from DB-IP.com and licensed under a CC BY 4.0. You must create an attribution to DB-IP.com for using the DB-IP database. In the case of a web application, you must include a link back to DB-IP.com on pages that display or use results from the database. If you need better accuracy, no attribution, or daily updates, explore the commercial version of dp-ip here.

View the Spreadsheet

How to lookup IP address location in a spreadsheet

Here are steps to map your IP addresses to locations in a spreadsheet. Row Zero is a good tool for this exercise because it has the power to handle large IP address databases (millions of rows) and has a built-in Python feature that makes it easy to convert IP addresses to integers.

Open a new workbook in Row Zero. Login or sign up for free to get started.
Import your list of IP addresses. You can import from file, URL, S3, or directly from your data source (Postgres, Snowflake, etc.).
Convert your IP addresses to integers in a new column. Here's how using Row Zero's python window.
Import an IP address database into a new sheet. There are a large number of IP address databases to choose from. In this example, we show how to use two free databases of IP addresses with geolocation data using IPv4 addresses.
If your imported IP address database is not in number form, you can convert the IP addresses to integers using the same process in step 3 above.
Use XLOOKUP to lookup each IP address's location data.
Drag your formula down to do a bulk IP address lookup in seconds.

Row Zero works like Excel and Google Sheets (just a lot more powerful) so you can analyze your dataset with formula functions, pivot tables, charts, etc. You can also import free population datasets from Row Zero to calculate market penetration by country, state, etc. using a similar process with XLOOKUP.

Here's a video tutorial for how to bulk lookup IP address locations in a spreadsheet:

How to Convert IP Addresses to Integers

We can use Row Zero's built-in python code window to create a simple function to convert IP address to integer.

import ipaddress
def IPtoInt(ipv4_string):
    return int(ipaddress.IPv4Address(ipv4_string))

We can then call the function in the spreadsheet and drag down to bulk convert IP addresses to integers. bulk convert ip address to integer in a spreadsheet

Choosing an IP address database

There are a large number of IP address databases you can work with. For our template spreadsheet, we used two free IPv4 databases. Note that there are 4,294,967,296 unique IP addresses on IPv4 (2 to the power of 32). Given the large size, IP databases typically give ranges for IP addresses that map to geolocations. Here are some things to consider:

Format: Since IP address datasets are large, they are often compressed. The datasets used in our example were CSV.gz and TSV.gz files. Ensure the tool you're using can open the file format. Row Zero supports a wide variety of large formats including .gz files, parquet, and JSONL in addition to CSV, TXT, TSV, etc.
Size: Similarly, big IP address datasets cannot be opened in Excel or Google Sheets. The Excel row limit is 1,048,576 and Google Sheets has a 10 million cell limit.
Tool: The tool you're using to work with the IP address data will impact the databases you can choose. Among spreadsheets, Row Zero gives you the greatest versatility in terms of file size and format. If you want to lookup IP addresses in Excel, you'll need to trim your IP database below the 1 million row limit. You can first open the larger file in Row Zero and then filter (by country for example) to get the file below 1 million rows so you can export to CSV and open the IP address dataset in Excel.
Accuracy: IP addresses change over time (and map to different locations and ASN providers). Given the large number of IP addresses and the use of IP address ranges in geoip lookups, the accuracy of IP address lookup tools and databases can vary. Generally, free IP lookup databases are less accurate and updated less frequently.
Price: There are many free IP address databases available, but they are less accurate and provide less granular detail compared to paid versions. Generally, you'll pay for better accuracy and more frequent updates.

To get started, you can download the free IP address databases used in the template.

How to use the IP address lookup spreadsheet template

Here are steps to map your IP addresses to locations using the spreadsheet template:

Go to the sheet Example - map IP addresses to locations in the spreadsheet template.
Insert your list of IP addresses into column A.
Pull down the formulas in columns B through J.
Go to the sheet Template for IP to ASN dataset and follow the instructions for how to update the data.
Go to the sheet Template for dbipcity dataset and follow the instructions for how to update the data.

You can use any IP address database and follow the same steps. Note, to map IP addresses to locations, you need to convert IP addresses to integers. This workbook has a pre-made python function (access by clicking the </> Code button in the top right) that is used in the example.

View IP address to location spreadsheet