10 Microsoft Excel Formulas to Run eCommerce Business

You are currently viewing 10 Microsoft Excel Formulas to Run eCommerce Business
10 Microsoft Excel Formulas to Run eCommerce Business

10 Microsoft Excel Formulas to Run eCommerce Business

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.

AVERAGEIF:

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

AVERAGEIF - 10 Microsoft Excel Formulas to Run eCommerce Business

 

The formula will be as follows.

AVERAGEIF Function - 10 Microsoft Excel Formulas to Run eCommerce Business

 

The result will be 438.

Note that AVERAGEIF will accept multiple criteria. 

E-COMMERCE APPLICATIONS:

AVERAGEIF is a great way to extract high-level management information from a large, detailed dataset—for example, average selling price, average margin, etc.

SUMIF:

Like AVERAGEIF, the SUMIF function can calculate a SUM based on different criteria.

This function can be used to calculate total sales for each specific item in our e-commerce store.

=SUMIF (range, criteria, [sum_range])

For example, we have region-wise sales data, which is as follows:

SUMIF - 10 Microsoft Excel Formulas to Run eCommerce Business

To find total sales for the East region formula and results will be as

SUMIF function - 10 Microsoft Excel Formulas to Run eCommerce Business

E-COMMERCE APPLICATIONS:

SUMIF is another great way to extract high-level management information from a large, detailed dataset—for example, total sales, gross margin, etc.

COUNTIF:

The COUNTIF function is similar to AVERAGEIF and SUMIF but counts the number of occurrences of specific criteria.

=COUNTIF (range, criteria)

For example, in the example below, we calculate the number of items sold for over $100.

COUNTIF - 10 Microsoft Excel Formulas to Run eCommerce Business

E-COMMERCE APPLICATIONS:

COUNTIF is another great way to extract high-level management information from a large, detailed dataset—for example, the total number of sales transactions, transactions with a certain gross margin, etc.

CONCATENATE:

The CONCATENATE function is used to join strings together.

=CONCATENATE (text1,text2,[text3])

For example, if we wish to join the first name and second 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, standard meta descriptions on product pages can be a great way to generate them from a product spreadsheet.

LEFT AND RIGHT AND MID:

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 follows

=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:

CONCATENATE

Right:

CONCATENATE Function

 

Mid:

CONCATENATE formula

 

E-COMMERCE APPLICATIONS:

LEFT & RIGHT & MID are also extremely useful when working with product names and descriptions. For example, creating a product code using the first three letters of a supplier and the product name combined allows you to generate standard product codes automatically.

Data Validation:

Data validation is a great way to catch simple errors before they occur.

It includes data list validation, where only certain pre-defined data can be used in a spreadsheet, and specifies that only certain types of data can be entered into certain cells.

For example, only specific categories can be entered if you wish to track your inquiries. It avoids having to combine ‘Chat,’ ‘Chat,’ and ‘CHAT’ when you are 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.

data validation excel

E-COMMERCE 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 Duplicates, click Data>Data Tools > Remove Duplicates.

Remove Duplicates excel

 

E-COMMERCE APPLICATIONS:

Removing duplicates 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:

IF statements are extremely helpful when introducing 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 simple example below, we check that we are not losing money on our sales. If we are, 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” is the output that is true, and “BAD” is the output if it is false.

 

IF Statements excel

E-COMMERCE APPLICATIONS

Suppose statements are a great way to use simple logic and can be used everywhere to check things. For example, if a figure is ahead of or behind budget, this month’s figure 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 (VLOOKUP):

XLOOKUP is a new function that is only available in Microsoft Excel 365.

It works like VLOOKUP and is widely used to fetch data 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.

xlookup excel

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 be if the lookup_value cannot be found?

match_mode: This is where you can define if Excel is looking for an exact or approximate match.

search_mode:

E-COMMERCE APPLICATIONS:

This is useful for exact data from a data table. For example, the price/stock level/size, etc., for a specific product ID would be an obvious example.

Pivot Tables:

Pivot Tables are one of the most powerful functions available in all versions of Excel. It allows users to create and summarize data into different Reports in seconds. For instance, in the example below, we have a million rows of data on fruits and their sales in different countries through our e-commerce platform.

pivot tables excel

We can summarize the above large data to get different reports and find sales for each fruit.

pivot table excel

We can create pivot tables by selecting them from the Insert tab.

E-COMMERCE APPLICATIONS:

Pivot Tables are useful for drawing conclusions and high-level data from detailed datasets. They allow you to summarise data in multiple different ways in a matter of seconds.

Follow us on LinkedIn – Build, Grow, Convert.

eCommerce FAQs

Passionate advocate for digital inclusivity, leading the charge at Understanding eCommerce to provide web accessibility solutions for businesses and organizations. Committed to making the online world accessible to all.