Join Curt Frye for an in-depth discussion in this video Visualizing table data in a chart, part of Learning Excel Cluster Analysis.
When you analyze your data using cluster analysis, it often helps to visualize the clusters using an xy scatter chart. And in this movie, I will show you how to create such a chart using the xy chart sample workbook which you can find in the Chapter Three folder of your exercise files collection. So, this workbook contains. Table of data and also all the distance and centroids calculations and then a list of starting centroids here and a centroid update information.
It is extremely hard to visualize any of this without using a chart, so we'll go ahead and create one. I want to plot the x and y axis values, so I will select cells A2 through B17, and then on the Insert tab in the Charts area, I would click the Insert Scatter or Bubble Chart button, and click the first scatter chart. And when I click that, my chart appears. And I'll just move it a little bit down so none of the data is covered.
So that's where my points appear. And you can see that I have one pretty good looking group here, one group here, and one group here. And again this is the test data that I've been using throughout this course. And the idea is to make the clusters easy to see. What we don't see in this chart are the centroids, so we need to add them. To do that with the chart selected, and on the Design contextual tab, click the Select Data button. Doing so displays the Select Data Source dialog box.
Then, in the legend entries. Portion of the dialog box, click the Add button. Doing so will allow you to add another data series. And for the series name, in that box, we'll type Centroids. C-E-N-T-R-O-I-D-S. Then click in the Series X Values box and click the collapsed dialog button here so that we can select the range. And the centroid's X values are in C field one.
So, I will select I3 through I6, and you can see. Oh, excuse me, it's I3 through I5. I'm only selecting three rows. So, I have I3 through I5 and I can click the expand dialog box button and now I'll click the Series Y Values collapse dialog button here. And select cells J3 through J5, which are the y components of the centroids. Click the expand dialog button again. And click OK. I see that the data appears inside of the Select Data Source dialog box so I can click OK.
And I see that my centroids appear here in orange. Because I created them as a separate data series, they are colored differently then the points that were in the original table and that's very useful. And now, when I update the centroides by copying my centroid update values in the cells L6 through L8. Then I'll click the Copy button, select cells I3 through J5, click the Paste button's down arrow, and click Values.
And doing so updates the positions of the centroids, in the chart, because there's a live link between the data and the chart. And if I Copy again, and select cells I3 through J5 and Paste values. Then I get my centroids and they appear to be right in the middle of each group. And if I click away from my data and press Escape to get rid of the Copy-Paste mode. I can see that the centroid updates values are exactly the same as the existing centroids, so I can stop my analysis.
- What is clustering?
- Setting up your Excel table
- Creating a list of centroids
- Identifying the closest centroid
- Updating centroids using averages
- Automating updates with macros
- Analyzing your data in Excel
- Visualizing data in a chart