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 Outlook contacts – which took all of 5 minutes:
This is barely scratching the surface and doesn’t even begin to address to power Excel has gained over the last couple years with the release of PowerPivot , its DAX language and PowerView.
What’s “a fair share of Excel’s functionality” anyway? All I can say is that with Excel as will all things, the more you learn, the more you realize how little you know…
How many times have I needed to do this for reporting purposes? I remember coming up with some convoluted process that did the trick but was in no way as elegant as the solution suggested in this post (or the one in the comments).
You might find the two following posts interesting if you’re dealing with vast amounts of data:
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 again at vbaexpress.com and amended it slightly so that it applies to the currently selected pivot table only:
Dim pt As PivotTable
On Error Resume Next
Set pt = ActiveCell.PivotCell.PivotTable
On Error GoTo 0
If pt Is Nothing Then
MsgBox “No PivotTable selected”, vbInformation, “Oops…”
For Each pf In pt.DataFields
If Not pf Is Nothing Then
.Function = xlSum
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),””)
Now that’s a coincidence. As she searched for her umbrella just a few days ago, my wife suggested it would be great if there were some kind of gizmo that she could attach to objects so her iPhone would help her find stuff. Well it seems the guys at BiKN had the same idea. It’s interesting to note that their product is designed for the iPhone 4/4s, and Apple isn’t exactly making life easy for partners like BiKN as it switches to a larger form factor and a different plug with its iPhone 5… Oh well. I’m getting a Windows 8 phone a few weeks from now anyway.
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 are used transparently, providing data used in PivotTables, PivotCharts, and Power View reports. You can view, manage, and extend the model using the Microsoft Office PowerPivot for Excel 2013 add-in.
So yes, this basically means better integration of the current PowerPivot technology stack into Excel. The fundamental change, I believe, from an end-user’s standpoint is that it will be installed as a core functionality of Excel 2013 – without requiring the download of an add-in. Of course, this will enable the sophisticated analysis that you should expect of the xVelocity In-Memory Analytics Engine and the DAX language. But another benefit will be to allow many more users to leverage the power of Excel’s cube functions (it so happens that PowerPivotPro has another great article on that particular topic). xVelocity, together with cube functions and the slicers that appeared in Excel 2010 completely change the face of spreadsheet report design and ad-hoc analysis.
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 covered in Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model by Marco Russo, Alberto Ferrari and Chris Webb but I’m not even halfway through the book yet;-)
Austin Meyer, owner and developer of X-Plane, is now involved in the development of real avionics. Imagine a flight simulator installed as a piece of equipment in… a real airplane. Now imaging that, as you fly, the software in that little box “constantly simulates a glide down to every runway, in every direction, at every airport within gliding range of the airplane[…], simulating every possible choice you could make to glide down if the engine quit, and estimating a chance of success for that runway choice”. Well apparently that piece of equipment is real. The VP-400 Seeker can even take control of the autopilot if the airplane’s real pilot is incapacitated for some reason, although the product’s page does carry a disclaimer that “the VP-400 is for use only with experimental and light sport aircraft”. Still, it suggests a fascinating relationship between real and virtual worlds as far as I’m concerned.