You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
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!
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.
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:
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!
Thanks for the information. Excel is going places. Nice to see that
Question: Could we create a table for sea-lax,lax-sea,sea-sfo instead of sorting ?
Michael-Thanks for some really great functionality! We use it all the time to do some really interesting things on our site. The only item on our wish list is to be able to embed larger Excel files (in the 25MB range), either through SkyDrive or through our Office365. Another alternative might be allowing us to link workbooks in Sky/drive so that we can separate the data from the presentation/visualization.