Playing with Excel 2016

I’m working on a bunch of new projects, one of which led to an installation of 2016 Office. Oops, that wasn’t the intent but it became necessary to do so after an inadvertent purchase of a 2016 application. So now I’m getting an advance preview of 2016 products, one of which is Excel.

Already, I see some intriguing things such as pivot table’s ability to convert dates into plain months or quarters, automatically. Then there’s a button referencing a “Forecast” model. And there is supposed to be the Power Pivot embedded in all Excel 2016, not just some super-powered corporate version.

I played around with the Pivot table and Data Model. Last time I mentioned about Data Model, I said that you have to first create a pivot table before you can make a table within a spreadsheet part of a data model, even if you already made a pivot table and data model with another set of data within the file. Each time you want to include a new set of data in the file, you have to create a pivot table in order to add that new data to a data model.

In this new play, I tested the idea of combining or accessing two data models residing in separate files. Unfortunately, that doesn’t work… I think I’m not understanding how the data models can help. At the present time, all I see are useless pivot tables sitting around, just to create a data model to combine two sets of data.

I’m also working on or playing around with a new concept of a report or dashboard. The last few days I was working on making macros work for me. There’s a type of macros that are specific to just the sheet or tab and these macros start to run automatically after some kind of action is taken. There’s one where if you select a cell, a macro will start to run. Then there is one for when contents of a cell changes. And there is yet another for when the calculated contents of a cell changes. The last time that I was playing around with this, I was working with changing a filter in a pivot table. I couldn’t use a “change” type of macro so I ended up using the “calculate” macro where a specific cell (which contained a formula) changed when you change a filter. This week, I tried to re-use the “calculate” macro but it did not work. I had a cell that offered three options given by the Data Validation. This cell did not contain a formula so I could not use the “calculate” type macro. A “change” type macro had to be used.

There are some subtleties here.