Useful Excel features functions and formulas

Microsoft Excel is the most useful and easy tool for business analysts. It has many useful formulas, features, and bundles of interactive charts. But, most of us are not known of all of them and there are some more features that are powerful and easy to use to make our work simpler. You might not have noticed some of the useful Excel features like Sparklines, Slicers, Conditional Formatting, and other formulas which add value to your work. In this article, I will take you through them and will give you an idea of what those are and how to use them.

Useful Excel features functions and formulas

Excel Features: Sparklines, Slicers, Conditional Formatting, SMALL LARGE, Remove Duplicates

Among many Excel features, there are some hidden features that are easy to use and you may not know all of them. Without any further delay, we will look at 5 such Excel features.

Sparklines Excel Feature

Sparklines were first introduced in Excel 2010 and are used to represent visualizations for the trend across the data in a row. It fits in a single Excel cell and saves space on the worksheet. This is a cool feature and is very easy to use. Calculating the trend for row data and placing the visualization in single excel is really a great feature to use.

In order to create your own Sparklines, select the range of data. Click insert on the ribbon and select the type of Sparklines (Line, Column or Win/Loss). Next, enter the range of the target where you want to show the Sparklines. For more information on how to create Sparklines, visit Office Blogs.

Conditional Formatting Excel Feature

Conditional Formatting is a well-known feature of Excel. It is used to visually present the data based on the conditions met. It is also useful to create heat maps. This would be helpful to find interesting patterns by exploring the data effectively.

To create the heat map, select the data and head over to the ribbon. Under Home, click Conditional Formatting and then click Color Scales. Now, pick the color scale. You can even set the color scale by editing the formatting rule. For more information on Conditional Formatting, visit Office Support.

SMALL and LARGE Excel Functions

We all know about MAX and MIN functions. They give you the maximum and minimum values of the selected data respectively. But, in order to find the 1^st, 2^nd, 3^rd or nth largest or smallest value of the selected range if data, we can make use of LARGE and SMALL functions respectively.

In this example, in order to find the top two products for each month, we made use of MATCH and INDEX functions along with LARGE and SMALL functions. For more information, visit SMALL and LARGE functions.

Remove Duplicates Excel Feature

Do not blame me for mentioning this feature in this list. It is very important to get rid of redundant data from the available huge amount of data. It is one of the best ways for cleaning and organizing the data and so thought of having it in this list of powerful Excel features. Removing Duplicates feature is helpful to remove duplicates which is the most important problem which we face.

To remove duplicates, select the data and head over to the ribbon. Under Data, click the Remove Duplicates button and see the data without duplicates. For more information on how to Find and Remove Duplicates, visit Office.com.

Slicers Excel Feature

Slicers act as visual filters. It helps you to visualize the subset of data as a connected chart or as raw data. For example, if you want to show the trend of sales of various products, then you can create the interactive sales trend chart using Slicers. Based on the product you select, the respective chart is shown. Slicers were first introduced in Excel 2010 and enhanced a lot in Excel 2013.

In Excel 2013, if you want to add Slicer to your charts, select the data range and click on insert > Slicer. Now, select the part of the data you want to use as a filter. In the image above, Product column is used as a filter. How here for more information on how to use Slicers.

Also read:

What are 10 features of Microsoft Excel?

Microsoft Excel comes with countless features and options that you can use to get things done in various situations. However, when it comes to the best ten options or features, you can check out Conditional Formatting, Themes, Table within table, PivotTable, Charts, Equations, Data Visualizer, 3D models, etc. The best thing is that Excel allows users to install add-ins so that they can enrich the user experience.

What are the advanced features of MS Excel?

Excel includes some easy-to-use and advanced features together. For example, you can insert a table within the spreadsheet, use countless functions, validate data, use input restrictions, match with index, and more. The best thing about Excel is that you can use any function in a nested method. In other words, you can apply one function multiple times and dynamically.

How many of you have used these powerful and useful Excel features? If you want to add more features to the list, please let us know through comments.