3 Ways to Create Dual Graphs in One Excel Sheet

Using Excel's In-built Chart Tools

Excel has been an essential tool for data visualization and analysis for decades. Its intuitive interface makes it a favorite among professionals from diverse fields. One common need when analyzing data is to compare two sets of data. Here's how you can create dual graphs on one Excel sheet using Excel's built-built chart tools:

• Select Your Data: Begin by selecting the data ranges for both graphs. Ensure they are either in two separate columns or rows.
• Insert a Chart: Click on Insert in the Excel ribbon, then select the chart type you wish to use for your first dataset. Let's say we choose a Line chart.
• Add a Secondary Axis: After the chart is created, you'll need to add a secondary axis for your second dataset:
  • Right-click on any part of the data series for the second dataset on your chart.
  • Choose Change Series Chart Type from the drop-down menu.
  • In the dialog box, under the Series Options tab, change the chart type for this series to another chart type if desired.
  • Check Secondary Axis option to enable the secondary vertical axis.
• Customize: Now you can format your dual graphs:
  • Right-click on any of the axes to format them or change their scale.
  • Adjust colors, legends, and data labels to differentiate the two graphs effectively.

📘 Note: Ensure your data series have similar value ranges; otherwise, it might be challenging to compare the graphs effectively without skewing the visual representation.

Creating Combo Charts

If you want to compare different types of data or visualize relationships where one axis can't effectively represent both datasets, a combo chart can be useful:

• Data Preparation: Make sure both sets of data are aligned (in terms of dates, categories, etc.).
• Select Data: As before, select your dual datasets. Ensure they are in a layout that Excel can recognize for plotting different series.
• Insert Combo Chart:
  • Go to Insert, click on the Combo chart icon.
  • Choose a combo chart type from the menu, such as Clustered Column - Line or Clustered Bar - Line.
• Change Series Chart Type:
  • In the Combo Chart dialog, select the series you want to display differently, and change its chart type.
  • Assign the series to primary or secondary axes.
• Format and Customize: Once again, you'll need to customize your chart:
  • Use Chart Tools to adjust the scale of each axis independently.
  • Ensure labels are clear, and data points are easily distinguishable.

Using VBA for Dual Graphs

For those seeking more control or are working with complex datasets, using Visual Basic for Applications (VBA) might be the best approach:

• Open VBA Editor: Use Alt + F11 to open the VBA editor.
• Insert Module: Insert a new module from the Insert menu.
• Write the VBA Code: Here’s a simple example of how you might write the code:

  
  Sub CreateDualGraphs()
      Dim Chrt As Chart
      Dim DataSheet As Worksheet
      Set DataSheet = ThisWorkbook.Sheets("Sheet1") 'Ensure your data is on this sheet
  
      'Create the first chart
      Set Chrt = DataSheet.Shapes.AddChart.Chart
      Chrt.SetSourceData Source:=DataSheet.Range("A1:B6")
      Chrt.ChartType = xlLine
  
      'Create the second chart on the same sheet
      Set Chrt = DataSheet.Shapes.AddChart.Chart
      Chrt.Top = DataSheet.ChartObjects(1).Top + 200
      Chrt.SetSourceData Source:=DataSheet.Range("D1:E6")
      Chrt.ChartType = xlColumnClustered
  
      'Add dual axes if needed
      Chrt.SeriesCollection(1).AxisGroup = 2
  
      'Make further customizations here
  End Sub
  
  

This VBA script will create two separate charts. However, you might need to adjust the positions, data ranges, and customize them further to meet your needs.

To create dual graphs effectively in one Excel sheet, you've explored three robust methods: Excel's in-built tools, combo charts, and VBA. Each has its merits depending on the complexity of your data and your comfort level with Excel:

• The in-built chart tools are straightforward for basic dual graph creation.
• Combo charts offer a more versatile visual comparison for different data types.
• VBA provides extensive control for customizing charts, especially useful for advanced users or those dealing with extensive datasets.

💡 Note: Remember to save your Excel workbook as a macro-enabled workbook (.xlsm) if you decide to use VBA macros.