7 Useful Microsoft Excel Tricks You Must Know

We all know that the MS Excel is one of powerful tool to maintain records, generate reports, create dashboards etc. There are many in-built formulas and controls available in MS Excel which can make our life easier. I’m a big fan of Excel and I use it for my day to day reporting, handling projects and to maintaining records.

1. Transpose Data from a Row to a Column

This feature can be used when you want to transpose the data for better display or any other purpose. This is very cool feature as you don’t need to retype the data.

Follow the below steps…



Go to Paste – > Transpose (You can also Right Click – > Paste Options – > Transpose)


Result:


Note: Function will only be available once you copy the data.

2. Input Restriction with Data Validation Function


Sometimes you may need to restrict values in the column in order to maintain the validation. For an example, in below table, I don’t want the age of the person to be less than 20 or greater than 50.

Follow the below steps to put data validation to your column
Select the column and go to Data ->Data Validation



Select the minimum and maximum values, Validation Message and Error Alert for customized error.

Result:

Note:You can explore more by selecting different values in Allow and Data drop-down.


3. Generate a Unique Value in a Column

Sometimes you may need to filter the unique values from the repeated data and it can be easily done by using excel inbuilt feature.

Steps to filter unique values from the repeated data:
Go to Data -> Advance Filter


You can filter unique values in the same column by selecting “Filter the list – In place” or filter the record in different column by selecting “Copy to another location”


Note: Don’t forget to select “Unique records only”.

4. Speedily Move and Copy Data in Cells

If you want to move a column data quickly then you can simply select the column and point the mouse cursor to the column border and drag freely to required place.

1. Transpose Data from a Row to a Column

This feature can be used when you want to transpose the data for better display or any other purpose. This is very cool feature as you don’t need to retype the data.

Follow the below steps…



However, you can also quickly copy and paste the selected column by just pressing Ctrl while dragging the column.


5.Entering Line Sparkline Microcharts

Line Sparklines are mini charts placed in single cells, each representing a row of data in your selection. This could be used to show the trends of multiple items together. This enhance the view and helps in understanding the trends. To make one, you need to first select the data from which you wish to create a sparkline, and then go to Inset ->Line.



There you will be asked to enter the destination location of your sparkline chart. Select the destination location to draw the Sparkline chart. Once you are done, you can see amazing sparkline chart ready.


6.Conditional formatting

This is one of the magical Excel built-in features that I often use to prepare dashboard or management reports. It’s very simple to use, however, one can customize as per their need in order to get the maximum benefit. Getting back to our previous table, Select the column on which you want to apply the conditional formatting.



As you can see, there are lot of option that we can use but I’ll try to cover options which I find enough to prepare excellent reports. No doubt once can go beyond the limit by exploring features.

There are 3 easy to use feature in conditional formatting:

1.Data Bar
2. Color Scales
3.Icon Set

1. Data Bar: It is used to represent the value in the cell. Higher the value, longer the bar.

2. Color Scale: Apply the color gradients to a range of cells, color indicates where each value falls within that range.

3. Icon set: It is used to represent values in cells through icons.

7.VLOOKUP

VLOOKUP is one of the powerful feature offered by Excel and I’m sure most of you already aware of it.Formula format for the VLOOKUP is as below:

1. VLOOKUP (Lookup value, table array, col_index_num, [range lookup])
2. Lookup value: The value to search for in the first column of the table.
3. Table array: Two or more columns of data that are sorted in ascending order.
4. Col_index_num: The column number in the table from which the matching value must be returned. first column is 1.
5. [Range lookup]: Optional. Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this parameter is omitted, TRUE is the default.

Note:

  • If you specify FALSE for the approximate_match parameter and no exact match is found, then the VLOOKUP function will return #N/A.
  • If you specify TRUE for the approximate_match parameter and no exact match is found, then the next smaller value is returned.
  • If index_number is less than 1, the VLOOKUP function will return #VALUE!.
  • If index_number is greater than the number of columns in table, the VLOOKUP function will return #REF!.

Example: Let’s go back to our first table (let’s call it Table A) which has name, age and Rank details. I have created on more table (Let’s call it Table B) with department and its fixed basic salary. Now, I will add two columns i.e. Department, Salary in table A and I will write vlookup formula on Salary column to get the salary from Table A based on department selected in column D.



You can also use the various charting option available in excel to showcase the data in the pictorial form.