spreadsheet-tools

majiayu000's avatarfrom majiayu000

Guides and code for creating, analyzing, and formatting spreadsheets. Use this skill to work with Excel files programmatically and apply data analysis techniques.

⭐0stars🔀0forks📁View on GitHub🕐Updated Jan 5, 2026
[Spreadsheets]

When & Why to Use This Skill

This Claude skill provides comprehensive guides and code snippets for programmatically creating, analyzing, and formatting Excel spreadsheets. By leveraging powerful Python libraries such as pandas, openpyxl, and xlsxwriter, it enables users to automate complex data tasks, generate pivot tables, and insert dynamic charts, significantly enhancing productivity in data-centric workflows and ensuring high-quality data management.

Use Cases

  • Automated Financial Reporting: Generate recurring financial statements and sales reports with automated pivot tables and professional formatting.
  • Large-scale Data Processing: Use pandas to clean, filter, and transform massive datasets before exporting them to structured and readable Excel files.
  • Programmatic Workbook Management: Automate the insertion of complex Excel formulas and cell styles across multiple workbooks to ensure data consistency and accuracy.
  • Dynamic Data Visualization: Create professional-grade visual dashboards and charts within Excel files directly from raw data sources using xlsxwriter.
namespreadsheet-tools
descriptionGuides and code for creating, analyzing, and formatting spreadsheets. Use this skill to work with Excel files programmatically and apply data analysis techniques.
licenseMIT
categorydata

Spreadsheet Tools Manual

Overview

This skill provides instructions and code for manipulating spreadsheets, generating formulas, and analyzing data.

Working with pandas and openpyxl

Reading and Writing Excel Files

import pandas as pd

# Read Excel file
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# Write DataFrame to a new Excel file
df.to_excel('output.xlsx', index=False)

Applying Formulas

from openpyxl import load_workbook

wb = load_workbook('output.xlsx')
ws = wb.active

# Insert formula into cell C2
ws['C2'] = '=SUM(A2:B2)'
wb.save('output_with_formula.xlsx')

Pivot Tables

# Create a pivot table
pivot = df.pivot_table(values='Sales', index='Region', columns='Quarter', aggfunc='sum')
pivot.to_excel('pivot_table.xlsx')

Charts in Excel

import xlsxwriter

workbook = xlsxwriter.Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()
chart = workbook.add_chart({'type': 'line'})

# Write some data
data = [10, 40, 50, 20, 10, 50]
worksheet.write_column('A1', data)

# Configure chart
chart.add_series({'values': '=Sheet1!$A$1:$A$6'})
chart.set_title({'name': 'Sample Data'})
chart.set_x_axis({'name': 'Index'})
chart.set_y_axis({'name': 'Value'})

worksheet.insert_chart('C1', chart)
workbook.close()

Excel Best Practices

  • Use separate sheets for raw data, analysis, and results.
  • Name ranges and use table references for clarity.
  • Avoid hardcoding values in formulas; use cell references.
  • Document complex formulas with comments or a README.

Analytical Techniques

  • Descriptive statistics: mean, median, standard deviation.
  • Filtering and sorting: use pandas' query() and sort_values().
  • Time series analysis: convert date columns to datetime objects; resample using df.resample().

Additional Resources

  • pandas documentation.
  • openpyxl and xlsxwriter docs.
  • Excel Jet for formula tips.