Top 4 Tricks in Excel you should know

MS-Excel is a really powerful tool and most heavily used all over world by every professional and in all sectors from corporate to education. Today we will let you know the top 3 tricks in excel which will improve your performance in terms of time and quality.

1. PIVOT table is one of Excel’s most powerful feature. Using PIVOT table you can create your own extract from a large detailed data set.

For example we have the below table containing 5 columns as you can see.

 

Here comes the two-way pivot table that breaks down the amount by product and category as follows.

 

Click here to see the detailed step of creating different kinds of PIVOT table as per your need.

2. The VLOOKUP function is one of the most commonly used functions in Excel. VLOOKUP searches for a value in the first column of a table with information organized vertically. From the row with which the value matches, it retrieves a value from the specified column.

=VLOOKUP (value, table, col_index, [range_lookup])

• value : The value to look for in the first column of a table.

• table : The table from which to retrieve a value.

• col_index : The column index in the table from which to retrieve a value.

• range_lookup : [optional] TRUE = approximate match (default). FALSE = exact match.

Click here to understand the VLOOKUP function with a simple example.

3. There are several types of graphs available in excel which can be used to create visualizations of data sets. Graphs are more efficient with respect to precise analysis of the data. Below are different kinds of charts we can create in excel in which the data is displayed in a graphical way. 

• In Column Chart category there are below 2D charts available in excel.

• Clustered Column Chart 

• Stacked Column Chart

• 100% Stacked Column Chart

Click Here to see corresponding 3D Charts.

• In Bar Chart Category there are below 2D Charts available in excel.

• Clustered Bar Chart

• Stacked Bar Chart

• 100% Stacked Bar Chart

Click Here to see corresponding 3D Charts.

• In Line Chart Category there are below 2D Charts available in excel.

• Line Chart

• Stacked Line Chart

• 100% Stacked Line Chart

• Line with Markers Chart

• Stacked Line with Markers Chart

• 100% Stacked Line with Markers Chart

Click Here to see corresponding 3D Charts.

• In Area Chart Category there are below 2D Charts available in excel.

• Area Chart

• Stacked Area Chart

• 100% Stacked Area Chart

Click Here to see corresponding 3D Charts.

• In Pie Chart Category there are below 2D Charts available in excel.

• Pie Chart

• Pie of Pie Chart

• Bar of Pie Chart

• Doughnut Chart

Click Here to see corresponding 3D Charts.

• There are other combination charts available in Combo Chart category as follows.

• Clustered Column – Line Combo Chart

• Clustered Column – Line on Secondary Axis Combo Chart

• Stacked Area – Clustered Column Combo Chart

• We can create several types of Scatter Charts in Excel.

• Other than the above we can also create Pareto Chart in Excel.

4. There are several keyboard shortcuts in excel you can use to format or edit cell, navigate around in a worksheet, to select cell/ row/column/table and manipulating workbooks. Using keyboard shortcuts is more efficient and time saving. Below are some frequently used excel keyboard shortcuts. To learn more about various keyboard shortcuts in excel please check here.

Ctrl+Space : Select the entire column

Ctrl+Shift+Space : Select the entire worksheet

Shift+Left/Right Arrow : Extend the cell selection to the left or right

Shift+Space : Select the entire row

Ctrl+Shift+” : Copy value from cell above

Ctrl+k : Add hyperlink

Ctrl+D : Fill down from cell above

Ctrl+R : Fill right from cell left

F2 : Edit a cell

Home : Go to the leftmost cell in the current row (or go to the beginning of the cell if editing a cell)

Ctrl+Home : Move to the beginning of a worksheet

Page Up/Down : Move one screen up or down in a worksheet

Alt+Page Up/Down : Move one screen to the right or left in a worksheet

Ctrl+Page Up/Down : Move to the previous or next worksheet

Ctrl+Shift+Plus(+) : Insert row/column

Ctrl+Minus(-) : Delete row/column

Ctrl+9 : Hide the selected rows

Ctrl+0 : Hide the selected columns

Ctrl+Shift+9 : UnHide the selected rows

Ctrl+Shift+0 : UnHide the selected columns