How to Highlight Blank Cells in Excel Using WPS Office? (3 Common Methods)

February 1, 2024 463 views

We all know how to highlight text or values written somewhere, using it to put special emphasis on certain information. However, if you believe that highlighting is limited to text and values, you're severely mistaken. Highlighting empty spaces or cells is equally crucial, especially in spreadsheets. There are instances where you might need to draw attention to an empty space for specific reasons, such as indicating where data is expected, marking placeholders, or emphasizing areas for future input. In this article, we will explore how to highlight blank cells in excel WPS Office using three simple methods.

How to Highlight Blank Cells in Excel Using WPS Office

Method 1: Using Conditional Formatting

The first method we will explore to highlight blank cells in Excel using WPS Office is through conditional formatting. Conditional formatting enables users to establish conditions to format their data. WPS Office provides users the advantage of applying any number of conditions to ensure their data is formatted and presented according to their preferences. Let's see how to highlight blank or empty cells in our Excel files using WPS Office.

Step 1: Open the Excel file in WPS Office and select all your data to apply conditional formatting.

Step 2: Next, go to the Home tab and click on the Conditional Formatting option.

WPS Spreadsheet Conditional Formatting

WPS Spreadsheet Conditional Formatting

WPS Spreadsheet Conditional Formatting

Step 3: In the Conditional Formatting dropdown menu, select "New Rule" to set up a new condition for formatting our cells.

WPS Spreadsheet New Rule

Step 4: In the New Formatting Rule window, click on the "Format only cells that contain" option in the "Select a new rule type" section.

WPS Spreadsheet Format only cells with

WPS Spreadsheet Format only cells with

WPS Spreadsheet Format only cells with

Step 5: Now, in the "Edit the Rule Description" section, expand the first field in "Format only cells with" and then select "Blank".

WPS Spreadsheet format blank cells

So far, we have set a condition where WPS Office will only format cells that contain or match our specified condition, and we have chosen "Blank" as our condition.

Step 6: With the condition set, click on the "Format" button.

WPS Spreadsheet Format option

Step 7: To highlight the cells, go to the Pattern tab in the Format Cells dialog. Here, select the desired highlight color from the available options and click OK.

WPS Spreadsheet Format Cells dialog

Step 8: Once the highlight color has been selected, you will be back to the New Formatting Rule dialog, here, simply click on OK.

WPS Spreadsheet apply Conditional Formatting

Step 9: WPS Office will execute based on the condition and apply formatting to all blank cells. As a result, we can see all the blank cells in our data highlighted with the chosen color.

WPS Spreadsheet Conditional Formatting to highlight blank cells

One notable feature of WPS Office is that it provides all tools conveniently, and applying conditional formatting is quite straightforward. While users new to using conditional formatting in a spreadsheet tool might find it confusing initially, WPS Office has simplified this potentially confusing tool, making it easy for users to learn and perform tasks with ease.

Method 2: Using 'Go to Special' Feature

The next tool on the list is the "Go To" feature in WPS Office. With this feature, WPS Office will simply select all the blank cells in your data, and users can then manually highlight the empty cells in Excel.

Step 1: In WPS Spreadsheet, select all the data in your spreadsheet using your mouse or simply press the keyboard shortcut "CTRL + A".

Step 2: Once all the data is selected, navigate to the Home tab, and then on the far right of the Home ribbon, click on the "Find and Replace" option.

WPS Spreadsheet Find and Replace option

Note: If you can't find the "Find and Replace" option, try clicking on the arrow on the Home ribbon to scroll right and see more available options.

Step 3: In the "Find and Replace" option, simply click on the "Go To" option.

WPS Spreadsheet Go To Option

Note: We can also use the keyboard shortcut "CTRL + G" to directly access the Go To feature in WPS Spreadsheet.

Step 4: In the Go To dialog, check the "Blank" option and then press "Go To".

WPS Spreadsheet Go To dialog

Step 5: You can see that all the blank cells have been selected. Now, you can manually highlight them.

WPS Spreadsheet all blank cells selected

Step 6: To highlight them, click on the "Fill Color" icon in the Home tab.

WPS Spreadsheet Fill Color

Step 7: If you want to change the highlight color, click on the small arrow on the Fill Color icon to expand a list of colors to choose from.

WPS Spreadsheet Color Option

Step 8: That's it! The cells you selected will be highlighted with the chosen color.

The Go To feature is a very easy-to-use tool, and in WPS Spreadsheet, we can easily utilize this tool. The simplicity of this method means that every time we need to highlight blank cells, we'll have to go through this manual process. However, considering that WPS Spreadsheet is free software, it's not a significant issue, and users can use it unlimited times.

Method 3: Using Filtering Feature

WPS Spreadsheet also features the Filter tool, an important tool that can be used to highlight blank cells if your cells have been formatted as a table.

Step 1: In WPS Spreadsheet table, go to the header column of your table and find a small arrow at the right side of the cell.

WPS Spreadsheet Table header column

Step 2: Clicking on this arrow will expand the filter tool. Here, we will see all the data in this column. Since we only want to filter Blank cells, we will tick only the "Blanks" cell checkbox and then press OK.

WPS Spreadsheet filter blank cells

Note: Instead of unselecting all the checkboxes, untick the "Select all" checkbox and then tick the "Blank cells" checkbox.

Step 3: Now, all cells in our table will be hidden, and only blank cells will be displayed.

Step 4: Next, we will select all the cells in our table using our mouse or using the shortcut key "CTRL + A".

WPS Spreadsheet select all blank cells

Step 5: To highlight our blank cells, we will go to the Home tab and then click on the "Fill Color" icon to highlight our cells.

Step 6: To select a different color to highlight, click on the arrow in the Fill Color icon to expand and view more color options.

WPS Spreadsheet Fill Color

Step 7: Now, all our blank cells have been highlighted. To turn off the filter, open the filter options again and this time tick the "Select all" option.

 WPS Spreadsheet Filter table column

WPS Spreadsheet Filter table column

Step 8: Now, we have our table back with all the blank cells highlighted.

WPS Spreadsheet Filter blank cells to highlight

WPS Spreadsheet Filter blank cells to highlight

This is a very useful tool, but one thing to note is that it is only viable when your data is in table format. The filter will group the blank cells from one column together, allowing them to be selected and highlighted conveniently. Additionally, if you have multiple columns, you will need to filter each individual column for blank cells, which can make the process lengthy.

Which Method to choose?




Conditional Formatting

  • User-friendly interface.

  • Flexible conditions for formatting.

  • Quick and easy to apply.

  • Initial learning curve for users new to conditional formatting.

Go to Special Feature

  • Simple and quick process.

  • No complex conditions to set.

  • Easy to use for basic highlighting.

  • Manual processes may be time-consuming for large datasets.

Filtering Feature

  • Efficient for table-formatted data.

  • Allows grouped selection of blank cells.

  • Limited to table format.

  • Can be lengthy for multiple columns.

WPS Office Highlight blank cells Method Comparison

Optimize Data Presentation with WPS Spreadsheet

WPS Office is an all-in-one office suite, providing users with simplicity and a comprehensive set of office tools within a single platform. The suite encompasses essential tools like Writer, Spreadsheet, and Presentation, ensuring users can maximize productivity. The Spreadsheet tool, in particular, offers diverse capabilities, from handling formulas to efficient highlighting. This not only simplifies tasks but also transforms the work process into a seamless and enjoyable experience, allowing users to rediscover their passion for their work.

WPS Spreadsheet

WPS Spreadsheet

WPS Office truly brings out your productivity by placing everything conveniently in front of you, eliminating the need for tedious searches through various tools. Its direct PDF exporting options not only simplify collaboration but also make sharing so much easier. This user-friendly design makes WPS Office the top choice for both students and professionals globally.

WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
Algirdas Jasaitis


Q1. Can I remove the highlight in the WPS Spreadsheet?

Yes, you can effortlessly remove the unwanted highlights in the WPS spreadsheet by employing any of the following two methods:

  • Undo Function: Remove unwanted highlights using the “Undo” button..

  • Clear Formatting Function: Utilize the “Clear Formatting” function to erase all formatting, including the highlighting format.

Q2. Are the highlighting options in WPS Office similar to Excel?

If you're familiar with using Excel's highlighting features, you'll find a comparable experience in WPS Office. The highlighting options in WPS Office share similarities with Excel, offering users a user-friendly and familiar interface. Whether you are highlighting cells, rows, or columns, WPS Office ensures a smooth transition, allowing users to apply their preferred highlighting techniques effortlessly.

Q3. What is the importance of highlighting blank cells in data analysis?

Highlighting blank cells in data analysis is crucial for identifying missing data points and ensuring the accuracy and completeness of the dataset. This emphasis on blank cells allows analysts to easily identify areas requiring attention and take appropriate actions to fill in missing values, thereby contributing to the overall reliability of the analytical process.

Q4. Can I highlight blank cells based on specific conditions using Conditional Formatting?

You can highlight blank cells based on specific conditions using Conditional Formatting in WPS Office Excel. This feature empowers you to define specific criteria, enabling the automatic highlighting of blank cells that meet your chosen conditions.

Effortless Blank Cell Highlighting in WPS Spreadsheet

Highlight blank cells in excel WPS Office can convey various messages, such as signaling the need for specific input, marking placeholders, or creating assumptions. It's a versatile tool in data management, providing clarity and emphasis where needed. With WPS Office, this process becomes even more seamless. The user-friendly interface ensures a minimal learning curve, enabling you to perform tasks efficiently. WPS Office offers the simplicity you need for productive and effective work. Download WPS Office now to experience enhanced data handling and collaboration features.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.