skip to Main Content

Explaining the PowerPivot Calendar Chart, Plus an Updated XLSX Download

image

Hidden Rows and Columns Visible, Color Coded, and Explained
(Slicers Deliberately Moved Aside for Clarity)
(Click for Larger Version)

A Most Popular Post Indeed!

Well the CalChart post was a hit – the second most popular post of this year in fact.  (Second only to Dan Battagin’s spreadsheet formatting post, and that one had the benefit of being directly linked to from the official Excel blog – Dan is a big cheater).

I particularly enjoy how many Excel Pros are arriving at this blog for the first time as a result of the CalChart – you know who you are!  You’re helpless against the luxuriant charms of the CalChart! 🙂

And you have to have PowerPivot for it to work, muhaha.  Resistance is futile.  Go download it from Microsoft now.  It’s free.

Modifying it to fit your needs

The workbook I made available for download last week included a bunch of unused “machinery” – formulas and cells that I created while I was experimenting with different techniques, but ended up not using in the final version.

Read the Rest

PowerPivot Calendar Chart in Excel: Specific Steps for Adapting it to Work With YOUR Data

image

Modifying This to Work With Your Existing Workbook Isn’t Hard

Continuation

Given the continued popularity of the Calendar Chart and the post I did on its anatomy, I thought I’d continue today with a more pragmatic “how do I adapt this to work with my data?” post.

Adding the Calendar Chart to YOUR PowerPivot Workbook

OK, so you like the calendar chart but you don’t want to start from scratch in a new workbook?  You already HAVE a PowerPivot workbook and want to just “port” the calendar chart into THAT workbook?

It’s easy.  Probably a 30 minute task, and that includes the time spent reading this post.

Read the Rest

Better Way to “Catch” Multiple Slicer Selections in a Formula

 
It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas, IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas, IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas,
IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

A Popular Topic

No, I don’t mean things like “Fox Urine” or “Face to Anogenitaled” – those are pretty funny of course, and they come up in my job because I consult for my scientist neighbor on his lab rat projects.

But no, I’m here to talk about something even more popular than Fox Urine Smile

Every day, one of the most-read topics on this blog is some variant of “I want to catch slicer selections in formulas.”  This has been covered in at least three different posts:

  1. https://powerpivotpro.com/2010/06/use-slicer-values-in-a-calculation-with-powerpivot-dax/
  2. https://powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/ 
  3. https://powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/

There have also been a number of comments on each post that suggested alternate (and often better) ways of doing things.  I’ve never been terribly comfortable with that third post in particular, the one dealing with multiple selections.

Funny thing is, I used a new technique (for me) in the Calendar Chart posts, but never went back and called it out explicitly.  Today I am going to correct that omission.

Read the Rest

David Hager on Dynamic Conditional Formatting

Intro From Rob:  Greetings from Vegas!

Well ok, Vegas isn’t tons of fun when you don’t leave your hotel room very much, but hey, I have a great view of castles and downtown Manhattan.  I’m a little worried that this photo may open a wormhole into some alternate universe however:

image

Hey Wait a Second – How Is this Picture ALREADY in
the Blog Post Being Written in The Same Picture???  My Head Hurts.

Anyway, today David Hager has graciously stepped to the plate with a guest post.  He’s seen me goofing around with conditional formatting a lot and he’s got some advanced techniques to add to the mix.  Today’s post sets the stage for that.

It all revolves around the capability of CF (Conditional Formatting) to use formulas as the “decider” of whether or not to format a cell:

image

This CF Rule Type is Important to David’s Techniques

All right, take it away Mr. Hager…

CHANGING CONDITIONAL FORMATTING LOGIC USING DROPDOWNS

By David Hager

There has been a lot of demos and discussion on this blog relating to the innovative use of conditional formatting (CF), particularly in connection with PowerPivot.

https://powerpivotpro.com/?s=%22conditional+formatting%22

The technique of CF in Excel is especially powerful where formulas are used to define the desired TRUE/FALSE condition to be applied. It occurred to me that in this era of data visualization and controls (such as slicers) for altering the data view, there was no user method for changing the underlying logic of applied conditional formats. As an example, say that on a worksheet that cell D7 has a CF formula of =D7>8. There is no way to change the CF to =L7>8 unless the CF control on the Ribbon is opened and the formula is manually changed. Further, a CF formula with both conditions ( =AND(D7>8,L7>8) ) may be what is desired, and the user may not know how to do this. Thus, I decided to create a method that allows for the selection and changing of CF conditions from dropdowns using data validation lists.

Read the Rest