Stop Doing Manual Reconciliations in Excel: Use Power Query
Вставка
- Опубліковано 11 лип 2024
- Download sample file: www.excel-university.com/yt_f...
Are you spending hours every month reconciling your data in Excel?
There is a better way. In this video, I show you how to use Power Query to automatically reconcile your data, and save yourself hours of work each month.
Learning Microsoft Excel on UA-cam is the slow way....Learn Excel the FAST way, find out how here 👉 www.excel-university.com/yt
Subscribe here 👉 ua-cam.com/users/ExcelUniver... and turn on all notifications 🔔 for MS Excel time hacks that will make you more productive!
Check out 7 Excel Time Hacks Everyone Should Know here • 7 Excel Time Hacks Eve...
Watch VLOOKUP Hacks Playlist here • VLOOKUP Hacks Series
Watch VLOOKUP vs SUMIFS Playlist here • VLOOKUP vs SUMIFS Series
Watch Dynamic Arrays playlist here • Dynamic Arrays Series
Watch Treasure Maps playlist here • Treasure Maps Series
Watch Slow to Fast playlist here • Slow to Fast Series
Table of Contents:
00:00 Reconciliations
00:06 Objectives
00:50 Overview
00:58 Step 1 - Cleared Checks
08:03 Step 2 - Outstanding Checks
09:06 Step 3 - Bank Charges
09:57 Step 4 - Deposits in Transit
13:00 Step 5 - Bank Additions
13:45 Step 6 - Retrieve Values
14:14 Refresh Future Periods
15:40 Summary Recap
By far the most complete bank rec (lists comparision) i found on entire youtube so far!
i cant lie i had to watch the video for 2 days and on the 3rd day i was able to do the whole rec from start to end by myself with no mistakes. Excel and Power query is an amazing tool and Jeff is a great teacher! Kudos!!
I thought I wasn't going to learn Power Query. Your tutorials are well laid out and you explain it very clearly. Thank you! Your work is appreciated.
This process helped me with a credit card clearing account that has thousands of transactions a month. I had to create a funky index column because the amounts are the only things that repeat in each of the comparative systems (and the amounts can be repeated dozens of times), but it worked! Thanks!
Thank you so much for this informative video, it's very helpful
Thank you very much for the discussion, I have been looking for this topics!.. Thanks again....
My pleasure!
Awesome Mr. Jeff. Love it
Thanks :)
Great Video, Thanks!! 😀
Glad you liked it!
Best of video of what I was searching for❤❤❤❤
Thank you!!
Nice video, thanks for sharing!
Thanks for watching!
Thank you for your effective teaching style, which has greatly improved my understanding of banking reconciliation using Power Query. I kindly request you to create another tutorial featuring real bank statements and cash book data. Real statements are often less organized, requiring advanced skills for effective cleaning and organization with Power Query. In essence, a tutorial incorporating real bank statements and ledger accounts would be highly beneficial.
Excellent!
This is super... Thanks
Welcome 😊
Great
Very easy to follow steps, great presentation. Thanks for sharing, I am trying to combine multiple worksheets, each work sheets having different header columns. For eg,
Sheet 1 has A,B,C has columns.
Sheet 2 has B,D,E has columns,
Sheet 3 has A,CF has columns,
Sheet 4 has E,G,H has columns,
I wanted to merge all 4 sheets together A,B,C,D,E,F,G,H is this possible in Power Query? Can you please share a technique if there is any?
You can even reference other files as source data so you do not need to copy paste data in the next month just replace the source data files and hit refresh and all new data is loaded and transformed
Really great video and I have a question please? what if two different people created a sheet include names and i want to compare names but everyone writes the names by his own spelling for example jo and joe or johnn and john ..etc here the same data but different spelling and i have more than 50 names and all names not repeated again in the same sheet so i can't make it manually by replace so how to solve that because different spelling will affect the comparison and match formula
Hi Jeff big fan of your work and I am actually a student from Excel Campus and nice to see you here. Just wondering if the worksheet and data shared so that we can work along with you.
Thanks a lot
Thanks for your kind note :) I'm working on the written narrative w/file that accompanies this video, and once I post it to my website, I'll add a link here. It should be ready soon.
@@ExcelU Thanks a million, really appreciated
Hii jeff hope you are good,
Actually we do store deposit reconciliation and takes us alot of time,
Like we have a bank statement with store name and deposit statement with a store name too,
Now we have to match the values as well as write the date in which amount is deposited in bank,
Is there any simple shortcut for that?
A really good video; good moderate pace, good clear speech and reference naming (eg. transformations). I am now being exposed to Power Query in this vid and have a question - When do you know to use a "reference" to create "a connection only query" - What does "connection only query" really mean for transformation purposes?
And when to double click an existing query to "modify it". Overall, what's the difference between creating a reference to connection only query versus modifying a query? (Is it a connection to source data versus modifying source data?)
Also, when are you to use "Close & load" versus "Close & Load to"?
Hi Samantha, I’ve used Power Query some. A connection only query means that you have only loaded it to a connection within Excel, as opposed to loading it into a sheet as a table. There are a couple other ways to load the data, but I have only used “connection only” and tables. The connection only option is useful if you are going to reference it with it another query or if you are not finished and plan to finish and load the query to a table.
Close and load is going to default to loading all the queries as tables within your sheets. “Close and load to” is going to give you loading options such as a table, pivot table (if I recall right), and connection only.
i haven't tried it yet but I think I'm gonna cry, this will make reconciliation efficient. just have to be patient at first. just need to identify common data
Glad it will be helpful :)
could you share any other excel tool for doing bank recon. I felt this is complicated
Hi Jeff..Thanks for your content. However, every time I try to download the sample file I am blocked. Please help! Thanks Taiyab
Do you have similar vedios for AP/AR
Thankyou
You’re welcome 😊
Hi Jeff..thanks for the wonderful content. However, every time I try to download the sample I am blocked by Excel Universiy! Please help! Thanks Taiyab
Love this! My merged query keeps adding the total from one of my tables as an additional line item in the query result which is essentially doubling the total (except it adds another instance every time I refresh so the difference keeps getting larger) any ideas why this might be happening?
This happens because every time you refresh, power query will add together all the data from the tables in that workbook (including your merged query table). As a result, your merged query table will always be duplicated by itself anytime you refresh. To avoid this, copy your merged query table name, then using filters do a "DOES NOT INCLUDE" type filter in the applied steps to exclude that table from your merged query source data.
I have downloaded a ledger report Now want in insert a column and put each ledger name
to each transaction.
I downloaded your Excel spreadsheet (check register with reconciliation) I must say I very much impressed. However, I do a question I can't seem to find solution to. Is there any to display my values in two digit numbers or dollars and cents? I have looked at the Queries. They show to convert text to an Integer. If i change int 64 to number of course get an error.
Change value format to $
Hi there..Thank you so much for your content. So for odd reason everytime I try to download the excel workbook from the given link above I am blocked! Please remove this block. Thanks
👍
I am new learner but i find the video fast
how to reconcile if there is no common reference in both sheets or you cannot have any clue about the similarity in both sheet. For Example, your check sheet only have check numbers and your bank book have check numbers with something with Text also and you have separated checks from text and further you match them in query. suppose if both the sheets have different reference number but they must reconcile with value, then how to achieve that?
try matching on value then, and then check they refer to same transaction. maybe by date.
@@craftyboy2919 what if on the GL (books) its 3 or 4 separate amounts that make up a batch and that ties to the total amount that comes out of the back on one day with a different date because on the books it's posting with the Transaction date and the Bank Has a date of the Settlment date.
please share practice file.
Thanks for the suggestion about sharing the sample file. I have added the download here: www.excel-university.com/fxm8ay8sudu/
I also made a note in the video description. Hope it helps!
Thanks
Jeff
Hi there, i am follwing this but I do not understand what do you mean by "checks " is that an American way to say transactions ?
Hi there! Checks is a type of expense transaction, yes :)
जय श्री राम
These teacher take only simplest possible data to teach stuff. Why dont you get some actual bank statement and try to reconcile it.
You jumped through the steps too quickly.