To find the physical location of an IP address in Google Sheets, install the InstaMaps add-on and use the =GEOCODE_IP() formula. Point the formula at a cell containing an IPv4 or IPv6 address, and it returns approximate coordinates, converting raw server logs into geographic data points directly within your spreadsheet.
Built for security analysts, server administrators, and ad-ops teams auditing traffic logs, this function turns a column of 1,000 IP addresses into actionable location data. You finish with a clean dataset of coordinates ready to be plotted on a live map, without exporting to a separate Python script or paid lookup database.
- →The =GEOCODE_IP() formula converts IPv4 and IPv6 addresses into geographic coordinates.
- →Syntax requires only the cell reference or IP address string: =GEOCODE_IP(ip_address).
- →Use this to map server access logs, customer sign-up IP lists, or network traffic data.
- →Free tier limit: 100 lookups per day (increases to 1,000/day with a free email unlock).
- →If you see a #NAME? error, ensure the InstaMaps add-on is installed and authorised.
- →Chain the output with =CITY() and =INSTAMAP() to build a live, shareable map of your data.
Syntax and arguments
=GEOCODE_IP(ip)
Returns the latitude and longitude coordinates for a specified IPv4 or IPv6 address.
Arguments: ip (Required). The IP address you want to resolve. This accepts a direct text string enclosed in quotation marks (e.g., "8.8.8.8"), a single cell reference (e.g., A2), or a vertical array range (e.g., A2:A201).
When you pass a range into the ip argument, =GEOCODE_IP() dynamically expands downwards, returning a comma-separated string of coordinates (e.g., 37.4056, -122.0775) for every valid IP in the column. If a cell contains an empty string, the corresponding output cell remains blank. If the provided text fails standard IPv4 or IPv6 validation checks, the formula returns an #ERROR!.
Note: IP geocoding relies on server-side database lookups. The default free tier allows for 100 lookups per day, which increases to 1,000 lookups per day with a free email unlock.
ip: The IP address or array of addresses to resolve.
How to use =GEOCODE_IP() in your sheet
To begin resolving IP addresses, the InstaMaps add-on must authorise the sheet to make external calls. You can do this manually or via the sidebar.
1. Navigate to Extensions > InstaMaps > Enable formulas. This opens the sidebar interface on the right side of your spreadsheet.
2. Click the Build-the-workflow button located at the top of the sidebar.
3. Select the IP to Location template from the list of available workflows.
4. Highlight the column containing your raw IP addresses. If your server log data starts in cell A2 and runs to row 201, the sidebar automatically detects the range and writes the formula =GEOCODE_IP(A2:A201) into the adjacent B column.
Alternatively, you can type =GEOCODE_IP(A2:A1000) directly into cell B2. Because the function handles arrays natively, it will instantly resolve coordinates for all 999 rows beneath it without needing you to drag the fill handle.
If a #NAME? error appears in the cell instead of coordinates, the add-on is either not installed or the session has expired. Reopen the InstaMaps sidebar via the Extensions menu to restore the connection and clear the error.
Worked examples: Mapping IP lists at scale
Example 1: Security analyst mapping failed logins
A security analyst exports a raw CSV list of 200 failed admin login attempts. The IPv4 addresses are pasted into column A, specifically spanning cells A2:A201. The analyst types =GEOCODE_IP(A2:A201) into cell B2. InstaMaps processes the array and outputs the exact latitude and longitude coordinates down to row B201. The analyst can then pass this coordinate data directly into =INSTAMAP() to generate a live hosted shareable map URL. This instantly highlights if the brute-force attempts originate from unexpected geographic clusters, replacing manual log parsing. If an IP address in row 140 is malformed or contains a private string like 192.168.x.x, that specific cell returns an #ERROR! or #VALUE! while the surrounding rows resolve correctly. Empty cells in the range simply return a blank value, preventing broken data sets.
Example 2: Marketer tracing affiliate sign-ups
A digital marketer wants to visualise the geographic spread of 47 affiliate sign-ups received over a 24-hour promotional period. The IP addresses are captured by a web form and stored in range D2:D48. The marketer enters =GEOCODE_IP(D2:D48) into cell E2, immediately resolving the batch of 47 addresses. Because this volume falls under the standard free tier limit of 100 lookups per day, the marketer can execute this tracking workflow daily without requiring the 1,000-lookup email unlock. The coordinate output in column E can be dropped directly into reporting dashboards or compared against region data. This verifies that the affiliate traffic matches the targeted areas specified in the initial campaign brief.
Example 3: Network admin sorting traffic logs
A network administrator is reviewing 1,000 rows of firewall traffic logs pasted into Google Sheets. The IP addresses are listed in C2:C1001. Because resolving 1,000 IPs exceeds the default daily quota of 100 lookups, the administrator must first complete the free email unlock within the InstaMaps sidebar to lift the daily limit to 1,000. The admin enters =GEOCODE_IP(C2:C1001) into cell D2, generating a full column of coordinates. Next, they need to group this traffic by nation for a quarterly compliance report. By placing =COUNTRY(D2:D1001) into cell E2, the sheet automatically appends the corresponding country name next to every resolved IP coordinate. This builds a clean, filterable dataset that can be sorted or pivoted by region, allowing the admin to isolate anomalous traffic patterns. This chaining turns a raw column of numbers into an actionable audit trail without writing custom scripts.
Composes with: Chaining formulas
InstaMaps formulas are designed to pass data directly to one another. =GEOCODE_IP() outputs coordinates formatted perfectly as inputs for other functions, allowing you to build complex data pipelines.
Chain 1: Resolve IP lists to a shareable map
If you need to turn a raw list of website visitors into a hosted map, you can chain =GEOCODE_IP() with =CITY() and =INSTAMAP().
1. In cell B2, enter: =GEOCODE_IP(A2:A250). This resolves the IP addresses in column A into decimal coordinates.
2. In cell C2, enter: =CITY(B2:B250). This extracts the human-readable city name from the coordinates, appending it to your original data.
3. In cell D2, enter: =INSTAMAP(B2:B250). This function reads the coordinate array and generates a live, hosted shareable map URL. When you click the link, it opens a visual map complete with pins. Because the URL is dynamic, adding new IP addresses to column A automatically updates the hosted map without any further configuration.
Chain 2: Generating Street View links from IP addresses
When investigating fraudulent activity, an investigator may want to see the physical location of an IP address.
1. In cell B2, enter: =GEOCODE_IP(A2:A50). This isolates the latitude and longitude of the 50 suspicious addresses.
2. In cell C2, enter: =STREETVIEW_LINK(B2:B50). This function accepts the coordinate array and generates a direct Google Street View URL for each address. Clicking the generated link drops the user onto the street-level view of the resolved ISP or server location.
Common errors and quota limits
If Google Sheets returns a #NAME? error in cell B2 after you enter =GEOCODE_IP(A2), the InstaMaps add-on is either not installed or not enabled. To resolve this, navigate to Extensions > InstaMaps > Enable formulas. If the add-on is missing entirely from your extensions menu, you must install it from the Google Workspace Marketplace. For further troubleshooting on installation failures and formula recognition, see our [google-sheets-geocode-formula] guide.
InstaMaps enforces a daily quota to manage server costs. When you exceed the baseline limit, the cell returns the exact text: Error: Daily quota exceeded (100 lookups). Sign up for free to get 1,000 lookups/day. If you are processing an array of 500 IP addresses in the range A2:A501, cells 101 through 500 will display this specific quota message.
You can expand your access directly within the sidebar interface. By entering your email address under the InstaMaps settings, you verify your account. This free email unlock increases your daily capacity from 100 IP lookups to 1,000 IP lookups per 24 hours. The quota resets automatically at midnight Coordinated Universal Time (UTC).
#NAME? error: Indicates the InstaMaps add-on is not installed or enabled. Use the Extensions menu to activate it.
Quota message: Daily quota exceeded (100 lookups). Sign up for free to get 1,000 lookups/day. Resolves at midnight UTC or via the free email unlock.
Limits and honest alternatives
IP geolocation translates a numerical address into a physical location, but the accuracy of this data is inherently limited by ISP routing infrastructure. It is typically regional or city-level, not street-level. An IP address assigned to a user in Bristol might resolve to a regional internet exchange or a generic city centre coordinate rather than the user's actual physical address. Consequently, plotting these coordinates with =INSTAMAP() will display a cluster of points in a broad geographic area, not a precise rooftop.
If you only need to categorise web traffic by country or identify an internet service provider, do not waste your daily lookup quota on generating map coordinates. Instead, use Google Sheets' built-in text functions. For example, if firewall logs in column A contain predictable IP structures, you can use =REGEXMATCH(A2, "^192\.168") to quickly isolate internal network traffic. Similarly, =ARRAYFORMULA(REGEXEXTRACT(A2:A500, "^(\d+\.\d+)")) can extract subnet prefixes to group traffic by provider. Reserve =GEOCODE_IP() strictly for scenarios where generating an actual latitude and longitude to plot a live map is operationally necessary.
Who this is for
Cybersecurity analysts: Processing daily firewall logs, proxy requests, or failed login attempts. If you are reviewing a list of 450 failed SSH login IP addresses and need to visualise unauthorised access attempts on a live map to identify regional botnets.
Marketing operations managers: Tracking event registrations or downloadable content requests. If you need to plot IP addresses from a CSV of 300 recent leads to determine regional demand and visualise where your audience concentrates.
Server administrators: Monitoring incoming API traffic or scraping behaviour. You need a quick, shareable visual representation of where heavy server load is originating without leaving your spreadsheet.
Who should skip this: If you need precise, device-level tracking, such as plotting delivery vehicles or tracking field technicians via GPS coordinates, this formula will not provide the necessary accuracy. IP addresses reflect the ISP's routing node, not the device's exact location.
Map your Salesforce accounts in under 5 minutes — no admin setup.
Common Questions
Install the free InstaMaps add-on from the Google Workspace Marketplace. Once enabled, type =GEOCODE_IP(A2) into an empty cell, where A2 contains the IP address. The formula queries the database and outputs the estimated latitude and longitude. You can drag this formula down to process an entire column of IP addresses.
The syntax is =GEOCODE_IP(ip_address). The function requires one argument: ip_address. This can be a direct text string like "192.168.1.1" enclosed in quotation marks, or a cell reference like A2 pointing to the cell containing the IP. You can also pass an array range like A2:A100 to geocode multiple IP addresses simultaneously.
A #NAME? error indicates Google Sheets does not recognise the formula, which happens when the InstaMaps add-on is not installed or enabled in the current document. To resolve this, install the add-on from the Workspace Marketplace. You can read our dedicated guide on the google-sheets-geocode-formula error for further troubleshooting steps.
IP geolocation is highly accurate at the country and regional level, but less precise at the street level. It typically pinpoints the internet service provider's routing hub rather than the exact device. Expect a margin of error up to 50 kilometres for rural IP addresses, making it reliable for regional traffic analysis rather than pinpoint device tracking.
Yes. Once you have the coordinates from =GEOCODE_IP(A2:A50), use the =INSTAMAP() function on those coordinate cells. =INSTAMAP() generates a live, hosted shareable map URL directly in your sheet. As new IP addresses are added to your logs and their coordinates update, the hosted map automatically reflects the new data points.
The free tier allows 100 IP lookups per day. If you verify your account with a free email unlock, this limit increases to 1,000 lookups per day. If you attempt to process a range exceeding your daily quota, the add-on will return a quota message, and you must wait 24 hours for the limit to reset.
Yes, the function fully supports both 32-bit IPv4 addresses and 128-bit IPv6 addresses. The formula automatically detects the IP format and returns the corresponding latitude and longitude coordinates. It does not require you to specify whether the input is IPv4 or IPv6 before running the function.
Stop bouncing between scripts and paid APIs to audit your server traffic. Install InstaMaps to instantly convert IP lists into coordinates and map them visually.
Install InstaMaps free