Excel IF and VLOOKUP Formulas in WPS Office Spreadsheets to Split Scatter Chart Data
Question details
The user needs to create helper columns with an IF formula that returns a related value when a condition is met and NA() otherwise, so a scatter chart plots only the rows that match the condition.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Preparing split scatter chart data from a table where a Y/N column controls which rows should be plotted.
- Observed behavior
- The formula should return the corresponding value from column D for rows marked Y and #N/A for other rows, preventing those points from appearing in the scatter chart.
Open the workbook in WPS Office Spreadsheets and identify the condition column, for example column B, and the value column to plot, for example column D. Make sure an empty helper column is available so the IF formula can fill the split data.
Use an IF Formula with NA() to Create a Split Scatter Chart Data Series
Use this when you want to plot only the rows whose condition cell equals Y and hide all other points from the scatter chart.
The formula checks the condition in column B and returns the value in column D when true. When false, NA() creates an #N/A error, which scatter charts ignore instead of plotting as zero.
Click an empty cell in the helper column next to your data, for example C2, and enter the formula =IF(B2="Y",D2,NA()).
Press Enter, then drag the fill handle from the bottom-right corner of the cell down through all data rows to copy the formula.
Select the helper column together with the X-axis values, go to the Insert tab, choose Scatter, and use the helper column as the Y values for the split series.
Use IF with VLOOKUP to Pull the Plotted Value from a Lookup Table
Use this when the value to plot is not in the same row but must be retrieved from another table based on an ID or key.
Create Split Scatter Chart Series in WPS Office Spreadsheets with IF and VLOOKUP
WPS Office Spreadsheets supports Excel-style IF, VLOOKUP, and NA() formulas, so you can build split scatter chart data directly in a familiar grid without converting files. Enter the formula, fill it down, and insert a scatter chart that ignores #N/A values.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the .xlsx file that contains the Y/N condition column and the scatter chart source data.
- 2. Add a helper column: Click an empty cell next to the table and enter the formula =IF(B2="Y",D2,NA()).
- 3. Fill the helper column: Drag the fill handle down to copy the formula across all rows you want to evaluate.
- 4. Insert the scatter chart: Select the relevant X and Y helper data, go to Insert > Scatter, and choose a scatter chart type to plot only the matching points.
- 5. Save the file: Press Ctrl+S to save the workbook in Excel-compatible .xlsx format.
Frequently Asked Questions
Why use NA() instead of an empty string in the IF formula?
NA() returns #N/A, which WPS Office Spreadsheets scatter charts ignore. An empty string may be treated as zero, which can create unwanted points along the axis.
Can I use this formula when the value to plot is in another sheet?
Yes. Use =IF(B2="Y",Sheet2!D2,NA()) for a direct sheet reference, or use =IF(B2="Y",VLOOKUP(A2,Sheet2!$A$2:$D$100,4,FALSE),NA()) to retrieve the value by ID.
How do I copy the formula without changing the condition range?
Use relative references such as B2 and D2 when copying down, but keep lookup ranges absolute with dollar signs, such as $G$2:$H$100.
Does WPS Office save this workbook in Microsoft Excel format?
Yes. WPS Office Spreadsheets opens and saves .xlsx files, so the IF, VLOOKUP, and NA() formulas remain compatible with Microsoft Excel.
What should I do if the scatter chart still plots #N/A as zero?
Check the chart's hidden and empty cell settings. In WPS Office Spreadsheets, #N/A values are normally ignored; if not, set the data range to plot gaps instead of zeros.
