To download the workbook from this video: www.mrexcel.com/youtube/u9rgH5UPxwo/
@julielyon2135
21 күн бұрын
This has saved me so much time. Thank you.
@excelisfun
2 жыл бұрын
Rad VBA solution, Mr Excel!!!!!
@mathivanangunasekaran9020
Ай бұрын
Thankyou sir great trick. Is there a way to add multiple headings into fields values section at once.Please help sir
@chrism9037
2 жыл бұрын
This is a great solution Mr. Excel!
@gregorytaylor2749
2 жыл бұрын
Great tutorial!! Thanks!!
@TheDiegoLacerda
Жыл бұрын
Thanks for the code, this was very helpful. Just a contribution, in my attempts the code didn't work if the option "Add this data to the Data Model" was selected in the Pivot Table.
@ricos1497
2 жыл бұрын
interesting video. I'd do it one of two ways. If using VBA, I'd probably create a few shapes as buttons (right click, assign macro) and name them with the pivot name and action, such as PivotTable1_Max. I'd then just use a single macro, using application.caller to get the shape name, split by underscore. Thus: with pivot tables(split(appCaller, "_")(1)) . function= split(appCaller,"_")(2) end with because then I only have to write one sub, and I'm lazy that way. But more likely is that I'd go to the data model. I'd create a disconnected table with a list of sum, average etc, and then for each of my calculated fields, I'd use an if statement or switch to define which calculation I'd perform (default to sum or whatever). The disconnected table would be the basis for a slicer of calculation types, which I think would look coolest and work best.
@z.719
2 жыл бұрын
Amazing!
@benrogers9092
2 жыл бұрын
Another idea could be to use PowerQuery to "unpivot other columns", to get a [State] column for pivot table column labels, and then use a single field in the values box of the pivot table. Tricky pivot tables often stem from pivoted/non-tabular data sources :)
@MrXL
2 жыл бұрын
Thanks Ben. The OP only mentioned that he had 125 fields. Without knowing the actual fields, I guessed and used states. But there is certainly some chance that going back to the start of the process and unpivoting the data would make this easier. But... like most processes that have been growing out of control for years, the solution with the least friction is to keep everything in place and add a few extra lines of VBA code.
@sirewan
Жыл бұрын
Hi! This is a great solution to a problem that has been bothering me for quite a while! One issue (that you probably solved elsewhere): I added the shortcuts to my Quick Access Toolbar and as long as the "Personal.xlsb" file is open it all works well, but otherwise I get a Microsoft Excel Security Notice saying "Microsoft Office has identified a potential security concern." Is there anything that can be done to avoid this issue? Thanks!
@d9niel
Жыл бұрын
What about if you wanted to switch between the Sum of the Values & 'Show Values As' % Running Total In. So I can then see the values or a running total %?
@Dexcelo
2 жыл бұрын
Great trick !!
@kleinboertjie
2 жыл бұрын
Could you create a variable to loop through the table names if the table names are name "Table1", "Table2" etc to do it for the other tables as well by wrapping it around the while loop.
@MrXL
2 жыл бұрын
Great idea. Here is that code: Sub ChangeAllToAverage() Dim PT As PivotTable Dim PF As PivotField For Each PT In ActiveSheet.PivotTables ' Loop through any fields in the Values area For Each PF In PT.DataFields PF.Function = xlAverage Next PF PT.RefreshTable Next PT End Sub
@trulyrandom2
6 ай бұрын
BLESS
@dirkstaszak4838
2 жыл бұрын
Wouldn't the data model and power pivot the more sustainable design for such a problem?
@MrXL
2 жыл бұрын
If we could back in time to when this workbook started, that would be great.
@alexrosen8762
2 жыл бұрын
Wow! Great solution
@ezzathafez1585
Жыл бұрын
Hello, I tired this but when i run it, the macro will give an error with a message "run-time error 424" object required and this will reference this .NumberFormat = "#,##0" line. what do you think the problem is? BTW, when this happens my whole table will be vanished, as it didnt exist before
Пікірлер: 24