The PowerPoint Team Blog
From the PowerPoint Team at Microsoft.

  • Microsoft Excel 2010

    Making features easier to find and quicker to use


    One of the things the ribbon afforded us a chance to do was to make existing Excel functionality (meaning functionality already available in current versions of Excel) easier to find and quicker to use.  Today I thought I would walk through a few (but in no way exhaustive) examples.

    Find can be more than just find
    Most users are likely familiar with Edit|Find, which allows users to look for text in their Excel document.  Not as many are familiar with the fact that you can find all cells that have conditional formatting applied, all cells that contain formulas, or all cells that contain comments, primarily because there is a separate UI path to get at these capabilities (Edit|Go To … and then the “Special” button).  In Excel 2007, we have merged “Find”, “Go To Special”, and a few other capabilities in one control in the Ribbon, which our research shows helps users discover and take advantage of functionality they did not previously know existed.

    (Click to enlarge)

    Paste can be Special too
    In a similar vein, we have taken the most heavily used Paste Special commands and merged them into the Paste control in the Excel 2007 Ribbon.  Again, we believe that this will serve two purposes – advertising capabilities like “Transpose” to users that may not have otherwise discovered the feature, and putting commonly used Paste Special commands fewer clicks away.

    (Click to enlarge)

    Wrap text to the forefront
    Our research shows that “Wrap Text” is a very heavily used command in Excel, yet in current versions of Excel, it is only available as a checkbox on one of the tabs in the Format Cells dialog.  In Excel 2007, we have added a button on the first Excel tab that allows users to toggle “Wrap Text” on and off.

    Users simply need to jab the button or use a keyboard shortcut to toggle “Wrap Text”.  Again, the benefits are feature visibility and efficiency.

    Text orientation too
    Another example of a commonly used feature that is only available on the Format Cells dialog is text orientation.  We have added a drop-down to the first Excel tab that allows users to orient text with one or two clicks.

    Note that these features are still available on the Format Cells dialog; we have simply made them available front and centre on the Ribbon.

    Number format
    The final example from the front tab of the Excel Ribbon is setting number formats.  Excel 2007 has a control that allows uses to set the most common number formats without needing to launch the Format Cells dialog.

    (Click to enlarge)

    Paste Names Renamed … A "Formula" tab example
    Folks that use names frequently may be familiar with Excel’s Insert|Name|Paste feature.  In its current incarnation, the feature brings up a dialog listing names available in a workbook which users can then insert into formulas they are building.  In Excel 2007, we have shifted the UI to the ribbon, again making the feature (hopefully) more discoverable and more efficient to use.  To build a formula using names is as simple as clicking the drop-down control in the ribbon and picking the names that you want to use.  Here are a few screenshots that should give you an idea of how this works ... in this case, I am building a formula that subtracts “Cost” from “Sales” by simply picking those two names from the “Use In Formula” drop down in the “Named Cells” chunk.

    (Click to enlarge)

    (Click to enlarge)

    (Click to enlarge)

    That’s it for today.  Next week, I will start a series of posts on work we have done to make producing professional-quality documents simple and fast.

  • Microsoft Excel 2010

    Analyzing Data: Functions or PivotTables


    Today’s author, Monica Poinescu, a Software Developer in Test on the Excel team, discusses two different approaches to analyzing data in Excel.

    Edit: I've attached a file at the bottom of this blog that contains spreadsheets of the examples discussed in this post.

    My earlier blog on the new Excel 2007 function SUMIFS spawned a very interesting discussion (thanks to everyone who posted comments there): when trying to analyze/aggregate data in a table, how do we decide whether to use functions versus PivotTables?

    This blog outlines reasons to use one option or another. To better illustrate the two alternatives I’ll consider a real estate inspired example: let’s say I have list of homes for sale and their corresponding characteristics:


    and I’m trying to find the average price for those homes which have at least 3 bedrooms, a garage and are between 5 and 10 years old. Just by looking at the table, we see that only house3 and house4 satisfy all conditions and the average of their prices is 312,500.

    Here is a functions based solution:


    which returns $ 312,500.

    To build a corresponding PivotTable, one can use several filters:


    Advantages of PivotTables:

    1. While AVERAGEIFS is limited to using at most 127 conditions, PivotTables can handle more than 127 conditions.
    2. The elements of the set that fulfill all conditions are listed in the resulting PivotTable.
    3. PivotTables have a lot of flexibility: the ease of use offered by the new UI allows for a very quick detailed analysis of different available options. Nested layers in a PivotTable offer added results visualization.
    4. Several different approaches are possible: one can construct different PivotTables that answer the same question above.
    5. Particularly useful for large data sources: when relying on an external data source, you don’t need to bring all the data in Excel and one could, for example, use OLAP databases.

    Advantages of using functions:

    1. It’s easier to see in one glance all the conditions being used either by looking in the formula bar, or by listing all criteria in separate ranges.
    2. The result updates immediately when adding rows to the source table, while PivotTables need to be refreshed.
    3. The criteria, when referenced in a cell, could be as well the result of another formula, while value filters in PivotTables can only use constants.
      For example, in the formula above, one could replace
      where I4 contains another calculation ( e.g. =">"&FIXED(SUM(1,1),0)).
      The image below shows a corresponding PivotTable filter:
      The last field will not accept formulas, only numbers.
    4. Formulas take little space and are easy to move around in a sheet.

    Note that in both cases you can use the wildcard characters to define criteria. Also both solutions deal in a similar manner with missing data or errors in the range.

    I don't know if I can be fully objective on this question because I'm more of a formula person myself. If I've missed a reason you should pick one approach over another, feel free to let me know the reason you use formulas or PivotTables for summarizing data by leaving a comment.

  • Microsoft Excel 2010

    Simple Expense Tracking With New Excel 2007 Functions


    Today’s author, Monica Poinescu, a Software Developer in Test on the Excel team, gives an overview of some new functions in Excel 2007 and along the way shows us how to do some simple expense tracking.

    Considering the current economic environment, one may desire to better keep track of personal expenses. The new functions introduced in Office Excel 2007 simplify this effort. Here is how: given a list of expenses sorted by dates and categories (food, travel, clothing, etc.), how do I find how much was spent for each category, per month? And what’s the average amount spent? The functions SUMIFS, AVERAGEIFS and COUNTIFS perform selective calculations: they take into account only those values which fulfill several criteria. The values could be in a range spanning several rows and columns.

    Let’s assume I entered the expenses in a table:


    The solution below uses the new Excel 2007 feature structured references; see 'Tables Part 3: Using Formulas with Tables' for more details on how to simplify your formulas based on tables.

    I’m also using the function SUMIFS which extends the functionality of SUMIF: given a range to be summed, it will add only those values that fulfill all the given criteria (one could use between 1 and 127 criteria applied to corresponding ranges).

    The syntax is SUMIFS( the_range_to_sum, range_to_apply_criteria1, criteria1, [range_to_apply_criteria2, criteria2], … ) where the first parameter tells Excel what range we want to sum and the following pairs of arguments tell Excel on which range to apply the respective condition.

    In visual terms, I think of each pair (range_to_apply_criteria1, criteria1) as a punch card
    with some (0 or more) perforations, in the places where the criteria is TRUE. Stack all these cards on top of each other, the_range_to_sum being on the bottom, and SUMIFS adds only the values that remain visible. 

    Then start building your reports table by entering in F1:I3 the categories and the months:


    and in G2 enter:

    =SUMIFS( Table1[Amount], Table1[Category], G$1, Table1[Date], $F2 )

    which will evaluate the ‘home’ expenses for January (235.35).

    It should look like this:


    Similarly, in H2 enter:

    =SUMIFS( Table1[Amount], Table1[Category], H$1, Table1[Date], $F2 )

    this will evaluate the ‘food’ expenses for January .

    And in I2 enter:

    =SUMIFS( Table1[Amount], Table1[Category], I$1, Table1[Date], $F2 )

    to get your travel related amount.

    You can copy/paste these formulas down as you add more months. This will automatically take into account new entries in your expense list. Also, in case you need to figure the average expense per category, within each month, you can use:

    =AVERAGEIFS( Table1[Amount], Table1[Category], "home", Table1[Date], "January" ) - this returns 117.675

  • Microsoft Excel 2010

    Calling all PivotTable users: Let us know how you like to summarize your data...


    A few months back I wrote several posts about the improvements that we made to PivotTables in Excel 2007.  I even showed how summarizing data in different ways is now just a right click away on the “Summarize Data By...” menu.  What I didn’t show was that in addition to these standard ways of summarizing data (sum, count, max, min, average, etc.), Excel already supports some more complex summary views for items contained in a PivotTable.  For instance, by selecting “More Options,” you can choose to show the data as a “Running total” or “% of Total.”

    So, given the data below in a PivotTable ...

    (Click to enlarge) can choose to “Summarize Data By... > More Options” and then show the values as the “% of total”....

    .... and Excel will automatically perform the calculation for you in the PivotTable.

    (Click to enlarge)

    We’ve gotten a few pieces of feedback about this functionality, usually that (a) it’s useful but hard to find, or (b) it would be more useful if there were additional options for how the data could be shown in addition to the ones listed in the dialog above.

    We have someone on the team starting to look at this as part of our planning for future releases (the version after 2007), so we’d like to hear what you think.  Here are some specific questions we would love to hear your answers to, but feel free to add additional commentary too.

    1. Have you ever used this feature in PivotTables?
    2. If not, why not?  (Might you use it now that you know about it?)
    3. If so, what are the most common options that you choose, and what type of data do you use them with?
    4. Perhaps most interestingly, are there additional options that you would like to see added (for instance - “% of parent” could show the percentage of an item to its parent group instead of to the column total)?

    Please post your answers using comments, as it is easier for everyone on the Excel team to read comments (as opposed to the email link which sends email just to me).

    Thanks for your help.

  • Microsoft Excel 2010

    The Excel 12 Blog Rides Again, or “CUBE Functions Part 1”


    OK, I am freshly back from a fascinating couple weeks of visiting a number of customers (thanks to all you that spent time with us over the last few weeks), so it is time to get back to talking about Excel 12 features.

    We’ve already talked about using Pivot Tables to bring OLAP data from SQL Server Analysis Services into Excel.  In cases where you want to explore the data and look for interesting facts within the data by “slicing and dicing” it, PivotTables are the perfect tool.  You can change the fields that are being displayed, drill into the data, change the filtering and sorting that has been applied, and in the case of OLAP data from SQL Server Analysis Services, you can use all the other features I have covered to look at the data your way.

    There are other scenarios that involve bringing OLAP data from SQL Server Analysis Services into Excel, though, that are not as well suited to using a PivotTable.  While PivotTables are an ideal tool for quickly exploring your data and finding a wide variety of answers in an ad hoc manner, they are not as well suited for building fixed reports with irregular or custom layouts.  Here are a few quick examples:

    • "Asymmetric reports,” wherein the rows or columns don’t follow a fixed pattern in terms of the data they contain (more on this below)
    • Reports that mix data from multiple data sources
    • Reports that require a lot of custom spacing and/or precise grouping and ordering, often in a different way than the data is stored in the data source

    To address those needs (and others),  we wanted to provide a way to place OLAP data from SQL Server Analysis Services directly into specific spreadsheet cells without being constrained in any way by what might be in the adjacent cells.  This would provide users with the power of a multi-dimensional data store in conjunction with the power of the Excel formula language and calculation engine.  Let’s take a look.

    Here is an example of an asymmetric report that was built using the Excel 12 beta build and the Adventure Works sample database that ships with SQL Server 2005 Analysis Services. 

    (Click to enlarge)

    Note that the fields across the top axis (on columns) do not come from the same dimension.  The sales reasons (‘On Promotion’ and ‘Price’) and the countries (‘Canada’ and ‘United States’) are members from two completely distinct dimensions.  Also note that while all the values are based on the items in Column A, this report contains blank “spacer” columns in columns B and E.  Finally, you can see the formula for cell C2 which fetches the ‘On Promotion’ member from the ‘Sales Reason’ cube dimension.  This formula uses the CUBEMEMBER function (which is one of seven new CUBE functions) to place OLAP data from SQL Server Analysis Services into Excel spreadsheet cells.

    What are the new CUBE functions?
    We have implemented seven new CUBE functions that can be used in Excel formulas just like any other function in Excel.  These functions permit Excel to fetch data from SQL Server Analysis Services (2000 & 2005), including any member, set, aggregated value, property, or KPI (Key Performance Indicator) from the OLAP cube.  This data can then be placed anywhere in the spreadsheet, intermingled with other local calculations and/or within other formulas.  Here are the seven new CUBE functions:

    CUBEMEMBER (connection, member_expression,[caption])
    This function will fetch the member or tuple defined by the member_expression.  For example, (from the illustration above,) the formula:  =CUBEMEMBER ("Adventure Works", "[Sales Reason].[On Promotion]")  returns the member named “On Promotion” from the “Sales Reason” dimension of the Adventure Works cube.
    CUBEVALUE (connection, [member_expression_1], [member_expression_2], …)
    This function will fetch the aggregated value from the cube filtered by the various member_expression arguments.  For example, the formula: =CUBEVALUE ("Adventure Works","[Measures].[Gross Profit]","[Product].[Category].[Bikes]","[Date].[Fiscal Year].[FY 2004]")  returns the value $5,035,271.22  which is the aggregated amount in the Adventure Works cube for Gross Profit for Bikes in Fiscal 2004.

    CUBESET (connection, set_expression, [caption], [sort_order], [sort_by])
    This function will fetch the set that is defined by the set_expression parameter.  Optional parameters allow you to specify the ordering of the set as well as the caption to be displayed in the Excel cell that contains this formula.  (Note that the set itself won’t have a display value.)  For example, the formula: =CUBESET ("Adventure Works","[Customer].[Customer Geography].[All Customers].children","Countries") returns the set of countries in the Customer Geography hierarchy and shows “Countries” as the cell’s display value.

    This function returns the number of items in a set.  Typically the argument to this
    function will be a CUBESET function or a reference to a CUBESET function.

    CUBERANKEDMEMBER (connection, set_expression, rank, [caption])
    This function returns the Nth item from a set.  This can be very useful when building a Top N (or Bottom N) report in Excel.

    CUBEMEMBERPROPERTY (connection, member_expression, property)
    This function returns a property of a member in the OLAP cube.

    CUBEKPIMEMBER (connection, kpi_name, kpi_property, [caption])
    This function returns a KPI (Key Performance Indicator) from the OLAP cube.

    CUBE functions provide MDX to other CUBE functions
    There is one way in which the CUBE functions are significantly different from any other Excel functions.  This is in the way that they behave when they are passed in as arguments to other CUBE functions.

    Functions that are not CUBE functions return a value which is displayed in the Excel spreadsheet cell.  For example, the result of a SUM function will be a number that is displayed in the cell.

    CUBE functions (except for CUBESETCOUNT) return a result which is more complex than this.  CUBE functions return two distinct values.  One is the value that is displayed in the cell.  But there is also a second hidden value which can be thought of as an MDX expression (MDX is the Multi-Dimensional eXpression language used by SQL Server Analysis Services) defining the result.  When one CUBE function uses another CUBE function as its argument, the argument takes on the MDX value rather than the display value.

    Here’s an illustration that I hope will help to make this clearer.  Here is the asymmetric report that we looked at earlier.

    (Click to enlarge)

    Cell C2 actually contains two separate values.  The first is the display value that you see in the cell, namely “On Promotion”.  But when the formula in cell C3 uses cell C2 as an argument, the value that it obtains from cell C2 is actually “[Sales Reason].[Sales Reasons].[Sales Reason].&[2]” which is the MDX unique name for the “On Promotion” member.  You can find this hidden value behind the CUBE function using Excel’s object model by selecting cell C2 and then typing “?Activecell.MDX” into the immediate window in the Visual Basic Editor.

    And that’s the overview.  In my next post, I will provide some examples that illustrate how to use these functions in your reports.

  • Microsoft Excel 2010

    Excel 2010 PivotTable What-If Analysis (Writeback)


    Thanks to Diego Oppenheimer for putting together this post.

    When thinking of Excel as an OLAP analytical tool the first thing that usually comes to mind is the ability to quickly and easily analyze data from an OLAP data source. With the introduction of PivotTable What-If Analysis in Excel 2010 you can now easily modify this data as well. Put simply, PivotTable What-If Analysis is the ability to modify values in PivotTable cells, recalculate the PivotTable with those values and, if the results are satisfactory, publish the changes so that they are reflected in the OLAP data source for other people to see. This feature is also called writeback, though it really goes beyond just the ability to write values back to the cube. “What if our sales for New York increased by 10%?” “What if the total number of product orders fell by 25%?” These are examples of questions that PivotTable What-If Analysis is aimed at helping the user answer.

    Let’s take a look at the following scenario:


    Stephen, the sales manager at a small IT company, is trying to figure out what his optimal sales quotas should look like for 2010. Based on a previous analysis and forecasts for next year it seems like 2010 will have a large decrease in sales which would mean his quotas would be reduced by 10% from the 2009 quotas. With the new PivotTable What-If Analysis feature he can now modify the relevant values in his PivotTable report and calculate the PivotTable with the changes to see what the totals would look like without actually modifying the data source. (click to see larger image)


    So here is what his PivotTable looks like after calculating with the changes:


    If he wants to keep these numbers and share them with other people he can go ahead and publish the changes to the OLAP data source.


    He can also just keep the changes locally without publishing back to the OLAP data source by simply saving the file. When the workbook is loaded again in Excel 2010 all the changes will be reapplied and the PivotTable recalculated.

    With little knowledge of OLAP data sources the manager was able to change values and recalculate his PivotTables. The changes can be shared across the organization by simply sharing the workbook or publishing the changes back to the OLAP data source.


    Advanced Options

    Advanced options for PivotTable What-If Analysis include being able to change the allocation method which is the methodology in which the OLAP data source will allocate the newly entered values in the cube. Excel 2010 also gives you the ability to define your own weight allocation definitions from within an MDX editor.

    We on the Excel team are excited about this feature for the possibilities it brings to our users and as always your feedback is much appreciated and welcomed.

  • Microsoft Excel 2010

    Function Improvements in Excel 2010


    Thanks to Jessica Liu for putting together the next few posts on function improvements.

    In Excel 2010, we made many improvements to Excel's function library. Excel 2010 will feature an accurate and consistent function library while remaining compatible with previous versions of Excel. In this first blog post, I will be giving an overview of the work we did in this area as well as talk about the function accuracy improvements. Subsequent posts will go into the details of the consistency improvements as well as the backward compatibility story.

    The first area we invested in was to improve the accuracy of functions. Over the years there have been various academic papers detailing issues in Excel's worksheet functions. In Excel 2003, we started the work to address the most serious of the issues reported in these papers and in Excel 2010 we have addressed even more of these issues. Our goal for Excel 2010 was to address the most significant function accuracy issues reported. For any function we modified, we corrected all known bugs relating to that function.

    We implemented new algorithms in order to improve the accuracy of our statistical, financial and math functions. We worked very closely with industry experts to determine which algorithms to use as well as to validate these new algorithms. Our hope is that Excel 2010 users will be able to utilize functions in our library with confidence knowing that they have comparable accuracy to those of other statistical packages.

    The other area we invested in was making our function library more consistent. This was in response to the other set of concerns voiced in these academic papers as well as by our users. Users have noted that there were consistency issues with Excel's function names and definitions. In Excel 2010, we will offer users a set of consistently and accurately named functions as well as function definitions that are consistent with user expectations. We have introduced over 50 new functions in order to do this.

    Finally, the last piece of work we did in this area was to update the functions user interface. We have improved the function auto complete feature, and we have also made changes to support the new function set.

    Improved Function Accuracy

    For Excel 2010, we overhauled the function library and implemented completely new algorithms for many of our statistical, financial and math functions. The Excel team partnered with Frontline Systems, the Numerical Algorithms Group, and ScienceOps to select, implement and validate these algorithms.

    The algorithms for calculating the follow statistical distribution functions have been modified or redesigned completely for better accuracy:

    Binomial distribution


    Chi squared distribution


    Exponential distribution


    F distribution


    Gamma distribution


    Hypergeometric distribution


    Lognormal distribution


    Negative Binomial distribution


    Normal distribution


    Standard Normal distribution


    Poisson distribution


    Student's t distribution


    Weibull distribution


    The following financial functions have improved accuracy:

    Cumulative interest paid on a loan


    Cumulative principal paid on a loan


    Interest payment for an investment


    Internal rate of return for a series of cash flows


    Payment for a loan


    Payment on principal for an investment


    The accuracy of these additional functions has been improved:

    Hyperbolic arcsine


    Ceiling function


    Convert function


    Error function


    Complementary error function


    Floor function


    Natural logarithm of the gamma function


    Geometric mean


    MOD function


    Random number function


    Sample standard deviation


    Sample variation


    As part of the accuracy improvements, we will also accept a larger range of input values and as a result will be returning a wider range of results for certain functions. For example, the ERF and ERFC functions will now take in negative input values, and the MOD function will be able to take larger input values.

    In the next post, I will talk about the changes we have made in Excel 2010 to improve the consistency of the function library.



  • Microsoft Excel 2010

    Using Excel 2007 for Progress Tracking in the Classroom


    Today's author, Danny Khen, a Program Manager on the Excel team, discusses a solution he recently built using Excel.

    The Manual Spreadsheet

    Spreadsheets are the bread and butter of running the business world, where PCs are ubiquitous. The possibility, indeed even the need, to use spreadsheets in an environment where access to a computer is not taken for granted seems absurd to us, as business users and technology professionals. We do still print reports for some output scenarios, such as executive reports and meeting handouts. But as a whole, using a computer for day to day interaction with a spreadsheet application is so obvious that it's almost embarrassing to state.

    Well, meet the preschool classroom.

    I won't risk talking about the "typical" preschool classroom in the early 21st century, because I only know one of them - the one my youngest daughter used to go to, and where Michal, my better half, teaches. Let's cautiously say that at least some of them are not yet IT-enabled. This particular one does not have a single computer on premises. And most staff members are novice computer users at best. Which does not mean that they don't have excellent reasons to use Excel (don't we all?); they are just not always quite aware of those reasons.

    Enter Michal: a Montessori preschool teacher. Michal has a CS degree (Cum Laude - there, I said it, I can safely come home tonight) and a previous software development career and, as a computer user, she just loves Excel. So she set out to solve some of the tracking and reporting needs that she had as a teacher by using Excel, the tool of her choice, and applying it to her, well, IT-challenged work environment.

    Progress Tracking (Take 1)

    The problem

    The Excel workbook that this article discusses is used to track a teacher's work with each child in the class and the progress each child is making. This is a Montessori school which, for the purpose of this article, means that there are several learning areas, each broken down into many steps, and that each child progresses at their own pace. Teachers regularly give 1:1 lessons to children and need to track which steps they have covered with each child during a lesson and how the child is progressing towards mastering each step. Finally, it is important to be able to collect this information into a report. This report will be presented as supplementary information at the parent/teacher conference at the end of the school year.

    In short, the classic story: Data Entry; Tracking; and Reporting.

    The solution

    The parts of the problem that Michal approached first were data entry and tracking. The main challenge is that several teachers have the need to collaborate on collecting, writing down and monitoring the data and there are no computers in the classroom. Michal designed a solution by which each input sheet and its associated report are one and the same and collaboration is done on paper. It goes like this:

    • The sheets are designed in Excel and printed out, one per main Montessori learning area.
    • The printouts are hung on the wall in a corner of the classroom (hidden from visitors' eyes).
    • Whenever a teacher observes a child progress on a topic, she marks it in the appropriate worksheet cell on the relevant printout.
    • Once a month or so, Michal yanks the sheets of paper off the wall, takes them home, updates the master copy on her computer, and prints new versions of the sheets for hanging in the classroom. This update process is not very time consuming; it has to do with writing 1, 2 or 3 in the cells that teachers changed during the passing period.

    As she was working on this, I introduced her to Excel 2007, and she immediately saw the potential of one new feature in particular - the icon sets that can be used as easy-to-visualize KPIs. Here's a sample of what her sheets look like, after several update cycles (student names are fictitious of course):


    Teachers mark down day-to-day results in the blank cells. Michal then updates the machine version with the status - the numbers 1, 2 or 3 - and Excel's conditional formatting does the rest.

    One interesting fact about this solution: it breaks the normal design pattern of separating input data from nicely formatted sheets. Input is done on the formatted sheets and the sheets are structured in a way that optimizes manual input and tracking at the expense of having source data that's easy to process. For example, it is rather hard to build BI-style reports using this data. It would be much easier to build a PivotTable of some sort using this data if the input was organized in records such that there were columns for name, area, topic, lesson, status and so on and a row per "event" (lesson given). But in this solution, which focuses on optimizing for paper usage, the sheet is organized as a sort of manually laid-out cross-tab.

    Notice also the usage of Page Break Preview mode (the "Page 1" watermark; the blue page borders). It is important to be able to lay things out and adjust widths and heights based on the need to print the sheets.

    The reporting part of this phase of the solution was still completely manual: Michal designed a report template in Excel, and teachers would photocopy the template once per student and fill it out based on the tracking sheet results. Remember, I am talking about filling out the form in the pen-and-paper sense. The process is error prone and errors do indeed occur. Here's a portion of the report template:



    Incredibly enough, with all its manual weaknesses and error prone report creation, the solution was enthusiastically received and has been successfully used for over a year now by Michal's fellow teachers. Here's the feedback from the field:

    • The solution is easy to explain to new (even IT-challenged) teachers.
    • Promotes communication among teachers.
    • Input is simple and quick; low bar to usage (no real reason to avoid doing the input).
    • New teachers or subs walk into the classroom, and easily figure out at a glance what lesson needs to be given to each child, as if they really knew those children.
    • Teachers in other classrooms asked for, and received, empty copies of the tracking sheets; they added their students' names manually on the left, and have been using the sheets for pure manual tracking - without even the benefit of periodic cleanup - and are very happy with it.

    Progress Tracking and Reporting (Take 2)

    The problem

    The workbook above was a great start and has been successfully used for over a year now. Still, Michal knew about various improvements she wanted to make but just didn't have time for. So I offered to put some of my time into it and asked her to give me the functional specifications or, in human speak, to tell me what's still missing and what else she would like this solution to do for her. She came up with a few things:

    • The workbook has several sheets, one per main learning area (Math and Sensorial are two used in the article examples). The student names are completely manual; it is hard to add/remove students and synchronize the different sheets.
    • Teachers need to be able to see some supporting information about the students. For example, it is important to know if a child is in kindergarten age; it would be helpful if we could calculate this from his date of birth and indicate it on the sheets.
    • Writing up reports for the end of year conference is a huge, laborious task. Michal wished they could be generated. She wanted to be able to print a report page per child; and she wanted to keep snapshots of the children's progress.
    • Whatever we did, we had to keep in mind the key scenario: these are printed sheets, hanging on the classroom walls and written on by the teachers. This is the spirit of the solution. Without it, the solution loses its power.

    The solution

    <Several nights pass>

    Here's what I did (complete solution attached to this article):

    • Added a Students sheet. It has a table with students' names, as well as dates of birth, and calculated ages (displayed as years and months - using /12 fraction format).
    • Using the current school year, I calculated for each child if they are in kindergarten age.image
    • The tracking sheets refer to the Students sheet and add conditional formatting to student names - showing them in bold if they are kindergarteners.
      • Note that the source student table can be sorted and filtered to one's heart's content. Other sheets always use =VLOOKUP in order to fetch a student's info from the table; reordering the table does nothing to the order of rows on the tracking sheets. Why is this important? Right! Manual data entry. To avoid confusing teachers, the student list on the tracking sheet should not change from print to print. But whoever uses the machine version may well want to reorder the student table, e.g., to sort by age.
    • I added support for report generation. It has 3 main parts:
      • A report input sheet, on which a teacher enters some parameters for a student's progress report, such as the date and the student to report on. In addition, the input sheet has a table with the textual comments that the teacher adds for each child.
      • A report sheet. This is based on the template in the original solution (with some more formatting), but it pulls data for the selected student from all around the workbook - the various tracking sheets and the report input sheet. The status terms of "Introduced", "Working" and "Mastered", located next to the learning steps, map to the icons on the tracking sheets.
        • This is fairly rudimentary. E.g. I look up a student by name on the tracking sheets (good), but I then grab a topic's status for the student by hard-coded column indexes to VLOOKUP (bad). This could definitely be improved.
      • Bulk report generation code. This is a VBA macro that creates a new workbook, goes over the student list, and generates one report sheet per student, as snapshots that show data for each student. I also added a button that runs the macro on the report input sheet. The generated workbook can be printed out and the printed pages distributed on the conference night; the workbook can also be saved as a results snapshot file.

    A student report looks like so:



    Still remains to be seen, as this new version has not been used yet. Michal is happy about the better maintainability and robustness of this workbook. She is particularly excited about the bulk report generation. Being able to generate dozens of reports in seconds, rather than hours, opens up possibilities she did not have before - such as reporting back to parents in shorter intervals.


    I hope I was able to demonstrate how Excel can be used in a somewhat unconventional way, combining manual input and tracking with report automation. Excel is flexible and powerful enough to even lend itself to such a hybrid solution.

    Attached to this article is a copy of the progress tracking and reporting workbook, which has all the elements discussed here, including the VBA code to auto-generate multiple snapshot report sheets.

  • Microsoft Excel 2010

    Charting I – Professional charts, made easy


    A few posts ago when I described the work we did in the area of “great looking documents”, I mentioned charting.  I am going to spend the next week or two covering charting in detail.  For this first two posts, I want to cover how we have used the ribbon to make it possible, with no more than 3-4 clicks, to create a wide variety of professional-looking charts.

    When talking to customers about charting in Excel, one of the big pieces of feedback we hear is how hard it is to make a chart that looks ready to publish. Generally, users aren’t graphic designers or experts in information visualization, yet they still want a result that looks professional and communicates their information effectively.  With advent of the ribbon, we have a new UI design approach – results-oriented design (which is discussed in a blog post here).  In a nutshell, the idea is to give users a couple of reasonable choices for professional designs, then allow them to mix and match those choices into a professional document.  While we’ll still give users access to the detailed features that make their documents look good, they should be able to get close to a final result in just a few clicks.  To that end, chart creation in Office 2007 can be as simple as making four straightforward choices that give users access to a vast range of possibilities.  The four choices are: what type of chart they want, how they want the elements on that chart laid out, how they want their chart formatted, and what document theme they would like to use. I’ll talk about two of these choices in this post, and the other two choices in the next post.

    Chart Types

    In current versions of Excel, when a user creates a chart, the first thing they need to do is select the type of chart - column, line, scatter, pie, surface, and etc. In Excel 2007, we’ve made the variety of chart types available a lot more visible, and we have offered help for users to choose between them.  To insert a chart, a user would start with the Insert tab.  Excel 2007 has an insert chart type “group” (7 related controls) on the ribbon’s insert tab. This makes it easy to pick a chart type, with large icons and tooltips that describe when to use a particular type.

    (Click to enlarge)

    Note that we have provided galleries for the most common chart types – column, line, pie, bar, area, and scatter – with the remaining chart types surfaced in a 7th gallery.  For those who want to browse through the full list of chart types, or change the type of an existing chart, the Create Chart dialog makes it easier to explore the list of chart types and pick the one you want.

    (Click to enlarge)

    And for those who love shortcut keys, Alt+F1 will now create a chart object with the default chart type, while F11 still creates a chart sheet with the default chart type.

    Although we do hear many customer requests for new chart types, unfortunately we weren’t able to add any in Office 2007. We’re not yet planning the next version of Office in depth, but no doubt that will be considered again, as this is another area where we hear a lot of feedback.

    Chart Layouts

    Once a user has chosen a chart type, there are a variety of charting features that can help the user communicate their data effectively.  In previous versions of Excel, these are scattered around through a variety of dialog boxes, making it hard for all but the most diligent users to take advantage of the settings.  For Office 2007, we studied a massive number of charts that we found in publications, books, and Excel spreadsheets to determine the most common combinations of chart elements such as titles, legend, data table, etc.  From this, we created a gallery of predefined chart layouts (e.g. combinations of chart elements) that can be applied with one click.  Here is what the gallery looks like for a bar chart

    (Click to enlarge)

    And here is what the galleries look like for a few other chart types.  Note that each chart type has its own unique set of chart layouts.

    Line Quick Layouts

    Pie Quick Layouts

    Scatter Quick Layouts

    I have not included the pictures of the chart layouts for all the chart types, but I’ve tried to include those for most of the more popular chart types.  We have tried to included layouts for the sorts of charts we are commonly asked about.  For example, there’s a chart layout designed for making histograms - the third choice in the second row of the column chart layouts.  There’s a chart layout designed for making sparklines – the first choice in the third row of the line chart layouts.  There’s a chart layout designed for making pie charts with labelled slices instead of a legend.  Each chart layout is a useful high-quality chart which presents data in a different way.  We are still refining the set of chart layouts and their icons, so expect some changes before Office 2007 is done.

  • Microsoft Excel 2010

    Your Turn - Drawing Tools


    The team that builds the drawing tools in Office (sometimes referred to as OfficeArt) is interested in understanding all the different ways people are using the OfficeArt drawing tools, images, and audio/video with Excel.  While a lot of the usage is well-understood (on charts as callouts, as controls on the grid, as backgrouds in an application, as navigational items, etc.), we run into people doing all sorts of interesting things, so we thought it would be interesting to hear from blog readers on all the ways they use the drawing tools, images, etc.  We are also interested in how people are using VBA with drawing shapes.  For those of you that are game, please give us a brief description of what you’re doing, and let us know if it’s ok to contact you with follow-up questions sometime in the future.  Files are welcome too – you can send them to


    One other thing that is also interesting is anything that you cannot do today that you would like to be able to do (i.e. extending support for formulas, more object model, etc.).

  • Microsoft Excel 2010

    Sparklines – Lining Up the Points


    In this blog post I’ll walk through some of the ways you can use sparklines in Excel 2010 using sample sparklines from the book store demo file:


    In the above example we have sales for each year, the cost of sales, and net profit for that year all divided by the category of book.  One of the things you can do with sparklines in Excel 2010 is to stick them in cells above or below each other and have the points line up.  Doing this will allow you to see trends for multiple fields in a related way.


    Here I’ve taken the table, hooked up sparklines to it, and broke it out into a dashboard like sheet.  This conveys the trend of sales and profit for each year for past 10 years.  The sales are represented by the line sparkline, and the profit is the win / loss sparkline below it.  You can see in some places, like romance, that even though sales have fallen, we still turned a profit … and the opposite is true for sports.

    Axis options can make a big difference here.  Right now the axis for the sparkline group is set to automatic, so each sparkline is showing the trend for its data, if we make the sparklines share a single axis, you can also tell which category has more sales than others:


    In this example, mystery has traditionally had a lot more sales than history, but now they are about the same.  To create something like this isn’t too hard in Excel:


    Here you can see that the line sparklines and win / loss sparklines are just one cell above the other and their points line up.  One other thing to note here is that I’ve added conditional formatting after the end of the win / loss sparkline.  There are upcoming blog posts on the new conditional formatting features, so more details to come on that.  You can vary the design of this a lot, and format the cells around and the sparkline cell itself to show highs and lows:


    In the first example above, the number is in the cell with the sparkline.  Since sparklines in Excel sit in the cell like data bars and icon sets, numbers can exist with them in the same cell.  In the second example they are in the cells next to the sparkline, color coded to match the high and low point … and the last example shows the original table with the sparklines embedded in line with the data.

    And on that note I’ll wrap up the sparkline post for today, still a couple more coming this week, stay tuned!

  • Microsoft Excel 2010

    CUBE Functions 3: Formula AutoComplete revisited


    Previously when discussing CUBE functions, I showed a couple of examples of reports based on OLAP data that could be built using CUBE functions.  Now I’d like to explain how we’ve done something very special with Formula AutoComplete to make it easy to navigate the OLAP namespace while authoring CUBE functions.

    Formula Auto-Complete for CUBE function arguments
    Formula AutoComplete for CUBE function arguments has some special behaviours relative to the other functions in Excel.   As a brief refresher, Formula AutoComplete is a feature that provides a list of values from which to choose as you write formulas … here
    is a blog post that describes this in detail.  In most Formula AutoComplete scenarios, Excel knows the list of values (formulas, named ranges, table names) that it should display because those values are part of the Excel application.  For example, when you start typing a function name, Excel can give you a list of all the other functions that start with the same character(s), as is shown in this screenshot.

    (Click to enlarge)

    In this case, Excel already knows what all the possible function names are that begin with “s”, so it can easily display a list of function names that begin with the specified character(s).  Ditto named ranges, UDFs, table names, etc. 

    For many arguments to the new CUBE functions, however, we have a different scenario.  Excel does not inherently know about the multi-dimensional database (OLAP cube) from which data is being fetched.  In order to provide an auto-complete dropdown, it is necessary for Excel to query the multi-dimensional database or OLAP cube to find out what the set of valid items will be.  Here’s an illustration of how this works.  In this example, I have already created a connection named “Adventure Works” to the Adventure Works database on SQL Server 2005 Analysis Services.

    I will start by entering a CUBEMEMBER function that uses the Adventure Works connection, and when I get to the second argument, (which calls for a member_expression,) I will only enter the opening quotation marks that tell Excel I am planning to enter a string.

    (Click to enlarge)

    The list of possible values that appear in the dropdown did not come directly from Excel.  How’d that work?  Excel issued a query to the Adventure Works OLAP cube and displays a list of dimension names from that cube.  Let’s say that I choose the Dimension named “Customer” and then enter a period (which is used as a separator by the MDX language).

    (Click to enlarge)

    I see that there are several hierarchies in the Customer dimension.  Using the arrow and tab keys, I will select the “Customer Geography” hierarchy and enter another period.

    (Click to enlarge)

    The auto-complete drop down shows me that there is a single member at the top of the Customer Geography hierarchy, and that item is “All Customers”.  By choosing this item and entering another period, I will see an auto-complete drop down of the children of “All Customers” (which are the countries in which the customers are located).

    (Click to enlarge)

    At any point, I can finish the argument by entering the closing quotation marks.

    The key point that I want to make here is that the Formula AutoComplete feature is providing a mechanism for users to navigate the hierarchies in the multi-dimensional database.  Even if you have no prior knowledge of the multi-dimensional database to which you are connecting, Excel’s Formula AutoComplete feature will show you the dimensions in the cube, the hierarchies in each dimension, and the members (and their children and grandchildren, etc.) that are contained in each hierarchy.  Also, it can be much faster to use auto-complete to enter CUBE functions into Excel formulas because you only need to identify the item you want from a drop-down list and then hit the Tab key, as opposed to typing the full MDX name for each function parameter.

    The name that you get for a member using Excel’s auto-complete will be a fully qualified name because you make a selection at each level of the hierarchy.  It is not the only name that could be used, nor is it the special “member unique name” for a member in the cube.  When you know that a shorter MDX expression will resolve to the same member, you are free to enter the shorter expression.  It’s just that Excel will help you to navigate the namespace of your database when you’re not already familiar with that data.

    The list of items that is displayed in each case is the list of the first fifty (50) items which match the characters that have been entered so far.  As you enter more characters, the auto-complete drop down list is automatically updated.

    This is a feature I love to demonstrate – for folks that use Analysis Services cubes, it really is going to be a very helpful feature.

    Next time, I’ll talk a bit about the work we’ve done in Excel 12 to make connection management easier.

  • Microsoft Excel 2010

    Charting V – PivotCharts


    Whenever we talk to users about PivotCharts, the first request we hear is that they behave more like regular charts.  In previous versions, PivotCharts had very limited layout and formatting options. In addition, if you refreshed the PivotTable that the PivotChart was based upon, the PivotChart would lose whatever formatting it had. We heard from many users that they would often just create regular charts instead of PivotCharts, since could be problematic  As PivotTables and charts changed in Excel 2007, we made sure PivotCharts changed along with them – with a key goal being that they became more consistent.


    PivotCharts in Excel 2007 can have all the same formatting as regular charts, including all the layouts and styles talked about in previous posts.  You can move and resize chart elements, or change formatting of individual data points.  One of the few restrictions that we did not have time to address in this version is the one on chart types – you still can’t create scatter, bubble, or stock PivotCharts.

    If you refresh the data for your PivotChart, the chart updates and the formatting does not change.  Any new series or data points will be formatted to match the style or series as appropriate.  As a result, you can easily make PivotCharts for presentation or publication.

    Furthermore, PivotChart will remember formatting across pivots.  If you set the colour of the series or a particular data point to red, then change the pivot so your data is no longer showing, the red band is gone as well. If you bring the data back into view, the red colour returns.

    PivotTable Field List

    PivotCharts use the same field list as PivotTables.  Just as with PivotTables, you can click the checkboxes for the fields you are interested in, and they will be reasonably laid out as a PivotChart.  You can also drag the fields around from region to region to pivot the PivotChart.  The field list also provides access to field settings such as how to summarize the data.

    PivotChart Filter Pane

    The PivotChart filter pane is a new task pane that enables you to filter PivotCharts.  In previous versions, filters were accessed from buttons in the PivotChart, but removed these so they didn’t affect the layout of the PivotChart.  The filter pane has the same filter capabilities as for PivotTables.  See filtering in PivotTables for details.  Below is a shot of the PivotChart field list and Filter task pane.

    (Click to enlarge)

  • Microsoft Excel 2010

    Creating a Master-Detail View in Excel


    Today's author, Dan Battagin, a Program Manager on the Excel team, talks about joining two tables in Excel (a.k.a. returning multiple rows for VLOOKUP).

    Today, we'll take a look at the VLOOKUP function, and work out a way to get around its major drawback - it returns only a single value that matches the lookup.

    If you're like me, there are times when you have a big table of data which is pretty well normalized, and you want to pull all of the rows out of that table that match a certain criteria. Well, it's easy to get the first match in that table by using VLOOKUP (in fact, it's easier than ever with the new structured references in Excel 2007):

    Figure 1: VLOOKUP is an easy way to return a value out of the first row that contains a customer name.
    Figure 1: VLOOKUP is an easy way to return a value out of the first row that contains a customer name.

    As you can see above, with structured references in Excel 2007, not only is VLOOKUP easy to use, but it's actually pretty readable - we're matching the value of A9 in the first column of Table1 and returning the value from the 3rd column ($25.00). That's really nice, and very useful (especially if you use VLOOKUP as part of a calculated column in a table) but it does have the drawback that it doesn't ever allow us to return any of the values for the second "Dan" in the list. And, if you're like me, this can be pretty frustrating. I'm constantly working with lists of Excel bugs (yep, we've got some bugs), materials lists for woodworking projects that I'm currently involved in, price lists for whatever current home remodel I'm working on, etc. where I really need to see more than just that first row.

    So today, let's look at how we can actually make this work, and retrieve all of the values from a table that match a certain criteria.

    Before we get started on the solution, let's lay out the data that we're going to use. Since it's always a pain to come up with data sets for work examples like this, I'm going to use an old standby - the Northwind Sample Database that ships with pretty much every version of Access. Of course, this being Excel, I'm going to copy the portion of the data that I need into worksheets. (Yeah, yeah, I'm a database guy too, and I know we could do the joins in SQL, but that's not always the case, so bear with me.) OK, so here's the data I'm going to copy onto sheets in Excel:

    Figure 2: The "Customer" table, inserted on a sheet called Customers, and named "tblCustomers" for easy referencing in the future. Figure 3: The "Orders" table, inserted on a sheet called Orders, and named "tblOrders" for easy referencing in the future. Figure 4: The "OrderDetails" table, inserted on a sheet called Details, and named "tblDetails" for easy referencing in the future.
    Figure 2: The "Customer" table, inserted on a sheet called Customers, and named "tblCustomers" for easy referencing in the future. Figure 3: The "Orders" table, inserted on a sheet called Orders, and named "tblOrders" for easy referencing in the future. Figure 4: The "OrderDetails" table, inserted on a sheet called Details, and named "tblDetails" for easy referencing in the future.

    So you don't have to do this yourself, I've also made a copy of the spreadsheet available so you can just follow along (see the attachments at the bottom of this post). Of course, if you want to walk through this yourself, go right ahead, that's a pretty cool compliment.

    So now that we've got all of our source data, I want to put together the actual Master-Detail form. Nothing too fancy, but basically I want to select the Order Number that I want to look at, and see some details about that order (who ordered it, order status, etc.) as well as all of the items that are part of that order (item name, price, quantity, etc.). Pretty standard stuff. Here's our target end result:

    Figure 5: The master-details form where I can pick an order number and see all of the details.

    Figure 5: The master-details form where I can pick an order number and see all of the details.

    In the available workbook, you'll find this form put together already, but if you're doing it yourself, here are the steps you'll want to take (note that these steps skip the formatting of this form, since not everyone likes green the way I do):

    1. Enter the following static strings in the sheet:
      1. B1: Order Number
      2. C3: Order Information
      3. C4: Customer
      4. F4: Order Date
      5. F5: Status
      6. F6: Salesperson
      7. F7: Ship Date
      8. C10: Order Details
      9. C11: Product
      10. D11: Quantity
      11. E11: Unit Price
      12. F11: Discount
      13. G11: Total Price
      14. H11: Status ID
    2. Create a Named Range that includes all of your Order IDs, which we'll use to create the Order ID drop down using Data Validation. To do this, click on the Formulas tab | Define Name and enter:
      1. Name: OrderIds
      2. Refers to: =tblOrders[ID]

        Tip: Data Validation cannot refer to ranges on a different sheet than the one that contains the validation, but by using a named range, you can get values from another sheet (and in this case, get a dynamic list of values using the structured reference to get an entire table column, even if new values are added to it in the future)
    3. Select D1 and name it rngOrderId (we'll use this later).
    4. With D1 still selected, click on the Data tab | Data Validation and create a new List type validation with Source: "=OrderIds". Boom, now you have your drop down.

      OK, now we're going to create some "normal" VLOOKUPS as we discussed above already, with a bit of a twist to make them a bit more robust: we're going to use the MATCH function instead of a hard-coded column number for the value we want to retrieve from the source table.
    5. Select C5 and enter the following function:
      =VLOOKUP(rngOrderId, tblOrders, MATCH(C4, tblOrders[#Headers], 0), FALSE)

      Here we've said find the Order ID that I've selected in the Orders table, and return the value from the column that MATCHes Customer.
    6. Just like C5, we're going to setup the rest of the normal VLOOKUP functions to return metadata about the order - this is all the "Master" data in this Master-Detail form:
      1. C6: =VLOOKUP($C$5,tblCustomers,MATCH("Address",tblCustomers[#Headers],0), FALSE)
      2. C7: =VLOOKUP($C$5, tblCustomers, MATCH("City",tblCustomers[#Headers],0), FALSE) & ", " & VLOOKUP($C$5, tblCustomers, MATCH("State",tblCustomers[#Headers],0), FALSE) & " " & VLOOKUP($C$5, tblCustomers, MATCH("Zip",tblCustomers[#Headers],0), FALSE)
      3. H4: =VLOOKUP(rngOrderId, tblOrders, MATCH(F4, tblOrders[#Headers], 0), FALSE)
      4. H5: =VLOOKUP(rngOrderId, tblOrders, MATCH(F5, tblOrders[#Headers], 0), FALSE)
      5. H6: =VLOOKUP(rngOrderId, tblOrders, MATCH(F6, tblOrders[#Headers], 0), FALSE)
      6. H7: =VLOOKUP(rngOrderId, tblOrders, MATCH(F7, tblOrders[#Headers], 0), FALSE)
    Figure 6: The completed Master section of the Master-Details form.

    Figure 6: The completed Master section of the Master-Details form.

    Right, so that's not too bad, and get's us something that's pretty robust, even if we add additional data to our source data tables - just as long as we keep the headings in our form matching the column headings in the tables. OK, so now let's move on to the "Details" part of the form - and this is where we'll get to find a solution to the VLOOKUP limitation of only returning a single value.

    Before we build up the actual solution, let's talk about some of the elements of the formula we're going to create:

    1. We're not actually going to use VLOOKUP! Since we need to return multiple items, what we really need is a way to return an array (list) of values, and VLOOKUP just doesn't allow for that. Instead, we'll use the INDEX function to return the value at a specific row and column intersection.
    2. We need a way to designate which item in the array we want to show in the cell, and we'll use a combination of the SMALL and ROW functions to accomplish that.
    3. We want this to be robust in the same way that we made our VLOOKUP function robust - that is, we want to be sure that if we add additional columns to our source data, that these functions don't break. Just like with the VLOOKUP solution, we'll use MATCH to ensure this.

      Tip: A nice side effect of what we're going to do here is that this formula is totally fillable within the "Details" portion of the form, which makes it pretty easy to work with/edit.
    4. Lastly, we want to have a bit of error handling - specifically, we'll use IFERROR (a new function in Excel 2007) to ensure that we just show a "blank" cell if there is an error in the calculation.

    OK, so without further ado, let's see that function, as it exists in cell C12 (note that when you enter this function, you enter it without the curly braces, and you press Ctrl+Shift+Enter to commit the cell, which adds the curly braces, making it an array function):

    Figure 7: Array function used to return the Nth item that matches a specific value.

    Figure 7: Array function used to return the Nth item that matches a specific value.

    That looks pretty complex, so let's take a look at what's actually going on here.  I've pretty printed sections of the formula for easier reading, from the inside out, since that's how Excel will ultimately calculate it:

                                  IF(tblDetails[Order ID]=rngOrderId,
                                       ROW(tblDetails[Order ID])-ROW(tblDetails[#Headers])
                        MATCH(C$11, tblDetails[#Headers], 0)

    Let's look at the SMALL function first.  Small works by taking an array (list) of values, and returns the Nth smallest value from that list. 

                                  IF(tblDetails[Order ID]=rngOrderId,
                                       ROW(tblDetails[Order ID])-ROW(tblDetails[#Headers])

    In this case, the array of values is determined by the IF function.  Specifically, IF the Order ID for a row in the Details table equals the Order ID I've selected in the drop down, then add the row number of that row (minus the row number of the heading row, in case the table doesn't start in row 1) to the array of values.  And, once the full Details table is analyzed in this way, return the 1st item in the array - ROW(1:1) returns 1.

    Now, you'll see a couple tricks that we've used here:

    1. Instead of ROW(1:1) to return the first item in the array, we could have just used the value 1 (or the value 4 if we wanted the 4th item in the array).  However, by using the ROW function, Excel will adjust the formula for us as we fill it down a range of cells, so the next row will contain ROW(2:2) for example.
    2. We used structured referencing to make the formula more robust.  We could have had cell references for the tblDetails[Order ID] and for the [#Headers], etc. but those would not have adjusted as the source table was modified.  By using structured referencing, we have a pretty solid solution here. (and more readable)

    OK, let's move on to the INDEX function next.  INDEX works by taking a 2d array (table) and returns the value that is at the specified row and column position in that array.

                                  IF(tblDetails[Order ID]=rngOrderId,
                                       ROW(tblDetails[Order ID])-ROW(tblDetails[#Headers])

                        MATCH(C$11, tblDetails[#Headers], 0)

    In this case, we know that the entire SMALL function returns the ROW(Nth) value in the list of Order Details that match the Order ID that I entered - so in this case the 1st value, which is the row number that we want to retrieve a column out of in the Details table.  The only thing left then is to specify the column number that we want to retrieve, which as we learned earlier is what MATCH is used for - in this case, taking the value in C11 and finding the column in the Details table that has the same name (Product in this case).

    Right, so we see a couple more tricks here:

    1. To specify the 2d array (table of data), we are again using a structured reference - tblDetails - as opposed to defining the array using a range reference (A1:G50 or whatever). This gives us robustness.
    2. Ditto with the MATCH function. We could have used the column number explicitly, but by using MATCH, we can much more easily make changes to the source table without having to worry about whether it will break the rest of our model.
    3. Lastly, you'll see that with the MATCH function, we've made it so that C$11 will adjust across, but will not adjust down - that will come in handy as we want to fill this formula to create our details list.

    And with that, all we're left with is the IFERROR function and the array function designators. IFERROR is pretty simple - it basically says: if an error occurs while calculating the stuff I contain, replace the error value with an empty string (""). The array function designators (curly braces) are what allow INDEX and SMALL to work over the entire Details table range.

    OK, so now that we've discussed the formula in C12, let's quickly finish up the form (and watch the magic of our robust formulas at work). With C12 selected, grab the little fill handle in the bottom right corner of the cell and drag it to H12.

    Ooooohhhhhh. Aaaahhhhhh. Notice how that C$11 auto-adjusted to C$12, C$13, etc. as you moved across.

    Next, with C12 - H12 selected, grab the fill handle and fill it down 10 rows or so. When you let go, you'll see, as if by magic, that all of the Order Details appear (as appropriate) and now as you change the Order ID at the top of the sheet, the entire Order Details updates.

    Figure 8: The completed details table, with the array formula filled across then down in order to retrieve all details.

    Figure 8: The completed details table, with the array formula filled across then down in order to retrieve all details.

    Pretty cool huh? We had to create a formula that was a bit more complex than just using VLOOKUP, but we also got a Master-Detail view working in a very robust way, using only built-in Excel formulas (no code, etc.).

    Of course, I'm sure there are other, equally slick ways of doing this - if you've got one, let's see it!

  • Microsoft Excel 2010

    Using PowerPivot with Excel 2010


    In this blog article, we’ll step through using PowerPivot for Excel 2010 for building a rich application in Excel.

    Note: following screenshots describe the SQL Server 2008 R2 August Community Technology Preview (CTP) functionality for a feature codenamed Gemini. As recently announced, Gemini will be released under the PowerPivot brand.

    After installation, Gemini appears on the Excel 2010 ribbon:


    Importing Data

    Selecting the ‘Load & Prepare Data’ button launches the PowerPivot client window:


    As you would expect from any modern tool, the ‘From Database’ button launches a wizard to step you through getting data from a database. More information about data import process and functionality is available on the PowerPivot blog here. For this article, we’ll bring in following tables:

    • Media – all movies our hypothetical company rents out,
    • Purchases – all purchases our customers have had in the last few years,
    • Date – details per date of purchases such as whether it was a weekend, holiday, etc.,
    • Time – time of day of purchases, and
    • BoxOffice – table of box office sales for some of the movies in the last few years

    During the import process, a snapshot of all these tables is imported into PowerPivot and stored in memory using a highly scalable engine. The table Purchase, for example, contains 100 million rows. Saving the file will not only save any contents on the workbook but also all PowerPivot data. i.e. the PowerPivot data is stored as a blob within the Excel workbook to simplify transport as well as remove the management overhead of managing separate database services.

    After the import, each table shows up as a tab in the PowerPivot client window. In this case, our database had relationships already defined between these tables and they were automatically recognized up by the PowerPivot Data Import Wizard. Users can, of course, define their own relationships as well.


    The PowerPivot client window allows you to operate with this large data set very quickly: common operations such as sorting and filtering typically complete in under a second on common hardware available today (< $1000).

    PowerPivot also allows you to extend imported data using calculations which are maintained through data refresh. Various types of calculations and mechanisms for creating them are described on the PowerPivot blog here.

    So far, we’ve imported data only from a single source. You can, of course, combine data from a variety of data sources – databases, text files, ATOM data feeds, as well as just Copying/Pasting data directly – and create calculations and/or relationships as if they were a table imported from a single database.

    Visualizing Data

    Once you have the data, PowerPivot enhances the Excel experience by providing quick templates for frequently used layouts:


    Selecting ‘Four Charts’, for example, creates a worksheet with four Pivot Charts and one worksheet each for the PivotTable source of the data, speeding up the view creation process:



    PowerPivot overrides the default PivotTable Field List by providing its own Task Pane:


    For those users familiar with OLAP Pivot Tables, there are several interesting features.

    Instead of seeing dimensions and measures within measure groups, PowerPivot shows a tabular view of the data – just tables and columns. In addition, based on whether a column is dropped in the Values area or on Axis or Legend, the PowerPivot Task pane creates a measure or uses the field as an attribute. For example, if Distributor is dropped  on the Values area, a measure (Count since this is a string column) is created automatically. If, however, Distributor is dropped on AxisFields, it is used to group the data. This highlights a few points:

    1. PowerPivot leverages the richness of SQL Services Analysis Services mode internally without imposing dimensional modeling concepts on users, thereby enabling the functionality of OLAP PivotTables with a friendlier, tabular, model of the data, and without requiring IT to create and deploy Analysis Services cubes.
    2. All PivotTable and PivotChart queries are answered by the PowerPivot engine running on the desktop using data that’s stored within the Excel workbook. A connection to a server running SQL Server Analysis Services is not necessary.
    3. PowerPivot complements the richness of Excel’s visualization facilities – including tables and charts – with a rich, scalable, embeddable, data engine.

    Within a matter of seconds a quick view is created…



    … which can be easily made more appealing using standard Excel formatting features:


    As mentioned earlier, saving the workbook at this point will save both the view above as well as the PowerPivot data and any enhancements such as calculations.

    PowerPivot also provides helpers for adding slicers to the workbook:


    The ‘Slicers Vertical’ and ‘Slicers Horizontal’ areas create zones on the left and top of the Pivot controls that help layout, align and resize slicers easily:



    The combination of usability of Slicers and the performance of PowerPivot engine provides a very interactive set of views that one could easily mistake for a rich custom application built by IT after weeks of effort. This – Self Service Business Intelligence – is the key value that Gemini provides end users.

    This completes a quick run through of PivotTable for Excel 2010. In the next article, we’ll dig in to PowerPivot for SharePoint to see how it enhances the SharePoint collaboration experience.

  • Microsoft Excel 2010

    Developer Tab


    Eric is out for a few days, so there is going to be a temporary break in the compatibility posts (they will resume sometime next week).  In the meantime, I thought I would quickly review a few tabs that I have not talked about yet and potentially one or two other items.

    Today, I wanted to briefly review the Developer tab.  As part of the general redesign of Office UI, a decision was taken that by default, features used primarily by solution developers would not be visible in the UI – they would be available on a separate tab, (the Developer tab) that the user could turn on and off.  The toggle for this tab is in the Excel Options dialog (same for Word and PowerPoint).

    Once the user has toggled the Developer tab on, they will see a seventh tab in Excel (at the end of the other tabs).  The Developer tab has three “chunks” – one for code, one for controls, and one for XML.

    Briefly, here is what is in each of those three chunks.

    The Code Chunk

    This chunk contains functionality related to writing VBA.  The Visual basic button launches the Visual Basic editor.  The Macros button launches the Macro dialog (no change from current versions), the Record Macro button does just that, and the Use Relative References button does, well, why don’t I just show you the “super tooltip”.

    Finally, Macro Security brings up the appropriate page in the Trust Centre to configure macro security levels.

    The Controls Chunk

    The Controls chunk contains functionality related to putting ActiveX and Forms controls on your worksheet.  Again, none of this functionality is new to Excel 2007, it is just nicely organized and presented in one place.

    The XML Chunk

    The XML chunk contains all the functionally related to working with XML files in Excel.  (This feature was added in Excel 2003 – see here for a brief overview.)  The feature has not changed in Excel 2007, but it is now included in all versions of Office (in Office 2003, it was only included in Office 2003 Professional, not the Standard edition).

  • Microsoft Excel 2010

    Excel 2010 Games: Missile Command and Tower Defense


     Thanks to Mauricio Acevedo, Nathan Shomber, Karen Cheng, and Manpratap Suri for putting together this blog post.

    It's official, Office 2010 is out! To celebrate, we've created two games that are playable in Excel for you to download.



       Missile Command                                                                       Tower Defense


    To play, you'll need -

    · Download games here

    · To install Excel 2010, hot off the press last week. You can get a free trial here. The games use features that are new to Excel 2010, so they won't work in older versions.

    When you open the files to play, don't forget to enable the macros.


    Here’s a video of the two games in action:

    In the next two blog posts, we'll explain how we made these games.

    And one more thing - for a look at the people behind Excel on the job, check out this video of Excel program managers, Karen and Manpratap, singing a song about Excel and Excel Services.

  • Microsoft Excel 2010

    Help us make Excel 2007 faster …


    Currently, the Excel development team is spending a lot of time tuning Excel 2007’s calculation performance to make it as fast as possible.  Given the near-infinite variety of things we see people build in Excel, we are always looking for good examples of workbooks that are calculation-intensive to help us compare Excel 2007’s calculation performance with previous versions’ performance on real-world files that matter to customers.  At some point last week it dawned on me that some of the Excel 12 blog readers might be in a position to help (given the number of comments and emails I have had from folks interested in or concerned about calculation and function speed).  So I’d like to appeal to folks to send in calculation-intensive workbooks they would like us to use as part of our performance tuning exercise.

    Specifically, we are looking for workbooks that take some time to calculate (say anything over 5 seconds).  Other than that, pretty much anything goes with respect to features used in the product.  (Please note that some features, like linked workbooks, UDFs, database queries, etc. may require more than just the file.)  Of course, anything submitted will be treated as 100% confidential and used only for calculation performance testing.  Folks can either email the files to me (with any explanations necessary) using this link:, or if email will not work, we can sort out other arrangements.  Once we have the workbooks, we baseline their performance in earlier versions of Excel and then run them on set of test machines (different CPU and memory configurations) using Excel 2007 and compare the results.

    The Office marketing group has made some prizes available, so the person that submits the longest-calculating model will get an interesting prize, as will the person that submits the model that exercises the widest range of Excel’s calculation features.  Double points for using more than 30 functions in a model or for using array formulas.  Thanks in advance to anyone that sends in workbooks. 

    PS Since this post has a limited lifespan, I will probably remove it at some point in the future.

    PSS Updated to include email link directly in post

  • Microsoft Excel 2010

    Add Buttons to the Quick Access Toolbar and Customize Button Images


    Today’s author is Ron de Bruin, an Excel MVP. You can find more useful tips from Ron and links to Excel add-ins at his website:

    This post shows you how to add a button to the Quick Access Toolbar (QAT) for one or all workbooks. It also shows an example of how you can change the image of a QAT button. One reason you might want to change a button’s image is that a lot of commands use the same button image (a green circle).

    For more information about adding missing built-in commands to the QAT or Ribbon see:

    How do you Customize the QAT for All Workbooks?

    Right-click on the QAT in the upper right corner of the Excel 2007 window, and then click Customize Quick Access Toolbar:

    In the Choose commands from dropdown make your choice, select the command you want, and then click Add:

    You can add buttons for all your favorite commands to the QAT this way.

    Tip: You can also right-click on a control in the Ribbon, and then click Add to Quick Access Toolbar.


    In the example shown above, I chose Commands Not in the Ribbon in the Choose commands from dropdown list. I then added the Add-ins command to the QAT. After doing so, you should see that your QAT looks like this now:

    If you select Macros in the Choose commands from dropdown, and then add a button for a macro to the QAT, you can use the Modify button to choose one of the 181 images available when using this dialog (there are a total of 1871 images, but more about that later). However, you do not have this option for the built-in commands from the user interface, such as the Add-ins command.

    Excel saves your QAT setup in a file named Excel.qat.

    In Windows XP you will find it here:
    C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\OFFICE\Excel.qat

    In Vista you can find it here:

    Note: The Excel.qat file will not exist if you have not customized your QAT.

    Tip: You can right-click on the green circle button for the Add-ins command, and then click Remove from Quick Access Toolbar to delete it.

    How do you Customize the QAT for One Workbook?

    You have the option to add commands to the QAT for all workbooks or only for one workbook. When you do this for all workbooks, Excel will save the setup in the Excel.qat file as described above.

    What if you want to customize the QAT for one particular workbook? Excel will not save this in the Excel.qat file, but will save the QAT setup in the userCustomization folder in a file named customUI.xml inside the workbook file.

    You can read more about this in the example for Change QAT Button Image below.


    Create a new file and save it as OneWorkbookQAT.xlsm

    Right-click on the QAT, and then click Customize Quick Access Toolbar.

    In the Customize Quick Access Toolbar dropdown, select For <workbook name>.

    As in the previous example, I chose Commands Not in the Ribbon in the Choose commands from dropdown, and then added the Add-ins command to the QAT. You should see that your QAT looks like this now:

    Note: The new Add-ins command button with the green circle is only visible when the OneWorkbookQAT.xlsm workbook is active.

    Just like customizing the toolbar for all workbooks, if you select Macros in the Choose commands from dropdown, and then add a macro button to the QAT, you can use the Modify button to choose one of the 181 images, but you do not have this option for built-in commands from the Excel user interface.

    Excel will save the setup in the userCustomization folder in a file named customUI.xml inside the OneWorkbookQAT.xlsm file. This folder will not exist if you have not customized the QAT for the workbook. You can read more about this in the example for Change QAT Button Image below.

    Change QAT Button Image

    Changing the QAT Images for All Workbooks

    Editing the Excel.qat to change images is not good option, so I will not show you how to do this.

    Reason: The Excel Customize the Quick Access Toolbar feature does not preserve manual changes made to the Excel.qat file. So, if you subsequently use Excel to reset the QAT or to add or remove commands from the QAT, the imageMso attribute will disappear.

    The only good option for all workbooks is this:
    Use the example above to customize the QAT for one workbook. Then, after you add the buttons you want to the QAT, save and close the OneWorkbookQAT.xlsm file. Excel 2007 files are really Zip files, so you can change the extension to .zip and open it in a third party zip program or use the default Windows Zip program.

    Important: I suggest that you make a backup copy of the original file just in case you break something.

    Change the extension of OneWorkbookQAT.xlsm to .zip now and use the default Windows Zip program to open the zip file.

    After you change the extension of the Excel file to .zip and open the .zip file you will see this:

    Excel will save the QAT setup in the userCustomization folder in a file named customUI.xml.

    Note: This folder will not exist if you have not customized the QAT for this workbook.

    Drag and drop this file out of the zip file on your desktop and open it in Notepad or another XML editor and you will see this:

    <mso:customUI xmlns:mso="">
    <mso:control idQ="mso:AddInManager" visible="true"/>

    You can change this line:

    <mso:control idQ="mso:AddInManager" visible="true"/>

    To this:

    <mso:control idQ="mso:AddInManager" imageMso="M" visible="true"/>

    Then save and close the file, drag and drop it back into the zip file, and say Yes to replace the file. Now close the .zip file, and change the extension back to .xlsm.

    If you open the workbook you see that the green circle is changed to an M now.

    Note: You can only use the built-in images provided by Excel. There are a few downloads on my ribbon page (see point 5 on that page) that will help you find the names of button images:

    Perform the following steps to make this customization available for all workbooks.

    1. Save the OneWorkbookQAT.xlsm file as an Excel Add-in. To do that, click the Office Button, click Save As, and then select Excel Add-in (*.xlam) from the Save as type dropdown.
    2. Name the file MyQAT.xlam.
    3. Close and reopen Excel, click the Office Button, click Excel Options, and then click the Add-Ins tab.
    4. In the Manage drop-down, click Excel Add-ins, and then click Go. If you don’t see MyQAT in the Add-ins available list, use the Browse button to locate the add-in, and then click OK.
    5. Verify that MyQAT is checked in the Add-ins available list, and then click OK.

    Now, every time you start Excel it will load this add-in with your custom QAT. This way you will not have the problem of your images disappearing when you add, remove, or reset the commands on your QAT.

    Copy a Customized QAT from one Workbook to Another (Add-in)

    If you have made a few different customized QAT's in your workbooks this add-in may be
    useful to copy a QAT from one workbook to another. There is a button on the Add-Ins tab to open the user form shown below.

    Download version 1.0 of the  RDBQAT add-in

    Note: This is the first version of the add-in, suggestions and bug reports are welcome.
    Check out this page once in a while to see if there is a update.
    Many thanks to Jim Rech for his suggestions and testing.

    More Information

    Change the ribbon in Excel 2007

    There are additional links in the More Information part of that page.

    There is a bug in SP2 when you add a button for one workbook. I have a few notes and a workaround on this page:

  • Microsoft Excel 2010

    Introducing PowerPivot


    Today we have a guest author from the SQL Server Analysis Services team, Ashvini Sharma, to tell us about the PowerPivot (née Gemini) feature that you may have heard about recently.

    PowerPivot is the recently announced name of technologies this blog previously referred to by its codename, Gemini. This article describes why there is a need for such a tool, and briefly what PowerPivot provides. More information is available on the PowerPivot blog.

    The Need for PowerPivot

    PivotTables continue to be indispensible for allowing users to analyze their data flexibly and interactively. If you’re a subscriber of this blog, you’ve already read some of the recent articles on investments the Excel team continues to make around PivotTables for Excel 2010.

    However, using a PivotTable that connects to an OLAP data source of course requires such a data source to exist. While a corporation may have many OLAP data sources where a single version of the truth and a unified model for looking at the business is necessary, this is not always the requirement.

    For personal or workgroup-oriented solutions, our customers tell us there’re shortcomings in technology available: 

    1. Requires advanced technical knowledge: Creating OLAP cubes is a non-trivial effort which requires highly technical understanding of concepts such as dimensions, measures, MDX, etc. As such, IT staff is frequently called upon to create such models on behalf of business users.
    2. Incurs higher cost to solution: Since IT groups have limited bandwidth, only a few of an organization’s analysis projects get the necessary attention and resources. In order to increase efficiency, IT may also attempt to consolidate similar solutions, which incur higher coordination cost and increased time to delivery.
    3. Produces solutions that are hard to customize: Business users frequently ask for data sets or analysis paths that they could not have predicted earlier. This is typical of ad hoc analysis that PivotTables support – an answer frequently leads to the next question and it is very hard to predict all possible questions, and time consuming to bake them in the model a priori. In addition, some data, may be so specific to a business problem that one user of the model may have it on their desktop, and it is not appropriate to share it across all users of a cube.
    4. Increases cost of ownership and friction: Some business teams hire technical consultants or volunteer one of their own to take on this “burden”. Unfortunately, this responsibility goes beyond learning new technology into also developing skills and devoting time for managing and maintaining any delivered solutions. In addition, IT stays unaware of such underground applications and get rightly concerned about business decisions being made on solutions not supported by them.

    Lets take a step back to make a few key observations:

    • A significant gap exists between an organization’s need for deriving insights from their data and the organization’s capacity to satisfy that need.
    • IT and business user resources are being stretched beyond their natural competencies: IT has to become more familiar with business users’ domain, and business users need to become more technical so that they can “speak” IT.
    • In our view, what’s missing is simply technology that allows business users to help themselves while providing visibility to IT, a scenario we call “Managed Self Service Business Intelligence”.


    The PowerPivot functionality is delivered by SQL Server’s Analysis Services team in collaboration with the Excel team and is based on our experience delivering the Microsoft Business Intelligence platform over the last decade.

    There’re two components of PowerPivot: PowerPivot for Excel 2010 and PowerPivot for SharePoint 2010.

    Designed for business users, PowerPivot for Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the application users already know and love — Excel. Leveraging familiar Excel features, users can transform enormous quantities of data from virtually any source with incredible speed into meaningful information to get the answers they need in seconds. PowerPivot for Excel consists of the following components:

    • The Excel 2010 addin that delivers the seamless PowerPivot user experience integrated within Excel.
    • The VertiPaq engine that compresses and manages millions of rows of data in memory with blazing fast performance.

    PowerPivot for SharePoint 2010 enables end users to effortlessly and securely share their PowerPivot applications with others and work seamlessly in the browser using Excel Services. PowerPivot for SharePoint also helps IT improve their operational efficiencies by tracking PowerPivot usage patterns over time, discovering mission-critical applications, and improving system performance by adding resources. PowerPivot for SharePoint consists of the following components:

    • PowerPivot Gallery – a Silverlight based gallery where users can share PowerPivot applications with others and visualize and interact with applications produced by others using Excel Services and Reporting Services.
    • PowerPivot Management Dashboard – a dashboard that enables IT to monitor and manage the PowerPivot for SharePoint environment.
    • PowerPivot Web Service – the “front-end” service that exposes PowerPivot data via XML/A to external applications such as Report Builder.
    • PowerPivot System Service – the “back-end” service that manages the PowerPivot application database, load balancing, usage data collection, automatic data refresh, etc.
    • Analysis Services – the Analysis Services server running the VertiPaq in-memory engine and integrated with SharePoint to load and manage the data within PowerPivot workbooks.

    We’ll drill into these features in the next few blogs. Stay tuned!

  • Microsoft Excel 2010

    Find Combinations


    Today’s author is Bill Seddon from Lyquidity Solutions, who informs us about a free tool for Excel users that allows you to find combinations of numbers that total to a selected value. For more information and a video, see

    Imagine you are an auditor or cash accountant and need to reconcile cash balances to their invoices.  Which combination of values makes up each cash value?  Not surprisingly, the Find Combinations add-in has been popular with the audit firms.


  • Microsoft Excel 2010

    Excel 2007 investments in UDFs #1


    For the next few posts, we have a “guest post” from Danny Khen, a program manager on the Excel Services team.  Danny is going to talk about UDFs in Excel and Excel Services.  Enjoy.

    UDFs are user-defined worksheet functions – custom functions that you create to supplement Excel’s set of intrinsic worksheet functions. UDFs are used to create calculation libraries, or to import data into Excel sheets in custom ways.

    In Excel 2007, we’ve made a number of key investments around UDFs. They revolve around two main areas: allowing UDFs to take advantage of important Excel improvements, and extending UDF-based Excel solutions to the server side with Excel Services.

    Updated XLLs

    A while back Dave mentioned that we updated XLLs (Excel’s addins based on the C-API) to give developers access to new Excel functionality. As that post explained, one of the common things that developers do in XLLs is to implement UDFs. We wanted to make sure that XLL authors can make use of some great new features of Excel 2007 itself in the UDFs they create. To recap, XLLs have support in Excel 2007 for:

    • The bigger grid
    • More function arguments
    • Multi-threaded calculation

    There are many more detailed about those improvements in that other post.

    Server-side UDFs

    Dave has also posted a number of entries about Excel Services – the new feature in the Office SharePoint Server 2007 that enables calculation, display, and exploration of Excel workbooks on the server. These posts cover many aspects of Excel Services.

    Much like Excel’s ability to be extended by writing UDFs in Excel addins, Excel Services also has an extensibility mechanism for writing UDFs. I’d like to use the rest of this post to explain and demonstrate Excel Services UDFs. In a follow-up couple of posts, I will show how you can create Excel solutions that use UDFs and can run both on a client machine using Excel 2007 and in a server environment using Excel Services.

    They’re managed

    Server-side UDFs are implemented as methods .NET 2.0 assemblies. That is to say, Excel Services directly supports only managed code UDFs. Existing native function libraries and Excel UDFs can be used with Excel Services by “wrapping” them with the new style of server managed UDFs; I will show how in the follow-up posts.

    But why did we actually “go managed”? Excellent question. Managed code for enterprise-level solutions is becoming more and more popular, because of the many advantages that .NET code has to offer. Robustness and security are among the important advantages. Some of you may already be engaged in developing managed UDFs or other types of managed solutions. With Excel Services, we focused specifically on server stability, and we felt that using .NET as the basis for our extensibility would be the right thing to do in this respect.

    They’re part of a V1 feature

    In Office SharePoint Server 2007 we introduce the first implementation of Excel Services and of its extensibility mechanism. To start with, we simply had to prioritize the support for various features in Excel Services (even regardless of UDFs), and not everything made it for this first version. Moreover, we know that going forward we will be doing a lot of thinking around managed interfaces and extensibility for Excel – both on the client side and on the server. We want to make sure that customers' initial investment in managed UDFs is secured, and that at the same time we are not constrained in any way when we design the best possible infrastructure in the future. For those reasons, there are certain restrictions with the first version of server UDFs:

    • No Excel OM: the entire interface with the Excel sheet is done thru the UDF call signature; arguments are passed into the function from the Excel formula, and return values are passed back into the formula.
    • More restrictive than Excel addins with
      • Type conversion and supported data types.
      • Error handling: all exceptions thrown by the UDF code are returned into the Excel sheet as #VALUE errors.
    • Simple load / runtime behavior: for example, all UDFs currently run together with Excel Services code in the same .NET application domain.

    We believe that lots of useful solutions can be created under these restrictions; in fact we see many existing Excel UDF-based solutions that could comply.

    They need to be thread-safe

    Excel Services is a server feature, and its calculation engine runs as a multi-threaded backend service. We have to require (and assume) that all UDFs are thread safe.


    A couple of things to mention about the way a server administrator can control the security of Excel Services UDFs.

    First, Excel Services will not load and run just any old method in any old .NET assembly. An admin needs to register the assembly on a server list of trusted UDF assemblies.

    On top of that, since these are .NET assemblies, an admin can make use of .NET’s CAS (Code Access Security) infrastructure to restrict UDF access to resources. For example, if a UDF package is simply a collection of math calculation functions, an admin can turn off its ability to access web services, external data, native code etc – and make the server environment safer for everyone.

    So what does this thing look like?

    Very simple, actually. All you need to know about (assuming you speak some .NET dialect) is two new attributes. Both of them are defined in the Microsoft.Office.Excel.Server.Udf namespace, and you need to reference an assembly that is shipped with Excel Services (Microsoft.Office.Excel.Server.Udf.dll) to get them.

    The two attributes are UdfClass, which you use to mark a class where server UDFs are defined, and UdfMethod, with which you mark each individual public method to be considered a server UDF. The UdfMethod also has a boolean property – IsVolatile – used to declare the UDF as volatile, if you want the server to call it each time it recalculates the workbook, regardless of any change in its dependencies. The default is false, which means that the method is non-volatile; it gets called only when a value changes somewhere down the dependency chain of the formula that calls the UDF.

    That’s really it. A typical UDF class will look something like this:

    using Microsoft.Office.Excel.Server.Udf;

    namespace YourNamespace

        public class YourClass
            public <return-type> NonVolatileMethod(<arguments>)
            public <return-type> VolatileMethod(<arguments>)

    Show us the goods!

    Well, alright… Attached to this blog post is a fully developed code sample. It shows a UDF package by the name of WishExcelHad (because it implements a couple of functions that we wish Excel had…). The package has two method to manipulate text strings with words. The first method, WehWordM, takes the string, a required word position, and a delimiter string, and returns the word in that position after parsing the text with the given delimiter. The other method, WehWordcountM, takes a string and a delimiter, and returns the number of words in that string, when it is parsed with the given delimiter.

    You can use the attached Excel workbook as an example that calls these two methods in formulas. You’ll need to save it to Excel Services as an XLSX file.

    Why the weird “M” as the suffix for those method names? It designates “Managed” – these UDFs are implemented purely with managed code, and are intended to run with Excel Services. Stay tuned to the blog and look for the following posts, where I will show versions of the same UDFs, that can run across Excel 2007 and Excel Services.

  • Microsoft Excel 2010

    Hidden and Invisible Objects


    Today’s author, Ben Rampson, a Program Manager on the Excel team, talks about cleaning up spreadsheets.

    I often receive files demonstrating issues customers are experiencing with Excel.  Recently I have noticed a common problem in some of these files that impacts the file’s performance and size: hidden and invisible objects.

    When gathering data in Excel it is common to start with information found in a variety of sources, frequently lists or tables located on the Internet.  Customers often copy and paste this data into Excel from the original source, but unfortunately this can also unintentionally paste many additional objects into the spreadsheet besides the data.  These objects (shapes, text boxes, controls, etc.) are often not noticeable to the user after the paste, but can result in slower performance and larger file sizes.  One step customers can take to avoid unwanted, hidden, and invisible objects in their document is to find and remove the objects after the paste operation as part of their data cleansing process.

    Below is an image of part of a data set I copied and pasted into Excel 2007 from a website. The data set had 35 rows of data, but only the first few are shown.

    After pasting my data (and extra objects) into Excel. 

    After pasting my data (and extra objects) into Excel.

    Looking at this image it is evident that some additional shapes were copied into Excel; an icon is shown in cell A1 and there are checkboxes in many of the rows in column B.  I could select these visible objects and delete them, however, I would end up missing some of the objects that have been pasted into my sheet.

    The following steps will help you find and delete all of the additional objects in your Excel 2007 worksheet. 

    The first thing you want to do is verify that you have additional objects on your spreadsheet.  The easiest way to view a sheet’s objects is to turn on the on the Selection Pane (on the Home Tab go to the Editing Chunk > click the Find and Select Dropdown > select the Selection Pane option making the pane visible).  Looking at the selection pane you will see a list of all objects on the current sheet and an indication if the objects are visible or hidden.  If the selection pane is blank then you do not have extra objects on the sheet.

    The top of the Selection and Visibility Pane for my sheet.  Notice that 100 objects were pasted into my document. I have seen customer files with thousands of hidden objects.

    The top of the Selection and Visibility Pane for my sheet.  Notice that 100 objects were pasted into my document. I have seen customer files with thousands of hidden objects.

    Once you have the selection pane open and verified that you have objects on the sheet, the next step is to put your spreadsheet in Design Mode if it is supported on your current workbook.  Design Mode will only be enabled in Excel if your sheet contains certain types of controls.  Turning on Design Mode will allow you to select all controls, not just the basic shapes and form controls.  To enter Design Mode select the Design Mode button in the Controls Chunk on the Developer Tab. If this button is disabled in the Ribbon then your sheet does not contain the types of controls that require this step.  (Note: If you do not have the Developer Tab visible in the Ribbon you can enable it with the following steps: Office Button > Excel Options > check the “Show Developer tab in the Ribbon” checkbox on the default Popular tab). 

    You now are ready to select objects using the Go To Special dialog.  To select all objects in the sheet: Ctrl+G to open the Go To dialog > select the Special button > select Object > click OK.  The objects will then be selected and their selection handles are visible on the sheet. 

    My sheet with all objects selected.  You can see that in addition to the previously visible objects I now also have selected some textboxes previously hidden on my sheet.

    My sheet with all objects selected.  You can see that in addition to the previously visible objects I now also have selected some textboxes previously hidden on my sheet.

    At this point you can simply hit the Delete key and remove all of these objects from the sheet.  You can also choose to be more selective about the objects you delete; if you have other objects already on your sheet you wish to keep, ctrl+clicking the objects in either the selection pane or on the sheet will remove them from the current selection prior to hitting delete.

    With just a little data cleansing work to you can avoid having additional unwanted objects in your document, resulting in faster performance and smaller file size.   

  • Microsoft Excel 2010

    PivotTables – overview of improvements in Excel 12


    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 (the name “PivotTable” is derived from the fact that the process of rearranging your data is known as “pivoting” your data).

    To illustrate the core capability of PivotTables, let’s imagine you have sales records listed in a worksheet something like this.

    (Click to enlarge)

    To see a sales summary, all you have to do is to click anywhere inside this range of data, create a PivotTable, and specify how you would like your data summarized.  For example, if you wanted to see Sales Amount organized by Product Category and SubCategory, it would look like this (UI is our Beta 1 build, in which the PivotTable UI is not final and in some areas not yet complete).

    (Click to enlarge)

    Without a PivotTable, summaries like these are typically built using formulas like SUBTOTAL, VLOOKUP etc.  However, in cases where you want to build more complicated reports, or where you want to look at the data in numerous different ways, or where the data itself changes quite frequently (i.e. Categories and Subcategories show up or disappear frequently), PivotTables are a great tool.

    I deliberately chose a small data set for the example above (and I demonstrated a fraction of what PivotTables can do), but as soon as you have even a few dozen rows of data that you want to summarize, PivotTables can deliver magical results (I love showing PivotTables to customers for the first time and watching their eyes light up).  For this reason, the PivotTable feature has been a very popular tool for quite some time, at least with those people who have learned to use it.  Unfortunately, there are a large number of users who are not yet aware of this feature, or have not figured out how to use it.  While planning for Excel 12, we did a lot of customer research and we found a couple of things.  First, we found that many users need the summary capabilities that PivotTables offer, but some currently consider PivotTables “too advanced” and don’t use them regularly.  Second, we found that users that did use PivotTables regularly had lots of requests to make them more powerful and more capable.  Finally, we found that customers that had adopted SQL Server Analysis Services as their business intelligence platform wanted great support for Analysis Services in Excel. 

    Essentially, it became clear that there was still a lot of additional capability that we could add in this area that would benefit all sorts of users, so we set out to improve the feature in a number of ways.  Specifically, we set out to:

    • Make PivotTables easier to build, read, and explore … make them more broadly accessible to any type of Excel user
    • Using the Ribbon and new dialogs to expose PivotTables’ capabilities to a much bigger range of users
    • Improve PivotTables’ visual appearance for presentation and printing … we wanted to make PivotTables look professional so that they can be used more widely for presentations and printed reports
    • Provide new filtering and sorting capabilities  ... making it possible for people to see exactly the data they need to see
    • Address top customer requests - many users have requested that we make it easier to change or modify the data source for OLAP PivotTables, retain formatting applied by the user across operations, make the PivotTables more readable, etc.  We wanted to address as many common requests as possible.
    • Make Excel 12 PivotTables a first-class tool for working with SQL Server Analysis Services data - the combination of SQL Server 2005 Analysis Services and Excel 12 provides business users access to tremendous amounts of corporate data, enabling them to quickly and easily answer a wide variety of business questions without assistance

    These goals translated in to the following work:

    • A new dialog for creating PivotTables that streamlines the overall experience
    • A new field list with checkboxes making it very easy to add and remove fields from a PivotTable
    • Drop zones in the new field list to make it easier to rearrange fields within the PivotTable
    • New expand/collapse indicators in the PivotTable to make it visually clear to users when there are more details to show
    • New filtering capabilities … we added Label Filters, Date Filters and Value Filters
    • Improved sorting capabilities … it is now possible to sort by the values in a specific row or column in addition to the grand total values
    • New layout options to make the PivotTable more readable and presentable
    • PivotTable-specific styles for making PivotTables look great
    • Better layout tools … for example, the ability to expand/collapse to any level of detail on rows or columns in the PivotTable without having to expand/collapse each level one-by-one
    • A fast, easy way to clear a PivotTable
    • New and improved contextual menus, Options dialog, and Field Settings dialog
      Specific for PivotTables connected to SQL Server Analysis Services cubes, we undertook the following:
    • Support for SQL Server 2005 Analysis Services “metadata” in the PivotTable Field List so users can find relevant data quickly and easily
    • Support for more Analysis Services features including key performance indicators (KPIs), named sets, drill-through, actions and server side formatting
    • The ability to hide any level of a hierarchy in an Analysis Services cube
    • Retaining user-applied formatting across operations performed on the PivotTable

    This is a pretty big, exciting area of work for our team.  Over the next two weeks or so, I am going to review all this in detail.

  • Microsoft Excel 2010

    Office 2007 Public Beta Available


    Starting today, those of you that are interested can download the public Office 2007 Beta from the Office preview site using this link:

    For those that do download and check things out, please let us know what you think.

Page 5 of 17 (423 items) «34567»