The horizontal axis of a line chart only shows evenly spaced groupings (categories) of data. This is because a line chart only has one value axis (the vertical axis). In a line chart, however, the same daily rainfall and particulate values are displayed as two separate data points, which are evenly distributed along the horizontal axis. These numbers represent the values in cell A9 and B9 on the worksheet. The first data point to appear in the scatter chart represents both a y value of 137 (particulate) and an x value of 1.9 (daily rainfall). These data points may be distributed evenly or unevenly across the horizontal axis, depending on the data. The chart displays points at the intersection of an x and y numerical value, combining these values into single data points. Often referred to as an xy chart, a scatter chart never displays categories on the horizontal axis.Ī scatter chart always has two value axes to show one set of numerical data along a horizontal (value) axis and another set of numerical values along a vertical (value) axis. In a scatter chart, the daily rainfall values from column A are displayed as x values on the horizontal (x) axis, and the particulate values from column B are displayed as values on the vertical (y) axis. For example, when you use the following worksheet data to create a scatter chart and a line chart, you can see that the data is distributed differently.
Try it yourself.The main difference between scatter and line charts is the way they plot data on the horizontal axis. In this case, simply put it on secondary axis, and ensure that setting are same for primary and secondary axis Note: If you have a lot of data points, your highlighter may get hidden behind the data points. Now you can select a company and it would circle and highlight the company. Marker Line Style –> Width (Make it 1 or higher).Marker Line Color –> Solid Line (Red or whatever color you want).Select Marker Option –> Built-in –> Type (select circular shape and increase the size to 11).Select that data point, right click and select Format Data Series. There would be a data point in a color and shape different from the other data points.In the Select Data Source dialogue box, Click Add.Select the already created scatter chart.In cell D25, use the VLOOKUP formula to extract the Profit Margin (Y axis) value for the company in cell B25:.In cell C25, use the VLOOKUP formula to extract the Revenue (X axis) value for the company in cell B25:.Cell B25 would change whenever I change the drop down selection.For example, in this case, the drop down is in F3, and in B25 I have the formula =F3. Select a cell and refer it to the cell with the drop down.
In the Data Validation dialogue box, select List (as validation criteria) and select the entire range that has names of the companies (in this case, the list is in B3:B22), and click OK.Ģ.2 – Extracting the Values for the Selected Company.Go to a cell where you want to create the drop down.This has 3 parts to it: 2.1 – Create a Drop Down List with Company (data point) Names Now comes the interesting part – creating the marker to spot your selected company. This will create a simple scatter chart for you.
SCATTER CHART EXCEL DEFINITION SERIES
In the Edit Series dialogue box, select the range for X axis and Y axis.In the Select Data Source dialogue box, click on Add.Click on the empty chart and go to Design –> Select Data.Go to Insert –> Charts –> Scatter Chart.Other Excel tutorials you may also like:.2.2 – Extracting the Values for the Selected Company.2.1 – Create a Drop Down List with Company (data point) Names.