Since its launch in 1987, Excel has been the dominant spreadsheet tool in the market. It is used by millions of professionals worldwide daily.
Excel is loaded with some amazing features that can really boost your productivity and save a lot of time.
Here is an infographic by Excel MVP Sumit Bansal that explains five Excel features that are easy to use but does a lot of work that would otherwise take a lot of your time.
The infographic covers the following Excel features:
- Remove Duplicates
- Conditional Formatting
- Text to Columns
- Find and Replace
- Paste Special
Remove Duplicates in Excel
If you work with data in Excel, handling duplicates wouldn’t sound unfamiliar. These duplicate data points can create a lot of issues if used in doing calculations or creating data models.
While it’s easy to find and remove duplicates in small data sets, doing do manually with large data set could take you a lifetime.
Fortunately, the Remove Duplicate feature in Excel does this in seconds.
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.
Pro Tip: As a best practice, before using the Remove Duplicate feature, 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, as the name indicates, formats when a specified condition is met.
This is really useful in adding 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 an Area Sales Manager and you want to know who all in your team achieved the sales target, you can do that using conditional formatting.
Another great use of Conditional Formatting is in creating Heat Maps. These are extensively used in reporting and management dashboards.
Pro Tips: Conditional Formatting is volatile and may slow down your workbooks. While the impact is negligible in slow data sets, be a bit cautious when applying it to large data sets.
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 is a superfast way to split the content of the cells.
For example, if you have the full names (first name and the last name) in the same cell 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 allows you to quickly find cells with a specified value/string and replace it with another specified value/string.
For example, if you have a dataset where you have referred to a company as XYZ, and you have to change all the instances of XYZ to XYZ Limited, you can do that easily 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).
But what if you only want to copy the formatting or the comments or the formula?
Paste Special comes in handy in such cases.
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 are simple to use but can save a lot of your time.
Infographic Source: Trump Excel
