Collaborate on Billing Statement in Excel for R&D with Ease Using airSlate SignNow

Billing statement in excel for R&D

hey there it's john with excel campus and in this video we're going to look at how to analyze bank statements with pivot tables and specifically when bank statements contain these debit and credit columns like you see here we're going to look at how to create a total of the debits and credits and how to create a summary report of that total with a pivot table so let's dive into it and again we have this bank statement here that's exported from a system or a bank and here we have a debit column that contains the expenses if this is for a credit card or a checking account be the expenses and the credits would be any refunds back or deposits back to the bank account so what we're going to do first is convert this to an excel table or insert an excel table before we create the pivot table in order to do that we'll just select any cell in the range there go to the home tab on the ribbon and then format as table you can just choose one of these styles here go ahead and hit ok and that will insert our table and i have a separate post that explains five reasons to use tables as a source of an excel of a pivot table i'll put a link to that in the description below this video the main reason being that we can quickly add columns and rows to our source range and not have to update the source data range of the pivot table so now that we have our table we're going to go ahead and summarize this with a pivot table just click this button here and we'll put that on a new worksheet and at this point we can first just quickly build out a pivot table that's a summary report of our data so for that we can take the category field here we'll put it in the rows area that'll give us a list of all of our categories here from the category column we can then take the debits and add that to the values area that'll give us a sum of the debit amounts for each category and we can also take the credits here and put that in the values area as well and that'll again give us a sum of credits for each category in this case now one question that often comes up is how do i add the debits and credits together to get a total here i want the total not just the debits and credits by themselves and there's a few ways to do that in this video we're going to look at two different ways to do that the first is using a calculated field so with any cell selected here in the pivot table we're going to go to the pivot table analyze or options tab and from this fields items in sets drop down we're going to choose calculated field that'll open up this window which allows us to create a calculated field which is essentially a field with a formula to do that math so for the name we can call this total total debits and credits anything you want you can put here the formula we're going to delete this zero and we're going to first double click the debit field so that'll add a reference to the debit field right here i'll then type a plus and then we'll double click the credit field there so that'll add the credit field as well you'll notice some extra spaces here you can keep those spaces or delete them it doesn't matter so once you have your formula we'll go ahead and hit add that'll add that to the fields list there we'll hit ok and now we can see that we have the sum of total column added to our pivot table it's also added as a field over here in the fields list the total here so we can remove it from the values area or add it back at any time it's also been added down here to the values area so we have the sum of total here and again this is going to give us the sum of the debits and credits and of course this is a grade because it essentially gives us the total expense or the total spending here for each category and of course the nice thing about a pivot table along with a calculated field is the flexibility so if we didn't want to see these uh the category over here in the rows area we could of course remove that now we're just going to see the total sum of debits and credits for the entire data set along with the total we might want to see this by date instead so we could put date in the rows area now we have the dates grouped by months here in the rows area we can expand that out if needed but this just gives us a different view and a different summary report very quickly and the calculated field still works across these different filter contexts or these different layouts or views with our pivot table and one other thing i should point out here is if we go back to the data set in this case here my credit column contains negative numbers again those are the refunds or the deposits back to this the account and these are negative numbers if that's not the case for you if they're positive numbers or maybe even the numbers are reversed here the debits are negative the credits are positive something like that you'll just simply reverse the math on the calculated field so instead of a plus we'll jump back over here to the pivot table go to the analyzer options tab fields item sets calculated field here we can choose from the drop down our total field and we can see the formula and this is where we can modify it so it's not a plus for you and your credits are a positive number then you might need to make this a minus instead so you can just make this a minus you click the modify button and that will save the changes in this case i'm not going to make that change so i'll just escape out of this now the other way we can solve this is with a calculated column and we'll add that back to the source data range so we'll go over here to our original table here we'll jump up to the top and here we're going to add a column with a formula so here we can do the same thing we call this total or i'm just going to call it amount there we go and hit enter and here we'll do the same math so we're just going to type a formula equals we'll select this cell here d2 and then in this case i'm going to do a plus and select this cell here e2 now even though that's blank it's just going to add those two numbers together and give us the result which is the total amount so we'll see that we have one column now with both the credit and debit number that gives us the total amount so here we have our negative number and of course our positive numbers where we have uh debits so now that we have this amount column we can add this to the fields in the pivot table so we'll jump back over to the pivot table and again since i'm using an excel table here that new columns automatically included in the pivot table all i need to do is right click refresh my keyboard shortcut is alt f5 to refresh the pivot table and then we'll see that new amount field or column over here in the fields list and of course we can add this to the values area as well so before i do that i'm going to remove the sum of total field here just so it's not confusing and i'll enter or i'll move the amount field over to the values area and so this is going to give us the exact same result the reason i removed the total field is that we do not need that calculated column this is an alternative solution using the i'm sorry we don't need that calculated field this is an alternative solution using the calculated column that we have here for sum of amount and again this has the same flexibility so if we want to remove these date fields from the pivot table the rows area and add the category field instead again we're going to see that we get the same result here so the advantage here of the calculated column is that we can see this number here and tie it out over here in our table so you might prefer this if you're doing some filtering here on your data range and maybe on your data set here maybe you just want to filter to tie out some numbers and of course then you can see that here in the amount column you can add that up or one quick tip here on the table design tab is to turn the total row on and then you'll see the total right down here for the filtered range so if you're tying out numbers to either your pivot table or some other summary report or even another reporter file altogether this is a great way to do it with this amount column because we can quickly tie out and check our numbers here maybe we want to go and exclude some items here some vendors or something like that you can obviously do that as well with the filters so i do like the calculated column technique for those reasons but if you don't need to see that or tie out numbers then the calculated field technique is a great solution because it's very simple easy way to add a new field to the pivot table and then one other quick thing i almost forgot to mention is that you do not need these debit and credit fields in the pivot table regardless of which technique you're using so we can remove the sum of debit some of credit fields out of here and we'll just still have the sum of amount of course with the calculated column this will work but it also works with the calculated field as well so we can just add the total in here that will again show us our total and we don't need to display the credits and debits in the pivot table so i hope this video helps you analyze bank statements and create quick summary reports with pivot tables of course if you have any additional questions or suggestions please feel free to leave a comment right below this video if you enjoyed this video hit the like and subscribe buttons below as well thanks again for watching have a great day and i'll see you in the next video you