# How To

## A collection of 341 posts

How to Split Each Excel Sheet Into a Separate File Using Python
13 April 2022
How to perform email automation using python,excel spreadsheet and gmail
10 March 2022
This example teaches you how to calculate the tax on an income using the VLOOKUP function in Excel. The following tax rates apply to individuals who are residents of Australia. Taxable income Tax
06 November 2020
This example shows you how to solve a system of linear equations in Excel. For example, we have the following system of linear equations: 5x + 1y + 8z = 46 4x - 2y = 12 6x
06 November 2020
To calculate a weighted average in Excel, simply use the SUMPRODUCT and the SUM function. 1. The AVERAGE function below calculates the normal average of three scores. Suppose your teacher says, “The
06 November 2020
Learn how to get the day of the week of a date in Excel and how to get the number of weekdays or workdays days between two dates. Weekday function 1. The WEEKDAY
06 November 2020
The VLOOKUP function is one of the most popular functions in Excel. This page contains many easy to follow VLOOKUP examples. Exact Match Most of the time you are looking for an exact
06 November 2020
This example teaches you how to lookup a value in a two-dimensional range. We use the INDEX and the MATCH function in Excel. Below you can find the sales of different ice cream
06 November 2020
To enter today’s date in Excel, use the TODAY function. To enter the current date and time, use the NOW function. To enter the current date and time as a static
06 November 2020
This example teaches you how to create a simple timesheet calculator in Excel. Cells that contain formulas are colored light yellow. If you are in a hurry, simply download the Excel file. 1.
06 November 2020
Calculating the difference between two times in Excel can be tricky. Times are handled internally as numbers between 0 and 1. Simple Formula Let’s start with a simple formula that calculates
06 November 2020
To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’. For example, when you want to separate a list of full
06 November 2020
When joining text and a number, use the TEXT function in Excel to format that number. This page contains many easy to follow TEXT function examples. 1a. For example, without using the TEXT
06 November 2020
This example teaches you how to use the SWITCH function in Excel 2016 or later instead of the IFS function. 1a. For example, the IFS function below finds the correct states. Explanation: cell
06 November 2020
Summing with Or criteria in Excel can be tricky. This article shows several easy to follow examples. 1. We start simple. For example, we want to sum the cells that meet the following
06 November 2020
This example shows you how to create an array formula that sums a range with errors. You can also use the AGGREGATE function to sum a range with errors. 1. We use the
06 November 2020
This example shows you how to create an array formula that sums the largest numbers in a range. 1. We use the LARGE function. For example, to find the second largest number, use
06 November 2020
This example shows you how to create an array formula that sums every nth row in Excel. We will show it for n = 3, but you can do this for any number. 1.
06 November 2020
This example shows the difference between the SUBSTITUTE function and the REPLACE function. 1a. If you know the text to be replaced, use the SUBSTITUTE function. 1b. The SUBSTITUTE function has a 4th
06 November 2020
There’s no SUBSTRING function in Excel. Use the MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM and the MAX function in Excel to extract substrings. Mid To extract a substring, starting
06 November 2020
This example teaches you how to use the PERCENTILE and QUARTILE function in Excel. Below you can find a list of scores (green fill for illustration only). 1. Use the PERCENTILE function shown
04 November 2020
This page explains how to calculate the standard deviation based on the entire population using the STDEV.P function in Excel and how to estimate the standard deviation based on a sample using
04 November 2020
Use the SEARCH function in Excel to find the position of a substring in a string. The SEARCH function is case-insensitive and supports wildcards. 1. The SEARCH function below finds the string “
04 November 2020
The PMT function in Excel calculates the payment for a loan based on constant payments and a constant interest rate. This page contains many easy to follow PMT examples. PMT examples Consider a
04 November 2020
The percent change formula is used very often in Excel. For example, to calculate the Monthly Change and Total Change. 1a. Select cell C3 and enter the formula shown below. 1b. Select cell
04 November 2020
The OR function in Excel returns TRUE if any of the conditions are true and returns FALSE if all conditions are false. Combine the OR function with other functions and become an Excel
04 November 2020
The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. 1. The OFFSET function below
04 November 2020
An easy formula that returns the quarter for a given date. There’s no built-in function in Excel that can do this. 1. Enter the formula shown below. Explanation: ROUNDUP(x,0)
04 November 2020
This example teaches you how to separate strings in Excel. The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of
04 November 2020
Excel has two useful functions when it comes to generating random numbers. The RAND and RANDBETWEEN function. Rand The RAND function generates a random decimal number between 0 and 1. 1. Select cell
04 November 2020
This example teaches you how to simulate the roll of two dice in Excel. If you are in a hurry, simply download the Excel file. Note: the instructions below do not teach you
04 November 2020
This example describes how to count the number of words in a cell. 1a. The TRIM function returns a string with leading spaces, extra spaces and trailing spaces removed. 1b. To get the
04 November 2020
The RANK function in Excel returns the rank of a number in a list of numbers. Use RANK.AVG to return the average rank if more than one number has the same rank.
04 November 2020
This example describes how to count the number of instances of text (or a number) in a cell. 1. Use the LEN functon to get the length of the string (25 characters, including
01 November 2020
The correct NPV formula in Excel uses the NPV function to calculate the present value of a series of future cash flows and subtracts the initial investment. Net Present Value For example, project
01 November 2020
A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged. Below you can find two columns with numbers. To subtract the numbers in column B from the numbers
01 November 2020
Use the IRR function in Excel to calculate a project’s internal rate of return. The internal rate of return is the discount rate that makes the net present value equal to
31 October 2020
The IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples. Simple If Examples The IF function checks whether a condition is met,
31 October 2020
This example illustrates three functions to round numbers to a multiple of x in Excel. The MROUND, CEILING and the FLOOR function. Mround 1. For example, round a number to the nearest multiple
31 October 2020
Use MAXIFS and MINIFS in Excel 2016 or later to find the maximum and minimum value based on one criteria or multiple criteria. 1. For example, the MAXIFS function below finds the highest
31 October 2020
This example teaches you how to convert a text string to lower, upper or proper case in Excel. 1. Use the LOWER function to convert all letters in a text string to lowercase.
31 October 2020
This example teaches you how to find the cell address of the maximum value in a column. 1. First, we use the MAX function to find the maximum value in column A. 2.
31 October 2020
This example teaches you how to compare loans with different durations in Excel. 1. First, we calculate the monthly payment on a loan with an annual interest rate of 6%, a 20-year duration
31 October 2020
The VLOOKUP function only looks to the right. To look up a value in any column and return the corresponding value to the left, simply use the INDEX and the MATCH function. 1.
31 October 2020
This example illustrates the ISERROR function in Excel. 1. For example, Excel displays the #DIV/0! error when a formula tries to divide a number by 0. 2. The ISERROR function in Excel
31 October 2020
This example teaches you how to calculate the future value of an investment or the present value of an annuity. Tip: when working with financial functions in Excel, always ask yourself the question,
31 October 2020
Use the INDIRECT function in Excel to convert a text string into a valid reference. You can use the & operator to create text strings. Cell Reference Use the INDIRECT function in Excel
31 October 2020
Use the IFERROR function in Excel to return an alternative result, such as text, when a formula evaluates to an error. 1. For example, Excel returns the #DIV/0! error when a formula
31 October 2020
Use the IF function and an empty string (“”) in Excel to check if a cell is blank. You can also use the IF function and the ISBLANK function. If Blank Remember,
31 October 2020
To get the date of the last day of the month in Excel, use the EOMONTH (End of Month) function. 1. For example, get the date of the last day of the current
31 October 2020