# Create formula with structured references in WPS Office Excel

A free Office suite fully compatible with Microsoft Office

A free Office suite fully compatible with Microsoft Office

### Welcome to WPS Official Academy to check the contents of how to create formula with structured references in WPS Office Excel.

##
· **Explicit reference & S****tructured reference**** in WPS Spreadsheet**

Before learning how to use structured reference, it’s best to know what it is and why it is used first. Here we introduce another concept **Explicit reference **for comparison.

###
**1. ****What is ****Explicit Reference**

**Explicit Reference**** is** mostly used in our daily work. When you input the specific cell range like C2:E9 in a formula, you are using **Explicit Reference**.

The specific cell range you reference can be known easily from the formula, but you may not learn what the formula is calculating.

Besides, if you changed the table, for example, inserting a column, you have to revise your formula.

###
**2. ****What is ****Structured reference**

**Explicit Reference** refers the combination of the table and column names. When you input the table and column names instead of the specific cell range in a formula, you are using**S****tructured Reference**.

##
· **Steps to use structured references**** in WPS Spreadsheet**

**Step 1: Change the table name for structured references**

1. Insert a table in WPS Spreadsheet: Select the cell range of your data, click the **Insert **tab > **Table **> check **My table has headers **> **OK**.

2. Click any cell of the table to enter the **Table Tools **tab, input the **Table Name **in the upper left corner of the ribbon and press **Enter **to confirm it.

*(Here we input **sales **for demonstration. You can also use the default name.)*

**Step 2: Know about the structured reference syntax rules**

Here we take two formulas as examples to learn about the syntax rules.

=SUM(Sales[Banana])

**Table name**: “Sales” is a custom table name. It references the table data, without any header or total rows.

**Column specifier**: [Banana] and [Apple] are column specifiers that use the names of the columns they represent. They reference the column data, without any column header or total row. Remember to enclose specifiers in brackets.

###
**Step 3: Apply structured references to a spreadsheet example**

1.Suppose we want to calculate the total sales of Banana. Input: =SUM(Sales[Banana]).

2. Suppose we want to calculate the total sales of January. Input: =SUM(Sales[@Banana],[@Apple],[@Orange],[@Pineapple]).

Tips: the @ symbol refers to “this row.”

3. Suppose we want to calculate the sales proportion of Banana in each month. Input: =[@Banana]/[@Total]*100&%

With these steps,**you have mastered how to use structured references efficiently. **

###
**Learn more advanced skills about ****reference**** in WPS ****Spreadsheet****:**

Relative reference, absolute reference, and mixed reference | WPS Academy Free Office Courses

For certain tasks, you can insert checkbox in WPS Spreadsheet by making a checklist for your task or daily chores. Making a checklist could be helpful for making inventories, schedules, or simply running through a number of stuff in order. If you don't know how to insert checkbox in excel just go to WPS Academy, where you can find free detailed instructions.

Was this helpful?

Yes

No

Trending Tutorials

- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to color cell based on value in Excel?
- 5. How to add text to beginning or end of all cells in Excel
- 6. How to compare two excel sheets and highlight differences