Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Dynamic Scatter Plot in Excel (3 Easy Methods)

Scatter plot is used to plot a diagram of two or multiple variables using Cartesian coordinates. It shows the relation between the variables through a visible graph. Not to mention, the variables can be in sets. Dynamic scatter plot means the change of any variable value in the dataset will create a change in the scatter plot. In this tutorial, I will show simple steps to create dynamic scatter plot in excel.

dynamic scatter plot excel

The above GIF is an overview of the dynamic scatter plot. It shows how the scatter plot functions after filtering by Business Unit.


Download Practice Workbook

You can download the practice workbook here.


3 Easy Methods to Create Dynamic Scatter Plot in Excel

I have a dataset containing information about the salary statement of employees. This dataset has attributes such as Full Name, Business Unit, Years of Experience, and Annual Salary. Using this information I will create a dynamic scatter plot of Years of Experience and Annual Salary. Here, I used Microsoft Excel 365. You can use any available Excel version.

sample dataset


1. Employing Pivot Table to Create Dynamic Scatter Plot

You can use Pivot Table to create a dynamic scatter plot that can also be functional with filters. Follow the below steps:


Step-1: Creation of Table from Dataset

In the beginning, we will convert the dataset into a Table.

  • At first, select any cell from the dataset.
  • Here, I selected cell E14 randomly.
  • Then, select Table from the Insert tab.

creating a table from the dataset

  • After that, a dialog box of Create Table will appear.
  • At this point, click OK.

Create Table dialog box

  • Therefore, the table is now created.

Created table from dataset

Read More: How to Create a Scatter Plot in Excel with 3 Variables (with Easy Steps)


Step-2: Inserting PivotTable to Create Dynamic Scatter Plot

Next, I will create a PivotTable from the table.

  • Similar to the previous step, select any cell from the dataset. Again, I selected cell E14 randomly.
  • Then, select PivotTable from the Insert.

creating pivot table from the table

  • At this point, a PivotTable from table or range dialog box will appear.
  • Now, select Existing Worksheet.
  • After that, set the location to any blank cell. Here, I selected cell G4.
  • Finally, click OK.

PivotTable from table or range dialog box

  • At this moment, a side window of PivotTable Fields appeared to fill up the necessary information about the Pivot Table.
  • Now, drag the fields from the top section to the areas at the bottom section as following.
  • Drag Full Name in the Rows area, Business Unit in the Filters area, and both Years of Experience and Annual Salary to the Values area.

filling up PivotTable Fields

  • With that, the PivotTable is created.

pivot table creation

Read More: How to Make a Scatter Plot in Excel with Multiple Data Sets


Step-3: Creating  Dynamic Scatter Plot

In this step, I will create a dynamic Scatter plot using the Pivot Table information.

  • In the first place, randomly select a cell.
  • Here, I selected cell K10.
  • After that, go to the Insert tab >> select the Insert Scatter or Bubble Chart group.
  • Furthermore, select Scatter Chart.

creating dynamic scatter plot

  • At this point, a blank Scatter chart or plot space will appear.

blank scatter plot

  • After that, click on the plot box >> go to the Chart Design tab.
  • Then, from the Data group >> choose Select Data.

selecting data for the scatter plot

  • Immediately, a Select Data Source dialog box will appear.
  • Then, to fill up the Select Data Source dialog box click on Add.

filling up the Select Data Source dialog box

  • Right after, an Edit Series dialog box will appear.
  • After that, under the Series name option, select cell H4.
  • Afterward, inside the Series X values, select values from the Sum of Years of Experience.
  • Then, select values from the Sum of Annual Salary, inside the Series Y values.
  • In addition, click OK.

filling up the Edit Series dialog box for creating the dynamic scatter box

  • Then, click OK in the Select Data Source dialog box.

added the data in the Select Data Source dialog box

  • Therefore, the blank scatter plot is visible with dots, which are the data we inserted.

the dynamic scatter plot

  • Furthermore, edit the title by clicking on it and typing Years of Experience vs. Annual Salary.
  • Finally, the dynamic scatter plot is created.

editing the title for the dynamic scatter plot

Read More: How to Make a Scatter Plot in Excel with Two Sets of Data (in Easy Steps)


Similar Readings


Step-4: Employing of Filter and Clearing off Grand Totals, Subtotals

In this step, we will create a filter for the Business Unit to view the individual impact of the business units.

  • First of all, right-click on the Business Unit from the side window of PivotTable Fields.
  • Then, select Add as Slicer.

adding a slicer to the pivot table

  • At this point, a Business Unit slicer will appear.

The Business Unit filter

You may need to clear off the grand totals and subtotals from your dynamic scatter plot if you do not want them in your dynamic scatter plot. To do so, follow the below steps,

  • Select any of the cells from the Pivot Table.
  • Here, I selected cell H7.
  • After that, go to the Design tab >> from the Layout group >> select Grand Total.
  • Then, select Off for Rows and Columns.

clearing off the grand totals from the rows and columns of the pivot table

  • Also, go to the Subtotals >> select Do Not Show Subtotals.

clearing off the subtotals from the pivot table

  • Therefore, you can see the Dynamic Scatter Plot.

The Final Look of Dynamic Scatter Plot

  • The following GIF shows how the dynamic scatter plot works with Business Unit filtering.

dynamic scatter plot excel

Read More: How to Create a Scatter Plot in Excel with 2 Variables


2. Using Table to Create Dynamic Scatter Plot

You can simply use a table to create the dynamic scatter plot. Follow the below steps.

Steps:

  • Like previously, create a table from the sample dataset or press CTRL+T after selecting a random cell from the table.
  • Then, select cells D5:E14 from the columns Years of Experience and Annual Salary.
  • After that, go to the Insert tab >> select the Insert Scatter or Bubble Chart group >> choose the Scatter graph.

Creating dynamic scatter chart from the table

  • Therefore, the dynamic scatter plot will appear.
  • Moreover, edit the title of the dynamic scatter plot to Years of Experience vs. Annual Salary.

Dynamic Scatter Plot from the table

Read More: How to Create a Scatter Plot with 4 variables in Excel (with Quick Steps)


3. Implementing Named Range to Create Dynamic Scatter Plot

In this method, I will use the Name Range to create dynamic scatter plot.

Steps:

  • First, to make the range of this scatter plot dynamic, I will provide a name for the range.
  • To do so, go to the Formulas tab >> click on Define Name.

Creating data range by selecting Define Name

  • Immediately, the New Name dialog box will pop up.
  • Afterward, fill up the Name with the preferred range name.
  • Then, select the worksheet from Scope.
  • Finally, type the following formula in Refers to box to set the range:
=OFFSET(NamedRange!$D$5,0,0,COUNTA(NamedRange!$D:$D)-1,1)

Filling up the New Name dialog box

Formula Breakdown

  • COUNTA(NamedRange!$D:$D)-1The COUNTA function will count all the data from column D. This will only count non-empty cells and -1 is to indicate we have column headers in our dataset.
    • Output 10.
  • OFFSET(NamedRange!$D$5,0,0,COUNTA(NamedRange!$D:$D)-1,1) → becomes
    • OFFSET(NamedRange!$D$5,0,0,10,1)The OFFSET function will extract all the data from the Years of Experience column starting from cell D5.
      • Output D5:D14.

  • Click OK.
  • Again, click on the defined name to create another named range.
  • And fill up the New Name like the below image and type the following formula in the Refers to box:
=OFFSET(NamedRange!$E$5,0,0,COUNTA(NamedRange!$E:$E)-1,1)

creating another named range by filling up another New Name dialog box

Note: Here, column E is selected in this formula. Also, the NamedRange is the name of the worksheet name.
  • Now, randomly select a cell.
  • Here, I selected cell G4.
  • After that, go to the Insert tab >> select the Insert Scatter or Bubble Chart group.
  • Furthermore, select Scatter chart.

inserting the scatter plot

  • At this point, a blank Scatter chart or plot space will appear.

Blank scatter plot

  • After that, click on the plot box >> go to the Chart Design tab.
  • Then, from the Data group >> choose Select Data.

Selecting data for the scatter plot

  • Immediately, a Select Data Source dialog box will appear.
  • Then, to fill up the Select Data Source dialog box click on Add.

filling up the Select Data Source dialog box

  • Right after, an Edit Series dialog box will appear.
  • After that, under the Series name option, type Years of Experience vs. Annual Salary.
  • Afterward, inside the Series X values, to select the Years of Experience data range, type:
=NamedRange!Experience_Range
  • Then, inside the Series Y values, to select the Years of Experience data range, type:
=NamedRange!Salary_Range
  • In addition, click OK.

filling up the Edit Series dialog box

  • Again, the Select Data Source dialog box will show up.
  • Click OK.

filled up the data source in the Select Data Source dialog box

  • Now, the dynamic scatter plot in Excel is created.

the dynamic scatter plot

Read More: How to Create a Scatter Chart in Excel (with Easy Steps)


Practice Section

On the Practice Section worksheet, you will find a dataset to practice on your own.

Practice Section


Conclusion

In this article, you learned how to create dynamic scatter plot in excel. Follow our ExcelDemy page for regular blogs related to Excel. You can suggest your thoughts about this article in the comment section below.


Related Articles

Rabeya

Rabeya

Hello! I am Rabeya Islam. I have completed my B.Sc. in Computer Science and Engineering from East West University. Currently, I am working as an Excel and VBA Content Developer, and I research MS Excel casually. I have an interest in Research and Development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo