Microsoft Excel is an affordable data management, tracking, and analysis software for all industries and sectors.
Why has Excel become a need of every business?
One of the most widely used software applications today is Microsoft Excel. It is a flexible program that may be applied to many tasks, and it offers features like the ability to enable macros. Excel is a terrific tool to have in your toolbox, whether you’re a business person, a student, or just trying to keep track of your funds or activities.
Excel has become necessary for every small business and significant enterprise for numerous reasons, including but not limited to its capacity to manage data, enable macros and workbooks.
Recording data, controlling finances, and producing reports are excellent platforms for graph and chart creation within Excel.
In addition to being extremely flexible and user-friendly, Excel is also reasonably priced. It can be used for various tasks, from setting budgets to tracking sales. Furthermore, it has pivot tables, graphing tools, and Visual Basic for Applications, a macro programming language. Excel is available on various operating systems, including Microsoft Windows, Mac OS X, iOS, and Android, making it a versatile choice for users across different platforms
Here are a few key justifications for why every firm needs it
- 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.
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.
To sum up, Excel is a fantastic skill for anyone looking to manage data, plot charts, analyze data, forecast, etc. Excel is crucial for company owners to have more efficiency and heighten productivity, but it is also an excellent skill for everyone else.
What is Excel Formula?
Microsoft Excel formulas perform calculations, mathematical operations, and other data-processing tasks.
In Microsoft Excel, a formula manipulates a group of cell data to yield the intended result. The Formulas nevertheless return a response, even if the outcome is incorrect. Excel formulas perform basic calculations to the most complex mathematical operations.
Excel 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.
This post will cover some of the most popular Excel formulas and shortcuts to help you do tasks quickly and easily.
Let’s look at a few of the most used Microsoft Excel formulas.
ARRAY
An Excel formula called an ARRAY is used to carry out complicated computations that are frequently impossible to do manually.
It can be applied in several ways, but its fundamental function is as an input for calculations throughout an Excel cell range.
The two basic categories of array formulas are those that return an array of values and those that return a single value. ARRAY formulas are an essential tool for anyone who wants to get the most out of Excel and may be used to execute a wide range of different computations.
Single Cell Array illustration
As illustrated below, the array formula multiplies shares from Cell C3 to G3 and their prices from Cell C4 to G4. Finally, adds those results to produce a total value of 14400.
This formula is an example of a single-cell array formula since it only occupies one cell.
Excel allows the equal symbol (=) to start formulas in its standard syntax, simplifying this procedure. You can utilize any of the built-in Excel functions in your array formulas.
The users can use the Excel workbook’s ARRAY functions to carry out a variety of calculations. You can obtain more details about utilizing Arrays can be obtained from Microsoft.
AVERAGE
The AVERAGE (Arithmetic mean) function in Excel is used to compute the data’s average, as suggested by its name. The average of that particular data is provided in a row or column by adding all numeric values and dividing the result by the number of numeric values.
Let’s use an illustration to assist you in grasping this:
Let’s look at how to find the average share price in the Excel sheet below.
Place the cursor where you wish to insert the average, go to the Autosum menu in the Excel’s top left corner, and choose AVERAGE, as shown in the Excel sheet below.
The formula for the average of the entire row will be added, displaying the answer to the appropriate cell as shown below:
The Microsoft video might also teach you how to comprehend the Average function.
COUNT
In Excel, the COUNT function is typically used to count the number of cells in a given array.
In other words, it is employed to determine how many cells in a specific range of numbers are present.
For instance, you may enter the formula =COUNT to count the numbers between D4 and D8 (D4:D8). The COUNT function returns four as the result of this range contains four cells with numbers as illustrated by the example below:
The data can be quantified using the COUNT formula. To find more information about how to use the COUNT function, visit Microsoft Video.
DATE
The Excel DATE function can be used to combine specific years, months, and days from other cells to construct dates.
It can be helpful to dynamically alter the dates in an Excel worksheet based on other information.
The default syntax for the formula is =DATE (year, month, day). The image below shows how the DATE formula functions in Microsoft Excel.
You can alter the date format using the Format Cell option, as seen in the image below.
DIVISION
In actuality, Excel doesn’t have a DIVISION symbol. Just press the forward slash (/) to utilize the division function. It is one of Excel’s simplest formulas.
Let’s examine an example to understand how Excel’s DIVISION function operates.
The formula =D/10 will be used to divide values from column D by 10, as seen below. The same formula can be applied to all cells if you drag down the corner of cell E4 to all the other cells.
The division formula will be =C4/D4, as shown below, to divide the numbers in cell C4 by the numbers in cell D4.
Simply dragging the corner of the cells will allow you to reproduce results to any number of cells.
IF
IF command is a frequently used Excel function. It enables logical comparisons between values and output returns. Text and values can both be evaluated using the IF function.
The IF function can be used for mathematical operations with logical functions like AND and OR in addition to comparison. Error evaluation is another application for it.
Additionally, you can nest different IF functions together to carry out numerous comparisons.
The IF function in Excel performs a logical test and returns a value or text for both a TRUE and a FALSE outcome.
It’s default syntax is IF (logical test, value if true, value if false).
Let’s examine the Excel sheet pic1 below to understand the IF function:
The task is to identify students who scored more than 50 and mark them as Pass, while pupils who achieved less than 50 should be recorded as Fail.
The IF statement, as shown below in Picture 1, checks the student scores in the D column with the number 50, and if the score is higher than 50, it will mark as Pass; if lower than 50, it will insert as Fail. The Pass and fail are inserted in the Result Column.
Here is another illustration of how an IF statement may be applied to project management or sales:
The task is to find projects that are going within budget and over budget in the Excel file below.
IF formula is added to the E column determines if a project is within budget or over budget by comparing its actual cost from the D column with its budgeted price from the C column. The outcome is placed in E Column.
Many businesses use the IF function to calculate debt repayment schedules, create depreciation schedules, evaluate data inaccuracies, monitor budgets, organize data, etc.
LEFT, MID, and RIGHT
To extract a specific character, word, or number from an Excel cell, use the commands LEFT, MID, and RIGHT.
Let’s see a quick demonstration of how to do that.
Exercise – Extract the First name from the Full name using the LEFT function
The LEFT function’s syntax is =LEFT (text , num)
As seen in the Excel spreadsheet below, our example uses =LEFT(B4,3), and the first name Sam is extracted and entered into cell D4. The first name from the left’s final position is represented by the number 3.
If you want to extract names and numbers using the IF function, you must be very careful when specifying the start and end numbers; you cannot just duplicate the formula into the cells below and expect the outcome.
Since the first name Ricky has five letters, your formula would be =LEFT(B5,5) if you wanted to extract the first name from B5.
Now let’s see how to use the MID function and separate the second name from the full name.
The MID function’s syntax is =MID(text, start number, finish number)
In our below Excel sheet, the second name is Derling from B4 cell, so that the formula will be
=MID(B4,5,11).
The D of Derling begins at count five, while the G of Derling ends at count 11. The numbers 5 and 11 are used in the formula. Derling will be extracted as the second name and entered into cell E4.
In this function, you must also be careful when mentioning the start and end numbers. You can’t copy and paste the formula to replicate the formula in other cells.
Finally, we will Extract ‘ext.’ number from Column C using the RIGHT function
The RIGHT function’s syntax is =RIGHT (text , end num)
Look at the Excel sheet below to see how the RIGHT function works.
The formula will be =RIGHT(C4,2).
The number 2 is the ending location of the ext. from the right. As seen in the image below, extension 88 will be removed and placed in the F4 cell.
Not to mention that to avoid mistakes, the position number must match exactly.
To swiftly extract information from a vast database, such as contacts, addresses, lists, etc., use the LEFT, MID, and RIGHT functions.
MULTIPLICATION
Another easy Excel operation is multiplying numbers using the asterisk (*) symbol.
You have already learned that Excel formulas always start with the equal sign (=). To comprehend the multiplication formula, let’s look at one example.
For instance, if you enter the formula = 11*5 and press Enter, the cell will show the answer as 55.
Take yet another illustration.
If you want to multiply the number in column C by a number in column D, then the formula will be =C5*D5, as shown below in the Excel sheet.
If you drag the corner of the E5 cell to any other cells, the formula will automatically be applied to all of them, and the outcome will be positioned appropriately.
The Multiplication formula will begin with PRODUCT if you want to multiply specific numbers from a column, as illustrated in the following pic.
The PRODUCT can also be used to mention sequential cells separated by a colon (:), as illustrated in Picture 1.
There are several ways to use Excel’s multiplication feature.
The formula for multiplying a single constant integer by a whole column is =C5$D$5, as demonstrated in the Excel sheet below.
The example below multiplies all of the numbers in column C using the constant number 8 from column D.
The dollar ($)symbols inform Excel that the reference to D5 is “absolute,” which means that the reference will always be D5 when you copy the formula to another cell.
PERCENTAGE
Excel’s PERCENTAGE function is used to determine percentages of various data, including exam correct answer percentages, discount pricing based on percentage discounts, the percent difference between two numbers, and more.
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.
Excel calculates percentages by dividing data and turning them into percentages using the keyboard shortcut Ctrl+Shift+% or the percentage sign (%) found in the Numbers group on the Home tab, as seen in the image below.
Let’s look at the example below to understand better how percentages work:
The task is to determine the percentage of students who have four subjects with 100 marks each.
As shown below, Column I contains marks earned by the students, and total marks (100 marks for each subject x 4) are presented in column J.
Let’s examine the percentage calculation process now.
The percentage is calculated using the following formula.
= I5/J5 (Marks Obtained / Total Marks)
This formula is added to column K in the Excel sheet, and the results are shown below; however, we require percentages instead of decimals.
Navigate to the Home tab, select the Number group as indicated in the below image, and click the percentage sign (%) or press CTRL+SHIFT+% to convert these decimals to percentages. In the Excel image above, Column L displays the calculation results.
One needs to be familiar with cell formatting to comprehend the decimals in the PERCENTAGE function. Visit Microsoft’s page to see a preview of it.
RANDOMIZE
In Microsoft Excel, there is no precise function for RANDOMIZING list items.
The RAND function produces random numbers as the basis for randomizing your list based on ascending and descending order if you want to RANDOMIZE anything.
Place the RAND function in the column next to the list to RANDOMIZE. Then, sort these random numbers in either ascending or descending order, which causes your list items to change positions.
You can delete the RAND function column once your items have been randomized since you no longer require it.
To further understand how the RAND function works, consider the example below.
Set the cursor next to your column list, then type =RAND().
As you can see in the image below, this function will insert one random number into the cell, and if you move the corner of the cell, it will reflect all of the random numbers until the bottom of your list.
Go to the Home tab, click Sort & Filter, and then choose either Sort Largest to Smallest or Sort Smallest to Largest to randomize the list.
As you can see from the image below, the things on your list are randomized. You can eliminate the RAND function column once your list has been randomly generated.
SUBTRACTION
The simplest tasks in Microsoft Excel are adding and subtracting numbers.
Let’s practice subtracting values from one column from values in another column.
The formula begins with the equal sign (=), selects the cell to be subtracted from, enters the negative sign (-), and selects the next cell.
The subtraction formula will be duplicated in all cells if you drag the corner of the E4 cell.
You can even subtract a single number from the range of numbers.
Let’s see the below example to understand
The formula will be =C5-$D$5, as displayed in the Excel sheet below. You can enter this formula in the adjacent column. By moving the corner of the E5 cell, you can copy this formula to other cells.
SUM
In Excel, the SUM function combines several numbers, adds a single number to a range of numbers, adds numbers from one column to another, etc.
Let’s see how to add numbers from alternate cells.
The sum formula starts with the equal sign (=), as shown in the Excel sheet below.
Place the cursor where you want the total numbers to show, then begin by adding an equal symbol (=), choosing the cell you want to add to, followed by a plus sign (+), and doing this as many times as you like. After pressing enter, you will get the sum of alternative numbers, as shown below.
Let’s look at how to use the AutoSum method to add all the numbers in a single column. It is, again, one of the simplest formulas in Microsoft Excel.
Let’s use the same example as earlier and add all the numbers simultaneously.
All selected numbers will be automatically added by going to the Home tab, switching to the Editing area, and selecting AutoSum, as shown in the image below.
SUMIF
In Excel, the SUMIF function adds 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.
Now let’s look at how to carry out this calculation.
Set the cell range and the condition for the addition; in this case, we want to add only values bigger than 11.
As can be seen in the example below, the formula to perform this operation is =SUMIF (C4:C10, “>11”).
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.
TRIM
Extra leading or trailing spaces are eliminated using TRIM.
The TRIM formula usually begins with an equal sign (=), followed by the cell information and Enter.
=TRIM (C7)
As seen in the image below, the TRIM function eliminates leading, excess, and trailing spaces from cell C7.
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.
VLOOKUP
Manually searching would take a lot of time, especially if the workbook is extensive, to find something in a large table or range of cells.
In such circumstances, the VLOOKUP function is quite helpful. It’s used to locate an item’s number, text, etc., inside a particular range.
Take a look at this example to see how VLOOKUP functions.
Students’ names and information about their test results are included in the Excel file below.
The task is to find the result status of Margaret Adleman, whose Roll no is 3.
The equation to carry out this action is
=VLOOKUP(G5, B4:E9,4, FALSE)
Let’s understand the syntax details
- 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.
As seen from the worksheet below, the VLOOKUP function identified Margaret Adleman’s result status as Fail in the G6 cell.
Use XLOOKUP, an upgraded version of VLOOKUP, if you have Excel 365 or Excel 2021. Additional benefits of the XLOOKUP function include the ability to search in any direction and the provision of precise matches by default. Additionally, it is simpler and more convenient to utilize.
What are Excel Shortcuts?
Excel shortcuts can streamline your work and allow you to complete tasks fast and efficiently without constantly going through the menu.
When working with a sizable database, Excel shortcuts can be beneficial. You can quickly get the functionality you require with a few keystrokes.
They can speed up and simplify your data analysis. You can swiftly ascertain what’s happening in your workbook with shortcuts.
Let’s see some popular Excel shortcuts.
Quickly Select Rows or Columns
Click the alphabet at the top of the column to swiftly choose the entire column, or select any cell and press Ctrl + Space to select the whole column.
Click on the serial numbers at the far right of the sheet to swiftly pick the complete row, select any cell, and press Shift + Space to select the entire row.
The control (Ctrl) key, while clicking the desired rows or columns, will select the random ones.
Format Numbers
Ctrl+Shift+1 [!] is a shortcut for applying the standard Number format.
Work on the Workbook Instantly
You need a few shortcuts to work on a workbook, which is as follows:
Ctrl + N to start a new worksheet
Ctrl + O will open an existing worksheet.
Ctrl + S Workbook saving command
Ctrl + W will close the current worksheet.
Ctrl + PageDown will advance to the next sheet.
Ctrl + PageUp will take you to the previous sheet.
Alt + A will take you to the Data tab.
Alt + W will take you to the View tab.
Alt + M will bring up the Formula tab.
Insert Current Date and Time
Press Ctrl+; (semi-colon) to add the current date.
Press Ctrl+Shift+; (semi-colon), to insert the current time.
Copy to Adjacent Cells
Ctrl+R will copy values or formulas to an adjacent column cell.
Ctrl+D will copy values or formulas to the next row cell.
Ignoring Blank Cells while Editing
There are four primary methods for ignoring blank cells.
1. Use the IF Function to ignore blank cells in the range.
2. Using Excel’s ISBLANK Function to Disregard Blank Cells in a Range.
3. ISNUMBER Function.
4. Using Excel’s COUNT Function to Disregard Blank Cells in a Range.
Copy and Paste Multiple Selections
Press Shift + Down Arrow key: Multiple cells can be selected.
Ctrl+C will copy several cells.
Ctrl+V is used to paste numerous cells.
Go Back to Previous Locations
The Go-To dialogue box, from where you can go to the previous location, will appear when you use the F5 key or Ctrl + G.
Change Formula to Value
Change formula to value is done in three steps:
Step 1: Highlight the cells you want to convert
Step 2: Press Ctrl + C
Step 3: Shift + F10 and V together.
Entry Cell and Clear Data
Step 1: Data entry into a cell
Step 2: Type the required text or numbers to enter data and press ENTER or TAB.
Step 3:Pull the Fill handle (corner of the cell) to fill data automatically in other worksheet cells.
Step 4: Enter a line break by hitting ALT+ENTER to start a new line of data within a cell.
Step 5: To completely clear the Excel sheet’s data and information, Go to the Home tab, the Editing group, and then click the Clear button and choose “Clear All“.
Final Words
Any firm needs Microsoft Excel as a tool. Knowing how to use Excel will benefit you no matter your field, including sales, marketing, accounting, or any other.
The Excel formula and shortcuts described above will make it easier for you to manage your activities, including but not limited to tracking sales data, monitoring your marketing campaigns, maintaining accounts, organizing activities, and much more. You may get Excel’s free trial from Microsoft.
Next, you can check out online excel courses for beginner to advanced levels.