We are so lucky to have Mike Girvin on youtube ...That's why "Excel is fun" channel is very important ...because even if we were excel experts ...it's important to refresh our knowledge from to time to time ... thanks alot Mike.
Clean, systematic presentation. Great pedagogy. Like riding on an intellectual superhighway from start to finish. No potholes; no detours--just smooth sailing.
Topics in Video: 1. (00:00) Intro 2. (00:10) Files to use in class 3. (00:34) Goals for video 4. (01:25) Data Analysis Step #1: Get Data. Use Power Query to Get Data. Learn about important data analysis terms. 5. (07:24) Data Analysis Step #2: Create Data Model. Use Worksheet formulas to create flat table for the Standard PivotTable tool. Learn about the new lookup function XLOOKUP. 6. (12:44) Data Analysis Step #3: Create Calculations & Reports. Create first report: Year Month Sales Report with Product Slicer. 7. (14:32) Grouping in a PivotTable. 8. (19:02) Data Analysis Step #4: Create Visualizations: Line Chart to show sales trends. 9. (21:41) Add Slicer to PivotTable. 10. (23:53) Create remaining reports. 11. (25:40) Show Values As Calculations in a PivotTable. 12. (29:35) Limitations of Standard PivotTable: Grain of the Table. 13. (31:10) Cross Tab Report. 14. (32:12) Data Analysis Step #5: Get New Data and Refresh Reports & Visualizations 15. (34:52) Conclusion
You're BRILLIANT, Mike Girvin! I appreciate you and your teaching so much. As an aspiring data analyst, this type of education is so enriching. Thank you for sharing this to the global community - you're a genius.
Your channel is the only one that breaks down info into digestible steps, is very organized, detailed, and with a great array of info that has really helped me out especially during a difficult period of being unemployed. I really REALLY appreciate it Mike! THANK YOU!🙌
You are welcome, makeuplily!!! For 14 years at RUclips I have provided free Excel and Data Analysis resources to help people like you learn, and trying to tell stories to make the complicated seem less complicated : )
What a clever way to say it: "scratch my part of the brain where pleasure exists"!!!!!!!!!! : ) For me, making them scratches the happy part of my brain : )
Thank you Mike for the video. I enjoyed making the formulas. The pivot tables are really rewarding also because reports can be made so easy, including the chart and slicers. Updating the data, tables, formulas charts and pivots with a new source file is amazing, and a task I can practice more because it make me nervous as easy as it is. Thank you for showing all these things.
You are welcome for all these things, N Sancho1!!!! With this video and the next 4 videos over the next 4 Mondays, it should be a LOT of Data Analysis fun : )
Hi MIke, May I suggest that instead of going back to the original Fact Table sheet location and pressing Alt+N+V each time, you could just Copy/Paste the already created pivot table. That keeps all the formatting, and you just play around with PivotTable Fields pane to do whatever you want. I've noticed that's quicker. I am a big fan of your work, however, so please keep up with those great posts you've been creating
Now I normally leave a pun in the comments, but I feel that doing so here might take away from the expression of gratitude I'd like to give. This video was one of the most helpful things I have ever seen, on RUclips or anywhere else, and I am *amazed* at the width and breadth of your very high-quality material. Thank you for making all of this publicly available, and I can't wait to check out everything else that you have!
I am glad to provide free education here at RUclips. For last 14 years my goal has been to provide high level free education. Have fun and learn a lot, Pun!!! The content is technically not "free", because I do charge a Thumbs Up : )
@@PunmasterSTP I definitely want more Pun Jokes!!!!! Leave one on each video and help make our Team here at excelisfun laugh. Thanks, Teammate MakePeopleLaughALot!!!!
I always look forward to these videos and this is something I'm now working on in work. This is an incredibly informative series. Thank you Mike for creating you are the best!
@@excelisfun Great, looking forward to it. I also plan to look into your statistical analysis videos too. would love if you added more to them as well.
@@brand1974 Enjoy! Statistics playlist: ruclips.net/p/PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj Analytics playlist with lost more statistics: ruclips.net/p/PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ
I am sure you know this Mike but I have never heard you say it. If you right click on a field in the PivotTable Fields area you can add a slicer from there too.
I did not know that! That is a hot tip : ) : ) I always depend on cool Teammates like you provide cool tips that I do not know. As a Team, we can do it all : )
I use this from time to time but not that often. It would be more useful if it opened up the complete list if fields not just the option to create slicer for just that field. But I am commenting because I would have thought I learned this from Mike but now he denies that he is the culprit. It's quite possible that Mike has forgotten that he knows it! I've always said that Mike and Bill J have forgotten more Excel that the rest of us know. This just might be "Ex;hibit A" LOL .
Yes, I belong to this planet, in fact I belong to the Team you are on: The Online Excel Team: where I can make and post videos and files and Teammates like you watch, learn, click thumbs up and comment. It is an awesome Team : ) : )
Mike for some strange reason when i used my own data and dropped dates in the row section of the pivot table, i didnt get the quarter, years and month. Any help?
It is always a pleasure listening to you. Even dough I was familiar with everything from this video (from your previous classes), I was really enjoying it. 👍
Excellent refresher video. One question I always have is related to the proper setting of items that look like numbers, but you may not want to treat them like numbers. In your case here it was the product ID. I have this with general ledger account numbers and customer numbers. I don’t want them to be subject to arithmetic operations (add, subtract, etc). Would you leave them as whole numbers or convert them to text?
You can definitely convert then to text, if you prefer. However, for lookups, merges and relationships, if the number is text on one side, it must be text on the other also. If it is a number, they must both be numbers. They are not considered equivalent if one is text and one is number.
Hi Mike. Awesome Video #1 for EDA!! It is packed full of great tips, tricks and techniques for both the experienced and the beginner. I always learn something new and refresh something old when I watch your videos which is why ExcelIsFun is the #1 destination for EXCEL training, learning and inspiration!. Looking forward to #2 :)) Thumbs up!!
@@excelisfun Hi Mike. Since I have watched and studied many of your past series (EDAB, MSPTDA, etc.), much of this was review which is still great to keep the concepts fresh. Things I did pick up: 1. Noticed that even though you imported a .txt file, the M code function is = Csv.Document() vs. Txt.Document(); 2. Previously, had not noticed the green bar or icon change when dragging fields to the drop regions when creating a Pivot Table; 3. Previously, had not noticed the Load Status or Data Sources references when hovering over an existing Query Connection in the sidebar; 4. Did not know that now Pivot Tables use the same cache on subsequent PT creations after the first (thought it was necessary to copy and paste from the first one to keep the same cache); 5. Thanks for the reminder on "grain of the table" when discussing monthly average; 6. Previously always modified data source by going back into PQ editor and clicking the gear icon or directly modifying M code.. thanks for pointing out Get Data -> Data Source Settings from the worksheet. Those are what I can recall. Also, I noticed that you now default your Report Layout to Tabular Form when creating a PT. Some viewers might get confused by the different look if they use the EXCEL default for Report Layout of Compact Form. Easy to change, but might not be self evident to a beginner. Those are my thoughts. Even though lots of review, you can never repeat and reinforce your good methods and concepts enough. It pays dividends when you are working creatively on your own and you must draw from yourself as to what to do next or how to tackle a challenge. Working through your examples and seeing how you seamlessly flow from one thing to the next is very instructive and inspirational :)) Hope my comments are useful to you. Thumbs up!!
@@wayneedmondson1065 #4 is a good one to know for sure. If you ever group and then need to have a PivotTable not grouped, you can get to old three step wizard which DOES create a new cache each time" Alt, D, P : ) Yes, I need to remember to tell viewers about Tabular Layout and the option to make it permanent. Your comments are very helpful. Thanks Teammate Wayne : ) : ) : ) : )
There is a workaround for the limitation you mentioned for standard pivot table. Which is by using 'Calculated Field and Items'. Correct me if i am wrong.
Great teaching, wish I found this video 2 years ago, hope I can still ask questions. Around 26:00: when placing the 'Sale column' for the 2nd time into the 'Value area' of 'Field List panel', a '∑ Values' automatically pop up into the 'Column area', but when I follow along with my own data set, the '∑ Values' end up in the 'Row area' instead. Was lost for a while for not able to replicate the '% Change column'. Eventually found out I can just move the '∑ Values' from 'Row area' to 'Column area', but still curious to know how Excel automatically determine which area to add in the '∑ Values' by default.
Hi Mike @ExcelIsFun, You mentioned that the data has to be in a table to use the data analysis tool. I seldom put my data in a table and create PivotTables using that data. Is there a reason it allows me to use the data in a non-proper data set? Also, thank you so much for you! I have learned a lot and it has helped me in my role tremendously.
Dear Sir, facing an issue in the columns, why am I not able to able to get the "Month column" separately in the pivot table. It is embedded along with the years columns----the visual image would be something like this Years Sum of Sale($) 2021 404515 Jan 12245 Feb 30 Mar 11553
Hi Mike, It's amazing how much we learn in all your videos. I have one question, if I create a query to transform the data from a csv file and the file changes everyday, will the query still work with the new file. Should I run the query first with the new file and then delete the old file to show the latest data... If I can program this then I will make it dynamic saving us a lot of time. Thank you. Looking forward to next Monday.
I show how to change the file or folder path in this video. But if you just name the new file with the new data the same name that the query uses, then the query can just be refreshed. Maybe, file from yesterday - you change name, and rename new file with the previous file name - do this when you drop the new file. Otherwsie, next video shows how to get Power Query to see new files with new data : ) Next Monday!
@@excelisfun Thanks Mike. I will try this. I had the same thought process. I followed your EDAB course last year during lockdown. Your videos kept me company over the last year and I am so grateful for how much I have progressed because of you. I am also starting your statistics playlist...but this 4 part series is a great refresher....
@@saratharavabhoomi It will be a great refresher and it has the new user interface and options and things. Plus the Context Transition stuff next video will be cool too : )
Your page is life saving! Do you have videos on how to create your own portfolio? Do you know where we can practice our own data or do we create our own?
@@excelisfun for a Data Analayst role we have to create our own profile using tableau (which is what I’m learning in school), SQL and excel projects? Where can I practice more examples for pivot tables, power Query, formulas and charts? I am trying to build my LinkedIn portfolio as a beginner
@@briajones1203 I have 1000s of videos, all with files below them, if that helps. Just search my channel. Or watch the intro video on the home page and check out class playlists.
It been a great learning experience just following you channel. I have been stuck with a large data set, to find out what are the exact lines which are form the open item balance. I need to do a clearing exercise where i need to match single debit against multiple credits. Is there any way to do that, the data is for more than 3k line items ? . Please, If there is any previous session a link to that would be also very helpful.
@@rastkogligoric833 O, I did not understand your original question. I understand now, it was just the labels that did not show and you saw the generic "Row Label". Nice detective work, Rastko : )
This is good refresher, thanks Mike. One thing I am not too sure is about the short cut key for insert a pivot table, I am always doing it using Alt, N, V, why do you need to add a T after V now?
Alt, N, V worked in Excel 2016 and a few others, but in the latest version of Microsoft 365, they moved the data connectors from the Create PivotTable dialog box to the PivotTable drop down. If Alt, N, V works, just use it, but later in updates, you will have to use the T or D. I made a video about this: ruclips.net/video/ohkT4Zhk8PU/видео.html
@@excelisfun Mike, Amazing video presentation as always. Was thinking about how much time it took to present this video! Your skills are extensive. Was looking for a way to support you and couldn't find a way outside of merch. Question: I also use Alt + N, V on Microsoft 365 not Alt + N, V, T. Does this mean that my 365 isn't updating properly or are these the steps at 10-May-2021?
@@bencacace5491 The Alt, N, V, T is not fully released yet. It should be soon. As for support, comments and thumbs ups are the best way to support my efforts to bring free Excel education to the world! But if you want to make a donation to the cause, then you can use my donate paypal button on my home page or about page, here: ruclips.net/user/ExcelIsFunabout
E-DAB is zero to intermediate. MSPTDA is advanced with lots of coding with M Code and DAX. This one EDA will be a combo of both, condensed into 4 videos, with all the latest user interfaces and updated items. The intent of this class is for a beginner to be able to get up to speed with Data Analysis. However, it has advanced topics like Filter Context, Columnar Database and Context Transition (all in next video). I will use this in my final class for graduating Highline students so they know all the data analysis topics that I expect them to take, at a minimum, into the working world.
That is all I have now: ruclips.net/p/PLrRPvpgDmw0k_h8ORYyh7waGfuiiufu6H Watching next Monday's (Power Pivot and DAX) and the following Monday's (Power BI & DAX and Visuals) and the third Monday (both Power Pivot and Power BI ) shoudl help too.
@Abhishek Saraswat I have beginner data analysis Power BI videos in this E-DAB playlist: ruclips.net/p/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT If you want to become good with Power BI, you must become good with Data Modeling and DAX, so videos #7-10 in this playlist are for beginners who want to do Power BI, even though only videos #9 and 10 are using Power BI. Videos #7 & 8 teach data modeling and DAX. I think you will like this set of videos for beginners : )
@@testsample1005 Cool : ) : ) Great that you have been hanging out at excelisfun since 2015. I think E-DAB videos will really be cool - but even more, the next 3 Mondays will bring the beauty and power of how Power Pivot and Power BI work together to make our reporting and visualization life better!
Yes, it will cover Formulas, Power Query, Standard PivotTables, Power Pivot, Data Model PivotTables, DAX formulas, Data Modeling, Power BI and more. Mon, April 12: Data Analysis Class 00: Intro to PivotTable, Power Query, Power Pivot, Power BI & Visualizations Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas This is a free four video class taught by Excel MVP, Mike Girvin, that teaches Data Analysis using the Microsoft Power Tools: Power Query, Worksheet Formulas, PivotTable, Power Pivot, Power BI & Visualizations.
.CSV files can be openned with Word too or they need another type of software. They are kind of difficult due to comma delimeter and comma as decimal marker in database number format
“Comma” can be misleading as not all CSV files are comma separated. For instance, saving an Excel workbook in .csv file format will use semicolon as separator if the system’s decimal separator is comma (which is quite common).
Dear Mike , i've more than 100 different subtitles in my journal entries, so how can i move it to the other column, so that i can have clean data, pls kindly help.
Every time I go to "Import external data" in "Data" and select txt file, "text import wizard" opens and not PowerQuery. Everything from there goes haywire and I m not able to follow the steps. How can you sort this?
Question, in section 7 how do you get the headers for Year in a separate cell from the date? For example, Years header is in B3 and Date is in C3. I'm following along and my pivot has them stacked in the same column.
Mon, April 12: Data Analysis Class 00: Intro to PivotTable, Power Query, Power Pivot, Power BI & Visualizations Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas
Hi Mike! This stuff is great, but I have Excel 2016, and while importing the source table, Power Query is not an option. The old style import from txt pane appears. If I format it as a data table, I loose the connection. Please provide a solution. (Since I don't have the XLOOKUP function, I willl use the INDEX/MATCH combination.) Thanks in advance.
Not yet part of a playlist. I have two more videos to post, and then I will create the playlist. For right now, they sit at the top of my RUclips homepage in most recent videos.
The schedule for release of the videos in this class are: Four video release dates: Mon, April 12: Data Analysis Class 00: Intro to PivotTable, Power Query, Power Pivot, Power BI & Visualizations, ruclips.net/video/mFusmAyagsk/видео.html Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts, www.youtube.com/watch?v=LpvCj... Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling, www.youtube.com/watch?v=eIaKC... Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas This is a free four video class taught by Excel MVP, Mike Girvin, that teaches Data Analysis using the Microsoft Power Tools: Power Query, Worksheet Formulas, PivotTable, Power Pivot, Power BI & Visualizations.
Hi, I want calculate my teams incentive, but the problem is that I am unable to figure it out. So the situation is an employee will get incentive basis on his bookings. So if he is able to book 0 - 10 tickets then he will earn per ticket 1000, if in the same month he is able to book 15 tickets, then he will get 10000 for ten tickets and the remaining 5 will be calculated by 1250 so he will receive 16250 as total incentive. This range keeps on increasing by 250 every 10 tickets till 60 tickets, so in case he is able to book 60 tickets and above he will earn incentive of 97500. Can you help me with this?
Dear Mike, why R u taking so much of time to release next video, in this competitive world learning also has to be fast.. 😁 Pls hurry up, eagerly awaiting..
We are so lucky to have Mike Girvin on youtube ...That's why "Excel is fun" channel is very important ...because even if we were excel experts ...it's important to refresh our knowledge from to time to time ... thanks alot Mike.
Yes, this 4 class series will be a good refresher becasue it uses all the latest user interfaces and options : )
@@excelisfun Hussein is right !!! best RUclips channel on Excel, hands dows! #GoTeam!
@@spilledgraphics Go Team!!!!! I Love Our Team!!!!!
@@spilledgraphics I don't know, your Excel Music channel is pretty awesome : ) : )
It's terrific how Mike Girvin provides ample enthusiasm to people wanting to learn Excel. Bravo, Mike.
Thanks for the Bravo, DRSteele : ) : )
Clean, systematic presentation. Great pedagogy. Like riding on an intellectual superhighway from start to finish. No potholes; no detours--just smooth sailing.
WOW!!! I LOVE this: riding on an intellectual superhighway from start to finish!!!! You do have a way with words, Richard : ) : ) : ) : )
Topics in Video:
1. (00:00) Intro
2. (00:10) Files to use in class
3. (00:34) Goals for video
4. (01:25) Data Analysis Step #1: Get Data. Use Power Query to Get Data. Learn about important data analysis terms.
5. (07:24) Data Analysis Step #2: Create Data Model. Use Worksheet formulas to create flat table for the Standard PivotTable tool. Learn about the new lookup function XLOOKUP.
6. (12:44) Data Analysis Step #3: Create Calculations & Reports. Create first report: Year Month Sales Report with Product Slicer.
7. (14:32) Grouping in a PivotTable.
8. (19:02) Data Analysis Step #4: Create Visualizations: Line Chart to show sales trends.
9. (21:41) Add Slicer to PivotTable.
10. (23:53) Create remaining reports.
11. (25:40) Show Values As Calculations in a PivotTable.
12. (29:35) Limitations of Standard PivotTable: Grain of the Table.
13. (31:10) Cross Tab Report.
14. (32:12) Data Analysis Step #5: Get New Data and Refresh Reports & Visualizations
15. (34:52) Conclusion
You're BRILLIANT, Mike Girvin! I appreciate you and your teaching so much. As an aspiring data analyst, this type of education is so enriching. Thank you for sharing this to the global community - you're a genius.
You are welcome! I am happy to try and bring free Excel & Data Analysis education to the world : )
Your channel is the only one that breaks down info into digestible steps, is very organized, detailed, and with a great array of info that has really helped me out especially during a difficult period of being unemployed. I really REALLY appreciate it Mike!
THANK YOU!🙌
You are welcome, makeuplily!!! For 14 years at RUclips I have provided free Excel and Data Analysis resources to help people like you learn, and trying to tell stories to make the complicated seem less complicated : )
17 years working on Excel and ... still learning!
Thank you Mike
these data analysis videos scratch the part of my brain where pleasure exists. Thanks, Mike!
What a clever way to say it: "scratch my part of the brain where pleasure exists"!!!!!!!!!! : )
For me, making them scratches the happy part of my brain : )
Thank u for teaching all these properly without leaving any doubt.
Now finally I think I can get a job
You are welcome! With Excel skills, it is definitely easier to get a job ; )
Thank you Mike for the video. I enjoyed making the formulas. The pivot tables are really rewarding also because reports can be made so easy, including the chart and slicers. Updating the data, tables, formulas charts and pivots with a new source file is amazing, and a task I can practice more because it make me nervous as easy as it is. Thank you for showing all these things.
You are welcome for all these things, N Sancho1!!!! With this video and the next 4 videos over the next 4 Mondays, it should be a LOT of Data Analysis fun : )
Pivot table, the heart of Excel data analysis, thanks so much Mike🙏🙏
You are welcome so much, awesome stats guy Ogwal : )
This channel and teacher is a TREASURE!! Wow!!! Ty, SIR!🙏
Hi MIke,
May I suggest that instead of going back to the original Fact Table sheet location and pressing Alt+N+V each time, you could just Copy/Paste the already created pivot table.
That keeps all the formatting, and you just play around with PivotTable Fields pane to do whatever you want.
I've noticed that's quicker.
I am a big fan of your work, however, so please keep up with those great posts you've been creating
That is an awesome tip!!! I show that is numerous videos, but not this one. You are so right: it saves a lot of time : )
I rarely sub to channels unless they are exceptional. You are exceptional, Sir!
Thank you : )
Now I normally leave a pun in the comments, but I feel that doing so here might take away from the expression of gratitude I'd like to give. This video was one of the most helpful things I have ever seen, on RUclips or anywhere else, and I am *amazed* at the width and breadth of your very high-quality material. Thank you for making all of this publicly available, and I can't wait to check out everything else that you have!
I am glad to provide free education here at RUclips. For last 14 years my goal has been to provide high level free education. Have fun and learn a lot, Pun!!! The content is technically not "free", because I do charge a Thumbs Up : )
@@excelisfun Haha, that's good to hear! And at any rate, I think a thumbs up is such a small price to pay for such...Pivot-al...information 😎
@@PunmasterSTP Pivot-al...information lol
@@excelisfun Hey sometimes jokes can have a big effect. You should ask me for more; you’d be making a…Power Query 😎
@@PunmasterSTP I definitely want more Pun Jokes!!!!! Leave one on each video and help make our Team here at excelisfun laugh. Thanks, Teammate MakePeopleLaughALot!!!!
i havent seen a fanastic teacher like mike , mike you really made excel a fun.
thanking you
Excellent video with great pacing and clear communication. The best of the best
I have the book from library but the youtube videos make so much more sense to me! Really appreciate it.
I always look forward to these videos and this is something I'm now working on in work. This is an incredibly informative series. Thank you Mike for creating you are the best!
You are welcome, Mark Ward!
Great refresher! Thanks Mike!
Glad it refreshes, Ken !!! : )
Thank you Mike for another EXCELlent video. It's always lovely to have something to learn from each video.
What new things did you learn in this video, Fellow teacher?
@@excelisfun like we have to have empty cell all around the table for data analysis tools to work properly.
@@SyedMuzammilMahasanShahi Cool : )
How do I get the columns for the year and date in the pivot table?
I got it. You have to select "show in tabular form" from report layout
Thank you Excel really IS FUN!!
You are welcome for the Excel Fun, Guiomar!!!!
Thank you for making these videos. Especially on this subject
You are welcome!!! The next three Mondays will be great fun : )
@@excelisfun Great, looking forward to it. I also plan to look into your statistical analysis videos too. would love if you added more to them as well.
@@brand1974 Enjoy!
Statistics playlist: ruclips.net/p/PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj
Analytics playlist with lost more statistics: ruclips.net/p/PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ
@@excelisfun Awesome! Thanks. I am saving the entire playlist.
@@brand1974 : ) : ) Let's see those thumbs ups and comments in support of the free Excel and analytics education ; )
Boom!Great First Class...Thank You Mike :)
You are welcome, darryl!!!!!!!!! Boom!
Thanks as always, Mr Mike!
You are welcome!
Mike is a brilliant teacher.
Another incredible video Mike 👍
Glad you like it!!!!
I am sure you know this Mike but I have never heard you say it. If you right click on a field in the PivotTable Fields area you can add a slicer from there too.
I did not know that! That is a hot tip : ) : ) I always depend on cool Teammates like you provide cool tips that I do not know. As a Team, we can do it all : )
I use this from time to time but not that often. It would be more useful if it opened up the complete list if fields not just the option to create slicer for just that field. But I am commenting because I would have thought I learned this from Mike but now he denies that he is the culprit. It's quite possible that Mike has forgotten that he knows it! I've always said that Mike and Bill J have forgotten more Excel that the rest of us know. This just might be "Ex;hibit A" LOL .
Some videos beg me for a like. This one earned it without asking
Glad you liked it!!!!
Been looking forward to this video since last Monday. Can’t explain how much I’ve learned using Excel for fun. Love power query 👍
Power Query is the greatest tool in Excel since the 1990s PivotTable. Lots more Power Query in the upcoming videos : )
The next 3 Mondays will be awesome!!!
Thank you very much. May God bless you!
Great refresher video Mike, excellent as always!
Thanks, Chris : ) : ) : )
Mike, you are a great teacher.
Glad to help with the teaching!!!!
Do u really belongs to this planet ?
Bcoz the bunch of knowledge you have that unmatchable.
Terrific !!
Yes, I belong to this planet, in fact I belong to the Team you are on: The Online Excel Team: where I can make and post videos and files and Teammates like you watch, learn, click thumbs up and comment. It is an awesome Team : ) : )
Mike for some strange reason when i used my own data and dropped dates in the row section of the pivot table, i didnt get the quarter, years and month. Any help?
Excellent video. Looking forward to the next one.
The next one will be great Power Pivot and DAX fun!!!
Cant wait for your next videos. Very useful. Thanks a lot. Kudos for the great work.
Me either! I can't wait till next Monday! AND it WILL be epic: over 1 hour of Power Pivot and DAX magic!
Great endeavor, Mike!
And beautiful logos, too. ;-)
VERY beautiful logos and music intro!!!!!!!!!! The dude who made them is sooooo rad!!!!
This is great content. Thank you, sir! Your enthusiasm is makes it that much better!
You are welcome, John!!!!
It is always a pleasure listening to you. Even dough I was familiar with everything from this video (from your previous classes), I was really enjoying it. 👍
I am so glad that the videos are a pleasure! You are are REALLY good with Excel, so I am glad that you can still watch and enjoy : ) : )
@@excelisfun can't get enough, and looking forward to another video!
@@ljubicar1987 Woo Hoo : )
Always you are in the first row
thank Mike
First row, thanks, Sevag!!!!
hi Mr. Mike Girvin, your share was very helpful. Just my exel not show Year and Month in 2 columns. How can I make it?
Thank you Mike for this amazing video and appreciate your hard work for us.
You are welcome, Amena : ) : )
Great job sir.
Glad you like it : )
@ExcellsFun- What version of excel are you using because I can't find few of the tools or functions you're using
In 30:10, in the pivot we can add a calculated field equals to =sales/12, without a need to an intermediate table
This guys is AWESOME! 👏🏻👏🏻👏🏻👌🏼
Thank you, i was waiting for this video since announcement.
It is very helpful and easy to understand. 👍😍
Waiting for coming Monday's
Glad it is easy to understand! Mondays are going to be awesome!!!!!
Amazing content.
Glad you think so!
Thank you so much Mr Mike
You are welcome, so much : ) : )
Thanks!!
You are welcome, Junior : )
Excel Grooooooving !!!! 😎😎 by Master Mike !
Grooooooooving = I love that!!!!!!!
Excellent refresher video. One question I always have is related to the proper setting of items that look like numbers, but you may not want to treat them like numbers. In your case here it was the product ID. I have this with general ledger account numbers and customer numbers. I don’t want them to be subject to arithmetic operations (add, subtract, etc). Would you leave them as whole numbers or convert them to text?
You can definitely convert then to text, if you prefer. However, for lookups, merges and relationships, if the number is text on one side, it must be text on the other also. If it is a number, they must both be numbers. They are not considered equivalent if one is text and one is number.
Thanks Mike. Can't wait for the rest. : ) : )
Me either : ) : )
Outstanding! My best regards from Limón, Costa Rica!
Thanks for the regards from Limon!!!!
Nice to watch you again Mike :-) We are Up to Date and Up to Speed :-)
Up to date and up to speed is good : )
@@excelisfun Yes it is :-)
Excellent. Go to videos for Excel. Thanks
Power Query is Data Make up Room .1)Get ugly Data from any source 2) Do Some Make up by Transforming to make it a " model"
3) Load to Show the world.
That is a clever analogy!!!
Great video, as always
Glad you enjoyed it!!
Hi Mike. Awesome Video #1 for EDA!! It is packed full of great tips, tricks and techniques for both the experienced and the beginner. I always learn something new and refresh something old when I watch your videos which is why ExcelIsFun is the #1 destination for EXCEL training, learning and inspiration!. Looking forward to #2 :)) Thumbs up!!
Thanks for the comprehensively kind words about the video : ) : ) : ) Since you are an Excel Master, what did you learn from this video?
@@excelisfun Hi Mike. Since I have watched and studied many of your past series (EDAB, MSPTDA, etc.), much of this was review which is still great to keep the concepts fresh. Things I did pick up: 1. Noticed that even though you imported a .txt file, the M code function is = Csv.Document() vs. Txt.Document(); 2. Previously, had not noticed the green bar or icon change when dragging fields to the drop regions when creating a Pivot Table; 3. Previously, had not noticed the Load Status or Data Sources references when hovering over an existing Query Connection in the sidebar; 4. Did not know that now Pivot Tables use the same cache on subsequent PT creations after the first (thought it was necessary to copy and paste from the first one to keep the same cache); 5. Thanks for the reminder on "grain of the table" when discussing monthly average; 6. Previously always modified data source by going back into PQ editor and clicking the gear icon or directly modifying M code.. thanks for pointing out Get Data -> Data Source Settings from the worksheet. Those are what I can recall. Also, I noticed that you now default your Report Layout to Tabular Form when creating a PT. Some viewers might get confused by the different look if they use the EXCEL default for Report Layout of Compact Form. Easy to change, but might not be self evident to a beginner. Those are my thoughts. Even though lots of review, you can never repeat and reinforce your good methods and concepts enough. It pays dividends when you are working creatively on your own and you must draw from yourself as to what to do next or how to tackle a challenge. Working through your examples and seeing how you seamlessly flow from one thing to the next is very instructive and inspirational :)) Hope my comments are useful to you. Thumbs up!!
@@wayneedmondson1065 #4 is a good one to know for sure. If you ever group and then need to have a PivotTable not grouped, you can get to old three step wizard which DOES create a new cache each time" Alt, D, P : ) Yes, I need to remember to tell viewers about Tabular Layout and the option to make it permanent.
Your comments are very helpful. Thanks Teammate Wayne : ) : ) : ) : )
@@excelisfun Thanks Mike! My pleasure to contribute :)) Go Team!!
There is a workaround for the limitation you mentioned for standard pivot table. Which is by using 'Calculated Field and Items'.
Correct me if i am wrong.
Great teaching, wish I found this video 2 years ago, hope I can still ask questions.
Around 26:00: when placing the 'Sale column' for the 2nd time into the 'Value area' of 'Field List panel', a '∑ Values' automatically pop up into the 'Column area', but when I follow along with my own data set, the '∑ Values' end up in the 'Row area' instead. Was lost for a while for not able to replicate the '% Change column'. Eventually found out I can just move the '∑ Values' from 'Row area' to 'Column area', but still curious to know how Excel automatically determine which area to add in the '∑ Values' by default.
Hi Mike @ExcelIsFun, You mentioned that the data has to be in a table to use the data analysis tool. I seldom put my data in a table and create PivotTables using that data. Is there a reason it allows me to use the data in a non-proper data set? Also, thank you so much for you! I have learned a lot and it has helped me in my role tremendously.
Thanks!
You are welcome, Roy!!!!! Thanks for your donation - It helps me to keep making content : ) : ) : ) : )
Super duper hit sir 💯💯💯💯💯💯☝️☝️👌👌👌
Glad you like the hit : )
Great Class!
The next 3 Mondays should be great fun!
Dear Sir,
facing an issue in the columns, why am I not able to able to get the "Month column" separately in the pivot table. It is embedded along with the years columns----the visual image would be something like this
Years Sum of Sale($)
2021 404515
Jan 12245
Feb 30
Mar 11553
Hi Mike, It's amazing how much we learn in all your videos. I have one question, if I create a query to transform the data from a csv file and the file changes everyday, will the query still work with the new file. Should I run the query first with the new file and then delete the old file to show the latest data... If I can program this then I will make it dynamic saving us a lot of time. Thank you. Looking forward to next Monday.
I show how to change the file or folder path in this video. But if you just name the new file with the new data the same name that the query uses, then the query can just be refreshed. Maybe, file from yesterday - you change name, and rename new file with the previous file name - do this when you drop the new file. Otherwsie, next video shows how to get Power Query to see new files with new data : ) Next Monday!
@@excelisfun Thanks Mike. I will try this. I had the same thought process. I followed your EDAB course last year during lockdown. Your videos kept me company over the last year and I am so grateful for how much I have progressed because of you. I am also starting your statistics playlist...but this 4 part series is a great refresher....
@@saratharavabhoomi It will be a great refresher and it has the new user interface and options and things. Plus the Context Transition stuff next video will be cool too : )
Why couldnt we use a helper column for the month on the fsales table? Use the Text function to provide the month name?
Thank you sir
You are welcome!
Your page is life saving! Do you have videos on how to create your own portfolio? Do you know where we can practice our own data or do we create our own?
I am not sure what you are asking. But the data in every video can be downloaded in the links below the videos : )
@@excelisfun for a Data Analayst role we have to create our own profile using tableau (which is what I’m learning in school), SQL and excel projects? Where can I practice more examples for pivot tables, power Query, formulas and charts? I am trying to build my LinkedIn portfolio as a beginner
@@briajones1203 I have 1000s of videos, all with files below them, if that helps. Just search my channel. Or watch the intro video on the home page and check out class playlists.
@@excelisfun thank you so much!
Thank you, sir, for the video that you sent to me. I hope to watch your videos about the Visual Basic in Excel.
I do not tech VBA coding, only recorded Macros: ruclips.net/video/WKyN8e7XXjI/видео.html
great job! thank you so much.
You are welcome so much!
REAL FUN Mike THKS
It been a great learning experience just following you channel.
I have been stuck with a large data set, to find out what are the exact lines which are form the open item balance. I need to do a clearing exercise where i need to match single debit against multiple credits. Is there any way to do that, the data is for more than 3k line items ? . Please, If there is any previous session a link to that would be also very helpful.
the excel Gods are pleased
I liked that video!
Glad you liked it!!!
Amazing!
Thank you for this super lessons
Glad you liked it!
nice explanation...
Glad you like it, Sravan!!!
Guru Mike,how is it when u drag ''Date" to the Rows field,pivot automatically creates and labels columns as ''Years";"Quarters";"Dates"?
There is a setting to do this. If it does not do it for you, just right-click dates in Rows area and point to Group, then click Year and Date
@@excelisfun Field Settings/Layout and Print/Show item labels in tabular form did it for me!Thx :)
@@rastkogligoric833 O, I did not understand your original question. I understand now, it was just the labels that did not show and you saw the generic "Row Label". Nice detective work, Rastko : )
This is good refresher, thanks Mike. One thing I am not too sure is about the short cut key for insert a pivot table, I am always doing it using Alt, N, V, why do you need to add a T after V now?
Alt, N, V worked in Excel 2016 and a few others, but in the latest version of Microsoft 365, they moved the data connectors from the Create PivotTable dialog box to the PivotTable drop down. If Alt, N, V works, just use it, but later in updates, you will have to use the T or D. I made a video about this: ruclips.net/video/ohkT4Zhk8PU/видео.html
@@excelisfun Mike, Amazing video presentation as always. Was thinking about how much time it took to present this video! Your skills are extensive. Was looking for a way to support you and couldn't find a way outside of merch.
Question: I also use Alt + N, V on Microsoft 365 not Alt + N, V, T. Does this mean that my 365 isn't updating properly or are these the steps at 10-May-2021?
@@bencacace5491 The Alt, N, V, T is not fully released yet. It should be soon. As for support, comments and thumbs ups are the best way to support my efforts to bring free Excel education to the world! But if you want to make a donation to the cause, then you can use my donate paypal button on my home page or about page, here: ruclips.net/user/ExcelIsFunabout
Amazing 🌷
Glad it is amazing for you, Ahmad!!! The next three Mondays will have the next three Data Analysis videos in this class series. They should be fun : )
Mike, how does this course compare to your MSPTDA course (which was awesome, by the way!)?
E-DAB is zero to intermediate. MSPTDA is advanced with lots of coding with M Code and DAX. This one EDA will be a combo of both, condensed into 4 videos, with all the latest user interfaces and updated items. The intent of this class is for a beginner to be able to get up to speed with Data Analysis. However, it has advanced topics like Filter Context, Columnar Database and Context Transition (all in next video). I will use this in my final class for graduating Highline students so they know all the data analysis topics that I expect them to take, at a minimum, into the working world.
Also, this four video class compares each tool in more careful detail as the class proceeds.
@@excelisfun Thanks, Mike - you're the best!
@@JSUG4219 Go Team!!!!
Always amazing, Thank.
Glad it is amazing for you, Sai!!!!
EPIC! THANKS MIKE!
Flad you like it, Nika!!!
In 2016, it expands in a single column with Column name Data Labels
Yes Mike the same question from me as well, from I can start learning PowerBI.
That is all I have now: ruclips.net/p/PLrRPvpgDmw0k_h8ORYyh7waGfuiiufu6H
Watching next Monday's (Power Pivot and DAX) and the following Monday's (Power BI & DAX and Visuals) and the third Monday (both Power Pivot and Power BI ) shoudl help too.
@Abhishek Saraswat I have beginner data analysis Power BI videos in this E-DAB playlist: ruclips.net/p/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT If you want to become good with Power BI, you must become good with Data Modeling and DAX, so videos #7-10 in this playlist are for beginners who want to do Power BI, even though only videos #9 and 10 are using Power BI. Videos #7 & 8 teach data modeling and DAX. I think you will like this set of videos for beginners : )
Let me know in the comments how you like it : )
Your help is highly appreciated Mike. I am fan of you since 2015.
@@testsample1005 Cool : ) : ) Great that you have been hanging out at excelisfun since 2015. I think E-DAB videos will really be cool - but even more, the next 3 Mondays will bring the beauty and power of how Power Pivot and Power BI work together to make our reporting and visualization life better!
Awesome 😎😎😎
Glad this helps, JB Racing!!!! What do you race? I race BMX : )
Great 👌
It will be a complete series ?
Yes, it will cover Formulas, Power Query, Standard PivotTables, Power Pivot, Data Model PivotTables, DAX formulas, Data Modeling, Power BI and more.
Mon, April 12: Data Analysis Class 00: Intro to PivotTable, Power Query, Power Pivot, Power BI & Visualizations
Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts
Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling
Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling
Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas
This is a free four video class taught by Excel MVP, Mike Girvin, that teaches Data Analysis using the Microsoft Power Tools: Power Query, Worksheet Formulas, PivotTable, Power Pivot, Power BI & Visualizations.
.CSV files can be openned with Word too or they need another type of software. They are kind of difficult due to comma delimeter and comma as decimal marker in database number format
“Comma” can be misleading as not all CSV files are comma separated. For instance, saving an Excel workbook in .csv file format will use semicolon as separator if the system’s decimal separator is comma (which is quite common).
@@TSSC thanks for your help
Hi Mike, How do Dax Calculate day on Day changes excluding holidays and weekends? Thanks!
Dear Mike , i've more than 100 different subtitles in my journal entries, so how can i move it to the other column, so that i can have clean data, pls kindly help.
Every time I go to "Import external data" in "Data" and select txt file, "text import wizard" opens and not PowerQuery. Everything from there goes haywire and I m not able to follow the steps. How can you sort this?
Question, in section 7 how do you get the headers for Year in a separate cell from the date? For example, Years header is in B3 and Date is in C3. I'm following along and my pivot has them stacked in the same column.
Did you figure out how to separate the year and date in the pivot table
@@seandre59 no not yet
Very Nice! Thanks! When is the next video coming?
Mon, April 12: Data Analysis Class 00: Intro to PivotTable, Power Query, Power Pivot, Power BI & Visualizations
Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts
Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling
Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling
Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas
Glad you liked it!
@@excelisfun Thank you! Going to put it in my Calendar :)
@@shoppersdream Cool!!! LOVE your user name : ) : ) : ) : )
Hi Mike! This stuff is great, but I have Excel 2016, and while importing the source table, Power Query is not an option. The old style import from txt pane appears. If I format it as a data table, I loose the connection. Please provide a solution. (Since I don't have the XLOOKUP function, I willl use the INDEX/MATCH combination.) Thanks in advance.
Open csv file, copy data, paste into Excel, then manually fix data , like with join formula =Cell1&" "&Cell2
These videos are part of a playlist? I'd like to add it to my favourites playlist
Not yet part of a playlist. I have two more videos to post, and then I will create the playlist. For right now, they sit at the top of my RUclips homepage in most recent videos.
The schedule for release of the videos in this class are:
Four video release dates:
Mon, April 12: Data Analysis Class 00: Intro to PivotTable, Power Query, Power Pivot, Power BI & Visualizations, ruclips.net/video/mFusmAyagsk/видео.html
Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts, www.youtube.com/watch?v=LpvCj...
Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling, www.youtube.com/watch?v=eIaKC...
Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling
Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas
This is a free four video class taught by Excel MVP, Mike Girvin, that teaches Data Analysis using the Microsoft Power Tools: Power Query, Worksheet Formulas, PivotTable, Power Pivot, Power BI & Visualizations.
Hi, I want calculate my teams incentive, but the problem is that I am unable to figure it out. So the situation is an employee will get incentive basis on his bookings. So if he is able to book 0 - 10 tickets then he will earn per ticket 1000, if in the same month he is able to book 15 tickets, then he will get 10000 for ten tickets and the remaining 5 will be calculated by 1250 so he will receive 16250 as total incentive. This range keeps on increasing by 250 every 10 tickets till 60 tickets, so in case he is able to book 60 tickets and above he will earn incentive of 97500. Can you help me with this?
Dear Mike, why R u taking so much of time to release next video, in this competitive world learning also has to be fast.. 😁 Pls hurry up, eagerly awaiting..
He has to prepare materials and record the videos. Also, He has a life outside of RUclips. Please be patient.