For 16 years, free Excel & Power BI Highline College classes, videos, files & pdf notes here at KZread. All to allow you to become awesome with data analysis, financial calculations and model building! Over 3,700 videos: kzread.info/dash/bejne/eYKcsJOTp5vVfZM.html . I am a business analytics professor at Highline College, author Excel and Power BI books, and many years ago ran a world famous boomerang manufacturing company called Gel Boomerangs. Hopefully these free classes and videos will help you to become better with Excel, Power BI and data analysis so you can save time at work, help coworkers become better, and save so much time that you can take an extra vacation!
Go Online Excel Team!
P.S. if you want to Donate, use PayPal link below : )
Пікірлер
👌🏽👌🏽👌🏽
MIke, you are always our MVP!
Why you started speaking slowly ?
You Are Big Boss Of Excel
Done! Keep going Mike, for me (and I'm pretty sure the entire team too) you are an authority in the field of Excel, Power Query and Power BI.
Thanks alot Mike! It is a excellent reference video for me! I have a question. In the video (16:50) you have shown that Pivot Table can group Dates into Year, Quarter etc. Can it be done for six monthly period? [Like from 01 Jan to 30 Jun and from 01 Jul to 31 Dec ] Thank you once again!!!
Thank you very much ^_^ .
You are welcome very much!!!
These prices are like the Rock and Roll Hall of fame, they mean nothing. Great names are not included, to this day in the RRHF and that doesn't mean nothing!
Absolutely right: nothing!!!!!!
starting this on a friday at 7 pm, i wonder in which day i will finish this and the hw, wish me luck phewww
I am wishing you luck!!! You will do great : )
It was my honour to complete the form.
Done and done, Sir! Thank you, Celia!!!😊
Clear explanations and practical examples made this seemingly complex concept easy to understand and significantly enhanced my knowledge and confidence using Excel.
Glad to help!!!!
my notes from this video for others to use: I followed this video using Excel 365 in browser, so some features are different/inaccessible keybind: ctrl + 1 = opens format cells dialog box WHEN selecting a cell/cells setting up a table with formula inputs can make your life easier when using formulas on a table Want to create a visual chart of data? Select your labels, and your numbers, then go to insert, and click the chart you want to create You can format the chart after you create it by ... right click -> format Try to avoid chart junk! Chart junk is something that doesn't help you articulate your message. Ask yourself if numbers, labels, etc are necessary You can manually delete features of the chart by clicking the visual feature itself in the chart, and hitting the delete key Can't seem to figure out where the option is for "vary colors by point", it doesn't appear in the format options while using Excel 365 in browser Also can't seem to create a dynamic chart title with Excel in browser. But the formula would be =*chartName*!$A$1 To snap chart to grid, select grid and hold alt key Excel Golden Rule: if the formula inputs can change, then you put them into a cell and label them! then refer to those formula inputs with cell references end of video goes over page setup. Things to note before printing: - name your worksheet - use proper number formatting so it is readable AND visually accurate($40,869 -> $40.868.9)
Is it only in Microsoft 365?
Snazzy!
: ) : )
LOVE THIS ONE! Thank you!!!
You are welcome!!!!
I can't believe how far I've come. This was the video that got me through my accounting masters Excel class 5 years ago, and now all this stuff is my bread and butter.
I am so glad to help with the butter on the bread!!!! : ) : )
@@excelisfun I'm probably the best at formulas on my team, and I give you all the credit
How would ensure the file source is updated automatically when doing a refresh of the query sheet if my file names change over time? This happens because everything I download a new sheet it saves with new file name.
Bro you are Legend. God Bless you.
Glad the videos help!!!!
OMG thank you. I have been trying different formulas for days now. This one worked like magic. Thank you so much
You are welcome so much!!!
love your pacing and engaging teaching style. I figure I would leave my notes here for others to use. I took these notes while using Microsoft 365 Excel in the browser, so some features are accessed differently: Worksheets are different sheets of the WorkBook. worksheets example = months. January, feb, march , ... cell cursor options - select - single replace - multi row/column replace. multi-replace can numerate automatically. quiz 1, quiz 2, quiz 3, ... keybind: tab = goes to next cell, default by row. or if selected a range of cells, go to next cell in the selected range of cells keybind: ctrl + a = select whole table can highlight rows or columns with color there is a formula selection button (add, subtract, sum, etc) keybind: alt + equals = sum function starting a cell with = is ALWAYS a formula. and = will give formulas and their descriptions to choose from to select a range of cells while creating a formula, click and drag ( =AVERAGE(*clickanddrag*) ) you can click a cell while creating a formula to use that cell within the formula. which you can also use arrow keys for formula used on a cell can be selected and viewed in the formula bar above keybind: F2 while selecting a cell = show formula used on the cell keybind: F4 while selecting a cell when creating formula = LOCKS this selected cell to formula ($H$2 = absolute lock, H2 = relative). formula when copied/dragged, is relative by default (H2). so formula moves with position. use f4 to lock to a selected cell to solve this ($H$2) number formatting good for changing the cell type. ex: have currency number? use number format dropdown to turn number in cell to currency number formatting doesn't change the actual data of the cell, just its appearance to the user keybind: ctrl + z = undo Page Layout - hit ALT key when in page layout to get visual view of keybinds for options - page setup in page layout gives customization for how the page will look when printed (portrait vs landscape, margins, header, footer, etc) select different and multiple areas of cells using the ctrl key. it allows start and stop select, then start and stop select, ... to copy a worksheet - right click sheet, select copy, then right click another sheet, and select paste. this creates a new copy
I thought I am good at mixed cell references, but I learned a lot from this video. Thanks a lot for such beautiful channel ^_^.
You are welcome for the beauty!!!!
Thanks so much for this, I am currently applying to jobs post-uni that will likely need me to know how to use excel, and this has certainly acted as an amazing stepping stone!!
This is why I post these free classes: help people to get and keep jobs - and have fun doing it!!!!
Yo mike (M9:M13*N6) this is my command but it only calculating m9*n6 why ?.....!
Very good tutorials, learning a lot! But many shortcuts doesn't work on Mac. Is there any workarounds, or do I just have to keep doing it the slow way?
Unfortunately, Macs are not used in the working world. I like you started out on my beloved Mac back in the 1990s. But when I tried to get a job, none of the skills I had worked on PC. So I switched. The workaround is simple: you must drop the Mac and get a PC. It is one of the best things you can do for your career. Also, just as back in the 1990s, the PC version has more and better tools than the Excel Mac version.
Thanks for sharing 😊
You are welcome!!
Thanks very much Mike as always. Great explanation.
You are welcome, Legend S!!!!
Thanks for this spectacular lesson !! This file give us tons of new tips to learn !!!!
You are welcome!!!
Ekdum kadak bro
Hi Mike, I would like you to make few videos on Excel FMWC eSports challenges. I see Diarmuid Early has some great content in his channel, but he is so fast and i couldn't follow him through.
I am going to need to reference this video more than once! Saved in my favs! Awesome!
Yes, at first we all do, but then we all get it : )
@@excelisfun For how my brain is wired, the first options work better! I may try the second later down the road :) Funny how some of these videos never age. Always great to go back to. Thanks again Mike!
I know this is an old vid but I am having to undertake a refresher and just tried the scenario manager example and I have been on it for a long time as I have completed the same example a number of times and here is why. When I enter the 3 scenarios all is fine but when I try to use Scenario on QAT I get a message saying “Redefine scenario based on current cell values? And does not matter which I choose it all stops working so I have completed another and stopped at the Show stage and all is fine. Anyone else seen this message or had this problem?
Your videos are awesome. Thank you very much!
You are welcome very much!!!
Thanks man... Easy to follow guidance indeed~ got it the first time on your guide👌🏾😎
Glad to help!!!!!
Mike is All-Time MVP in my book 🥇
Thank you : ) : ) : ) And I will continue to be an all-time contributor of videos, files and free classes for our Team!!!!!
We all stand with you Mike ❤
Thank you very much. It is wonderful to stand side-by-side with our whole Team!!!!!!!
I cannot seem to get the correct file or setup for this video. I downloaded the files to my PC. I opened the "17-...Class Start" file and then went to change source like you said to do to bring it to my folder and I'm just not starting at the same place. :(
You pointed the destination file to the source data folder? And that did not work? I am not sure what is going on. You said you are not starting at the same place? What place is that? WHat do you see? What do you not see?
@@excelisfun I'm starting from the ClassStart file which is a PowerBIDesktop file. When i change the source file to my version of your "CSV Files" saved to my PC. I click Close and a pop-up appears at the top in yellow about "Pending changes to apply". I click apply changes. At this point I'm still in the file with tabs at the bottom beginning with "Formulas", "Total" and many more. But from your video you're in PowerPivot so it says "fSales" and has only three more columns (names of your queries) after it. I'm sorry to bother you bc everyone else seems to get it. Something on my end seems off. I'm not in the right spot.
@@kyleepps8528 I am not sure. Can you just import the csv file and not connect to the one that I created?
@@kyleepps8528 If you see tabs at the bottom beginning with "Formulas", "Total", on the left can you do to the data tab, or model tab?
@@excelisfun I guess the best way to describe my confusion is at 15:20 you have a spreadsheet with 4 tabs. How do I get to that part. Those are the names of the queries. I don't have a file that shows that from the downloads.
Bravo!!! pour tu ce que tu fais pour la diffusion du savoir, mille merci.
You are welcome!!!!
first and foremost, thank you so much for these great videos!! only question I have around the 25:10 mark is why do you multiply the discount? cant seem to wrap my head around that because I feel like itd make more sense to do the retail price minus the discount THEN multiply by quantity. edit: I just realized that column is the decimal version of the discount percentage. ex: 0.171 is the discount of 17.1% hence is why you need to do the 1 minus the decimal to get the final answer after the discount.
If the product costs $1 and the discount is 17%, If you multiple 1*0.17 = $0.17. That is the amount of the discount, not the amount that the customer should pay. But 1*(1-0.17) = $0.83, which is exactly how much the customer should pay.
You are great Mike! I completed the form. You are the best. Cheers.
I find your videos extremely helpful. I wanted to ask about obtaining the "Existing connections" feature, as it seems to be missing in our current version, Office 365. Could you please provide a solution for this issue? or you have an update video for it?
I immediately liked this for the enthusiasm alone. Thanks for making this task a painless exercise!
You are welcome!!! I am glad that this 16 year video can help. If you want a more recent set of videos: kzread.info/head/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
Hello, I am a student at GSW Major: Accounting, I'll be using your videos to help me stay on track for graduation, and to prepare me for my future job! Thank you, I know you put a lot of work into this, I want you to know it's being put to use!
Yes!!!! I love to hear it is being put to good use : ) : ) : )
Mike, Just filled out the form. I have used many of your Excel Magic Tricks as a starting point for multiple improvements in data handling and analysis at work.
Thanks!!!! I am glad to help with data analysis improvements at work : ) : )
oh my god your charisma 😂 ''no way it works!" it defenatly does! thank you!! you made it easy to understand !
Glad to help< Laura!!!
Done. And I wouldn't have done it for anyone else.
I thank you dearly : ) : ) : ) Go Team!!!!
I'm happy to have been probably 500-1,000 of those views!
Nobody deserves it more than you Mike! Take care of yourself!
Hello. I don't understand this part: At 21:33 into the video, it is mentioned that the visual criteria flow into the MAX() function? Is it really so? Isn't MAX() working on the dRegularIncrements table after ALL() clears all visual filters? If MAX() is working on dRegularIncrements without the visual filters, how is running total calculated? At 22:44, the MAX() does pick the upper limit, but there is no ALL() function invoked before MAX() to remove the visual filters in this test/proof, which is why this test/proof succeeds. Please provide a more detailed explanation. Thank you!
If there was an Excel Nation where Mike resided in his Palace, I would happily take the position of a palace guard.
Thank you for offering to be palace guard - the full Team thanks you for all the that extra fun that is possible, with the added security : ) : ) Go Team!!!!!