Do You Know These 7 Tricky Microsoft Excel Features?

Microsoft Excel is composed of many useful features that are ideal for managing and utilizing data. Out of these, there are quite a few MS Excel functions that are lesser known than others but give off incredible results. Here are the top 7 MS Excel features that you may not be familiar with.

In all probability, skilled users may be using these lesser known Microsoft Excel features on a regular basis; but the same may not be true for learners like you. Believe us! These features would introduce you to fascinating methods of accomplishing management tasks and presenting calculations in the most sophisticated way. 

Feature 1: Pivot Table

Trait: This feature is an essential tool in Microsoft Excel. Pivot tables in Excel help in presenting datasets as a list or in the tabular form without the need for typing any formula or function. 

Process: Your table of data should not contain any blank row. Click on the table and select “Insert” >“Pivot Table”. Select every cell that you want to evaluate. Then click on “OK” (First figure). A different sheet would open where you will have to make use of the presented right bar (Second figure). Then, you can go on making the table by bringing in fields from the top end to the boxes given at the lower end of the right side bar. 

Suitability: Pivot tables are best suited for presenting managerial reports on data that require analysis and evaluation. 

pivot table

pivot table feature

Feature 2: Conditional Formatting

Trait: This less-used MS Excel feature comes in handy in various situations, for instance, when you are reviewing a report with your upper level manager. Conditional formatting allows you to display results at a glance. With the help of this Excel feature, you can point to the values or areas that you want to show directly.

Process: Firstly, select the entire cell range that you desire to filter and show. Then, go to “Home” and click on “Conditional Formatting”. In the given example, say, you want to highlight the sales figures that are less than $ 60000. Just click on “Highlight Cell rules” and then on “less than” after going to “Conditional formatting”; then, type 60000 on the area given for typing. You will see the following result as depicted in the figure below. 

Suitability: This feature is ideal for short but important meetings with your boss. 

conditional formatting

Feature 3: Remove Duplicates

Trait: “Remove Duplicates” is very helpful for big-sized organizations managing huge datasets pertaining to their employees and other stakeholders. This feature eradicates a vital common problem of big organizations, that is, duplication of data. 

Process: Firstly, you need to select the entire set of data that you want to sort or evaluate for removing duplication. Then, click on “Data” and opt for the “Remove Duplicates” option. Once done, you will love to watch Microsoft Excel follow your command and complete the task.   

Suitability: This MS Excel feature would help in over viewing your task when you are running short of time.

remove duplicates

Feature 4: Changing the Looks of Comments

Trait: Changing the shape and color of comments in an Excel workbook is a feature that is seldom undertaken by common users. But what happens when your presentation is primarily based on the comments placed in a workbook? The comments have to look attractive, right? Here’s how to go about the task.

Process: In Excel 2007, add the “Quick Access Toolbar” and click on “More Commands”>”All Commands”. Now search for an option “Change Shape”. After selecting the same, click on “Add”>”OK”- You will find the “Change Shape” option in your Quick Access Toolbar. 

Now, right click on the cell with the comment in question and select “Edit Comment”. A rectangular box with the comment would appear; thereafter, click at the box’s corner and select the “Change Shape” option from the toolbar. Choose the shape of your choice, click, and you are done! 

changing the looks of comments

You may even change the color of your comment boxes. See how.

Suitability: This feature is of great help for business meetings that can prove to be long-drawn and boring.

Feature 5: Format Painter

Trait: This is an Excel feature that can be used for copying a particular format in a wide range of cells/ other cells. 
Process: Click on the cell whose format you wish to copy. Then, click on “Format Painter” on the ribbon under “Home”. Now, select the range of cells that you would like to flaunt the same; and you are done!

Suitability: This feature is extremely helpful for saving upon formatting time when you are in a rush.

Feature 6: Only Blank Cells Formatting

Trait: This feature is helpful in case you desire to format the blank cells only or require the same format across a wide series of data. 

Process: Select the entire range of data consisting of both blank and filled cells. Then, press f5. A box will appear; wherein you have to click on “Special”>”Blank”>”OK”. 

only blank cells formatting

All blank cells would be selected by implementing this MS Excel feature. Next, you may like to press “Ctrl+Enter” for writing the same thing (as in a given cell) on these blank cells. 

only block cells of excell formatting

Suitability: This feature is beneficial for quick formatting before an important surprise meeting. 

Feature 7: Scaling

Trait: Scaling is an important feature required for printing your worksheet in a single page, both skillfully and elegantly. Many a times Excel users complain about not being able to print what they want exactly, and as a result, all their hard work goes to waste. Scaling would help them solve this problem. 

Process: Select “Page Layout”. Then, go to the “Scale to fit” area where you need to work on the options of “Width” and “Height”. Adjust the values as you want and print your desired spreadsheet directly. 

Suitability: This is an obvious feature for enhancing the presentation quality of your worksheet. 

So, look for these lesser known MS Excel features that you may not be using when you are working on spreadsheets again. You will not be disappointed. 

About Author
Uma Daga