Excel sorting by best customers

Creating a list of your best customers yourself is still quite complex. We can do this for you, but otherwise, we'll explain below how you can do it yourself.

1. Export your transactions for the desired period. For this, see this other manual article .

2. Open the exported Excel file.

3. Click the triangle icon in the top left, so that all rows and columns turn green.

4. Click the 'Insert' tab at the top and then click the 'PivotTable' option.

5. A new screen appears. Click 'OK'.

6. A new worksheet opens. Click on the PivotTable image so that the window in step 7 appears.

7. On the right, click 'clientId', 'salutation', 'firstname', 'prefix', 'lastname', 'revenue', 'street', 'house number', 'city', 'postal code' and 'country'.

8. Make sure all fields except 'revenue' are under 'Rows' on the bottom right. Drag 'revenue' into the 'Values' window if it's not already there.

You can move data by clicking on it, holding down the mouse button, dragging, and then releasing the mouse button.

Make sure 'ClientId' is at the top in the 'Rows' window.

9. Do you see 'Count of revenue' in the 'Values' window?

Click on it, so a new window appears.

Click on the 'Value Field Settings' option.

10. Click the 'Sum' option and then click 'OK'.

11. Right-click on the information on the left side and select the 'PivotTable Options' option.

12. A new screen appears.

• Click on the 'Display' tab.

• Select the 'Classic PivotTable layout (enable dragging of fields in the grid)' option.

• Click the 'OK' button.

13. The data order shifts.

• Right-click on 'Country'.

• Click on the 'Subtotal country' option so that the checkmark in front of it DISAPPEARS.

14. Repeat step 13 for all fields, so that the list is displayed as follows:

15. Select all columns by clicking on the last one (in this case, letter K) and holding down the mouse, then moving the mouse to A. Then release so they are all green.

16. Press CTRL and C simultaneously to copy the information. The line will then become a dotted line.

17. Click the + icon at the bottom to create a new worksheet.

18. Right-click on cell 'A1' and select the 'Values' option under 'Paste Options' (the second icon).

19. Remove all extra rows above the headers, like ClientID. In this example, it concerns the top 3 rows.

(You can delete a row by right-clicking on the number in front of it and then clicking the 'Delete' option).

20. Click the triangle icon in the top left so that all columns turn green.

21. Press CTRL and the letter F simultaneously. A new window appears.

• Click on the 'Replace' tab.

• Enter (blank) in the 'Find what:' field.

• Click the 'Replace All' button.

Click the 'OK' button that appears and close the windows.

22. Click the triangle icon in the top left again so that all columns turn green.

23. Click the 'Sort & Filter' option in the top right and then click the 'Filter' option.

(This is under the 'Home' tab).

24. Click the triangle icon next to the 'Total' field and click the 'Sort Largest to Smallest' option.

25. Optionally, remove rows 2 and 3 if no customer data is shown there. This is a row with the total revenue and a row with the revenue from all transactions not linked to a customer.

Do you want to use this data for a mailing? Don't forget to filter by country, for example, and check if the address field is complete for customers.

Optionally, you can click the triangle icon next to 'street' and then uncheck the (Empty Cells) field.

For a mailing, it is also interesting to remove the other 2 worksheets to reduce the total file size.

Also, remember to remove the Total column if you share this data with third parties.

Furthermore, you can delete the rows to which you do not want to send a mailing.