Practical PivotCharts in Excel

AppId is over the quota
AppId is over the quota

This post is brought to you by Kevin Donovan, a Program Manager on the Office Graphics & Visualizations team.

PivotCharts allow for charting-based data exploration and summarization in a way that no other Office visualization can. But they also offer power and flexibility that are largely unknown to many standard chart users, unless they work across businesses where PivotCharts play a significant role within their business intelligence efforts. Like PivotTables, PivotCharts work really well on aggregate data, which is why a lot of casual Excel analysts don't often encounter the need to use them. I'll show you some of the reasons and ways to use PivotCharts to explore your data.

I've borrowed a nicely-sized data set from my Power Map colleagues to illustrate the point. The data set examines power generation plants, by type, and across different states in the U.S. I like this data set because it illustrates the type of data structure best suited to PivotCharts, which aggregate values based on repeating categories of data, allowing you to slice it in interesting and insightful ways.

If you've ever inserted a chart in Excel, you may have used the incredibly valuable Recommended Charts feature in Office 2013.

The algorithm behind that feature looks at your data and decides what chart type best matches the selection. It's an awesome feature because it means that customers don't have to agonize over the best chart for their data. Moreover, the recommendation engine evaluates whether PivotCharts fit the shape of the data. When the recommendation engine sees a bunch of repetitive rows or columns, the chart suggestions will include PivotCharts because it's likely that you want to see that data aggregated in some meaningful way. As you can see from its repetitive values, our sample set lends itself to a PivotChart:

You'll know when the recommendation engine suggests a PivotChart because you will see a little pivot icon in the upper-right corner of the preview thumbnail:

Given the size and shape of this data, you likely wouldn't want to visualize it with a standard chart. If you did, it would be very difficult to read because the chart attempts to plot all of the data in the table, like this:

But if I apply a PivotChart at the same data set, I get something much more useful. That's because PivotCharts allow you to visualize the aggregated data, which is automatically grouped when you create the PivotChart, according to different "pivots" (often called "dimensions"), such as date, company, country, and state. Thus, a PivotChart on the same data set yields a chart like this:

Think of a PivotChart as the visual equivalent of a PivotTable. Compare the prior chart with the one that follows. The two use the same data set. However, in order to generate the one below, I used the Field List to select additional pivot points. Once they're selected, I used the Field Well to move them around. In my Axis category, I have State first (in this case I filtered out everything except the Western most states) and Energy Description second. This creates a hierarchy on the x-axis, first by State, then by Energy Description.

The first thing to notice are the on-chart meta-data elements. These are the tell-tale elements that distinguish PivotCharts from standard charts. One of those elements appears in the upper-left corner of the chart. It allows users to see the values (typically called a "measure") on the y-axis. If I had two measures in my data set, I would see two of these badges on the chart.

The other element unique to PivotCharts is an on-chart filtering option. This piece is one of the most valuable elements on the PivotChart because you can filter the PivotChart in really robust ways. It's always located on the lower-left corner of the chart and contains the same set of functions found in PivotTables.

Because PivotCharts often operate on large, hierarchical, and generally complex data sets, they also contain some enhanced filtering options. For example, as long as the PivotChart connects to a PivotTable, users can reduce the uninteresting data in their charts very quickly, filtering on values, sorting, and searching.

You'll notice that these are the same, powerful filtering options that you find on PivotTables. The options become very handy when your charts get too unwieldy or when you only care about a subset of the data. For example, if you only want to see types of energy whose megawatt value is over 100,000, you could easily create that chart:

In Excel 2013, we added the ability for you to create PivotCharts against a data model in your spreadsheet without creating a corresponding table. We call this decoupled PivotCharts. For these charts, we create the PivotTable in memory and use that data to drive the chart. This allows you to create a really clean surface of charts without the clutter of all of the accompanying tables. Plus, you get the Field List and Field Well that come with all PivotTables and PivotCharts. I can insert a new PivotChart directly against the data model contained in the workbook without having to select a PivotTable first. To do this, simply click the PivotChart button in the Charts group of the Ribbon, and choose Use an external data source.

Here, I can see an aggregate of megawatts, by energy type, by date.

In short, we created PivotCharts to visualize hierarchical data sets, and they work really well for those situations where data can be aggregated and sliced by some dimension that allows users to gain new insights on their data. Most of those scenarios involve business intelligence because they integrate nicely with in-memory models and OLAP data sets, which tend to prevail within businesses. But there's no reason why anyone analyzing the types of data I've described wouldn't benefit from the power and flexibility of PivotCharts.

I hope that helps clear up some of the mysteries of PivotCharts. Let us know what you think in the comments below!

-- Kevin Donovan, Program Manager, Office Graphics and Visualizations

 For more on Excel charts:

Power Map for Excel earns new name with significant updates to 3D visualizations and storytelling

Telling a story with charts in Excel 2013

Drilling around in your Excel PivotTables and PivotCharts

View the original article here

Automatically plot multiple pieces of online data in your Excel spreadsheet

AppId is over the quota
AppId is over the quota

This post on exploring Webservice functions is brought to you by Lee Bizek, a Program Manager in the Excel Team. 

If you’ve ever wanted to incorporate data from online resources into Excel spreadsheets, now you can with the new Webservice function. With this new feature, you can add information such as stock quotes, weather, Bing Search results or even Twitter feeds to your spreadsheets—and you can make the information update automatically. In this post, we'll discuss some of the more advanced aspects of the Webservice function.

I’m starting with a spreadsheet I created to organize a group camping trip that includes the latest weather information from the internet. In this example, being aware of online weather updates helped us to prepare for the elements and also predict high and low tides because we wanted to go crabbing as part of the adventure. I’ll show you how to take this spreadsheet a step further to use the =FILTERXML(xml, xpath) to extract multiple records of data from the XML string with an array. This will allow you to automatically plot multiple pieces of data in your spreadsheet.  

Note: Before you start the exercise, be sure to review how to use Webservice functions to automatically update Excel 2013 spreadsheets with online data so that you know how to:

Use a =WEBSERVICE(url) function to get dataUse the =FILTERXML(xml, xpath) function to extract a single piece of data from the XML string

We’re going to set up a Webservice function call to the URL for the 10-day weather forecast and then pull out the details from the XML string. To do this, follow these steps: 

1. Open the example file Kamping 2013 – Start and Complete Sample Files.zip located at the end of this post (click the icon next to Attachment).

2. Go to http://www.wunderground.com/weather/api/d/docs?d=data/forecast10day to get the URL for the 10-day forecast. At the bottom of the page, you’ll see an example URL.

3. Copy the example URL and paste it into your Webservice function in cell B11 of your spreadsheet. (Note: Be sure to replace the API key and location information in the URL to the cell reference we used to incorporate data from an external source so that you can control the API key and zip code for both Webservice functions in the same place.)

4. In your Excel spreadsheet, select cell range B14:B23.

5. Click the Formula Editor Box and then type: 

=DATEVALUE(CONCATENATE(FILTERXML(B11, "//date//month"), "/", FILTERXML(B11, "//date//day")))

In this example, I am pulling out the month and day, CONCATENATING them together and then converting the string to a DATEVALUE.

6. Press CTRL + SHIFT + ENTER to execute the array. This action adds the brackets { } that indicate it is an array. Find more information about array formulas here.

7. Select cell range C14:C23, then type: =FILTERXML(B11, "//high//fahrenheit")

8. Press CTRL + SHIFT + ENTER for the high temperature.

9. Select cell range D14:D23, then type: =FILTERXML(B11, "//low//fahrenheit")

10. Press CTRL + SHIFT + ENTER for the low temperature.

11. Select cell range E14:E23, then type: =FILTERXML(B11, "//conditions")

12. Press CTRL + SHIFT + ENTER for the conditions.

 

Note: To edit the formula, select all of the cells in the array, then click the Formula Editor. Press CTRL + SHIFT + ENTER to execute the new formula.

WEBSERVICE Functions are “non-volatile”, which means they refresh only when:

A referenced cell is edited

The entire workbook is refreshed (CTRL + ALT + F9)

Some text strings include spaces or non-URL-friendly characters instead of URL-friendly characters such as a zip code. You can still use the URL in the WEBSERVICE function, but it’s best to encode the string first. Use the =ENCODEURL(text) function to include text searches into your WEBSERVICE function URL. Learn more about ENCODEURL function here.

 

Now we are better prepared for packing and planning our crabbing activities!

--Lee Bizek, Excel Program Manager

Download the full example file Kamping 2013 – Start and Complete Sample Files.zip attached below. The example file also utilizes the tide data and includes XPATH filters in the WEBSERVICE Functions. Remember you can use this functionality for many different web services to pull in data that you can then analyze using Excel.

To use the web service in this example, go to www.wunderground.com and register for their Weather API to get your own API key. Once you have your API key, add it to the Weather tab. All WSF calls in the file will use this cell reference in the URL.

View the original article here

Exciting new BI features in Excel

Yesterday during the Microsoft Worldwide Partner Conference we have some exciting new business intelligence (BI) features available for Excel. In particular, we announced the extension of the BI offered as part of the power bi-findings analyze a cloud-based BI solution, easily attract customers from their data in Excel and work visualizing data in a self-service manner.

Power BI for Office 365 now consists of:

Power query, the customer to easily search and public data and your company data, all within Excel (formerly known as "Data Explorer").  Information about downloads here. Power card, a 3D data visualization tool for mapping, to explore and interact with geographical and temporal data (formerly known as code name "Geoflow").  Information about downloads here.Power pivot for create and customize flexible data models in Excel. Power view for the creation of interactive charts, graphs and other visual representations of data.

Head on over to the Office-365-technology blog, to learn Office News Blogand power BI website to find out more.

View the original article here

Adding rich data labels to charts in Excel 2013

AppId is over the quota
AppId is over the quota

Storytelling is a powerful communication tool, and data is essential for many decision-making tasks. Together, they can be data visualization at its best: the science and art of transforming your data so that the most important points shine through. Sometimes a basic chart will do the trick.  But to make your visual message really pop, it's often handy to add data and text to your chart. The rich data label capabilities in Excel 2013 give you tools to create visuals that tell the story behind the data with maximum impact. 

To illustrate some of the features and uses of data labels, let's first look at simple chart.

This clustered column chart shows the sales (revenue) of drinks and snacks from a neighborhood lemonade stand during one week. If I want to turn on basic data labels on the blue data series (Drinks), there are a few ways to do that.  One familiar and simple way is just single click on any data value (or column, in this example) to select the entire data series that it belongs to.

Above, I have clicked all of the blue columns. Once the series is selected, I can right-click any column to pull up the context menu, then click the Add Data Labels entry.    

When I click Add Data Labels, I get the following result. 

To reposition any single data label, all I have to do is double-click the data label I want to move, then drag it to the desired position on the chart. Here, I have selected only the Tue value of the blue Drinks series.

Once selected, I can drag that label wherever I want it on the chart. If I drag the label far from its default location, a leader line appears by default to show what data point the data label is associated with.

Basic formatting of data labels is simple to achieve by using the Font section of the Home tab on the Excel ribbon.

If you wish to go beyond basic text formatting and text box fills, many more formatting options are available on the Formatting Task pane. Though there are several ways to open the Formatting Task pane, the easiest is to double-click the data labels themselves. Here, I have double-clicked one of the data labels for the blue Drinks series.

In the Formatting Task Pane, you can customize the way the data labels appear, change their size and alignment, change their text properties, and even add another data series for them to include. See Format and customize Excel 2013 charts quickly with the new Formatting Task pane for more discussion about the Formatting Task Pane in general.  

Often, the real story doesn't lie in all the numbers in the chart, but it's hidden in a few key data points. Let's reapproach our example with that in mind.  

First, I'll delete the data labels that I already put in place. To delete all the data labels for a given series, click once on any data label in the series, and this will select them all.  If you press the delete key on your keyboard, all the data labels from that series will disappear from your chart. To delete any single data label, follow the same procedure, except click twice (and not too fast) on the individual data label you wish to delete.

Below, I have inserted just one data label and moved it to a roomy place in the chart. Next, I want to type custom text into the data label box to help tell the story behind the data.

To make it easier to place an insertion point in the data label, I have found that it helps to zoom in on the chart. You can do this by adjusting the zoom control on the bottom right corner of Excel's chrome.

Then, select the value in the data label and hit the right-arrow key on your keyboard.

The story behind the data in our example is that the temperature increased significantly on Wednesday and that appeared to help drive up business at the lemonade stand. So I type some text to emphasize that point while still leaving the data label intact.

Excel 2013 also lets you put numbers from your spreadsheet into your data labels - that is, numbers that are not directly associated with the data point.  Here is a quick example. Let's say that I want to add a further annotation about the temperature on Wednesday and I want to include a data value with that annotation. Here's how I would do it: 

First, I select my data label and I type some additional text to give context to the new number I'm about to add to the data label. Then, I right-click the data label to pull up the context menu. Note the Insert Data Label Field menu item. 

When I click Insert Data Label Field, Excel 2013 opens a dialog that gives me a few options to choose from. I want to pull in a data value that is calculated on my worksheet, so I select Choose Cell.

The Choose Cell option opens a familiar type of dialog that allows me to go back into my worksheet and select the cell with the value that will be shown in my data label. In this example, I select a cell that contains the value that shows how many days it has been since the temperature was this warm (26 days, in this case).  

When I click OK, the value from the cell I selected (D39, here) appears in my data label. To finish it off, I type the rest of my statement and end up with a very rich data label.    

The data labels up to this point have used numbers and text for emphasis. Putting a data label into a shape can add another type of visual emphasis. To add a data label in a shape, select the data point of interest, then right-click it to pull up the context menu. Click Add Data Label, then click Add Data Callout.  The result is that your data label will appear in a graphical callout.  In this case, the category Thr for the particular data label is automatically added to the callout too.

In the image below, I clicked inside the data callout, backspaced over the Thr entry, and then typed a bit of information that explains what is behind this anomalous data point.    

If you want to change the shape of a data callout, you can do so by right-clicking the data label to pull up the context menu, or by selecting the data label, then clicking Change Shape in the Format tab in the ribbon.

In this case, let's say that for the Snacks value on Thursday, I don't really want to show the value in the data label, but I'd like to make my point with something a bit more whimsical. For this, I turn to the rich formatting options in the Formatting Task pane we talked about earlier. Below, I have double-clicked the data label to pull up the Formatting Task pane for the data label.

Then, I clicked the Fill and Line symbol:

I then selected Picture or Texture Fill, and clicked on Online.

Let's say that on Thursday the lemonade stand ran out of donuts, which were the main selling item in the snacks section. I can search Office Art on the web for an image of a donut to serve as the background in my data label.

I pick an image from the results, and it's automatically inserted into the background of my data label.

We're almost there. The donut image is good, but it's too small to convey the message. Also, I want to use a text comment instead of showing the data value for this point. So I re-size the data point's bounding box, select the data value, and replace it with some clarifying text.  I can adjust the text color and font size using the Font controls on the Home tab in the ribbon.   

In this article, we used data labels with text, images, and shapes to help reveal the story behind the data. This example only scratches the surface of the many things you can do with data labels in Excel 2013. Have fun exploring and storytelling, and let us know in the comments how you're putting data labels to work for you!

View the original article here

Webinar: Simplifying your Excel Data

AppId is over the quota
AppId is over the quota

The webinar is over. A video of the webinar will be posted shortly .

In this week’s webinar you’ll learn how to make a massive sheet of numbers something that can tell a story with just a few clicks. We’ll also show you a better what to show data in a presentation. We start our Office 15-Minute Webinar at 9:15 am Pacific Time, with a Q&A to follow.

What you will learn at Tuesday's webinar

Cut the clutter with Conditional Formatting and Sparklines Excel 2013’s Quick Analysis tool A better way to present numbers to a crowd

References for this webinar

 Go to http://aka.ms/offweb for more information on how to join the series.

--Doug Thomas

View the original article here

Opening workbooks by running separate instances of Excel

AppId is over the quota
AppId is over the quota

This post is brought to you by Chad Rothschiller, a Program Manager in the Excel Team.

In Excel 2013, each of your workbooks opens in its own window, similar to Word and PowerPoint. This means that each workbook has its own ribbon and top-level window frame that you can move and resize independent from your other open workbooks. This functionality can improve multitasking, visibility, and analysis across workbooks.

We'll discuss some of the more advanced aspects of these changes, in particular how to have workbooks running in different instances (or processes) of Excel.

Excel 2013 helps prevent accidental extra instances (or processes) of Excel running. We've found that extra instance introduced unexpected and sometimes confusing results (for example, pasting a range from a workbook in one instance of Excel to a workbook in a different instance of Excel works differently than if the two workbooks were running in the same instance). In the new Excel, we have built new detection logic that knows when an additional, new instance of Excel is starting up (e.g., you clicked Excel on the start menu), and merges that workbook (and new Excel instance) into the instance of Excel that's already running.  In other words, you won't very often see more than one Excel.exe in the Task Manager. Word has behaved this way for several releases now, and it is a much simpler model to understand.

However, we've built in a couple of ways for you to get a second (or third) instance of Excel running, if that's really what you want. Most often, people want to do this when they know they have a huge workbook that takes a long time to open (or some other process that takes a long time to complete, such as a data refresh or a calculation), and they want to keep working in Excel while that long-running process finishes. One good way to accomplish this is to get two instances of Excel running: one you can use to kick off the long process, and the other you can use to keep working while you wait for the other process to finish.

In Windows 8, one way to start an additional instance of Excel is to press and hold the ALT key, then, without releasing the ALT key, right-click the Excel icon in the Windows taskbar and click the Excel icon above the Unpin option.  

Continue holding down the ALT key until you are asked whether you want to start a new instance of Excel.

Click Yes to get a second instance of Excel up and running.

In general, anytime Excel is starting up, holding down the ALT key triggers the above dialog asking about a new instance.

There's also a switch for booting Excel from the command prompt: /x. In Windows 8, press the Windows key and X key at the same time and then select Run. 

Type excel.exe /x, and then click OK. This should launch a new instance of Excel.

The switch to Single Document Interface (SDI) might impact some of the code in your add-ins or other solutions, particularly if the code:

Works with Window methods, events, and properties. All existing application-level window methods, events, and properties are unaffected by this change. All existing workbook-level window methods, events, and properties now operate on the top-level window for that workbook.Users custom ribbon controls and/or task panes. Code working with custom ribbon controls and task panes might need to be updated to handle multiple custom ribbon controls that are present when more than one workbook is open. Remember that each workbook gets its own instance of the ribbon, along with all the controls it contains.Employs modeless userforms. Now that there are multiple top level Excel windows, none of them is the master. Which makes keeping modeless userforms on top of all Excel windows very difficult. Code might need to be added to help keep the userform on top.

Thanks everyone for reading. I hope you all get wonderful benefits from being able to spread your workbooks out on your screens!

--Chad Rothschiller, Excel Program Manager

View the original article here