Author: Adam DuVander

Use an Excel Pivot Table to Count and Sum Values

So, you have data and you need a quick way to make sense of it. A PivotTable is a great option and it is one of Excel’s most powerful tools. We’ll walk you through what a PivotTable is, preparing your data for a PivotTable, quickly performing analytics using a PivotTable to Count and Sum your data, and finally, overlaying your PivotTable data onto a map using sum clustering.

What is a PivotTable?

First, let’s establish what a PivotTable is and what it can do. A PivotTable is a quick and easy tool within Excel that allows users to easily summarize data. Now, most regular tables have summary rows at the bottom such as a Sum to show the total sales of all products in all states or a Count of all of the entries included within the table.

However, a PivotTable takes those summaries a step further by allowing users to quickly answer more specific questions such as the total sales broken down by each product, state, or even city with just a few mouse clicks.

Preparing Your Data

Now that we know what a Pivot Table is and what it can do, the first step to create one is to prepare your data by organizing it into a single worksheet, preferably into a Defined Table.

Organizing Data into a Single Worksheet

It’s common to have data stored in multiple places, like separate tabs for various time periods or products. In order to view all of this data within your PivotTable, you’ll need to combine it into a single worksheet. The simplest way to do this is to identify the difference between each data source and create a new corresponding column within your combined worksheet to store that differentiator. For example, perhaps a different salesperson manages each business segment resulting in a separate workbook for each segment as pictured below.

*3 Separate Worksheets for each Segment*

To combine this data, we can create a new column called “Segment” anywhere within the data set and populate the rows with the corresponding segment name as we copy and paste all the data into a single table.

*All 3 Segment Worksheets Combined with a new Segment Column*

Pro Tip! – If your data already includes a date field, there is no need to add an additional column for the time period identifier. The date field can be used to break the data back out into the applicable time periods once we create our PivotTable.

Creating a Defined Table

Now that you’ve organized your data into a single worksheet, you can save yourself time down the road by identifying the data as a Defined Table. Do this by clicking anywhere within your data and choosing the “Format as Table” option on the “Home” ribbon. A major advantage of creating a Defined Table upfront is that your PivotTable can be kept current over time even as the underlying data is updated. All you have to do is toggle the “Refresh Data” option within your PivotTable to pull in any new or modified data.

Create a PivotTable to Count

If your goal is to determine how many times a specific event occurred, such as how many distinct customers made a purchase or how many sales were generated within each city, a PivotTable configured to Count records is exactly what you need. To start, if you already have your data within a Defined Table, simply click anywhere on your table and choose “Summarize with PivotTable” from the “Table” ribbon.

*Find the “Summarize with PivotTable” option within the Tools section of the Table Ribbon.*

Alternatively, if your data has not already been organized into a Defined Table, you can select your data manually by clicking the top leftmost cell within your dataset and then dragging down to the bottom rightmost cell. At this point, you can click “PivotTable” from the “Insert” ribbon.

*Find the “PivotTable” option within the Tables section of the Insert Ribbon.*

From there you’ll be able to choose which data point you want to count by selecting the checkbox next to the data in the right-hand PivotTable Fields settings that automatically open when creating a new PivotTable.

By default, Excel will sum the data as it sees that we have chosen a numerical field. We can change this by left-clicking on the “i” button on the far right corner of the “Sum of Sales” value. This will give you several formula options to choose from. We’ll choose “Count” which results in a count of all sales record instances.

Pro Tip! – Save time by formatting your data columns with the correct field type from the start such as Date, Number, or Text. Excel will automatically sort by Date data, Sum numerical data, and Count text or mixed data.

*Default Text/Mixed Data Count PivotTable*

Now, let’s let Excel do the heavy lifting! Simply drag the “City” column from the list of fields to the “Rows” box within the PivotTable settings to break down the number of sales by city. You can also increase the depth of the PivotTable by dragging in an additional field, such as the “State” field, to the Filter selector in order to drill down into the data you are most interested in.

*Basic PivotTable to Count with Filter Applied*

Create a PivotTable to Sum Values

There are other instances in which using the Sum of the data rather than the Count is more useful. In order to sum the data, go back to the “i” on the right-hand side of the “Count of Sales” field and choose “Sum”. Now we can see the total sales revenue broken down by each city.

*Basic PivotTable to Sum with Filter Applied*

Depending on your goal, it is often helpful to stack different fields within the Rows or Columns selectors. For instance, placing the “State” field above “City” in the below example allows us to quickly see not only the highest-grossing states but also the individual city contributions within each state.

*PivotTable to Sum with Additional Row Selector*

One of the most beneficial aspects of a PivotTable is that they are dynamic. You can move the fields around between Rows, Columns, Filters, and Values boxes on the fly to gain perspective and play with different analyses. Spend a moment moving the fields you are interested in between the boxes to get a better feel for how the PivotTable works. You may be surprised how quickly you can discover new insights!

Create a Map of Sales Data

Now, if you have geographic data such as addresses, cities, or states as in the sales examples above, you can take your data analysis to the next level by visualizing the data on a map with sum clustering. Just like when we summed up our data in a PivotTable, BatchGeo’s mapping service has an advanced clustering feature. When enabled, this feature allows you to sum up the values of a specific field as a label for each cluster.

Pro Tip! – To enable sum clustering:

View Example Sales Data in a full screen map

Here you can see sum clustering data analysis on sales data broken out by city or state. Regions are clustered together and the cities and states are averaged. As you zoom in or even click on a cluster, you’ll see smaller clusters that demonstrate how the smaller areas contribute to the overall sum.

US Retailers by Revenue: $2.7 Trillion in Sales on a Map

As another year comes to an end, we took a look back to see which stores competing for business did the best. Thanks to company reports of retail sales (all of which are in the billions, by the way), we know the top 100 stores of the year. The retailers that made the most include Walmart and Amazon (unsurprisingly), though one wholesale warehouse ranked in the top ten with just under 530 stores. To put it in perspective, the majority of the top ten companies of 2019, and the top 100 for that matter made their billions of dollars in sales by owning at least 1,700 stores throughout the country. This just goes to show that more stores don’t mean more sales. Or maybe the stores that didn’t make the cut aren’t in the right market because there are categories of stores that make more cash than others, like supermarkets and fast food places. There are also states and cities where more successful stores operate their headquarters. Find out where in the U.S. the most top stores are based and more as you continue reading about the top 100 stores of 2019.

View Top 100 Stores of 2019 in a full screen map

The map above depicts Kantar’s data for the top stores of the year in grouping columns, the most useful of which are the 2018 retail sales (billions), the store category, and the number of stores.

Billion-Dollar Stores

Of the 100 top stores of 2019, ten stand out the most in retail sales.

Rank	Company	Category	2018 retail sales (billions)	2018 stores	Headquarters City	Headquarters State
1	Walmart	General Retailer	$387.66	5,263	Bentonville	AR
2	Amazon.com	E-commerce	$120.93	490	Seattle	WA
3	The Kroger Co.	Supermarket	$119.70	3,035	Cincinnati	OH
4	Costco	Membership-Only Warehouse Club	$101.43	523	Issaquah	WA
5	Walgreens Boots Alliance	Drug Store	$98.39	9,451	Deerfield	IL
6	The Home Depot	Home Improvement	$97.27	1,969	Atlanta	GA
7	CVS Health Corporation	Drug Store	$83.79	9,954	Woonsocket	RI
8	Target	General Retailer	$74.48	1,844	Minneapolis	MN
9	Lowe’s Companies	Home Improvement	$64.09	1,723	Mooresville	NC
10	Albertsons Companies	Supermarket	$59.71	2,249	Boise	ID

The #1 top store of 2019 is Walmart. The general retailer made $387.66 billion in sales throughout its 5,263 stores this year. Walmart is headquartered in Bentonville, Arkansas. The only other Arkansas-based stores to make the overall list of the 100 top stores are Dillards (#71) and Discount Tire (#91) but the #1 Walmart outearned them both by at least 63%. As for #2: it’s Amazon. The e-commerce giant brought in 120.93 billion dollars in 2019. The H.Q. is located in Seattle, Washington, and is only one of four top 100 companies to conduct business from the Evergreen State. Amazon’s 490 brick-and-mortar stores may come as a surpsie as we tend to think of the company as solely e-commerce. However, Amazon’s physical stores include Whole Foods, Amazon Books, Amazon Go, AmazonFresh Pickup, Amazon Pop-Up stores, and all their package pickup locations.

Following Amazon is the U.S.’s largest supermarket chain: the Kroger Co. It’s one of eight Ohio-based retailers serving up top sales this year. Based out of Cincinnati, you may not immediately recognize the stores associated with The Kroger Co. As of 2019, The Kroger Co. owns Dillons, Food 4 Less, Fred Meyer, Fred Meyer Jewelers, Fry’s, Harris Teeter, Home Chef, King Soopers, the Little Clinic, Mariano’s QFC, Ralphs, Roundy’s, Ruler Foods, Smith’s, and Vitacost. All that to say, the Kroger Co. and its 3,035 stores made $119.70 billion in retail sales — just 1.23 billion less than Amazon.

Costco is next. The wholesale warehouse best known for its free samples made $101.43 billion in sales last year. With just 523 stores, Costco is the only retailer other than Amazon (though Amazon makes most of its sales online, not in-store) to rank in the top ten with less than 1,500 stores. Costco is similar to Amazon in that its headquarters are located in Washington, though in a city called Issaquah. Costco is also the last retailer to hit over 100 billion dollars in sales on the list.

Walgreens Boots Alliance comes in at #5. The drugstore’s 9,451 stores made just under $99 billion throughout the year. One of eight top retailers based in Illinois, Walgreens (H.Q. in Deerfield) owns the most stores of the top ten up until this point. The Home Depot, CVS Health Corporation, Target, Lowe’s Companies, and Albertsons Companies round out the top ten retailers of the year. Each of these successful stores earned at least $59 billion and they own over 1,000 stores across the country.

More Stores Don’t Mean More Sales

We noted both Amazon and Costco made over 100 billion dollars with only 490 and 523 stores respectively. This just goes to show that more stores don’t mean more sales. Other examples include Ikea. With just 49 stores, Ikea still ranks at #72. It beat GameStop, which has over 3,800 stores throughout the U.S. and the furniture emporium is also neck-in-neck with Dillards’ 5,371 stores. Additionally, Apple Stores/iTunes managed to rank at #11 despite only having 271 stores. Both Macy’s and H.E. Butt Grocery also have well-under 900 stores yet they still make it in the top 20.

On the other hand, we have Subway. The sandwich shop has the largest amount of stores out of all 100 companies: 26,932. Yet, it’s only ranked at #43. The lowest-ranked company with the most stores appears to be the Shell Oil Company. Even with 4,406 stores, Shell only ranks at #99.

Common Cash Categories

We sorted the top stores of 2019 into 12 distinct categories. The category of store that appears most frequently appears on the map is apparel. Seventeen apparel stores are ranked within the top 100 stores of 2019. The second most common category is restaurants. There are fifteen of these throughout the map. McDonald’s is the highest-ranking restaurant on the list at #14, which is not bad for a business dedicated to fast and cheap food. Yum! Brands ranks second in fast food (#24) followed by Starbucks, and Subway, and 11 others.

Supermarkets also make a lot of sales. There are 14 on the map, including the Kroger Co. and Albertsons Companies, which are the two supermarkets making the most. While there weren’t enough dollar stores and related companies on the map to merit a mention above, we thought it was notable that these companies where everything is usually $1 or less appear on this list twice and quite high in the ranks. Dollar General, for example, is ranked at #18 and made $25.63 billion throughout the year. Not far behind was the Dollar Tree. As the #21 top store of 2019, the Dollar Tree brought in about $22.48 billion. Absent from the top 100 is the 99 Cent Store.

Successful States & Cities

A number of top stores are headquartered in Texas: 11 to be exact. The ranks of these Texas-based companies range from #20 (H.E. Butt Grocery) to #99 (Shell Oil Company). Dallas, Texas is the only city in the U.S. where four top stores have headquarters. 7-Eleven, AT&T, Army & Air Force Exchange Service, and Neiman Marcus are all successful companies based in Dallas.

In addition to Texas, many top retailers are based in California (nine), most of which are located in San Francisco. There are also eight top stores working out of both Ohio and Illinois, and six in Pensylvania, though no city in Ohio, Illinois, nor Pensylvania is home to more than two top companies. Seattle is home to three of Washington’s four top companies. That puts the Emerald City second to Dallas for the number of companies in the top 100. San Francisco and New York also have three companies each.

In addition to company reports of retail sales, the end of a calendar year also brings fun events for the family, like holiday light displays around the snow globe and the ability to map your holiday cards list. But the end of the year also means getting organized for the new year. If you have a bunch of business data you need to dive into, let BatchGeo help you use an Excel pivot table to count and sum values or teach you about Excel data visualization examples to make sure you start the year off right.

Latitude and Longitude in Excel: Calculate Distance, Convert Degrees, and Geocode Addresses

If you have a long list of geographic coordinates to work with, a Microsoft Excel spreadsheet is sure to be useful. There are three basic Excel tools that can work for you, no matter how you want to manipulate your geographic coordinates. You’ll need to know how to calculate the distance between two latitude and longitude points, how to convert latitude and longitude data to decimal degrees, and finally, how to geocode latitudes and longitudes.

How to Calculate the Distance Between Two Coordinates in Excel

In an example of how to calculate the distance between two coordinates in Excel, we’ll seek to measure the great circle distance. We’ll note that latitude and longitude are denoted in degrees, minutes and seconds.

Convert Latitude and Longitude to Decimal Degrees in Excel

Everything, including something as complicated as degrees, minutes, and seconds, can be converted into decimals. The degrees part remains the same, but minutes and seconds need to be converted into their percentage of a degree and combined. There are 60 minutes in a degree and 60 seconds in a minute (which means 3,600 seconds in a degree). Therefore, divide minutes by 60 and seconds by 3,600.

The overall formula:

Total Degrees (in the decimal form) = Deg + [Mins / 60] + [Seconds / 3600]

…So About That Distance

Now that you have the latitude and longitude values in decimals, you just face the equation for the nautical miles between Timbuktu and Casablanca, noted underneath.

Nautical Miles= ACOS [(sin(Lat_place_1*PI()/180)*sin(Lat_place_2*PI()/180)+

cos(Lat_place_1*PI()/180)*cos(Lat_place_2*PI()/180)*

cos(Lon_place_2*PI()/180-Lon_place_1*PI()/180)) ] *3443.8985

That three thousand number, in the end, is the radius of Earth, in, Nautical Miles. Even if you were to substitute it with the radius of a sphere, assuming the Earth is spherical, at 3437.7468 NM, you will not be near the real, accurate distance.

Figure: You only have to enter that formula into EXCEL ONE time.

Figure: The 4476 number is the distance in Nautical Miles. The decimals are pretty much useless since you know it’s an approximation.

Geocode Latitude and Longitude in Excel

Geocoding is the conversion of street addresses to latitude and longitude data that can be mapped. Now, there are two ways to geocode latitude and longitude from an Excel spreadsheet: the hard way and the easy way.

We’ll cover the hard way first, which is what you’ll need if you require the coordinates to live in your Excel document. You’ll need to write the Visual Basic (VBA) script to make a call to an external geocoder, or find the code elsewhere, such as this GitHub repo. There are a number of geocoder APIs and some geocoders are faster than others.

The Google Maps Geocoding API is a common choice and this is the API we’ll call in the easy option. However, it also can work directly within Excel. Armed with the code you’ve written or discovered, here are the steps to deploy geocoding within Excel.

Step 1: Find the Developer tab in your version of Excel.

Step 2: Look for the “Visual Basic” tab. This opens the development window.

Step 3: Insert —> Module. The new module will hold your function.

Step 4: Here is where you copy and paste your code.

Step 5: We need to add references now. So, Tools —> References. Make sure to check the Microsoft XML v6.0. CLICK OK.

Step 6: Press CTRL / CMD + S. Save as type —> Excel Add-In. Now you get to name your function. The extension has to be .xlam. Time to close all Excel files.

Step 7: Open a new file. File —> Options —> Add-Inns. In the Manage box, choose Excel-Add Ins.

Step 8: Press Go. Choose your function in the window that appears by ticking in the box. Press OK.

Step 9: VOILA. Now you could just use the new function you created, like any other function.

After you are done retrieving the values, copy and paste them elsewhere, off of the sheet with the calculations. Then delete the function calls because otherwise when you reopen this file, the Google API will start to be pinged again. That will eat up several minutes before the file is open. To optimize Google resource usage, take a 10-second break between addresses.

That’s a lot of steps, and assumes you’ve found some code to try out. Next, we’ll see the easier way to geocode your Excel data. It’s fast and reliable, but it won’t import the coordinates into your Excel file. On the other hand, the excel geocoding tool is copy-paste simple and gets you an interactive map.

Create a Map with Excel

There are a number of ways to create maps with Excel data. Perhaps the easiest is to just copy and paste your spreadsheet data into our map-making tool. Doing so turns your Excel document into a beautiful, interactive map like the one below.

View Example Latitude/Longitude Map in a full screen map

To make your own map based on latitude and longitude coordinates, separate the coordinates into their own columns within your spreadsheet.

Then, select and copy the rows and columns of your spreadsheet (Ctrl+C command or Cmd+C on Mac) and navigate to batchgeo.com. Paste (Ctrl+V or Cmd+V on Mac) in your data, and you’re done!

Whether you want to make a latitude and longitude map or create your own virtual wedding guide for your guests, BatchGeo provides a free and easy way to make beautiful web maps that are ready to be saved and shared.