7 easy and powerful feature of Microsoft excel you may not know about

Microsoft excel is used by almost everybody.Here are some of the cool Excel feature  which every body should be aware

1.  Using Excel function to complete many task

Excel has powerful list of function to solve various problems and task.You can use sum,concatenate,average,if .The list goes on

2) Auto Filter

This is one of the best function to use when playing with lot of data on the excel. You can filter any column and find the corresponding intersection very easily.

Just go to the top and click on filter, your excelsheet will converted into  filter form as shown below.

3) Conditionally Format

We may be asked many time to present the data in such a form so that highlights are visible like  Who has the highest (or lowest) sales, what the top five are, etc.? Excel’s Conditional Formatting will do everything from put a border around the highlights to color coding the entire table. Use the Highlighted Cells Rules sub-menu to create more rules to look for things, such text that contains a certain string of words, recurring dates, duplicate values, etc. There’s even a greater than/less than option so you can compare number changes.

The simple operation is shown below, We colored  the row where E column is greater 2

4) Great Excel Shortcut Keys

CNTRL B	Bold the text in word  or bold the cell
CNTRL -k	Add Hyperlink
CNTRL- S	Save the file
CNTRL -1	Format the cell
CTRL+SHIFT+L	Turn on/off the filter
F12	Save as
CNTRL + F2	Print preview
Shift + F11	Add new sheet
ALT+ ENTER	Get a line break inside the cell
CNTRL+F4	Close the workbook
Ctrl+;	Inserts today’s date.
Ctrl+Shift+:	Inserts the current time (the colon is what is in a clock reading, like 12:00).
Ctrl+0	Hides the current column
Ctrl+9	Hides the current row
Ctrl+F6	Switches between open workbooks (that is, open Excel files in different windows).

5) Pivot Table

It is also one of the powerful feature in Microsoft excel to analyze and present the data

If you have well-organized source data, you can create a pivot table less than a minute. Here’s how:
1.Select any cell in the source data
2.On the Insert tab of the ribbon, click the PivotTable button
3.In the Create PivotTable dialog box, check the data and click OK
4.Drag a “label” field into the Row Labels area

Example

 

6) Using Macros

you can automate tasks in Excel by writing so called macros. First we need to enable developer toolbar to do it

Each Excel version has different ways to enable the developer toolbar

Excel 2010

In Excel 2010 you can display the developer toolbar the following way:

1. Click the green File Button
2. Press Options
3. Make sure that the Customize Ribbon right menu item is selected.
4. In the dropdown list called Customize the Ribbon, select All Tabs.
5. In the group called Main Tabs, make sure that the option Developer is checked.

Excel 2007

To display the developer ribbon, do the following:

1. Click the Office Button
2. Click the Excel Options button at the bottom of the dialog.
3. Ensure that the Popular tab in the left menu is selected (se picture below)
4. Check the option Show Developer tab in the Ribbon

Now once the developer toolbar is visible , we can record any macro and then use it

 

7) Validate Data to Make Drop Downs

If you Creating a spreadsheet for others to use and wants that people should enter some valid values only then  it will be beneficial  to create a drop-down menu of selections to use in particular cells (so they can’t screw it up!), that’s easy. We can create this using the below steps

1. Highlight the cell, go to the Data tab, and click Data Validation.
2. Under “Allow:” select “List.”
3. Then in the “Source:” field, type a list, with commas between the options. Or, you could click the button next to the Source field and go back into the same sheet to select a data series
4. You can hide that data later, it’ll still work.

Example

Hope you like these cool Excel feature and use it in your daily task .Please do provide the feedback