Save Time With These 5 Useful Excel Features
Excel spreadsheets are used by millions of people around the world daily. Over the years it has evolved to become one of the best data analysis tools out there.
With the addition of features such as Power Pivot and Power BI, it has taken a huge leap to be the best tool in the data analysis world. Also, there are formulas available to combine or merge spreadsheets together such as Vlookup and Index Match
Despite all these powerful features, most of the people use Excel as a data entry tool.
While it’s great for data entry, it’s much more that.
Excel also has some easy to use features that can boost your productivity and save a lot of time.
Here is an infographic by Excel MVP Sumit Bansal that explains five Excel features that you should start using to save time and be productive.
The infographic covers the following Excel features:
- Remove Duplicates
- Conditional Formatting
- Text to Columns
- Find and Replace
- Paste Special
Remove Duplicates in Excel
While working with data in Excel, you are likely to end up with duplicate data points.
If these duplicate data points are not removed, it may lead to a lot of issues (especially when using this data for calculations).
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 allows you to automatically format/highlight cells that meet a specified condition.
For example, if you’re a class teacher and you want to highlight all the student names that scored less than 35 in your subject, you can easily do this using conditional formatting.
Another great use of Conditional Formatting is in creating Heat Maps. These are extensively useful reporting and management dashboards.
Pro Tips: Conditional Formatting is volatile and may slow down your workbooks. Be cautious while applying it to large datasets.
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 an easy way to split the content of the cells.
Suppose you have an address in a cell that includes house number, street, locality, city, state and pincode. Now if you only want to get the pincode from the address, you can use text to columns to split the content of the cell.
It saves time as it can do this splitting for thousands of cells in seconds.
Where to find it: You can find the Text to Columns feature in the Data tab in the ‘Data Tools’ group.
Find and Replace
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 ABC to ABC 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.
When you copy and paste a cell in Excel, it copies that cell exactly (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.
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 are five Excel features that can help you save time by doing a lot of heavy lifting in your day to day work. Excel is full of such features and just by knowing their basic usage, you can increase boost your productivity dramatically.
Infographic Source: Trump Excel