By Chad Davis, Contributor at ExcelAssignment company.
To create a spreadsheet table that can be plotted in the form of a graph, first calculate your X or Y values. In Microsoft Excel, you can generate results for these variables by applying a formula to a known data series. Once you’ve completed your table, you can use your X and Y data to automatically create a scatter chart with straight lines and markers. Use the Chart tools to label each axis on your chart, and then label the data callouts for each point on the X and Y axes.
- Create a new blank workbook in Excel.
- Enter your formula into cell A1 for reference. For example, enter “y=5x-2” (without quotes here and throughout).
- Enter labels for your X and Y column data into cells A2 and B2. For example, label the cells “X” and “Y”.
- Enter your X-values into the first column, starting in cell A3. For example, enter the following values into cells A3 through A7: “-2, -1, 1, 2, 4”.
- Select cell “B3,” and then calculate its Y-value using your formula. For example, enter “=5*A2-2”, and then press “Enter” to compute the value for Y.
- Select cell “B3” again, and then drag the fill marker, resembling a small box in the bottom right corner of the cell, to the bottom of your data series. For example, drag the fill marker from B3 to B7. Your Y-values are computed based on your formula and corresponding X-values in the table.
- Select your formula in cell A1. Click on the “Insert” tab to switch to the Insert ribbon. Click the “Insert Scatter (X,Y) or Bubble Chart” drop-down button, resembling a scatter chart icon, and then click “Scatter with Straight Lines and Markers” to plot your formula in a chart.
- Click the “Add Chart Element” drop-down button in the Chart layouts group of the Design ribbon, select “Axis Titles,” and then click “Primary Horizontal” to add a horizontal axis title to your chart. Repeat the process, but this time select “Primary Vertical” to add your vertical axis title. Replace the titles with the labels you used for your X and Y data. For example, enter “X” for the horizontal axis title, and then enter “Y” for the vertical axis title.
- Click the “Add Chart Element” drop-down button again, select “Data Labels,” and then click “Data Callouts” to label your data markers.
Tips & Warnings
Information in this article applies to Microsoft Excel 2017. It may vary for other versions or products.