Knowledge Base

Retail Express

Excel Tips from the Support Team

The majority of reports available in Retail Express are available for export to Excel, giving you even greater reporting power! Using Excel you can manipulate the data to easily find more information, or create your own reports.

Tip: To learn more about using Excel, we recommend the free training courses and articles available directly from Microsoft

Below are the top tips from our Support team to get you started using Excel efficiently for reporting in your business.

In this article:

Using Filters to find data
Using Conditional Formatting to find duplicate SKUs
Using Pivot Tablets to view customer spend amounts
Sorting data into numerical or alphabetical order

Using Filters to find data in large spreadsheets

When reviewing a large volume of exported data it can sometimes be difficult to find what you are looking for. To assist with searching, filtering, and even grouping of information, the Filter tool can be very useful.

As an example we will use a Mass Download file, and search the Short Description column for all products with the word ?Water? in them in order to increase their price by 20%.

Navigate to: Inventory > Mass Download
Enter filter options as required
Click Export to Excel
Open the report in Excel
Click Enable Editing
Select the correct Outlet using the tabs at the bottom of your Mass Download spreadsheet
Click the header of column (D) so that the column is highlighted
In the toolbar ribbon click Sort & Filter (in the Editing section)
Click Filter
At the top of the column a drop-down arrow will be displayed (just under the column heading)
Click the arrow to display the Filter menu
Select Text Filters and Contains
Enter the word to search for e.g. Water
Click OK
Edit the product fields as required e.g. POS Price

📋 Tip:The Filter options also contains other formulas as well as the ability to tick only specific products. Try playing around with these settings to see how they might assist you with finding data e.g. "Begins With", "Ends with". You can even try filtering with more than one column by selecting all the data in your spreadsheet (not just one column)

Note: For more information refer to the Inventory - Using Mass Upload/Mass Download to create & edit products article.

Use Conditional Formatting to find duplicate SKUs

Conditional Formatting gives you the ability to highlight data in the spreadsheet based on particular rules e.g. cells that contain specific values, or if more than one cell exists with matching data. This makes it perfect to use when creating products.

For example, if you're using the Mass Upload spreadsheet to create new products, you can use Conditional Formatting to highlight duplicate SKUs.

📋 Note: You can enable Conditional Formatting for the column before entering your products, so duplicates are highlighted as you create them.

In the toolbar ribbon at the top of your screen click on the Sort & Filter button
Click the Filter option again
The filter will turn off (all rows in the spreadsheet will be displayed
Save your spread sheet
Upload the file in Retail Express to update the data
To resolve the duplicate error, change one of the SKU codes. Once they're both unique values the highlighting will be removed.

Back to top

Using Pivot Tables to find customer spend amounts over specific periods

A common query for the Support Team is how to view customer spend over a certain time frame. This can be achieved relatively easily via a pivot table, without advanced Excel knowledge.

Important: The above Excel steps are based on Office 365. If you are using a different version of Excel, please visit the Microsoft Excel Help Centre for alternate details.

Back to top

Sorting Data by numerical or alphabetical order

When you have exported a large amount of data from Retail Express it is sometimes easier to manipulate if your data is sorted alphabetically or numerically. Of course data is rarely in order when you extract it from Retail Express to Excel.

The example below uses the a Mass Download file.

The spreadsheet will be sorted in numeric and alphabetical order.

Back to top
1. Highlight the Supplier SKU column by clicking on the column header "Column C"
2. Click Conditional Formatting > Highlight Cells Rules > Duplicate Values
  📋 Note:The Conditional Formatting button is on the Home tab of Microsoft Excel
3. Select Unique and the colour preference for highlighting the cells
4. Click OK
5. The duplicates will be highlighted accordingly
6. Navigate to: Quick Links > Sales Report
7. Enter the required Date From\Date To
8. Tick Do not summarise by Outlet
9. Click Search
10. The results will display two Export to Excel buttons (in the top right corner).
11. Click the lower one (just above the list of individual transactions)
12. The report will be downloaded as a file, open the file in Excel
13. Click Enable Editing
14. Click the Insert tab
15. Click Pivot Table
16. Leave the auto selected options as is
17. Click OK
18. Using the Pivot Table Fields details (on the far-right) tick to select Bill Name and Order Total Inc
19. The details will be populated into the main sheet for review
20. Open the Mass Download file.
21. Click Enable Editing if required
22. Click the Home tab of the ribbon at the top
23. Click and highlight the cells in the column you want to sort, being careful to not include the headings
  Tip: Click the first cell, hold Shift and press End, then the Down arrow to quickly select all the content in the column
24. Click Sort & Filter in the tool bar at the top
25. Select Sort A to Z or Sort Z to A (depending on your preference for ascending or descending)
26. Select Expand the selection (to ensure all of your data stays together)
27. Click Sort
The spreadsheet will be sorted in numeric and alphabetical order.