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

Watch on YouTube

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