auto update pivot table

[jitterbug888]

ive attached a demo pivot table here and want it to update, 

if it helps to put the pivot table on another page I will do that.

I want to be able to add new dates, invoices and $ amounts to the dataset and have it update in the pivot table..I thought this would be the case but it is not.

kindly,

[#2 · Accepted solution]

@jitterbug888 Just press "Refresh" after you have entered new data, then your number for January 2018 will be included in the PT.

[#3]

@jitterbug888 

Google has 60 million answers. Have you already tried all of them? 

[#4]

Thanks Riny!

[#5]

@Riny_van_Eekelen 

Follow up question to this issue. I've used Excel for years and have created pivot tables on numerous occasions. I'm running into some issues since I moved to Office 365 on my Mac. I'm using an iMac with the latest updates to Excel 365.

I have a pivot table that is not automatically adding one particular row that is something different from what's in the pivot table rows when I refresh the pivot table. (It used to work that way.)

The area of the data source has not changed - it's set up so that I have a major break for "project" and underneath that rows for "type". I added a new "type" today, and, thinking that since it is within the original data source, it would automatically populate when I refreshed the pivot table. 

What step(s) am I missing? is there a section specifically for Mac Excel users? 

Thanks,

[#6]

@dkinpgh1 Not sure I can relate to your question. I'm a Mac user myself and on Office365. I don't understand "- it's set up so that I have a major break for "project" and underneath that rows for "type"

Can you upload an example?

[#7]

best to attach your chart, or a copy of it without sensitive identity information. @dkinpgh1 

if its a table check the tables not broken, or as Riny showed me create a new sheet and start again have no gaps in your data table and generate the pivot table from it. Make a copy of your doc and give it a try.

[#8]

@Riny_van_Eekelen 

The pivot table looks like this: 

Office

dues

equipment

meals

Meeting

Software

subscription

Telephone

travel

Office is the first row, the other categories the second row and sit under Office in the pivot table.. The purpose of the pivot table is to sum all the expenses in each category month by month. I was trying to add the supplies category under Office but can't. I reset the data source but was still unable to pull "supplies" in to the pivot table. In past versions of Excel, that was a non-issue - it worked automatically as long as the data source included that row. 

[#9]

@dkinpgh1 Sorry, but I still don't follow you. Can you upload your workbook (with som fake data), but with a PT that has the problem that you described. Then I (or someone else) can have a look at it.

[#10]

@Riny_van_Eekelen Thanks for getting back - I've attached the spreadsheet for your review. I copied the data source from my original workbook into a new one and reconstructed the pivot table. As you'll see, the "supplies" row shows up here, but not in the original. I've reset the data source in my original workbook a couple of times but still can't get the Supplies row to fill into the pivot table.

I know I can copy and past the data into a new workbook, but I have about 15 years data plus pivot tables in the original. My last post showed how it looks in the original wb. This new Excel is really quirky - and the instructions on the MS Excel help page don't necessarily reflect what I'm seeing on my computer. If I were to guess, since you're a Mac user too, you see the same issues.

Thanks, Riny!

[#11]

@dkinpgh1 All I can see is that it works as expected in the schedule you attached. Difficult to judge why it doesn't work in your actual sheet without seeing that one. Understand you can't share it with all the live data, but perhaps can you copy the original and then just throw away most of the old data and keep just the latest 50 entries or so, like in the file you attached. Do a refresh of the PT but don't rebuild it. The data source should have updated it self. See what happens. Still wrong, then you can can perhaps upload that file.

[#12]

@Riny_van_Eekelen This is really peculiar and I'm spending more time on it than on billable hours!

I copied the original data onto another WB sheet in the same wb, now labeled 2020 expenses (2), changed the data source to that sheet and refreshed the PT. Still no supplies. Went back and changed the data source to the original sheet, refreshed the PT and still no supplies row. 

Just for giggles, I used the 2020 expenses (2) sheet, created a new PT on that page and, lo and behold the supplies row showed up and in alphabetical order. So, just for the experience, I changed one of the Type Expenses to "widgets", refreshed this new PT and it showed up. 

Tried on the original sheet, changing one of the type expenses to widgets, refreshed the PT and no joy. 

The spreadsheet I sent a little bit ago was from the original sheet copied into a new wb. I didn't rebuild it. I can't understand why it works on the copy I sent you and the copied page in my original WB. That's a significant issue, at least in my mind. 

Seems that I remember that there was a way in older versions to add a subfield by checking a box, for instance, right click on Office, and pick which of the fields to display - used to be able to not show blank fields for instance. Do I remember that function correctly?

[#13]

@dkinpgh1 Sorry, I don't remember the function you describe. Perhaps I never used it. Sorry.

[#14]

@Riny_van_Eekelen not a problem. At this point, I'm probably hallucinating. Now, I'm trying to eliminate blank rows from the PT. 

I appreciate your help, @Riny_van_Eekelen