Blogroll

Here are a few interesting posts related to embedding Excel on web pages:

  • Crabby Office Lady shows an example of a very nice dynamic mortgage calculator.
  • Groovy Post has a very detailed step by step on uploading Excel files to SkyDrive and then sharing and embedding them in html
  • Spreadsheet Templates adds their own detailed how to guide
  • Last but not least, a very cool demo with interactivity from Microsoft.com

Time-dependent information in an Excel web page

In one of the WordPress forums there was a question about displaying the list of pharmacies that are on duty today, given a list of all the pharmacies in an area and when each of them is on duty. I’ve decided to give it a try below. Note how the list will change if you come back to this site some other day.

Here is how the workbook works. On the second sheet, there is a table with a list of pharmacies, their address, and when they are on duty. I have added a calculated column, that determines if they are on duty today (by using the =TODAY() function). Then, I have other columns that calculate, for rows that are “on duty”, their index on the list. On the first sheet, I show (by using =VLOOKUP()) only those rows that are on duty today.

Related posts:

Inserting a range from a sheet

I am now searching the WordPress.org forums for people needing help in embedding Excel in their posts. One of them was about allowing someone to search their name in a long list of names, and show the value of the other column in the table.

Below I built a simple example for this, with the top 100 baby names. Enter a name, and it will tell you what (if any) was its position in the top 100.

The workbook itself is fairly simple – just a table of boy and girl names and their “ranking”, and a vlookup formula into that table.

In terms of embedding, I am introducing 2 new concepts: showing only a range (as opposed to the whole workbook), and selecting the active cell.

My workbook has 2 sheets. I only wanted to show the first sheet, and only a few cells in it. This simplifies what it shown to the user. To do this, I first created a named range called ‘Form’ for the cells I wanted to show. Then, I add &Item=Form to the HTML snippet. Note that users can still get to the rest of the workbook, for example by pressing the “view full size workbook” button in the bottom Excel Web App bar.

I also wanted the selection to be on cell B2, so that users can enter the name w/o selecting another cell. I have added  &ActiveCell=B1 to the HTML snipped, and… voila!

Real world: embedded pay calculator and comparison

A couple of days ago I pointed Ruth from “My Paper Work” to Excel Web App, as a way to create an Excel calculator embedded in a blog post. She created a post, with an Embedded workbook that translates hourly and annual income to a per minute rate, and then gave a table of how much various jobs earn per hour (in the U.K.). Her post, “How much is your time worth?” is here. I think that the way Ruth used Excel Web App is a really great way to get started. Very cool! I love to see Excel in action.

While reading it, I thought of what other ways people can use Excel to create an interactive, live experience in their blogs, while using graphics to tell a story and keep their users engaged. I’ve tried the following version:

In this example, I’ve used color scale conditional formatting on cells to show the range of pays for different jobs, and also created a simple bar chart to visually compare ones hourly rate with that of other jobs. A picture is worth a thousand words…

Related Posts:

How to: insert workbook in WordPress

While embedding Excel on a web page is pretty easy, doing it in WordPress.com is a little tricky.

The problem: Excel Web App embedding is done using an iframe, and WordPress.com strips out iframes for security reasons, except from sites that are whitelisted.

The solution (which you can do even w/o understanding the problem :-)) is below.

  1. While editing the post, switch to HTML mode
  2. Paste the HTML markup you got from the file in http://office.live.com
  3. Click Save Draft

Once you’ve saved the draft, you can continue working as you usually do, preview the post, continue editing it, publish it, etc. The important thing is to save the draft immediately after pasting the HTML code. Otherwise, WordPress will strip it out.

Doing “Save Draft” changes the iframe code to a different code, that is not stripped out by WordPress. For example:

<iframe src=”http://r.office.microsoft.com/r/rlidExcelEmbed?su=89268436867536320&Fi=SD13D252B9D24D5C0!111&AllowInteractivity=False&#8221; width=”402″ height=”346″ frameborder=”0″ scrolling=”no”>

Is replaced by:
[office src="http://r.office.microsoft.com/r/rlidExcelEmbed?su=89268436867536320&Fi=SD13D252B9D24D5C0!111&AllowInteractivity=False" width="402" height="346"]

Notes:

First of all, the trick above and the notes below are based on my own experience and trial and error. I couldn’t find any official documentation for this. If you have experienced anything else, of have more information, please enter a comment.

As far as I can tell, this issue (and workaround) is only relevant for WordPress.com. Self-hosted WordPress seems not to strip iframe so it doesn’t exhibit this issue.

Using Windows Live Writer to post to your WordPress.com blog also seems to work just fine.

Creating a web calculator using Excel

Another typical use of Excel is a calculator. In the example below, it is a simplified version of a refinance calculator.

Users can enter new values for the loan, current and new interest rates, and the refinance fees, and get a calculation a graph for when the new mortgage will pay off.

In order to allow people to enter data in cells, you need to set AllowInteractivity=True and AllowTyping=True.

Step by step:

  1. Create the workbook
  2. Save or upload the workbook to a public folder in http://office.live.com
  3. Get the embed code for that workbook
  4. Embed it in your web page
  5. Change AllowInteractivity to true
  6. Change AllowTyping to true

Related posts:

  • How to embed Excel on a web page

 

Visualize your data with interactive charts

Let’s take the example from the previous post one step forward, by adding a simple chart on top of the data:

Here, you can see how slicers can filter the data, and the chart built on top of it.

Step by step:

  1. Create the workbook with a chart on top of the data 
  2. Add slicers to slice and dice the data (optional step, requires Excel 2010)
  3. Save or upload the workbook to a public folder in http://office.live.com
  4. Get the embed code for that workbook
  5. Embed it in your web page
  6. Change AllowInteractivity to true

Related posts:

Follow

Get every new post delivered to your Inbox.