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.