Excel Data Types: Tips on Using This Time-Saving Tool

Excel Data Types: Tips on Using This Time-Saving Tool

One of the cool updates we noticed recently in Microsoft 365 is the addition of over 15 new data types in Excel. 

At first glance, many users may just brush past data types as “one of those things that I have no idea what it does.” However, this particular feature brings a world of useful data to you inside your spreadsheets.

Data types has been around for a while, but for the longest time, it only included two types, stocks and geography. They were somewhat helpful for specific businesses, but not for the majority of users.

With the addition of more data types, this feature now holds the promise of hours of saved research time for users in just about any industry. As a top Fresno IT consultancy, we can see tons of ways this can help our clients.

For example, if you own a restaurant, you can find nutritional information on your recipes using the Foods data type. If you’re in the engineering field, you can tap into the power of the Terrain and Locations data types. 

Doctor’s offices, hospitals, and pharmacies have two data types that will be of particular interest, one for data related to anatomy and the other for medical data (including ICD-9 and ICD-10 codes).

How Do I Use Excel Data Types?

Say that you need to compare different universities for a report. You need to find things like acceptance rate, the cost for books and supplies, and student to faculty ratio. You can imagine how many hours searching for all that data on each school’s website would take. 

What if you could just type the university names into an Excel spreadsheet, tell Excel, “Hey, I’m looking up university data,” and then be presented with a list of various facts and figures to choose from?

Sounds like magic, right? But that’s exactly what data types does. It allows you to classify your data as a specific type (there are over 15 built into Excel and you can also create custom types). You’re connected to a database based upon the data type you choose with tons of current data about that type.

Data Types in Excel Step-by-Step

Let’s go through the university report scenario and at the same time, you’ll learn how to use Excel data types.

  1. Type your data into a column. In this case, we’ve added university names.
  2. Highlight the cells of data.
  3. Choose “Data” from the top menu to open that panel of options.
  4. In the “Data Types” window scroll to find Universities and click it.
  5. This converts your plain text data into the “Universities” data type.
  6. You’ll notice that you get a little data type icon that shows up to the right of your entry.
  7. If you see a question mark icon, that means Excel needs more information.
  8. In the screenshot below, it found two entries for Rutgers, so it is asking us to choose the one we meant.
  1. Once you’ve addressed your question marks (we’ll get into those more in a minute), highlight all the cells of data.
  2. You will see a small icon at the top that looks like a spreadsheet page.
  3. Click that for a list of available facts and figures.
  4. Click the one you want, and it will populate data for all the highlighted cells into the next open right-hand column.
  5. Do this as many times as you like to populate your spreadsheet with data.

You often have a full range of data options available in the database window.

Make sure to highlight all the cells that you want to populate data for.

Data types allows you to create a report that would normally take hours or days in just minutes! That’s how powerful this tool is. 

More Tips for Using Data Types

Explore Your Options & Test Them Out

There are many different buckets of information you have available in the data types feature, many of which are perfect for various industries.

It can be helpful to take a little time to explore your options and what source of data is the best to use. You may find that instead of having to look up nutritional information for recipes online and spend time copy/pasting that into a spreadsheet, that you can use a data type (“Foods”) and do it in a few seconds.

Here is an overview of the data types you have available, each one attached it its database of information. 

  • Anatomy
  • Animals
  • Chemistry
  • Cities
  • Currencies
  • Foods
  • Geography
  • Locations
  • Medical
  • Movies
  • People
  • Plants
  • Space
  • Stocks
  • Terrain
  • Universities
  • Yoga
  • Automatic (detects things like books, media, and more)

Try Different Term Variations If You Get Question Marks

When people first begin using data types, it’s not unusual for them to get a question mark from Excel because it doesn’t recognize an entry in relation to the chosen data type. 

If you run into this problem, then you may want to try other variations for the term you’re using and see if that is recognized.

You can tell when a term is recognized in that data type because the icon will change from a question mark to one that represents that data type (such as an apple for the Foods type or a leaf for the Plants type).

Explore Productivity-Boosting Features of the Cloud With Unity IT

Unity IT can help your Fresno area business explore game-changing features like data types and many other productivity-boosting tips for working in the cloud.
Contact us today to schedule a technology consultation at 559-297-1007 or reach out online.