Video instructions and help with filling out and completing Fill Form 2220 Accounting

Instructions and Help about Fill Form 2220 Accounting

Welcome to Excel campus my name is John and in this video I'm gonna explain how to automatically format the numbers in your pivot tables with VBA macros so typically when we add a field to the values area in a pivot table like if I add the revenue field here to the values area we get these numbers that are not formatted this is just the general number format we can see we have multiple decimal places here and it just looks ugly so I'm going to go ahead and remove the revenue field so I've created a macro that helps automatically format these numbers and to turn it on just right click anywhere in the pivot table and then we're going to choose this automatic number formatting option you won't see this until you install the macro but I'll just go ahead and click that right click again we'll see that it's now on so now when we add a field to the values area so again I'll add revenue we can see that it's automatically formatted and it looks great and this works with multiple fields in the values area as well so we're add a few more fields here maybe we add the shipping fee into the values area we can see that to automatically format it as well and maybe we want quantity over here and we'll do that and we get nicely look nice-looking automatic number formatting and you can see that the number formats are different here for each field and that's because the macro detects the number formatting in the source data range so if I jump over here to the source data range we can see that we have different number formats for each column and that's exactly what to bring being brought into our pivot table and then if for any reason those numbers are not formatted like the quantity column here just has the general number formatting the pivot table will detect that as well or the macro will detect that and then automatically apply some a default number formatting that you choose you can actually choose that in the macro so I'll go ahead and jump over to the BB editor here and there's a place where you can set the default number format I just have this very simple comma number format but you can choose any number format you'd like and this technique basically uses the pivot table update event a macro with a pivot table update event to run this code when you make an update or a change to the pivottable and it just looks in the pivot table in the values area or what are consider the data fields to see if the number format is general and if it's general it will change it to either the source formatting which is what's happening here changing it to either the source formatting or that default number format if the source formatting just contains a general format so there's really two ways to implement this macro first of all at the workbook that contains the macros there's a series of macros here that do this that workbook is available for free download below this video there's a link below the video where you can download that for free and then you can put the code modules in your personal macro workbook you really just drag and drop them into your personal macro workbook there's a class module and then one regular code module here that you can drag and drop in your personal macro workbook and once you do that and run a macro you'll get this option here on the right-click menu for the automatic number formatting and everything should work if you don't want to mess around with a personal macro workbook or VBA macros or anything like that this option is also available in the pivot pal add-in so the pen pal added is an app that contains a lot of features that make it easier to work with pivot tables this being one of them the automatic number formatting so you can just download and install the pivot pal add-in and then you'll have this feature here on the right-click menu to just press the button and you'll be good to go with the automatic number formatting and of course you can turn that off at any time as well so we click it again that turns it off you can right click here and see the button is off and now if we were to let's just remove the revenue field and add it back we can see that this sum of revenue column here is not formatted so you can toggle that on or off however you'd like which is really nice but it just saves a lot of time from typically once you add this field they have to right click go number format or whatever way you format the numbers depending on how you do it it can be multiple steps to get your numbers formatted nicely and get your pivot table looking nice this does it all in one step right when you add that field to the I use area of the PivotTable so I'm going to have a whole nother video that walks through the macro in much more detail and we'll jump into the VB editor and step through this macro so you can learn and understand how it works there's multiple macros in play here to get the right-click menu and all that stuff I don't want to spend more time and go into a little more depth on how that all works old another video on that but if you want to get started right away click the link below the video to download the file or check out the pivot pal add-in if you have any questions at all please leave a comment below the video and I'll