Now this is interesting. I remember searching a few years back for spreadsheet design patterns. That combination of keywords never led me to the Best Practice Modeling Standards upon which BPM – a company founded in 2002 by ex-Salomon Smith Barney investment bankers – provides its modeling products and services. The base version is apparentlyContinue reading “Spreadsheet Design Standards”
Think again… I thought I knew my way around a fair share of Excel’s functionality but I’m not so sure anymore, having finally had a chance to play a bit with Microsoft’s new Excel add-ins: Data Explorer and GeoFlow. As a first test, I set out to create a map of some of my OutlookContinue reading “So you think you know Microsoft Excel?”
Very interesting post by Marco Russo related to tables in Excel 2013 and their relationship to the PowerPivot data model.
Ever needed to change a large number of pivot table data fields from Count to Sum? I used to have a macro just for that in my personal.xls file but my office workstation was upgraded a few weeks ago and I had forgotten to keep a copy of the code. I found the code againContinue reading “Changing all pivot table data fields to sum”
Posted here for future reference, here is neat little trick with Excel’s CUBE functions that enables building formula-based reports with dynamic members lists. Please refer to the original discussion on the discussion on the MSDN forum for context and additional ideas. =IF(ROW(A2)-1 <= CUBESETCOUNT(AbsoluteReferenceToSet), CUBERANKEDMEMBER(“Connection”,AbsoluteReferenceToSet, ROW(A2)-1),””)
Colin Banfield has posted at PowerPivotPro a great overview at of how Microsoft is implementing an advanced data model object right into into Excel. According to Microsoft: A Data Model is a new approach for integrating data from multiple tables, effectively building a relational data source inside the Excel workbook. Within Excel, Data Models areContinue reading “Not your father’s Excel”
This is really cool – I had completely missed it so far. The SQL Server 2012 release of PowerPivot for Excel includes several DAX functions (PATH, PATHCONTAINS, PATHITEM, PATHITEMREVERSE, and PATHLENGTH) dedicated to dealing with parent-child relationships. Kasper de Jonge (Program Manager at Microsoft Analysis Services) has a tutorial. Of course this is also coveredContinue reading “Parent-Child hierarchies in Excel!”
You may remember the day you were introduced to Excel’s pivot tables. It might have happened through a co-worker, a book or an online tutorial but the effect was probably along the lines of: “Wow! How did I ever analyze data without them?” Well, I sort of went through the same experience again several monthsContinue reading “Applying a complex Excel model to multiple input values”
You’ve probably noticed that you shouldn’t trust this blog for real-time news tracking. The following are essentially timeless, however, so here we go… Rob van Gelder (of DailyDoseOfExcel fame) shared a tip back in May on how to build a simple Gantt chart in Excel. I’m posting a link here because it’s the easiest I’veContinue reading “Data-related tips & tricks from around the web”
Andreas Lipphardt informed me that BonaVista Systems (or should we now say XLCubed?) are running an Excel dashboard competition. Participants can win an iPhone, a data visualization workshop or one of Stephen Few’s books.