Easy steps to freeze panes

This example describes how to create drop-down lists in Excel. Here’s what we are trying to achieve:

Example 1

Excel Drop down List
Go to under Excel Data Tab and select Data validation as below image.

Excel Drop down List
Dialog box will appear, select list under Allow, and next data source. 

excel dropdown list
Now Click ok.

Excel Drop down list
Example 2

This example describes how to create dependent drop-down lists in Excel.
The user selects Laptop from a drop-down list.
excel formulas

The result in the next drop down Only Laptop related Brands.

Drop down list

To create these dependent drop-down lists, execute the following steps.

1. On the second sheet, create the following named ranges.

Excel Drop down list

Go to Data Validation under data tab As first step in Example 1.

Dialog box select The range of Product type range as below image.

Drop down list
Click ok. 
Step 2. For Dependent drop down keep your courser next to Brand and again go to data validation.
Select List, For source use INDIRECT formula to complete this task.
Excel Data validation
Now click ok and check.
Excel Data validation
Explanation: the INDIRECT function returns the reference specified by a text string. For example, the user selects laptop from the first drop-down list. =INDIRECT($B$2) returns the laptop reference. As a result, the second drop-down lists contains the laptop items.
Do you like this page..? Follow us on Google plus for Daily update..!!


How to freeze column headings in excel-Excel Tutorial

If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});

Freeze Top Row

To freeze the top row, execute the following steps.
1. On the View tab, click Freeze Panes, Freeze Top Row.
Freeze Top Row

2. Scroll down to the rest of the worksheet.
Result. Excel automatically adds a black horizontal line to indicate that the top row is frozen.
Freeze Top Row Result
Note: to keep the first column visible while scrolling through the right of the worksheet, click Freeze First Column.

Unfreeze Panes

To unlock all rows and columns, execute the following steps.
1. On the View tab, click Freeze Panes, Unfreeze Panes.
Unfreeze Panes

Freeze Panes

To freeze panes, execute the following steps.
1. Select row 3.
2. On the View tab, click Freeze Panes, Freeze Panes.

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});

Freeze Rows
3. Scroll down to the rest of the worksheet.
Result. All rows above row 3 are frozen.
Freeze Rows Result
Note: to keep columns visible while scrolling to the right of the worksheet, select a column and click Freeze panes.
4. Select cell C3 (unfreeze panes first).
5. On the View tab, click Freeze Panes, Freeze Panes.
Result. The region above row 3 and to the left of column C is frozen.
Freeze Rows and Columns Result
Courtecy By Easy-excel.com

Protect sheet : how to protect excel sheet with password






Protection: You can prevent people from editing Locked cells or changing the from the cells,

  1. To unlock any cells or ranges that you want other users to be able to change, do the following: Select each cell or range that you want to unlock. On the Home tab, in the Cells group, click Format, and then click Format Cells. On the Protection tab, clear the Locked check box, and then click OK.
Example.

Select All the cells.

Protect Excel sheets
Right Click and go to Format cells.

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});

Protect excel sheets
Following dialog box will appear go to under protection tab and Un-tick Locked.
Protect excel sheets
Click ok again select the cells range that you want to Lock.
protection excel sheets
Again Right click and go to Protection and tick the locked box.

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});

Protecting Excel sheet
Click ok. Now go to Under Review Tab click on Protection,
Protecting excels cells
Following Dialog box, Enter your password and confirm and click ok.
Protecting excel cells & sheets
Now your done.
Do you like this page..? Subscribe for news letter below. Or add follower on google plus..!!


Data filter: How to filter data in excel




Filter : Enable Filtering of the selected cells or columns, Once the Filter turned on click             column header to choose a filter for column data.

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});



     Under Data Tab Click Filter.
Data filter
Once the filter Turned on click the column header to choose a filter for column data.

Data filter help
To Filter Largest to Smaller click the appropriate option.

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});

Data filter help Excel 2013
Note : Even you can tick the value only you wants get,
Do you like this Page Please Share this page on Google + 

(function() { var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true; po.src = ‘https://apis.google.com/js/platform.js’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s); })();




Remove Duplicate in Excel



Remove Duplicate: Remove Duplicate values from a sheet or column cells, You can specify       
which column should be checked for Duplicate,



Lets see an Example.

We have bunch of Data’s having Repeated.


Excel Remove Duplicate




To remove the duplicates. First select the data range as above and go to Under Excel Data tab > Remove Duplicates.
//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});

Excel Remove Duplicate
Click OK.
Excel Remove Duplicate
You can see 17 Duplicates Values found and Removed out of 23.

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});

Do you Like this page..!! Please subscribe us for Daily News letter and get New posts in you Mail Box…!! 



Text to Column – Excel Tutorial





Text to column: Separate the content of one Excel cell into separate column, For Example  you can separate a column of full name into First name and last name,


1. Select the range with full names.

Excel text to coloum
2. On the Data tab, click Text to Columns.

Excel text to coloum
The following dialog box appears.

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});

3. Choose Delimited and click Next.
Excel text to coloum
Tick Comma or type comma on the text box
Excel Text to coloum
Click Next
Excel Text to coloum
Keep it by default dont do anything here and click Next.
Seperate Excel Text to coloum
Thats All.
Note: This example has commas and spaces as delimiters. You may have other delimiters in your data. Experiment by checking and unchecking the different check boxes. You get a live preview of how your data will be separated.

Do you Like this Page Please Like us on Goole Plus 

(function() { var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true; po.src = ‘https://apis.google.com/js/platform.js’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s); })();




Refresh All Pivot Tables at once -Excel Tutorial




Refresh All  : Update all the information in the workbook that is coming from a data 
source( Pivot Table, SQL, Data from another sheet)


Example: Refresh All Pivot Tables at once.
//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({});

Excel Under Data Tab > Refresh All.

Refresh All Pivot Tables

Lets Assume We have data’s as below.

Refresh All Pivot Tables
There are two Diffrent pivot table.
Excel Refresh All Pivot Tables
So Lets change few Values in the data Dump.. In this case to refresh the pivot tables you have to Right Click Each Pivot Table and Click Refresh..
To make this work Easy click Refresh All in the Excel Data tab it will Refresh All the pivot table and other Data source Easy and at once.

Do you Like this Page..Please Like us on Facebook for Daily Updates..!!

(function() { var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true; po.src = ‘https://apis.google.com/js/platform.js’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s); })();