# How to create a scatter plot in Excel with 3 variables

You can watch a video tutorial here.

Graphs are great ways to visualize data and Excel has tools to build many types of graphs. Excel also has a variety of formatting options and features that can be used to enhance visualizations. A scatter plot shows you the relationship between a set of variables and is usually built on 2 variables as this represents 2 dimensions i.e. the horizontal x-axis and the vertical y-axis. As it is not possible to introduce another axis in 2-D, the third dimension can be added as the color or size of the dots. In this example, the third dimension we will add is the color of dots to represent the 3^{rd} variable.

In this example, we are looking at the relationship between the kilometers driven and the selling price of previously-owned cars. The 3^{rd} variable to be added is the manufacturer of the car. To create the scatter plot, we use the following approach:

1. Identify the unique values for the 3^{rd} variable

2. Create a new column for each unique value

3. Populate the columns with the corresponding value of the selling price

4. Use the new columns with the kilometers driven to create the scatter plot

### Step 1 – Create columns for each unique value

– The unique values of the manufacturer are:

>Tata

>Maruti

>Honda

>Mahindra

– Type each of these values as new column names in the table

### Step 2 – Create the first formula

– Select the first cell of the ‘Tata’ column

– Type the formula using cell references:

=IF (Tata = manufacturer, selling_price, NA())

– This function checks if the manufacturer in the row is the same as the column name

>If it is true, then the selling_price is populated in the cell

>If it is false, then an #N/A value is to be populated

– Make the ‘manufacturer’ constant by selecting the cell reference and pressing **F4**

### Step 3 – Create the formulas for the other columns

– Create the formula in the first cell of each of the other columns using the respective column name

### Step 4 – Copy the formula

– Select the cells containing the formulas

– Using the fill handle from the first cell, drag the formula to the remaining cells

OR

a) Select the cell with the formula and press **Ctrl+C** or choose **Copy** from the context menu (right-click)

b) Select the rest of the cells in the column and press **Ctrl+V** or choose **Paste** from the context menu (right-click)

### Step 5 – Select the data for the chart

– Select the following columns:

>km_driven

>Tata

>Maruti

>Honda

>Mahindra

### Step 6 – Select the chart

– Go to **Insert > Charts**

– Click on **Insert Scatter (X,Y) or Bubble Chart**

### Step 7 – Choose the type of Scatter plot

– Select the first **Scatter **option

### Step 8 – Format the graph

– Select the chart

– Go to **Chart Design > Add Chart Element**

– Choose whatever elements that need to be added to the chart to make it easy for the readers to understand e.g. Legend, Chart Title, Axis Title

### Step 9 – View the Result

– The scatter plot is displayed with 3 variables