K-means Cluster Analysis With Excel - A Tutorial

In this video I will teach you how to perform a K-means cluster analysis with Excel. Cluster analysis is a wildly useful skill for ANY professional and K-means a great first clustering algorithm - no VBA code required!
Cluster analysis is used in all areas of business:
👉 In HR analytics for understanding similar employees
👉 In marketing analytics for customer segmentation
👉 In supply chain management to optimize logistics
The list goes on.
The great news is that you can perform K-means clustering in Excel using simple tools like VLOOKUPs and a tiny bit of Power Query.
This video is a fairly in-depth tutorial on K-means clustering, so feel free to jump around if a full tutorial experience is not what you need.
☕ If you found this content useful and would like to support the channel, you can buy me a coffee: www.buymeacoff...
★★★ My online data analysis courses ▶ school.daveond...
-----------------------------------------------------------------------------------------------------------------------
Video Resources
-----------------------------------------------------------------------------------------------------------------------
💻 Get the Excel workbook & Notepad file for this video:
github.com/Dav...
💥 Learning R programming is easy! 💥
Conduct powerful cluster analyses with R:
📺 • Learning R Programming...
💻 Example of a scree plot for choosing the number of clusters:
rstudio-pubs-s...
-----------------------------------------------------------------------------------------------------------------------
Excel Analytics Training
-----------------------------------------------------------------------------------------------------------------------
👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist:
📺 • Introduction to Busine...
Stay healthy and happy data sleuthing!
#KMeansClusterAnalysisWithExcel #KMeansClusterAnalysis #ClusterAnalysis #KMeans

[@DaveOnData]

💥 Learning R programming is easy for Excel users! 💥 📺 kzbin.info/www/bejne/g3_TpKygiNqFaJI 👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist: 📺 kzbin.info/www/bejne/rnq7qqCXl955f68

[@nagendravishwamitra3652]

One of the best lecture on k-means ..if you were in front of me i would have kissed you ..Greetings from India

[@mbaintheusa1165]

Thanks for the video! But what if the measures in different columns are way different? Lets say, we have spend in thousand dollars and some other parameter in Sq meters? Is it correct to say that we would need first to normalize date, to bring to some comparable values?

[@DaveOnData]

Great question! The results of k-means can be skewed when the data is not on equal footing. A standard solution (pun intended) is to apply the Z-score to each column before clustering to help with this issue.

[@Dfcameron13]

I love the detailed explanation. Dave Langer has a great way of explaining things conversationally. As mentioned, the first problem the algorithm needs to solve is the starting centroids for the proposed clusters. This is actually a critical problem that does not have a good general solution. I do this for a living, and use domain expertise vs. random. My only additional recommendation is to realize that you don't often know what cluster each point belongs to at the start. So, his setup of a "red cluster" and a "green cluster", where you can find the means, as Dave said, is overly simplistic. In real-world situations, it isn't that easy to find means across dimensions.

[@DaveOnData]

Thank you so much for taking the time to add such a valuable comment to the video - much appreciated! 🙏🙇‍♂️ Also, thank you for the kind words regarding my teaching style, this is also much appreciated. 😁

[@caitlinthompson458]

This is such a great tutorial. I've been trying to do this all week (started in python but came back to Excel) and this is exactly what I needed. Really thorough, lots to think about and still easy to follow!

[@JoseOlivar-sk5rv]

Hello, great video! Do you have a video showing how you would do this using R?

[@RohbertWhite]

You clearly know your stuff, but the excel worksheet is blurry and often unreadable. So I'm having to make sense of much that you are talking about from your verbal description of what you are doing. It might as well be an audiobook instead of a video. Constructive criticism only.

[@DaveOnData]

Interesting. I just watched the video, and it was crystal clear. The video was recorded in 1080P.

[@RohbertWhite]

@@DaveOnData Thank you for your input. My computer did auto adjust to 480p for some ungodly reason. So I was wrong about that part, however, the writing is still very small and I have vision problem after starring a screen to long. It would have been helpful if he would zoom in on the particular chart that his is referring rather than me having to look at all three chart while he's talking about one. I may just be old, blind, cranky, and stupid. good possibility.

[@user-rc2vh6zu8d]

If you would add a timeline (very easy in youtube) it would make navigation within this great video even easier.

[@DaveOnData]

My new videos have video chapters, but haven't had time to go back and update my older videos.

[@andrew-jones-data-science]

k-means is extremely useful - this is great!

[@DaveOnData]

Agreed! There's a reason why is the de facto standard clustering algorithm - easy to understand, easy to implement, and useful in so many business scenarios.

[@festusdavidoundo6227]

Thanks Dave, Got it.

[@SMILE__6208]

Hi Dave! This video is very helpful, but i would like to know how you were able to add your existing tables into the power query editor. i am unable to select any other table from the worksheet, only the current one.

[@AlexanderKonig2577]

Hi David, great video!!! today there are several new formulas, xlookup is an amazing and much easier way than vlookup. and for you huge and monster formula use the SUMXMY2 function. To find the average of each cluster (top rows) use the averageif formula... muuuuch easier and skip the power query step. Finnaly you shouldt run a min solver in order to find the min distance among variables and centroids.

[@AchooUsman]

Can u tell me the formula how to do it

[@jissbela]

this video is awesome, thank you David!

[@enanta7]

Can we cluster text with this method too?

[@DaveOnData]

You can cluster text documents using k-means, but the data must be preprocessed first. Check out my Python in Excel videos to learn more.

[@paddies64]

A great thank you Dave, don't know where you're from but very clear accent and a great teacher cos the subject is not that easy to teach. Need te review some parts of the video especially the one with the 1km formula ;-) but it's very clear ! Deserve a good bottle of wine Dave!

[@boknows8263]

He is from US, obvious from how he speaks English.

[@aaronporche6820]

DUDE i think you sir are maybe the best. most well spoken teacher i have ever had. THANK YOU FOREVER YOU ARE SPECIAL. I am a logistics coordinator and trying to schedule work in excel with at least 10 dimensions of variables, this is a miracle for me ill figure out how to use R for sure cause this is exactly what I need. My brain hurts so bad this sounds easy hahaha I think its mostly the teacher tho

[@DaveOnData]

Wow! So glad you found my content useful. If you're interested in k-means clustering, you might want to check out some of my newer videos. K-means using Python in Excel: kzbin.info/www/bejne/m5iQYmacm86anas Combining k-means and decision trees using Python in Excel: kzbin.info/www/bejne/hWWaaqiNgsqSfJI

[@strategy_gal]

Great tutorial, David! This is very helpful!

[@DaveOnData]

Wow! Thank you so much for taking the time to offer feedback. I'm flattered.

[@abhinav18rana]

Dave, you are a master at simplistic explanation of complex topics. I would be coming to your channel more often now and will recommend to my team as well who are new to analytics.

[@DaveOnData]

Thank you so much for this feedback! As a content creator and teacher, such recommendations are strong motivation to continue my mission.

[@mikastamaria3062]

Hello! May I ask how the end results can be put into a graph for visualization to show the actual result of the clustering? Trying to search online but I can't seem to find one that aligns to this method. Very informative tutorial on the groundwork though to understand the foundations. :)

[@kisholoymukherjee]

As always, fantastic video Dave.

[@DaveOnData]

Thank you! I am so glad to hear you are finding my content useful.

[@ejpeninger]

Excellent tutorial, thank you so much Sir!

[@DaveOnData]

You are most welcome, hope you find the content useful!

[@rainashrivastava6182]

Hey, I have a media data set in which, Rows are episode names and Columns are the different slot timings, So Let's say episode A has data for only 3 slots, and Episode B has data for 4 slots and so on. How do I apply K means to this Data set?

[@pedroserpa2897]

Great Video. Suggestion1 If in the iteration 1 you calculate de sum of the minimum distances. After that, you use the "excel->Data->Solver" to find your minimum of that sum by changing your initial points. With that, excel will do all the work for you in a glance.

[@krispsimpson79]

You mentioned that for categorical data there are better alternatives for cluster analysis - what would you recommend please?

[@willykitheka7618]

Great job Dave! I really enjoyed that entire tutorial

[@DaveOnData]

Thank you for the feedback and so glad you are enjoying my content!

[@dataanalyst1012]

In k means clustering, is there an assumption in numbers of observations and variables? Would having variables greater than observation affect the results of clustering and make it less accurate?

[@spilledgraphics]

7 emojis: 🤯🤯🤯🤯🤯🤯🤯 ...... one more.... 🤯❗ amazing David !

[@spilledgraphics]

@David, have you thought combining LAMBDA function for the iterative calculations ?

[@DaveOnData]

Thank you for the feedback, very much appreciated! 🙏🙇‍♂️ I considered a number of options, but in the end I decided to go with the simplest (albeit most manual) solution.

[@PaulAcito-ce2ex]

Just fantastic explanations and approach. Thanks for sharing this.

[@williamstan1780]

David Thanks for the clip, very useful and informative. May I suggest to have one for Mixed Data (Category and numeric data? I have been searching it for ages but in vain. Thanks for your help

[@sebastianbriceno5293]

Great video! Thank you so much. Can we to access to the xls file to understand the formulas that you showed in the notepad? Regards

[@willykitheka7618]

at 39:27 David mentioned that you can find the notepad and the excel file in his Github repo.

[@DaveOnData]

That's correct. The GitHub repo link is in the video description.

[@sebastianbriceno5293]

@@willykitheka7618 Thanks!

[@sebastianbriceno5293]

@@DaveOnData Thanks!

[@topfundus1093]

Prima, vielen Dank! Konnte nun mein eigenes Programm mit VB zur flexiblen Clusterbildung beliebiger Wertepaare schreiben. Die Daten (x, y) übernehme ich zunächst aus der Tabelle in ein Array. Dieses lasse ich dann mit verschachtelten Schleifen n-mal (=Iterationen) durchlaufen, bis alle Wertepaare auf Basis der kürzesten Entfernungen einem Cluster(-Punkt) zugeordnet sind, ohne das weitere Schleifendurchläufe diese Zuordnungen verändern. Es erfolgt der Ausstieg aus den Schleifen. Die Clusternummern werden dann auf einen Schlag in die Quelltabelle, in eine neue Spalte eingefügt. Fertig! Das geht alles blitzschnell und ohne die vielen, doch ziemlich aufwendigen Tabellen und Formeln, die Du im zweiten Teil Deines Tutorials zeigst. Dazu kommt, dass ich mein k-Mean-Programm universell nutzen kann. Es ist gleich, welche (numerischen) Datenspalten einlesen und clustern kann. Great. Thanks! I was now able to write my own program with VB for flexible clustering of any pairs of values. First, I take the data (x, y) from the table into an array. I then run this through nested loops n times (=iterations) until all pairs of values are assigned to a cluster (point) based on the shortest distances, without further loop runs changing these assignments. There is an exit from the loops. The cluster numbers are then inserted in one fell swoop into a new column in the source table. Finished! It's all lightning fast and without the many, but rather complex tables and formulas that you show in the second part of your tutorial. In addition, I can use my k-mean program universally. It doesn't matter which (numeric) data columns can read and cluster.

[@Persianchannel1]

dude you are a great teacher. i appreciate your passion of teaching. your explanation was great. thank you so much. your videos deserve more views 🌹🌹

[@sandaranote6979]

Thank you Dave for this video! It was interesting and your explanation is really clear and useful =)

[@swadminstudynwork1360]

Thanks!!! I learned a lot with your video :)

[@husseinadam4724]

You gained a new subscriber now thanks Dave

[@ultimatepravint]

Great video, plain and simple language explanation

[@mamaraci]

Hello! Thanks for the detailed explanation. But, do you have the same, but fully based on PowerQuery? Initially, I want to try the same, but with ~2 million (lines) of customers, and >50 columns (dimensions). I think VLOOKUping them is not an optimal way to do that ( Or, my best alternative is to switch to R / Python with these volumes?

[@boknows8263]

Yes

[@vacilando86]

You're my Hero. God bless you 🤝

[@venkatesh0075]

Thanks for sharing. Can you also show how this works in Python pls

[@DaveOnData]

My apologies, but I don't do Python. I'm a Excel, SQL, and R guy. 😁

[@abhinav18rana]

@@DaveOnData I usually do stuff in excel first and then transition the logic to a tool or language. Excel makes me grasp the data and logic much better which in turn makes my life easy when the time comes for the project to upscale on a tool/language for heavy processing.

[@DaveOnData]

@Abhinav Rana - I would suspect that the way you approach analytics is very common. Prototype in Excel and move to another technology if needed.

[@AchooUsman]

​@@DaveOnDataDavid is it easy for any non programmer and no computer science student to use R ?

[@btomblin386]

This was an awesome video!

[@kevinwflanagan9156]

Can you add the formula to download?

[@DaveOnData]

Apologies, I'm missing the intent of the question. The formula to download what?

[@kevinwflanagan9156]

@@DaveOnData The Notepad formulas you showed

[@willykitheka7618]

@@kevinwflanagan9156 you can find the notepad in David github repo. (watch at 39.27)

[@DaveOnData]

This is correct. The link to the GitHub repo is in the video description.

[@ajaykulkarni576]

Great Content David!

[@DaveOnData]

Thank you, Ajay, for the feedback, much appreciated! 🙏🙇‍♂️

[@ie95_Moh]

Sir i need to contact you please i have a question about k means