Need to know how much you sold over time but Excel is not playing nice? Now worries, here are a few quick steps on how to get all the info you need, including skus, quantities sold, quantities shipped, and price over a specific period of time using a Pivot Table. Sounds scarier than it is, promise!

Please be aware that depending on the operating system and Excel version you have, it may look slighly different, but the concept is the same.

  1. Export your orders from Ordoro based on the order date range you want to analyze
  2. Open the downloaded csv file and Save As Excel (always do that before modifying a file since not all changes can be saved as csv later and you don’t want to do it all over again; any excel version is fine)
  3. Either export your shipped orders only, or consider sorting the file by shipped and/or in process orders only (delete the cancelled orders).
    1. Click on Highlight All
    2. Click on the Sort/Arrange tool
    3. Click on Custom Sort 
    4. Make sure the My List has Headers checkbox is selected
    5. Select Order Status from the column header
    6. Click OK 
    7. Delete all rows for cancelled orders
      1. Highlight the cancelled order rows
      2. Right click
      3. Select Delete 
  4. Create a Pivot Table
    1. Click on Highlight All
    2. Go to DATA tab
    3. Select Pivot Table
    4. Select Create Automatic Pivot Table (which will most likely open in a new sheet) 
  5. Make sure all checkboxes are unselected before you pick the correct values if any have been preselected (this helps avoid any confusions later)
  6. Only then select the following values which will fall into the below boxes (they may not always fall into the same ones, but no worries, you can always rearrange them by dragging and dropping them later):
    1. SKU
    2. Ordered quantity
    3. Order Line Price
    4. Shipped quantity
    5. Drag and drop SKU to the Row Labels column (left) 
  7. Voila! You will see the expected values automatically fill into a table. 

 

TIP: If you want to learn more about Pivot Tables and how to bend data to your own will, check out this great youtube video tutorial by ExcelIsFun. He provides easy to understand examples that you can follow along and practice with the downloadable templates.

Leave a Reply

Your email address will not be published. Required fields are marked *