PivotTables have been on my mind lately (you'll see why in a couple weeks). An issue you may face with PivotTables is how to change the source data for a PivotTable you've meticulously set up. You have some new data being added to your source data, and you have to change the PivotTable source data to reference the additional rows that show up at the bottom of your source data. This may not be a big issue for you because maybe you're not getting new data added often so manually going into the PivotTable settings and changing the reference to the source data doesn't feel onerous. If you have new data coming in every day or every hour, you may want to automate this process.

Here are a few methods to accomplish this in both Excel and Google Sheet. My preferred method is to turn your source data into a table in Excel or reference the entire columns in Google Sheets. Download the Excel file or copy the Google Sheets with the dataset for this episode.

Ramen ratings from ramenphiles

I'm a big fan of these niche datasets like the one for this episode. It's a list of ramen products and their ratings created by a website called The Ramen Rater. The list consists of 2,500 ramen products along with that product's country of origin, the style (Pack or Bowl), and of course the rating. It appears the ratings are all done by one person. More importantly, the list contains the full name of the ramen product which means you can do some interesting text analysis to see what words are used most often in ramen products, how words might correlate with ratings, etc. For our purposes, this dataset is a great for creating a PivotTable with the rating being the main metric to analyze.

Method 1: Reference the entire column

Excel PivotTables

As shown in the first screenshot, the source data for the PivotTable in the Excel file comes from the "ramen-ratings" worksheet from cells $A$1:$G$2581. As you add more data to the source data, you'll have to change the source reference to reference a higher row number. If you add 10 more ramen ratings, you'll have to change the PivotTable reference to $A$1:$G$2591. We want to avoid having to change the reference every time we add new data, so we can just reference the entire columns in $A:$G:

The problem is the PivotTable we have in the "Ramen Pivot Table" worksheet now has this "(blank)" item in both the columns and rows fields of our PivotTable. Why? Because we're referencing a bunch of empty rows of empty countries and ramen styles:

This isn't a huge issue, because we can just remove the "(blank)" via the row and column filters:

Now when you add new rows of ramen ratings to the source data and then you refresh the PivotTable, the PivotTable will automatically pick up all the new rows of data since it's referencing the entire columns from column A to column G.

Google Sheets PivotTables

The same solution applies to Google Sheets:

I find the user interface much easier to use in Google Sheets for a variety of reasons:

* Less clicks - Right when you click on the PivotTable (as shown in the above gif), you can see and edit the source data in the top right of the PivotTable field settings. In Excel, you have to click on the PivotTable Analyze tab in the rib...