How to remove a ProPlus subscription from your computer: Reset Microsoft 365 Apps for enterprise activation state - Office
New in Office 365 Home/Student: Wolfram Alpha Data Types. After Excel added Geography and stock data types last year, we now have a hundred new data types. Each is curated content from Wolfram Alpha.
Beyond the new data types, we have several new features:
New! Data Types can return an array of values!
New ARRAYTOTEXT() function and VALUETOTEXT() functions.
A Data Type can return another Data Type!
Data Types can return images (but you will have to resize the cell).
New in Office 365 Home/Student: Wolfram Alpha Data Types. After Excel added Geography and stock data types last year, we now have a hundred new data types. Each is curated content from Wolfram Alpha.
Beyond the new data types, we have several new features:
New! Data Types can return an array of values!
New ARRAYTOTEXT() function and VALUETOTEXT() functions.
A Data Type can return another Data Type!
Data Types can return images (but you will have to resize the cell).
Transcript of the video:
I chose the right shirt for today. The #BUSY.
Learn Excel from MrExcel podcast episode 2341.
Wolfram Alpha Data Types in Excel - The First Look.
Hey, welcome back to the MrExcel Netcast. I am Bill Jelen.
Some exciting new things that debuted today.
We've had stocks and geography data types for over a year.
But a whole bunch of new items: Chemistry, Elements, Space, Satellites. It is crazy how much new stuff they have. Now, first the bad news.
It is a hassle to get this. It has actually been out for a couple of weeks.
I could not get it on any machine. You need Microsoft 365 Home (or Student).
Which I had.
BUT: never having signed into that computer with ProPlus ever.
That was the one that killed me.
I finally found a link, which I am going to put in the YouTube description below, for the exorcism instructions.
You need Office Insiders, beta channel and to be in the lucky 50%.
Now, beyond the new data types, the exciting new things we have: A data type can now return an array of values, which is awesome.
Two new functions that we are hardly ever going to use:ARRAYTOTEXT() and VALUETOTEXT() A data type can now return another data type or be used in data validation. Data types can return images.
Although the image fits in the size of a cell so you are going to have to resize the cell to make the image visible. All right. Let's just give it a try.
I have a list of some of my favorite movies here. I am going to go up to Data, Data Types.
They have a category for movie. So I choose movie.
and it's going to go out to the Internet and try and figure out what i'm talking about.
Movies - unfortunately - there is a lot of ambiguity in these.
So we are going to get some that we're going to have to go through and select.
They say they don't know the Blues Brothers.
So over here, The Blues Brothers or the sequel in 2000. Of course, the original that is the one.
Ocean's 11.
The one from 1960 (which is on TCM on Aug 11 2020), or this one - the new one - with George Clooney. Footloose? The original.
Dirty Dancing? The original. And Caddyshack or Caddyshack II.
Clerks. Titanic. Men in Black - the original.
All right. There we go. Now let's add Movie Title up here as a heading.
These always work better as tables. So Ctrl+T to format as tables.
That allows us to add new fields.
We have all kinds of things like what's the genre?
We can get what's the rating. We can get the run time.
You know, these are the kinds of things that you would expect that you could get from the "old" Geography data types or things like that.
But: they also offer things that have multiple items.
For example, Cast and Roles.
What happens here is that I get Tom Cruise as Joel Goodson and Rebecca de Mornay as Lana.
And so on. Take a look at this.
They automatically wrap this in ARRAYTOTEXT - a brand new function that takes an array of values puts them into a single cell with commas in between. Ignoring errors I believe.
Then VALUETOTEXT would do the same thing with values. These two functions are actually in your Office Insiders right now.
With either Home or Enterprise. When it arrived in Enterprise, we tried to figure out a use case.
Well, it is for this use case. All right, here let's do one more.
We are going to add the image! look at all those hash busy errors as it downloads those images.
The hassle of course is they are too small.
All right, so rather than do data types like you've been doing in the past where you put all the geographies and just copy it down, I think the better way to do this is to build a dashboard for a single item.
So here's the same list of movies. Over here I did Data Validation (Alt+D+L).
Set a list, based on that source. Click ok.
And now i can choose from the list. All right so we'll come in here and choose Men in Black.
all right. Instantly it goes out.
This field is F4.Image and H6.Image. So F4 is the item that I chose. =F4.Image is the movie poster.
Down here -- this is awesome -- this is =F4[ Cast and roles ] without being wrapped in the ARRAYTEXT function and it spills like any other dynamic array would spill, which is awesome.
Okay. Then I have Will Smith. He is the top item.
So i come over here and say =F6.Cast which converts it to a person.
Once I've converted Will to a person then I can do =H6.[ Full Name ].
So this cascading idea where we're taking the movie, Getting the cast, Getting the person, Getting information about the person.
Then, notable film appearances gives me a list of all the film appearances.
But not in any particular order that I can tell.
So here I have =H9# of the box office total receipts. That gets those receipts for the entire array.
If there were any errors then I put comma zero.
Then over here a SORTBY function.
One of the new dynamic array functions that sorts the array in H9# by the box office in I9#.
In descending order.
So then I get Will Smith's movie appearances, high to low, based on U.S. box office.
And this all works perfectly.
So right up here, if I choose a different movie: Ocean's 11. I get the movie poster.
I get all of these #BUSY! errors. The shirt is for sale down on the Merchandise shelf.
Feel free ... we are all #BUSY! right? I get it Excel...
You'll eventually get to it. George Clooney.
And then there are all of George Clooney's movies, sorted by box office over there.
This is pretty pretty cool and actually I could spend three months going through all of the different items that they have: Movies, Artists, Science, Rockets.
Why didn't I do rockets? Good question! So take a look at this list.
If there is anything here you want to see, write me a note down in the YouTube comments below and I'll try and create create something unique for you.
So these brand new Wolfram Alpha data types.
The hassle is you have to be on a Home or Student subscription, never signed in using Pro Plus.
I had plenty of computers that have the home or student, but at some point along the way i signed it in to Pro Plus.
I finally found that link about how to get rid of that Pro Plus account. You can't do it in the Excel interface.
It's tough to do.
I want to thank you for watching.
Please, down below, click Like, 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.
Click the "i" in the top right hand corner for more information about that book. Well these are pretty exciting.
The new Wolfram Alpha data types.
Equally exciting is the fact that data types can now do more things.
Remember there is still a goal that hopefully someday we'll be able to put *our* data into the data types and just think that now we can return arrays and images and all kinds of great things like that.
I want to thank you for stopping by we'll see you next time for another netcast from MrExcel.
[ ♪♪ ] Tips and tricks, get your spreadsheet fix, at MrExcel.com.
[ ♪♪ ]
Learn Excel from MrExcel podcast episode 2341.
Wolfram Alpha Data Types in Excel - The First Look.
Hey, welcome back to the MrExcel Netcast. I am Bill Jelen.
Some exciting new things that debuted today.
We've had stocks and geography data types for over a year.
But a whole bunch of new items: Chemistry, Elements, Space, Satellites. It is crazy how much new stuff they have. Now, first the bad news.
It is a hassle to get this. It has actually been out for a couple of weeks.
I could not get it on any machine. You need Microsoft 365 Home (or Student).
Which I had.
BUT: never having signed into that computer with ProPlus ever.
That was the one that killed me.
I finally found a link, which I am going to put in the YouTube description below, for the exorcism instructions.
You need Office Insiders, beta channel and to be in the lucky 50%.
Now, beyond the new data types, the exciting new things we have: A data type can now return an array of values, which is awesome.
Two new functions that we are hardly ever going to use:ARRAYTOTEXT() and VALUETOTEXT() A data type can now return another data type or be used in data validation. Data types can return images.
Although the image fits in the size of a cell so you are going to have to resize the cell to make the image visible. All right. Let's just give it a try.
I have a list of some of my favorite movies here. I am going to go up to Data, Data Types.
They have a category for movie. So I choose movie.
and it's going to go out to the Internet and try and figure out what i'm talking about.
Movies - unfortunately - there is a lot of ambiguity in these.
So we are going to get some that we're going to have to go through and select.
They say they don't know the Blues Brothers.
So over here, The Blues Brothers or the sequel in 2000. Of course, the original that is the one.
Ocean's 11.
The one from 1960 (which is on TCM on Aug 11 2020), or this one - the new one - with George Clooney. Footloose? The original.
Dirty Dancing? The original. And Caddyshack or Caddyshack II.
Clerks. Titanic. Men in Black - the original.
All right. There we go. Now let's add Movie Title up here as a heading.
These always work better as tables. So Ctrl+T to format as tables.
That allows us to add new fields.
We have all kinds of things like what's the genre?
We can get what's the rating. We can get the run time.
You know, these are the kinds of things that you would expect that you could get from the "old" Geography data types or things like that.
But: they also offer things that have multiple items.
For example, Cast and Roles.
What happens here is that I get Tom Cruise as Joel Goodson and Rebecca de Mornay as Lana.
And so on. Take a look at this.
They automatically wrap this in ARRAYTOTEXT - a brand new function that takes an array of values puts them into a single cell with commas in between. Ignoring errors I believe.
Then VALUETOTEXT would do the same thing with values. These two functions are actually in your Office Insiders right now.
With either Home or Enterprise. When it arrived in Enterprise, we tried to figure out a use case.
Well, it is for this use case. All right, here let's do one more.
We are going to add the image! look at all those hash busy errors as it downloads those images.
The hassle of course is they are too small.
All right, so rather than do data types like you've been doing in the past where you put all the geographies and just copy it down, I think the better way to do this is to build a dashboard for a single item.
So here's the same list of movies. Over here I did Data Validation (Alt+D+L).
Set a list, based on that source. Click ok.
And now i can choose from the list. All right so we'll come in here and choose Men in Black.
all right. Instantly it goes out.
This field is F4.Image and H6.Image. So F4 is the item that I chose. =F4.Image is the movie poster.
Down here -- this is awesome -- this is =F4[ Cast and roles ] without being wrapped in the ARRAYTEXT function and it spills like any other dynamic array would spill, which is awesome.
Okay. Then I have Will Smith. He is the top item.
So i come over here and say =F6.Cast which converts it to a person.
Once I've converted Will to a person then I can do =H6.[ Full Name ].
So this cascading idea where we're taking the movie, Getting the cast, Getting the person, Getting information about the person.
Then, notable film appearances gives me a list of all the film appearances.
But not in any particular order that I can tell.
So here I have =H9# of the box office total receipts. That gets those receipts for the entire array.
If there were any errors then I put comma zero.
Then over here a SORTBY function.
One of the new dynamic array functions that sorts the array in H9# by the box office in I9#.
In descending order.
So then I get Will Smith's movie appearances, high to low, based on U.S. box office.
And this all works perfectly.
So right up here, if I choose a different movie: Ocean's 11. I get the movie poster.
I get all of these #BUSY! errors. The shirt is for sale down on the Merchandise shelf.
Feel free ... we are all #BUSY! right? I get it Excel...
You'll eventually get to it. George Clooney.
And then there are all of George Clooney's movies, sorted by box office over there.
This is pretty pretty cool and actually I could spend three months going through all of the different items that they have: Movies, Artists, Science, Rockets.
Why didn't I do rockets? Good question! So take a look at this list.
If there is anything here you want to see, write me a note down in the YouTube comments below and I'll try and create create something unique for you.
So these brand new Wolfram Alpha data types.
The hassle is you have to be on a Home or Student subscription, never signed in using Pro Plus.
I had plenty of computers that have the home or student, but at some point along the way i signed it in to Pro Plus.
I finally found that link about how to get rid of that Pro Plus account. You can't do it in the Excel interface.
It's tough to do.
I want to thank you for watching.
Please, down below, click Like, 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.
Click the "i" in the top right hand corner for more information about that book. Well these are pretty exciting.
The new Wolfram Alpha data types.
Equally exciting is the fact that data types can now do more things.
Remember there is still a goal that hopefully someday we'll be able to put *our* data into the data types and just think that now we can return arrays and images and all kinds of great things like that.
I want to thank you for stopping by we'll see you next time for another netcast from MrExcel.
[ ♪♪ ] Tips and tricks, get your spreadsheet fix, at MrExcel.com.
[ ♪♪ ]
