Excel - Vlad Smirnov - See all Pivot Items - ModelOff - Episode 1839
1,659 views · Published 19 December 2013 · 3:24 · Indexed 4 November 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: How to Display Items with No Data in Pivot Table | ModelOff Finalist Shares Helpful Trick. Welcome to the ModelOff finals in New York City, 2013! I'm Bill Jelen and I'm here with one of the finalists, Vlad Smirnov, all the way from Moscow, Russia. Vlad is a financial analyst at TCS bank and he's here to share a helpful trick with us today. In this podcast, Vlad will be showing us how to display items with no data in a Pivot Table, a useful skill for financial analysts and Excel users alike. In this video, Vlad demonstrates how to calculate provisions for loans by multiplying balances with probabilities of default. However, the challenge arises when there are missing risk statuses for certain months. Vlad shows us how to use the Pivot Table to display all risk statuses for each month, allowing for accurate calculations and avoiding any errors. Vlad's tip is especially helpful for those using international versions of Excel, as he explains the difference in using semicolons and commas for formulas. He also shares his experience of using a different decimal point in Russia, highlighting the importance of regional settings in Excel. As a fellow Excel user, I appreciate Vlad's insight and I'm sure it will be helpful for many others as well. As we gear up for the ModelOff finals, Vlad will be using his own laptop to ensure he has all the necessary settings for his calculations. But for now, he's graciously sharing his knowledge with us on my podcast laptop. Thank you, Vlad, for this valuable tip and good luck at the finals! And to all our viewers, don't forget to like, comment, and subscribe for more Excel tips and tricks. Thanks for watching!" Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) ModelOff fifinalist Vlad Smirnov (00:16) Explanation of how to show items with no data in Pivot Table (01:08) Step-by-step guide to setting up Pivot Table (01:57) Calculating provisions for each month using SUMPRODUCT formula (02:22) Discussion on using commas instead of semicolons in international versions of Excel (02:54) Preparation for ModelOff finals and using personal laptop (03:08) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Calculation of provisions for loans Fill Settings in Pivot Table How to show items with no data in Pivot Table International versions of Excel Layout and Print options in Pivot Table ModelOff finals 2013 Probabilities of default Regional settings for Excel Risk Status and Balance TCS bank Vintage of loans Vlad Smirnov financial analyst Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153664/ ModelOff Finalist Vlad Smirnov works for a bank and is setting up reserves for outstanding debt based on risk class. But, there are some months where the pivot table does not include all risk classes. Since the SUMPRODUCT formulas are counting on all risk classes being there, Vlad shows how to force the pivot table to always show each risk class for each month.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920