How to Use Amazon Seller Tools in Excel
Microsoft Excel is one of the most popular tools among Amazon sellers for good reason. It's a well-known tool that is good at compiling and organizing data in spreadsheet format, and it's got lots of tools for crunching and analyzing the data to allow sellers to make some decisions about their businesses.
Amazon data download isn’t easy, and once you get the data, you want to be able to use it. But there's a lot going on in Excel, and many don't realize just how many tools this platform has to offer. If you're looking to get more out of Excel's capabilities, this guide will go over some of the capabilities that are uniquely suited to Amazon sellers.
Generally, the Amazon seller tools in Excel you'll want to use fall into three categories: formulas, PivotTables, and Power Query.
Formulas
Many sellers who use Excel have dabbled in formulas at least a little bit. A formula uses values in a range of sales and performs a simple calculation.
Some of the most useful formulas for sellers include SUM, VLOOKUP, IF, MIN, MAX, AVERAGE, and SUMIF.
SUM
This formula adds up the values in a range of cells to create a sum total. This would be expressed with a formula like "=A1+A2+A3," which would add up the figures of A1, A2, and A3 to create a final total.
Microsoft definition: "The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three. For example: =SUM(A2:A10) Adds the values in cells A2:10."
VLOOKUP
VLOOKUP enables you to find certain figures buried deep in the data. Let's say you wanted to look up the price of a part but didn't want to look through your spreadsheet. However, you know the part is associated with a part number that you do have. You could use VLOOKUP to specify a range to look up that part number.
Microsoft definition: "Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID."
IF
The IF formula returns one value if the specified condition is true and another if false. So if you wanted to check to see which products in your inventory have been shipped, and they are marked as such in your spreadsheet, you could run an IF formula to see a full list of those products.
Microsoft definition: "The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False. For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2)."
MAX/MIN
The MAX/MIN formulas let you calculate the smallest or the largest number in a range. So if you'd like to know which product in your catalogue had the highest price tag and which had the lowest, you would run these formulas. For example, "=MIN(A2:A100)" would find the smallest number of everything listed between cells A2 and A100.
Microsoft definition: "MAX: Returns the largest value in a set of values. MIN: Returns the smallest number in a set of values."
AVERAGE
AVERAGE is a very simple formula that lets you quickly average all of the figures within a range. For example, if you want to know the average sale price of a certain list of products and they are all listed from cells A2 through A100, the formula would need to be: =AVERAGE(A2:A100)
Microsoft definition: "Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers."
SUMIF
SUMIF averages all of the values that meet a certain criteria, rather than simply all of the values within a range. So you would specify not only a range, but also a qualifier, such as that the figure must be above or below a certain number.
For example, a seller who is looking at getting rid of low-dollar items so they can focus on higher-end goods might seek to identify products in their catalogue with a unit price of less than $20. If all of those product prices are listed in column D between cells D2 and D100, the formula would look like this: =SUMIF(D2:D100,"<20")
Microsoft definition: "You use the SUMIF function to sum the values in a range that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula: =SUMIF(B2:B25,">5")"
More info on formulas: Overview of formulas in Excel
PivotTables
PivotTables are more complex than formulas. These are tables that aggregate data and allow you to drag things into rows and columns. They also allow you to, say, filter for a specific product ASIN. In that example, you'd be able to input dates and see how much of a specific product sold on each date.
It's less of a formula and more of a summarization of data. You can slice and dice it as you please, and manipulate it to give you the information you're looking for. These are very helpful in uncovering key realities of your business and opportunities for growth.
PivotTables are so named because they allow you to "pivot" statistics in such a way to get different angles on your business and make actionable conclusions about the data. They are key to data analysis and therefore it's vital that you learn how to use them effectively as an Amazon seller.
Here's how to create a simple pivot table in Excel:
Select the cells you want to analyze.
Click on "Insert" then "PivotTable."
In the box provided, choose "select a table or range" and "existing worksheet." Choose the cell range in the "Table/Range" field. Click OK.
Modify and add fields to your PivotTable in the PivotTables Fields pane.
More info on PivotTables: Overview of PivotTables and PivotCharts
Power Query
Power Query is an even more advanced option in Excel. This tool takes data imported from multiple different files and combines and sorts them into one Excel spreadsheet. It takes a little skill to match up the data, but once you've mastered it, it's a powerful tool to combine your data into one place.
That's not to say this is always the right option. Sometimes a pivot table is a better way to crunch the data, and sometimes you need Power Query. But when you need to join data together, this is the tool you need.
As Microsoft says themselves about Power Query: "A vase starts as a lump of clay that one shapes into something practical and beautiful. Data is the same. It needs shaping into a table that is suitable for your needs and that enables attractive reports and dashboards."
For example, if you have both inventory data and sales data, and you want to see how much inventory you have on a specific day, you could merge those two data sources together. Then, you could sort the data by ASIN and by date. Now, you have one table that shows how many net received and how many sellable units are available on hand.
You can do other things like merge columns, change a data type, filter rows, or remove a column.
The Power Query Editor is a dedicated window that gives you control over the process. It has all of the aforementioned commands in a menu at the top, making it a little easier to manipulate the data. You will need to familiarize yourself with its capabilities, however.
More info on Power Query: About Power Query in Excel
Getting the Data Into Excel Can Be a Challenge
Of course, none of this data is helpful if you can’t even get to it to begin with. If you’ve got spreadsheets of data, but they’re all for only one product and one day at a time, it’s not just you -- that’s how Amazon provides data to sellers. It’s frustrating and time-consuming to combine it all.
Fortunately, there are plenty of services out there that will collect all of your data in Amazon and put it in an Excel spreadsheet or other format to help you analyze it (without spending all of your time collecting it). We’ve put together a whitepaper that breaks down all of your options. “Top 8 Methods for Accessing Amazon Seller Data.” It is available below for free download.
Read More:
Top 8 Methods for Accessing
Amazon Seller Data
As an Amazon seller, you know that data can be the difference between thriving as a company and losing share to competitors. But Amazon data has plenty of quirks, and acquiring it can be an unexpected challenge. This guide will help you understand your options for accessing and managing Amazon seller data, and each option’s potential impact on your business.
Receive the complete 14-page whitepaper: