Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
The Excel Team Blog » All Tags » PivotTables (RSS)

Browse by Tags

  • Scatter charts with PowerPivot

    Rob over at http://powerpivotpro.com has a interesting post on scatter charts and PowerPivot: At right is a list of all chart types in Excel. But not all of them are supported as PivotCharts. Try to use XY (Scatter), Bubble, or Stock as a PivotChart, and you’ll get an alert saying you cannot create it with pivot data. So, do you give up? Nope. Remember the post where I converted a PivotTable to Cube Formulas using that button on the ribbon? This is a wonderful opportunity to use that feature in an...
    Filed under: ,

  • 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:...

  • 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...

  • A Few More PivotTable Improvements in Excel 2010

    Thanks to Diego Oppenheimer for putting together this post. In today’s post I will be covering a couple of smaller PivotTable features that we incorporated in Excel 2010. Most of these features have been longstanding customer requests or pain points that we felt could be addressed in this release. This includes fixing functionality that worked in versions of Excel previous to Excel 2007 (Filtering on calculated members) as well as making it easier to access features that historically our users have...

  • 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...

  • PivotTable Named Sets in Excel 2010

    Thanks to Diego Oppenheimer for putting together this post. When working with PivotTables, you often come across scenarios where you want to work with the same set of items from the data over and over again. For example, you might be a regional manager and you want to create a set of reports about the branches in your particular region, but the list of which are “your” branches isn’t in the data source, so you always have to filter to the same eight branches, like so: Example of two PivotTable reports...

  • Excel 2010: New Search Filter

    Exploring large data sets and creating filters has never been easier than with Excel 2010. Tables, PivotTables and PivotCharts now have a new search functionality that easily enables you to find what you need, filter and repeat for a faster more efficient way of navigating those enormous data sets. Filtering FAST! In Excel 2010 when you click on the dropdown for autofilters, tables, PivotTables and PivotCharts you will now find a new search interface that enable swift navigation of extremely large...

  • Sneak Preview of Project Gemini

    On the topic of “teasers”, one of the things we’ll be talking about in the coming weeks is project Gemini. I won’t get into the details just yet (otherwise it wouldn’t be a teaser), other than to say it’s a powerful data analysis feature, it’s an add-in to Excel (i.e. not a built-in Excel feature), and it’s the result of collaboration between the Excel and SQL teams. The BI Blog has been covering all news related to Gemini . From the BI Blog, here are a couple sneak peak videos : BI Power Hour: Sneak...

  • 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. 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...

  • Quick and Easy Data Access with Excel Services

    Today’s author, John Campbell, a Program Manager on the Excel Services team, shows us the quick and easy way to get external data access working with your Excel Services spreadsheets. I have seen a lot of great blog posts that tell folks how to do great things with refreshed data in an Excel spreadsheet using Excel Services. I’ve seen posts about slicing dashboard pages, about using .odc files to manage data, and more. One thing that continues to be an issue is how to get connected to that data in...

  • Using PivotTables to Analyze Data From a SharePoint List

    Today's author, Diego Oppenheimer, a Program Manager on the Excel team, talks about connecting PivotTables to data stored in SharePoint Lists. Many of us on the Excel team have been approached by customers asking us how to create a connection to a SharePoint list. SharePoint lists can be exported easily by creating a Microsoft Office Excel Web Query. The connection can then be tied directly to lists or PivotTables in the workbook like any other Data Connection. I have a SharePoint List that we...
    Filed under: ,

  • Using Office Data Connection files (.odc) and the DataConnections Web Part in SharePoint to Specify External Data Connections in Newly Created Excel Workbooks.

    Today's author, Christian Stich, a Program Manager on the Excel Services team, discusses how to enable users to quickly and easily create workbooks using external data connections specified in server based data connection files. Overview Excel and Excel Services support importing external data, which can be specified using connections embedded in the workbook itself or in Office Data Connection (.odc) files. One of the advantages of using .odc files is that any updates to the external data connection...

  • PivotTables: Calculated Items

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

  • Example file for PivotTable / Data Validation Trick

    Recently I posted an article discussing how PivotTables can be used on Excel Services to mimick the Data Validation feature . The author, Dany Hoter, sent me a copy of the file he used in the example screen shots and I never got around to posting it for our readers. Here's a short description of the example file from Dany: The example implements a scenario of calculating the pricing for car insurance based on the details entered in a form. The form can be collected on paper and entered manually into Excel or collected online and fed into Excel services to perform the calculation. Each value is connected to a numeric value. High values means less risk to insure this car to this driver. More driving experience, safer car, better neighborhood etc. Of course all data is completely fake and does not represent any real data. Each field might have a different number of possible values, some have only Yes/No and some might have 10 different values. All list of values where extended to the last row so to avoid blank appearing as an option for input. We can’t really prevent the user from choosing multiple options or the all option so an error message is displayed when the user does that. The file can be downloaded from here . Read More...

  • A PivotTable Trick That Brings Data Validation to Excel Services

    Excel has a feature called Data Validation that controls the possible values a user might enter into a cell or a range of cells. As you can see there are many options for validating data entry. The most popular is probably validating against a list of values. The list can be included in the validation definition or can be a region in the sheet referenced from the dialog. Recently I saw an example created by a customer that validates input using a PivotTable instead of data validation. The idea is to use only the report filter area of the PivotTable and to use the values selected in multiple filter fields as inputs for a model: In the example you see multiple input fields and when clicking the filter icon the user will see a list of values and can choose one. The advantage of this method is easier maintenance of multiple lists of values used for validating multiple fields. The range that the PivotTable is based on looks a bit odd because usually PivotTables are based on rectangular ranges...
1 2 Next >
Copyright Excel User Group and the relevant contributors, 2009. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.