How to excel in Excel? (Part 1 in the Excel Series)
A simple and integral tool, MS Excel is a part of the software suite of Microsoft Office. A basic spreadsheet, Excel can perform wonders, sounding too good to be true at times. All you need is a formula and your repetitive, monotonous tasks will be done automatically. From updating data to merging two sheets and combining information from multiple cells, Excel is a pro at finding solutions and easy way outs.
Even though it is quite a famous tool, widely used by people from various sectors, mastering the art of using Excel isn’t everybody’s cup of tea. To help you be one in their league, we bring to you a few hacks to excel in Excel.
1. Pivot Tables
Pivot tables come in handy when you have to reorganize the spreadsheet’s data. On the basis of what you’d like the pivot table to do for you, it can compare different values or sum them up. For instance, if you are given a particular set of data and you need to figure out the number of students in a particular house, pivot tables can do that for you.
To create one, go to Data, then select Pivot Table. Excel populates your Pivot Table automatically, but you always have the option of modifying the order of your data. After that, you’ll have four options to choose from according to your requirements: Report filter, Column labels, Row labels and Value.
2. Filters
Sometimes, all you need to look at is specific data and not everything that exists on the sheet. You can then categorize the data that fits into certain criteria. This is where the role of filters come in play. They allow you to look at certain rows or columns one at a time, thereby avoiding unnecessary confusion.
To add a filter, click the data tab and select ‘Filter’. To arrange your data in either ascending or descending order or to select which specific rows you want to see, you can click the arrow beside the column headers.
3. Remove Duplicates
Often, the data you’re dealing with consists of duplicate values. You may have a large data set with repetitive, redundant names or contact numbers. To remove the excess content from the spreadsheet, this feature helps. To delete them, select the rows or columns from which you need to remove the duplicates. Go to Data tab next and select ‘Remove Duplicates’ from the tools. A pop-up will question the data you want to work with, click ‘Remove Duplicates’ and there you are! The duplicate values will be deleted.
4. Transpose
There might arise scenarios when you’d be required to shift the content in the rows to that in the columns. Doing that manually will require a lot of effort and time, copying and pasting each header into rows or columns. But the transpose feature eases this task out for you. It allows you to simply move your data stored in rows to columns or vice-versa.
To make use of this feature, select the column that you want to transpose into rows. Then right-click the cell where you want your row to begin and select ‘Paste Special’. In the drop-down list that you see next, select transpose and click OK. Tada! Your column will successfully be transferred into a row or the other way round.
5. Text to Columns
In cases where you’re required to split names or extract names from email IDs, what would you do? The entire process of copy-pasting each name into two columns is tiring and tedious, especially when it comes to data sets having thousands of rows and columns. This is where Excel pulls you out of the trouble. To make this possible, select the column that you need to split. Go to the ‘Data’ tab next and select ‘Text to Columns’. A pop-up window will appear with more information.
There are two options: ‘Delimited’ or ‘Fixed Width’. Delimited comes in handy when you want to break the column up, on the basis of characters like spaces, tabs or commas. Whereas, Fixed Width helps when you want to select the precise location in each column where you’d like the split to occur. To segregate names on the basis of the first and last names, you can use Delimiters.
Excel is one of the basic and easiest tools offered by Microsoft Office. We’ve mentioned quite a few of the essentials which will help you excel in Excel. Using these can help in easing your workload. Refer to these to save time, even if that means gaining a few extra minutes on your hands.
Keep them in your pocket to look back as and when you need and like. Be the pro you ought to be!