Function Problems

How to Automatically Enter a Score in Excel When an Option Is Selected

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs Excel to automatically generate a score of 1 in a specific column if either of two option columns contains data, and leave the cell blank if both option columns are empty.

Product: Excel
Device & OS: not provided
Scenario: Automating scoring or data evaluation based on user-selected options across multiple columns.
Observed behavior: The target cell displays a 1 when at least one option is selected, and stays blank when no options are selected.

Before you start

Ensure you have identified the exact columns containing your options (for example, Column A and Column B) and the target column where you want the automated score to appear (for example, Column C).

Solution 1Recommended

Use the IF and OR Functions to Automate Scoring

This method uses a combined IF and OR formula to check multiple cells simultaneously. If any of the specified cells contain a value, it outputs a 1; otherwise, it leaves the cell completely blank.

By nesting the OR function inside the IF function, you can evaluate multiple conditions at once. The <> operator is used to check if a cell is not equal to empty.

Select the target cell

Click on the cell in the score column where you want the first result to appear, such as cell C2.

Enter the logical formula

In the formula bar, type the formula: =IF(OR(A2<>"",B2<>""),1,"") and press Enter.

Apply the formula to the rest of the column

Click on cell C2 again, hover over the small square at the bottom-right corner until your cursor becomes a plus sign, and drag it down to fill the formula for the remaining rows.

Formula Breakdown: The OR(A2<>"",B2<>"") segment checks if either A2 or B2 is not empty. The IF function outputs 1 if this is true, and an empty string ("") if both cells are empty.

Use WPS Spreadsheet

Automate Formulas Seamlessly with WPS Office

WPS Spreadsheet is fully compatible with Microsoft Excel formulas, including logical functions like IF and OR. You can easily automate data entry and scoring with a familiar, user-friendly interface.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your option columns.
2. Select the score cell: Click on the cell where you want the automated score to display.
3. Input the formula: Type =IF(OR(A2<>"",B2<>""),1,"") and press Enter.
4. Fill the series: Drag the fill handle at the bottom-right of the cell down the column to automatically apply the scoring rule to all rows.

100% compatible with Microsoft Excel (.xlsx) formats and functionsFree, lightweight, and fast-loadingFamiliar UI makes transitioning from Excel effortlessBuilt-in intelligent formula hints and error checking

microsoft office alternative - wps office

Frequently Asked Questions

Can I assign a different score based on which option is selected?

Yes. Instead of the OR function, you can nest IF functions to output different scores. For example, =IF(A2<>"", 1, IF(B2<>"", 2, "")) will give a score of 1 if A2 is filled, and a score of 2 if B2 is filled.

Why is the formula returning a 0 instead of remaining blank?

Ensure that the last argument in your IF formula is two double quotes ("") without any space between them. This specifically instructs the spreadsheet to return a visually blank cell rather than defaulting to a zero.

How do I check more than two option columns for a score?

You can add additional cell references into the OR function separated by commas. For example, to check three columns (A, B, and C), use =IF(OR(A2<>"",B2<>"",C2<>""),1,"").

Excel & Data Problems