You are reading the article Excel Formulas Cheat Sheet (Examples) updated in December 2023 on the website Cattuongwedding.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Excel Formulas Cheat Sheet (Examples)
Excel Formulas Cheat SheetThe cheat sheet of Excel formulas is like a customized worksheet that shows you how to use different functions and formulas in Excel. It includes shortcuts to quickly execute Excel functions and instructions on combining multiple functions in your way.
It can also include more complicated formulas that may be difficult to remember or use. Overall, an Excel Formulas Cheat Sheet is a handy tool for anyone who wants to improve using Excel.
Calculations Used in the Cheat Sheet of Excel FormulasThis article will cover Excel formulas that are most frequently used in calculations.
Let’s take some examples to understand the uses and workings of these functions.
You can download this Cheat Sheet Excel Formulas Template here – Cheat Sheet Excel Formulas Template
TEXT Functions in ExcelTEXT functions in Excel allow you to manage and format text within a cell and help perform various tasks, including converting dates to text, extracting specific characters from a string, and more.
To access this function, follow the below steps:
Go to the FORMULAS tab.
Refer to the below screenshot.
As we can see here, several string functions are available. The most commonly used text functions are RIGHT, LEFT, MID, CONCATENATE, LOWER, LEN, etc.
Example #1We will understand how to apply the following text functions.
LEFT
RIGHT
MID
CONCATENATE
LEN
LOWER
We will use the following data to apply the text function:
Please refer to the following table for the result of text functions in Excel.
Sr. No.
Name
TEXT Function
Method of Function
Result
Description
1 Samuel Martin LEFT =LEFT(A4,3) Sam It counts the string from the left. It returns the specified no. of characters.
2 Ronica Brave Joyce RIGHT =RIGHT(A5,5) Joyce
It counts the string from the right and returns the specified no. of characters.
3 Phillip Studer MID =MID(A6,5,9) lip Stude It counts the string from starting position and returns the no. of characters. Space also counts as a character.
4 Ian Smith CONCAT =CONCATENATE(A7,A6) Ian SmithPhillip Studer It merges the strings mentioned and forms them as one string. Here we have passed A7 & A6 as an argument. If one needs to add space between the contents of A7 & A6, include (” “), wherever necessary i.e., =CONCATENATE(A7,” “,A6).
Ian Smith Phillip Studer
5 Fedrick Rodger LEN =LEN(A8) 14 It counts the no. of characters and returns the length of the string.
6 Petrick Henderson LOWER =LOWER(A9) Petrick Henderson It converts the string into lowercase.
STATISTICAL Functions in ExcelStatistical functions in Excel allow you to perform statistical analysis on data, like calculating measures of central tendency, variability, correlation, regression, and more.
To access this function, follow the below steps:
Go to the FORMULA tab.
Choose the Statistical Functions category.
It will open a drop-down list of functions.
The most commonly used statistical functions are MIN, MAX, COUNT, AVERAGE, MEDIAN, etc.
Example #2We will understand how to apply the following statistical functions.
MAX
MIN
AVERAGE
COUNT
MEDIAN
We will use the following data for calculations:
Please refer to the table below to note the statistical function result in Excel.
Sr. No.
Number Values
Max
Min
Average
Count
Median
1 34
=MAX(A4:A8)
=MIN(A4:A8)
=AVERAGE(A4:A8)
=COUNT(A4:A8)
=MEDIAN(A4:A8)
2 56 60 10 41 5 45
3 10 It returns the maximum value from the list. It returns the lower value from the list It returns the average of the list of values Counts the values in a list. It first arranges the values in increasing order and then returns the mid value. If the number of values is odd, it returns the mid value. If the chúng tôi values is even, then it takes the average of mid-two values.
DATE & TIME Functions in ExcelDate and time functions in Excel allow you to manage and format dates and times in cells. These functions can be helpful for various tasks, such as calculating the difference between two dates, extracting the month or day from a date, adding or subtracting time from a given date, and more.
To access this function, follow the below steps:
Go to the FORMULAS tab.
It will open a drop-down list of functions, as shown below.
Let’s understand this with some examples in the below screenshot.
Example #3We will understand how to apply the following date and time functions.
DATE
NOW
TODAY
WEEKDAY
TIME
Please refer to the below table to understand how to use date and time functions in Excel.
Sr. No.
Function
Syntax
Example
Result
Explanation
1 DATE DATE(year,month,day) =DATE(1996,4,24)
4/24/1996
This function returns the serial number of a date.
2 NOW NOW() =NOW()
4/24/2023 10:12
This function returns the current date and time.
3 TODAY TODAY() =TODAY()
4/24/2023
This function returns the current date as formatted.
4 WEEKDAY WEEKDAY(serial_no) =WEEKDAY(D6)
2
This function returns the day of the week.
5 TIME TIME(hour,minute, second) =TIME(1,15,60)
1:16 AM
It converts the hour, minute, and second to an Excel serial number in time format.
MATHEMATICAL Functions in ExcelMathematical functions in Excel enable you to perform mathematical operations on data and carry out basic arithmetic operations like subtraction, addition, multiplication, and division and more complex operations like trigonometry, logarithms, and exponentials.
To access this function, follow the below steps:
Go to the FORMULAS tab.
It will open a drop-down list of functions, per the screenshot below.
We will understand the working of this function with some examples.
Example #4We will understand how to apply the following mathematical functions.
SUM
PRODUCT
SUBTOTAL
RANDBETWEEN
SQRT
We will use the following data for calculations:
Given below is the application of the respective functions.
Sr. No.
Number
SUM
PRODUCT
SUBTOTAL
RANDBETWEEN
SQRT
1 11 =SUM(A4:A8) =PRODUCT(A4:A8) =SUBTOTAL(4,A4:A8) =RANDBETWEEN(23,38) =SQRT(A4)
2 12 65 360360 15 25 3.31662479
3 13 It returns the sum of all number values passed as an argument
It multiplies all the number values passed as an argument in the function It returns the subtotal in a list.
It returns the random number between the passed argument values.
It returns the square root of a number.
4 14 Here, we have passed the number values range A4:A8 as an argument. Here we have passed the number values range A4:A8 as an argument. Here we have passed the number values range A4:A8 as the second argument and want to see the maximum number value out of these, which comes under no. 4 passed as the first argument of this function.
Here we have passed two values, 23 as the bottom value and 38 as the top value. This function returns any random value between them.
Here we have passed A4 values as an argument. If we select the entire range, it will provide the square roots of every number.
5 15
Additional Excel Formulas for Daily UseFormula
Data in Excel Cells
Example
Result
AVERAGE A10: 4
=AVERAGE(A2:A10)
This formula will calculate the average values in cells A2 to A10.
6
MAX A10: 4 =MAX(A2:A10)
This formula will return the highest value in cells A2 to A10.
10
MIN A10: 4 =MIN(A2:A10)
This formula will return the lowest value in cells A2 to A10.
2
COUNT A10: 4 =COUNT(A2:A10)
This formula will count the number of cells in cells A2 to A10 that contain numbers.
7
COUNTIF A5: 5
This formula will allow us to count the number of cells in the range A2:A5 greater than 7.
1
COUNTBLANK A10: 4 =COUNTBLANK(A2:A10)
This formula will help to count the number of blank cells in cells A2 to A10.
0
IF A2: 6
This formula will return “Yes” if the value in cell A2 exceeds 8 and “No” otherwise.
No
SUMIF B9: 6
This formula helps calculate the sum of values in cells B2 to B10, where the corresponding values in column A are more significant than 5.
22
VLOOKUP B4: 89 =VLOOKUP(“Paper”, A2:B4, 2, FALSE)
This formula will search for the value “Paper” in the first column (in table A2:B4) and return the value in the second column.
21
Things to Remember About Excel Formulas Cheat Sheet
And choose the function as per the requirement from the list.
Frequently used shortcut keys are:
CTRL+Z – Undo, etc.
Frequently Asked QuestionsAnswer: Excel has many valuable formulas, but some of the most common, helpful, and basic ones are:
SUM: Adds the value in several cells together.
AVERAGE: This function computes the average of a set of cells.
MAX: Finds the maximum value in a cell range.
MIN: Finds the minimum value in a cell range.
COUNT: Counts the number of cells containing numbers in a particular range.
COUNTA: Counts the number of cells other than blank ones in a range of cells.
IF: Tests a particular condition and returns one value if the given condition proves true and another if it turns out false.
ROUND: A number rounds to the specified number of digits.
CONCATENATE: Combines two or more strings of text or otherwise into a single string.
VLOOKUP: Finds a value in the first column of a table and returns a value from another column in the same row.
Answer: To merge cells in Excel, follow these steps:
Select the cells you need to merge. These cells should be adjacent and not contain any data you want to keep.
Your cells should now merge into one cell. If there were any text in the original cells, it would appear centered in the new merged cell.
Answer: You can use many keyboard shortcuts in Excel to save time and increase productivity. Below are some useful shortcuts that use the Ctrl key:
Ctrl + Y: Redo the last undone action.
Ctrl + A: Select all cells in a worksheet.
Ctrl + S: Save the current workbook.
Ctrl + N: Open a new workbook.
Ctrl + -: Delete the selected cells, rows, or columns.
Ctrl + Shift + =: Insert a new column or row into the worksheet.
Ctrl + Shift + $: Apply currency formatting to the selected cells.
Ctrl + Shift + #: Apply date formatting to the selected cells.
Ctrl + Shift + &: Apply border formatting to the selected cells.
Ctrl + Shift + ~: Apply general number formatting to the selected cells.
Recommended ArticlesThis has been a guide to Excel Formulas Cheat Sheets. Here we discuss the TEXT, STATISTICAL, DATE & TIME, and MATHEMATICAL Functions in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles.
You're reading Excel Formulas Cheat Sheet (Examples)
Int In Excel (Formula, Examples)
INT in Excel
INT in Excel is a very simple function used to convert any number into an integer value. Integer values are any number that is a whole number but can be a positive or negative number. Int function can consider any number, whether it is a decimal, fraction, or square root value, but in the end, we will be getting a whole number out of it.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
INT Formula in Excel:
Below is the INT Formula in Excel.
where
How to Use INT Function in Excel?INT function in Excel is very simple and easy to use. Let us understand the working of the INT function in Excel by some INT Formula examples. INT function can be used as a worksheet function and VBA function.
You can download this INT Function Excel Template here – INT Function Excel Template
Example #1The below-mentioned table contains a value in cell “C8”, i.e. 6.79, which is a positive number; I need to find out the nearest integer for 6.79 using the INT function in Excel.
Select the cell “E8,” where the INT function needs to be applied.
A dialog box appears where arguments (number) for the INT function need to be filled or entered.
i.e. =INT(C8).
It removes the decimal from the number and returns the integer part of the number, i.e. 6.
Example #2The below-mentioned table contains a value in cell “C14”, i.e. -5.89, which is a negative number; I need to find out the nearest integer for -5.89 using the INT function in Excel. Select cell E14, where the INT function needs to be applied.
A dialog box appears where arguments (number) for the INT function need to be filled or entered.
i.e. =INT(C14)
It removes the decimal from the number and returns the integer part of the number, i.e. -6.
Example #3In the below mention example, I have the date of birth ( 16th May 1982) in cell “J8” I need to calculate the age in cell “L8” using the INT function in Excel.
Before the INT function in Excel, let’s know about the YEARFRAC function; the YEARFRAC function returns a decimal value representing fractional years between two dates. I.e. Syntax is =YEARFRAC (start_date, end_date, [basis]). It returns the number of days between 2 dates as a year.
Here the INT function is integrated with the YEARFRAC function in cell “L8”.
YEARFRAC formula takes the date of birth and the current date (given by the TODAY function) and returns the output value as age in years.
i.e. =INT(YEARFRAC(J8,TODAY()))
It returns the output value i.e. 36 years.
Example #4Usually, Excel stores the date value as a number, considering the date as an integer and the time as a decimal portion. If a cell contains a date and time as a combined value, you can only extract the date value using the INT function in Excel. Cell “P8” contains the date and time as a combined value. Here I need to extract the date value in cell “R8.”
Select the cell R8 where the INT function needs to be applied.
i.e. =INT(P8)
It removes a decimal portion from the date & time value and returns only the date portion as a number, where we need to discard the fraction value by formatting in the output value.
i.e. 11/12/18
Example #5The below-mentioned table contains a value less than 1 in cell “H13”, i.e. 0.70, which is a positive number; I need to find out the nearest integer for decimal value, i.e. 0.70, using the INT function in Excel. Select cell I13 where the INT function needs to be applied.
A dialog box appears where arguments (number) for the INT function need to be filled or entered.
i.e. =INT(H13)
Here, it removes the decimal from the number and returns the integer part of the number, i.e. 0
Things to Remember
In the INT function, Positive numbers are rounded toward 0, while negative numbers are rounded away from 0. E.G. =INT(2.5) returns 2 and =INT(-2.5) returns -3.
Both INT() and TRUNC() functions are similar when applied to positive numbers; both can convert a value to its integer portion.
If any wrong type of argument is entered in the function’s syntax, it results in #VALUE! Error.
If the referred cell is not a valid or invalid reference in the INT function, it will return or result in #REF! Error.
#NAME? Error occurs when Excel does not recognize specific text in the formula of the INT function.
Recommended ArticlesThis has been a guide to INT Function in Excel. Here we discuss the INT Formula in Excel and how to use the INT function in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –
Count In Excel (Formula, Examples)
What is COUNT in Excel?
The COUNT in Excel is a function that counts the number of cells that consists of numeric values in a selected range and ignores all the other entries in the range. For example, the formula “=COUNT(A6:A20)” counts all the cells with numerical values (code number) in the cell range A6:A20, which corresponds to 7.
The COUNT function counts numeric values, including the date, time, percentages, negative numbers, formulas, and fractions.
Key Highlights
The COUNT in Excel is a completely programmed function that can be used for an array
The COUNT function family has a total of five variants- COUNT, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK
To count logical values, we use the COUNTA variant of the COUNT function family
To count numbers meeting certain criteria, we use either COUNTIF or COUNTIFS function in Excel
The function COUNT in Excel does not count formula errors and logical values
The COUNT function counts dates, too, as Microsoft Excel stores the dates as serial numbers
The function COUNT in Excel does not count the logical values- TRUE or FALSE
COUNT in Excel Syntax:The syntax for the COUNT Function in Excel is-
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
Explanation:
Value1: A required argument of the COUNT function indicates the first item or cell of the specified range.
Value2: It is an optional argument of the COUNT function in Excel that denotes the second set of cells or ranges we wish to count. Once we put the first Value1, all other values become optional.
Note: We can provide up to 256 values to the COUNT function.
The return of the COUNT function is always either zero or greater than zero.
How to use the COUNT in Excel?Consider the examples below to understand how we can use the function COUNT in Excel.
You can download this COUNT in Excel Template here – COUNT in Excel Template
Example #1Solution:
Step 1: Place the cursor in cell C7 and enter the formula,
=COUNT(A6:A20)
The above formula will count the numeric values in the given list, as shown below.
Step 2: Press the Enter key to get the below result
The selected range contains 15 values, but the COUNT function in Excel only counts the numerical values and ignores everything else. As a result, it returns 4 as the total number of numerical codes.
Example #2The table below shows a list of dates. We want to count the total dates using the COUNT in Excel function.
Solution:
Step 1: Place the cursor in cell C8 and enter the formula,
=COUNT(A6:A12)
Step 2: Press the Enter key to get the below result,
The total number of selected cells is seven, but the COUNT function returned the value 5 because two dates in the given list are written in an incorrect format.
The below image shows the dates with incorrect format (highlighted in RED)
Example #3The table below shows the IDs of five employees, as well as their wages and attendance for the first week of January 2023. If an employee is present, his attendance is marked as 1; if absent, his attendance is marked as A. We want to use the COUNT function in Excel to calculate the Employee’s total wages based on his weekly attendance.
Solution:
Step 1: Place the cursor in cell J6 and enter the formula,
=COUNT(C2:I2)
Step 2: Press the Enter key to get the Total no. of Paid Days as shown below
The COUNT in Excel function returns the Total no. of Paid Days as 6.
Now,
Step 3: Place the cursor in cell K2 and enter the formula,
=B6/7*J6
Step 4: Press the Enter key to get the Total Wages of the week for Empl ID 1005
Step 5: Follow the same steps to get the Total wages for all the Emp IDs to get the below result
COUNT in Excel with IF condition
Syntax-
=IF(logical_test,[value_if_true],[value_if_false])
Example #4Consider the above example of employees with IDs, wages, and weekly attendance. Using the COUNT and IF functions, we want to find eligible employees for Full Payment.
Solution:
Step 1: Place the cursor in cell L2 and enter the formula,
=IF (COUNT(C6:I6)=7,” Full Pay”, “Not Full Pay”)
COUNT(C6:I6): There are 7 working days in the week. Therefore, an employee present on all the days will be eligible for Full Payment.
Thus, the condition is written as COUNT(C6:I6)=7
Step 2: Press the Enter key to get the below result
Now,
Step 3: Enter the same formula in the remaining cells to get the below output
Combined with the IF condition, the COUNT function shows that only the person with Emp ID- 1D006 is eligible for Full Payment. Since all other employees were absent on one or the other day that week, they are not eligible for full payment.
Difference Between COUNT and COUNTA
The function COUNT in Excel counts the number of cells having numeric values within a cell range, whereas the COUNTA function counts the number of non-empty or blank cells within a given range.
The function COUNT in Excel counts numeric values and dates, whereas the function COUNTA counts all the cells within a range irrespective of the data type.
Syntax of COUNTA function is-
=COUNTA (value1, [value2], …)
Difference between COUNT and COUNTA with ExampleThe table below shows 10 rows with 6 numeric codes, 2 non-numeric codes, and 2 blank cells.
The COUNT function counts the number of cells with numeric codes and gives the result of 6.
The COUNTA function counts all the cells having codes, excludes the empty cells, and gives the result of 9.
Things to Remember
Only numerical values are counted in the COUNT function.
The COUNT function ignores empty cells, text and string values, and error values in the array.
If the COUNT function is applied to an empty range of cells, the result will always be zero.
If a text follows the number, COUNT ignores that value also. For example, =COUNT (“145 Number”) would return the result as 0.
If logical values such as TRUE or FALSE are supplied to the formula, the COUNT function will count these logical values.
The result will be zero if the same TRUE or FALSE is supplied in a range.
If you want the count of all the values in the given range, use COUNTA, which counts whatever comes it’s way.
Frequently Asked Questions (FAQs) Q1) How do I count cells in Excel?Answer: In Excel, we can count cells using any of the COUNT function variants: COUNT, COUNTA, COUNTIF, and COUNTBLANK.
COUNT: To count cells with numeric values
COUNTA: To count non-empty cells
COUNTBLANK: To count blank or empty cells
COUNTIF: To count cells meeting specified criteria
Q2) What is the significance of the COUNT function in MS Excel Class 9?Answer: We can use the COUNT() function to sum or add the number of cells that contain numbers in the specified cell. The count function can perform the complex calculation of adding numbers in a large data set, thus saving time and effort.
Q3) What is the main difference between COUNT and Countif?The COUNT in Excel function counts the number of cells containing numeric data or entries, whereas the COUNTIF function counts the number of cells meeting the given criteria.
For example, the table below shows students’ Maths marks out of 50. Here, we use the COUNTIF function to count the number of students who have scored more than or equal to 35 and passed the test.
Recommended ArticlesThe above article is EDUCBA’s guide on using the function COUNT in Excel. For more information related to Excel formulas and functions, EDUCBA recommends the below articles.
Filter Shortcuts In Excel (Examples)
Filter Shortcuts in Excel
In this article, we will learn about Filter Shortcuts in Excel. there are different ways to access and apply filters in Excel. In the first way, we can select the headers first, then from the Data menu tab, select Filter Option under the Sort & Filter section. In a second way, we can apply the filter by pressing shortcut keys Alt + D + F + F simultaneously, and another way is by pressing shortcut keys Shift + Ctrl + L together to apply a filter in one go. Once the filter is applied, we can use other shortcut keys, such as the Alt + Down key, to get into the applied filter and select an option by pressing a shortcut key or navigation keys.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
How to Use Filter Shortcuts in Excel?Filter Shortcut in Excel is very simple and easy to use. Let’s understand the working of filter shortcuts in Excel with some examples.
You can download this Filter Shortcuts Excel Template here – Filter Shortcuts Excel Template
#1 – Toggle Autofilter in the ribbon Example #1Here is a sample data on which we have to apply a filter.
You will see the name of the consumers who have taken Business Growth Loan.
Example #2
Now, if we want to see how many consumers have taken a loan of 10000, we can apply the filter as shown below:
#2 – Applying filter using the “Sort and Filter” option on the Home tab in the Editing Group
It can be found on the right side of the Ribbon in MS Excel.
Using the above data, here is how we can apply the filter:
Example #3
To filter the consumers who have taken a car loan.
Example #4
Now, if we need to filter some particular consumers, like their loan amount and their loan type, suppose we want to know the above details for Arjit and Dipa; we will apply the filter as below:
We will then be able to see the relevant details.
#3 – Filter the Excel data by shortcut key
We can rapidly press a shortcut key to apply the filter to our data. Together, we have to press Ctrl+shift+L.
Example #5Here is a sample data on which we have applied the filter using the shortcut key.
Select the data and then press the shortcut key to apply the filter, i.e., Ctrl+shift+L.
If we want to filter the status of the Agents, like Active and Terminated, from the complete data, we will proceed as below:
Press OK, and we will see the Active and Terminated agents.
While applying filters on the selected criteria, we see other options as well as shown below:
On top of the filter drop-down, we see an option Sort A to Z; then we have Sort Z to A, Sort by color, and Text filters.
The data is filtered and sorted in order of Z to A.
Filter cells that Begin with or End with a specific text or character.
Filter cells that Contain or Do not Contain a particular text or symbol.
Filter cells that are either Equal to or Do not equal to a specific text or characters.
The data shows only Active and terminated status and not suspended.
Once you have applied the filter, you can disable it from that column as shown:
To remove a filter from the whole data, again press Ctrl+Shift+L.
Once you have filtered the data, copy it to any other worksheet or workbook. You can select the filtered data or filtered cells by pressing Ctrl + A. Then Press Ctrl+C to copy the filtered data, go to another worksheet/workbook, and paste the filtered data by pressing Ctrl+V.
Things to Remember about Filter Shortcuts in Excel
Some Excel filters can be used one at a time. For Example, you can filter a column by either value or color at a time and not with both options simultaneously.
For better results, avoid using different value types in a column. As for one column, one filter type is available. If a column contains different types of values, the filter will be applied for the high-frequency data. For example, if the data is in number in a column but is formatted as text, then the text filter will be applied, not the number filter.
Once you have filtered a column and pasted the data, remove a filter from that column because the next filter in a different column will not consider that filter, and hence the filtered data will be correct.
Recommended ArticlesThis has been a guide to Filter Shortcuts in Excel. Here we discuss how to use Excel filter shortcuts, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
Excel Autocorrect: A Complete Guide + Time Saving Examples
What happens when you type the word ‘Drnik’ instead of ‘Drink’ in Excel?
You would notice that Excel will autocorrect that misspelled word to Drink (as shown below).
Somehow, Excel knew that this is not the correct spelling and autocorrected it to the right one.
Now, it won’t autocorrect all the misspelled words.
Just a few!
For example, try the word ‘dirnk’.
It would not be auto-corrected.
The reason some words are autocorrected and others aren’t is because there is already a fix list of words that are prefilled in Excel to autocorrect.
Note: Autocorrect is enabled by default in Excel.
In this tutorial, I will explain what Autocorrect options are and then show you some examples where you can use it to save time. I will also cover how you can disable it (i.e., turn off autocorrect)
It also allows you to get some more control when using Excel (as we will see in the examples later in this tutorial).
But let’s first understand where are the autocorrect options and what is available by default.
This will open the Autocorrect Options dialog box.
Let me explain the different tabs in the Autocorrect dialog box and the options in these.
Autocorrect Options TabIn the Autocorrect Options tab, there are some options that are enabled by default and take care of some common issues.
Show Autocorrect Options buttons: This one is not relevant for Excel but it is for other MS applications. When this option is enabled, you see the autocorrect options in MS Word or MS PowerPoint (as shown below).
Correct two initial capitals: This option when enabled will automatically correct the two capital initials in Excel. For example, if you type ‘HEllo’, it will automatically change it to ‘Hello’
Capitalize first letter of sentences: When enabled, this option ensures that a new sentence starts with a capital letter. For example, if you type, ‘Hello. how are you?’, it will be autocorrected to ‘Hello. How are you?’
Capitalize names of days: This will automatically change the first letter of the day name if you enter in lowercase. For example, wednesday would be changed to Wednesday.
Correct accidental use of Caps lock key: In case you have the Caps lock on and you write a sentence, it will automatically correct the text and disable the Caps lock. For example, if you enter hELLO, it will automatically change it to Hello.
Replace text as you type: This is where Excel already has some commonly misspelled words (or shortcodes for some symbols). For example, if you type (c), it automatically gets converted into the copyright symbol. That is because it has been specified in the list in this option. You can add or remove words from the list (more on this in an example below).
Autocorrect ExceptionsWhile these autocorrect options are amazing, sometimes you may want it to not act super smart and correct these automatically.
For example, if you have the brand name ATs (where the ‘s’ is in lower case), Excel would automatically convert it into ‘Ats’.
While you like the autocorrect happening in all other cases, if you want to exclude this particular case, you can do that.
In the Autocorrect Exceptions dialog box, you can have two types of exception:
First Letter: By default, Excel capitalizes the alphabet after the period (dot). You can provide some exceptions here (there is already a list for common exceptions).
Initial Caps: If you don’t want ATs to be converted to Ats, you can specify that here.
Autoformat As You Type TabThis tab has three options (all of which are enabled). I find all these three options useful.
Apply as you work: This will automatically add new rows and columns in an Excel Table when you enter anything in the cell adjacent to the one in the table.
Automatically as your work: When you enter a formula in a column in an Excel Table, this option will enter the same formula (with cell references adjusted) into all the cells in the column.
Actions TabIn Microsoft applications, you can create an action based on a specific word or text.
In Excel, there is only one type of action available – which is date action.
This could be useful if you have a list of dates and want to quickly save some in your calendar or want to schedule a meeting (using Outlook).
This option is disabled by default and you have to enable it to be able to use it in Excel.
Math Autocorrect TabJust like you can insert symbols in an Excel cell (such as Delta, Degree, or Checkmark), you can also insert math symbols in an equation.
This tab has some text that automatically converts into the specified math symbol. For example, if you type sigma, it will replace it with the σ symbol.
Note that this will not work in the cells in the worksheet. It only works with equations.
Wish there were some words that were a part of autocorrect?
For example, let’s say you want to add the word ‘drikn’ to autocorrect so that it corrects it to ‘drink’.
You can use the below steps to add a word to autocorrect:
In the Options dialog box, select Proofing.
In the Autocorrect dialog box, enter the following:
Replace: drikn
With: drink
Now, when you type ‘drikn’ in Excel, it will autocorrect it to ‘drink’.
Before I show you some cool examples to use this, here are a few things you need to know about Autocorrect in Excel:
Autocorrect list is case sensitive. This means that you have added the word ‘drikn’ to be replaced by ‘drink’, it would only work with the lower case word. If you enter ‘Drikn’ or ‘DRIKN’, it will not be corrected.
This change is saved in Excel and would exist even if you close the workbook and open again. If you no longer want this, you need to go and delete it manually.
The change happens only when the exact word is used. For example, if you use ‘drikns’, it will not be autocorrected. For it to work, the word must not have characters just before or after it.
When you specify an autocorrect in Excel, it automatically gets activated in other MS applications such as MS Word or MS PowerPoint.
Autocorrect was created as a way to correct common spelling errors. But you can also use it in some awesome ways to save time.
Related: Spell Check in Excel.Below are some useful examples to use Autocorrect (other than correcting a misspelled word).
Imagine you work for a company ‘ABC Technology Corporation Limited’.
No matter how fast you type, this would feel like a waste of time.
Wouldn’t you wish there was a way where you can just enter ABC (or whatever you want), and excel replaces it with the company’s name?
This is where the awesomeness of Autocorrect can help.
You can specify an abbreviation in Autocorrect, and whenever you use that abbreviation, Excel would automatically convert that into the specified text.
For example, you can specify that whenever you type ABC, Excel should automatically replace it with ‘ABC Technology Corporation Limited’.
Something as shown below:
This happens when you add an autocorrect in Excel where ABC should be corrected to ” (as shown below in the autocorrect dialog box).
What if you want to insert ABC and not the full name?
In case you don’t want the autocorrect to change ABC to the full name, simply hit Control + Z to get back ABC.
While using Control + Z works, it’s best to choose an abbreviation which you’re unlikely to use in your work. This ensures there is no chance of you getting the full name by mistake (when all you wanted was the abbreviation text).
Below are some scenarios where this autocorrect trick can save a lot of time:
You can enter file names or folder names quickly (instead of copy-pasting it every time).
If you have a list of team members, you can use their initials to enter their names quickly.
A word of caution: Any autocorrect option you specify in Excel also get activates in other MS applications such as MS Word or MS PowerPoint. In such cases, it’s best to use abbreviations that you’re not likely to use anywhere else.
There are some symbols that are hard to insert/type in Excel as these are not already available on the keyboard (such as the degree symbol or the delta symbol or bullet points).
You either need to know the keyboard shortcut (which are often long and complicated) or need to use the Insert Symbol dialog box (which is time taking).
If there are some symbols you need to use quite often, you can use the Autocorrect feature to give these symbols a code name or abbreviation.
Now when you have to enter that symbol, you can simply use the code name and it will get autocorrected to that symbol.
Below is an example where I am using the code DEGSYM to get the degree symbol in Excel.
To do this, make the following change in the Excel Autocorrect dialog box:
This trick (which I learned from this blog) is a little far-fetched, but if you work with a lot of long formulas, this can save you some time.
Below is a formula that will combine the text of the three cells that are left to the cell in which this formula is used.:
Now if you often need to create a formula such as this, it’s better to create a simple code for it and use it in Autocorrect.
In this case, I have used the code ‘com3’ in autocorrect to get the formula.
Now, you can use the code ‘com3’ to get the entire formula in a few keystrokes (as shown below):
Note: As I mentioned, this is something most of you would never have to use, but it’s still a good trick to know (just in case). The above example is a real-life case where I am currently using this in one of my projects to save time.
While I believe autocorrect is a great feature, it may not be relevant for everyone.
And in some cases, it may actually be an irritation. For example, if you type (c) or (r) or ™, Excel autocorrect is going to change the text automatically (into © or ® or ™)
In such cases, it’s best to turn off autocorrect, or at least delete the terms that you don’t want to be autocorrected.
Below are the steps to turn off autocorrect:
In the Options dialog box, select Proofing.
In the Autocorrect dialog box, within the Autocorrect tab, uncheck the ‘Replace text as you type’ option.
Note: The above steps would completely turn off the autocomplete feature where it replaces some text with the specified text. This may also mean that those commonly misspelled words will no longer be corrected.
If you want to keep the overall ‘Replace text as you type’ feature but want some exceptions, you can find the word in the list and delete it manually (or edit it).
Below are the steps to do this:
In the Options dialog box, select Proofing.
In the Autocorrect dialog box, within the Autocorrect tab, select the word that you want to delete.
You can also replace a word in Autocorrect. For example, instead of (c) turning into the copyright symbol, you can use it to be converted into the word – copyright.
If you write something and Excel changes it because of autocorrecting, you can get back the original text by hitting Control + Z.
For example, as soon as you type (c) in a cell in Excel and press the space key, it will instantly be converted into the copyright symbol.
But if you now use Control + Z, it will go back to being (c) and would remain that way.
While Autocorrect is a feature which most of the Excel users will never have to tweak, it’s good to know some ways you can use it to save time (as shown in the examples).
I have lately started using it for some formulas that are quite huge but I use these often (as shown in example 3).
You May Also Like the Following Excel Tutorials:
How To Use Excel Vba Or Function With Examples?
Excel VBA OR Function
Like a worksheet function excel VBA also has a logical function which is OR function. In any programming language OR function is defined as follows:
Watch our Demo Courses and Videos
Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.
Condition 1 OR Condition 2. If any of the given conditions happens to be true the value returned by the function is true while if both of the condition happens to be false the value returned by the function is false. OR Function can be termed as that it is opposite to AND function because in AND function both of the condition needs to be true in order to get a true value. Even if a single condition is termed as false then the whole value returned by the AND function is false. While in OR Function only one condition needs to be true in order to get TRUE as an output.
Syntax of OR Function in Excel VBA
VBA OR function has the following syntax:
{Condition 1} OR {Condition 2}Let us use this function in VBA to have a clear mindset of how to use this function in general terms.
Note: In order to use VBA we need to have developer access enabled from the file tab.
How to Use Excel VBA OR Function?We will learn how to use VBA OR Function with few examples in excel.
You can download this VBA OR Excel Template here – VBA OR Excel Template
Example #1 – VBA ORFollow the below steps to use VBA Union function in Excel:
Step 1: Now once we are in VB Editor go ahead and insert a new module from the insert section.
Code:
Sub
Sample()End Sub
Step 3: Define the four variables A B C and D as integers.
Code:
Sub
Sample()Dim
AAs
Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
End Sub
Step 4: Define a variable X to store the value of OR Function, define it as a string.
Code:
Sub
Sample()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
Dim
XAs String
End Sub
Step 5: Assign Random Values to A B C and D.
Code:
Sub
Sample()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
Dim
XAs String
A = 10 B = 15 C = 20 D = 25End Sub
Step 6: Define X’s Values as conditions for A B C and D.
Code:
Sub
Sample()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
Dim
XAs String
A = 10 B = 15 C = 20 D = 25End Sub
Step 7: Now we will display the value of X stored in it.
Code:
Sub
Sample()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
Dim
XAs String
A = 10 B = 15 C = 20 D = 25 MsgBox XEnd Sub
Step 8: Run the code from the run button provided in the screenshot below and then we see the following result when we run the above code.
Why we get the value as false because A is not greater than B and C is not greater than D. Both of the values of the condition were returned as false so our final output is also returned as false.
Example #2 – VBA ORStep 1: Now once we are in VB Editor go ahead and insert a new module from the insert section.
Step 2: A code window will appear on the right-hand side of the screen. Define the subfunction as Sample1.
Code:
Sub
Sample1()End Sub
Step 3: Define the four variables A B C and D as integers.
Code:
Sub
Sample1()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
End Sub
Step 4: Define a variable X to store the value of OR Function, define it as a string.
Code:
Sub
Sample1()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
Dim
XAs String
End Sub
Step 5: Assign Random Values to A B C and D.
Sub
Sample1()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
Dim
XAs String
A = 10 B = 15 C = 20 D = 25End Sub
Step 6: Define X’s Values as conditions for A B C and D.
Code:
Sub
Sample1()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
Dim
XAs String
A = 10 B = 15 C = 20 D = 25End Sub
Step 7: Now we will display the value of X stored in it.
Code:
Sub
Sample1()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
Dim
XAs String
A = 10 B = 15 C = 20 D = 25 MsgBox XEnd Sub
Step 8: Run the code above from the run button as shown and we will see the following result as we run the above code.
Why we get the value as True because A is less than B and C is not greater than D. One of the values of the condition were returned as true so our final output is also returned as true.
Example #3 – VBA ORNow let us use OR Function in VBA with the IF function. Earlier we used another variable to store the Boolean value of OR function and display it. This time we will use a personalized message to display using or and if function.
Steps 1: Now once we are in VB Editor go ahead and insert a new module from the insert section.
Step 2: A code window will appear on the right-hand side of the screen. Define the subfunction as Sample2.
Code:
Sub
Sample2()End Sub
Step 3: Define all the four variables A B C and D as integers and assign them random values.
Code:
Sub
Sample2()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
A = 5 B = 10 C = 15 D = 20End Sub
Step 4: Now write the if statement for the given variables, for example, like in the code given below,
Code:
Sub
Sample2()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
A = 5 B = 10 C = 15 D = 20End Sub
Code:
Sub
Sample2()Dim
AAs Integer
Dim
BAs Integer
Dim
CAs Integer
Dim
DAs Integer
A = 5 B = 10 C = 15 D = 20 MsgBox "One of the conditions is true"Else
MsgBox "None of the conditions is true"End If
End Sub
Step 6: Run the above code from the run button and we will get the following result displayed.
As one of the conditions were true we have the above result.
Example #4 – VBA ORLet use VBA OR function in a real scenario. We have the following data, Name of the employees and sales done by them. If their sales are equals to specific criteria or greater than that then they will receive the incentive or there will be no incentive for those employees. Have a look at the data below,
The incentive criteria are 10000 for this example. If the sales done by the employees is equals to or above 10000 they will receive the incentive.
Steps 1: Now once we are in VB Editor go ahead and insert a new module from the insert section.
Step 2: In the code window, declare the subfunction,
Code:
Sub
Employee()End Sub
Step 3: Declare a variable X as Long and write the if statement as below,
Code:
Sub
Employee()Dim
XAs Long
For
X = 2To
10 Cells(X, 3).Value = "Incentive"Else
Cells(X, 3).Value = "No Incentive"End If
End Sub
Step 4: Start the loop for the next cell.
Code:
Sub
Employee()Dim
XAs Long
For
X = 2To
10 Cells(X, 3).Value = "Incentive"Else
Cells(X, 3).Value = "No Incentive"End If
Next
XEnd Sub
Step 5: Run the code to form the run button provided and once we have run the code check the result below,
In the If Statement, we used that if the sales are done is equals to 10000 or sales done greater than 10000 the employee will receive incentive.
Things to RememberThere are a few things we need to remember about VBA OR Function:
It is a logical function in excel or any other programming language.
It returns a logical output true or false.
It is the opposite of AND Function.
Recommended ArticlesThis is a guide to VBA OR. Here we have discussed how to use Excel VBA OR Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
Update the detailed information about Excel Formulas Cheat Sheet (Examples) on the Cattuongwedding.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!