In today’s fast-paced business world, time is money. For business workforce handling data on a daily basis, Microsoft Excel is quite often an indispensable tool. And though many of us are acquainted with the basics of Excel, there are numerous tips and tricks Excel holds in store that could not only save time but also boost productivity. In this blog, we’ll explore some of the most useful features Excel has to offer.
#1: AutoFill Feature
A simple yet powerful feature in Excel is AutoFill. Using this feature, users can swiftly fill a series of cells with data such as dates, text, numbers, repeating formulas or patterns.
How to use it? That’s easy. If you start entering some data in a cell, you’ll notice a small square at the bottom right part of the cell. This is called the fill handle. Simply drag the fill handle across rows or columns you want filled with same or patterned data. Excel will decode the pattern and auto fill your series for you.
No need to repeatedly type data, just drag the fill handle and you’re done.
#2: Group/Ungroup Excel Feature
A simple alternative to hide certain columns when working on an Excel file is to group data. You can find this feature under the ‘Data’ tab.
All you need to do is to highlight the rows that you wish to group together to hide and click on ‘Group’. When you group data, you will see a broadened left column in your Excel sheet. The ‘+’ sign on that left column is where you can click to see the hidden grouped rows.
To Ungroup a selection, simply highlight the grouped cells and click ‘Ungroup’.
#3: Unhide All Hidden Rows & Columns
Unhiding rows or columns can be a challenge and if you have multiples.
Here’s a simple shortcut to unhide all hidden rows: Press ‘Ctrl + A’ to select the entire sheet and then ‘Ctrl + Shift + 9’.
To unhide all hidden columns: Press ‘Ctrl + A’ to select the entire sheet and then ‘Alt + H O U L’ in this order, one at a time while keeping the Alt key pressed.
#4: Excel Feature for Freezing Panes
This is a great functionality in Excel which allows users to easily navigate large excel spreadsheets with extended column widths without losing views of the headings/definitions for each column. You can find this option under the ‘View’ tab.
Now, you can make use of this option in three different ways:
Freeze Top Row
This option will freeze the top row in its place. This way, when you scroll down the sheet, the top row will always be visible.
Freeze First Column
Similar to the top row option above, this is an option to freeze the first column. This way, when you move to the columns to the far right of your Excel sheet, the data in the first column will still be visible.
Freeze Panes
This option allows users to be able to move anywhere on the Excel while still being able to see the rows and columns frozen.
For this, all you need to do is click on a cell (let’s say B2) and click ‘Freeze Panes’. This will freeze the top row and first column on the sheet.
Similarly, if you click on C2 and freeze panes, it will freeze the top row and first 2 columns on the sheet. This gives flexibility to accommodate your use case of this feature.
Excel data sets can grow huge and navigation through these rows and columns of data can sometimes become challenging.
#5: Navigation Shortcuts
Excel offers some shortcuts to make navigation easier. You can make use of Ctrl + Arrow keys to navigate to the first and last row or column. For example, Ctrl + ↑ will take you to the top row of the sheet. Similarly, Ctrl + ↓ will take you to the last row of the sheet. Ctrl + ← moves to the first column and Ctrl + → to the last column of the sheet.
To go to a specific cell or row, you can also type ‘Ctrl + G’ feature and enter the cell or row reference and this feature will help you get to that particular cell or row.
#6: Data Validation
This Excel feature is very useful when dealing with data because it allows you to validate the data that you enter in a cell.
To use this feature, go to the Data tab, select ‘Data Validation’. This will open an input box where you can define how you want certain fields validated so only allowed data can be filled in. This feature can also be used to create a dropdown from a user-defined list.
Validated data reduces errors by allowing only the allowed data to be entered. This also saves time required for cleanup of incorrectly filled data.
#7: Adding Comments on Cells or Data
Often times, there is need to provide context for certain data in a cell. To add a comment on a certain cell, simply press ‘Shift+F2’. This will open a comment box to enter your comment.
Adding comments to the cell creates a small red triangle on the top of the selected cell and when clicked, it opens up the comment which contains the explanation.
This feature is quite helpful in reviewing Excel sheets as all comments can be opened up and worked on in the Review tab of Excel, thereby providing better collaboration and review process.
#8: Excel Feature to Get Rid of Duplicate Data
One large part of working with large datasets is the hassle of cleaning up data and removing duplicate data.
Excel provides a simple yet efficient feature to tackle this problem. To remove duplicates from a data set, simply select the range where you want unique values only. Go to the Data tab and then click ‘Remove Duplicates’. This will give you an option to choose the columns in your selection where you want the duplicate data removed.
Once you select the columns and press ‘OK’, you will have unique values only in these columns.
#9: Use Wildcards When Searching
Did you know that you don’t need to know the exact phrase or word when searching in Excel but you can use wildcards instead. For instance, let’s say you have a list of employees and you want to search names of all employees who have “Al” in their name.
To do this, use ‘Ctrl + F’ and enter *Al* and click ‘Find all’. You’ll be able to find all those names with cell references listed in the Find box.
You can also use the Replace feature to replace all findings with something different.
#10: Shortcut for AutoSum
You don’t always have to type out the SUM function every time you need to add certain values. There is a shortcut for that.
Select the range of cells that you want added and use ‘Alt + =’. Excel will automatically create the sum in the next cell of the range.
Even if you don’t want to select a range, you can use the above to have the SUM formula ready for you on the desired cell so you can enter the cell references as needed.
Microsoft Excel is packed with multitude of such amazing features and shortcuts to boost productivity. Whether you are working on crunching sales numbers, building financial models or using Excel to track progress of projects, these Excel power features and shortcuts will help you stay on top of your game.
Read more interesting blogs relating to Office 365 skillset here.
