Financial Reporting from Trial Balances - Super fast

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★
Sign up for our free Insiders program and get the example file:

★ About this video ★
For Financial Reporting it is common to create reports from Trial Balances.

In this video, we look at how to setup our workbook the right way, so that when we get a new Trial Balance we can update our reports with 1 click.

INCLUDES:
0:00 Introduction
0:30 Example files
1:28 Importing the trial balances (via Power Query)
7:45 Add the report mapping
8:56 Creating the formulas
10:48 Updating with 1 click
11:23 Wrap-up

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[ExcelOffTheGrid]

Sign up for our free Insiders program and get the example file:
File Reference for this video: 0225 Financial Reporting from Trial Balances

[IvanCortinas_ES]

Another true stroke of genius. Thanks for so many moments of glory, Mark!!

[Curic-Ivan]

One of best videos which has absolute purpose in everyday's practise.
Thank you man!

[BIGorilla]

Brilliant, lots of examples to learn from, and smooth talk. Enjoyed it!

[ivanmamchych]

I like the way you made the general changes in Transform Sample File query. It's very cool and efficient, much better then cleaning up the grid after combining of files.
Thank You, Mike
🤝

[simplejuan]

Very easy to follow. Thanks for the tutorial.

[evednavimedia]

Thank you so much! I implemented this on one of my reporting tasks and it works brilliantly!!!

[TheWorshipguitar]

I really like this video! It enlightens me a lot! Thanks much!

[mcwahaab]

Thank you, Mike, as always. I've been working on similar data using Power Query and Dynamic Arrays together, and I found this video quite relevant and helpful.

[Swizzletwice]

Excellent video and practice file. Thank you.

[duncanwil]

Lots of features and functions covered, which is obviously useful. I create accounts from a trial balance using a pivot table!

[haicalis]

I struggled to get the step for retrieving the date to work with the data I was using. I found if I transposed the column and row ref (in your example made it {3}[Column1] this fixed it.
Other than that, another very helpful video.

[AbdulKamara-tu]

Hello Mark

Great use of PQ, how did you get the balance sheet or TB to pick the latest balance of balance sheet items as those arent running balances like P&L but are as at a period in time.

[jeezzrom]

I’m actually doing this kind of report as a project but we need to be able to easily investigate variances when they pop up by drilling down at the account level without breaking up the report. Any thoughts? 😉

[ExcelWithChris]

Thanks. I have a question on bank statements. I will drop each new statement in a folder and use the above method to combine. The issue I have is that bank statements usually have the balance brought forward as the first line and maybe the balance left at the bottom. Obviously for the first statement you import you need the balance brought forward (and not the balance at the end), but for the next one you do not need the balance brought forward or the end balance. how do I solve this issue. Thanks.

[isabellelaporte]

Hi, I am looking at your website to pursue this reporting with P&L with monthly and year to date data. Would you have a link to the proper section. Thanks

[DataVisualisation]

... is that magic? You have the August 2024 trial balance data before the month is over. 😮

[bobbysilver]

Weird that at 6:45 you changed the date to UK format but then it showed as US format on the Power Query screen.

[financnifitness]

This was amazing!! Thanks a lot. Btw, is there a way to go back with some action in pivot table? Like CTRL+Z - seems does not work :D

[lomofatboy]

This is very helpful. million thanks.
I am stucked when add column with .csv File
There is no Navigation so when is the soruce name ?