Mastering Microsoft Excel to Enhance Productivity

Microsoft Office is one of the most popular productivity suites worldwide comprising several softwares amongst which Excel is the most powerful. It can help you accomplish your tasks more efficiently and quickly. Therefore it is important to learn how to use the features of Excel effectively for improvement in output.

This article guides you through a few tips and tricks to help you understand the capabilities of MS Excel.

Let's Start :

1. Interface of Excel :

Let us navigate the interface before we learn other formulae and shortcuts.

Whenever we start using Excel sheet we can use enter key to shift to below cell and Tab key to move to next cell.

To add another sheet in a Workbook, please press alt+Shift+F1.

By pressing the Ctrl key and the Up, Down, Left or Right arrow keys together will help you to go to the last used cell in that particular direction.

2. Freeze a Row or Column :

This feature is very useful whenever you work on large data excel spreadsheets, specifically with high row count. 

This wonderful feature will help you to freeze the heading column while you scroll down to edit the data. It makes the heading visible while working. 

In order to freeze the heading , Go to View > Freeze > Freeze Top row.

3. Mastering Formulae in MS Excel :

Formulae are the excellent features in MS Excel which can really help you complete the task efficiently and easily, in fact  the USP of Excel. 

For new users, you can start with basic ones like SUM, IF, CONCATENATE, AVERAGE.

Some features like conditional formatting, removing duplicates, Vlookup and Hlookup can really be helpful while working on large data sheets. Becoming skilled with these  can really make your life easier.

Here is the list of few commonly used FORMULAE :

• SUM,SUMIF,COUNTIF
• Using FILTER option to get any particular data from a Column
• CONCATENATE,AND
• RIGHT,LEFT 
• SORT by A to Z in any column
• VLookup, Hlookup and many more

Click here for all formulas and functions.

4. Applying Conditional Formatting to View Data :

Conditional Formatting is one of the best and most popular features used in Excel. It helps the user to understand the data properly by applying various conditions.

Below are few actions you can take with conditional formatting:

1. Highlight Cells with particular text

2. Edit a cell with specific conditional formatting rule

3. Find and Remove duplicates

4. Make a rule dependent on another cell

5. Highlight any row

6. Highlight a row or column within a range of values or dates

5. Some Useful Shortcuts to Simplify Your Task :

By learning these shortcuts you can easily simplify your task and manage it properly. Without touching the mouse you can perform the task very easily.

If you are new to Excel, then start by the general shortcuts on keyboard. The most common ones are Ctrl + C and Ctrl + V to copy and paste. And Ctrl + Z to undo the last action. Ctrl +F to find, Ctrl+H to Find and replace, Ctrl+D to duplicate previous data in the next cell and many more.

One of my favorite shortcuts is Ctrl + ; to enter today's date in a cell. 

6. Pivot Tables :

Organize and summarize your data sheet using Pivot Table.

This feature can be used for summarizing your data with its count. For eg. If you wish to prepare a consolidated data of the number of products sold in the month of April, then you can apply the Pivot table and then select count from Product column to achieve the results. 

7. Macros :

Click here to learn about Macros.