Data preparation

Create two columns with data, as presented below. Next, select values in column C and choose conditional formatting in Home ribbon (see screenshot). This way you can assign colors to values. You can choose More Rules… to customize the colors.

Scatter plot to heat map

First, create ordinary scatter plot using columns A and B (with values X and Y). Next, use the macro posted here by Reddit user carry_a_laser:

Sub ColorCode()
Dim i As Integer
Dim MyCount As Integer

MyCount = ActiveChart.SeriesCollection(1).Points.Count * ActiveChart.SeriesCollection.Count

For i = 1 To MyCount

ActiveChart.SeriesCollection(1).Points(i).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = Range("C" & i + 1).DisplayFormat.Interior.Color
        

    End With
    
Next

End Sub

Put this macro in your sheet, select your chart and run the macro. All the points will get colors based on conditional formatting. It is slow in Excel 2016 (for 3000 points it takes around 2 minutes), just be patient. See the result below:

Pivot tables

Select your whole data (three columns). Choose Insert -> Pivot Table and hit OK. Next, drag X to rows, Y to columns, Value to Values. You should get ordinary pivot table. Now, select values inside it and apply color formatting again. See below:

First, create any spreadsheet and save it somewhere. I will use the following:

Choose Data ribbon and click From Other Sources button. Choose From Microsoft Query:

After clicking the button you will be able to choose data source. In fact, Microsoft Query is an external application (distributed with Office). Choose Excel Files:

Now select your file stored on the hard drive:

Now you should be able to add tables to your workspace. Each sheet in spreadsheet represents separate table. Add table and hit Close.

Next, click SQL button placed just below the menu bar and insert any query you would like to execute:

That’s all.