VBA / Excel Tutorials

Freezing the rows or columns while working in a long workbook can be really useful. For example, the top row contains the headers of the workbook and while entering data or analyzing it

The MOD function returns the remainder after a number is divided by the divisor. The general syntax is: = MOD(number, divisor)

In Excel, there is no specific function for division. Instead, it is quite simple; use the “/” operator. You may use “forward slash” / for the division in cells directly or in the formula bar as well.

The SUBTOTAL function in Excel is used to return the subtotal in terms of sum, average, count, max, min etc. for the specified list, named range or database.

There are a few ways of getting the Weekday in Excel. if you require the number of day in a Week for the given date then may use then WEEKDAY function. If you require short day name then use the TEXT function

The INDIRECT function is used when you require changing the reference to a cell in a formula while you do not want to change the formula itself.

if you require the count of cells that contain numbers only, use the Excel COUNT function.
To get the count of all cells with numbers, text, dates etc except the blank cells, use the COUNTA function. Similarly, for returning the count of cells based on certain criteriause the COUNTIFS function. To get the count of blank or empty cells, use the COUNTBLANK function.

If you require displaying the date like this 2-Feb-2010 or August 1, 2015 etc. while entering dates you may change the settings in Excel easily.

The Pivot table enables creating another table based on above table and can summarize data for analysis like averages, sum, or other useful reports. With the Microsoft Excel pivot table feature, this is the matter of few clicks

There is no Excel SUBTRACTION function. Instead, you may accomplish the task of subtracting numbers or a cell’s value from the other by using the minus arithmetic operator (-).

The SUMPRODUCT function is used to multiply corresponding arrays or ranges and returns the sum. The SUMPRODUCT function is versatile.

You may simply use the ‘*’ operator e.g. 10*5, A2 * B2 or use the multiple operators in various functions. The PRODUCT function can also be used for multiplication of numbers and ranges.

The Excel TEXT function can be used to convert numbers and dates into text. This is particularly useful for scenarios where you require displaying the numbers or dates with text strings. For example:

There are a numbers of ways in Excel to combine data for two or more cells in Excel. The technique and functions for merging data are:
Using CONCATENATE function
By using ‘&’ (ampersand sign)
TEXTJOIN function for merging ranges (Excel 2016, Excel online)

In this tutorial, I am going to show you how to create checkbox (also called tick box) in the Excel sheet. This is followed by a few examples of using it for different tables, so you can see checkboxes in action.