Soon, Excel's Rich Data Type feature (aka Project Yellow) will be expanding with 100 new data types. Read the Microsoft blog post here: Introducing the new Microsoft 365 Personal and Family subscriptions
Recently, a question arose: Where is Excel storing the fields in the data type? In this episode, I will show you how to crack the Excel Workbook open and find that the information is actually stored in the workbook.
Table of Contents:
(0:00) Welcome and Discussion of the new Data Types coming
(0:30) 100 New data types coming to Excel
(1:38) Renaming XLSX file to Zip File
(2:18) Looking inside the XML files for Geography Fields
(3:30) Thanks and Subscribe
Recently, a question arose: Where is Excel storing the fields in the data type? In this episode, I will show you how to crack the Excel Workbook open and find that the information is actually stored in the workbook.
Table of Contents:
(0:00) Welcome and Discussion of the new Data Types coming
(0:30) 100 New data types coming to Excel
(1:38) Renaming XLSX file to Zip File
(2:18) Looking inside the XML files for Geography Fields
(3:30) Thanks and Subscribe
Transcript of the video:
Learn Excel from MrExcel podcast episode 2336. Where are the data type fields stored?
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
I've been thinking a lot about data types recently primarily because they've already publicly announced that there's a great new feature coming.
They say it's coming this spring - the spring of 2020. Here we are, it's June 19th.
There is only one day left in spring.
I get it - they are running late due to Corona Virus. No one is working in the office.
I'll put a link to this blog post down in the YouTube description.
They say they are coming out with a hundred new data types - like chemistry, food, movies, places, even Pokemon! I don't know why we need Pokemon in Excel but the whole thing with these hundred new data types.
A discussion here and especially after that episode 2332 - where I was doing an XLOOKUP into a single column of data types.
Where is it being stored?
Is Excel running out to the Internet every single time we calculate?
Or does it run out to the Internet once and store the data in the workbook?
I said, well wait, there's a simple way we can figure this out. I have three cities here on the data tab.
I'm going to come in and mark this as Geography. All right, it worked.
I am not going to pull any data out. We just have the map icon there. This is called ThreeDataTypeCells.
I'll click Save and then Close.
I will come out to that folder where that's stored.
Alright, now the first thing we have to do if you've never changed this setting (and I always change this setting).
Go into View, Options, Change Folder and Search options.
Under View, and uncheck the box for Hide Extensions For Known File Types. Click OK.
That allows us to see that this is a dot xlsx file.
To crack this open right click rename and change it from xlsx to .Zip. They warn you that it might become unusable.
No, it's really ok it's going to be fine. So we have a zip file here.
That's all an xlsx file really is. It is a zip file. Double-click to open that.
And the XL folder here. I'm going to go into that. Then rich data.
Data types were originally called rich data.
We now have 1, 2, 3, 4, 5, 6 different XML files. So, let's just take one of these. This one. Copy - Ctrl C.
Come out here to this folder. Right-click and Paste. All right.
And then we'll take a look at what's inside of this. We are going to Edit this. Alright. There it is.
Remember I had Miami.
And so now they've stored in here the URL to the picture of Miami. The description is " Image of Miami".
It says that it is in Florida. Here is the description.
So all of this data that backs up those Data Types is stored right inside the workbook, down in the XL folder and then Rich Data Types. So it is there it's right in the folder.
And then another question on that YouTube video about the XLOOKUP.
Someone asked "Well wait a second, do we need to have the internet every time we want to do this XLOOKUP?
No, you only have to have the Internet when you create the data type and then all of the data is stored right there in the file. So, that's kind of cool, I think.
I really hope by now we'd be able to take a look at these new data types with Food, Movies, Places.
If you are on the beta channel (which used to be called Insiders Fast) you could just check every couple of days here on the Data tab and see if something new pops in. It should be really cool when it comes along.
Now, if you liked these tips, please, right down there below the video, click "Subscribe" and ring that bell.
Feel free to post any questions or comments down in the comments below. My new book MrExcel 2020 Seeing Excel Clearly.
It's an e-book now. We'll be printing it later on.
click that "I" in the top right hand corner for more information.
Well I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
I've been thinking a lot about data types recently primarily because they've already publicly announced that there's a great new feature coming.
They say it's coming this spring - the spring of 2020. Here we are, it's June 19th.
There is only one day left in spring.
I get it - they are running late due to Corona Virus. No one is working in the office.
I'll put a link to this blog post down in the YouTube description.
They say they are coming out with a hundred new data types - like chemistry, food, movies, places, even Pokemon! I don't know why we need Pokemon in Excel but the whole thing with these hundred new data types.
A discussion here and especially after that episode 2332 - where I was doing an XLOOKUP into a single column of data types.
Where is it being stored?
Is Excel running out to the Internet every single time we calculate?
Or does it run out to the Internet once and store the data in the workbook?
I said, well wait, there's a simple way we can figure this out. I have three cities here on the data tab.
I'm going to come in and mark this as Geography. All right, it worked.
I am not going to pull any data out. We just have the map icon there. This is called ThreeDataTypeCells.
I'll click Save and then Close.
I will come out to that folder where that's stored.
Alright, now the first thing we have to do if you've never changed this setting (and I always change this setting).
Go into View, Options, Change Folder and Search options.
Under View, and uncheck the box for Hide Extensions For Known File Types. Click OK.
That allows us to see that this is a dot xlsx file.
To crack this open right click rename and change it from xlsx to .Zip. They warn you that it might become unusable.
No, it's really ok it's going to be fine. So we have a zip file here.
That's all an xlsx file really is. It is a zip file. Double-click to open that.
And the XL folder here. I'm going to go into that. Then rich data.
Data types were originally called rich data.
We now have 1, 2, 3, 4, 5, 6 different XML files. So, let's just take one of these. This one. Copy - Ctrl C.
Come out here to this folder. Right-click and Paste. All right.
And then we'll take a look at what's inside of this. We are going to Edit this. Alright. There it is.
Remember I had Miami.
And so now they've stored in here the URL to the picture of Miami. The description is " Image of Miami".
It says that it is in Florida. Here is the description.
So all of this data that backs up those Data Types is stored right inside the workbook, down in the XL folder and then Rich Data Types. So it is there it's right in the folder.
And then another question on that YouTube video about the XLOOKUP.
Someone asked "Well wait a second, do we need to have the internet every time we want to do this XLOOKUP?
No, you only have to have the Internet when you create the data type and then all of the data is stored right there in the file. So, that's kind of cool, I think.
I really hope by now we'd be able to take a look at these new data types with Food, Movies, Places.
If you are on the beta channel (which used to be called Insiders Fast) you could just check every couple of days here on the Data tab and see if something new pops in. It should be really cool when it comes along.
Now, if you liked these tips, please, right down there below the video, click "Subscribe" and ring that bell.
Feel free to post any questions or comments down in the comments below. My new book MrExcel 2020 Seeing Excel Clearly.
It's an e-book now. We'll be printing it later on.
click that "I" in the top right hand corner for more information.
Well I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
