Unlock Excel’s Hidden Power: Pivot Tables, VLOOKUP & Text Tools

Introduction

Borrowing the concepts of “Dao, Fa, Shu, Qi” from Lao‑zi’s Dao De Jing , the article maps them to a data product manager’s framework: “Dao” is the underlying law of a problem, “Fa” the guiding plan, “Shu” the technical methods, and “Qi” the tools such as SPSS or Excel.

Excel is portrayed as an indispensable “Qi” for data product managers, but merely entering data is insufficient; analysts must extract insights and devise actionable solutions.

01 Pivot Tables

Pivot tables have two view modes corresponding to the .xlsx (post‑2007) and .xls (pre‑2007) file formats. The classic layout (97‑2003 style) is preferred for its clarity; it can be toggled via PivotTable Options → Display → Classic Layout.

Pivot tables simplify analysis with a “drag‑drop‑summarize” workflow. Key techniques include:

Creating a pivot table : Click any cell in the data source and choose Insert → PivotTable; Excel automatically selects the data range.

Using groups : For time‑based data, right‑click a row field → Group to aggregate by month, quarter, or year.

Applying slicers : Slicers enable dynamic filtering of one or multiple pivot tables, allowing simultaneous analysis of overall totals and product‑level details.

Dynamic data source : Convert the source range to an Excel Table (Insert → Table). After adding or removing rows, right‑click the pivot table and select Refresh to capture changes automatically.

02 VLOOKUP Function

VLOOKUP belongs to the most popular function families (SUM, VLOOKUP, IF) that cover roughly 80% of data‑analysis tasks. Its syntax includes four arguments: lookup value, lookup range, column index, and match mode (0 for exact, 1 for approximate).

The article illustrates how to use VLOOKUP to verify or retrieve data from large datasets, emphasizing practical, visual examples.

03 “Text to Columns” Tool

The Text‑to‑Columns feature handles two common scenarios: extracting information (e.g., birthdate from an ID number) and converting text‑formatted numbers into numeric format for calculations.

Steps: select the source column, choose Data → Text to Columns, and follow the wizard to split or convert the data instantly.

Conclusion

Excel offers a rich set of tools that, when combined with proper techniques, can dramatically improve efficiency for data product managers and analysts. A link to a comprehensive Excel shortcut guide is provided for further reference.