top of page
  • Writer's pictureJoanne @ Paramount

Useful Formulae for your Excel Spreadsheets

We often get asked ‘is there a list somewhere of formulas?’. Well, the answer is that if you check the FX button at the start of the formula bar in Excel, you can access every single formula possible. However, we guess that probably 95% of them we will probably never use as they are fairly niche. Do any of these sound useful for your data…..?!

BINOMDIST – returns the individual term binomial distribution probability

CHISQDIST – returns the left-tailed probability of the chi-squared distribution

HARMEAN – returns the harmonic mean of a data set of positive numbers: the reciprocal of the arithmetic mean of reciprocals

Well, exactly!

So, we’ve put together a list of a few formulas we often use along with a note of how you could use them. Let us know if there are any other favourites you use!



Possible uses


Returns the average of a range of numbers

​Average sales, average salary, average cost


Return the largest number in a range. Returns the latest date in a range

Find out the last date a customer ordered from you, last staff appraisal date. Largest salary. Largest order value


Return the smallest number in a range. Returns the earliest date in a range

Find out the first date a customer ordered from you, first staff appraisal date. Lowest salary. Smallest order value


Counts how many cells contain a number

How many orders have been placed


​Counts how many cells contain a value ie text or numbers

Count a list of names


​Find a value from the main table in another table

Find the salesperson’s name from a separate list. Mileage claims eg a journey to London is always 75 miles


Evaluates a rule and returns one of two outcomes

Stock control – flag up when the minimum stock level has been reached


​Counts how many items in a list that have a specific value

eg. number of people who work in a specific department


​How many cells are empty

​Check for missing data


Returns the total (sum) of the cells which meet a specific criteria

​What are the total salaries for a specific department


Joins two or more text cells into a new cell

​Combine first and surnames into a new cell


Returns the first/first two/first three etc characters from a text string

Use the results to define what type of product/customer/dept the code relates to


Returns the current date (automatically updates)

Working out how long since/until a date


Breaks down the selected date to extract the month/day/year

Identify all staff who joined in a specific year or month


What is the week number we are currently in


​What is the current date and time (auto updates)

​Working out how long since/until a date/time


How many working days between two dates

How many more days do you have before order delivery


What is the day of the week for the specified date

TIP: Use this with a vlookup to return the day in a word format rather than a number eg Tuesday rather than 2


What will the date be in x number of days’ time

How many more working days do we have before delivery


Remove excess spaces from a text string

Useful in conjunction with a vlookup


​Change the case of text

​To tidy up your data

Recent Posts

See All


bottom of page