**Multidimensional Scaling** is a way to visually present the similarity between objects (brands, product attributes, etc.) in a 2-dimensional scale, i.e. a perpetual graph. Multidimensional Scaling can reduce data in an XY plot. In this article, I will show **how to perform Multidimensional Scaling in Excel**.

**Table of Contents**hide

## Download Practice Workbook

## What Is Multidimensional Scaling?

**Multidimensional Scaling** is a visual display of similarities or dissimilarities between objects. Here, an object can refer to many things. In business, object refers to brands, product attributes, etc.

Whichever field (politics, science, media, etc.) you are from, the basic idea of **Multidimensional Scaling** is the same. If you consider similarities, the more similar objects will be in closer positions on a graph. Not only that, you can use Multidimensional Scaling to reduce the dimensions for data that have a huge number of dimensions.

## Steps to Perform Multidimensional Scaling in Excel

To perform multidimensional scaling in Excel, follow the steps below.

### 1^{st} Step: Conduct a Survey and Create Initial Data

- Suppose, we are going to do
**Multidimensional Scaling**for*5 brands*considering their*6 attributes*. - Before we start, we need to perform a customer survey on these brands and gather the ratings (
*1 to 9*) in an Excel spreadsheet. - Here is an example.

**Read More: ****How to Create a Rating Scale in Excel (4 Easy Ways)**

### 2^{nd} Step: Reverse Data

- Well, this is an optional step. You can now reverse the ratings by subtracting them from 10. The formula will be as follows.

`=10-C7`

**C7**= Old rating

**Read More: ****How to Scale Data from 1 to 10 in Excel (3 Quick Methods)**

### 3^{rd} Step: Find Distances Between Objects and Apply a Suitable Scale

- Now, create another section named “
**Scaled Data Comparison**”. - Here, in
**cell K26**, apply the following formula where we have used the**SUMXMY2 function**.

`=SUMXMY2($C$16:$H$16,C17:H17)`

- This formula will return the distance between brands 1 and 2.
- Drag this formula down.
- In a similar manner, write the following formula in
**cell L27**.

`=SUMXMY2($C$17:$H$17,C18:H18)`

- In
**cell M28**, write the formula below.

`=SUMXMY2($C$18:$H$18,C19:H19)`

- And finally, the following formula will be in
**cell N29**.

`=SUMXMY2(C19:H19,C20:H20)`

- After all, go to
**cell C25**and write the following formula.

`=IF(K25="","",K25*9/MAX($K$25:$O$29))`

- This formula will scale the distances between the brands with respect to the maximum distance.
- The multiplier 9 is not mandatory, choose any suitable number.
- Drag the formula down and right.

- In a similar fashion, find the scaled distances between the 6 attributes. The formulas are in the workbook, check them out.

**Read More: ****Scaling Formula in Excel (6 Practical Examples)**

### 4^{th} Step: Gather All the Distances Together

- Now, accumulate the distances in a separate table like the following image.

**Read More: ****How to Use Scaling Option in Excel (4 Quick Ways)**

### 5^{th} Step: Set Starting Map Points and Copy Them to Another Location

- Look at the following image. Here we have set some starting points. These distances are considered with respect to Brand-1.
- For example, Brand-2 is 7.84 units far from Brand-1. So we set it as (7.84,0).
- Similar approaches go to the other points.
- The 0 ordinates are alternating so that the data are scattered in a regular fashion.
- We have set (0,0) to Brand-1. We could specify any numbers instead since we will rectify these ordinates using Excel Solver.
- Finally, copy these values (if there are any referenced cells, make sure that there is no referenced cell in the copied region).

**Read More: ****How to Perform Scaling for Numbers in Excel (7 Effective Ways)**

### 6^{th} Step: Find New Distance Data

- From these map points, find new distances between brands and attributes at this step. The formulas to find these distances are in the workbook.

**Read More: ****How to Do Data Scaling in Excel (3 Easy Methods)**

### 7^{th} Step: Apply the RANK.EQ Function to Old and New Distances

- Now, find the ranks of these distances using
**RANK.EQ function**. - Apply the following formula in
**cell C87**and copy the formula all over the data table.

`=IF(C42="","",RANK.EQ(C42,$C$42:$M$52,1))`

- Similarly, rank the new distances.

**Read More: ****[Solved!] Excel Scaling Issues (2 Easy Methods)**

### 8^{th} Step: Find the Correlation Between Old and New Distances Ranks

- Now, we will find the correlation between the old and newly generated distances using the
**CORREL function**. - Apply the following formula in
**cell G114**.

`=CORREL(C87:M97,C102:M112)`

- The correlation is only 31.28%, this means, the existing distances are not representing the actual distances quite well. So in the next steps, we will maximize the correlation using the Solver add-in and make changes to Final Map Points according to it.

**Read More: ****How to Perform Custom Scaling in Excel (3 Easy Ways)**

### 9^{th} Step: Enable Solver Add-In

- This is another optional step if you don’t have the Solver add-in already.
- Press
**Alt+F+T**and the following window will appear. - Go to the
**Add-ins**section and find the**Excel Add-ins**and press**Go**.

- Now, select
**Solver Add-in**and press**OK**.

- The solver add-in will be added now.

### 10^{th} Step: Maximize Correlation Using Solver

- Now, go to the
**Data**tab and click on the**Solver**command.

- In the
**Solver Parameters**dialog box, set the objective as the correlation cell, here the**cell G114**. - Click on the
**Max**button. - Then select the
**Final Map Points**reference cells,**G57:H67**. - Set the constraints.
- Mark the
**Make Unconstrained Variables Non-Negative**box and select the**Evolutionary**Moving method. - Then press the
**Solve**button.

- To add constraints, press on
**Add**, and the following window will appear and set suitable constraints.

- After the solving is complete, keep the solver solution and press
**OK**.

- Look at the correlation value, now it’s
**94.84%**way higher than the previous one.

- The following image shows the changed
**Final Map Points**after you run the Excel Solver successfully.

### 11^{th} Step: Create a Perpetual Map

- Finally, to plot a
**perpetual map**, select the**Final Map Points**and go to the**Insert**tab, then click on**Scatter Charts**and then press**More Scatter Charts**.

- Now, go to
**XY (Scatter)**and select the 2nd type, and press**OK**.

- After
**applying formatting to the scatter chart generated**, we will get a perpetual map like the following image.

## Conclusion

Doing Multidimensional Scaling using Excel is never an easy task! So, if you have any questions regarding this topic, please leave us a comment below. And, visit our blog **ExcelDemy** for more such articles on MS Excel.