Spreadsheet Design Standards

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”

So you think you know Microsoft Excel?

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?”

Changing all pivot table data fields to sum

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”

More fun with Excel’s CUBE functions

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),””)

Not your father’s Excel

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”

Parent-Child hierarchies in 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!”

Applying a complex Excel model to multiple input values

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”

Data-related tips & tricks from around the web

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”

Create your website at WordPress.com
Get started