Microsoft Excel is the most useful and easy tool for business analysts. It has large number of 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 which 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 on what are those and how to use them.
Most Useful Excel FeaturesAmong many Excel features, there are some hidden features which are easy to use and you many not know all of them. Without any further delay, we will look at 5 such Excel features.
SparklinesSparklines were first introduced in Excel 2013 and are used to represent visualizations for the trend across the data in a row. It fits in a single Excel cell and saves the 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 the 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 FormattingConditional 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 the 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 FunctionsWe 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 1st, 2nd, 3rd 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 DuplicatesDo 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 was introduced from Excel 2007 and 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 what you see the data without duplicates. For more information on how to Find and Remove Duplicates, visit
Office.com.
SlicersSlicers act as visual filters. It helps you to visualize the subset of data as a connected chart or as a 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, 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.
For more details on these features and to download the example workbook, visit
Office Blogstwc
