The Microsoft MVP Award Program Blog

Independent Experts. Real World Answers.

Embedded Excel Services on your Website

Embedded Excel Services on your Website

  • Comments 10

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.

What is the point of sharing your Excel file(s) from SkyDrive?  Well, you can "embed" a file from your SkyDrive folder on a web page and even make it interactive for a user.  That's right; people will be able to work with your data online, meaning they can sort, filter, and even change the data on the web page. But rest assured that your data on SkyDrive can't be changed by anyone.  How can this be helpful  you might ask?  It's simple; for showing examples, creating live reports or sharing information.  For example, you want to show a loan calculator and what the payments would be, allowing your users to enter their own loan amount and terms.  To do this on a website, you'd probably have to use JavaScript.  Now you can do it with Excel!  Want to see what a loan payment would be with a lower interest rate, or a larger down payment?  No problem; let your users test out Excel's worksheet functions (like PMT, RATE, or PV) with your data as the variables, and they will see instant results!

In this article I will take you through how to embed your own Excel worksheet on a web page and how to make it interactive.  Before we get started, you need to know that the default script generated for you by Excel Web App is in HTML format, specifically an “iframe” control.  There's also a JavaScript version that gives you even more flexibility. Your site would need to allow this type of scripting in order to be viewed properly.  If you're unsure, contact your host administrator or technical support.

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!

Create an Excel file in SkyDrive

 

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.

Any Benefits?

 

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.

Benefits:

  • ·         Always available (with internet connection)
  • ·         Work is always saved
  • ·         Work is always in the same place
  • ·         Internet site is run on securely (https)
  • ·         No need for you to install additional software on your computer
  • ·         No need to install updates, patches or service packs
  • ·         Open straight into Excel if you want more functionality
  • ·         All updates are applied by Microsoft
  • ·         Because there's no need for large software releases, updates are immediately applied (at least monthly)
  • ·         Collaboration made easy
  • ·         Embed to website

Drawbacks:

  • ·         Internet connection needed
  • ·         Somewhat scaled back functionality
  • ·         No VBA (Visual Basic for Applications, i.e. macros)
  • ·         No shapes
  • ·         No data validation

Some common functionality you currently have (and more planned to be added):

  • ·         Worksheet functions
  • ·         Worksheet formatting
  • ·         Charts
  • ·         Tables
  • ·         Inserting/Deleting
  • ·         Hyperlinks

 

You can use these features if they're already in your source workbook on SkyDrive:

 

  • ·         Sorting
  • ·         Filtering
  • ·         Sparklines
  • ·         Slicers

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!

Look And Feel

 

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):

=PMT(A1/12,A3,-A4)

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.

Embed To Website or Blog

 

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.

Embed code:   This is where you get the code.  It’s in HTML by default, but you can get the javascript version if you'd like.   Copy the code in the textbox.  This is what you'll paste on your website or blog, and that's how easy it is!

Preview:   See what it looks like.  If you change the dimensions you can click the View actual size link.

As Simple As Copy & Paste

 

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.

Other Resources

 

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

Author's Bio

 

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.

melissa
Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post
  • Nice post Zack!

    The lack of VBA support is slightly compensated by the support for JavaScript. I show a demo here:

    www.jkp-ads.com/articles/embeddedexcel00.asp

  • 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.

    Best Regards,

    Christopher

  • 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!  :)

    Zack

  • 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?

    Jack

  • 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.

    Christopher

  • 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:

    FAQ: www.microsoft.com/.../faqs.aspx

    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

    Zack

  • 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.

Page 1 of 1 (10 items)