Success in e-commerce requires detailed analytical skills. Although there are tools like jungle scout, many companies rely on Excel, which is very capable in the right hands.
Acuity Training explains the 10 Excel formulas every eCommerce Business needs to know in this article.
- AVERAGEIFS
- SUMIF
- COUNTIF
- CONCATENATE
- LEFT / RIGHT / MID
- Data Validation
- Remove Duplicate
- IF STATEMENT
- XLOOKUP (VLOOKUP)
- Pivot Table
The AVERAGEIF function can calculate the average based on user-defined criteria.
For example, this function can calculate the average order per day for a specific item or the average selling price of an item.
=AVERAGEIF (range, criteria, [average range])
range: defines the cells that you want the formula to ‘look at’ when running the formula
criteria: defines the criteria that you would like Excel to use to select the relevant data for the formula
For example, we wish to calculate average sales for “Chocolate pastries” in the below data
The formula will be as follows.
The result will be 438.
Note that AVERAGEIF will accept multiple criteria.
ECOMMERCE APPLICATIONS:
AVERAGEIF is a great way to extract high level management information from a very large detailed dataset. For example, average selling price, average margin etc.
Like AVERAGEIF, SUMIF function can be used to calculate a SUM based on different criteria.
This function can be used to calculate total sales for each specific item in our Ecommerce Store.
=SUMIF (range, criteria, [sum_range])
For example, we have region-wise sales data as following.
To find total sales for East region formula and results will be as
ECOMMERCE APPLICATIONS:
SUMIF is another great way to extract high-level management information from a very large detailed dataset. For example, total sales, total gross margin, etc.
COUNTIF:
COUNTIF function if similar to AVERAGEIF and SUMIF but counts the number of occurrences of a specific criteria.
=COUNTIF (range, criteria)
For example, in the example below, we calculate the number of items sold which had a price of over $100.
ECOMMERCE APPLICATIONS:
COUNTIF is another great way to extract high-level management information from a very large detailed dataset. For example, the total number of sales transactions, transactions with a certain gross margin, etc.
The CONCATENATE function is used to join strings of together.
=CONCATENATE (text1,text2,[text3])
For example, we wish to join the first name and second the name of a person from different cells into one cell; we can use the concatenate function as follows:
CONCATENATE is extremely useful when working with product names and descriptions. For example, using standard meta descriptions on product pages can be a great way to generate them from a product spreadsheet.
These formulas are also very helpful when working with text.
LEFT: Get characters from the left side of a string
RIGHT: Get characters from the right side of a string
MID: Get characters from the middle of a string
The formulas are as following
=LEFT (Cell where the string is located, Number of characters needed from the Left)
=RIGHT (Cell where the string is located, Number of characters needed from the RIGHT)
=MID (Cell where the string is located, Position of the first character, Number of characters)
The screenshots below show the results of using these formulas on Cell A2
Left:
Right:
Mid:
ECOMMERCE APPLICATIONS:
LEFT & RIGHT & MID are also extremely useful when working with product names and descriptions. For example, if you create a product code using the first three letters of a supplier and the product name combined this allows you to automatically generate standard product codes.
Data Validation:
Data validation is a great way to catch simple errors before they occur.
It includes both data list validation where only certain pre-defined data can be used in a spreadsheet and also specifying that only certain types of data can be entered into certain cells.
For example, if you wish to track your inquiries, only specific categories can be entered. It avoids having to combine ‘Chat,’ ‘chat,’ ‘CHAT’ when you are doing reporting as the spreadsheet will only accept ‘Chat’ if you set this up correctly.
Similarly, it is good for catching data errors when working manually. For example, you can use data validation to ensure that only positive numeric numbers can be added to the price column.
For data, validation go to the Data tab and then data validation.
ECOMMERCE APPLICATIONS:
Data validation is beneficial whenever you have people working manually with spreadsheets. Three minutes of work setting up the sheet upfront can save you hours of data cleaning and sorting later.
Remove Duplicate:
Remove Duplicates automatically removes duplicate values from a data set for you.
To access Remove Duplicatesclick Data>Data Tools > Remove Duplicates.
ECOMMERCE APPLICATIONS:
Remove Duplicate is extremely useful in a very wide range of situations. For example, imagine a stock list where you have ordered the same product multiple times. Removing duplicates allows you to see the number of SKUs in stock.
IF statements are extremely helpful when you want to introduce simple logic into an Excel search or function.
The IF function works as follows
=IF(logical_test,[value_if_true],[value_if_false])
logical_test: define the logic you would like Excel to use for the test
value_if_true: the result that Excel should return if the test is true
value_if_false: the result Excel should return if the test or statement is false.
Both of the items in square brackets are optional, and they can be a formula.
In the very simple example below we check that we are not losing money on our sales. If we are, then the result is ‘BAD,’ and if we are not, the results are ‘GOOD.’
=IF(A1>A2, “GOOD”, “BAD”)
where A1>A2 is the case, “GOOD” if the output is true and “BAD” is the output if false.
ECOMMERCE APPLICATIONS
IF statements are a great way to use simple logic and can be used all over the place to check things. For example, if a figure is ahead of or behind budget, if the figure this month is ahead or behind the average of the last three months etc.
In addition, as you become more confident using it, inserting formulas in the ‘true’ and ‘false’ sections can make them very powerful.
XLOOKUP is a new function only available in Microsoft Excel 365.
It works in the same way as VLOOKUP which is very widely used to fetch data associated with a unique value.
Vlookup was limited as it could only look for values vertically. However, with XLOOKUP, we can do lookup both horizontally and vertically.
Let’s look at each of the components of an XLOOKUP formula in turn:
lookup_value: defines the value against which we wish to find results,
lookup_array: defines the array in which we are looking for a value that matches our lookup_value
return_array: defines the array from which we need to fetch results.
The components in [square brackets] are optional for the formula:
if_not_found: what should the formula output if the lookup_valuecan not be found.
match_mode: Where you can define is Excel is looking for an exact match or an approximate match.
search_mode:
ECOMMERCE APPLICATIONS:
This is useful for exactly data from a data table. For example, the price/stock level/size, etc., for a specific product ID would be an obvious example.
Pivot Table are one of the most powerful function available in all versions of excel. It gives the user to create and summarize data into different Reports in seconds. For instance, in the below example, we have Million Rows of data of different fruits and their sales in different countries through our E-commerce platform.
We can summarize the above large data to get different reports such as below to find sales of each fruit.
We can create pivot tables by selecting pivot table from insert Tab.
ECOMMERCE APPLICATIONS:
Pivot Tables are useful to draw conclusions and high-level data out of detailed datasets. They allow you to summarise data in multiple different ways in a matter of seconds.
