Excel XLSX Skill Tool
Overview
The xlsx skill is used to work with Excel spreadsheets (.xlsx, .xlsm, .csv, .tsv). It supports creating spreadsheets, reading data, modifying cells, adding formulas, and data analysis.
When to Use
- Creating new spreadsheets from scratch
- Reading or analyzing existing spreadsheet data
- Modifying cells while preserving existing formulas and formatting
- Adding formulas and performing calculations
- Analyzing data and creating visualizations
Setup
The xlsx skill is built-in and available through the Skill tool. No installation required.
Usage Patterns
Reading a Spreadsheet
Read the file 'data.xlsx' and understand the structure Invoke the xlsx skill with operation: read
Modifying Cells
To set a formula in a cell: Use xlsx skill with operation: write_cell Specify: file path, sheet name, cell reference, value/formula To modify multiple cells: Use xlsx skill with operation: batch_write
Working with Formulas
When adding formulas to Excel:
- Use cell references like A1, B2:B10
- Use sheet references like Sheet1!A1
- Formulas start with
= - For multi-sheet references: ='SheetName'!CellRef
Key Functions to Know
-
VLOOKUP: Lookup values in a vertical table
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) -
HLOOKUP: Lookup values in a horizontal table
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) -
INDEX & MATCH: More flexible lookup
=INDEX(return_array, MATCH(lookup_value, lookup_array, 0)) -
XLOOKUP: Modern Excel function (Excel 365+)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Statistical Functions
=MIN(range)- Minimum value=MAX(range)- Maximum value=MEDIAN(range)- Median value=AVERAGE(range)- Mean value=PERCENTILE(range, k)- kth percentile (k as decimal, e.g., 0.25 for 25th)=SUMPRODUCT(array1, array2)- For weighted calculations
Best Practices
- Always preserve existing formatting and colors
- When using two conditions for lookup, use INDEX/MATCH or XLOOKUP
- For weighted calculations, SUMPRODUCT is ideal
- Test formulas with sample data first
- Round percentage results appropriately (e.g., multiply by 100 for percentage display)
Example: Two-Condition Lookup
=INDEX(data_range, MATCH(1, (condition1_range=criteria1)*(condition2_range=criteria2), 0))
Requires array formula (Ctrl+Shift+Enter in some Excel versions).
Alternatively, use XLOOKUP if available:
=XLOOKUP(lookup_value, lookup_array, return_array)
Example: Weighted Mean with SUMPRODUCT
=SUMPRODUCT(values, weights) / SUM(weights)
This multiplies each value by its weight, sums them, and divides by total weights.
