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:
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)
Turning off Filters
- 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
Tip: For more information refer to the Inventory - Using Mass Upload/Mass Download to create & edit products article
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.
- Highlight the Supplier SKU column by clicking on the column header "Column C"
- Click Conditional Formatting > Highlight Cells Rules > Duplicate Values
Tip: The Conditional Formatting button is on the Home tab of Microsoft Excel
- Select Unique and the colour preference for highlighting the cells
- Click OK
- The duplicates will be highlighted accordingly
Tip: You can enable Conditional Formatting for the column before entering your products, so duplicates are highlighted as you create them.
To resolve the duplicate error, change one of the SKU codes. Once they're both unique values the highlighting will be removed.
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.
- Navigate to: Quick Links > Sales Report
- Enter the required Date From\Date To
- Tick Do not summarise by Outlet
- Click Search
- The results will display two Export to Excel buttons (in the top right corner).
- Click the lower one (just above the list of individual transactions)
- The report will be downloaded as a file, open the file in Excel
- Click Enable Editing
- Click the Insert tab
- Click Pivot Table
- Leave the auto selected options as is
- Click OK
- Using the Pivot Table Fields details (on the far-right) tick to select Bill Name and Order Total Inc
- The details will be populated into the main sheet for review
Note: 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.
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.
- Open the Mass Download file.
- Click Enable Editing if required
- Click the Home tab of the ribbon at the top
- 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
- Click Sort & Filter in the tool bar at the top
- Select Sort A to Z or Sort Z to A (depending on your preference for ascending or descending)
- Select Expand the selection (to ensure all of your data stays together)
- Click Sort
The spreadsheet will be sorted in numeric and alphabetical order.