PivotTables can save the day in a budget crisis


This “Office Show” episode features a common dilemma: The budget data you want to analyze in Excel contains so many rows and columns of numbers that it seems like an impossible task to get it all sorted out. Meanwhile, there’s a deadline looming and mounting pressure to figure out how to save money.

This is where PivotTables come in. PivotTables are brilliant at making sense out of walls of numbers, because they do a lot of the work for you. They can automatically organize and summarize your data, so you can immediately see things like what areas of your business are performing the best (and, unfortunately, the worst).

So what situations really call for a PivotTable? Consider creating one when you want to:

  • Make sense of large amounts of data and numbers – A PivotTable helps you see the forest through the trees.
  • Analyze your data in minute detail – Drill down to answer specific business questions. For example, you’ll be able to see which of your products are outselling others or which ones are generating the most revenue.
  • Focus on areas that need your attention – Apply specific filters to see just the data you want to analyze.
  • View your data from different angles – Pivot the data to get the perspective you want, perhaps to compare data from different regions or different products.
  • Present your data in a report – A professional-looking report format will help other people easily understand your data analysis, especially if you use features such as conditional formatting and sparklines. You can even include a PivotChart for visual cues.

We have plenty of resources on creating PivotTables, starting with this blog post and video by PivotTable expert Frederique Klitgaard. A few other helpful links:

Create or delete a PivotTable or PivotChart report

Quick start: Create a PivotTable report

Overview of PivotTable and PivotChart reports

What if you need to connect your Excel workbook to multiple data sources at once? That’s where PowerPivot, a free add-in for Excel, can really help. It also increases Excel’s capacity to deal with hundreds of millions of rows. We’ll have more on PowerPivot in an upcoming post.

 — Doug Kim and Frederique Klitgaard

Mail merge in Word and Publisher made easy

Mail merge in Office is not always the easiest thing to pull off correctly. But once you do, it can save you enormous amounts of time. Plus you can really impress your friends and customers with the results. Right now, when you’re getting ready for the holidays, is a great time to learn how to do it.

Part of the problem is with the number of variables involved. There are lots of different formats for your contacts, as well as lots of different types of mail merge: labels, emails, flyers, newsletters, etc.

Fortunately, we’ve created a new guide that helps you cut through all that quickly and get to the right information you need really quickly. It’s a PowerPoint deck that we call Mail Merge Made Easy.

The deck walks you through some simple questions, like what label program do you want to use, and where do you keep your contacts? Clicking on the link for your answer sends you to the portion of the deck tailored for exactly what you want to do. Then you can follow the simple, step-by-step instructions. Here’s a preview to show you what it’s like. This excerpt, for example, shows just the slides for creating labels with Word and Excel.

 

Try it yourself, we’d love to know if you find it useful. You can download it here from Office.com.  You can find lots more resources on mail merge and your other holiday planning needs at our Holiday Mailing page on Office.com.

Doug “Office Casual” Thomas also created some videos on “the key ingredients for mail merge”, and they’re super helpful. Check those out below.

Note: the deck is set up for Office 2010, but Office 2007 pretty much works the same way.

— Doug Kim

How to embed a PowerPoint presentation on a web page

You can display a Microsoft PowerPoint presentation on your web page, such as your blog or SharePoint wiki page.Here’s a step-by-step guide:

The presentation is stored in a public location in Windows Live SkyDrive, where you copy an HTML tag that you paste into the HTML of your blog or wiki page. That way, people can see the presentation without having to go to the SkyDrive folder. They advance through the slides one at a time.

Updates to the presentation on SkyDrive are automatically reflected on the page where the presentation is embedded.

Embedded PowerPoint presentation

Note: Any animation effects on slides or transitions between slides are not visible in the embedded viewer, and media such as audio or video does not play in the embedded presentation.

Step 1: Make your presentation public

Before you can embed a presentation on your web page the presentation must be available to view publicly.

If you are creating a new presentation, there are two ways to store it publically on Windows Live SkyDrive. One method is simplest if you are creating your presentation in the PowerPoint desktop application; the other method is simple if you are creating a presentation in your web browser, in PowerPoint Web App.

If you have an existing presentation that you want to embed on a web page, you can upload it to a public folder in your SkyDrive. Or, if it’s already stored in a personal folder on your SkyDrive, you can move it to a public folder.

  1. Sign in to http://office.live.com.
    If you don’t yet have a Windows Live ID, click Sign up on the Windows Live page.
  2. Click New, and then click Folder.
    Create New Document
  3. Type a name for the folder, and then next to Share with, click Change.
  4. Move the slider to Everyone, and then click Next.

 

In the PowerPoint desktop application

Create the presentation in the PowerPoint desktop application, and then save it in a public folder on SkyDrive.

  • If you have Microsoft PowerPoint 2010, save the presentation directly on SkyDrive by clicking Save & Send in the backstage view (click File). Click Save to Web, and then double-click your public folder after you are signed in to Windows Live.
    If you don’t yet have a Windows Live ID, click Sign up for Windows Live SkyDrive.
  • If you have an earlier version of PowerPoint, save the presentation on your computer and then upload it to SkyDrive by signing in at http://office.live.com and going to your public folder.
    If you don’t yet have a Windows Live ID, click Sign up on the Windows Live page.

For more information about saving Microsoft Office documents on SkyDrive see Store a file for Office Web Apps in Windows Live.

 

In PowerPoint Web App

  1. Go to http://office.live.com, and sign in with your Windows Live ID.
    Don’t have a Windows ID? Click Sign up on the Windows Live page.
  2. Near the top of the page point to Office, and then click New PowerPoint presentation.
  3. Type a name for the presentation, and then click Save.
    The presentation opens for editing in PowerPoint Web App, and your changes are saved automatically.
  4. To make the presentation public, click File, click Share, and under Who can access this, move the slider to Everyone.
  5. When you are finished, click Close near the top of the web page.
    Close

Upload or move an existing presentation

If the presentation is stored on your computer, do the following to upload it:

  1. Sign in at http://office.live.com and go to your public folder.
    If you don’t yet have a Windows Live ID, click Sign up on the Windows Live page.
  2. Click Browse, locate the presentation on your computer, and then double-click it.
  3. Click Upload.

Note: If you have a lot of files to upload, click Install the upload tool. When the Windows Live upload tool is installed you will be able to select multiple files to upload.

If the presentation is stored in a personal folder on your SkyDrive, do the following to move it to a public folder:

  1. Sign in at http://office.live.com and go to your personal folder.
  2. Point to the presentation, click More in the menu that appears, and then click Move.
  3. Click the name of the public folder.

Note: If the folder already contains a presentation with the same file name as the presentation you are moving, that presentation will be replaced with the presentation you are moving. To prevent this from happening, rename the file. Click Cancel. Point to the presentation, click More, and click Rename. Then, type a new name, and click Save. Now you can proceed by repeating Step 2.

  1. Click Move this file into YourFolderName.

 

Step 2: Copy the HTML tag from PowerPoint Web App

  1. Sign in to Windows Live at http://office.live.com.
  2. Near the top of the page point to Office, and then click Your documents.
  3. In the list of Shared documents point to your PowerPoint presentation. Or, open the public folder and point to the presentation there.
  4. In the menu that appears click More, click Share, and then click Embed.
    Click Embed
  5. Click Copy.
    If your browser prompts you to allow access to your Clipboard click Allow access.

 

Step 3: Paste the HTML tag into your web page’s HTML

The HTML you copied from the PowerPoint Web App page is an iframe tag, which is supported in many web authoring environments and blog services. The steps provided here work in some of the most popular blogging services, such as WordPress, Blogger, and TypePad.

Note: If you use a blogging service that does not support iframes in posts, consult your blogging service provider for assistance.

If you want to embed the presentation in a SharePoint wiki, you paste only the src portion of the tag into a Page Viewer Web Part. See the instructions below.

In a blog post

  1. In your blog editor, begin writing your post, and then switch to HTML editing.

 Note    In TypePad don’t use the Quick Compose editor. Go to Blogs, click the name of your blog, and then click Compose, where you can switch from Rich Text to HTML editing.

  1. With the HTML tag you copied from the PowerPoint Web App page as the most recent item in your Clipboard, press CTRL+V.
  2. Finish writing your post. Preview and publish as you normally would.

In a SharePoint wiki

  1. On the wiki page click Edit.
  2. With the HTML tag you copied from the PowerPoint Web App page as the most recent item in your Clipboard, press CTRL+V to paste the tag on the wiki page. This is so that you can easily copy a portion of the tag into the Web Part. You’ll delete the tag from the wiki page before you’re done.
  3. Copy the portion of the tag between quotation marks that begins with http. Don’t include the quotation marks.
  4. On the Editing Tools tab click Insert, and then click Web Part.
  5. In the list of categories, click Media and Content.
  6. In the list of Web Parts, click Page Viewer, and then click Add.
  7. Click open the tool pane, click OK to save changes, and then in the Link box delete http:// and press CTRL+V to paste the address you copied in Step 3.
  8. Click Apply to preview the page.
  9. Make adjustments to the Web Part as desired. For example, in the Page Viewer editor expand Appearance and specify height of 332 pixels and width of 407 pixels to fit the presentation in the frame with no scroll bars.
  10. When you are finished click OK in the Page Viewer editor, and delete the iframe tag from the wiki page.

— Roxanne Kenison

PowerPivot in Excel 2010: Millions of rows! (Insert Dr. Evil laugh here….)

 

PowerPivot for Excel is an Excel 2010 add-in that allows users to pull data from multiple sources, mash them up, and then build reports using regular pivot tables. You can even share these reports with others in Microsoft SharePoint (via PowerPivot for SharePoint). In this demo, Julie Strauss, Program Manager for Microsoft SQL Server Analysis Services, shows just how easy it is to get a better view into your data. Watch for the part where she sorts 100 million rows of data instantly. 100 million rows. Not a typo. You can download the PowerPivot add-in at the Microsoft Download Center. More info at powerpivot.com.

New for Excel 2010: Sparklines!

Guest blogger Sam Radakovitz, program manager for Excel, has a post today on a sparklines, a new feature in Excel 2010.

For Excel 2010 we’ve implemented sparklines, “intense, simple, word-sized graphics”, as their inventor Edward Tufte describes them in his book Beautiful Evidence.  Sparklines help bring meaning and context to numbers being reported and, unlike a chart, are meant to be embedded into what they are describing. 

As you can see, I’m really excited about this new version of Excel, and specifically speaking: sparklines!  Bill Jelen, aka Mr. Excel, and Mike Girvin (Excel is Fun) are as excited as me in this video highlighting sparklines in Excel:

[View:http://www.youtube.com/watch?v=gNf1zddxuQc:550:0]

This video shows you how sparklines were born into Excel and how they look:

 

…and you know I think sparklines are awesome, but you can download the Office 2010 Beta and take a look for yourself!

Sam Radakovitz, Program Manager, Excel