100 Excel Formulas To Supercharge Your Spreadsheets

Excel Basics: The Difference Between a Function and a Formula

In Excel, a formula and a function are often used interchangeably, but they have distinct differences. 

• A formula is a set of mathematical instructions that are used to perform calculations in a cell. It uses standard arithmetic symbols and can include variables and references to other cells. 
  For example: =B1+C1+D1
• A function, on the other hand, is a pre-built formula that is used to perform a specific task, such as calculating the sum or average of a range of cells. Functions can be used in formulas to simplify calculations but cannot be edited or modified. 
  For example =SUM(A1:D1)

Time and Date Formulas

Time and date formulas in Excel provide powerful tools for manipulating, analyzing, and formatting time-related data. They help streamline workflows, improve accuracy, and save time when working with time and date values in spreadsheets.

• Time calculations: Excel's time and date formulas enable you to calculate dates and times, such as adding or subtracting days, months, years, hours, minutes, or seconds. For example, you can determine the number of days between two dates, calculate the age based on the birthdate, find the time difference between two events, calculate the number of absence days, or calculate the number of hours worked between two-time intervals, usually known as timesheet calculations.
• Date formatting: Excel provides numerous formatting options for time and date values. For example, you can customize the display format to suit your needs, such as displaying dates in different formats (e.g., mm/dd/yyyy, dd-mm-yyyy) or showing times in various ways (e.g., 12-hour or 24-hour format). Formulas help you manipulate and format these values according to your preferences.

=NOW Show the date and time

=TODAY() Show the current date without the time

=DAY(TODAY()) Show today's date in a cell

=MONTH(TODAY()) Shows the current month in a cell

=TODAY()+7 Add seven days to the current date.

=TIME() Create a time with hours, minutes, and seconds

=HOUR() Shows the hour from a time value as a number from 0 to 23.

=MINUTE() Shows the minute from a time value as a number from 0 to 59.

=SECOND() Shows the minute from a time value as a number from 0 to 59.

=DATEDIF() Shows the difference between two dates in terms of years, months, or days.

=NETWORKDAYS Calculates the number of working days between two dates

=WEEKNUM Shows the given week number for a given date

=ISOWEEKNUM Shows the given week number for a given date in ISO Format

=text(E2;"mm/dd/yyyy") converts The E2 date format to MM/DD/YYYY.

Counting and Rounding Formulas

Counting and rounding formulas in Excel provide a powerful way to analyze data, summarize information, and present results in a more meaningful way. They streamline calculations, enhance data accuracy, and facilitate decision-making processes by providing valuable insights.

• Counting: Excel provides several counting formulas that allow you to count cells that meet specific criteria or conditions. These formulas are useful when you want to determine the frequency of occurrences or track the number of items that meet specific criteria within your data. For example, how many employees are part of the HR team, or how many vacations have been booked in May?
• Rounding: Rounding formulas in Excel allows you to adjust the precision of numerical values. They help you present data in a more readable and user-friendly format or perform calculations with rounded values. Rounding formulas are beneficial when dealing with financial data and measurements or when you need to present results more concisely. They can also ensure consistency in calculations or when working with data requiring specific rounding rules. For example, you would want a person's age rounded to 41 years, not, let's say, 41,74.

=SUM Calculates the sum of a group of variables

=AVERAGE Calculates the mean of a group of values

=ROUND Rounds a number to a specified number of decimal places

=ROUND(1.15, 1) Rounds 1.15 to one decimal place

=ROUND (-3.15,3) Rounds -3.15 to 3 decimal places

=ROUNDUP Rounds a number up to the nearest specified multiple.

=ROUNDDOWN Rounds a number down to the nearest specified multiple.

=IF Tests for a true or false condition

=COUNT Counts the number of cells in a range that contains numbers

=INT Removes the decimal portion of a number

=COUNTA(B1:B10) Count the number of non-blank cells in a range

=TRUE Returns the logical value TRUE

=FALSE Returns the logical value FALSE

=SUMIF Calculates a sum from a group of values in which a condition has been met

=COUNTIF Count the number of cells that meet a criterion

=AND Returns TRUE if all its arguments are TRUE

=OR returns TRUE if any argument is TRUE

Unit Conversion Formulas

Unit conversion formulas in Excel simplify and automate the process of converting values between different units of measurement. They ensure accuracy, consistency, and flexibility in data entry, analysis, and reporting, saving time and reducing the risk of errors. Converting all measurements to a consistent unit allows you to perform calculations, comparisons, and visualizations more effectively. 

• The CONVERT function provides the flexibility to convert between various units, including length, area, volume, time, temperature, weight, and more, based on your specific needs. This helps maintain data integrity and ensures that conversions are performed correctly and consistently across the spreadsheet. Additionally, this ensures that data from different sources or with different units can be easily integrated and analyzed together. Collaboration with people from different regions is enhanced as conversion formulas ensure standardization across different measurement standards.

=CONVERT (B1," DAY"," HR") Converts the value of B1 from days to hours

=CONVERT (B1," YR"," DAY") Converts the value of B1 from years to days

=CONVERT (B1," MI"," KM") Converts the value of B1 from miles to kilometers

=CONVERT (B1," KM"," MI") Converts the value of B1 from kilometers to miles

=CONVERT (B1," IN"," FT") Converts the value of B1 from inches to feet

=CONVERT (B1," C"," F") Converts the value of B1 from Celsius to Fahrenheit

=CONVERT (B1," TSP"," TBS") Converts the value of B1 from teaspoons to tablespoons

=CONVERT (B1," CM"," IN") Converts the value of B1 from centimeters to inches

=BIN2DEC (1100100") Converts binary 1100100 to decimal 100

=ROMAN converts a number into a Roman numeral

=B1 – B2 Subtracts values from the two cells 

=B1*A1 = Multiplies the numbers into the two cells

=PRODUCT(B1:B10) multiplies the cells in the range

=PRODUCT(B1:B10,2) multiplies the cells in the range and multiplies the result by 2

=B1/B2 Divides the value of A1 by the value of A3

=MOD Returns the remainder from the division

=MIN(B1:B10) Calculates the smallest number in a range

=ODD Subtracts values in two cells

=AVERAGE(B1:B10) Returns the average of those numbers. 

=MEDIAN(B1,B2,B3)  Returns the median of numeric values. 

=SQRT It will provide the square root of a positive number.

=PI Returns pi.

=POWER returns a number raised to a given power.

=POWER (7,2) Calculates 7 squared

= RAND Returns a random number between 0 and 1

=RANDBETWEEN Get a random integer between two values

=MAX (B10:B22) Calculates the largest number in a range

=SMALL(B3:B6, 1) Calculates the smallest number in a range

=LARGE(C1:C100, 2) Calculates the second largest number in a range

=7^3 Calculates seven cubed

=FACT(B1) Factorial value of B1

=EVEN Rounds a number up to the next even integer

=COS Get the cosine of an angle provided in radians.

=SIN Calculates the sine of an angle provided in radians.

=TAN Calculates the tangent of a given angle

=CORREL Calculates the correlation coefficient between two data ranges

=PROB Returns the probability that values in a range are between two limits

Text Formatting & Manipulation Formulas

Excel's formatting and manipulation formulas provide potent tools for formatting, cleaning, and manipulating text data within your spreadsheets. They enhance data consistency, improve data quality, facilitate data analysis, and streamline workflows by automating text-related tasks.

• Formatting: Excel provides text formatting formulas that allow you to modify the appearance of text based on specific criteria. These formulas help you apply consistent formatting across a range of cells, such as changing the case of the text (uppercase, lowercase, or proper case), adding leading or trailing spaces, or applying specific formats to text values (e.g., date, currency, percentage).
• Cleansing: Text manipulation formulas in Excel are useful for cleaning and standardizing text data. You can remove unwanted characters, trim extra spaces, extract specific portions of text, or replace certain text patterns with desired values.
• Concatenation: Excel's text manipulation formulas allow you to combine text from different cells or strings into a single cell. This process, known as concatenation, is useful for creating customized labels, joining text values with separators, or generating unique identifiers based on multiple text inputs.
• Parsing: Text manipulation formulas enable you to extract specific information or parse text into separate components. For example, you can extract the first or last name from a full name, separate a text string based on a delimiter (e.g., comma, space), or extract portions of a text value that match a specific pattern (using regular expressions).

=LEFT Extracts one or more characters from the left side of a text string

=RIGHT Extracts one or more characters from the right side of a text string

=MID Extracts characters from the middle of a text string

=CONCATENATE Merges two or more text strings

=REPLACE Replaces part of the text string

=VALUE converts a text cell to a number

=LOWER Converts a string to all lowercase

=UPPER Converts a text string to all uppercase

=PROPER Converts a text string to the proper case

=LEN Returns a text string's length in characters

=REPT Repeats text a given number of times

=DOLLAR Converts a number to text using the USD currency format

=CLEAN Removes all non-printable characters from text.

Finance formulas

Finance formulas in Excel provide powerful tools for financial analysis, modeling, planning, and reporting. They help in decision-making, risk assessment, valuation, and financial statement analysis. By leveraging these formulas, you can streamline financial calculations, improve accuracy, and gain valuable insights into your financial data.

=INTRATE Calculates the interest rate for a fully invested security

=EFFECT Calculates the effective annual interest rate

=FV Calculates the future value of an investment

=FVSCHEDULE Calculates the future value of an initial principal after applying a series of compound interest rates

=PMT Calculates the total payment (debt and interest) on a debt security

=IPMT Calculates the interest payment for an investment for a given period

=ACCRINT Calculates the accrued interest for a security that pays periodic interest

=ACCRINTM Calculates the accrued interest for a security that pays interest at maturity

=AMORLINK Calculates the depreciation for each accounting period

=NVP Calculates the net present value of cash flows based on a discount rate

=YIELD Calculates the yield of a security based on maturity, face value, and interest rate

=PRICE Calculates the price per 100 face value of a periodic coupon bond

Here are the top 10 best practices with Excel formulas:

1.    Start with an equal sign: All Excel formulas begin with an equal sign (=), which tells Excel that you're entering a formula instead of plain text.

2.    Use cell references: Instead of typing values directly into your formulas, use cell references (e.g., A1, B2) to make your formulas dynamic and easy to update.

3.    Keep formulas simple: Break complex calculations into smaller, more manageable parts to make them easier to understand and troubleshoot.

4.    Use parentheses to control the order of operations: Excel follows a specific order of operations abbreviated PEMDAS- Parentheses, Exponents, Multiplication, Division, Addition, Subtraction. To ensure your formulas calculate correctly, use parentheses to dictate the order in which calculations should be performed.

5.    Leverage built-in functions: Excel has many built-in functions (like SUM, AVERAGE, and VLOOKUP) that can help you easily perform complex calculations. Familiarize yourself with these functions to save time and improve accuracy.

6.    Copy formulas with caution: Be aware of relative and absolute cell references when copying and pasting formulas. To lock a cell reference and make it absolute, use the dollar sign ($) before the column and row (e.g., $A$1).

7.    Keep data and formulas separate: Organize your spreadsheet by keeping raw data, calculations, and final results in separate areas or sheets. This will make your workbook easier to navigate and maintain.

8.    Document your formulas: Add comments or notes to your formulas to explain their purpose and calculations, making it easier for you (or others) to understand or modify them in the future.

9.    Error-proof your formulas: Use error-handling functions like IFERROR or ISERROR to prevent errors from breaking your calculations or displaying confusing results.

10.    Test and validate: Always double-check your formulas for accuracy and test them with various inputs. This will help you catch errors early and ensure your results are reliable.

By following these formulas and best practices, you'll be well on your way to mastering Microsoft Excel basics and creating efficient, accurate spreadsheets.