Feedback by UserVoice

Akos Groller

My feedback

  1. 34 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    If we break the link to the data, the PivotTable will no longer be interactive and won’t support expand/collapse, filtering, or sorting. Copy/Paste into Paint or any other graphics program will preserve an exact picture of the PivotTable with all formatting. In any event, this item isn’t likely to be addressed soon.

    An error occurred while saving the comment
    Akos Groller commented  · 

    There is an ugly but pretty concise workaround for that.
    The key is that if you copy and paste only part of a Pivot table, Excel will implicitly dispose of the data linkage, furthermore it won't assume that the conditional formatting is for the Pivot report (thus not copying it when you request to paste values and formats).
    In most cases, you're well off copying first the page fields and headers, then in a separate step all the rest (row labels, data and column totals).
    You might also do the trick by adding a needless page field at the top (if you do have some unused field), then copying all except that top-row entry. Again, the Pivot link will break up and Excel will deem the formatting as applicable to the content, not the structure - as intended.
    Finally, if you need to detach in-place (e.g. your fancy conditional formatting links the surrounding areas), copy the entire sheet out to a new workbook, then copy back the pivot as values to break data link and make the range editable, finally copy back the pivot report in parts as above. There still can be cases when this messes up something (e.g needless localization of global named ranges), but often it'll do the job for you.

  2. 22 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Akos Groller supported this idea  · 
    An error occurred while saving the comment
    Akos Groller commented  · 

    Yes yes yes, MATCHIFS for President!

    It's very common that I only need a single match (I know from data logic there's only one, or all are equivalent for the purpose I need the record). Or the field I need might be non-numeric, this useless for the otherwise highly appreciated SUMIFS, COUNTIFS, AVERAGEIFS.
    Currently I need to add a RowNum field, pivot the data and extract Min(RowNum) as an intermediate step – OK, recalculates faster, but has memory overhead and does not allow for wildcards. (Concatenated key column, in turn, is just ugly...) When evaluation speed is not a major concern. MATCHIFS would be so much cleaner and more elegant.

Feedback and Knowledge Base