Saving and file formats (xlsx, csv) in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office assistant managing monthly sales data.

📋 Request: Your manager wants you to save the sales report in two formats: Excel (.xlsx) for editing and CSV (.csv) for sharing with the sales team.

📊 Data: You have a sales report with columns: Date, Product, Region, Sales Amount.

🎯 Deliverable: Save the sales report as an Excel file and also as a CSV file.

Sample Data

Date	Product	Region	Sales Amount
2024-05-01	Notebook	North	120
2024-05-02	Pen	East	75
2024-05-03	Notebook	South	90
2024-05-04	Pen	West	60
2024-05-05	Notebook	East	110
2024-05-06	Pen	North	80
2024-05-07	Notebook	West	95
2024-05-08	Pen	South	70

1

Step 1: Open the sales report Excel file with the data shown.

No formula needed.

Expected Result

The sales data is visible in the spreadsheet.

2

Step 2: Save the file as an Excel workbook (.xlsx) to keep all formatting and formulas.

Click File > Save As > Choose location > Select 'Excel Workbook (*.xlsx)' > Save.

Expected Result

File saved as 'SalesReport.xlsx' with all data and formatting preserved.

3

Step 3: Save the same file as a CSV file to share with the sales team who may not use Excel.

Click File > Save As > Choose location > Select 'CSV (Comma delimited) (*.csv)' > Save.

Expected Result

File saved as 'SalesReport.csv' containing plain text data separated by commas.

4

Step 4: Close and reopen the CSV file to verify data is correctly saved.

Open 'SalesReport.csv' in Excel or a text editor.

Expected Result

Data appears as plain text with columns separated by commas, no formatting or formulas.

Final Result

SalesReport.xlsx
+------------+---------+--------+--------------+
| Date       | Product | Region | Sales Amount |
+------------+---------+--------+--------------+
| 2024-05-01 | Notebook| North  | 120          |
| 2024-05-02 | Pen     | East   | 75           |
| 2024-05-03 | Notebook| South  | 90           |
| 2024-05-04 | Pen     | West   | 60           |
| 2024-05-05 | Notebook| East   | 110          |
| 2024-05-06 | Pen     | North  | 80           |
| 2024-05-07 | Notebook| West   | 95           |
| 2024-05-08 | Pen     | South  | 70           |
+------------+---------+--------+--------------+

SalesReport.csv
Date,Product,Region,Sales Amount
2024-05-01,Notebook,North,120
2024-05-02,Pen,East,75
2024-05-03,Notebook,South,90
2024-05-04,Pen,West,60
2024-05-05,Notebook,East,110
2024-05-06,Pen,North,80
2024-05-07,Notebook,West,95
2024-05-08,Pen,South,70

✓Excel (.xlsx) format keeps all formatting and formulas intact.

✓CSV format saves only plain text data separated by commas.

✓CSV files are useful for sharing data with users who do not use Excel.

✓Always verify CSV files after saving to ensure data integrity.

Bonus Challenge

Try saving the file as a 'Text (Tab delimited) (*.txt)' format and compare it with the CSV file.

Show Hint

Use the Save As option and select 'Text (Tab delimited) (*.txt)'. Open the file in a text editor to see tabs separating the data.

Practice

1. Which file format should you choose to save an Excel file if you want to keep all formulas, formatting, and multiple sheets?

easy

A. .pdf

B. .csv

C. .txt

D. .xlsx

Solution

1. Step 1: Understand file format features

   .xlsx supports formulas, formatting, and multiple sheets, while .csv only stores plain data.

2. Step 2: Match requirement to format

   Since you want to keep all Excel features, .xlsx is the correct choice.

3. Final Answer:

   .xlsx -> Option D

4. Quick Check:

   Full Excel features = .xlsx [OK]

Hint: Use .xlsx to keep all Excel features intact [OK]

Common Mistakes:

• Choosing .csv and losing formulas
• Saving as .txt which loses formatting
• Using .pdf which is not editable

2. Which of the following is the correct way to save a file as CSV in Excel?

easy

A. File > Save As > Choose .xlsx format

B. File > Save As > Choose .csv format

C. File > Export > Choose .pdf format

D. File > Save As > Choose .txt format

Solution

1. Step 1: Locate saving options in Excel

   To save as CSV, you use the Save As option and select the CSV format from the dropdown.

2. Step 2: Identify the correct format choice

   Choosing .csv saves the file as comma-separated values, suitable for plain data sharing.

3. Final Answer:

   File > Save As > Choose .csv format -> Option B

4. Quick Check:

   Save as CSV = Save As + .csv [OK]

Hint: Save As and pick .csv to export plain data [OK]

Common Mistakes:

• Selecting .xlsx instead of .csv
• Using Export > PDF instead of Save As
• Choosing .txt which is not CSV

3. You save an Excel sheet with formulas as .csv and then open it again. What will happen to the formulas?

medium

A. Formulas will be saved as text strings

B. Formulas will be preserved and editable

C. Formulas will be converted to their last calculated values

D. File will not open because CSV does not support formulas

Solution

1. Step 1: Understand CSV format limitations

   CSV files store only plain text data, no formulas or formatting.

2. Step 2: What happens to formulas when saved as CSV

   Excel saves the last calculated values of formulas, not the formulas themselves.

3. Final Answer:

   Formulas become their last calculated values -> Option C

4. Quick Check:

   CSV saves values, not formulas [OK]

Hint: CSV saves values only, formulas become static numbers [OK]

Common Mistakes:

• Thinking formulas stay editable in CSV
• Assuming formulas save as text
• Believing CSV files won't open

4. You saved your Excel file as .csv but notice that some data with commas got split into multiple columns. How can you fix this issue?

medium

A. Replace commas in data with another character before saving as CSV

B. Save the file as .xlsx instead to keep data intact

C. Open CSV in a text editor and remove commas manually

D. Save the file as .txt to avoid splitting

Solution

1. Step 1: Understand CSV comma delimiter issue

   CSV uses commas to separate columns, so commas inside data cause unwanted splits.

2. Step 2: Fix data before saving

   Replacing commas inside data with another character (like semicolon) prevents splitting when saved as CSV.

3. Final Answer:

   Replace commas in data before saving as CSV -> Option A

4. Quick Check:

   Commas inside data break CSV columns, replace them [OK]

Hint: Replace commas inside data before saving CSV [OK]

Common Mistakes:

• Just saving as .xlsx without fixing data
• Manually editing CSV text which is error-prone
• Saving as .txt which doesn't solve comma issue

5. You have a large Excel workbook with multiple sheets, formulas, and formatting. You need to share only the raw data from one sheet with a colleague who does not use Excel. What is the best way to save and share this data?

hard

A. Save the specific sheet as .csv and send the CSV file

B. Copy the sheet data and paste into a Word document

C. Save the entire workbook as .xlsx and send it

D. Print the sheet and send a scanned image

Solution

1. Step 1: Identify sharing needs

   The colleague does not use Excel, so sending an Excel file may not help.

2. Step 2: Choose a simple, widely supported format

   CSV files contain raw data and can be opened by many programs, making it ideal for sharing raw data.

3. Step 3: Save only the needed sheet as CSV

   Saving just the required sheet as CSV avoids sending unnecessary data and keeps it simple.

4. Final Answer:

   Save the specific sheet as .csv and send it -> Option A

5. Quick Check:

   Share raw data simply = save sheet as CSV [OK]

Hint: Save needed sheet as CSV to share raw data easily [OK]

Common Mistakes:

• Sending full .xlsx file to non-Excel user
• Using Word which may lose table structure
• Sending scanned images which are not editable