Skip to main content
4
Step 4 of 5
Data Editing

Data Editing in Excel

In this step, you will copy the converted marks data into the Dynamics marks template, clean up the columns, and prepare the final file for import.

Tutorial video​

0:00/0:00

What you will do​

  • Open both Excel files: your converted marks and the Dynamics template
  • Copy the marks data into the template
  • Remove the student number column
  • Copy the batch number formula down the column
  • Delete the first (header) row
  • Use Excel's filter to clean up the data

Step-by-step instructions​

1. Open both Excel files​

Open:

  • The converted marks file from Step 2 (in your Downloads folder)
  • The Dynamics template CSV from Step 3

2. Copy the converted data into the template​

  1. Click on the converted marks spreadsheet.
  2. Press Ctrl + A to select all data.
  3. Press Ctrl + C to copy.
  4. Switch to the Dynamics template spreadsheet.
  5. Click on cell A1 in the template.
  6. Press Ctrl + V to paste.

3. Delete the student number column​

The converted data includes a student number column that is not needed:

  1. Click on the student number column header to select the whole column.
  2. Right-click the selected column.
  3. Click Delete → Entire Column.

4. Copy the batch number formula​

  1. Click on the first cell in the batch number column (Cell 1).
  2. Press Ctrl + C to copy it.
  3. Click the cell directly below it (Cell 2).
  4. Press Ctrl + V.
  5. Now select both cells (hold Shift and press Arrow Down).
  6. Hover your cursor to the bottom-right corner of Cell 2 until you see a bold + (plus) cursor.
  7. Double-click the bold + cursor — this will copy the formula down to all rows automatically.
The bold + cursor

When you hover at the very bottom-right corner of a cell, the normal white cursor changes to a solid black plus (+). That is the fill handle — double-clicking it fills the formula all the way down the column instantly.

5. Delete the first row​

The first row (header from the converted data) is no longer needed:

  1. Click on Row 1 header to select the entire first row.
  2. Right-click.
  3. Click Delete.

6. Filter for hyphen entries​

Some rows may contain a hyphen (-) instead of a mark value. You need to filter for those rows first:

  1. Go to the Excel ribbon and click Sort & Filter → Filter.
  2. Double-click on the filter button to open it, then click Filter.
  3. Click the dropdown arrow on the marks column.
  4. Click Uncheck Select All to clear all tick boxes.
  5. Scroll down the list and check only the hyphen / dash sign (-).
  6. Click OK — the view now shows only rows with a hyphen.

7. Replace hyphens with blank using Find & Replace​

With the hyphen rows visible:

  1. Go to the ribbon and click Find & Select → Find (or press Ctrl + H to open Find & Replace directly).
  2. In the Find what field, type a hyphen: -
  3. Leave the Replace with field completely blank.
  4. Click Replace All.
  5. Click OK on the confirmation dialog, then Close the Find & Replace window.
Why replace with blank?

A hyphen is treated as text, not a number. Replacing it with a blank tells the system the field has no value, which is what Dynamics expects for missing marks.

8. Clear the filter and verify​

  1. Go back to the Filter button on the ribbon.
  2. Click the dropdown again and check Select All to show all rows.
  3. Click OK.
  4. Scroll through the data — there should be no hyphens remaining. The data is now clean.

9. Save the file​

  1. Click File → Save As.
  2. Choose the Excel format (.xlsx or .csv as required).
  3. Name the file marks or final marks.
  4. Click Save.
note

Make sure you save before closing. The cleaned file is now ready to upload into Microsoft Dynamics.

Next step​

Proceed to Step 5 — Uploading Marks to import this final file into Microsoft Dynamics.