Excel-Tips

02/08/2025

22/03/2024

Basic overtime calculation formula!!
We can use a set of formulas to calculate the pay associated with workers working overtime. The steps below will guide all levels of excel users through the process.

19/03/2024

The INDEX MATCH[1] Formula is the combination of two functions in Excel: INDEX[2] and MATCH[3]. =INDEX() returns the value of a cell in a table based on the column and row number. =MATCH() returns the position of a cell in a row or column.

28/08/2023

Viewing the Formula in Another Cell in Excel

FormulaText function will show the formula, while the original value stays put, making it easier to understand the formula and its working, while having the value displayed directly.

=FORMULATEXT(cell_reference to a cell containing a formula)

28/08/2023

The Excel function EOMONTH() returns the date of last day in a month. With a little extra, it’ll tell you the number of days in any month.

=EOMONTH(start_date, months)

⏺️ start_date : this is the initial date. Make sure to enter your date using the DATE function to avoid any errors which may incur from entering the date as text.

⏺️months : the number of months before or after start_date. A positive value for months produces a future date; a negative value creates a past date; Zero for the current month.

22/08/2023

How to convert date to text month?

21/08/2023

LOWER, UPPER and PROPER are text functions which let us do the following:

LOWER – Converts all words in the text to lowercase.

UPPER – Converts all words in the text to uppercase.

PROPER – Capitalizes the first letter in each word in the text.

Syntax

=LOWER(text)

=UPPER(text)

=PROPER(text)

20/08/2023

How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.

16/08/2023

Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.

15/08/2023

It is typed =COUNTIFS:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

15/08/2023

The COUNTIFS function is a premade function in Excel, which counts cells in a range based on one or more true or false condition.

14/08/2023

The COUNT function is a premade function in Excel, which counts cells with numbers in a range. It is typed =COUNT. Note: The COUNT function only counts cells with numbers, not cells with letters.