Microsoft Excel Tutorial: Embed any Excel Logic in a Web App Using Sheetcast.com.
Try this web app now to search my videos: MrExcel's Video Search
Welcome to episode 2635 of the MrExcel podcast! It's been 25 years since I started sharing my Excel tips and tricks with all of you, and I couldn't be more grateful for your support. Today, I have something truly amazing to show you - how to publish any Excel logic as a web app using Sheetcast while protecting your intellectual property. Plus, I'll also be sharing how you can search through 25 years of MrExcel content using just 18 Excel functions. Let's dive in!
Have you ever wondered how to create a web app from your Excel workbook without giving away your intellectual property? Well, I recently met with the team at Sheetcast and they showed me how it can be done in just minutes. All you need is your Excel workbook and the ability to build logic in Excel. Sheetcast takes care of the rest, giving you a fully functional web app. And the best part? You can protect your IP while still having all the calculations work seamlessly.
To put Sheetcast to the test, I challenged them to create a web app using some of the most complex Excel functions, such as IMAGE, HSTACK, and FILTER. And to my surprise, they were able to deliver a fully functional web app in just a few days. This is truly mind-blowing and a game-changer for anyone looking to share their Excel work without compromising their intellectual property.
But that's not all. Sheetcast also allows you to search through 25 years of MrExcel content using just 18 Excel functions. No more scrolling through endless YouTube videos or trying to remember which video had the information you need. With Sheetcast, you can easily search for specific phrases and find the exact video you're looking for. It's like having your own personal Excel search engine!
I'm truly amazed by the capabilities of Sheetcast and I'm excited to share it with all of you. In honor of the 25th anniversary of MrExcel, Sheetcast has created a special URL where you can try out the web app for yourself. Just click the link in the description below and see for yourself how easy it is to create a web app from your Excel workbook. And don't forget to use the coupon code "25years" for 25% off all Excel eBooks at the MrExcel store.
I also want to take this opportunity to thank the amazing community at MrExcel. Your support and participation in the MrExcel message board has been invaluable over the years. Together, we have created a community of smart and talented individuals who can solve any Excel problem. Thank you for being a part of this journey and here's to many more years of Excel success! Don't forget to like, subscribe, and ring the bell for more Excel tips and tricks. And as always, feel free to leave any questions or comments down below. Until next time, this is Bill Jelen from MrExcel, signing off.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Table of Contents
(0:00) Welcome to 25th anniversary of MrExcel
(0:45) Ctrl+; for Current Date
(1:05) YEARFRAC function in Excel
(1:25) First articles at MrExcel.com in 1998-1999
(1:54) Publish Excel logic to web app using Sheetcast
(2:17) Create a challenge for Sheetcast with 18 functions
(2:44) Workbook to search MrExcel YouTube content
(3:17) Testing the Video Search in Excel
(3:53) Excluding stop words from search
(4:31) How many times is pivot mentioned in a video
(4:58) Hash as array operator
(5:08) Seeing results of subformula in tooltip
(6:00) What match score is 5th largest
(6:14) FILTER and HSTACK the top 5
(6:35) IMAGE function
(7:34) Sheetcast Version of my Excel Workbook
(8:24) You can try it
(9:30) 25% Off E-books at MrExcel
(10:00) Thanks to MrExcel Board
(10:27) Clicking Like really helps the algorithm
Try this web app now to search my videos: MrExcel's Video Search
Welcome to episode 2635 of the MrExcel podcast! It's been 25 years since I started sharing my Excel tips and tricks with all of you, and I couldn't be more grateful for your support. Today, I have something truly amazing to show you - how to publish any Excel logic as a web app using Sheetcast while protecting your intellectual property. Plus, I'll also be sharing how you can search through 25 years of MrExcel content using just 18 Excel functions. Let's dive in!
Have you ever wondered how to create a web app from your Excel workbook without giving away your intellectual property? Well, I recently met with the team at Sheetcast and they showed me how it can be done in just minutes. All you need is your Excel workbook and the ability to build logic in Excel. Sheetcast takes care of the rest, giving you a fully functional web app. And the best part? You can protect your IP while still having all the calculations work seamlessly.
To put Sheetcast to the test, I challenged them to create a web app using some of the most complex Excel functions, such as IMAGE, HSTACK, and FILTER. And to my surprise, they were able to deliver a fully functional web app in just a few days. This is truly mind-blowing and a game-changer for anyone looking to share their Excel work without compromising their intellectual property.
But that's not all. Sheetcast also allows you to search through 25 years of MrExcel content using just 18 Excel functions. No more scrolling through endless YouTube videos or trying to remember which video had the information you need. With Sheetcast, you can easily search for specific phrases and find the exact video you're looking for. It's like having your own personal Excel search engine!
I'm truly amazed by the capabilities of Sheetcast and I'm excited to share it with all of you. In honor of the 25th anniversary of MrExcel, Sheetcast has created a special URL where you can try out the web app for yourself. Just click the link in the description below and see for yourself how easy it is to create a web app from your Excel workbook. And don't forget to use the coupon code "25years" for 25% off all Excel eBooks at the MrExcel store.
I also want to take this opportunity to thank the amazing community at MrExcel. Your support and participation in the MrExcel message board has been invaluable over the years. Together, we have created a community of smart and talented individuals who can solve any Excel problem. Thank you for being a part of this journey and here's to many more years of Excel success! Don't forget to like, subscribe, and ring the bell for more Excel tips and tricks. And as always, feel free to leave any questions or comments down below. Until next time, this is Bill Jelen from MrExcel, signing off.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Table of Contents
(0:00) Welcome to 25th anniversary of MrExcel
(0:45) Ctrl+; for Current Date
(1:05) YEARFRAC function in Excel
(1:25) First articles at MrExcel.com in 1998-1999
(1:54) Publish Excel logic to web app using Sheetcast
(2:17) Create a challenge for Sheetcast with 18 functions
(2:44) Workbook to search MrExcel YouTube content
(3:17) Testing the Video Search in Excel
(3:53) Excluding stop words from search
(4:31) How many times is pivot mentioned in a video
(4:58) Hash as array operator
(5:08) Seeing results of subformula in tooltip
(6:00) What match score is 5th largest
(6:14) FILTER and HSTACK the top 5
(6:35) IMAGE function
(7:34) Sheetcast Version of my Excel Workbook
(8:24) You can try it
(9:30) 25% Off E-books at MrExcel
(10:00) Thanks to MrExcel Board
(10:27) Clicking Like really helps the algorithm
Transcript of the video:
Just down below the video, if you click, like that'll make sure that YouTube shows this video to more people.
They say if you do something you love, you never have to work a day in your life.
That's certainly the case here.
It's been 25 years today that I've been sharing my Excel tips and videos and seminars with you.
I appreciate you being there.
I'm always excited when I have something new to show and today I think I have something amazing.
Let's take a look.
Learn Excel from MrExcel podcast, episode 2635.
Publish any Excel logic to a web app using sheet cast, protecting your intellectual property, or search 25 years of MrExcel content using 18 Excel functions.
Okay, what's the Excel shortcut for today's date?
It's control semi, and if I want to calculate the number of years between two dates, the year frack from the earlier date to the later date today, I can't believe this, it's been 25 years since I published my very first article at MrExcel with a column of data with daily dates.
Use the day function to insert a new column of monthly dates in order to further analyze the data by month and a pivot table or sub totals.
Look at that.
I didn't even have the EOMONTH function back then because it was still part of the analysis toolpak, which most people weren't allowed to install.
I went back to the Wayback machine and that first run there, this is the first snapshot of the Wayback machine.
It looks like almost weekly Excel articles being published.
Now, the hassle with 25 years of content means that I'm 25 years older than when I started, which means that I might remember that I'd done something, but where did I do it?
But what I'm really excited about today, just under a week ago, I met with the guys at Sheetcast.
They said they had a way to create a web app in minutes using any Excel workbook.
So you know how to build logic in Excel, you upload it to sheet cast and all of a sudden you have a web app.
It is a common question.
You don't want to give your Excel file to someone.
There's a lot of intellectual property in there, but you want the calculations in Excel to work.
So as I was talking to the sheet cast folks, I said, well, geez, what would be a cool way that I could build a web app that might be useful, that might be a stretch for the sheet cast app?
I said to 'em, do you support all the functions?
They said almost all the functions.
Oh, well that's like a challenge.
Let's see if we can break it.
I mean, let's face it, if you're going to put a product to the test, some good functions to throw at, it would be the image function H stack filter.
So just this past Friday, I built this little workbook with three Excel sheets and not many Excel formulas.
This is a great way for me or anyone to go look for the YouTube videos that I've created.
For example, if I search for the phrase Publish web, I see this episode 1432 from September, 2011, another episode from 2016.
It is a great way to find things here.
Let's try another one.
Let's do pivot table calculated field, and it comes back with several different videos about calculated fields or if I would search for subtotals horizontal or replicate pivot table or Python.
Alright, so let's take a look at the logic behind this.
We'll go with pivot table, calculated field, And by not the, alright, all right.
As people type in a search phrase, the first thing you have to do is think about the tiny words that you don't want to search for because they're going to be in every single video like, and those are called stop words.
There's a great database out on the web.
I pulled that into here and name the range as stoppers.
That was step number one.
So let's come back to the presentation layer here where I filter my database for everything where the score in column I is larger than the fifth largest score.
That should get me five results.
Sometimes there'll be a tie and I'll get 5, 6, 7.
So in the first two columns, I'm getting the title and the date that it was published, and then the image function, same filter here to get the images and then finally the watch link.
I'd really hope that I could make these into hyperlinks, but unfortunately it's not working.
Take those three arrays and horizontally stack them into a single result.
Now.
Hey, look, I thought that was a pretty cool little web app.
It's actually useful for me.
I can go back and find videos that I know that I've created in the past.
It potentially might be interesting to you in case you were ever searching for a particular video that you remember from the past.
Yes, YouTube has a search, but they're not searching the transcript.
And for the longest time when I started the YouTube channel, I really thought the video was the important thing.
I would create a great video, upload it, and then just put a two sentence description figuring if you wanted to know what it was about, you could watch the video.
Ah, that's horrible for SEO and over the past few months, we've spent a lot of time here to put out full descriptions for all 2,400 videos, but it's the subtitle file that really tells you everything that's mentioned in the video.
So this is Tuesday morning.
It was just a few days ago on Friday morning when I sent that Excel workbook over to Sheet cast and said, Hey, could we do a demo where you show me how sheet cast could put this workbook on the web?
I honestly figured I had used some functions that probably were going to throw them for a loop, but here we are.
It's Tuesday morning and yesterday end of day on Monday, they said, yeah, here, here's a great explanation.
Now they offered to come in here and teach me how to build this myself, and I said, Hey, it's pretty late.
Tomorrow's the 25th anniversary.
Let's publish something right now and then we can talk about building later.
They promised me that it would take five or 10 minutes to take my Excel workbook and convert it to this amazing web app, and I've asked them to create AURL where you can go try it as well.
I'll put that URL down in the YouTube description right at the top.
Alright, so this is pretty amazing.
This is a result.
They apologize that it didn't look better.
They said they try to use some of the same colors that I used and they could teach me how to customize this more, but the real question is, can we search and get the results?
Let's search for stock quotes.
There we are, the stock history, stock history, introducing free stock quotes in Excel stock data types and how to use the new geography data types, which must have mentioned stock quotes as well.
This is really pretty mind blowing.
First that I gave them a fairly complicated worksheet with 18 different functions on Friday and on Monday I now have a web app that's actually working that replicates all the logic that I knew how to build in Excel.
The next time that you have some amazing Excel workbook that you want to share, but you don't want your lookup tables and other intellectual property to get out there, check out this amazing new tool@sheetcast.com, a quarter of a century.
It's sort of amazing.
It seems like a long time.
Thanks to everyone at MrExcel.
Thanks to everyone who's left.
A comment on the video, watched the video, participated in Mr.
Excel message board, the MrExcel store this week for Thanksgiving through Cyber Monday, 25% off all Excel eBooks using the coupon 25 years.
I'll put that link down in the YouTube description and a shout out to everyone at the MrExcel board.
What an amazing community of really smart people about Excel with 5.7 million messages in over a million threads.
That first six months, it was me writing an Excel article every week.
When I launched the MrExcel message board, it opened the MrExcel community up to an amazing community of people who can solve Excel problems.
Anytime that I can't figure anything out, this is my first place to come.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from Face Your Excel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
[ Music ] OK, I'm going to teach you how to search in reverse.
Repeat after me.
It's easy with five little words.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
Your turn!
CONCAT, LEFT, RIGHT SEQUENCE LEN.
That's how you search in reverse.
That's how you turn it around.
Its just five little words.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
I think you got it.
They say if you do something you love, you never have to work a day in your life.
That's certainly the case here.
It's been 25 years today that I've been sharing my Excel tips and videos and seminars with you.
I appreciate you being there.
I'm always excited when I have something new to show and today I think I have something amazing.
Let's take a look.
Learn Excel from MrExcel podcast, episode 2635.
Publish any Excel logic to a web app using sheet cast, protecting your intellectual property, or search 25 years of MrExcel content using 18 Excel functions.
Okay, what's the Excel shortcut for today's date?
It's control semi, and if I want to calculate the number of years between two dates, the year frack from the earlier date to the later date today, I can't believe this, it's been 25 years since I published my very first article at MrExcel with a column of data with daily dates.
Use the day function to insert a new column of monthly dates in order to further analyze the data by month and a pivot table or sub totals.
Look at that.
I didn't even have the EOMONTH function back then because it was still part of the analysis toolpak, which most people weren't allowed to install.
I went back to the Wayback machine and that first run there, this is the first snapshot of the Wayback machine.
It looks like almost weekly Excel articles being published.
Now, the hassle with 25 years of content means that I'm 25 years older than when I started, which means that I might remember that I'd done something, but where did I do it?
But what I'm really excited about today, just under a week ago, I met with the guys at Sheetcast.
They said they had a way to create a web app in minutes using any Excel workbook.
So you know how to build logic in Excel, you upload it to sheet cast and all of a sudden you have a web app.
It is a common question.
You don't want to give your Excel file to someone.
There's a lot of intellectual property in there, but you want the calculations in Excel to work.
So as I was talking to the sheet cast folks, I said, well, geez, what would be a cool way that I could build a web app that might be useful, that might be a stretch for the sheet cast app?
I said to 'em, do you support all the functions?
They said almost all the functions.
Oh, well that's like a challenge.
Let's see if we can break it.
I mean, let's face it, if you're going to put a product to the test, some good functions to throw at, it would be the image function H stack filter.
So just this past Friday, I built this little workbook with three Excel sheets and not many Excel formulas.
This is a great way for me or anyone to go look for the YouTube videos that I've created.
For example, if I search for the phrase Publish web, I see this episode 1432 from September, 2011, another episode from 2016.
It is a great way to find things here.
Let's try another one.
Let's do pivot table calculated field, and it comes back with several different videos about calculated fields or if I would search for subtotals horizontal or replicate pivot table or Python.
Alright, so let's take a look at the logic behind this.
We'll go with pivot table, calculated field, And by not the, alright, all right.
As people type in a search phrase, the first thing you have to do is think about the tiny words that you don't want to search for because they're going to be in every single video like, and those are called stop words.
There's a great database out on the web.
I pulled that into here and name the range as stoppers.
That was step number one.
So let's come back to the presentation layer here where I filter my database for everything where the score in column I is larger than the fifth largest score.
That should get me five results.
Sometimes there'll be a tie and I'll get 5, 6, 7.
So in the first two columns, I'm getting the title and the date that it was published, and then the image function, same filter here to get the images and then finally the watch link.
I'd really hope that I could make these into hyperlinks, but unfortunately it's not working.
Take those three arrays and horizontally stack them into a single result.
Now.
Hey, look, I thought that was a pretty cool little web app.
It's actually useful for me.
I can go back and find videos that I know that I've created in the past.
It potentially might be interesting to you in case you were ever searching for a particular video that you remember from the past.
Yes, YouTube has a search, but they're not searching the transcript.
And for the longest time when I started the YouTube channel, I really thought the video was the important thing.
I would create a great video, upload it, and then just put a two sentence description figuring if you wanted to know what it was about, you could watch the video.
Ah, that's horrible for SEO and over the past few months, we've spent a lot of time here to put out full descriptions for all 2,400 videos, but it's the subtitle file that really tells you everything that's mentioned in the video.
So this is Tuesday morning.
It was just a few days ago on Friday morning when I sent that Excel workbook over to Sheet cast and said, Hey, could we do a demo where you show me how sheet cast could put this workbook on the web?
I honestly figured I had used some functions that probably were going to throw them for a loop, but here we are.
It's Tuesday morning and yesterday end of day on Monday, they said, yeah, here, here's a great explanation.
Now they offered to come in here and teach me how to build this myself, and I said, Hey, it's pretty late.
Tomorrow's the 25th anniversary.
Let's publish something right now and then we can talk about building later.
They promised me that it would take five or 10 minutes to take my Excel workbook and convert it to this amazing web app, and I've asked them to create AURL where you can go try it as well.
I'll put that URL down in the YouTube description right at the top.
Alright, so this is pretty amazing.
This is a result.
They apologize that it didn't look better.
They said they try to use some of the same colors that I used and they could teach me how to customize this more, but the real question is, can we search and get the results?
Let's search for stock quotes.
There we are, the stock history, stock history, introducing free stock quotes in Excel stock data types and how to use the new geography data types, which must have mentioned stock quotes as well.
This is really pretty mind blowing.
First that I gave them a fairly complicated worksheet with 18 different functions on Friday and on Monday I now have a web app that's actually working that replicates all the logic that I knew how to build in Excel.
The next time that you have some amazing Excel workbook that you want to share, but you don't want your lookup tables and other intellectual property to get out there, check out this amazing new tool@sheetcast.com, a quarter of a century.
It's sort of amazing.
It seems like a long time.
Thanks to everyone at MrExcel.
Thanks to everyone who's left.
A comment on the video, watched the video, participated in Mr.
Excel message board, the MrExcel store this week for Thanksgiving through Cyber Monday, 25% off all Excel eBooks using the coupon 25 years.
I'll put that link down in the YouTube description and a shout out to everyone at the MrExcel board.
What an amazing community of really smart people about Excel with 5.7 million messages in over a million threads.
That first six months, it was me writing an Excel article every week.
When I launched the MrExcel message board, it opened the MrExcel community up to an amazing community of people who can solve Excel problems.
Anytime that I can't figure anything out, this is my first place to come.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from Face Your Excel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
[ Music ] OK, I'm going to teach you how to search in reverse.
Repeat after me.
It's easy with five little words.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
Your turn!
CONCAT, LEFT, RIGHT SEQUENCE LEN.
That's how you search in reverse.
That's how you turn it around.
Its just five little words.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
CONCAT, LEFT, RIGHT SEQUENCE LEN.
I think you got it.
