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.