Microsoft Excel Features, Best Advanced Features of Excel - ByteScout
  • Home
  • /
  • Blog
  • /
  • Learn TOP-30 Excel Advanced Features and Functions

Learn TOP-30 Excel Advanced Features and Functions

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.

  1. Vlookup() function
  2. Pie Chart
  3. Mixed or Combination Type Charts
  4. Data Validation
  5. IFERROR Function
  6. Removing Duplicates
  7. Conditional Formatting
  8. MINVERSE() function
  9. DB() function
  10. Data Visualization
  11. Complex() function
  12. MKDIR() function
  13. IF() and OR() functions
  14. Hyperlink() function
  15. Transpose() function
  16. INDIRECT() function
  17. FORMAT() function
  18. CONCATENATE() function
  19. Paste Special
  20. TRIM() function
  21. ROUND() function
  22. PROPER() function
  23. NOW() function
  24. CHOOSE() function
  25. Named Ranges
  26. Quick Feature
  27. Input Restriction
  28. New Shortcut Menu
  29. Hide Data
  30. PMT() and IPMT() functions

Check this article as a VIDEO


1. Vlookup()

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.

Advanced Excel Functions

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:

=VLOOKUP("Ricky",$A$3:$E$10,2,0)

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.

Microsoft Excel Features

2. Pie Chart

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:

  • Select the range A1: D2.

MS Excel Features

  • Now On the Insert tab, in the Charts group, click the Pie symbol and then click Pie.

TOP 10 Excel

  • After clicking, the following image will show the result.

Microsoft Excel Features

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.

3. Mixed or Combination Type Charts

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:

  • Select the range A1: C13.

Excel Capabilities

  • Now, On the Insert tab, in the Charts group, click the Combo symbol and after that click on create custom combo chart.

Microsoft Excel Features and Functions

  • Now, insert chart dialog box will appear. Now, for the rainy days series, select clustered column as the chart type. After that for the profit series, select line chart type. Now, plot the profit series on the secondary axis.

Advanced Excel Formulas

 

  • After clicking OK, it will display the following result:

Advanced Excel Functions

Mixed or combination chart is one of the coolest Microsoft Excel features and functions.

4. Data Validation

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.

Advanced Excel Features

  • Now, to create Data Validation Rule, Select cell C7 and now on the Data tab, in the Data Tools group, click Data Validation.

Advanced Excel Tips

  • Now, On the Settings tab, in the allow list, click the Whole number and after that in the data list, click between. Now enter the minimum and maximum values.

Advanced Excel Formulas List

  • Now, after this click on Input Message and set the input message. After finishing this click on the Error Alert tab and set the error message to display if a user enters other than whole numbers or some other text. Following two figures show you the result.

Advanced Excel Formulas and Functions

Now, try to enter a number higher than 10. Following image is showing us the result:

Advanced Excel Functions List

5. IFERROR Function

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.

Syntax

=IFERROR (value, value_if_error)

Example:

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.

Advanced Excel Functions for Data Analysis

Now if we use iferror function then if a cell contains an error, an empty string (“”) is displayed.

XLS Features

6. Remove Duplicates

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.

Microsoft Excel Features

The following dialog box will appear. Now, leave all checkboxes checked and click OK.

XLS Features

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.

7. Conditional Formatting

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.

Top 10 Excel

8. MINVERSE

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.

Microsoft Excel Features and Functions

The result is displayed in cells from F1 to I4.

Excel Capabilities

9. DB Function

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.

Microsoft Excel Features

Now after entering the above formulas in each cell the following figure is showing us the result.

Microsoft Excel Functions

10. Data Visualization

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.

Excel Features and Functions

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:

Microsoft Excel Functions

11. Complex Function

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.

Excel Features

12. MKDIR

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.

13. IF and OR function

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.

Excel Functions

Now when you press enter and drag the fill handle, the Pass or Fail result will be displayed. Check below image.

Excel Features Microsoft

14. Hyperlink

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.

Excel Functions and Features

Insert Hyperlink dialog box will appear. Now, to create a link to an already created Excel file, select a file.

Microsoft Excel Functions and Features

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.

Advanced Excel Functions and Features

15. Transpose

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 )

Microsoft Excel Advanced Features

After entering function. Below image is showing us the result.

Microsoft Excel Functions

16. INDIRECT

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.

Advanced Features Excel

17. FORMAT

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:

Excel Format Function
The above figure shows us the use of the Format function.

18. CONCATENATE

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:

Excel Function Concatenate

19. Paste Special

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.

Excel Function Paste Special

20. TRIM

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.

Syntax: TRIM(text)

Excel Function Trim

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!

21. ROUND Function

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.

Microsoft Excel Round

22. PROPER

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.

Microsoft Excel Proper

23. NOW function

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.

Microsoft Excel Now

24. CHOOSE function

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.

Microsoft Excel Advanced Formulas Choose Option

25. Named Ranges

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:

  • Click on the Formulas menu option on the ribbon.
  • Now in the next step, click on the Create from Selection button.
  • Now in the third step, Select the ranges to give names.
  • The following image is displaying the use of Named Ranges.

Microsoft Excel Created Named Ranges

26. Quick Feature

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.

Microsoft Excel Quick Tool

27. Input Restriction

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.

Microsoft Excel Data Input Restriction

28. New Shortcut Menu

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.

Microsoft Excel Menu Function

29. Hide Data

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.

Microsoft Excel Hide Data Function

30. PMT and IPMT

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.

Microsoft Excel Mort Function

 

About the Author

ByteScout Author

Prasanna Peshkar

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.

 



prev
next