How can I connect two data poins on an Excel xy scatter if there is a missing point? For instance, I have an XY scatter which most of the points are connected. You can do that with the chart already created and when you are done the chart should a line connecting all desired data points.
![]()
Excel Chart Connect Missing Data Microsoft excel does not automatically connect data points in scatter or line charts when there is missing data or empty cells (see excel example below). Excel can manage missing data or bank cells when creating scatter or line charts in three different ways:. The blank cell is given a value of zero.
![]()
A connecting line is draw between the available data points which spans missing cell entries. There is no connecting line between the data points and the point can appear as a single entry. By default excel performs the third option and does not connect the scatter plot data points. In most case the second option, with the connecting line, is the most useful.
This guide will demonstrate how to change a line or scatter chart with missing data lines due to non data or blank cells into a connected line or scatter chart. The example below shows two batches with sporadic weekly measurements. When the scatter plot (or line plot) is created based on the data there are large gaps in the data lines. This guide will show you how to connect these data points. Right click over the chart or graph. This brings up the chart options. Click on the “select data” option.
On the Select Data Source window click on the “hidden or empty cells” button located on the bottom left of the window. Click the “connect data points with a line” radar button. The press OK. The lines between the data points will be updated and connected. This option now applies to the chart so any additional data or data removed will be automatically updated.
There is no preference option to ensure all data points are connected automatically by excel when a scatter or line chart is created. So this short process of connecting the curved line between data points in excels must be performed every time a line or scatter chart is made. Related Pages - Excel -Photoshop -Word.
A visitor to my web site asks, “How can I connect corresponding points of two different XY series in a chart?” In other words, how can this chart be converted into into this chart This is pretty easy by adding a third XY series that includes all of the data for the original two series. The data has to be properly sequenced, with a point from series A, then a point from series B, then a blank row, then the next point from series A, etc. While working on this data arrangement, I realized it was what I needed to enhance a step chart to omit the “risers” and draw only the horizontal segments. I guess there are more reused solutions than new solutions.
To arrange the data, start with the sequence below. The left hand block shows the original data. The middle block shows the original data copied and pasted into a new range. A column has been inserted to the left of the data, showing the index of each point (1, 2, 3, etc.). Notice that a set of blank rows below the data has also been given index numbers. If there are N points, only N-1 blank rows are needed. In the third block, the data has been sorted by these indexes, leading to the alternating A-B-blank sequence.
A 2 9 3 8 4 7 5 6 6 5 7 4 8 3 9 2 B 2.74 10.59 3.83 9.35 5.28 7.82 5.81 7.59 7.60 5.73 8.29 4.02 9.82 3.16 10.62 2.62 A-B 1 2 9 2 3 8 3 4 7 4 5 6 5 6 5 6 7 4 7 8 3 8 9 2 1 2.74 10.59 2 3.83 9.35 3 5.28 7.82 4 5.81 7.59 5 7.60 5.73 6 8.29 4.02 7 9.82 3.16 8 10.62 2.62 1 2 3 4 5 6 7 A-B 1 2 9 1 2.74 10.59 1 2 3 8 2 3.83 9.35 2 3 4 7 3 5.28 7.82 3 4 5 6 4 5.81 7.59 4 5 6 5 5 7.60 5.73 5 6 7 4 6 8.29 4.02 6 7 8 3 7 9.82 3.16 7 8 9 2 8 10.62 2.62 To create the chart, select the data for series A, and create an XY chart, using the markers without lines option. Copy the data for series B, select the chart, and use Paste Special to add the data as a new series. Finally copy the sequenced A-B data from the right hand block above, select the chart, and use Paste Special to add the data as a new series. Change this XY series type to the lines without markers option, or simply format the series to have lines but no markers. Peltier, I am one of your fans who has learned many and many from your great web site. I have read Excel 2007 Chart by John Walkenbach where i saw you name first time.
Since that time, I have 3 meals daily in addition to my Excel Charting Meal in Jon Peltier Resturant. I implement your tricks and tips in my daily task as a Planning and PMCE – Project Monitoring and Contorlling Engineer.
Thanks Thanks and Thanks a lot Mr. Peltier and I am waiting for an Excel Charting Book from you. Regards, Eyad Husseini PMCE. Jay says. Jay – In versions of Excel up to 2003, this can be achieved only through the addition of autoshapes, that is, lines with arrowheads drawn on the chart. These are tricky to align with the points, and if any little thing happens in the chart, the lines will have to be realigned.
I’ve also seen techniques where several extra points are added to each section of the series that connects each pair of points, such that these extra points define the shape of an arrowhead. This is tricky to define the points, given unequal X and Y scales, such that the arrow heads are nicely proportions, and if anything in the chart changes, these extra points have to be redefined. I’ve criticized the eye-candy formatting added to Excel 2007 charts to the detriment of chart functionality, but some of the added formatting is actually useful. You can format the lines in a chart series as if they are autoshapes, and by adding arrowheads to the lines, you can achieve the effect you have requested.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |