Displaying Multiple Series in One Excel Chart Displaying Multiple Series in an XY Scatter Chart Single Block of DataMicrosoft Excels charting tools create evocative data visualizations from rows or columns of numbers. I’ll show how to add series to XY scatter charts first, then how to add data to line and other chart types the process is similar but the effects are different. I’m going to show a couple ways to handle this. 3D Plot in Excel is used to plot the graph for those data sets, which may not give much visibility, comparison feasibility with.A common question in online forums is “How can I show multiple series in one Excel chart?” It’s really not too hard to do, but for someone unfamiliar with charts in Excel, it isn’t totally obvious. Step-2: Select data for the chart: Step-3: Click on the ‘Insert’ tab: Step-4: Click on the ‘Recommended Charts’ button:Get Started with Activities. In order to add a chart in Excel spreadsheet, follow the steps below: Step-1: Open MS Excel and navigate to the spreadsheet which contains the data table you want to use for creating a chart.But I’ll cover it just for completeness.If I have a single block of data, I can select the block of data, or just a single cell within it, and Excel will build a chart using all of the data. To doublecheck the underlying value, select the cell.This is a trivial case, and probably not what people are asking about. The Excel Consolidate function allows an analyst to combine multiple Excel files / workbooks into one.We’ll talk about this setting when we discuss Line charts. You will be greeted with the Paste Special dialog.Make sure that the settings in the dialog are correct: Values (Y) in rows or columns, series names in first row, categories (X labels) in first column.The Replace Existing Categories setting would replace existing X values with those being pasted, which makes little sense for an XY chart that already has X values defined. And like the expansion of data within a single range that I started this article with, there’s a faster and easier way to add data to a chart from different ranges.Copy – Paste Special: Select and copy the data you want to add to the chart, then select the chart, and from the Home tab of the ribbon, click the Paste dropdown, and select Paste Special. It just seems like too much work.
The first row is used for series names.When there are multiple blocks of data, Line charts still work mostly the same as XY Scatter charts. The first column (if the series data is plotted in columns) is used as X values, or more accurately, X labels the rest of the columns are used as Y values. Single Block of DataWhen your data is in a single block, a Line chart works just like the XY scatter chart. Displaying Multiple Series in a Line (Column/Area/Bar) ChartI’m using Line charts here, but the behavior of the X axis is the same in Column and Area charts, and in Bar charts, but you have to remember that the Bar chart’s X axis is the vertical axis, and it starts at the bottom and extends upwards. It’s even easier to use Paste instead of Paste Special, but sometimes Excel guesses incorrectly on those row/column, first row, first column settings, and you’ll have to undo the Paste and do Paste Special. To change the X labels, click the Edit button above the list of X labels in the chart. The original X labels remain on the chart.This dialog differs from the one seen when adding data to an XY Scatter chart, because there is no place for X values (or X labels). There are spaces for series name and Y values.Fill in entries for series name and Y values, and the chart shows two series. What is important is that the data can be formatted the same (markers or no markers, lines or no lines), while the X values are treated differently (numerical values in XY Scatter charts, non-numeric labels in Line charts).Copy – Paste Special: As in XY Scatter charts, adding data to Line charts can be faster and easier with Copy and Paste Special than with the Select Data dialog.Check the settings in the dialo: Values (Y) in rows or columns, series names in first row, categories (X labels) in first column. Line charts and their ilk treat X values as non-numeric labels, and all series in the chart use the same X labels.Change the range in the Axis Labels dialog, and all series in the chart now use the new X labels.The differences between Line and XY Scatter charts can be confusing. XY Scatter charts treat X values as numerical values, and each series can have its own independent X values. Select Data For Charts In Excel Update The ChartIn addition, both series formulas include the new X label range.This usually isn’t what I want, so I almost never check Replace Existing Categories for any chart type.The behavior even becomes stranger when we use mismatched data ranges. If we had selected only the new Y values, ignoring any new X values, and kept Categories in First Column unchecked, both series formulas would reference the same X label range.Here is what happens when we check Replace Existing Categories.When we click OK to update the chart, the new X labels appear along the axis. =SERIES(Sheet4!$C$2,Sheet4!$B$3:$B$8,Sheet4!$C$3:$C$8,1) =SERIES(Sheet4!$F$2,Sheet4!$E$3:$E$8,Sheet4!$F$3:$F$8,2)The X labels specified in the first series formula is what Excel uses for the chart. Click OK to update the chart.Although both series are plotted against the original X labels, if we examine the series formulas, we see that the original series formula contains the original X labels range ($B$3:$B$8), while the new series formula references the new range ($E$3:$E$8). The first series is again pushed to the left of the chart, since it has many fewer points than the second series.You can assign one series to the primary axis and the other to the secondary axis, and each axis will be long enough for its labels. Both series use the new X labels, which fill the entire length of the axis, and they don’t overlap excessively since I wisely used one-character labels. The first series is pushed to the left of the chart along with the axis labels, since it only uses a fraction of the X axis labels.Here is the same chart if we paste special with Replace Existing Categories checked. Since the first labels are being used, these fill the first part of the axis, overlapping excessively, while the rest of the axis remains unlabeled. Both series use the same X labels, so the axis has enough spaces for the longest series. Mac iso image for windowc10When I switched the series to the secondary axis, it used the new X labels from the series formula. This kept the new X label range in the series formula even though the series was initially plotted against the original labels.
0 Comments
Leave a Reply. |
AuthorJason ArchivesCategories |