Advanced Pivot table tricks for Reporting and Analysis
Vložit
- čas přidán 30. 07. 2024
- Learn 6 advanced pivot table tips and tricks for Microsoft Excel, specially if you are involved in financial reporting and analysis
Email questions to: learnaf@outlook.com
Advanced P%L analysis Course:
Access full course, including presentation, practice files and solved Excel file for P&L analysis: ebitda.thinkific.com/pages/start
FP&A playlist: • FP&A - Financial Plann...
Take the poll: / @learnaccountingfinance
Looking for more personalized help?
Get in touch with me on one of these platforms:
Instagram: / learnaccountingfinance
Facebook: profile.php?...
Tiktok: / learnaccountingfinance
Website: www.learnaccountingfinance.com
Subscribe: czcams.com/users/LearnAccount...
Learn 6 advanced pivot table tips and tricks for Microsoft Excel, specially if you are involved in financial reporting and analysis.
Download the Excel file here: bit.ly/37Sfe4v
Some Recommendations:
Are you a First Time or New Manager? Check this out: amzn.to/35qWzLc
Learn pivot table data crunching business skills here: amzn.to/3lgYB5E
Learn all about Excel in one place: amzn.to/3laCXQx
Videos you may like:
Learn the right way to use Vlookup: • Vlookup Automation - D...
Fastest way to convert numbers stored as text: • How to Convert Numbers...
Learn the basics of pivot tables: • Using Pivot Tables to ...
Create automated waterfall chart: • Create Waterfall Chart...
Microsoft Excel pivot tables are a great tool for simplifying data analysis and reporting. It pays off to learn these advanced pivot table tips and tricks as it can save you hours in time, and also increases your accuracy. The advanced pivot table tips and tricks are a musk know for everyone involved in analyzing data or creating and presenting reports.
Apply custom filter using Grouping
Grouping to create Data Range or buckets
Grouping Dates using Pivot tables
Creating Pivot table calculated fields
Combining and analyzing two reports in different formats
Pivot table slicers and slicer connections
We will start with the powerful grouping function available with the pivot tables. We will look at different examples of grouping, including custom grouping when you want to create a new group that is not available in the source data. After creating custom grouping, you will be able analyze data at the new group level which is extremely useful.
Connect:
/ learnaccountingfinance
www.learnaccountingfinance.com
We will then look at creating data bands or data range groups, which helps with analyzing data by putting them in various buckets or range of values. this is a great way to analyze data (including creating a Histogram), and gives you a much deeper insight into the data.
We will also look at grouping dates in a pivot table. And then we will look at how calculated fields can be added to a pivot table further enhancing our ability to analyze and report information. The calculated fields work with the new new groups that we have created as well, hence making it a great and powerful tool for data analysis.
Course Alert:
If you would like to learn in detail, how to calculate income statement variances and the impact they have on sales $, profit $ and profit margin % and ebitda %, and how to explain performance vs budget and prior periods, click on the link for a detailed video course (at a special price). You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates. ebitda.thinkific.com/courses/...
We will then look at a trick that I learnt early in my career to combine to sources of data which are formatted differently but have some information in common, and can be analyzed together. An example is we have separate data set for actual results and budget, and we want to compare actual results with budget side by side, but since the reports are in different format, we are not able to compare them. Using the trick I share in the video, you will learn how with a small tweak of data you can analyze actual results vs budget even though the source data is different in format.
And finally we will look at pivot table slicers which are a great tool for filtering data and connecting multiple pivot tables together. Most of the Excel dashboards are created using the Slicers.
I hope you find the information and tips and tricks provided in the video useful and it helps you analyze data better, faster and more accurately.
Dont forget to hit thumbs up and subscribe to Learn Accounting Finance.
Here are top 13 Excel hacks for accountants and finance professionals ... czcams.com/video/_7ubAI1-moo/video.html
The two tables with different columns would be much quicker and easier to merge using Power Query. That would also mean updates to either table would be reflected in the merged table or pivot table once they are refreshed.
This is so useful, thank you!
Very insightful. Thanks
Excellent way of explaining,Sir. Kindly make a video on price mix
Thank you sir, your video has been most helpful.
Awesome job! Just learned a few tricks to maximize my time. Thank you!
Glad you liked it. Thanks
I really enjoyed the pivot table lesson. Thank you for sharing.
Thank you N Sanch01. Glad you enjoyed it.
Learned something which has worth
thank you for tht tips. i love pivot table
Thank you sachin. I love pivot tables too :)
wow excellent
Very useful sir nd yours teaching way is also unique sir 👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻💯
Thank you Vijay.
Great tutorial video!
Thanks
Great tutorial. However, when formatting data in a pivot table, using the standard formatting tools in the Home tab only formats the data in the selected cells. To format the Pivot Table itself, one should RIGHT CLICK on any cell within the data set and select NUMBER FORMAT. This will bring up the same format window as using the standard Format option, however there are no other tabs available for Font, Border, etc. This will set the Data (in this case "Sum of Standard Margin %") to the desired format, and will extend to all values displayed for that data no matter what row or column changes are made. This will update the entire column. While selecting all of the data in the column does work, setting the format for the data set is easier as only one cell needs to be selected. Note that if the column formatted using this is removed and then added back, the format will need to be set again.
Jerry Dellasala Thank you for the tip on formatting.
I think to some extent its a matter of choice as well. I like to remove and add back columns (play with the pivot table fields a lot). Using the Home tab formatting option retains the formatting for the columns, but its important to know the formatting option within the pivot table, and thanks for sharing this.
Thank you. This is a great tutorial. Could you please provide the excel file to download so that I can practice with your tutorial step by step to learn?
Thank you. Here is the link to download the file bit.ly/37Sfe4v
thanks...
Glad you found it useful Hossein
You explained very well & your data is superb ❤️👍👍👍👍❤️
Thank you Sunil.
Helpful
Thank you!
subscribed!
I do data analysis and this is very helpful. Can you post videos about set value as? those percentages their differences and when or what scenarios they are best used for? Just to be more accurate with my reporting.
Thank you for your feedback. I will keep this in mind for future videos.
Thank you so much for this - its incredible!
For the adding of budget info, couldn't you do a vlookup and add column to your actual table?
Thank you Queen of Hearts. Vlookup only looks at one lookup value. It may actually give incorrect results. Because you may be looking up using customer name, and it will find the first value for that customer and bring in that value, while there may be multiple other data cells related to the customer. We are trying to get multiple fields such as customer and part number and period data etc, so vlookup is definitely not going to do it. We could use "sumifs" depending on the size of data. I have noticed that using sumifs slows excel performance significantly if the formula is repeated large number of times.
There are definitely better automated ways of doing it. This is a lazy yet very effective way though.
Thank you for your explanation. But would you please share the workbook you are using.
That would be a wonderful way of practicing what you are explaning.
Thank you. Here is the link to download the file bit.ly/37Sfe4v
This is a great video on pivot table. would you mind if you provide the excel file to download so that every one can practice with your tutorial step by step to learn? Practice makes a man perfect. thanks in advance
Thank you. Here is the link to download the file Here is the link to download the file bit.ly/37Sfe4v
Thanks!
thanks SK KONG! Appreciate it!
Please do more
It's not letting me group that country section. What should I do now ?
Don't you have this workbook for us to practice? Thanks
Here is the link to download the file bit.ly/37Sfe4v
عالی
👍🏻👍🏻
Some things are not used the way they should in Excel 365... When In Excel Tables, Freeze Panes is no longer necessary, When Formatting shouldn't use the Home tab, To combine two sheets use the Auto-Connect tool by a primary key