How to create an excel table in wps office (step-by-step)

August 21, 2023 3.3K views

Learning how to create tables is the essential foundation when beginning to use any spreadsheet software. To give you a solid start in honing your data management skills, we've prepared a comprehensive guide on how to create an Excel Table in WPS Office, giving you the confidence and know-how needed to navigate and organize information effectively.

What is WPS Office

Similar to other productivity suites, WPS Office offers familiar tools such as Writer for document creation, Spreadsheet for data management, and Presentation for creating impactful slideshows. However, what sets WPS Office apart is its additional PDF tool, which offers extensive capabilities, including OCR (optical character recognition) and signature features.

What makes WPS Office truly unique is its integrated design. Unlike many other office suites, you don't need to open separate windows for each tool. Instead, everything can be accessed within a single window, making it a comprehensive all-in-one productivity suite. Moreover, WPS Office goes a step further by incorporating integrated AI, enhancing the overall productivity experience for users.

Ways to Create an Excel Table in WPS Office

Create a Simple Excel Table

In this section, we will learn how to insert an Excel table in WPS Office. Here’s how:

Step 1: Open WPS Office on your system and select “New” from the Sidebar menu.

Step 2: Visit the Sidebar menu again and select “Sheets” followed by “Blank” to create a new blank sheet in WPS Office

Step 3: Choose the range of cells you want for the table.

Step 4: Visit the “Insert” Tab and click on “Table” from the ribbon menu.

Step 5: Since we have already selected the range, press “OK” in the “Create Table” dialog box. Users can also select the range or alter the range in the “Create Table” dialog box.

Step 6: Once the table has been inserted, WPS Office allows easy handling of the data. In the first row of each column, labeled "Column 1-3", click the arrow at the bottom right corner to filter the column. For instance, you can sort data in column A in descending order by clicking the arrow in A1.

Step 7: Users can also change the colors of their table by simply visiting the “Table Tools” tab and selecting the color of their choice from the ribbon menu.

Create an Excel Table with Subcategories

To create a table with subcategories in WPS Office, we need a data set that contains the main categories and their subcategories.

Let’s take the example of the store “Xfactor”. The store sells clothing goods, such as jackets, headgear, gloves, and sportswear. Here are the steps to create a table with subcategories for their products:

Step 1: Create a “MainItem” list. Navigate to the Formula tab, then pick “Name Manager”.

Step 2: Click on “New”.

Step 3: In the “Name” field, type “MainItem”. In the “Refer to” field, select the cells that contain the main items: H8:K8.

Step 4: To create the subcategories, select all the cells with Store data. Visit the Formula tab and select “Create”.

Step 5: Opt for “Top row” and press “OK”.

Step 6: Check the names of the main categories and subcategories. Access “Name Manager” to see all the lists.

Step 7: In cells A1 and A2, type Main Item and Subitem respectively.

Step 8: To create a drop-down list for the main items, select the cells below A1. Go to the Data tab and select Validation.

Step 9: In the Data Validation window, select “List” for the “Allow” field. In the “Source” field, enter “=MainItem” and click “OK”.

Step 10: To create a drop-down list for the “subitems”, select the cells below A2. Go to the Data tab and select “Validation” again.

Step 11: In the Data Validation window, select “List” for the “Allow” field. In the “Source” field, enter “=INDIRECT(A2)” and click OK.

You have created a table with subcategories in WPS Office. You can now select the main item and the sub-items under it from the drop-down lists. This makes stock management easier.



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

How to download WPS Office?

Step 1: Open the WPS Office website, and click the "Download" button at the screen center.

Step 2: To begin installing the WPS Office suite, double-click the downloaded file.

Step 3: Once the installation is finished, you will be able to utilize the WPS Office suite.


Q1. How do I make rows and columns in WPS?

To make rows and columns in WPS Spreadsheet, you can use the Rows and Columns tool in the Home tab. This tool can help you set the rows and columns of cells and make your work easier with WPS Spreadsheet.

Q2. How can I enhance the appearance of my Excel table in the WPS Office?

WPS Spreadsheet provides various formatting choices to improve the appearance of your Excel table. Fonts, cell colors, and borders may all be customized. This tool allows you to build visually stunning tables that improve the design of your page.

Q3. Is it possible to add a header row to my Excel table in WPS Office?

Yes, you can insert a header row into your Excel table in WPS Office. This function is beneficial since it allows you to assign names to your columns, making your data much more structured and understandable. With this effortless transformation, you immediately recognize the text of each column, improving the table's visibility and readability.

Q4. How can I resize columns and rows in an Excel table using the WPS office?

Resizing columns and rows in an Excel table is effortless in WPS Office. You can change the widths or heights of the columns or rows by clicking and dragging their border lines. This feature allows you to change the table arrangement, ensuring that your data fits precisely and is in an orderly pattern.

Master Table Creation in WPS Spreadsheets

In this article, you've gained valuable insights on how to create Excel tables in WPS Office. The process of how to create an Excel table in WPS Office is straightforward, and as you become more comfortable with it, you'll be well-prepared to delve into the array of advanced features that WPS Spreadsheet has to offer. Take a step forward by downloading WPS Office today to unlock its full potential.

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