Browse by Tags

Today's author, Chris Rae, a Program Manager on the Excel team, talks about using Perl code to read Excel's new file format. The OOXML Format As most readers will know, we spent a lot of time during the development of Office 2007 in creating and documenting a new XML-based file format (Office Open XML) to replace the much more complex binary formats the various Office applications have used for the last twenty years. A lot of time. Many of us dream in XML now. OOXML encompasses an XML file...

Today's author, Dan Parish, continues on his discussion of building an Excel Services solution. In Part 3b I walked through creating a User Defined Function (UDF) that pulls information from a SharePoint List into Excel Services. In today's post, I will describe how to create the two web part pages that make up the actual application: the page that end user's see, and the page that the admin uses. Overview Now that we have our spreadsheet running on the server and displaying data entered...

Today's author, Dan Parish, continues his discussion on building an Excel Services solution. Part 3 turned out to be a bit longer than we had anticipated so we've split it up into two parts: 3a and 3b. Thus far in this series, I have given an overview of the solution , and detailed how to build the Excel workbook that is the core of how it works. In today's post, I will walk through building the SharePoint List that users will input their machine information into. Overview In order to...

Today's author: David Gainer, a Program Manager on the Excel team. PivotTables are designed to help users make sense of large amounts of data by providing an easy way to build a summarized report. In addition, PivotTables can be rearranged easily, so that once you have some summary data in a PivotTable, you can look at the same information in many different ways with only a few mouse clicks. For a slightly less brief overview of PivotTables as well as a list of the improvements made to the application...

Today's author, Gabhan Berry, a Program Manager on the Excel team. A copy of the spreadsheet discussed in this post can be found as an attachment at the bottom of this post. Introduction When faced with a table of data, what kind of techniques do we employ to extract information from it? Maybe the first thing we do is create summaries. We might total up the columns or we might chart the data. Perhaps, we might even create a PivotTable and drill down into subtotals. All in all, summarizing data is useful. It's easy to understand and easy to do. Let's say that we're running a store and we have a small dataset of the sales we made in the first quarter. Using Excel we can easily create summary reports telling us how many products we sold to different types of people; how many to men and how many to women; how many to people over 40 and how many to people under 40. All these totals are useful. We can compare them and search for interesting differences. We might notice that we sold more products to men than we did to women or that managers bought more of our products than other professions. But can we get more information than this? Did managers buy an unusually high number of products compared to other professions? Is this number so high that it might indicate a bias of some kind? The answers to these types of questions enable us to make better business decisions. Perhaps if we know that our products are significantly more attractive to managers we might change our advertising policies to target managers more than other professions. You may think that we need data warehouses and data mining tools to answer these types of questions. But that's not necessarily true. Data warehousing and data mining are excellent tools, but so is Excel and Excel contains some sophisticated statistical functions which we can use to help us answer these types of questions. Understanding the Basics First of all, let's take a look at our data set. Our data contains customer information which we have gathered over the last quarter of trading. Each time we made a sale we recorded certain information about the customer. For instance, we recorded their Marital Status, their Gender, their Education level and their Occupation (amongst other fields). We have 1,000 records in our data set. Now let's say that we are interested in analyzing the Occupation field. It would be useful to know whether or not certain professions tend to buy more of our products than other professions. This would be a key piece of business intelligence for us. But how do we decide this? First of all, let's build some summaries. Let's count how many sales we made to each profession and each profession's percentage of the overall sales and display these numbers in a table. We'll also insert a pie chart to give some simple, visual indication of the proportions sold to each profession. With this done, let's now ask ourselves the question again: Do certain professions buy more of our products than other professions? We can see that Professional and Skilled Manual occurred more frequently in the data than the other professions and that Manual occurred the least. But is this enough information to enable us to generalize and state that Professional and Skilled Manual buy more of our products in general than the other professions? Well, not really. Why? Because before we can state anything about these totals we need to have some benchmark to compare them to. We need to have some way of calculating expected numbers for each profession. If a profession had more sales than the expected number then that takes us one step closer to being able to state that there is an unexpected difference between the amounts of products sold to each profession. To determine the expected values we have to think a little about probability. For any sale, what is the probability of the occupation being Professional? There are 5 occupations and each sale can only be assigned one occupation so the probability of it being Professional is 1 in 5 i.e., 20%. In fact the probability for each occupation is 20%, assuming there is no bias in the data. So, for any set of unbiased data, we would expect 20% to be Professional, 20% Clerical, 20% Management, 20% Manual and 20% Skilled Manual. Thus, for 1000 records, we would expect each occupation to have 200 sales. But common sense tells us that we're very unlikely to get exactly 200 sales for each profession. So what we really mean is that we expect to get approximately 200 sales for each profession. Looking at the actual data, we see that Clerical appears in 177 of the 1000 records. That's a difference of -23 from the expected value. Is this close enough to 200 to be considered approximately 200? Skilled Manual has 255 records; a difference of +55. Is this difference big enough to be significantly different ? In our data there are 255 Skilled Manual records. What is the probability of this? That is, what is the...

Today's author: Danny Khen, a program manager who works on both the Excel and Excel Services teams. What is this about? Office 2007 introduced new default file formats – Open XML. Excel, Word and PowerPoint take advantage of them. One of the nicest things about those file formats is that they enable easy manipulation of Office documents without needing the Office applications up and running – by simply modifying XML “parts” within the files. In June 2007 we published an SDK that facilitates this...

Today’s Author: Howie Dickerman, a program manager on the Excel team. Howie is going to discuss different ways to change data sources in Excel 2007. Prerequisite: This post assumes that you have read the blog post entitled “Managing External Database Connections in Excel 12” which can be found here . Folks who connect to external data from Excel will sometimes encounter the following user scenarios: Switch between test and production data sources I want to test my spreadsheet while connecting to...

Today’s Author: Eric Patterson, a program manager on the Excel team. Eric is going to discuss applying pattern fills to chart data points and includes a sample add-in for this purpose. Overview In Excel 2007, the interface for applying Pattern fills to chart elements has been removed in favor of the interface for applying Picture and Texture fills. Charts formatted with pattern fills in previous versions of Excel will retain and display the pattern fills when opened in Excel 2007. Here is an example...