In this article, we will explain to you the most used Microsoft Excel Formulas and Shortcuts.
Why we need Excel Formulas:
Excel is necessary for everyone working in any industry that requires tabular data. Knowing how to use Excel will make your life much more straightforward, whether in business, as a professional, or as a teacher.
Helpful in organizing client sales lists, budgeting, and stock analysis in the financial sector.
Useful for e-commerce enterprises to spot patterns and classify data into meaningful groups.
Carries out human resources tasks like classifying hours worked and arranging employee profiles and expenses.
Its other applications include content calendars, consumer data collection, invoicing, and inventory tracking and administration.
Its ability to quickly integrate with other data pipelines and automation tools makes it unique. For instance, Zapier enables you to link thousands of popular apps with Microsoft Excel.
Microsoft Excel Formulas
Microsoft Excel Formula performs calculations, mathematical operations, and other data processing tasks. It also allows you to work with date and time variables and do numerous other operations like finding averages and percentages for a group of cells.
Here we have most used Microsoft Excel Formulas:
Average
Array
Count
Division
Date
Multiplication
Sum
SumIf
if
Subtraction
Percentage
Randomize
Trim
Vlookup
Left, Mid, and Right
Average
It is used to compute the data's average. The average is provided in a row or column by adding all numeric values and dividing the result by the number of numeric values.
1. Create a table.
2. Go to Editing and select Average.
or simply you can write the formula =AVERAGE(B2:B6). (See the image below)
Array
It is used to carry out complicated computations that are frequently impossible to do manually. The two basic categories of the ARRAY Formula are:
Return Array of values
Return Single value
Count
It is used to count the number of cells in a given array or how many cells are present in a specific range of numbers.
The syntax is =COUNT (D4:D8)
Division
It is used to divide the values. But there is no specific formula for Division. You have to use "/" to use the division function.
For example, =D/10 is used to divide values from column D by 10.
Date
It is used to combine specific years, months, and days from other cells to construct dates. It can also be used to alter the dates in Excel Worksheet.
The syntax is =DATE (year, month, day).
You can change the format of the date.
Right-click on the date (D10) and select "Format cells".
Then go to date and select the format you want. Click OK.
Multiplication
It is used to multiply numbers using the asterisk(*) symbol.
The syntax is =column * column (C4*D4)
If you want to multiply specific number of columns then use =PRODUCT (C4, C6, C8)
Sum
the SUM function is used to combine several numbers, add a single number to a range of numbers, add numbers from one column to another, etc. In simple words, it is used to add numbers from alternate cells.
The syntax is =C6+C8+ C10+C12
SumIf
The SUMIF function is used to add numbers that meet specific conditions. For example, if you want to add numbers greater than 11 from a range of numbers, the SUMIF function is used.
You can also use SUMIF to add numbers depending on a Text condition. It is a very flexible formula that you may use to integrate, based on circumstances, into any Excel page.
The syntax is =SUMIF(C4:C10, ">11")
if
It is used to enable logical comparisons between values and output returns Text and values both can be evaluated using the IF function. The IF function performs a logical test and returns a value or text for both a TRUE and a FALSE outcome.
The syntax is =IF(logical test, value if true, value if false).
Subtraction
It is used to subtract the values from one column to another column. The subtraction formula will be duplicated in all cells if you drag the corner of the D6 cell.
The syntax is =C4-D4
Percentage
It is used to determine the percentage of various data including exam correct answers the percentage, or discount. The percentage is calculated as the proportion per hundred in mathematical procedures. In this sense, the denominator and numerator are divided, and the resulting number is multiplied by 100.
Trim
It is used to eliminate extra leading or trailing space. When formatting a giant Excel sheet, the TRIM function is helpful because all you have to do is specify the cell range to have any extra spaces removed.
The syntax is =TRIM (column) or =TRIM (C9)
Vlookup
It is used to find something in a large table or range of cells. It is used to locate an item's number, text, etc., inside a particular range.
The syntax is =VLOOKUP (G5, B4:E9, 4, False)
G5: This cell contains the search term or number that you want to locate from the large table or workbook
B4:E9: This is the table range wherein VLOOKUP searches.
4: Instructs the VLOOKUP function to return the value from the fourth column of the table in the same row.
FALSE: It returns an error if the match is not found.
Left, Mid, and Right
It is used to extract a specific character, word, or number from an Excel cell.
The Syntax is =LEFT (text, num)
Microsoft Excel Shortcuts:
Microsoft Excel Shortcut allows you to complete tasks fast and more efficiently without going through the menu.
Below we have some useful Excel Shortcuts to simplify your data efficiently:
Ctrl + Shift + 1 [!] - To apply the standard number format.
Ctrl + N - To start a new worksheet
Ctrl + 0 - To open the existing worksheet
Ctrl + S - To save the worksheet
Ctrl + W - To close the worksheet
Ctrl + PageDown - Take you to the next sheet
Ctrl + PageUp - Take you to the previous sheet.
Alt + A - Take you to the Data Tab.
Alt + W - Take you to the View Tab.
Alt + M - Bring up the Formula Tab.
Ctrl + ; - To add the current date.
Ctrl + Shift + ; - To insert the current date.
Ctrl + R - Copy formulas to adjacent column cells
Ctrl + D - Copy formulas to the next row cell.
Shift + Down Arrow key - Select multiple cells
Ctrl + C - Copy several cells
Ctrl + V - Paste numerous cells
F5 + Ctrl + G - go back to previous locations
Ctrl + C (to copy), after that Shift + F10 + V - To change the formula to value
Ctrl + Space - To select the entire row or column
The Tech Platform