Convert CSV to Excel with Formatting

A CSV is ugly. It's just text. When you send a report to your boss or client, you don't want them to see a raw CSV. You want:

• Column headers in Bold.
• Filters enabled.
• Columns auto-sized to fit text.
• Alternating row colors (Zebra striping).

Here is how to turn a CSV into a polished Excel Table instantly.

The "Format as Table" Trick

1. Open the CSV in Excel.
2. Click anywhere inside your data.
3. Press Ctrl+T (or Cmd+T on Mac).
4. A dialog pops up: "Where is the data for your table?". Ensure "My table has headers" is checked.
5. Click OK.

Boom.

• Your data is now an official Excel Table.
• Headers are blue and bold.
• Filters are added automatically.
• Row banding (colors) is applied.
• If you scroll down, headers stick to the top.

Auto-Fit Columns

Even with a table, some text might be cut off (###).

1. Click the triangle at the top-left of the sheet (between A and 1) to select everything.
2. Double-click the line between Column A and B headers.
3. Excel will Auto-Fit every column width perfectly.

Saving as Excel

Crucial: You cannot save formatting in a CSV. If you hit Save now, Excel will warn you: "Some features may be lost." If you say Yes, all your pretty formatting is deleted.

You must:

1. File > Save As.
2. Change type to Excel Workbook (*.xlsx).
3. Save.

Automating This (Python)

If you generate reports daily, don't do this manually. Use Python with xlsxwriter.

import pandas as pd

df = pd.read_csv('data.csv')

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('report.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1', index=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num, value, header_format)

writer.close()

Summary

• CSVs cannot hold formatting.
• Use Ctrl+T in Excel to instantly apply a professional Table style.
• Always Save As .xlsx to keep the look.

---

Need pretty reports? HappyCSV can help you clean your data before you style it in Excel.