Stop Doing Manual Reconciliations in Excel: Use Power Query

all right hello and welcome to reconciliations my name jeff i'm glad you're here let's just jump right in now before i flip over to excel i want to zoom out and talk about our objectives we are trying to compare two lists we're trying to do a reconciliation we want to ask things like what values are on one list that are not in the other list or what values are in the other list that aren't in the first list or what values are on both lists to demonstrate this type of process i'm going to use a bank reconciliation as the illustration basically trying to automate these values outstanding checks deposit and transit bank additions and bank charges so we want excel to automatically calculate those values so that we can automate and streamline our bankrack process even if you don't do bankrecs the underlying techniques discussed will help with any type of list comparison where we're comparing two different lists we're going to go through these basic six steps and the sixth step is where it all comes together where we retrieve all these values to automate the reconciliation so you definitely want to stick around and check out that all right so i've exported a list of checks from my accounting system a list of deposits from my accounting system and a list of banking activity from the banking website the first thing we need to do is get these loaded into power query the way that we do that is select any cell within the table and go to data and from the get and transform data group we're going to use from table range all right this loads it into power query where i'm going to give this a more descriptive name i'm going to call this book checks if there was any transformations or cleanup i needed to do i would do it but this looks good so i'm just going to close and load two and here i'm going to save it as a connection only query and i'm going to click ok let's do the same thing with deposit select any cell from table range i'm going to give this a new name i'm going to call this book deposits and this looks fine the way that it is so i'm going to close in load two once again i'm going to save this as a connection only query and click ok and finally let's pull this banking activity into power query from table range and i'm going to call this bank all and i'm going to close end load 2 i'm going to save this as connection only query and click ok all right now our three data sources have been loaded into power query and now we can go through and create our lists so we're going to go and create all of this stuff and then the last step will be here okay first of all a list of cleared checks what this really means is which checks appear on both lists and we want to generate a list of these so the way that we're going to do that is we're going to compare this checklist to the banking activity but this banking activity has a bunch of extra stuff so we first need to kind of clean that up and prepare it to be compared to this checklist so the way that we're going to do that is we're going to right click on bank all and we're going to click reference and what this does is it creates another query that uses as its source the bank all query so i click reference and i'm going to give this a new name i'm going to call this bank checks and now we need to do some transformations on this to clean it up so that it can be compared to our book checks so the first thing we need to do is get rid of non-check rows so depending on the way your bank export comes you may need to do some additional transformations or some different transformations to identify just the check rows but in our case i'm just going to go ahead and apply a filter i'm going to say i only want to see the rows that begin with check and i click okay and by the way this is case sensitive okay now the other thing i need to do in order to compare this is i need the check number column to have the exact same format and data type as the books and the book uses just the check number without this check label so i'm going to go ahead and and split this column there's a couple different ways to do it i'm just going to split it by a delimiter and it detected space if it was something different you can pick something different and i'm just click ok and now i don't need this column here so i'm just going to remove that i'm going to double click to give this a more friendly label i'm going to call it check num and hit ok and now this is the same format and data type as my other list so i can compare it so what i'm going to do now is i'm going to close and load two once again i'm going to send this to a connection only query all right now to get the list of clear checks i'm going to go ahead and go to get data combine and merge and now i need to identify the two tables to compare so i want to compare my book checks to my bank checks and then i need to identify the common or lookup column this would be the column that we use in vlookup or x lookup so we just basically select the columns now these don't need to have the same label but they just need to represent the same thing and before i hit ok i do want to point out this join kind we're going to go in here and use different options during the rest of this video but i want to point out a couple of basic high-level ideas first of all the default is left outer join that just means i want every single row from the first list and bring in the matches if any from the second list and that could be fine and we'll use that and then i'll show you how to work it but there are other options for example we could say only the rows that match or only the rows that are found in the first so we're going to use a couple of these different options during this video for now let's just start with left outer this means i want to return every single row from the first list and pull the matching values that you can find based on these columns and i click ok now in here we have all of the rows from the first table and then we have another column that represents all the matching results from the second table in order for me to see this i'm going to click expand and i'm going to click ok and now we can see we have the check number from the first list the check number from the second list the amount from the first list the amount from the second list if i scroll to the bottom i see that i have certain rows with null what that means is they were found in the first list but there was no matches found in the second list since the thing i'm trying to create right now is a list of cleared checks that represents a list of values found in both tables if i wanted to remove these null values i could just use a filter to remove null and click ok and that would certainly be one option but there's another option so i'm going to go ahead and delete this step delete this step and go back to this gear and let's go ahead and revisit this match type what i really want is i only want rows that match in both tables so i'm going to select enter and then i'm going to click ok now this list is shorter and let me go ahead and expand this so i can see what's going on now i see i have no null records that just means this is a list of checks that are found in both lists i don't really need this column so i'm going to hit remove i'm going to rename this per bank and now what if we want to catch any situation where these amounts are different for example i wrote a check for one amount but it cleared the bank for a different amount well since my amount here is positive and this is negative i could add these if your banks pulled these in as positive numbers i could subtract these an easy way to do that is just select both columns click add column under standard select whatever you want here i need to do add so i'm going to click add and this gives me a new column of zeros i'm going to go ahead and call this diff and this would allow me to quickly see that all the amounts match if there were any non-zero values i could investigate that i could do that here in power query i could sort it i could filter it i could do that in excel once we get it back into excel but this will be a quick way to determine that the values from both lists match as well as the check numbers once that's done i'm going to go ahead and give this a new name i'm going to call this cleared and i'm going to close on load two here i'm going to send it to a table in an existing worksheet and click ok all right now we have a list of cleared checks excellent let's go to the next thing outstanding checks this means i'm comparing two lists i want to identify things on the first list that are not on the second list in other words i wrote a check but it has not yet cleared the bank so it's not on the bank list so how do we do that well same basic steps data get data combine and merge once again i want to compare book checks to my bank checks and the lookup column is checknum in this case i want to find the rows that appear only in the first table which means they have not yet cleared the bank they're not in the bank list so i select that i click ok let's go ahead and expand this so i can see what's going on i click ok these are all null what this is telling me is that these checks were found on the book and they were not found on the bank so i'm going to go ahead and delete all that stuff now i'm going to call this outstanding and i'm going to close in load two table existing worksheet and click ok all right now i'm going to go ahead and check the total row checkbox just in case i might need this total later and now let's go to the next one bank charges what this represents is deductions that are made by the bank that i might not be aware of in other words what are any negative values that aren't checks that are on my bank list so let's go ahead and prepare that list i'm going to right click and reference so i first need any negative numbers so i'm going to use this filter i'm going to say let's grab everything that is less than zero and i've already analyzed checks so i want anything that's not a check so the way that i'll do that is with the filter and i want to show the rows that do not begin with check and i click ok all right and i'm going to call this bank charges i'm going to go ahead and close and load two a table existing worksheet is fine i click ok we got that now let's go ahead and turn on the total row there we go okay next up is deposits in transit all right now we need to find deposits that are found on the book but not on the bank so what that's referring to is we want to find deposits that are made here but that have not made their way over here yet now before we get to that i do want to point out something here for this first deposit we made it on the first for three hundred twelve dollars it ultimately hit the bank on the fifth for three hundred twelve dollars so there's a little delay so there's not like a date or memo that i can line up i have to kind of look at the amounts the second thing to note is that sometimes i might have two transactions that hit the bank on the same day and they've been combined per the bank so this isn't always true but when it is you know how are we supposed to handle that well we can aggregate these values first so that we get the value of 831 so that it matches the bank so it kind of just depends on how your accounting system works but if we wanted to aggregate these first so that this would have a total that matches to the bank we can easily do that we'll modify this query let's go ahead and go over to book deposits i'm going to double click that to edit the query i'm going to select date i'm going to select group by and this is basically saying group by every unique value in this date column in other words summarize everything for each date and i want a new column which is called total and the operation is to sum the amount column and then i click ok and now this just aggregates or summarizes everything by date i'm going to go ahead and close and load and that's updated that one and now we need is a list of just the deposits so to do that i'm going to right click on bank all i'm going to reference that i'm going to call this bank deposits and basically what we're looking for is any row that begins with the word deposit and depending on how your banking activity goes you may need some additional or different transformations and i click ok with that done i'm going to go ahead and close and load two i'm going to save this as a connection only query and click ok ok now we can do the comparison get data combine queries merge i want to look at my book deposits and compare them to my bank deposits i don't really have a lookup field so i'm going to have to match based on amount so if the deposits are unique every day then this should be a really really good start if there's duplicate amounts or there's the same amount that hits on different days this would just be like a starting point so you can do some further inquiry but in this case i want to match based on a mount and i really only want the ones that appear only in my book because the other ones match so what i would say here is rows only in first and i click ok let's expand this click ok this is going to all be null values so i don't really need those so i'm going to remove them i'm going to call this dit deposits in transit i'm going to close and load to table existing worksheet's fine i click ok and let's turn on the total row okay and let's go to bank additions and this just means there's some type of addition or deposit that the bank made that i might not know about yet so to do that let's right click bank all and reference it use that as a starting point and what we're looking for is any positive amounts that aren't a deposit so what i can do is i can apply a filter and say i only want to see them if they are greater than zero and if they do not begin with deposit because we've already accounted for those and then i'm going to go ahead and rename this to bank additions and i'm going to close and load to a table existing worksheet click ok let's go ahead and turn on the total row all right so now back at the reconciliation power query has done all of this stuff so all we need to do is retrieve the values into the reconciliation so where's our outstanding checks it's equals this and enter where's our deposits in transit equals this and enter and where is our bank conditions equals this and enter and where's our bank charges equals this and enter and just like that our diff is zero and that is a beautiful thing now i already know what you might be thinking which is this jeff it seems much easier just to do this manually like with my ruler and my pen i got you it also might feel faster just to do it with something like vlookup and i totally understand that too but here's the beautiful thing about power query once we have those queries set up we really don't need to go in and change them anymore next period when we have new checks we paste in the values into this table we have new deposits paste we have new bank data paste then all we need to do is go to data and click refresh all what happens is power query engages it goes and pulls in all the new values it does all the transformations all the list comparison and updates the tables and that means really all we need to do is change our balance for bank and balance per book click refresh all and we're done and by the way in this short video we can't get into all the capability and complexity of power query but it's a very powerful tool and i want to let you know that in addition to looking at check data that's already in an excel table power query can also go and look at an external csv file so if you've downloaded that from your accounting system and saved it as a csv file a text file an excel file power query can go out and look at that as well and that's what's great about taking the time to set this up the first month is that going forward it is really fast to update it's just a refresh all power query does its thing updates all these values and then we're done so if you haven't really used power query it's bigger than what i can cover in this short video so definitely check it out if you use excel often be sure to check out the seven time hacks video this is a collection of seven time saving techniques that i wish i knew years ago and i hope this video will help you streamline your reconciliations or list comparisons where we have two lists and we're looking to see what's on one and not on the other or what's on the other not on one what's on both and those types of questions thanks for joining me hopefully this helped thanks have a great day this video is a production of excel university [Music]

Channel: Excel University

Views: 68,806

Keywords: Excel University, Jeff Lenning, excel course, excel tutorial, advanced excel, advanced excel tutorial, microsoft excel, ms excel, excel tips, excel tricks, excel, spreadsheets, business, power query, reconciliations

Id: fxm8AY8sUdU

Length: 16min 18sec (978 seconds)

Published: Wed Sep 28 2022