Excel for Analytics - Portfolio Project Series Video 3 - Data Exploration and Analysis in Excel

This is the Excel for Analytics - Portfolio Project Series Video 3 - Data Exploration and Analysis in Excel where I'll walk through the data exploration and data analysis process using Excel including a bunch of shortcuts to help make the process easier. If you skipped the first two videos I would highly encourage you to check those out here:

If you skipped the first two videos you can still go ahead and download the source file using either of the below two options:
and drop your name and email in the form and the email will show up right to your inbox in case you need to download again. Going this route will give you exclusive access to TMB Analytics insider discounts on future courses.

Either way works, it is entirely up to you, but I do recommend working through the second video in the series on cleaning the data up so you're working from the same base as me in this video.

In this third video of the Excel for Analytics - Portfolio Project Series we will cover:
0:00 - Intro + Welcome
1:10 - Analytics Reality Check - A quick reflection on Analytics work
6:40 - File Versioning
7:55 - Reviewing the request
9:37 - Creating a Pivot table (Alt + N + V)
12:54 - Dates vs Text behavior in a Pivot Table
16:18 - Creating pivot charts
21:48 - Starting variance analysis
26:34 - Basic forecasting
33:18 - Data exploration - being curious
38:44 - Using COUNTIFS
45:00 - Quick formatting tips using Paste Special Formats (Alt + E + S + T) and more
51:30 - Note taking on narrative
58:55 - Pressure testing notes
01:03:00 - Final review and recap

[SarabjeetSingh-vj]

Matt, this is the most Holistic video series on data analytics, all the videos I have checked out have never followed this approach, I really appreciate what you have created here. Thank you so much.

[HakeemLawrence]

I appreciate how organic this video is. Hearing you talk about ideas that you come across gives us a picture of what kind of questions we should be asking ourselves. You also addressed being cautious with the analysis i.e. the "elevator pitch".

[josephojo]

Although I am from Nigeria, But I had decided after these video series that, the name of my first born son will be 'Matt'. Thank you for these videos, thank you and thank you again .I can confidently say I now do know excel now! Although there are still more to learn. Matt pls we do need the forecasting videos. Thank you and thank you again!

[jist]

Matt, thank you for this excellent series. The candid nature of your data exploration is refreshing. There are always so many ways to chop it up, and learning the approach others take can open up other creative avenues for data analysis. Very nicely presented.

[KS]

Great video Matt! Subscribed and now onto part 4!

[kinga-hill]

This is really great content. I love that you went through the entire process. Your on-the-fly thinking is really amazing. I would love your insight about going into in-depth analysis when the request is for a simple quarterly summary. This type of in-depth analysis might take you an hour or less, but it would take me half a day. What is the benefit of dedicating so much time to an exploration that wasn't requested? I imagine when you deliver the original request, then a follow-up request might come for an explanation. I have never worked in industry so I am just curious how this all works.

[vikasmev]

Thanks, Matt for the valuable content. And sure you may upload videos on forecasting and other useful techniques to help us make a more useful analysis

[cyclopelab]

Video 3. Leaning a lot. Man you should have way more Likes than that.

[lisaamante]

Great, Matt! The format of watching you work and think as you go was very helpful. Also appreciate you calling out your keystrokes - I learned some cool new stuff!
I did get lost at one point though, at about 27:09 timestamp. I don't understand the formula for cell O41. I thought to forecast Q2 2021 sales, it would be the Q2 2020 sales plus 4% which I thought would be C41*(1+J41). Please explain again why it's C41/(1-J41).

[Amanda_OConnor]

This is absolute gold! Thank you and certainly looking forward to your other content!

[abediaz]

Loading the video tonight so I can crush excel on my flight to NOLA!

[rakibulhasan]

I appreciate You, Matt.. Can You give us Basic Forecasting videos?

[kevinalexis]

Are you still able to do the Forecasting with Excel video you mentioned here? I'm following along this series and it's been awesome so far! Would love to follow more of your videos, Your a great instructor !

[franciscosierraluna]

Thank you so much for taking the time to do this tutorial so clear, it has been most helpful. I just have one question on the forecasting section. When you first calculated the growth percentage, that percentage is with respect to Q1 2020, such that volume on Q1 2020 multiplied by (growth percentage + 1) returns the vol on Q1 2021. so, following this idea, the forecasted vol on Q2 2021 should be Q2 2020 multiplied by (growth percentage + 1), but the way you did it, as you explain on 27:30, is assuming that the growth percentage is with respect to 2021 and not 2020. I just want to know if this was a mistake or i'm missing something.

[adeyemihammed]

Matt, at 24:01, I could not see the remaining code as your video was blocking it. Can you please shed more light on that. The whole concept of GETPIVOTDATA is totally new to me

[amel]

This is pretty much a lot to take in one go. but, I kind of get the whole idea. might need to go back again to grasp it eventually. but, this is great.

[ukamakaazode]

16:50 he said “don’t do that”. That’s exactly what I did on my own as a newbie 😂

[chizuoanetoh]

This is an awesome content. I like how your mind works and how you break things down. Your channel is really a great resource.
You just gained a subscriber and a fan. Keep up the good work man. Do you have plans of making a similar video series but on SQL/Tableau?

[Mr.Potato]

I completed a advance excel course on udemy before coming to this project, and I'm still getting overwhelmed 😅. I think I will need to give this project few couple of repeat views to understand better.

[victoriaadekoya]

Good day, Thank you for these projects series
Please I'm using also trying to do the projects as well using the file but anytime I try to save my file after adding the pivot chart, it comes with an error, what could be wrong?