How to Create a Vector Chart in Excel. 3 Steps to Follow

For some time now, we have been doing some good research How to create a vector chart in ExcelWe think it's an interesting challenge and could be useful for many users. With Excel tools, you can create black and white or color vector graphics. The process is a bit tedious, but we'll explain it step by step so you can do it by following the tutorial.

You must take into account that this is commands, so you must work with total precision. If you make a mistake in just one element, the procedure will not be carried out correctly and you will not be able to obtain the desired graph. Without further ado, continue reading to begin creating your graph.

Maybe you may be interested: Plotting an Equation on an Excel Chart

How to create a vector chart in Excel

There are several procedures involved when creating a vector chart in Excel, below we will explain everything in detail:

1. Create the vector data if you don't have it

In our case, we didn't have vector data at hand, so we had to make some up. For this exercise our vector field will be defined by the following:

The first step is to define the grid for the vector field. We chose an evenly spaced grid with dimensions of 0,3 x 0,3. We also decided, for simplicity, to place the tails of each vector, rather than the midpoint, on the grid. So the first step was to create two columns for the “x1” and “y1” values, or the position of the tails of each vector.

In the columns immediately next to “x1” and “y1”, we create the columns “x2” and “y2”. These points will be the end of the head or arrow of the vectors. The head of the vector must be located at the position x1+fx and y1+fyBut this absolute position makes the vectors very large relative to the grid spacing.

Typically, in a vector graph, what we care about is the relative size of the vectors compared to each other. To address this, we add a scale factor in the calculation of “x2” and “y2”. A value of 0,1 worked well in this case. The formula for x2 is shown below.

If we were trying to make a vector graph from existing data, we could add another column to multiply the data by a scale factor. Now that we have four columns of values ​​that completely define the vectors, we can create a vector graph in Excel.

2. Creating a Vector Chart in Excel

Vector chart is made from scatter chart type in Excel. So, we start by inserting a blank one in the worksheet.

Each vector will be represented by a data series. To start completing the graph, we right-click on it and choose «Select data» from the menu. Next, we add the new series by selecting the first row of “x1” and “x2” values ​​as “X values ​​as standard» and the first row of “y1” and “y2” values ​​as “Y series values«.

Next, we add an arrowhead to the vector by changing its format. First we select the data series, then we navigate to the Data tab. Chart Tools > Format to add the arrowhead.

Now we have something that looks like a vector in our graph. We also made the plot area square so that it doesn't skew the vectors.

All that's left is to repeat this process for each vector. Easy, right? We have better things to do than clicking buttons all day to add all the individual vectors to this graph. We guess you do too! So let's do it a little smarter, shall we?

3. Using VBA to Add Data Series to an Excel Scatter Chart

Instead of adding each vector manually by clicking buttons in Excel, we can create a subroutine to add each series. The VBA for the subroutine is below:

This is what the subroutine does:

1. Activate the chart (ours was called “Chart7”) and select the plot area.
2. Start a cycle «The» to loop through each of the 49 rows of values.
3. Defines the values ​​of x as the cells of the current row and columns C and D as a string that can be entered into the scatter plot.
4. Defines the values ​​and cells in the current row and columns E and F.
5. Add a new series to the chart using the x values ​​and y values
6. Then format the newly added series.
7. Add the arrowhead.
8. Finally, move on to the next row, until you complete the 49.

The result, after removing the axis labels and adjusting the limits, is this:

It looks like a vector plot, doesn't it? Each row of “x” and “y” values ​​in the table is represented as a vector, with the tail of each vector aligned to the predefined grid. For make a vector graphic so that the midpoint of the vector is on the grid, we undoubtedly need to define a third point for each vector.

WHATS NEXT?

We want to enhance the vector graphic by adding a color scale so that the color of the vectors changes with their magnitude. Basically, smaller vectors will be blue and larger ones will be red. This will involve linear interpolation with RGB values, as shown below:

NOTE:

This method will only work for vector charts where the number of vectors is less than 255 due to Excel's chart series limitation. Only 255 data series are allowed per chart.

You may want to know: 6 Best Programs to Solve Equations

How to Create a Colored Vector Chart in Excel

Previously we showed you how to use VBA to create a vector chart in Excel. That plot was just a simple black and white vector plot. So in this section, we will show you how to create a vector chart in Excel. Take that chart to the next level with a color scale to indicate the magnitude of vectors, such as the following:

The basic concept for creating the vector graphic in Excel that has various colors is this:

• Step 1: defines a gradient to be used to color vectors.
• Step 2: determines the magnitude of each of the vectors.
• Step 3: find the minimum and maximum magnitudes.
• Step 4: calculates the relative percentage magnitude of each vector (minimum = 0%, maximum = 100%)
• Step 5: iInterpolate the gradient to find the percentage of red, green, and blue for the vector.
• Step 6: plot the vector on the graph.
• Step 7: applies formatting to the vector, including color.

As you probably guessed, all of these steps (except 1) were done in VBA. We'll go through each one below.

Definition of gradient

Excel makes it easy to play around with gradients and find the one you like. We just created a rectangle on the worksheet, then filled it with a custom gradient with stops at 0% (red), 33% (yellow), 66% (green), and 100% (blue).

We then turned that into a table of RGB values ​​vs. percentage. We wanted 0% (or smaller values) to be blue and 100% (or larger values) to be red. RGB values ​​are integers between 0 and 255.

Later, we interpolate in VBA based on the relative magnitude of the vector (from 0 to 100%), so we also name each column (“legendx”, “legendr”, “legendg”, “legendb”) to make interpolation easier. With the gradient defined, let’s jump into VBA:

Calculation of vector magnitudes, minimum, maximum in vba

After defining the total number of vectors (numvect) and initializing the variables, “minvmag” and “maxvmag”, the following code loops through the rows of data to calculate the magnitude of each vector, “vmag”. It also stores each of the magnitudes in an array. This array will be used later when we color the vector with the appropriate shade based on the gradient we defined. Finally, the current value of vmag is compared with maxvmag and minvmag. If it is greater than maxvmag, then the value of maxvmag is updated. If it is less than minvmag, then the value of minvmag is updated.

numvect = 196

minvmag = 1000000

maxvmag = 0

ReDim vmagarray(1 To numvect)

For j = 1 To numvect

x1 = Cells(4 + j, 2)

x2 = Cells(4 + j, 3)

y1 = Cells(4 + j, 5)

y2 = Cells(4 + j, 6)

vmag = Sqr((x1 – x2) ^ 2 + (y1 – y2) ^ 2)

vmagarray(j) = vmag

If vmag > maxvmag Then

maxvmag = vmag

ElseIf vmag < minvmag Then

minvmag = vmag

End If

Next j

Calculate the relative percentage magnitude of each vector

With the magnitudes of each vector, as well as the minimum and maximum defined, you could move on to trace each vector with a loop The (For i = 1 to numvect). Inside the loop, we had to normalize the vectors between 0 and 1, in order to apply the gradient defined above. The following equation takes care of that:

relvmag = (vmagarray(i) – minvmag) / (maxvmag – minvmag)

Interpolating to obtain the color of each vector

The relative magnitude of the vector could be any value between 0 and 1, so we needed to interpolate from the table above to find the correct color to apply to the vector. We used a function interpolation simple linear (LinInterp) that we created earlier to handle this. And since RGB values ​​are integers, we had to round the result of the interpolation to get the final value.

red = Round(LinInterp(relvmag, Range(«legendx»), Range(«legendr»)))

grn = Round(LinInterp(relvmag, Range(«legendx»), Range(«legendg»)))

blu = Round(LinInterp(relvmag, Range(«legendx»), Range(«legendb»)))

Trace the vector

The VBA to plot the vector as a series and format it as an arrow is the same as before:

xvalues ​​= «=Sheet1!$B$» & i + 4 & «:$C$» & i + 4

yvalues ​​= «=Sheet1!$E$» & i + 4 & «:$F$» & i + 4

'add the series to the chart

ActiveChart.SeriesCollection.NewSeries

ActiveChart.FullSeriesCollection(i).xvalues ​​= xvalues

ActiveChart.FullSeriesCollection(i).Values ​​= yvalues

With Selection.Format.Line

.EndArrowheadLength = msoArrowheadLengthMedium

.EndArrowheadWidth = msoArrowheadWidthMedium

.EndArrowheadStyle = msoArrowheadTriangle

Apply a color to the vector

Finally, we use the interpolated RGB values ​​(“red”, “grn”, “blu” variables) in the With loop to color the vectors:

.ForeColor.RGB = RGB(red, grn, blue)

The complete vba subroutine to create a colored vector graphic

Below is the complete subroutine to create the colored vector graph in Excel. You must follow it exactly (without overlooking any detail), to avoid errors in the process:

Sub add_vector()

Dim xvalues ​​As String

Dim yvalues ​​As String

Dim numvect As Integer

Dim vmagarray() As Double

Dim minvmag As Double

Dim maxvmag As Double

Dim red As Integer

Dim grn As Integer

Dim blu As Integer

numvect = 196

'find the minimum and maximum vector magnitudes

'(these need to be defined before plotting)

'store the vector magnitudes in an array for later use

minvmag = 1000000

maxvmag = 0

ReDim vmagarray(1 To numvect)

For j = 1 To numvect

x1 = Cells(4 + j, 2)

x2 = Cells(4 + j, 3)

y1 = Cells(4 + j, 5)

y2 = Cells(4 + j, 6)

vmag = Sqr((x1 – x2) ^ 2 + (y1 – y2) ^ 2)

vmagarray(j) = vmag

If vmag > maxvmag Then

maxvmag = vmag

ElseIf vmag < minvmag Then

minvmag = vmag

End If

Next j

'activate the chart and select the plot area

ActiveSheet.ChartObjects("Chart 7").Activate

ActiveChart.PlotArea.Select

For i = 1 To numvect

'determine the relative percent magnitude of the vector

'(wrt the minimum and maximum vectors)

relvmag = (vmagarray(i) – minvmag) / (maxvmag – minvmag)

'interpolate to find the % of red, green, and blue

red = Round(LinInterp(relvmag, Range(«legendx»), Range(«legendr»)))

grn = Round(LinInterp(relvmag, Range(«legendx»), Range(«legendg»)))

blu = Round(LinInterp(relvmag, Range(«legendx»), Range(«legendb»)))

'define the xvalues ​​and yvalues ​​for the chart

xvalues ​​= «=Sheet1!$B$» & i + 4 & «:$C$» & i + 4

yvalues ​​= «=Sheet1!$E$» & i + 4 & «:$F$» & i + 4

'add the series to the chart

ActiveChart.SeriesCollection.NewSeries

ActiveChart.FullSeriesCollection(i).xvalues ​​= xvalues

ActiveChart.FullSeriesCollection(i).Values ​​= yvalues

ActiveChart.FullSeriesCollection(i).Select

'apply format to the series

With Selection.Format.Line

.EndArrowheadLength = msoArrowheadLengthMedium

.EndArrowheadWidth = msoArrowheadWidthMedium

.EndArrowheadStyle = msoArrowheadTriangle

.ForeColor.RGB = RGB(red, grn, blue)

End With

ActiveChart.ChartType = xlXYScatterLinesNoMarkers

Next i

End Sub

Take a look at: The 7 Best Graphing Programs.

Final thoughts

Creating a vector chart in Excel is a bit complex, whether it's black and white or color, since there are many factors involved, starting with the calculation. However, it's always fun to see how we can push the limits of Excel to do something new. We hope you enjoyed the tutorial and were able to apply it to the work you had in mind. You can always express your opinions through the comments section. We'll always be here to continue to dispel your concerns regarding Excel and other indispensable tools, so visit us soon.