Function Problems

How to Create a 1 to 5 Rating Selector Drop-Down in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to input ratings from 1 to 5 in an Excel worksheet easily, seeking an alternative to the difficult process of linking separate form buttons to individual worksheet rows.

Product: Excel
Device & OS: not provided
Scenario: Building a rating or feedback form in a spreadsheet where respondents or users can select a score from 1 to 5.
Observed behavior: The user finds that linking separate form buttons to the correct rows is too complex and inefficient, and needs a simpler selector method.

Before you start

Decide on the exact range of cells where you want the rating selector to appear and pick an empty column or hidden sheet to store your 1 to 5 reference numbers.

Solution 1Recommended

Use Data Validation to Create a Drop-Down List

A data-validation list is much simpler to implement than inserting and linking form buttons. It restricts cell input to a pre-defined list of numbers, creating a clean drop-down menu.

Using Data Validation allows you to create a scalable rating system without dealing with VBA macros or complex Developer tab form controls. You can apply this to as many rows as you need in seconds.

Create the reference list

Enter the numbers 1 through 5 in an out-of-the-way range in your worksheet, such as cells P1 to P5.

Select the target cells

Highlight all the cells where you want the rating drop-down menu to appear (for example, C1:C20).

Open Data Validation

Navigate to the 'Data' tab on the top ribbon menu and click on 'Data Validation'.

Configure the list source

In the settings dialog, change the 'Allow' dropdown box to 'List'. Click into the 'Source' box, then highlight your reference range (e.g., =$P$1:$P$5), and click OK.

Success: You will now see a clickable drop-down arrow next to your selected cells, allowing users to easily choose a rating from 1 to 5.

Easily Create Rating Forms in WPS Office

Use WPS Spreadsheet to Build Rating Drop-Downs Quickly

WPS Spreadsheet offers an intuitive Data Validation feature that makes creating custom drop-down lists and rating selectors incredibly easy, avoiding the hassle of complex button configurations.

1. Prepare the data: Open your workbook in WPS Spreadsheet and type the numbers 1 to 5 in a spare column.
2. Select target area: Highlight the cells where you want the rating drop-down to be available.
3. Apply Validation: Go to the Data tab on the ribbon and click Validation.
4. Set the List: Choose 'List' under Allow, select your 1 to 5 range as the Source, and click OK.

100% compatible with Microsoft Excel (.xlsx) file formats.Intuitive Data Validation menu for quick drop-down list creation.Lightweight application with smooth performance for large datasets.Free to use with a familiar, user-friendly tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I manually type the 1 to 5 list without using reference cells?

Yes. In the Data Validation dialog box, after selecting 'List', you can directly type '1,2,3,4,5' (separated by commas) into the Source field instead of referencing a cell range on the worksheet.

Can I use star symbols instead of numbers for my rating selector?

Yes. Instead of typing numbers 1 to 5 in your reference cells, you can insert star symbols using the Insert Symbol menu (e.g., ★, ★★, etc.) and use those cells as the source for your Data Validation list.

How do I remove the drop-down rating selector if I no longer need it?

Select the cells containing the drop-down, go to Data > Data Validation, click the 'Clear All' button in the dialog box, and then click OK.

Why does my drop-down arrow not appear when I click the cell?

Ensure that the 'In-cell dropdown' checkbox is checked within the Data Validation dialog box settings. If it is unchecked, the validation rules will still apply, but the visual drop-down arrow will be hidden.

Excel & Data Problems