Worldwide, millions of professionals use Excel spreadsheets to crunch numbers, maintain records, create financial models, track progress, and do a lot more.
While there are many advanced features available in Excel, an average user needs only a couple of these Excel features to get most of the tasks done.
Here is an infographic by Excel MVP Sumit Bansal on using 5 common Excel features that can help save time and make the users highly productive.
The infographic covers the following Excel features:
- Remove Duplicates
- Conditional Formatting
- Text to Columns
- Find and Replace
- Paste Special
Remove Duplicates with Excel Features
If you work with data, finding and removing duplicates can take up a lot of your time. Remove Duplicate feature can quickly identify and remove duplicate records from a tabular data in Excel.
For example, if you have sales transaction data where the same sale has been recorded multiple times, you can use Remove Duplicates to get rid of all the repetitions at once.
Before using Remove Duplicate feature, as a best practice, create a backup of the original data set.
Where to find it: You can find the Remove Duplicate feature in the Data tab in the ‘Data Tools’ group.
Conditional Formatting
Conditional Formatting can add a layer of visual analysis to your data set. You can specify the conditions and when these conditions are met, Excel automatically highlights those cells.
For example, if you’re a teacher, and you want to highlight the score of all the students who scores less than 35, you can quickly do this using Conditional Formatting in Excel.
Another great use of Conditional Formatting is in creating Heat Maps. Based on the specified range of values and colors, Conditional Formatting can automatically apply background colors to these cells. Heat maps are extensively used in reporting and management dashboards.
Make sure you only use Conditional Formatting only when necessary. It is a volatile feature and if used on a large range of cells, it may lead to a slow workbook.
Where to find it: You can find the Conditional Formatting options in the Home tab in the ‘Styles’ group.
Text to Columns
Text to Columns can be a great time-saver if you want to split the content of the cells.
For example, if you have the full names and you want to split these into the first name and the last name, you can do that in seconds using Text to Columns. Similarly, you can also split an address into house number, locality, city, state, pin code, etc.
Where to find it: You can find the Text to Columns feature in the Data tab in the ‘Data Tools’ group.
Find and Replace:
As the name suggests, Find and Replace would allow you to quickly find cells with a specified value/string and replace it with another specified value/string.
For example, if you have a data-set where you have referred to a company as ABC, and you have to change all the instances of ABC to ABC Limited, you can do that easily using Find and Replace.
You can also use Find and Replace to change cells references in formulas. For example, if you have a worksheet with formulas and you want to change the reference from A1 to A2 in all the formulas, you can do that using Find and Replace.
Where to find it: You can find the Find and Replace feature in the Home tab in the ‘Editing’ group. It will appear as a drop down when you click on Find & Select.
Paste Special
When you copy and paste a cell in Excel, it copies everything (including the value, formula, and formatting).
Paste Special allows you to copy from a cell (or range of cells) and paste selectively in destination cells. Using Paste Special, you can copy and paste only the value, or the formatting, or the formula, or the column width.
Where to find it: To access Paste Special options, you first need to copy a range of cells. Now right-click on the destination cell and click on Paste Special from the menu. It opens the Paste Special dialog box.
These five Excel features can take care of a lot of day-to-day work most people in Excel.
