Stop Doing Manual Reconciliations in Excel: Use Power Query

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.

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

[ThundrStrm]

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!!

[venividioptavi]

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.

[MaydayAggro]

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!

[MA]

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.

[Gorman-]

And... maybe I missed you saying it, but import table data from a .pdf file! Magic. works pretty well I think. Thanks!

[abdidekmuhumed]

Best of video of what I was searching for❤❤❤❤

[walidgamal]

Thank you so much for this informative video, it's very helpful

[terbasol]

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

[MaryGlorPingoy]

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

[silvestrecamposano]

Thank you very much for the discussion, I have been looking for this topics!.. Thanks again....

[AbuTaiyabAbuBakkar]

Hi Jeff..Thanks for your content. However, every time I try to download the sample file I am blocked. Please help! Thanks Taiyab

[rksenthilrajan]

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?

[AbuTaiyabAbuBakkar]

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

[Shobhaa]

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

[szabopal]

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

[samanthadaroga]

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"?

[anishchandran]

could you share any other excel tool for doing bank recon. I felt this is complicated

[abutaiyababubakkar]

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

[simbarashemakwati]

I have downloaded a ledger report Now want in insert a column and put each ledger name
to each transaction.

[Hyperth]

But power query I have restriction on row number. Whenever I load data in the power query editor, it shows that only 1000 rows are loaded. I have excel 2021 version. Can you suggest how i increase the row numbers so that It can load up 1, 00, 000 rows ?