Editor's Note: The following MVP Monday post is by Excel MVP Zack Barresse
Embedding Excel Services on your Website
Have you ever read a Microsoft Excel help topic and wished you could work with its examples right there in that topic? Have you mulled over pictures or descriptions of solutions, but had a hard time understanding them or implementing them? Did you know if you have a Windows Live ID that you also get a free version of Microsoft Excel you can use in a web browser, as well as one for Word, PowerPoint and OneNote? Most people do not. If you've been using Hotmail for a while now, you may have heard about Office Live Workspaces or SkyDrive (Live Workspaces was basically absorbed into SkyDrive). With SkyDrive, everyone has a 7GB online “cloud” storage limit for free. In addition to the free storage space, you have access to these online versions of Office programs, with which you can not only read these files but edit and even create them. In this article I'm going to focus on the Excel Web App, and its Share feature.
Believe it or not this has been out for a few years now. I wrote an article on this a couple of years ago but it got lost amongst the many junk, err, files I have. So I decided to start with a clean slate and just write a new one. In fact, this was written in the Word Web App!
When you're at your Windows Live Home screen (mail.live.com) you will see a link to SkyDrive at the top, as shown circled in red in this picture:
You can click on it or hover your mouse for more options. While hovering your mouse over it you'll see you have the ability to navigate to different folders, as well as create new files, as seen in the picture below. For the purposes of this article we're going to click on the New Excel workbook link.
Once you click on New (filetype) you will be prompted for a file name. Enter whatever you like. For the sample file I named it, ExcelServices TEST. All files are created as the XLSX file type.
Once you get the name entered you should now be in familiar territory - Excel! It looks very similar to the actual full application. You can now start creating your workbook. For the file I am using a simple loan payment. The file looks like the following:
As you can see, it is Excel. You can enter formulas as you normally would, and you have several formatting options.
Microsoft has worked very hard to continue the same user experience for their users on the web. There are some benefits and drawbacks to this method.
Some common functionality you currently have (and more planned to be added):
You can use these features if they're already in your source workbook on SkyDrive:
So while there is a lot you can do in the embedded worksheet, there are some things you can't do as well. Luckily, the majority of things done in Excel are available on the web. This is perfect for the high school or college student - those on a budget and might not be able to afford to purchase a full Office suite or application. Save your money, no need for Google docs, no need for OpenOffice - you have Office for free!
The Excel web version makes use of the ribbon just as with the full version, so it should look familiar. Note that Excel Web App looks very much like Office 2010 style. You will notice a few things right from the start. The file name you entered will show prominently at the top of your browser/file. You will also notice a Share menu button at the right end of the Home tab.
I'll get to that in a minute. First let me explain how I set up my data, a very quick example on how to calculate a payment amount based on some variables.
For this example I'm going to use the PMT() function. It's syntax is as follows:
=PMT( rate, nper, pv, [fv], [type] )
Calculates the payment for a loan based on constant payments and a constant interest rate
Rate: Required. The interest rate for the loan.
Nper: Required. The total number of payments for the loan.
Pv: Required. The present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv: Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type: Optional. The number 0 (zero) or 1 and indicates when payments are due.
As a quick note on this function, if you are going to be looking at monthly payments, which we are in this example, you'll need to use months as the nper as well as divide the rate by 12. This is how I have my sample file:
A1 houses interest rate of 5%
A2 houses number of years (life of loan), in this case 10
A3 houses number of months (calculated), in this case a formula of =A2*12
A4 houses total amount loaned, in this case $25000
A5 houses the PMT() function
Some quick notes about this information: A1 must be formatted as a percentage. We are going to look at monthly payments, so instead of multiplying the years in the function itself, I put this in another cell. We will still need to divide the rate by 12, but I left it as a separate cell so it could be changed in our live preview.
The function looks as follows (in A5):
You'll notice the minus sign right before the A4 reference. This is because as a payment it is considered a debit, thus a negative number. For viewing purposes I want to show it as a positive number, so I reverse the sign on it. If viewing in a ledger you will most likely see it as a negative value.
In my sample file I have highlighted the cells by color according to calculated cells versus input cells. The blue cells are input cells, whereas the green cells are calculated cells.
Now with all this in mind, I want to publish this to my website, embed it on a page, then let the users change the values to see what the formula does, and I want it all to do so live and with interactivity. For you to embed this it will need to be in a publicly shared folder. By default it will only give you rights, so you need to change it to public, which you can do when viewing the folder/location of your Excel file.
Let's jump right into it. If you click this Share button, you will see a menu with two options: Embed or Share.
For the purposes of this article we are going to click Embed. This will bring up a dialog box where you can set your options. This is what you will see immediately:
If you click Cancel then the dialog box will go away and nothing will happen (Duh.) Click Generate to bring up the dialog box to set your embedding options.
As you can see there are six major areas to look at (large green text).
What to show: By default it will be the Entire Workbook, meaning if you have data on multiple sheets, the users will be able to navigate to them. You can link formulas across worksheets as well.
Appearance: General viewing options, check and uncheck to show/hide what parts of the worksheet you want to show/hide.
Interaction: This is where it starts to get interesting! Want users to sort or filter your data? Want people to change cell values and formulas? Set it here! You can also change the starting cell.
Dimensions: I generally don't change this, but if you have a larger spreadsheet you want to show, instead of making the users click and drag the scroll bar, set it here. Changes can be seen in real-time. The largest you can go is 700x900.
Preview: See what it looks like. If you change the dimensions you can click the View actual size link.
It literally is that simple. Copy the embed code, paste on your website. Voila. Excel file embedded on your website. Simple!!
For my example file I went with only showing range A1:B5, to let users be interactive with it, kept it default size, and start with A5 selected. This is the iframe embed code:
<iframe width="402" height="346" frameborder="0" scrolling="no" src="https://r.office.microsoft.com/r/rlidExcelEmbed?su=230259801569442040&Fi=SD3320C11CE9938F8!416&ak=t%3d0%26s%3d0%26v%3d!AAevM213int_PBI&kip=1&wdAllowInteractivity=False&AllowTyping=True&ActiveCell='Sheet1'!A5&wdHideGridlines=True&wdHideHeaders=True&wdDownloadButton=True"></iframe>
Feel free to use this code and then try out your own.
Here are some great examples of what it can look like on your website:
'Five Very Useful Functions For Working With Text' by Ken Puls: http://www.excelguru.ca/content.php?138
More blog posts by Ken Puls regarding Excel Services: http://www.excelguru.ca/blog/category/excel-services/
'Excel On The Web - Another Spreadsheet in the Cloud' by Gregory Ropp: http://excelsemipro.com/2010/06/excel-on-the-web-another-spreadsheet-in-the-cloud/
'Introduction to Excel Web Apps' by Microsoft: http://office.microsoft.com/en-us/web-apps-help/introduction-to-excel-web-app-HA010378338.aspx
'Getting started with Office Web Apps' by Microsoft: http://office.microsoft.com/en-us/web-apps-help/getting-started-with-office-web-apps-HA101785172.aspx
'Introduction to Excel Services and Excel Web Access' by Microsoft: http://office.microsoft.com/en-us/sharepoint-server-help/introduction-to-excel-services-and-excel-web-access-HA010105476.aspx
'Share your spreadsheets on the Web ' by John Walkenbach: http://j-walk.com/ss/excel/usertips/tip016.htm (NOT Excel Services, but another option, mostly for older versions of Excel - 2000 and later)
Office Web Apps Blog by Microsoft: http://blogs.office.com/b/officewebapps/
'Differences between using a workbook in the browser and in Excel' by Microsoft: http://office.microsoft.com/en-us/web-apps-help/differences-between-using-a-workbook-in-the-browser-and-in-excel-HA010369179.aspx
About MVP Mondays
Zack Barresse is a firefighter and paramedic in Oregon. Currently he holds the rank of Captain and serves as the Training Officer of his department. He taught himself Excel after seeking help in the online help forums and started offering help himself. He can be found hanging around websites such as mrexcel.com, vbaexpress.com, techguy.com, smittypro.com/blog, excelguru.ca, experts-exchange.com, among others. Zack was awarded the prestigious Microsoft MVP award for Excel in October of 2005 and has received it each subsequent year. He is married to his high school sweetheart and has 5 children. In his spare time Zack likes hunting, fishing, camping, playing Xbox and swimming.
The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager for Dynamics, Excel, Office 365, Platforms and SharePoint in the United States. She has been working with MVPs since her early days as Microsoft Exchange Support Engineer when MVPs would answer all the questions in the old newsgroups before she could get to them.
Nice post Zack!
Wow, Zack nailed that one. GREAT, I repeat, GREAT article.
How this relates to putting out a fire or saving someone’s life, I do not know, but Zack not only KNOWS Excel, Zack can write, teach, and inform. GREAT, GREAT job Zack.
I will share this on my site with my clients.
Thanks very much! Tried to keep it slim and to the point. That's a great article, Jan!
It may not save a life (this time) but it saves time and work! :)
Too bad such an opportunity isn't being offered to Access
Is there a way to do this with Office 365 on a public page?
Jan Karel, I went back and looked at your demo, ann I agree with Zack, that is well worth the visit to the site to check it out.
Jan Karel, I really like your site. Content rich is an understatement. I great Excel resource.
O365 includes this, and some subscriptions include the full desktop version of the application suite. It depends on what you're looking for. Here is some information you might find useful about Office 365:
One of the best things about O365 is you get Office Web Apps which include SharePoint. This allows you to run it from a server in a SharePoint environment, which is very robust and powerful. Of course this is just one reason, but a big one.
There is a free trial of O365 as well: www.microsoft.com/.../free-office365-trial.aspx
Nice post, Zack!
There is a straight-to-the-point portuguese / Brazil version here: www.ambienteoffice.com.br/.../web_app_mashups
I reference Jan's page there.
Drop down lists do not seem to work in the embedded excel sheet. is there a bit of code that you can add to make it work?
Drop-down lists now work.
I note that check-boxes do not.