Microsoft Excel, and similar spreadsheet software, may be the most used application beyond word processing. Certainly, Excel has a diverse user base and laundry list of use cases, which probably even includes actual laundry lists. Many of us are casual Excel users, and it seems there’s always a new feature to learn.
In a 45 minute video originally shared with his internal teams, former Microsoft Excel Program Manager Joel Spolsky drops gem after gem of Excel knowledge. You can watch the video above and find the top five insights we’ve already put into action in our spreadsheets.
1. Use Tables For More Than Just Pretty Formatting
You have probably noticed the table feature in Excel. It’s really useful for quickly switching style templates and implementing banded data, where rows alternate in color. In addition to borders and other stylistics, tables also come with some powerful data manipulation abilities.
When you use tables, it becomes really easy to sort, sum, and add new data. The formatting is useful for end users, but also helpful in that it signifies to Excel where certain types of data begin and end. Each field in the header row of a table has a dropdown that gives you quick access to sorting and filtering. Fields in the total row let you select from SUM, AVERAGE, and other common Excel functions.
The best part of using tables is how easy it is to add new data. If you include a formula within one cell of a column, it will optimistically be copied throughout the rest of the column. Or, need to add new rows? Simply tab from the last field of the last row and a new row is added above the total row. Sort by a column and the header and total rows stay put. Also, the sorting doesn’t interfere with any other data on this sheet. Everything is contained in its own little world in the table. This becomes especially useful once you name columns and look up data with INDEX, but we’re getting ahead of ourselves.
2. Paste Values to Remove Formatting and Formulas
Copying and pasting is the Excel user’s best friend. It makes some powerful assumptions about cell references that make it incredibly useful. However, there are times when what you see is what you want. That’s where Paste Values comes in, a little trick used by Spolsky throughout the session.
For example, you might generate a full name field by using a formula to combine first names and last names (don’t forget the space between). If you later want to remove the first and last name columns, your full name column will disappear… unless you replace it with the values created by your formula.
Try it out:
- Select an entire column that was generated by a formula
- Copy the column to your clipboard
- From the Edit menu, choose Paste Special, then select Values
You can now remove the first and last name columns (or whichever extraneous columns you used to create the column whose values you’ve just pasted). Note that you can also paste formulas, formats (including conditional formatting), and more. Paste Special is, indeed, special.
3. Provide Your Data Room to Breathe
You may notice what first appears to be a strange habit as Spolsky adds data to his spreadsheet. He always leaves Row 1 and Column A blank. Why do cells need room to breathe? Is it just his fastidious need for margin? Nope, it turns out there is a more functional reason: selecting and sorting.
When you leave space around a table of data, whether it’s an official table or not, Excel is able to make intelligent assumptions about what goes together. When it comes time to sort data, for example, Excel automatically constrains itself to the “island” of data surrounding the selected cell(s).
One of the reasons this habit may seem strange is if you don’t already put your data in tables. But you’ll probably do that now after reading the first item in our tips from Spolsky’s presentation. If you aren’t already convinced, the next two tips should make you a believer.
4. Give Names to Columns and Cells
Have you ever found yourself including a value in a function, copying the function down an entire column, only to have to change the value in the function later? Take the example Spolsky uses in the video: he multiplies each employee salary by a fixed tax rate. A better method is to include that value somewhere in your spreadsheet and reference the cell. That way, you can alter the tax rate and all the data that relies upon it will update.
It’s great to pull repeated values from other locations in your spreadsheet, but then you need to memorize the cell value. Worse yet, when someone attempts to understand your formula later on, they have to figure out where the cell is and what it represents. If you declare a name for that cell, you’ll be able to reference it not as J2 (or worse, $J$2 to create an absolute reference), but as TaxRate.
Similarly, you can name entire columns or other ranges of data. Spolsky named the Salary column, so that his formula could be simply
=Salary*TaxRate — Excel took care of the rest, intelligently calculating the correct values.
5. Superpower Your Spreadsheets With INDEX and MATCH
Things really got interesting when Spolsky added multiple tables to the same spreadsheet. By using official tables, giving them room to breathe, and naming columns, you can perform some powerful lookups on your data.
First, create a lookup table. Spolsky used city names and tax rates for a simple two column table. His main table also had a city name field, so he used the MATCH function to find which row of the lookup table’s city column matched the city for each employee. Like so:
Then, he used INDEX to find the tax rate based on the matching city:
=INDEX(TaxRates[TaxRate], MATCH([@Location], TaxRates[City]))
Finally, as shown in the image below, Spolsky multiplied the tax rate he looked up by the employee’s salary.
Be sure to sort your lookup table alphabetically by city name (or whatever value you are using MATCH on), otherwise it won’t know how to find what you’re looking for. But that prep is minimal when compared to the time these functions save you.
While not part of Spolsky’s video, one of our favorite uses of Excel is to store geographic data like addresses and postal codes. Selfishly, the reason we like it for location data is because of the really cool mapping application we’ve been building for the last decade. You can use all the tricks above to organize your data, then simply copy and paste into our mapping tool to generate a map.
Many in the US are tired of arguing over the future of the presidency, so we decided to take a look back at the past. There have been 44 US Presidents, required by law to be born in the United States. Of those, 39 have been buried, often in their home state—either by birth or adoption. The map below shows where each president was born and where each who has died is buried. You can use the BatchGeo Grouping feature to show only births or burials. Or, read on for our analysis of the story told by this map of presidential birthplaces and final resting places.
View US President Births and Burials in a full screen map
The first thing you may notice when looking at the map: there is something in the water in Virginia! Eight presidents were born in The Old Dominion, which is incidentally also nicknamed Mother of Presidents. Additionally, seven presidents are buried in Virginia, so the state is tops for births and burials. Five of the presidents born in Virginia were also buried there, which helps with that total. Two non-Virginians are buried in Virginia, both at Arlington National Cemetery: William Howard Taft and John F. Kennedy.
Ohio is one native son or daughter away from tying Virginia. Ohio’s seven presidents span the ninth, William Henry Harrison, through the 29th, Warren G. Harding. New York and Massachusetts (each with four) round out the only states with more than two presidents. In terms of burials, New York edges out Ohio, with six and five respectively. Tennessee is the burial place of three US presidents, despite not a single president being born in that state.
A president’s state of birth is not always the state we associate with that president. For example, Abraham Lincoln is claimed in Illinois as a native son, but he was born in Kentucky. Ronald Reagan, on the other hand, was born in Illinois. Due to his time in Hollywood, and as governor, we think of The Gipper as a Californian. Similarly, both presidents named George Bush are considered Texans. Yet, they were both born in the Northeast (Massachusetts for the elder and Connecticut for the younger).
Despite the split between birthplaces and burials, over half of US Presidents were born and buried in the same state. Six were even returned to their native city for burial: Martin Van Buren (Kinderhook, New York), Herbert Hoover (West Branch, Iowa), Franklin D. Roosevelt (Hyde Park, New York), Lyndon B. Johnson (Stonewall, Texas), Richard M. Nixon (Yorba Linda, California), and Ronald Reagan (Simi Valley, California).
It is interesting to note that no president has been born in New York City, Los Angeles, Chicago, or any of the top US cities by population. Further, most presidents were born in the eastern half of the country, which makes sense given that the US started with thirteen colonies and expanded westward. In fact, it wasn’t until after World War II that the US elected someone born west of the Mississippi River. Dwight D. Eisenhower was born in Denison, Texas. Only three other presidents have been born in the west: Lyndon B. Johnson, Gerald R. Ford (Omaha, Nebraska), and Richard M. Nixon.
For hundreds—maybe thousands—of years, it was hard to make maps. People dedicated their entire lives to the craft. While that’s still true today, now there are tools that enable cartographers to share their work so others can build upon their maps. Most importantly, there is a coordinate system that makes it easy for ordinary people to understand and describe points on the earth. While latitude and longitude points have ben around for centuries, GPS and web maps have greatly enabled our ability to use them.
A Quick Latitude/Longitude Refresher
Back in algebra class, you spent way too much time plotting points, lines, and expressions on a grid. These x/y graphs are a simple way to think of latitude (the y axis) and longitude (the x axis). Of course, the earth is not flat, nor even perfectly round, so the reality is more complex. But to make most maps, this is all you need to know.
Latitude of zero is along the equator. Using decimal notation, latitude extends north to 90 degrees and south to -90 degrees. Longitude doesn’t have an obvious zero marker, so the British made one up at the Greenwich Observatory in London. Everything due North and South of that point is zero longitude. To the east, the numbers increase until 180 degrees on the other side of the earth from London. To the west, the numbers decrease until -180 degrees meets 180 degrees in the middle of the ocean.
When we put latitude and longitude together, they form a pair of numbers that can be plotted on a map. For example, zero latitude and -78.455833 longitude is the Mitad del Mundo in Quito, Ecuador, a monument to “the middle of the earth.” If you go to 51.500833 latitude and -0.141944 longitude, you’ll find Buckingham Palace, across town from the Observatory (notice the near-zero longitude).
Often you’ll see decimal notation listed as a pair of coordinates separated by a comma. For example, San Francisco’s Golden Gate Bridge is at 37.819722,-122.478611. When working with coordinates, be sure that you know which number is listed first. You can do this by sanity checking a known location. In this case, you know San Francisco is in the western hemisphere and should have a negative longitude. Also, latitudes are never greater than 90, so that’s another hint here. We go into some greater detail on other ways coordinates are formatted in our latitude and longitude coordinates page.
Mapping a List of Coordinates
Now that you have the basics of latitude and longitude down, let’s make a map! If you know how to code, you could use the Google Maps API directly. But that’s the hard way to make a map. Let’s look at what it takes to build a map from a simple spreadsheet.
Let’s say our spreadsheet looks something like this (your own version probably has many more rows):
Due to potential confusion over the order of coordinates, it’s a good idea to separate them out. Here’s a quick way to do that using Excel-compatible formulas:
- Add two new columns titled Latitude and Longitude
- To find the number to the left of the comma (latitude in our example), use the formula =LEFT(F2, FIND(“,”, F2)-1) where F2 is the first cell with the pair of coordinates. Then copy that formula down the column.
- To find the number to the right of the comma (longitude in our example), use the formula =RIGHT(F2, LEN(F2)-FIND(“,”, F2)) where F2 is the cell with the first pair of coordinates.
To account for the presence of a space after the comma, you can put a TRIM function around each of those formulas. But that’s optional. The important part is separating the coordinates.
View Example Latitude/Longitude Map in a full screen map
Now you can copy your spreadsheet rows, including the header, by highlighting and using the Ctrl+C command (Cmd+C on Mac) to copy it to your clipboard. Now, go to this map-making tool and paste (Ctrl+V, or Cmd+V on Mac) your spreadsheet data into the box. The result is a beautiful web map like the one above, ready to be saved and shared.
Use Geocoding to Find Coordinates of Addresses
If you don’t already have latitude and longitude points for your data, you can use our batch geocoding tool. You can convert addresses, postal codes, city names, and even some landmarks into coordinates and plot them on a map. Just copy and paste your spreadsheet data into the box.
More data does not produce more insights unless you have a way to analyze the data. In fact, that’s why there are hundreds of functions build into Excel and other spreadsheet software. The functions are what make up its most important functionality. Yet, you can’t use =HEATMAP to create a visualization of geographic density in your data. But what if that was almost as easy as an Excel function?
If you’ve spent any time using Excel, you’ve likely spent what feels like a similar amount of time searching the internet for help with its functions. You probably have come across many custom functions written by other Excel enthusiasts. Though the authors mean well, these sub-routines are often hard to use and have questionable security. For this and other reasons, BatchGeo made sense as a separate web service. We make Google Maps—and heat map overlays—as easy as copy and paste.
In our in-depth Google Map tutorial, we show some of the technical steps you get to skip when you use BatchGeo. Here’s how to create a basic marker map, which we’ll convert to a heat map in the next section:
- In your spreadsheet, highlight and copy (Ctrl+C, or Cmd+C on Mac) all rows, including your header row.
- Go to our map making tool and paste (Ctrl+V, or Cmd+V on Mac) your data into the box.
- Click “Map Now” and follow the steps to complete your map. We make intelligent guesses of location columns, but you can override them in the Validate & Set Options menu.
You’ll see a preview of your map and, if it looks good, you can save it. Include your email address so you can make edits in the future.
Now you have a map like the one embedded above. All the other data in the map is browsable by clicking individual markers. And we even get an idea of their density by the overlapping markers.
But it’s not a heat map. Not yet.
How to Make a Heat Map From Your Excel Spreadsheet
Now that you have a Google Map, you can easily activate the heat map layer with Advanced Mode available in BatchGeo Pro (30 day money back guarantee).
While signed in and viewing your map:
- Ensure Advanced Mode is enabled by clicking your account menu in the upper right.
- Right click (Ctrl+click on Mac) over your map and select “Heat View”
The markers on your map will disappear and be replaced by a heat map view.
You can zoom and pan your heat map as you would any Google Map. Our customers have used heat maps to gain valuable insights from open data, competition research, and more. Many have made decisions that saved or made their company thousands of dollars, or more.
Some example heat map use cases include:
- Map all places in a business category to determine potential new locations that are under-served.
- Map customers or leads to choose equitable sales zones or regions.
- Map a city’s violent crime to show hot spots for a comprehensive journalistic report.
It’s unlikely that Excel will soon have a =HEATMAP function of this caliber, so we’ve created our web service to fill this gap.
Buying a house can be an intense experience. Every hour of available time can go to searching, researching, driving by, and viewing potential purchases. Unlike other things you buy, there are no five star reviews or apples-to-apples price comparisons. That’s why visiting a home and neighborhood are so important. To simplify viewings, many listings have open houses, which means buyers plan their Saturday and Sunday afternoons around hour ranges found in real estate listings. Many BatchGeo users have discovered how much easier the planning can be with a geographic view of their chosen open houses.
View Open Houses This Weekend in a full screen map
Your real estate map starts with a spreadsheet you’re using to track your potential future homes. What you include may vary, but it’s likely to have the address, price, and number of bedrooms and bathrooms. For open houses, you can separate out the timing into its own spreadsheet column. In the example map above, we can group by any of that data, which BatchGeo either shows as a range or unique values. Click a marker on the map and you’ll see all the data from the spreadsheet, including that open house timeframe.
Let’s see how you could create your own, how to skip the manual work, and look into a method you could use to simplify finding open houses while on the go.
Create a Real Estate Listings Spreadsheet
The modern real estate search includes looking at hundreds of listings on the web. You get new listings delivered to your inbox, and you scour search sites to find the neighborhoods and criteria you’re after. What do you do when you discover a potential choice? Before you see it, you need to track it.
For many, including BatchGeo users, the tracking happens in a spreadsheet. The header row includes the location columns, bedrooms, bathrooms, price, and open house times.
As you find the places you’re interested in seeing, just jot those pieces of info into a spreadsheet. If it’s worth the time to see, it’s worth the time to track in your spreadsheet. If it’s too much work, you may be able to use a site such as import.io to convert website searches into downloadable data.
Group by Open House Hours
You may find yourself desiring even more automation, with a solution that tells you exactly the order to see the houses during your tours. This turns out to be difficult or inefficient (what Computer Scientists call the Traveling Salesman Problem). However, the BatchGeo mobile maps features can help you determine where to go next while you’re out and about. All it takes is a little more setup in your spreadsheet.
View Open Houses, Filter by Times in a full screen map
The above map has the same locations as the first map in this post, but we’ve added another eight columns to the spreadsheet for the potential open house hours. We noted that our listings started no earlier than 10:00 a.m. and ended no later than 2:00 p.m. — four potential hours for showings on Saturday and another four on Sunday. If you find yourself ready to go to an open house at 1:17 p.m. on Saturday, just group the “Sa1” field with “Y” to see the four listings that are open during the one o’clock hour.
It’s a little more up-front work, but it helps you be able to filter your map in the moment. Can you fit one more viewing in? That depends on how close the open house listings are, a question you can arrange your spreadsheet and map to answer.
Are you ready to use BatchGeo to find your next home (or perhaps wow your home buyer clients)? See how BatchGeo can help you with mapping solutions for real estate.