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!
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.
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.
- · 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
- · 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 setup 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.
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 700×900.
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.
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.