The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,445 other followers

MS Excel 2011 for Mac: How to Change Data Source for a Pivot Table

Posted by jpluimers on 2018/08/24

Based on [WayBackMS Excel 2011 for Mac: How to Change Data Source for a Pivot Table:

  • Refreshing the data is in the “PivotTable” toolbar under “Data”: either
    • “Refresh” for the current PivotTable or
    • “Refresh All” for all PivotTables in the spreadsheet
  • Setting the range of source data is “Change Source” in the “PivotTable” toolbar under “Data”
    • If your data is on a sheet by itself, it’s better to select the range using column only notation than using column:row notation, compare these:
      • ‘FRITZ!Box_CallList.csv’!$A:$S
      • ‘FRITZ!Box_CallList.csv’!$A$1:$S$401

The last trick makes it way easier to add newer data from an external CSV file into an existing workbook with various PivotTable analysis worksheets:

  1. Append the CSV data to the source
  2. Copy over any formulas needed to make pivot life easier
  3. “Refresh All”

On a 1920×1200 screen, the PivotTable toolbar looks like this:

Excel 2011 for Mac PivotTable toolbar

Excel 2011 for Mac PivotTable toolbar

–jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: