MS Excel is undoubtedly the most popular software used for data analysis and handling. It enables professionals from different backgrounds to effortlessly manage data, big and small. Despite its popularity, however, there's a hidden treasure trove of functions and shortcuts that a large number of users are unaware of. These tips can make it easier to handle work and improve productivity several times over. Here are some simple yet effective functions in Excel:
1. Paste Special
People often need to copy the content of one cell and paste it into another. But the data of a cell may have other attributes, such as formatting, value, formula, comments, and many more. To copy only certain characteristics, one can use the 'paste special feature.
To use this feature, you need to go to the "Home" tab in the toolbar on the top left. You will find it under Clipboard. Right-click or press Ctrl+Alt+V. The Paste Special box offers various options like Paste Values, Paste Values, and Number Formats & Transpose.
- Paste Values: If you want the value of a cell and not the entire formula, go for Ctrl-Alt-V-V and hit enter. Do bear in mind, the default action of Excel is to copy a formula.
- Paste Values and Number Formats: This transfers the format of the original cell. This feature is beneficial when you want to copy over dates or a currency and not the original formula of the cell. The shortcut for this is Ctrl+Alt+V+U & hit enter.
- Transpose: This enables you to flip rows and columns around in seconds. You can easily turn a row of numbers vertically or vice-versa by simply copying and then using Paste Special – Transpose. The shortcut for Transpose is Ctrl-Alt-V-E & hit enter.
2. Go To Special
This feature allows you to locate and select cells of the same characteristics. You can select cells that have characteristics like comments, formulas and numbers. It will help you to identify errors or highlight content. It is in the Editing section under 'Find & Select.' The shortcut for this is Ctrl+G followed by Alt+S.
For Instance, you have a column of cells with some blank cells which you wish to get rid of. First, select that column, then select Go To Special, then Blanks, and you'll see that all the blank cells are highlighted. Now, right-click on one and select Delete. All blank cells would be gone. The shortcut in excel for Go To Special, Blanks is Ctrl-G followed by Alt-S-K and hit the enter button.
3. Flash Fill Function
Excel came up with a great feature in 2013. If you have two columns of names (first and last) and you require it to put both columns together. Type manually for the first row, and you will find the rest of it appear on its own. This makes dealing with names a lot easier and quicker.
4. Use of Apostrophe
The apostrophe (') tells Excel to treat the data following it as it is and not to pick it up as a formula. If the first character in the cell is +, - and =. Excel treats the cell differently and starts looking for cells to refer to. This can be disorienting. That's where our next Excel hidden gem comes in. The apostrophe (') is Excel's solution to displaying data that Excel would otherwise treat differently. Apostrophe ensures data is displayed just the way it is entered.
If you use Excel a lot, you will understand the frustration of looking for your cursor. To give reprieve to us all, Excel provides a nifty hack just to hit F2. This activates the cell you are working in. It will bring you directly to that cell.