Welcome to another entry in our series on Dr. Wayne Winston’s best Excel tips! I’m Wayne Winston, Becker’s resident expert on all things Microsoft Excel. Let’s learn another helpful Excel skill that can help make your everyday accounting tasks a little bit easier.
I (and John Cougar Mellencamp) live in the “Small Town” of Bloomington, Indiana. Scattered throughout the Internet is lots of information on Bloomington. But wouldn’t it be great if I could easily download information on my small town into Excel?
I love Adele and her music (although Linda Ronstadt is the best singer in history!) Suppose I want to download information on Adele’s music into Excel. I could scour the Internet for a list of her albums and songs on each album, but that would be tedious.
Fortunately, if you have the newest version of Office 365, Excel’s New Data Types feature make it easy to download thousands (maybe millions?) of pieces of useful information into Excel.
To see if you have access to the New Data Types, go to the Data Types group on the Data tab and scroll down. If you see the list of data types shown in Figure 1, then you have the new data types.
Figure 1: New Data Types
Figure 2 contains a brief description of these Data Types (I know nothing about Yoga!)
Figure 2: Description of New Data Types
This downloadable Excel workbook contains many examples of the information available to you. But first, you must make Excel recognize the appropriate data type.
In row 3 of this data worksheet, we have entered 17 items about which we want information. If you select all 17 items (with Control+C) and then choose “Automatic” from the Data Types group, Excel will try and guess the appropriate data type and place an icon for that data type in the cell.
For example, in cell G3 of the data worksheet, Excel has classified Yale as a university, indicated by the cap and gown icon. When you see a question mark, you can go to the bottom right-hand portion of your screen and choose the correct data type. For example, for Yellowstone, you would probably select the National Park and not the Museum. For Topaz, you probably want the mineral and not the Leon Uris book or movie based on his book. Of course, if you desire, you could just select Topaz and tell Excel it is a mineral.
Once Excel associates the contents of a cell with a data type, you simply go to another cell and type the cell address, and a list of available information pops up.
For example, cell F5 contains my zip code (47401). After typing F5 in a cell, the information (only a subset of the list) is shown, as seen in Figure 3.
Figure 3: Information available on a Zip Code
In cell F6, I chose “average house value” with the formula =F5.[average house value]. In cell E10, I entered Adele. As shown in Figure 4, entering the formula =E10.albums in cell E11 returns a list of all of Adele’s albums. If you are interested in the album Nineteen, then you might type in cell F11 the E12.tracklist to generate a list of the album’s tracks.
Figure 4: All you ever wanted to know about Adele
Please check out all the other interesting information I obtained with the data types, especially the picture of the Beagle (I’ve never seen a beagle that thin!)
Other notes
- You can convert a cell with a data type back to text by right clicking on the cell; after selecting Data Type, choose Convert to Text.
- You can refresh all information about a data type by right clicking on the cell; after selecting Data Type, choose refresh.
- Formulas involving Data Types can be copied like ordinary formulas. For example, if you had a list of every zip code, you could copy a formula and obtain the average home value in each one.
In conclusion….
These new data types might be the best thing Excel has ever done! They will revolutionize education and business. A science teacher in K-12 might tell her students to evaluate the nutrients contained in their family’s daily diet. A marketing analyst might use demographic information about zip codes to determine the demographic variables that best predict sales of their product. The applications of these new data types are limited only by your imagination!
Next month, we will show you how to use Data Types to obtain historical weather data. I can’t wait!
Read Dr. Wayne Winston’s latest tip on using the Excel Stockhistory function.
