Excel Data Analysis Class 01: PivotTables, Power Query, Formulas and Charts

Поделиться
HTML-код
  • Опубликовано: 24 ноя 2024

Комментарии • 290

  • @drsteele4749
    @drsteele4749 3 года назад +30

    It's terrific how Mike Girvin provides ample enthusiasm to people wanting to learn Excel. Bravo, Mike.

    • @excelisfun
      @excelisfun  3 года назад +2

      Thanks for the Bravo, DRSteele : ) : )

  • @HusseinKorish
    @HusseinKorish 3 года назад +41

    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.

    • @excelisfun
      @excelisfun  3 года назад +3

      Yes, this 4 class series will be a good refresher becasue it uses all the latest user interfaces and options : )

    • @spilledgraphics
      @spilledgraphics 3 года назад +2

      @@excelisfun Hussein is right !!! best RUclips channel on Excel, hands dows! #GoTeam!

    • @excelisfun
      @excelisfun  3 года назад +2

      @@spilledgraphics Go Team!!!!! I Love Our Team!!!!!

    • @excelisfun
      @excelisfun  3 года назад +2

      @@spilledgraphics I don't know, your Excel Music channel is pretty awesome : ) : )

  • @richardhay645
    @richardhay645 3 года назад +34

    Clean, systematic presentation. Great pedagogy. Like riding on an intellectual superhighway from start to finish. No potholes; no detours--just smooth sailing.

    • @excelisfun
      @excelisfun  3 года назад +2

      WOW!!! I LOVE this: riding on an intellectual superhighway from start to finish!!!! You do have a way with words, Richard : ) : ) : ) : )

  • @excelisfun
    @excelisfun  3 года назад +8

    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

  • @jean-marcherard9216
    @jean-marcherard9216 3 года назад +3

    17 years working on Excel and ... still learning!
    Thank you Mike

  • @makeuplily
    @makeuplily 2 года назад +1

    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!🙌

    • @excelisfun
      @excelisfun  2 года назад

      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 : )

  • @nikakalichava8012
    @nikakalichava8012 2 года назад +1

    these data analysis videos scratch the part of my brain where pleasure exists. Thanks, Mike!

    • @excelisfun
      @excelisfun  2 года назад +1

      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 : )

  • @srbrt2916
    @srbrt2916 3 года назад +4

    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.

    • @excelisfun
      @excelisfun  3 года назад +1

      You are welcome! I am happy to try and bring free Excel & Data Analysis education to the world : )

  • @AllAboutRSCC
    @AllAboutRSCC 9 месяцев назад +2

    Some videos beg me for a like. This one earned it without asking

    • @excelisfun
      @excelisfun  9 месяцев назад

      Glad you liked it!!!!

  • @anandraj8065
    @anandraj8065 3 года назад +1

    Thank u for teaching all these properly without leaving any doubt.
    Now finally I think I can get a job

    • @excelisfun
      @excelisfun  3 года назад

      You are welcome! With Excel skills, it is definitely easier to get a job ; )

  • @skrapsaker2035
    @skrapsaker2035 Год назад +1

    This channel and teacher is a TREASURE!! Wow!!! Ty, SIR!🙏

  • @ogwalfrancis
    @ogwalfrancis 3 года назад +2

    Pivot table, the heart of Excel data analysis, thanks so much Mike🙏🙏

    • @excelisfun
      @excelisfun  3 года назад

      You are welcome so much, awesome stats guy Ogwal : )

  • @atiqkhan9637
    @atiqkhan9637 3 года назад

    i havent seen a fanastic teacher like mike , mike you really made excel a fun.
    thanking you

  • @jojochan358
    @jojochan358 3 года назад

    I have the book from library but the youtube videos make so much more sense to me! Really appreciate it.

  • @iammuko2879
    @iammuko2879 3 года назад

    I rarely sub to channels unless they are exceptional. You are exceptional, Sir!

  • @nsanch0181
    @nsanch0181 3 года назад +3

    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.

    • @excelisfun
      @excelisfun  3 года назад +1

      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 : )

  • @csemanate
    @csemanate 3 года назад +1

    Mike is a brilliant teacher.

  • @van4e13
    @van4e13 3 года назад +1

    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

    • @excelisfun
      @excelisfun  3 года назад

      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 : )

  • @kenzhu8353
    @kenzhu8353 3 года назад +5

    Great refresher! Thanks Mike!

    • @excelisfun
      @excelisfun  3 года назад +2

      Glad it refreshes, Ken !!! : )

  • @stephenkibara6887
    @stephenkibara6887 Год назад +1

    Excellent video with great pacing and clear communication. The best of the best

  • @markward8483
    @markward8483 3 года назад +3

    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
      @excelisfun  3 года назад +1

      You are welcome, Mark Ward!

  • @darrylmorgan
    @darrylmorgan 3 года назад +5

    Boom!Great First Class...Thank You Mike :)

    • @excelisfun
      @excelisfun  3 года назад

      You are welcome, darryl!!!!!!!!! Boom!

  • @PunmasterSTP
    @PunmasterSTP 2 года назад +1

    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!

    • @excelisfun
      @excelisfun  2 года назад +1

      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
      @PunmasterSTP 2 года назад +1

      @@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 😎

    • @excelisfun
      @excelisfun  2 года назад +1

      @@PunmasterSTP Pivot-al...information lol

    • @PunmasterSTP
      @PunmasterSTP 2 года назад

      @@excelisfun Hey sometimes jokes can have a big effect. You should ask me for more; you’d be making a…Power Query 😎

    • @excelisfun
      @excelisfun  2 года назад +1

      @@PunmasterSTP I definitely want more Pun Jokes!!!!! Leave one on each video and help make our Team here at excelisfun laugh. Thanks, Teammate MakePeopleLaughALot!!!!

  • @cleosavana4961
    @cleosavana4961 3 года назад +1

    Thank you Excel really IS FUN!!

    • @excelisfun
      @excelisfun  3 года назад +1

      You are welcome for the Excel Fun, Guiomar!!!!

  • @abdulsamadibrahim4929
    @abdulsamadibrahim4929 2 года назад +1

    Thanks as always, Mr Mike!

  • @phand21
    @phand21 Год назад +1

    Another incredible video Mike 👍

  • @SyedMuzammilMahasanShahi
    @SyedMuzammilMahasanShahi 3 года назад +2

    Thank you Mike for another EXCELlent video. It's always lovely to have something to learn from each video.

    • @excelisfun
      @excelisfun  3 года назад +1

      What new things did you learn in this video, Fellow teacher?

    • @SyedMuzammilMahasanShahi
      @SyedMuzammilMahasanShahi 3 года назад

      @@excelisfun like we have to have empty cell all around the table for data analysis tools to work properly.

    • @excelisfun
      @excelisfun  3 года назад +1

      @@SyedMuzammilMahasanShahi Cool : )

  • @hermelindapartidas5448
    @hermelindapartidas5448 3 года назад +1

    Mike, you are a great teacher.

    • @excelisfun
      @excelisfun  3 года назад

      Glad to help with the teaching!!!!

  • @seandre59
    @seandre59 2 года назад +2

    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

  • @martinpaulbannister1745
    @martinpaulbannister1745 3 года назад +1

    Been looking forward to this video since last Monday. Can’t explain how much I’ve learned using Excel for fun. Love power query 👍

    • @excelisfun
      @excelisfun  3 года назад

      Power Query is the greatest tool in Excel since the 1990s PivotTable. Lots more Power Query in the upcoming videos : )

    • @excelisfun
      @excelisfun  3 года назад

      The next 3 Mondays will be awesome!!!

  • @chrism9037
    @chrism9037 3 года назад +3

    Great refresher video Mike, excellent as always!

    • @excelisfun
      @excelisfun  3 года назад

      Thanks, Chris : ) : ) : )

  • @brand1974
    @brand1974 3 года назад +5

    Thank you for making these videos. Especially on this subject

    • @excelisfun
      @excelisfun  3 года назад +3

      You are welcome!!! The next three Mondays will be great fun : )

    • @brand1974
      @brand1974 3 года назад +2

      @@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.

    • @excelisfun
      @excelisfun  3 года назад +2

      @@brand1974 Enjoy!
      Statistics playlist: ruclips.net/p/PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj
      Analytics playlist with lost more statistics: ruclips.net/p/PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ

    • @brand1974
      @brand1974 3 года назад +1

      @@excelisfun Awesome! Thanks. I am saving the entire playlist.

    • @excelisfun
      @excelisfun  3 года назад +2

      @@brand1974 : ) : ) Let's see those thumbs ups and comments in support of the free Excel and analytics education ; )

  • @nigelbarrett8973
    @nigelbarrett8973 3 года назад +3

    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.

    • @excelisfun
      @excelisfun  3 года назад +1

      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 : )

    • @richardhay645
      @richardhay645 3 года назад +1

      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 .

  • @rahulbhujbal3663
    @rahulbhujbal3663 3 года назад

    Do u really belongs to this planet ?
    Bcoz the bunch of knowledge you have that unmatchable.
    Terrific !!

    • @excelisfun
      @excelisfun  3 года назад +1

      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 : ) : )

  • @oddanneout
    @oddanneout 2 года назад

    This guys is AWESOME! 👏🏻👏🏻👏🏻👌🏼

  • @engt1000
    @engt1000 3 года назад +2

    Excellent video. Looking forward to the next one.

    • @excelisfun
      @excelisfun  3 года назад

      The next one will be great Power Pivot and DAX fun!!!

  • @sanjaykumarguda8374
    @sanjaykumarguda8374 3 года назад

    Excellent. Go to videos for Excel. Thanks

  • @PaulieGNYC
    @PaulieGNYC 2 месяца назад

    When I update data from a new file the headers carry over. How do I fix this issue? Excellent video. Thank you.

  • @DucNhan3018
    @DucNhan3018 3 года назад +2

    Cant wait for your next videos. Very useful. Thanks a lot. Kudos for the great work.

    • @excelisfun
      @excelisfun  3 года назад +1

      Me either! I can't wait till next Monday! AND it WILL be epic: over 1 hour of Power Pivot and DAX magic!

  • @MultiThoa
    @MultiThoa Год назад

    Thank you very much. May God bless you!

  • @Penmazangmo
    @Penmazangmo 3 года назад +1

    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?

  • @TY-zl1vw
    @TY-zl1vw Год назад

    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.

  • @JWS1985
    @JWS1985 3 года назад +1

    This is great content. Thank you, sir! Your enthusiasm is makes it that much better!

    • @excelisfun
      @excelisfun  3 года назад

      You are welcome, John!!!!

  • @sevagjb
    @sevagjb 3 года назад

    Always you are in the first row
    thank Mike

    • @excelisfun
      @excelisfun  3 года назад +1

      First row, thanks, Sevag!!!!

  • @spilledgraphics
    @spilledgraphics 3 года назад +1

    Excel Grooooooving !!!! 😎😎 by Master Mike !

    • @excelisfun
      @excelisfun  3 года назад

      Grooooooooving = I love that!!!!!!!

  • @nelsonrln
    @nelsonrln 2 года назад

    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.

  • @hassanjawaid2806
    @hassanjawaid2806 2 года назад

    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.

  • @amenaislam4573
    @amenaislam4573 3 года назад +1

    Thank you Mike for this amazing video and appreciate your hard work for us.

    • @excelisfun
      @excelisfun  3 года назад +1

      You are welcome, Amena : ) : )

  • @mohamedchakroun4973
    @mohamedchakroun4973 3 года назад +1

    Nice to watch you again Mike :-) We are Up to Date and Up to Speed :-)

  • @GeertDelmulle
    @GeertDelmulle 3 года назад +4

    Great endeavor, Mike!
    And beautiful logos, too. ;-)

    • @excelisfun
      @excelisfun  3 года назад +2

      VERY beautiful logos and music intro!!!!!!!!!! The dude who made them is sooooo rad!!!!

  • @jerrymiles7804
    @jerrymiles7804 3 года назад

    Outstanding! My best regards from Limón, Costa Rica!

    • @excelisfun
      @excelisfun  3 года назад

      Thanks for the regards from Limon!!!!

  • @wcthrill
    @wcthrill 2 года назад +1

    Why couldnt we use a helper column for the month on the fsales table? Use the Text function to provide the month name?

  • @ljubicar1987
    @ljubicar1987 3 года назад +1

    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. 👍

    • @excelisfun
      @excelisfun  3 года назад +1

      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 : ) : )

    • @ljubicar1987
      @ljubicar1987 3 года назад

      @@excelisfun can't get enough, and looking forward to another video!

    • @excelisfun
      @excelisfun  3 года назад

      @@ljubicar1987 Woo Hoo : )

  • @patrickmcgill3542
    @patrickmcgill3542 3 года назад +1

    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?

    • @excelisfun
      @excelisfun  3 года назад +1

      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.

  • @ebenasare8661
    @ebenasare8661 2 года назад +2

    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?

  • @ashutoshsharma2527
    @ashutoshsharma2527 3 года назад

    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.

  • @johnborg5419
    @johnborg5419 3 года назад +2

    Thanks Mike. Can't wait for the rest. : ) : )

  • @parshuram230682
    @parshuram230682 3 года назад +1

    Thank you, i was waiting for this video since announcement.
    It is very helpful and easy to understand. 👍😍
    Waiting for coming Monday's

    • @excelisfun
      @excelisfun  3 года назад

      Glad it is easy to understand! Mondays are going to be awesome!!!!!

  • @briajones1203
    @briajones1203 7 месяцев назад

    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
      @excelisfun  7 месяцев назад +1

      I am not sure what you are asking. But the data in every video can be downloaded in the links below the videos : )

    • @briajones1203
      @briajones1203 7 месяцев назад +1

      @@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

    • @excelisfun
      @excelisfun  7 месяцев назад +1

      @@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.

    • @briajones1203
      @briajones1203 7 месяцев назад

      @@excelisfun thank you so much!

  • @Saad.PS2009
    @Saad.PS2009 3 года назад +1

    Thank you so much Mr Mike

    • @excelisfun
      @excelisfun  3 года назад +1

      You are welcome, so much : ) : )

  • @mirrafiqul5825
    @mirrafiqul5825 3 года назад +3

    Great job sir.

  • @shivamgarg6248
    @shivamgarg6248 3 года назад +1

    @ExcellsFun- What version of excel are you using because I can't find few of the tools or functions you're using

  • @wayneedmondson1065
    @wayneedmondson1065 3 года назад

    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
      @excelisfun  3 года назад

      Thanks for the comprehensively kind words about the video : ) : ) : ) Since you are an Excel Master, what did you learn from this video?

    • @wayneedmondson1065
      @wayneedmondson1065 3 года назад

      @@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!!

    • @excelisfun
      @excelisfun  3 года назад +1

      ​@@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 : ) : ) : ) : )

    • @wayneedmondson1065
      @wayneedmondson1065 3 года назад

      @@excelisfun Thanks Mike! My pleasure to contribute :)) Go Team!!

  • @ahawwas
    @ahawwas 2 года назад

    In 30:10, in the pivot we can add a calculated field equals to =sales/12, without a need to an intermediate table

  • @seanmen4141
    @seanmen4141 3 года назад

    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?

    • @excelisfun
      @excelisfun  3 года назад +1

      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

    • @bencacace5491
      @bencacace5491 3 года назад

      @@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?

    • @excelisfun
      @excelisfun  3 года назад

      @@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

  • @kss2066
    @kss2066 3 года назад +1

    Great Class!

    • @excelisfun
      @excelisfun  3 года назад

      The next 3 Mondays should be great fun!

  • @rastkogligoric833
    @rastkogligoric833 2 года назад +1

    Guru Mike,how is it when u drag ''Date" to the Rows field,pivot automatically creates and labels columns as ''Years";"Quarters";"Dates"?

    • @excelisfun
      @excelisfun  2 года назад

      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

    • @rastkogligoric833
      @rastkogligoric833 2 года назад +1

      @@excelisfun Field Settings/Layout and Print/Show item labels in tabular form did it for me!Thx :)

    • @excelisfun
      @excelisfun  2 года назад

      @@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 : )

  • @vijaysahal4556
    @vijaysahal4556 3 года назад

    Super duper hit sir 💯💯💯💯💯💯☝️☝️👌👌👌

    • @excelisfun
      @excelisfun  3 года назад

      Glad you like the hit : )

  • @saratharavabhoomi
    @saratharavabhoomi 3 года назад +1

    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.

    • @excelisfun
      @excelisfun  3 года назад +1

      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!

    • @saratharavabhoomi
      @saratharavabhoomi 3 года назад

      @@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....

    • @excelisfun
      @excelisfun  3 года назад

      @@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 : )

  • @Gorskiszlak
    @Gorskiszlak Год назад +1

    Amazing content.

  • @mohamedadjal8502
    @mohamedadjal8502 3 года назад

    Thank you, sir, for the video that you sent to me. I hope to watch your videos about the Visual Basic in Excel.

    • @excelisfun
      @excelisfun  3 года назад

      I do not tech VBA coding, only recorded Macros: ruclips.net/video/WKyN8e7XXjI/видео.html

  • @RZing
    @RZing 3 года назад

    Great video, as always

  • @simfinso858
    @simfinso858 3 года назад

    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.

    • @excelisfun
      @excelisfun  3 года назад +1

      That is a clever analogy!!!

  • @eslamnahla
    @eslamnahla 3 года назад +1

    great job! thank you so much.

    • @excelisfun
      @excelisfun  3 года назад

      You are welcome so much!

  • @yanbinlu3531
    @yanbinlu3531 3 года назад

    Hi Mike, How do Dax Calculate day on Day changes excluding holidays and weekends? Thanks!

  • @juniorbosso6943
    @juniorbosso6943 3 года назад +3

    Thanks!!

    • @excelisfun
      @excelisfun  3 года назад +2

      You are welcome, Junior : )

  • @Nikol0zi
    @Nikol0zi 3 года назад

    EPIC! THANKS MIKE!

    • @excelisfun
      @excelisfun  3 года назад

      Flad you like it, Nika!!!

  • @rajeswarynadarajan8347
    @rajeswarynadarajan8347 2 года назад +1

    Thank you sir

  • @JSUG4219
    @JSUG4219 3 года назад +2

    Mike, how does this course compare to your MSPTDA course (which was awesome, by the way!)?

    • @excelisfun
      @excelisfun  3 года назад +1

      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.

    • @excelisfun
      @excelisfun  3 года назад +1

      Also, this four video class compares each tool in more careful detail as the class proceeds.

    • @JSUG4219
      @JSUG4219 3 года назад

      @@excelisfun Thanks, Mike - you're the best!

    • @excelisfun
      @excelisfun  3 года назад

      @@JSUG4219 Go Team!!!!

  • @bandaralqahtani4356
    @bandaralqahtani4356 3 года назад

    Amazing!
    Thank you for this super lessons

  • @andresantoniocastroperez8429
    @andresantoniocastroperez8429 3 года назад

    .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

    • @TSSC
      @TSSC 3 года назад +1

      “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).

    • @andresantoniocastroperez8429
      @andresantoniocastroperez8429 3 года назад

      @@TSSC thanks for your help

  • @saizep22
    @saizep22 3 года назад

    Always amazing, Thank.

    • @excelisfun
      @excelisfun  3 года назад

      Glad it is amazing for you, Sai!!!!

  • @karimredouane2150
    @karimredouane2150 3 года назад

    REAL FUN Mike THKS

  • @andrewjohnson4352
    @andrewjohnson4352 Год назад

    I liked that video!

  • @ndelpurg
    @ndelpurg 3 года назад

    the excel Gods are pleased

  • @granand
    @granand 2 года назад

    In 2016, it expands in a single column with Column name Data Labels

  • @vishnu8899
    @vishnu8899 3 года назад +1

    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

  • @raitup00
    @raitup00 3 года назад

    These videos are part of a playlist? I'd like to add it to my favourites playlist

    • @excelisfun
      @excelisfun  3 года назад

      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.

    • @excelisfun
      @excelisfun  3 года назад

      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.

  • @testsample1005
    @testsample1005 3 года назад

    Yes Mike the same question from me as well, from I can start learning PowerBI.

    • @excelisfun
      @excelisfun  3 года назад

      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.

    • @excelisfun
      @excelisfun  3 года назад

      ​ @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 : )

    • @excelisfun
      @excelisfun  3 года назад

      Let me know in the comments how you like it : )

    • @testsample1005
      @testsample1005 3 года назад

      Your help is highly appreciated Mike. I am fan of you since 2015.

    • @excelisfun
      @excelisfun  3 года назад +1

      @@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!

  • @mohamedabdulatty7997
    @mohamedabdulatty7997 3 года назад +2

    Great 👌
    It will be a complete series ?

    • @excelisfun
      @excelisfun  3 года назад +1

      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.

  • @danjarupath
    @danjarupath 2 года назад

    Awesome 😎😎😎

    • @excelisfun
      @excelisfun  2 года назад

      Glad this helps, JB Racing!!!! What do you race? I race BMX : )

  • @ashea9112
    @ashea9112 2 года назад

    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.

    • @seandre59
      @seandre59 2 года назад

      Did you figure out how to separate the year and date in the pivot table

    • @ashea9112
      @ashea9112 2 года назад

      @@seandre59 no not yet

  • @wazeer1981
    @wazeer1981 3 года назад +1

    Amazing 🌷

    • @excelisfun
      @excelisfun  3 года назад

      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 : )

  • @shoppersdream
    @shoppersdream 3 года назад +1

    Very Nice! Thanks! When is the next video coming?

    • @excelisfun
      @excelisfun  3 года назад

      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

    • @excelisfun
      @excelisfun  3 года назад

      Glad you liked it!

    • @shoppersdream
      @shoppersdream 3 года назад

      @@excelisfun Thank you! Going to put it in my Calendar :)

    • @excelisfun
      @excelisfun  3 года назад

      @@shoppersdream Cool!!! LOVE your user name : ) : ) : ) : )

  • @gabormathe823
    @gabormathe823 2 года назад

    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.

    • @excelisfun
      @excelisfun  2 года назад +1

      Open csv file, copy data, paste into Excel, then manually fix data , like with join formula =Cell1&" "&Cell2

  • @13lila
    @13lila 2 года назад

    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.

  • @sravankumar1767
    @sravankumar1767 3 года назад

    nice explanation...

    • @excelisfun
      @excelisfun  3 года назад +1

      Glad you like it, Sravan!!!

  • @govind710
    @govind710 3 года назад

    Can u pls cover some use cases for solver

    • @excelisfun
      @excelisfun  3 года назад +3

      I already have full videos on this topic. Here is my free analytics class playlist: ruclips.net/p/PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ
      Videos 57 - 63.

  • @truekv5
    @truekv5 3 года назад

    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?

  • @kevinabrantes01193
    @kevinabrantes01193 3 года назад

    Hi! Where do I get the TXT file?