Profit and loss data modeling and analysis with Microsoft Excel

Excel profit and loss data modeling and analysisAccounting and Finance Professionals have a new learning resource. The Profit and Loss Data Modeling and Analysis with Microsoft PowerPivot in Excel article, Excel workbook samples, and sample Access database provide scenario-based data modeling and analysis help for self-service BI with PowerPivot and Power View.

The scenario describes how Finance professionals at Contoso Ltd. create a PowerPivot data model, Excel PivotTables, and Power View reports to analyze budget, forecasting, and other profit and loss account metrics, such as:

  • Aggregate, or value measures such as sums and averages for currency and headcount.
  • Comparison measures such as year-over-year, year-to-date, and variance for cash flow and headcount.
  • Performance, or ratio measures such as percentage, cost per head, and rate and volume variances–all of which calculate on different combinations of value and comparison measures.

Extensive details of over 80 DAX formulas are provided, as well as tips for creating highly efficient and fast performing data models. The Excel 2013 version of the sample workbook also provides several dynamic Power View reports.

To learn more, download the whitepaper.

Introduction to PowerPivot in Excel 2013

This blog post was brought to you by Diego Oppenheimer a Program Manager on the Excel team.

clip_image002

Some weeks ago I introduced an important, new part of the Excel 2013 Data Model and how you could create relationships between different data sources right in your Excel workbook (http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx). In today’s post I am going to show you how you can expand on this Data Model and add all sorts of analysis “goodies” using the PowerPivot add-in that’s built into Excel 2013.

NOTE: If you are on the consumer preview of Office 2013 then the add-in is available. Once you upgrade to the full version you will need Office 2013 Pro Plus for this functionality to be available.

Activating the add-in

The simplest way to activate the PowerPivot add-in is to just insert a PowerView sheet from the ribbon:

clip_image003

This will automatically create an empty Data Model, insert a blank PowerView canvas (http://blogs.office.com/b/microsoft-excel/archive/2012/10/04/intro-to-power-view-for-excel-2013.aspx) and activate the PowerPivot ribbon.

If this all worked all right, your ribbon should have a Powerpivot tab at the end:

clip_image005

Another route is to go to File-> Options -> Add-Ins and then on the bottom dropdown select COM Add-ins:

clip_image007

Once inside you can enable Microsoft Office PowerPivot for Excel 2013.

PowerPivot vs. Data Model: I am confused

Let’s take a step back to explain a little bit further the differences in functionality (especially for those of you who have used PowerPivot in the past).

In previous versions of the PowerPivot add-in (Office 2010), the add-in was a standalone application that was user downloaded and installed. The add-in came with two components: an in-memory data analysis engine and a modelling user interface.

In Excel 2013, the engine component has been directly integrated, and we refer to it as the Data Model engine (the technology is actually called xVelocity). All the basic components of the Data Model like import data and create relationships are available for ever every version of Excel, but the extra modelling component is reserved for those with Office 2013 Pro Plus.

The extra modelling component has a ton of features that anybody who is serious about data analysis will want to have (don’t take my word for it try it yourself!), including:

· Pre-filtering data when importing

· DAX expression language for intuitive calculations

· Rename tables and columns as you import data in PowerPivot

· Use the Diagram View to manage your model and create relationships

· Define calculated fields and key performance indicators

· more

Adding some more data analysis to my housing search

As my starting point, I am going to use the file I used for my last blog post (you can find it here). I open the PowerPivot window by simply clicking on the Manage button in the PowerPivot ribbon. As soon as you open it, you will notice a very similar interface to Excel. This is reading directly the Data Model I created last time.

To get a better view, check out Diagram View on the top right. You should see something like this:

clip_image009

Diagram View is a great way to understand your entire Data Model, see what fields are related to what others and even create new relationships by dragging from one field on a table to another field in another table.

Switching back to Data View, I am going to add a calculation that’s going to show me any price changes since the last time the house was sold. To do that I simply right click, insert new column and then start building my expression. Because I do not have last sale prices for all my properties I need to make sure I am not dividing by zero. My expression looks something like this:

=IF ([LAST SALE PRICE] <>0, [LIST PRICE]/ [LAST SALE PRICE], 0)

In plain English, if the value in the column [LAST SALE PRICE] is NOT 0 then return [LIST PRICE] divided by [LAST SALE PRICE], else return 0.

If I go back to my PivotTable report, and add it, and change its aggregation to average, I now get an idea of what the price movement has been for the different zip codes.

image

By applying some quick formatting like I showed the in the previous post, I see that I have 2 zip codes for which my average house has appreciated over 200% — not something to base my entire decisions on but yet another good data point to have when making the big decision. Another interesting thing to look at is that the properties I am interested in Seattle have lost about 6% of their value since they were last sold.

Note: I want to point out that none of the “conclusions” or observations I am making can be taken just as is. My data set is only looking at properties I personally picked, houses that have been “flipped” increase their price dramatically, etc. Doing data analysis is not just about performing the analysis but also understanding what we are looking at –just a little disclaimerJ.

Adding some calculated measures to my model

One of the great things about the PowerPivot add-in is that it allows for really easily creating measures, which are based on the aggregates of any given column. What if I want to know what the Maximum and Minimum List Prices are for each zip code?

By going into the PowerPivot add-in, and selecting one of the cells at the bottom of the grid (this is called the calculation area because this is where we define calculations!) I can input a simple formula like:

MaxPrice:=MAX([LIST PRICE])

and another

MinPrice:=MIN([LIST PRICE])

Go back to the PivotTable, add those two fields, and there you go: Max and Min for every zip code.

So much Powerpivot, so little time

In this blog post I covered a very simple introduction as to how the Powerpivot add-in has changed since 2010, and how it canbe used, but I really haven’t even scratched the surface. Before I leave you, I would like to share some links where you can get all sorts of goodies and learn more about PowerPivot than you ever imagined. Some of these are Microsoft, some are from our good friends and hardcore PowerPivot enthusiasts who stretch the capabilities of our product in really amazing ways:

Microsoft Business Intelligence

What’s new in PowerPivot in Excel 2013

PowerPivotPro.com by Rob Collie

PowerPivotGeek.com by Dave Wickert

Kasper de Jonge’s PowerPivot blog

And as always feel free to ask me questions in the comments of the blog or on Twitter (@doppenhe).

–Diego

Quick Analysis: Discover new insights

image

This blog post is brought to you by Chad Rothschiller a Program Manager on the Excel team.

 

 

 

 

As part of our planning work at the beginning of the Office 2013 project, we (the Excel team) participated in several customer visits. We went in small groups to someone’s work environment (whether that was a large corporation, a small business, or even a home office), watched them use Excel, and talked with them a bit about what they were doing. Of course several patterns and themes were observed, but here are a few that caught my interest and really resonated with me and with several others on the team:

· Many people aren’t familiar with the variety of data analysis features that Excel has to offer

· Many people are hesitant to create / insert new features, even a chart. Some people feared they wouldn’t be able to change it back if they didn’t like the result

· Many people are simply unwilling to navigate away from the home tab to go looking for interesting features & capabilities

These are the main motivations behind Excel 2013’s Quick Analysis feature. We set out to create a dynamic interface that allows fear-free exploration of Excel’s analytical capabilities. The Quick Analysis galleries are dynamic: what appears in them changes depending on the type of data you’ve selected. Live preview makes it quick and painless to see what’s going to happen to the workbook before committing to a change.

Feel free to open the workbook and come along as we take a closer look at how it all works.

Getting Started: Select a Range of Data

The way to bring up the Quick Analysis interface is to select a range of data, and then click the little button that appears near the bottom right of the selection:

clip_image001

Clicking the button brings up a gallery of options to explore:

clip_image002

By default the Conditional Formatting gallery comes up, showing just a handful of choices, but there are other galleries to explore as well. The Charts, Totals, Tables, and Sparklines tabs in this callout each show a handful of choices from those respective categories to explore.

Conditional Formatting

Looking at my sample data, I might first be interested in taking a closer look just at the numbers, so I select G4:I20, and click the button. Hovering my mouse over the various icons in the gallery gives me a live preview of what choosing that option will do. Below are some examples with Data Bars and Color Scale. If you like what you see, click the button to add that to your selection, otherwise if you just move your mouse away the live preview will disappear.

clip_image003

(Data Bars)

clip_image004

(Color Scale)

Dynamic Conditional Formatting Gallery

In the example above I’ve selected numbers, so it makes sense that I’m seeing Conditional Formatting options that work well with numeric values. If I select all Text or all Date values, the items in the Conditional Formatting gallery change to work better with those types of data:

clip_image005

(Text values in the “Territory” column selected, showing a live preview of the “Text Contains” Conditional Formatting rule)

clip_image006

(Date values in the “Dates” column selected, showing a live preview of the “Last Week” Conditional Formatting rule)

Charts

Now let’s say I want show my data as a chart, but I’m not yet sure exactly what I want. I’ll select the entire range of data from B3:I20 (Names column all the way to Sales 2011 column), and take a look at the CHARTS gallery in the Quick Analysis callout. This gallery is dynamic as well – – in the background Excel is doing some light weight analysis of the data in the selection to determine some good chart recommendations to get started.

At first glance it seems that the Clustered Column chart is being recommended over and over again:

clip_image007

Looking through the live previews though shows me that these are different Clustered Column PivotChart recommendations. The indicator that a PivotChart (instead of a regular chart) is being recommended is the little PivotTable icon in the upper right corner of the chart preview:

clip_image008

Incidentally, the choices you see here are a subset of the recommendations you get when you navigate to the Insert ribbon tab and choose “Recommended Charts” (the maximum number of recommendations in the Quick Analysis gallery is 5, whereas clicking the ribbon button doesn’t limit the recommendations to 5). Also, if the recommendation engine has fewer than 5 recommended charts, then fewer recommendations will appear in the Quick Analysis Charts gallery.

Totals

To explore the Totals gallery in the Quick Analysis callout, let’s first select all the numbers for Sales 2009, 2010, and 2011 (range G4:I20). Looking at the icons in that gallery, I’m thinking that this part of the feature will put totals either at the bottom or to the right of my selection (OK, since I helped design those icons, I’ve got an unfair advantage J):

clip_image009

Live previews confirm my guess:

clip_image010

This particular gallery has more choices, with right/left scrolling arrows to see the additional options. Hovering over the 2nd “Sum” button shows a live preview of the totals placed to the right instead of at the bottom:

clip_image011

This gallery offers “Percent of Total” as well as “Running Total” at the click of a button – – which is a first for Excel.

Tables

Exploring the Tables gallery quickly shows me that this is the place to get recommendations about how to summarize my data in a PivotTable. This gallery is dynamic as well – – in the background, Excel is doing more lightweight analysis of the data in the selection to determine good recommendations to get started. Even if I don’t know what the word PivotTable means, I can see from the live previews that it’s putting subtotals into a nicely formatted layout:

clip_image012

 

Again, just like recommended charts, this is a subset of the options you get when you navigate to the Insert ribbon tab and choose “Recommended PivotTables”. Also, if the recommendation engine has fewer than 5 recommendations, then fewer recommendations will appear in this gallery. As an extreme example, if the engine doesn’t recommend any PivotTables, there won’t be any recommendations in the gallery; only the option to insert a blank PivotTable. This can happen if your data contains only unique values, column-wise, since in that case there isn’t anything to consolidate and subtotal.

I’m really excited that you don’t have to know the word “PivotTable” in order to get good summary information about your data, and you get live previews of a good number of options to choose from.

Sparklines

Finally, let’s take a quick glance at the Sparklines gallery. I’ll select the numbers again, and hover over the “Column” icon to see a live preview of where the Sparklines will be inserted, and get a better understanding of what Sparklines even means:

clip_image013

Wrap Up

One aspect of this feature that was challenging for us to dial in exactly right was how and where the Quick Analysis button would initially show up: On the one hand we want people to see it and click it. We don’t want to be too subtle. On the other hand we don’t want to annoy people with a button always appearing and following them around as they work in Excel – – that would be a distraction. We tried to keep it toned down by having the button fade/disappear as the mouse moved away from it.

What do you think:

· Did we get the balance right?

· Do you think that people will notice it?

· Did you notice it?

· If so, did it draw you in to explore the feature?

· Or is it simply distracting? (you can turn it off in Excel / Options)

Thanks everyone for reading and providing comments!

–Chad

Calculated Member and Measures in Excel 2013

Today blog post is brought to you by Alex McMains a Software Development Engineer in Test from the Office Business Intelligence team.

 

Besides many new, exciting features, Excel 2013 also offers enhancements to older features even those that may be a little more obscure to the everyday Excel user. Today we will shed some light on one of these features. The feature that I want to talk about here is the OLAP based Calculated Members and Measures.

Definition and Caveat

OLAP is an acronym for online analytical processing. Although there are technically different OLAPs, we refer specifically to Microsoft SQL Server Analysis Services data sources.

clip_image001

To be clear this excludes tabular model (e.g. PowerPivot) data sources. Please see creating DAX calculations to see how to get equivalent functionality with tabular model data sources.

What are OLAP based calculated members and measures?

The creation of OLAP based calculated members and measures was available as far back as Excel 2007. However, in order to use them effectively, it was necessary to write and maintain tedious macros. Excel 2013 provides a new drag/drop GUI to greatly simplify creation and management of all of your calculated members and measures.

Before we delve into the specifics of creating calculated members and measures, let’s briefly look at a very high level description of what they are and discuss why you might want them.

A calculated measure allows you to create your own client-side custom calculation that is not available on the server’s model. For example, the cube may provide a measure that shows a sales total for a region and another measure that shows the sales for each district in the region. Assume you want the percent of total for each district. Simply create a calculated measure using the total and individual districts measures to get the percentage and then use it in your pivot tables just as you would a regular cube measure.

A calculated member allows you to create a custom member in the cube hierarchy. For example, assume you have a hierarchy that shows the United States at one level and then each state at a level below it. Let’s assume that you are responsible for the Northwest region and this includes Oregon and Washington. In this case, you might create a calculated member called Northwest that always aggregates Oregon and Washington.

Connecting to an OLAP data source

Before we can create a calculated member or measure we need to connect to a Microsoft SQL Server Analysis Services data source. We will use the Adventure Works cube that comes with Microsoft SQL Server Analysis Services.

Let’s get started.

Step 1

Click on the Data tab in the ribbon and drop down the menu From Other Sources and select From Analysis Services.

clip_image002

STEP 2

Enter your server name then press next.

clip_image003

STEP 3

Now select the database Adventure Works DW and choose the cube Adventure Works. Then press Finish.

clip_image004

Step 4

Choose your visualization, either a PivotTable or a PivotChart and press OK.

clip_image005

OLAP Tools menu

If we now click on our pivottable or pivotchart we will see a new menu in the ribbon called PivotTable Tools. Click on the Analyze tab in this menu and we find an OLAP Tools menu. It is in this menu that we find the hooks that allows us to quickly and easily create and manage calculated members and measures.

clip_image007

Adding data to our pivot table or pivot chart

Let’s put some data on our PivotTable or PivotChart.

Step 1

In the field list on the right hand side of the worksheet, choose the measure Internet Gross Profit under the measure group Internet Sales.

clip_image008

Step 2

Scroll down to the member section to find Customer. Expand it to show Customer Geography and select it.

clip_image009

Step 3

Click on Country and then the down arrow to bring up the item selector.

clip_image010

Step 4

Ensure that only United States is selected and press OK.

clip_image011

Step 5

Expand United States on the pivot table so that it looks like this:

clip_image012

We are now ready to create custom calculations.

Creating a calculated measure

Go to the OLAP Tools menu once again under PivotTable Tools on the ribbon and click on MDX Calculated Measure…

clip_image014

Looking under the Internet Sales measures, it’s not necessarily clear what the Revenue is. Perhaps it’s Internet Sales Amount, perhaps not. We know that Profit = Revenue – Cost, and we have pretty obvious profit and cost numbers so let’s create a custom measure called Internet Revenue.

clip_image016

One thing to notice is that anything in the Fields and Items list can be dragged/dropped to the MDX box to help more easily create the calculation and prevent typos.

Once we press OK, we can find our calculation in the field list just as though the measure had been from the cube itself. The one difference is that our calculation can have a display folder name which we called Northwest Region Calculations. Go ahead and select the new measure and add it to your pivot table.

clip_image018

Congratulations! You have now created an OLAP based calculated measure. Now let’s look at creating a calculated member which is slightly more complex..

Creating a calculated member

A calculated member can be created from the same OLAP Tools menu as a calculated measure. We instead choose MDX Calculated Member… and get a slightly different dialog box. Before we create a calculated member, let’s discuss the dialog a little.

The Parent Member box in particular has a few not-so-obvious features that are worth acknowledging:

clip_image020

Now let’s create a simple calculated member.

Choose a name for your new calculated member, for example, Northwest Region. Next choose the parent member to be United States under Country which is under Customer Geography. Drag United States from the Fields and Items list to the Parent Member box.

clip_image022

Now drag Oregon to the MDX box. Type ‘+’ and then drag Washington. You should now see the following:

clip_image024

Click OK.

Congratulations! You have now created a calculated member. Verify this in your pivot table.

clip_image026

NOTE: If your Analysis Services version is older than 10.5 (2008 R2), you may not be able to deselect the calculated member from the item selector.

Managing calculated members and measures

Having a GUI to create calculated members and measures is nice. What is even nicer is having a GUI to manage and edit them. Going back to the OLAP Tools menu we find that there is another option called Manage Calculations… From this dialog we can Create, Edit, or Delete a calculated member or measure.

clip_image027

NOTE: Once a calculated member is created, neither its parent member nor its parent hierarchy can be changed. Should you wish to change these, you will need to make another calculated member and copy your information over to it.

In closing

I hope you’ve enjoyed your tour through OLAP country. I’ve tried to provide something that can be used by anyone with even a basic familiarity of Microsoft SQL Server Analysis Services. To more advanced users, I hope you were able to gather some useful information from this posting as well.

Cheers.

Alex McMains, Office BI

Intro to Power View for Excel 2013

image

 

This blog post is brought to you by Allie Rutherford a Program Manager on the Excel team.

 

 

 

Have you ever looked at a dashboard someone made and went, “man, I like that, but there’s too many steps for me to remember”? Or maybe wanted to have a way to play around with your data in a safe space so you don’t mess it up?

Power View is a new add-in for Excel 2013 that consumes the Data Model. For those who are avid readers of our blog, you will remember Diego did a post on the Data Model we’ve integrated into Excel. If you don’t remember that post (because not all of us are perfect), the gist is that there’s a new way to have lots of data in Excel and not slow it down while also having handy things like relationships (to make your handy dandy new in-workbook cube relational). Another thing to note is that the Power View add-in comes installed by default in Professional Plus versions of Office.

The point of Power View is to make it very easy to create pretty, interactive data presentations (or “reports” as the experts call them) that will make your boss go “Wow, how did you do that? You’re a genius!” Well, OK, maybe not that far but you’ll definitely look super smart. Plus, it also can be used to explore your data visually without worrying about messing anything up and even make reports on your own, without IT help.

Some of you may be quizzical and asking yourselves, “But… Doesn’t Excel already have graphs and charts and visualizations?” To which I would say, “Yes!!” and probably also add that with Power View, you get automatic cross-filtering of visualizations, new visualizations that aren’t in Excel (such as a “play” axis, maps, and cards), a free-form canvas that doesn’t have the restriction of gridlines (no more messing up alignment because you inserted a new row!), unique filtering options (e.g. sliders), and support for images. Plus, if you’re a data head, you’ll be excited to know that Power View sheets in Excel can also be hooked up to external data models (i.e. Analysis Services Tabular Models).

For this post, I’ll be stealing borrowing the workbook Diego made for his house hunt. So, to *help* Diego find a new house, I *borrowed* his already-modeled workbook and booted up Excel. Then, I inserted a Power View sheet…

Insert a Power View sheet

1. Click on the “Insert” tab

2. In the middle of the ribbon, click on the “Power View” button in the Reports section (to the right of the Charts section)

clip_image001

3. If this is the first time you have used Power View, you’ll get a dialog asking you to enable the add-in. Go ahead and hit Enable so we can get started.

clip_image002

If you hit cancel, you’ll have to go to step 1 and we’ll be here all day.

4. If you don’t have Silverlight installed, you’ll have to install it. Power View needs it to render all those pretty visualizations you’re going to make. Good thing we have this nice bar that comes up to give you a handy link:

clip_image004

5. After you’re done installing Silverlight, go ahead and hit the “Reload” button on the bar there. You should now get a blank Power View. Whoohoo! Your first Power View. I’m so proud!

Add a Title

First things first: titles.

1. On your Power View sheet, click in the title text box at the top of the sheet and type “Where to buy a house?” Titles are important, people! Don’t skip this part, it’s definitely essential to the whole shebang.

Add a map

Second things second: we are going to put the data on a map so we don’t have to stare at it in table form, scratching our heads, wondering where this “Ravenna” place is and whether it has views of Puget Sound. (hint: it doesn’t)

1. In the field list on the right side of the screen, from Table1, check the checkboxes next to LIST PRICE and IS SHORT SALE. You should now have a table on the Power View canvas. Voilà!

clip_image006

What, a drag-n-drop table isn’t cool enough for you?

2. I want to visualize this data on a map (as people like to have something to look at to see what’s what), so I click on the table, then click the “Map” button on the Design ribbon tab.

clip_image008

3. Now, I have a map but I forgot to add location information, so there’s nothing on it. Don’t fear, it’s easy to fix! Just click on “All” in the field list (look at the tippy top) and drag “GeographyID” (aka the zip codes) from the demog1 table to the “Locations” well. The map zooms in to your locations! Fun.

4. Next, check LONGITUDE and LATITUDE from Table1. They should populate into the “Longitude” and “Latitude” well.

5. Next, change the aggregation function for LIST PRICE to average (as seeing a sum for an area is really not helpful. To do this, click the arrow next to the LIST PRICE field in the “Size” well and then click on “Average” in the drop down menu that appears:

clip_image009

6. Finally, drag ADDRESS from Table1 in the field list below GeographyID in the “Details” well. This will give us a “hierarchy” of locations; this means we can double click on a bubble on the graph and it will change the view to the next field level down in the Locations well. In our case, it will go from Zip Codes (demog1’s GeographyID) to the address level for houses (Table1’s ADDRESS)

7. Now, you should have a map that can tell you there are a lot of short sales going on in certain Zipcodes. What’s up 98122? You should have something like this:

clip_image011

Remember, double clicking on any point will bring you to the address level-view for those homes. To get back to the GeographyID or Zipcode level, there’ll be an Arrow icon next to the filter one. Nifty, huh?

Add a Bubble Chart

Since I’m happy with the map, let’s move on to something else that’s just as cool: Bubble charts! (no soap required)

1. To put a new visualization on the sheet, first click on empty canvas space (anything blank on the Power View sheet).

2. In the field list, check the checkboxes next to demog1’s UnemploymentRate, MedianAge2010, TotalHouseholds2010, and GeographyID. From Table1, check Location.

(NB: you’re going to see the “Relationships may be needed” warning, but we already have the relationships we need for this example. You’re seeing that because Location isn’t in the primary table, but since GeographyID is in the primary table, it can be used by Power View to differentiate each row)

3. In the Design tab, click on the “Other Chart” button and select “Scatter”. Move LOCATION to above GeographyID from the “Color” well in the field list.

4. Change the aggregation functions for the fields in the “X Value”, “Y Value”, and “Size” wells to Average.

5. Next, drag the Beds and Baths fields from Table1 and drop them on the Filters pane (to the right of the canvas, left of the field list).

6. Move the slider for Beds to “Greater than three.”

7. Click the switch filter mode button twice to get to the Advanced Filter Mode and type in “2” to the text box and hit “apply filter.”

clip_image013

8. Now, Diego can decide from all the 3 bed, 2 bath houses in the Seattle area. Here’s the Power View sheet we end up with:

clip_image015

Looks to me like he should look for a house in Fremont, as it’s got a nice median age and a low unemployment rate, as well as no short sales! I feel so helpful now. Wait, isn’t that the same conclusion Diego came up with last time? Well, now Diego can easily justify his decision with a beautiful, interactive report.

Also, I just put together something that’s fun to click around on and explore. Try clicking on a neighborhood in the bubble chart and watch the map filter! Maybe I’ll start looking for a house now that I have such an awesome way to dig around in the data.

In this post, we did a few things: insert a Power View sheet, create a map visualization, create a bubble chart, and add filtering to the whole sheet. I made something that looks pretty complicated in way fewer steps than you’d think.

For a more detailed demo of Power View, check out Sean Boon’s Olympic series here.

For more info on using Power View, check out these articles on Power View and What’s New in Power View for Excel 2013.

Allie

Inserting Charts in Excel 2013

image

 

This blog post is brought to you by Nick Chiang a Program Manager on the Office Graphics team.

 

As Scott mentioned in his Charting Overview post, users have always struggled with picking the right chart type to represent their data. Unless you have a good understanding of the different chart types available and the types of data they work for, many users have trouble choosing the right chart type to properly represent their data, and often fallback to choosing something familiar. Even worse, sometimes users ended up choosing chart types that misrepresent their data, changing the message they’re trying to present. So as a part of the data visualization effort for Excel 2013, we focused on simplifying the process for making charts, and helping users easily and quickly make great looking charts that are appropriate for their data.

clip_image002

The improvements begin with the Insert Ribbon, and the new Recommended Charts button. This new recommendation feature presents a list of different chart types generated by an algorithm based on your selected data. The algorithm looks for different patterns in how your data’s arranged to determine a list of what the most suitable chart types are. In past versions of Excel, you would’ve had to insert the chart at this point to see what it looks like with you data plotted. In Excel 2013, we’ll instead show you a live preview of the chart already with your data in it. That way, you’ll know you’re making the right decision without having to guess what the output will look like. This feature can be found both in the new Insert Chart dialog, as well as the new Quick Analysis feature (this feature will be covered in a later blog post). Let’s take a look at a couple different examples of these recommendations, and see the new Insert Chart dialog and Quick Analysis features in action (these examples will be available in a file attached at the bottom of this post).

Example 1 – Understanding your Data

clip_image004

Here we have a fairly simple table of data containing some statistics from the past few Winter Olympics. Selecting this dataset and clicking on the Recommended Charts button will present us with the new Insert Chart dialog:

clip_image006

Looking at the dataset, the algorithm has determined that the first row looks like a set of years, and has recommended a line chart as the top recommendation (line charts are great for showing changes over time). The other chart types that get recommended provide useful alternate views of the data in other chart forms.

Example 2 – Different Ways to Map your Data

clip_image008

In this example, we have values that plot out some basic mathematical formulas. Let’s try the Quick Analysis feature this time. With your data selected, you’ll notice a new icon pop up at the lower right corner of your selection. Clicking this icon will bring up the Quick Analysis feature:

clip_image010

The chart type recommendations presented here are the same ones the show up in the Insert Chart dialog. If you hover over the chart type icons with your mouse, you’ll see a live preview of the chart with your data plotted:

clip_image012

You’ll notice here that there are actually multiple scatter charts being recommended. In this case, the algorithm is presenting multiple ways in which the data could be mapped to a scatter chart – of the four columns in the dataset, there may be two sets of x and y values (as is the case here), or there may be a single column of x values, followed by three separate sets of y values. The algorithm will present multiple different mappings so that the user may select the correct one for their dataset. From here, clicking on the chart type will insert the chart directly onto your worksheet.

Example 3 – Pivot Chart Recommendations

clip_image014

Sometimes what you want to chart isn’t necessarily the values in a dataset, but rather the aggregate (like the sum or count) of a set of values. This would be useful in situations when you want to tally up or summarize the results from some data, like a survey for example. Working with the new Pivot Recommendations feature (which will also be covered in a later blog post), our algorithm is also be able to suggest pivot charts based off of your data:

clip_image016

In this example, the first recommendation is a chart that shows a count of the number of participants that responded to each of the Pet types. The other two recommendations each show a slightly different interpretation of the user’s dataset (the second counting the number of choices by Colour, and the third by Fruit).

Pivot chart recommendations are differentiated by the pivot icon in the upper right of the thumbnails:

clip_image017

Selecting one of these suggestions will automatically insert a pivot chart and a pivot table on a new sheet in your workbook, allowing you to further explore your data.

clip_image019

For our Power Users

Despite the focus on improving the chart creation experience for novice users, we wanted to deliver these features in a way that did not obstruct the workflows of our more advanced chart users. We’ve left in the individual chart type buttons on the Ribbon, to allow quick access to all of the available chart types in Excel without having to enter the dialog.

All of the chart types are also available in the Insert Charts dialog under the All Charts tab. In the screenshot below, you’ll notice that the selected chart type is accompanied by multiple live previews of the user’s data. These options represent different ways your data may be mapped to the selected chart type. In the example below, the two mappings show the chart plotting the user’s data either by rows or by columns.

clip_image021

Final Thoughts

These examples demonstrate just a small portion of the changes we’ve made to make it easier for users to create great looking charts in Excel. One thing to note is that not every chart type available in Excel will be recommended through the algorithm. You’ll never see a 3D chart recommended, nor will you see some of our more specialized chart types, such as surface charts. As mentioned earlier, these chart types are still available through both the individual chart type button on the Insert Ribbon and on the All Charts tab in the Insert Charts dialog.

Our hope is that these new features will help make charts much more accessible and easier to create for everyone. We’re constantly looking to improve our recommendations, so we look forward to hearing your comments and feedback on this experience. Thanks!

Nick Chiang

Introducing spreadsheet controls in the new Office

clip_image002

This blog post is brought to you by Steve Kraynak, Program Manager on the Excel Services team.

 

 

We have 5 brand new Excel, Web Excel and SharePoint features to introduce to you in the new Office, all designed to help you manage the use of spreadsheets and Access databases. I’ll tell you about each of them in more detail, but the names really speak for themselves:

· Audit and Control Management Server

· Discovery and Risk Assessment

· Spreadsheet Inquire

· Spreadsheet Compare

· Database Compare

Some background – EUCs

The acronym “EUC” (End-User Computing applications) has become a common way to say “critical spreadsheets and databases created by end users without the involvement of IT.” The applications I’m introducing to you are aimed at helping organizations deal with the significant risks introduced by EUCs.

In many ways, EUCs are a very good thing. A powerful tool like Excel gives the end-user a fantastic amount of ability, agility, and efficiency. However, with great power comes the chance to make some really awful mistakes. In many situations, these EUCs are very important applications that are designed, developed, tested, accepted, and used by a single person or a small group of people. It’s the norm that EUC’s are created on the fly, without a structured development process (design, develop, test). Since we know that humans occasionally make mistakes, it’s a good bet that mistakes will make their way into the EUC’s. Actually, I think it’s a good bet that most EUC’s will have errors, and often the errors and mistakes will go unnoticed until something really bad happens.

The data produced by the EUCs is then trusted and accepted by management and other end-users, and becomes the basis for critical business decisions and reporting. Without proper controls, use of EUC’s can lead to significant financial and operational loss, through undetected errors, unexpected mistakes, and even fraud. A quick Bing search will find you many stories about mistakes in spreadsheets.

In 2011, Microsoft acquired the Prodiance Corporation, which was one of the leading providers of software for spreadsheet controls, and since then, the Excel Services team has been working hard to integrate and adapt the Prodiance technology as a major new offering for Microsoft Office 2013. Through the acquisition of Prodiance, Microsoft will deliver in our upcoming Office 2013 release a comprehensive solution to help organizations establish proper safeguards and controls over mission critical EUCs. You can find out more about the Prodiance acquisition here.

clip_image004

What’s new in Office 2013

With the five new applications mentioned above, we’re offering some much needed tools to help organizations begin to gain control of their EUCs. Now, organizations can find and assess the complexity and risk of their EUCs using Microsoft Office Discovery and Risk Assessment 2013. End-users, auditors, spreadsheet developers, and analysts now have a powerful analytical tools for spreadsheets with the introduction of Microsoft Office Spreadsheet Inquire 2013. Anyone can quickly and easily determine differences between any 2 spreadsheets with Microsoft Office Spreadsheet Compare 2013, and likewise for Access databases with Microsoft Office Database Compare 2013. And, to round out the suite of EUC control applications, organizations can monitor and track changes down to the cell level using Microsoft Office Audit and Control Management Server 2013.

How to get the new stuff

You can preview all of the new applications today by trying the Office customer preview.

Three of the new tools will be available on the desktop with Office ProPlus. This includes Spreadsheet Inquire, Spreadsheet Compare and Database Compare. Since Spreadsheet Inquire is an Excel Add-in, you just need to turn it on in your Excel options to start using it. Spreadsheet Compare and Database Compare will show up as shortcuts along with the rest of your Office tools.

Then we have Audit and Control Management Server and Discovery and Risk Assessment, which are server products available in the Office customer preview.

Where do I begin – understanding the scope of your risk.

clip_image005One of the first steps in tackling any problem is to understand the problem. If an organization hasn’t taken stock of their EUCs, and hasn’t put in place adequate procedures and controls, then chances are good that no one knows how much of a problem they have with EUCs, or even how many EUCs they have. Microsoft Office Discovery and Risk Assessment gives organizations the ability to create an inventory of their EUCs and analyze them so they can determine which EUCs are the most complex, impactful, and risky. Simply having an inventory is one important aspect of controlling EUCs in an organization. The new Discovery and Risk Assessment application also helps you determine which are the most risky, so you can start taking further steps to reduce your risks. It does this by crawling specified network paths and SharePoint sites to find the EUCs, and then it analyzes each EUC and gives them a rating for complexity, materiality (impact to the organization), and risk based on configurable criteria.

clip_image007

Finally! A comparison tool for Excel (and Access, too)

clip_image008The new desktop tools provide some great efficiency improvements. If you ever wanted an easy way to compare 2 spreadsheets, now you have it. Spreadsheet Compare lets you pick any 2 workbooks and compares them in a fraction of the time it would take to you to do it manually. Also, the differences between spreadsheets are categorized so it’s easy to focus on important changes, like changes to formulas or VBA. You can also see changes to the data, but the point is that there are different types of changes, and Spreadsheet Compare makes it easy to distinguish one from the other. Also, Spreadsheet Compare is able to determine when rows or columns have been inserted or deleted and it factors those changes into the equation before comparing the cells that may have shifted as a result. Rather than showing differences just because thousands of cells moved down by one row, Spreadsheet Compare can simply report that a row was inserted.

clip_image010

clip_image011Database Compare provides similar capability for Access databases. Now you can pick any 2 Access databases and get a report of the differences between the tables, queries, modules and more. If someone changes an important query, now you can easily see exactly what was changed.

Powerful analytical capabilities

clip_image012Spreadsheet Inquire gives you the ability to perform a detailed analysis of a workbook right in Excel. We often refer to the Workbook Analysis as an MRI for spreadsheets. The Workbook Analysis feature gives you the ability to find and report on almost 50 different aspects of your spreadsheet. For example, you can easily find all the formulas with errors, inconsistent formulas, or invisible cells, and you can create a report with all this information so you can begin to document and understand your spreadsheets better, which is a critical aspect of any good EUC control program. Another highlight of Spreadsheet Inquire is the Workbook Relationship Diagram. This feature maps out the data lineage of your workbook. If you have linked workbooks or data connections, this feature gives you a fast and important view of where all the data is coming from, whether you have broken links, and whether the data is current. On the other hand, maybe you’re just developing a spreadsheet. In that case you can use these same tools to help you build a better spreadsheet from the start.

clip_image014

Keep track of changes in your EUCs


The workhorse of all these new products is our Audit and Control Management (ACM) Server, which provides powerful change management features for Excel and Access files, and it is complemented by Microsoft Discovery and Risk Assessment, mentioned earlier. ACM Server will keep a close eye on your critical EUCs and gives you the ability to see who has made changes to your EUCs, and when, as well as showing you exactly what the change was, down to the cell level. You can see if someone has modified a formula or VBA code, and ACM Server will show you the old value and the new value after the change. ACM Server also keeps a version history for your files, so you can easily compare any 2 versions, or even restore a prior version. There are lots of other features, too, like the email alerts for important changes to your EUCs. To try ACM Server, please visit this information page.

clip_image016

I’m really excited about the upcoming release of these products, and we as a team are looking forward to making an impact in the world of EUCs. We would love to hear your feedback about these new features. Please reply below and let us hear you! Are these features hitting the mark? What do you think, what do you love, what’s not so great? What else would help?

Charting Overview for the new Microsoft Office

image

 

This post is brought to you by Scott Ruble a Lead Principal Program Manager on the Excel team.

 

 

With the new Microsoft Office release, the Office DataViz team is proud to deliver a rich set of charting capabilities across Excel, Word, PowerPoint, and Project. In fact, because there are so many features, this article will only be an overview. Subsequent articles will go into the specific use cases and steps for each of them.

I’ve been working on Excel charting since the Office 2007 release, when we replaced the charting engine, changed the rendering layer, and enabled charts to run on the server. This was a pretty ambitious release, which set a whole new direction for data visualizations in Office. Since then, I’ve written several blog articles on Excel charting, and many of the reader comments have centered around when we were going to provide feature x, y, or z. I always appreciated these comments because it showed that there was a very passionate community of users who really cared about the charting capabilities in Office.

At the start of the new Microsoft Office product cycle, we spent a lot of time reflecting on all of the great customer feedback that we received over the years. We also visited many customers to better understand their work habits, true pain points, and unmet needs. The simple conclusion we reached was it’s actually pretty challenging for most users to create and customize charts even with basic datasets. As such, the thrust of the Office 2013 release was to significantly lower the bar for creating and customizing charts. So with that, I’d like to take you on a tour of the cool new charting features in Office 2013. The examples here are based on Excel but most of these features are also available in Word, PowerPoint, and Project.

Chart Recommendations

Customers have always struggled with picking the right chart type to represent their data. Typically, they would just use something familiar regardless of whether it was the best one or not. To pick the best chart type, there are actually a fair number of factors that need to be considered based on the data. We have simplified this entire process with one click of the “Chart Recommendations” button in the Insert ribbon. With chart clip_image002recommendations, we analyze the selected data and, based on a series of heuristics (fancy word for rules), we display a list of chart types appropriate for your data. Also, I will state for the record that you will never see a 3D chart recommended. As cool as these may look, they don’t allow a very accurate interpretation of the data. These 3D chart types are still available but you’ll need to dig for them a bit (location: Insert Chart Dialog > All Charts tab).

We go a step further and show a live preview of how your data looks in each of the chart types. We also assess whether the data has repeating values, such as categories of sales transactions. In these cases, a PivotChart (with the appropriate grouping applied) will be recommended instead of a regular chart. Through this approach we hope to eliminate a lot of the trial and error typically associated with the first step of creating a chart. Oh, and there is one last feature that we snuck in, which I’m sure will get rave reviews: a single series chart will no longer get inserted with a legend!

Combo Charts

clip_image004While in the Insert Chart dialog, you’ll notice that there is an “All Charts” tab. As the name implies, this provides access to all of the chart types – and, again, we show live previews of the data. One of the most common customer questions involves how to create a combo chart (e.g., column & line chart). In the “All Charts” tab we now have a “Combo” category that allows you to easily construct whatever combination of chart types you desire. We also make is super easy to move one of the chart types to the secondary axis which in previous versions would have required an entire blog article by itself to explain how to do that.

 

Chart Polishing

After a chart has been created, there is typically some polish needed. On the right of a selected chart, a series of buttons will be displayed. These buttons provide quick access to common operations. The first button (Chart Elements) allows various chart elements, like a title, to be quickly added or removed. Hovering over one of the items shows a live preview on the chart. So, for example, if you don’t know what a data table is, just mouse over it, and see what it looks like. Also, animations smoothly illustrate the transition for any changes made to the chart, such as removing a legend or changing a data point value. The second button (Chart Styles) provides a series of professionally-designed styles. The styles cover a wide range of appearances. Some are minimalistic while others are more intense. You can also select different color schemes to explore various looks. The last button (Chart Filters), allows you to easily add or remove categories and series from the chart. Sometimes extraneous data accidentally gets included in a chart. The Chart Filters allows you with the click of a button to remove those unwanted values, such as a Grand Total, in the example below.

clip_image008
clip_image009

Formatting Task Pane

clip_image011We’ve also tried to make fine-grain adjustments more fluid. When editing the properties of a chart element, such as a series (double click or ctrl+1), the properties of this element are now surfaced in a task pane. Changing any of the properties shows the result on the chart so you can instantaneously see the affect. You can also quickly jump to different chart elements via the element selector at the top of the task pane. This comes in handy for small items that are otherwise hard to select.

 

Data Labels

For many releases, customers have been asking for richer ways to label their charts. In Office 2013, we’ve pulled out all the stops. Not only can you change the look of a label to something more interesting like a bubble, but you can also include free-form text and bind values in the label to a specific cell. This is a great way to more fully describe a data point. Also, we have one more killer feature up our sleeve. You can now label XY Scatter charts! Stay tuned for a future article that will go into the details
of this.

 clip_image013

Server Charts

Rendering charts in a workbook on the server has been around for many releases. However, the fidelity between the client rendering and server rendering wasn’t that great. The server was not able to render a number of formatting options such as shadows, bevels, and a variety of other effects. In Office 2013, the server rendering has been significantly improved for Excel Services and the Web Apps for Excel, Word, and PowerPoint. Now charts on the client with effects look identical on the server.

 

 

Data Grid in Word & PowerPoint

In prior versions, the chart data grid in Word and PowerPoint was pretty intrusive. It took up a large part of the screen real estate and laid out the windows in undesirable ways. All of that has been significantly improved in Office 2013. Now the data grid is displayed in a streamlined small input window that floats above the chart. Also convenient controls for undo/redo and opening in Excel are
easily accessible.

clip_image015

As I hope you can see, the charting investments in Office 2013 are pretty significant. We think these improvements will go a long way in making it easier to create and customize charts – ultimately improving productivity. Please let us know if you have any additional feedback.

Scott

Introduction to the Data Model and Relationships in Excel 2013

 

This blog post is brought to you by Diego Oppenheimer a Program Manager on the Excel team.

 

I am very happy to be writing this blog post today. Not just because I will be showing you another way Excel can make your data analysis easier but also because I will be introducing the new Data Model and Relationships features that will hopefully change the way you use Excel for data analysis forever.

For those of you who are not familiar with the power and usefulness of Pivot Tables you might want to check out this article (Overview of PivotTable and PivotChart reports) or this training (PivotTable I: Get started with PivotTable reports) . Some of these articles are a bit old but the principles and functionality still apply .

Ok, here we go…

Finding a home

Around this time last year my wife and I were considering purchasing a house in the Seattle area, even if it meant dealing with some of the worst traffic in the US. So like any self-respecting Excel nerd I started a spreadsheet with a table of data that fit our parameters. This data was easy to find on the many real-estate sites out there like Zillow.com or Redfin.com. One thing I noticed though was that none of the these sites by themselves had all the relevant data I wanted to make an informed decision and this is where the Data Model came into play by allowing me to combine data from multiple sources and perform a richer analysis.

You can see what I started with below or just download the workbook for yourself.

clip_image002

My first look at the Data Model

If you open the file above you will see I have a table with a lot of data. The first thing I am going to do is create a PivotTable so that I can sift through it easily. Under the INSERT tab, hit PivotTable and the 
following dialog should pop-up:clip_image004

I have highlighted a new option in the create PivotTable dialog which is to “Add this data to the Data Model”. So what is this Data Model I speak of?

A Data Model is a new approach for integrating data from multiple tables, effectively building a relational data source inside the Excel workbook. Within Excel, Data Models are used transparently, providing data used in PivotTables, PivotCharts, and Power View reports“. Read more here…

In other words, the new Data Model allows for building a “model” where data from a lot of different sources can be combined by creating “relationships” between the data sources. For those of you with some database knowledge this is similar to creating joins between tables, except all the tables live in Excel.

I chose to add this data to my Data Model because I am going to be combining it with data I will get from other sources to make my analysis more complete.

Create the PivotTable

The first thing I want to do is look at the number of houses I have selected by zip code. To do this I drag the ZIP field to ROWS and the LISTING ID field to VALUES. By default, this will give me a SUM of LISTING ID’s, but we want a COUNT. To do this, right click on the header that says SUM of LISTING ID -> Value Field Settings… and change to COUNT. As you can see, I only have to sift through 165 house listings now (L).

I have decided to add a couple more fields to my PivotTable to help with my analysis. I added LIST PRICE, DAYS ON MARKET and SQFT and changed the Value Field Settings to AVERAGE.

At this time my PivotTable looked something like this:

clip_image006

And my Field List like this:clip_image007

So now I have a layout that shows me the number of houses that met my criteria per Zip code and some extra data like Average Price, Square footage and Days on Market (my realtor says this comes in real handy when negotiating).

What do I know about the Zip codes?

One thing I noticed about all of the real-estate listing sites is that they give you a ton of detail about the listing but don’t really tell you much about the neighborhood. I want to better understand the demographics of the Zip codes I have selected.

Good thing there is a marketplace for just this type of inquiry (http://datamarket.azure.com). This place is great, and you can read all about it here. Make sure you have a Microsoft/Live-ID or sign up for one free.

Type “Demographics” in the search box and find a data set called “2010 Key US Demographics by ZIP Code, Place and County (Trial)”. If I click on that link to the data set, the first option is to “Explore Data Set” and that’s exactly what I want to do.

Next step is to narrow down the data set to only what is relevant, so I make GeographyType: Zip code and StateAbbreviation “WA” and hit “RUN QUERY”. Great, I think I have now what I need.

clip_image009
To get this into Excel you’re going to need two things from this website, so copy these somewhere:

(1) The URL for the current expressed query

(2) Your private account key

Now that I have all of this figured out, I can easily add what I found to the Data Model. In Excel, go to the DATA tab and select “From Other Sources”, “From Windows Azure Marketplace”.

Fill out the information with what you have saved from the website:

clip_image010

clip_image011
Select that table.

Hit “Finish” and then select “Only Create Connection”:

clip_image012

Note: Some of you might be wondering why I chose “Only Create Connection”. I chose to do so, so that the data was never brought onto the Excel sheet, but directly into the Data Model. I only plan on using it in combination with my original table so there was no need to bring it in as a table onto my sheet or create a new PivotTable or PivotChart. If you’re wondering what that PowerView Report does check out this post by Sean Boon.

Combining my Data

If I go back to my original PivotTable, specifically back to the field list, you might have noticed a small change, particularly around the top.clip_image013

You can see a tab called “Active” (which is selected) and a tab called “All”. The field list is the best way to explore our newly created Data Model, and the “All” tab lets us do just that. Any connection or table that was added to the Data Model will show up in this tab. Let’s take a look.

clip_image014

We can see that our original Table (Table 1) and the Table we brought in from the Data Market are both there. Great!

Now for some magic. I want to know a bit more about each of my Zip Codes, so what I am going to do is add those fields from the demog1 table to my PivotTable. The first one I’ll add is MedianAge2010 (we want to live around people our age so we have a better chance of meeting neighbors we have something in common with).

There are two things to notice here. First, the median age is the same for every zip code, which gives me a hint that something is wrong:

clip_image016

And second, a little message popped up in the field list.

clip_image017

Excel is telling me that a relationship between the two tables might be needed, so I go ahead and create it.

clip_image019

A couple of things to notice when working with relationships:

· Both columns chosen need to contain the same type of data (Zip Code in this case).

· They only work when one of the table’s columns contains unique values.

· The Related Column (Primary) should always be the one containing the unique (no duplicates) values.

· In a PivotTable, to be able to put something from the Related Table on Values you will have to have a field from the Related Table on Rows or Columns.

To satisfy this last condition, I remove ZIP and add GeographyId to Rows. Now my PivotTable looks something like this:

clip_image021

The reason for all those ugly blank spaces is because I have no houses that match my criteria from Table1 for every single Zip Code from the Marketplace data. I can easily filter these out by clicking the downward arrow next to Row Labels, Value Filters, Does Not Equal

clip_image022

Great, now I only have the Zip Codes I care about. I went ahead and added some more fields, conditional formatting and some sorting to figure out what the best zip code for us is and ended up with this:

image

Seems like 98103 is a really good candidate, falling in the middle in price and Average SQFT but towards the bottom in both Unemployment and % Vacant units. I can go ahead and add a COUNT of ListingIDs and see that I have 8 houses to go check out in this neighborhood that meet my criteria.

More Details on the Data Model

In this last example, I really am only scratching the surface of what the Data Model can do and I plan on showing much more in future blog posts. Here are some quick facts though:

  • Any given workbook will only have one Data Model.
  • Any table in Excel can be added to the Data Model.
  • Almost all Data Sources can be added to the Data Model (SQL, Odata, Atom feeds, Excel tables and more).
  • Tables in the Data Model have no limit in terms of rows.
  • Relationships can be defined across multiple tables.

I hope this is enough information to make you dangerous with the new Data Model capabilities. Feel free to leave your questions and comments below or message me on Twitter @doppenhe.

Flash Fill

This post is brought to you by Chad Rothschiller a Program Manager on the Excel team. To try out this new feature download the Office Preview here.

Before coming to Microsoft I spent a good deal of time messing around with data. I would extract first names, last names, and email addresses from chunks of text. I would join first names and last names together into a single column of text. Sometimes I only needed first name initials, put together with the last name. I had to change the format & display of dates, social security numbers, and dollar amounts. Because the data came from a database, I often had to make sure the names had Proper Casing (e.g. not ALL CAPS, not all lower).

Today I’m proud to be writing about a new feature in Excel 2013 called Flash Fill that makes all of those tasks very simple for even the most novice user. Several years ago, I wrote a blog post demonstrating how to do string manipulations like the above examples. The problem is that the solutions relied heavily on using formulas, which can feel quite complex and intimidating for people not as familiar with Excel. Now, with Flash Fill, you can do it all without formulas. In fact, it’s easier than that. Now you can do it all by providing a few examples.

Here’s what Flash Fill looks like in action:

clip_image001

Let’s work through the examples in my original post, but using Flash Fill as the solution. I updated a few of the values from the original data set to make things more interesting, you can get it here. After pasting the data into Excel, you’ll need to split it out into separate columns. I used Text to Columns feature, using “comma” as the separator.

clip_image002
Note: I used bold formatting on the top row so they stand out. But it also helps Excel to understand that the top row is a header row so that Flash Fill won’t use it as part of your examples when generating pattern rules.

Step 1: Format social security numbers like this: 123-45-6789

Insert a new column between SSN and Last Name and title it “SSN”. Notice below that as you start typing the formatted SSN, Excel will help you complete the column based on the value you entered in the prior row.

  1. Make sure the inserted column is wide enough to display the formatted value (80 pixels should be enough; this isn’t required to use Flash Fill, but it looks better that way)
  2. Type the first social security number in B2, with hyphens: 413-66-4341
  3. Start typing the second SSN value: 2

At this point Flash Fill recognizes what you’re doing and makes additional suggestions:

clip_image003

If you like the suggestions (in this case, I do!), just hit ENTER to accept them.

Now, if you’re paranoid like me, then at step 2. You won’t trust yourself to re-type the SSN correctly. Instead you can double click in A2 (goes into “cell edit mode”), copy the value, then double click cell B2 (back into “edit mode”), and paste the value. Then, before hitting ENTER to commit the value, put the cursor between the 3rd and 4th digit, and type the hyphen. Ditto for between the 5th and 6th character. Now hit enter, and continue to step 3.

Step 2: Format names like this: Rothschiller, Chad B. (in other words: <Last Name>, <First Name> <Middle Initial>.)

Insert another column between First Name and DOB, call it Name.

  1. Make sure the new Name column is wide enough for everyone’s names (162 pixels should be enough; again, not required, just looks nice)
  2. Enter into cell F2: Freehafer, Nancy D.
  3. Type the “C” for Cencini, and see Excel Flash Fill make suggestions for the rest of the dataclip_image004 
  4. If you like the results (not perfect, but I’ll take them), hit ENTER to accept. clip_image005
     
    In this case, Flash Fill wasn’t sure what to do for rows without a middle name or initial, because the only example I provided used a middle initial.
    By providing additional examples, called revisions, I can teach Flash Fill what I want for those cases that it got wrong.
  5. Go down to cell F5 (first blank cell)
  6. Type: Sergienko, Mariya, then hit ENTER
    Notice that while editing that cell, the Flash Fill visuals come back for the Flash Filled range: clip_image006

   

After hitting enter, Flash Fill adds my new example, creates a new set of rules, and re-applies the new set of rules to the entire range (excluding the values you typed), which includes the other row without a middle name / initial (Guissani, Laura, 2nd from the bottom):clip_image007 

Notice that all the extra spaces in the original name values were removed. Flash Fill ignored the extra whitespace and cleaned up my results.

Step 3: Convert DOB from 19610222 to 2/22/1961 (more generally, from YYYYMMDD to M/D/YYYY)

Let’s convert the DOB column to date format so it’s more readable and so Excel can understand the values as dates. Insert a new column called DOB.

  1. Make sure the column is wide enough so you can see what you’re doing (75 pixels would work).
  2. Select the entire range where the new dates will go, and format them as mm/dd/yyyy (Format Cells / Number / Custom, then enter “mm/dd/yyyy” into the Type: text box).clip_image008
  3. Type the first 3 dates.clip_image009
  4. Notice that Flash Fill hasn’t triggered. We decided to disable automatic Flash Fill for numeric data. After all, there are only 10 unique characters in our numbering system, and it’s just too easy to come up with random patterns that don’t make good sense. For this case, providing additional examples and using the ribbon button is the way to go.
  5. On the Data tab, click the Flash Fill button:clip_image010

…and the results:clip_image011

Now that we have converted to dates, we can change the date format to whatever we like, say m/d/yyyy, by using the Number Format dialog box again.clip_image012

You can use the same steps to convert Date of Letter, so I’ll skip that step.

Notice that there were a few extra steps when dealing with dates (and numbers in general). The algorithm engine we’re using looks at everything as text. So it doesn’t know any special rules that go along with numbers (for example, it’s OK to drop leading zeros when dealing with numbers, but not so much when you’re dealing with Postal Codes or other IDs). If I hadn’t initially formatted the new DOB column with two digits for month and two digits for day, then the algorithm would see me converting “02” to “2” (say, “February”), and figure that I only want the 2nd digit of the month number. But then it would also convert “12” to “2”, because it doesn’t know that there’s a special number rule that says it’s OK to drop leading zeros, but not other leading digits. Looking to the future, this is definitely an area where we can push the feature forward and get better results faster.

Step 4: Format phone numbers like (206) 555-1212

Insert a new column after Home Phone, and call it Phone (I made it 96 pixels so I could see the suggestions).

  1. Start typing! (206) 695-9457
  2. When you start typing the “(“ for the second row, the suggestions kick in (Note: while we’re working with numbers here, since it’s mixed with text, Excel treats them as text, not numbers, so you will automatically get suggestions). clip_image013
  3. Hit enter to accept, and you’re done!

Step 5: Put the leading zero back on Postal Codes (like 01001)

This is going to be very similar to the date step. Once you’ve got your target column set up (I called mine Zip), select the whole range, and format it as TEXT. We do this so that Excel doesn’t treat the number as a number and re-drop the leading zero.

1. Format the Zip range as TEXT.clip_image014

2. Start typing Postal Codes, and see the suggestion UI.clip_image015

I do see some of the suggestions are a bit off, but I’m going to take it anyway, and then go revise the ones it didn’t get right. Flash Fill is probably thinking that instead of a “hard coded” leading zero, I want to extract that digit from somewhere else in my data.

3. After accepting, I go to the one that needs to be revised (in my case, it’s in cell S5), and type the desired value. clip_image016

4. …and hit enter. clip_image017

Notice that with that single revision, the other values were updated as well.

What about the other columns in your source data?

I didn’t give detailed steps for these because getting the desired results is very straight forward, once you know how to trigger Flash Fill.

  • E-Mail Address values can be converted to lower case. Just insert a new column and start typing lower-case versions of the values and Flash Fill will suggest to do the rest of them for you.
  • Extra spaces can be removed from Address. Just start typing the values without the spaces in a new column and let Flash Fill do the rest.
  • State values can be made UPPER CASE by typing a couple UPPER CASE values in a new column.

Some final tips on using Flash Fill

You might have noticed some patterns to how I was working with my data. Here are a couple things you should know to get the most out of Flash Fill.

  • The Flash Fill algorithm for recognizing patterns doesn’t treat numbers or digits any differently than text.
    • It doesn’t know that zeros to the left of the left-most non-zero digit are insignificant and can be dropped (or shouldn’t be dropped, if it’s a Postal Code).
    • It doesn’t know that 1 might correspond to “January”, 2 to “February”, and so on.
    • Having said the above, Flash Fill does a good job with numbers and dates, though in some cases you might have to think more explicitly about how to get the desired results.
  • Flash Fill does better when the relationship between the source data and the example output you provide is clear, not ambiguous. For example, if your source data looks like this:

clip_image018

Then it would be better to provide an example suffix value for the 2nd or 3rd row of data (“EF” or “YZ”, respectively), rather than the first (“AB”). The reason is because it’s unclear whether the “AB” value would come from before or after the hyphen in the source data.

  • To get automatic suggestions, you have to be editing right next to related data.
    • You can’t have one or more blank columns between the cell you’re editing and the data its related to.
    • If you’re in doubt, just select the cell, and hit CTRL + A (select the contiguous range). If the resulting selection is the whole sheet, then Excel doesn’t think the cell is next to any data. If the select is some set of data that’s related, then your cell is in the right spot.
  • To get automatic suggestions, you have to make two sequential edits, one right on top of the other. “Two sequential edits” means that you don’t do something else between edit #1 and edit #2 (e.g. switch to another sheet) .
  • If your table of data has headers at the top, make sure Excel recognizes those as headers, so that it excludes them when generating Flash Fill rules.
    • The way I like to see whether Excel thinks I have headers is to put the active cell in the data range and hit CTRL + T (create Table). The dialog that comes up has a checkbox saying “My table has headers”. If that box is checked, then Excel’s automatic header detection logic thinks there are headers. Otherwise, Excel doesn’t think you have headers.
    • The easiest way to “tell” Excel you have headers is to change your range into a Table (just hit OK on the dialog after hitting CTRL + T).

Thanks everyone for reading and providing comments. If you come across a pattern that doesn’t work well with Flash Fill, please let us know. We’re always looking for ways to improve, and your feedback is valued and appreciated!

Office Business Intelligence – The way people experience data

This post is brought to you by Steve Tullis Group Program Manager of the Office Business Intelligence team.

This is the third introductory post from the Excel family – Office BI. If you have not read Jane’s post about the Excel client, or Dan’s post about the browser-based versions of Excel, I encourage you to do so, as they are great overviews and provide foundational information about how we decided in what to invest, and how those investments are manifest in the Excel products. In this post, I will share the Business Intelligence specific story which is designed to provide an end-to-end experience that both empowers end users to have a great conversations with their data, and enables IT to manage the risk inherent with empowered end users.

At the beginning of this product cycle, we started with a fairly simple statement – Empower you to gain insights from your data with proven and familiar Microsoft products that make it easier to work smarter and faster – and feel we’ve not only met the intent of that statement, but, in doing so, have changed the end user BI game. Let me explain . . .

Easy On-Ramp

imageOften, the first step in analyzing and exploring data is the hardest: I have data . . . how do I connect to it? Explore it? Shape it? Format it? Create visualizations based on it? Great questions! The answer is Excel 2013 which has what Jane called in her blog post “More Smarts Built In” – Excel understands your data, and your context, and helps you improve your productivity by:

· Recognizing how you are shaping your data, and suggesting how to finish more quickly with Flash Fill

· Enabling you to quickly and easily preview and apply conditional formatting, suggest and create charts, PivotTables, and tables using Quick Analysis

· Introducing a new way for you to easily navigate multidimensional and tabular data models, and create Trend charts to analyze information over time – Quick Explore.

Essentially, we have removing much of the start-up friction you may have experienced in the past. A few keystrokes and clicks of the mouse to select the best recommended options – and you are well on the way to gaining great insights from your data. And you don’t have to be a professional analyst to get these results.

Integrated and Powerful

I’d like to make a bold statement: In Excel 2013 you now have the only self-service end user BI tool you need. Building on Excel as the foundation, we started by introducing new analysis capabilities such as the Timeline Slicer, calculated OLAP fields, Quick Explore, then extending existing capabilities such allowing Slicers directly on Excel tables.image Not satisfied with that, we integrated the xVelocity in-memory engine directly into Excel – which means the ability to create and manage larger, more complex data models, as well as new features such as PivotTables across multiple Excel tables. Still not satisfied, we integrated the PowerPivot add-in to enable more advanced users to directly manage the in-memory data model. Wait . . . there’s more . . . we then integrated Power View which allows you to create compelling data visualizations designed to be highly interactive– essentially, a new mechanism for beautiful, immersive, ad hoc analysis and reporting, directly in Excel. Together, this means that when you install Excel, it is likely the only self-service end user BI tool you need.

Available Everywhere

And, here’s another bold statement: All data is an Excel experience. You want your data, analysis and exploration when and where you need it. Excel 2013 investments support that:

· End user tools beyond the desktop. We continue our BI related investments in browser-based Excel adding support for query tables, the field list / well to allow re-pivoting of your PivotTable and PivotCharts, etc. We also made sure new investments, such as timeline slicers, xVelocity integration, and Power View integration are also supported via the browser.

· Sharing your analysis and insights. 2013 brings the next release of PerformancePoint Services, further enabling your corporate dashboard and score-card needs. Additionally, enhancements in Excel services allow easier embedding, so you can have your data in its most effective context.

· Easily consume other data. Excel Interactive View, described in Dan’s post, provides a one-click solution which automatically presents data in an interactive view complete with custom filters and charts, bringing the power of Excel to the Web. Learn more on ExcelMashup.com.

These investments take the power of Excel beyond the desktop – viewing, editing, analyzing, exploring, embedding, sharing and consuming enabled for the top browsers and devices on the market.

…With Compliance and Control

For end users, information workers, and BI professionals, this blog post has described amazing new capabilities and opportunities . . . for the IT professional, it has likely raised your stress level: you can just imagine a proliferation of spreadsheets containing corporate data in massive models; essentially less control over data and where and how users are leveraging that data.

image

This section is for you – the IT professional or the corporate compliance manager. We have introduced a new suite of capabilities:

· SpreadSheet Compare, which allows you to compare any two versions of a spreadsheet to see what has changed.

· The Inquire add-in which helps organizations automate inventory, risk management, analysis, auditing, and remediation for critical spreadsheets.

· The Audit and Control Management (ACM) server which discovers, analyzes, and manages critical spreadsheets and ships as part of SharePoint Server

These capabilities are designed to help end users, risk and compliance managers, and IT professionals manage your risk and compliance needs.

Little Need to Ever Leave Excel

Wow. Seems like a lot. And, the info above is just the introduction – future blog posts will provide additional details and examples. But, ultimately, have we changed the end user BI game? I think so. Try it, and I think you’ll agree.

Introducing Excel 2013

This blog post is brought to you by Jane Liles Group Program Manager for the Excel team. With this post she kicks off a brand new blog series introducing all the features we have added across Excel for the release of Office 2013.

Greetings from the Excel team hallway…

By now you’ve hopefully tuned into our Office Next blog, which provides all-up view of our latest release for Office, and seen some articles on the web. Today I have the privilege of sharing a high-level view of Excel 2013, a release that arrives on the heels of Excel’s 25th anniversary. The team has been working hard to deliver the next version, and we’re excited to be able to share Excel 2013 Preview with you and hear your feedback.

Excel is a powerful spreadsheet and data analysis application, with hundreds of capabilities that can help you organize and make sense of the data and numbers in your life. We’ve made several investments in Excel 2013 to empower our users by bringing these and more capabilities to you in ways that are easy, intuitive, and enjoyable. But before I tell you about where we focused our efforts, I’d like to share a little about how we got there.

It Begins with Our Customers…

When we began planning for Excel 2013, we started by doing a ton of customer research. Yes, we do this every release, but this time our goal was really to cast the net wide and get a sense of the full spectrum of users, from the spreadsheet dabblers to the analysts and “super-crazy power user” (you probably know a few of these Excel gurus). We visited small businesses, home offices, “consumers”, and large organizations, and observed Excel users in action. We conducted surveys and focus groups, mined the blogosphere, interviewed people 1-on-1, and consulted our MVPs, friends and families. And we listened to feedback from past releases. Then we took some time to wallow in the resulting data and do some analysis (go figure — we’re the Excel team, we like to analyze.). The insights we gained from this customer research played a major role in our decisions about where to invest.

We proceeded into development with 4 major areas of investment, with one consistent theme across all of them: empowering our users. As I mentioned at the beginning of this post, Excel is a powerful application – and many of our users have become experts at harnessing that power and turning that into results: lists, spreadsheets, reports, business solutions, in-depth analyses, dashboards, etc.

Enjoyable Experience, With More Smarts Built in

clip_image004One of the things we learned through our customer research was that while many people know that Excel has a wealth of capabilities, they can sometimes feel intimidated by all of the options available and aren’t sure how to use them. So this time we made it a priority to provide a cleaner experience that puts the focus on your content and offers features that are appropriate to what you’re doing — you’ll see this in both the Excel desktop client (“traditional Excel”) and the Excel Web App. We also provided some additional guidance to show you what’s possible, and to help you make your data do and look like what you want, more quickly, with less hunting around and trial-and error. Features like Quick Analysis, Flash Fill, Chart Recommendations, and PivotTable Recommendations are examples of this type of guidance. Do you have trouble getting charts to look right? We’ve made it easier to create the right chart, and customize it with a couple of clicks. We’ll go into more details about all of these in future posts. Do you use multiple monitors and wish you could have an Excel workbook open on each of them? Well, with Excel 2013 you finally can!

Powerful, Integrated Business Intelligence Capabilities

clip_image006For Excel 2013 we decided to make major investments in BI, and in particular, we had a goal to make Excel a full-fledged self-service BI tool. If you have already discovered PowerPivot for Excel 2010 and SharePoint 2010, you’ll be happy to see that we’ve integrated the same tabular data model natively into Excel 2013! We’ve also included new analysis capabilities like Quick Explore, Timeline slicers, PowerPivot and Power View – yes, PowerPivot and Power View now come with Excel and integrate with the Excel experience – in the client and in the Web App via SharePoint and Excel Services. Wow! For Enterprise-scale BI, the convenience of self-service in Excel on the front end is backed by the collaboration, scalability, reliability, and compliance/control of SharePoint with Excel Services and SQL Server 2012.

There is so much to tell about BI and Excel 2013…consider this a teaser.

Excel, Everywhere You Need It to Be

clip_image008It’s important to our customers to be able to get to their Excel spreadsheets from wherever they are, on whatever device they’re using. Whether it’s from their PC at work, their laptop at home, a Mac, a tablet while on the go, or their phone, Excel users need to get to their data. For 2013 we invested in that Excel Everywhere experience by increasing our investment in Excel on tablet devices and in the browser, aiming to help users getting the most out of our SkyDrive and O365 services with enhancements to the Excel Web App and embedding capabilities, as well as the sharing and touch input experiences. More on “Excel Everywhere” in our next post…

New Office Application Model

clip_image010Do you build solutions for Excel? If so, you probably know that the world of application and solutions development has evolved significantly towards web services and live content over the past couple of years. In this release you will be able to extend Excel to include web services content by creating and embedding the new Office Apps right into Excel using simple JavaScript and HTML5– and they will work in the client and in the Web App!

clip_image012

We’re eager to share everything Excel 2013 with you in more detail, and we promise that we will. Stay tuned for more posts about Excel 2013, and please try it out for yourself by going to the Office 2013 Customer Preview site!