Microsoft Office Excel is one of the most important tools to perform the calculation, analysis, and visualization of data and information. It helps people to organize and process data by the use of columns and rows with formulas and some cool features of Excel. In MS Excel 2010, row numbers range from 1 to 1048576. There are total 1048576 rows, and columns range from A to XFD and there are total 16384 columns. Now, let’s take a closer look at some of the best Microsoft Excel features or functions you can use to become more efficient.
Check this article as a VIDEO
This function helps to search a value in a table. It returns a corresponding value. In other words, it searches for the given value and returns a matching value from another column. To know more about it, let’s look at the following syntax and its example.
Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In the above syntax, lookup_value is the value to search for. It can be text, date or number. Table_array is two or more data columns. Col_index_num is the column number in table_array from which the value in the comparable row should be returned.
Example: In the below example we have student names and marks in different subjects in column B to E.
From the above data, we need to know how much Ricky scored in Math.
Here is the VLOOKUP formula that will return Ricky’s Math score:
First, it looks for the value of Ricky in the Name column. It goes from top to bottom and finds the value in cell A9. After that, as soon as it finds the value of Math score, it goes to the right in the second column (Math) and fetches the value in it. The following picture shows us Ricky’s marks.
The pie chart is one of the best Excel features. It is used to visualize the contribution of each value to a complete pie diagram. It always uses one data series.
Follow the below steps to create a pie chart:
The above figure shows us the pie chart for the year 2012. It is showing us the Python slice, Whales slice, and Dolphins slice. In short, the contribution of each slice.
Mixed or combination chart is one of the top features of MS Excel. It combines and displays two or more chart types in a single chart.
To create a combination chart follow the following steps:
Mixed or combination chart is one of the coolest Microsoft Excel features and functions.
Data validation is one of the most powerful excel capabilities. It makes sure that users enter particular values into a cell. In the following example, we have restricted users to enter a whole number between 0 and 10.
Now, try to enter a number higher than 10. Following image is showing us the result:
If you are in the field of data analysis then IFERROR is one of the coolest advanced excel formulas and functions. It returns a result when a formula generates an error and a typical result when no error is found. IFERROR is a simple way to manage errors without using more complex nested IF statements.
=IFERROR (value, value_if_error)
The following example will show the use of IFERROR function. In this example, it displays the #DIV/0! error when a formula tries to divide a number by 0.
Now if we use iferror function then if a cell contains an error, an empty string (“”) is displayed.
Removing duplicates is the most powerful ms excel feature for people who work as a data analyst or those who play with data regularly. This example shows you how to remove duplicates in Excel. Check the following example. Click any single cell inside the data set and on the Data tab, in the Data Tools group, click Remove Duplicates.
The following dialog box will appear. Now, leave all checkboxes checked and click OK.
MS Excel will remove all identical rows (blue) except for the first same row.
To remove duplicates, sort and clean lines in Google Docs / Google Sheets please check these free plugins: Lines Sorter and Cleaner plugin for Google Sheets and Lines Sorter and Cleaner plugin for Google Docs.
Conditional formatting allows a user to change the format of a cell dependent on the content of the cell, or a range of cells, or another cell or cells in the spreadsheet. It also allows users to highlight errors and to find important patterns in data. Conditional formats can apply basic font and cell formattings like number format, font color and other font properties, cell borders and cell color. Also, there are different conditional formats that allow visualizing data by using icon sets, color scales, or data bars.
The Microsoft Excel MINVERSE function gives the inverse matrix of a given matrix. This function comes under Math/Trig Function.
Syntax: MINVERSE( array )
In the below example, the minverse function is entered into cells F1 of the spreadsheet. It will be like =MINVERSE( A1:D4 ). Check the following images for a result.
The result is displayed in cells from F1 to I4.
Microsoft Excel DB function is a financial function. It allows users to calculate depreciation of an asset. In this, the fixed-declining balance method for each period of the asset’s lifetime is used.
Syntax=DB(cost, salvage, life, period, [month])
In the following spreadsheet, the DB function is finding the yearly depreciation of an asset that costs $10,000 at the start of year 1, and has a salvage value of $1,000 after 5 years.
Now after entering the above formulas in each cell the following figure is showing us the result.
Data is the new oil and data visualization is also one of the most powerful Microsoft Excel features. Sparkline is an amazing excel feature. It is also called as a visualization tool of MS Excel that enables people to perfectly visualize the overall trend of a set of values. In short, sparklines are mini-graphs situated inside of cells. The following example will show you sparklines.
To create the sparklines, follow these steps Select the range that contains the data that you’ll plot. Now, Go to Insert > Sparklines > Select the type of sparkline you want (Line, Column, or Win/Loss). In the above example, we are using lines. Check below image to see the result:
COMPLEX function converts real and imaginary coefficients into a complex number like x + yi or x + yj.
Syntax: COMPLEX( real_coefficient, imaginary_coefficient, [suffix] )
The following image is showing us the conversion. If you write =COMPLEX(A2,B2) in D2 cell then it will convert a real and imaginary number into a complex number 3+5i as shown in the figure.
Yes, MKDIR exists in MS Excel and it is one of the most underrated excel capabilities. It comes under File/Directory Function. MKDIR can be used a VBA function. It can be used in macro code that is entered through the Microsoft Visual Basic Editor.
Syntax: MkDir path
Example: MkDir “c:\Sample\Excel2010”
The MKDIR command will only create the Excel2010 directory under the c:\Sample directory. It will not create the c:\Sample folder automatically.
If you want a binary result in excel then If and Or function is useful. In short, if you want to achieve True or False or Pass or Fail as result then If or function can be used. In this, our formula starts with an IF function and in that we embed the OR function. See below image.
Now when you press enter and drag the fill handle, the Pass or Fail result will be displayed. Check below image.
The Excel HYPERLINK function allows you to create a shortcut to a file or website address. To create, click on Insert tab, in the Links group, click Hyperlink.
Insert Hyperlink dialog box will appear. Now, to create a link to an already created Excel file, select a file.
Now, to create a link to a web page, type the Text to display, and click OK. We have entered google.com as text to display and the following image is showing us the result.
The TRANSPOSE function allows users to a transposed range of cells. It returns a horizontal range of cells when a vertical range is entered as an input. Or a vertical range of cells is returned if a horizontal range of cells is entered as an input. It comes under a Lookup/Reference Function of features of MS Excel.
Syntax: TRANSPOSE( range )
After entering function. Below image is showing us the result.
This function returns the reference to a cell based on its string description. It comes under Lookup/Reference feature of MS Excel. It returns the referenced cell’s value.
Syntax: INDIRECT( string_reference, [ref_style] )
The following image shows us the use of the INDIRECT function.
FORMAT function accepts a string argument and returns it as a formatted string. It comes under String/Text Function of Microsoft Excel.
Syntax: Format ( expression, [ format ] )
Let’s look at some Excel FORMAT function examples:
CONCATENATE function enables users to join 2 or more strings together.
Syntax: CONCATENATE( text1, [ text2, ... text_n ] )
The following figure is showing us the use of the CONCATENATE function:
Paste Special is one of the most amazing features of Excel. It enables the user to control how the content will be displayed when pasted from the clipboard. The following image is showing how to use paste special.
If you want to remove extra spaces from spreadsheet then TRIM function is useful. The trim function will remove all the unnecessary trailing and leading spaces from the cell.
The above figure is showing you how TRIM function works. It has removed the unwanted spaces from the column A and the result is displayed in column C.
Stay tuned and check our next article about Advanced functions in Google Sheets.
Check 10 more Microsoft Excel functions below!
This is one of the coolest Microsoft Excel features. The round function is utilized to receive an amount that has several decimals and round it to the number of specified decimals. The following image is displaying the use of Round function. In this, we have divided labor into Total expenses in column D. If we just implement the formula B2/C2, the large number after the decimal will be displayed. To avoid this we have used the Formula: =ROUND(B2/C2,2). This will format the cell to 2 decimals and it will display it perfectly. The Round() function is pretty useful if there are large decimal numbers. This function is similar to that or Round() function used in SQL.
The PROPER function is used to make each of the entered phrases into a proper looking style or sentence cases. It is a text function which is used to capitalize each word. The following image is displaying the use of the PROPER function. In this function, you just have to enter formula =PROPER(A2:A3). The range can be anything. The function is a string function and will convert the entered text into the proper sentence case. The PROPER function is crucial if users have involved in text spreadsheet while data migration. It is simple to implement and useful to convert text into proper sentence structure.
The now function is pretty simple. It is an uncomplicated function that will just tell users precisely what time and day it is. By using this function users can format it as a date to show the date and time or just the date. The following image is displaying the use of NOW() function. The cell B3 is displaying the current date of your system after you enter this formula =NOW() in cell B3.
The CHOOSE function is excellent for summary analysis in commercial modeling. It enables users to choose between a particular number of options, and answer the selected choice. For example, there are three distinct postulates for revenue growth next year: 5%, 12%, and 18%. Applying the CHOOSE formula users can return 12% if they recognize Excel you want choice. The following image is displaying the result.
This is one of the best advanced Excel features. If there are large numbers then this feature is helpful to give names to ranges so that users can refer to these names in advanced Excel formulas without clicking and picking long ranges. To promptly give names to the ranges follow these steps:
This is one of the best advanced excel tools for business. This feature gives aggregate statistics like Average, Count, Numerical Count, Max, Min, and Sum of the data from a picked range without inserting any formula. To display these results in the bottom toolbar, right click on the toolbar and pick the wanted statistic. The following image is displaying the use of this feature. The quick feature is really helpful if users want to aggregate numbers quickly.
This feature is useful to preserve the validity of data. Often, users need to check the input value and give some advice for further steps. For example, the date of birth in sheet should be in DD/MM/YYYY format and all users engaging should be between 18 and 55 years old. This feature is just like constraints.
To assure that data outside of this rule isn’t inserted, go to Data->Data Validation->Setting, input the conditions and shift to Input Message to give suggestions like, “Please input your date of birth in DD/MM/YYYY format, which should range from 20 to 60. Users will get this message when hovering the pointer in this area and get a notification message if the entered data is not proper. The following image is displaying the use of these features.
Usually, there are three shortcuts on the top menu. These are Save, Undo Typing and Repeat Typing. Nevertheless, if users want to apply or use more shortcuts options such as Copy and Cut then they can set them up by following these steps: File->Options->Quick Access Toolbar, add Cut and Copy from the left column to the right, save it. The two more shortcuts will get added to the top menu. This is one of the coolest features of Excel which helps users to quickly apply useful functions. There are many such useful functions which can be implemented on the top menu by following this simple trick.
Almost all Excel enthusiasts understand how to hide data by right-clicking to select the Hide function. The problem with this is that this can be quickly seen if there is only a limited data. The safest and smoothest way to hide data completely is to utilize the Format Cells function. Follow these steps: Pick the area and go to Home->Font->Open Format Cells->Number Tab->Custom-> Type;;; -> Click OK, then all the contents in the area will be hidden, and can only be spotted in the preview area near to the Function button.
If users are working in any finance sector, real estate, FP&A or any business analyst position that handles debt programs then these functions are one of the most important functions of Excel. The PMT formula provides users the value of equal amounts over the period of a loan. Users can apply it in combination with IPMT which explains users the interest amounts for the equivalent type of loan and then separate principal and interest amounts. Following image is displaying how to use the PMT function to get the regular mortgage amount for a $1 million debt at 5% for 30 years.
About the Author
Prasanna is an independent cybersecurity consultant and technical writer, focusing on penetration testing and vulnerability assessment. He provides penetration testing services to a wide variety of clients, including financial institutions, brokerage firms, professional regulators, manufacturing companies and transportation companies.