Tips for making your embedded Excel experience sparkle

This post is brought to you by Michael McGraw-Herderg, a program manager on the Excel team who specializes in embedding and programmability.

When we show people examples of beautiful, interactive embedded Excel workbooks built with the embedded Excel Web App, they often ask one of two things:

1) (Excited) “How do I do that?”

2) (Skeptical) “Can I really make my own workbooks look as good as these examples?”

We love to hear that first question, because this summer we shipped a new in-app embedding experience that makes things as easy as opening up a public workbook in your Windows Live SkyDrive and clicking the Embed button:

(If you don’t see an “Embed” button, make sure that the file is public and that that it’s an .XLSX file.)

We love to hear that second question, because it turns out that there are plenty of easy, small steps you can take to make any embedded workbook shine.

In this post I’ll explain three basic techniques that will get you started:

1) Organize your data with a table.

2) Summarize your data graphically with a chart.

3) Embed a single named item – like a table or a chart – instead of the entire workbook.

Let’s tackle these steps with a real-world problem.

I spend a fair amount of time each year on airplanes, but travel has slowed down lately. To keep my “frequent flyer status," I’d like to log at least 50,000 miles this year. My airline does a great job of telling me how many miles I have so far, but a terrible job of telling me how many more I’ve got booked or planned. So I do it myself. I blog about my travels, and I’d like to share how things are going with friends.

It turns out that Excel and the Excel Web App are great tools for this job. With about ten minutes and these three steps, I went from a busy, unfocused scratch pad:

To a tightly organized chart and table:

Here’s a step-by-step guide to getting there yourself.

Step One: Make it a Table

If it’s got rows and columns, it will probably look beautiful as a table.

My file was already on SkyDrive, so I decided to make it a table from within the Excel Web App. After selecting a cell inside my table, I chose Sort & Filter as Table from the Home tab:

It ended up looking like this:

All of a sudden I noticed that I had typed in my travel dates out of order. Happily, those little triangles on each column header let me sort things out:

With my date problem sorted, I found the table was still too busy – it showed me a lot of trips I had already taken. I manually touched up the table by greying out less-interesting rows from the past:

One more nit: that column on the right was ultra-wide and yet somehow it still didn’t fit the whole column title. That looked gnarly!

The Home Tab command “Wrap Text” let me shorten the column without losing any words:

Now, it’s pretty easy to keep a year-end total and see whether I have enough flights booked to hit 50,000 miles for the year. I started out with a basic SUM formula that shows me that.

But I actually also get some cool perks once I hit 25,000 miles. When, exactly, can I expect that to happen? To figure that out, I typed in a fairly simple formula that uses an absolute reference to build up a running total…

… and Excel automatically added a table column and filled my formula all the way down the table. (I cleaned it up a little to name the column header and keep my grayed-out rows gray.)

Looks like I’m scheduled to pass the 25,000-mile mark after just two more trips!

Step Two: Add a Chart

Data can tell a compelling story, but they sure can be noisy. In late April, I picked up 13,000 miles on one international round-trip. Who cares that I transited through <acronym title=”Denver”>DEN</acronym>, <acronym title=”Chicago”>ORD</acronym>, <acronym title=”Paris”>CDG</acronym>, <acronym title=”Zurich”>ZRH</acronym>, <acronym title=”Washington, D.C.”>IAD</acronym>, and <acronym title=”San Francisco”>SFO</acronym”>? I usually just want to see what that means.

To play with the data a little more, I opened my workbook from the Excel Web App into Excel 2010. (This works great with Excel 2003 or Excel 2007, too.)

I decided I’d visualize my running total versus time by selecting both of those columns, visiting the Insert tab’s Charts group, and choosing to insert a Line chart. Excel’s first try was pretty solid:

But I wanted to do better. Excel’s Chart Design tab helped me pick out a snappy green theme, and its Layout tab helped me put the legend under the chart:

I tweaked the number format of the axis to just show month names. And I double-clicked the chart title to get a new one:

While I was working in the Excel client, I used the Table Styles ribbon group to pick a matching green theme for my travel table.

Step Three: Embed An Item

When I hit “Save” in Excel, the latest version of my workbook was saved straight to my SkyDrive. That made it easy to open up my file again in the Excel Web App and bring up the in-app embedding dialog:

If you pick an item (like a chart or a table), we’ll automatically size the embedded Excel viewer and the preview to fit your item, and we’ll give you code you can use to drop the item straight into a blog post or Web page like this one.

Here’s that chart and table again:

Share Your Own Tips

Have you ever seen someone do something really simple that’s made their workbooks shine? Tell the world about it with comments on this blog post! 

--Michael