Sorting data

Sorting data is an integral part of data analysis. You might want to put a list of names in alphabetical order, compile a list of protect inventory levels from highest to lowest, or order row by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions .
You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list (such as Large, Medium, and Smallest) or by format, including cells color, font color, or icon set. Most sort operations are column sorts, but you can also sort by rows.

Default sort orders: In an ascending sort, Microsoft Office Excel uses the following order. In a descending sort, this sort order is reversed.

Value
Comment
Numbers
Numbers are sorted from the smallest negative number to the largest positive number.
Dates
Dates are sorted from the earliest data to the latest data.
Text
Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.
Logical
In logical values, FALSE is placed before TRUE.
Error
All error values, such as #NUM! and #REF!, are equal.
Blank cells
In both an ascending and descending sort, blank cells are always placed last.

Lock only specific cells and ranges in a protected worksheet

1. If the worksheet is protected, do the following:
· On the Review tab, in the Changes group, click Unprotect Sheet.
NOTE: Protected Sheet changes to Unprotect Sheet when a worksheet is protected.
· If prompted, type the password to unprotect the worksheet.
2. Select the entire worksheet by clicking the Select All button.
Select All button
3. On the Home tab, in the Font group, click the Format Cell Font dialog box launcher.

Keyboard shortcut You can also press CTRL+SHIFT+F or CTRL+1.
4. On the Protection tab, clear the Locked check box, and then click OK.
NOTE: This unlocks all of the cells on the worksheet when you protect the worksheet.
5. In the worksheet, select just the cells that you want to lock.
6. On the Home tab, in the Font group, click the Format Cells Font dialog box launcher.
(Keyboard shortcut You can also press CTRL+SHIFT+F or CTRL+1.)
7. On the Protection tab, select the Locked check box, and then click OK.
8. On the Review tab, in the Changes
group, click Protect Sheet.
9. Type a password for the worksheet.
· Changing the size and position of the windows for the workbook when the workbook is opened

Protect workbook elements

1. On the Review tab, in the Changes group, click Project Workbook.
2. Under Project workbook for, do any of following:
· To protect the structure of a workbook, select the Structure check box.
· To keep workbook windows in the same size and position each time the workbook is opened, select the Windows check box.
Select this check box To prevent users from
Structure
Recording new macros.

1. Viewing worksheets that you have hidden.
2. Moving, deleting, hiding, or changing the names of worksheets.
3. Inserting new worksheets or chart sheets.
4. Moving or copying worksheet to another workbook.
5. In PivotTable reports, displaying the source data for a cell in the data area, or displaying page field pages on separate worksheets.
6. For scenarios, creating a scenario summary report.
7. In the Analysis Toolbar, using the analysis tools that place results on a new worksheet.
3. To prevent other users from removing workbook protection, in the Password (optional) box, type a password, click OK, and then retype the password to confirm it.

Allow cells to be edited in a protected worksheet
When you protect a worksheet, all cells are locked by default, which means that they cannot be edited. To enable cells to be edited while leaving only some cells locked, you can unlock the cells and then lock only specific cells and ranges before you protect the worksheet. Windows
1. Moving, resizing, or closing the windows.
2. Changing the size and position of the windows for the workbook when the workbook is opened

· Changing the size and position of the windows for the workbook when the workbook is opened

Protect workbook elements

1. On the Review tab, in the Changes group, click Project Workbook.
2. Under Project workbook for, do any of following:
· To protect the structure of a workbook, select the Structure check box.
· To keep workbook windows in the same size and position each time the workbook is opened, select the Windows check box.
Select this check box To prevent users from
Structure
Recording new macros.

1. Viewing worksheets that you have hidden.
2. Moving, deleting, hiding, or changing the names of worksheets.
3. Inserting new worksheets or chart sheets.
4. Moving or copying worksheet to another workbook.
5. In PivotTable reports, displaying the source data for a cell in the data area, or displaying page field pages on separate worksheets.
6. For scenarios, creating a scenario summary report.
7. In the Analysis Toolbar, using the analysis tools that place results on a new worksheet.

view cell

5. On the Review tab, in the Changes group, click Protect Sheet.
6. In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.

Clear this check box To prevent users from

Select locked cells : Moving the pointer to cells for which the Locked check box is selected on the Protection tab of the Format Cells dialog box. By default, users are allowed to select locked cells.

Select unlocked cells: Moving the pointer to cells for which the Locked check box is cleared on the Protection tab of the Format Cells dialog box. By default, users are allowed to select unlocked cells, and they can press the TAB key to move between the unlocked cells on a protected worksheet.

Format cells: Changing any options in the Format Cells or Conditional Formatting dialog boxes, If you applied conditional formats before you protected the worksheet, the formatting continues to change when a user enters a value that satisfies a different condition.

Format columns: Using any of the column formatting commands, including changing column width or hiding columns (Home tab, Cells group, Format button).

Format rows: Using any of the row formatting commands, including changing row height or hiding rows (Home tab, Cells group, Format button).

Insert columns: Inserting columns.
Insert rows: Inserting rows.
Insert hyperlinks: Inserting new hyperlinks (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an internet. Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.), even in unlocked cells.

Delete columns: Deleting columns.
NOTE: If Delete columns is protected and Insert columns is not also protected, a user can insert columns that he or she cannot delete.


Delete rows:
Delete rows


NOTE: If Delete rows is protected and Insert rows is not also protected, a user can insert rows that he or she cannot delete.

Select this check box To Prevent users from

Format Cells

· On the Protection tab, clear the Locked check box, and then click OK.

3. To hide any formulas that you don't want to be visible, do the following:
· In the worksheet, select the cells that contain the formulas that you want to hide .
· On the Home tab, in the Cells group, click Format, and then click Format Cells.
· on the Protection tab, select the Hidden check box, and then click OK.
4. To unlock any graphic objects (such as picture, clip art, shapes, or Smart Art
graphics) that you want users to be able to change, do the following:
· Hold down CTRL and click each graphic object that you want to unlock. This display the Picture Tools or Drawing Tools, adding the Format tab.
· On the Format tab, in the Size group, click the Dialog Box Launcher next to Size.
· On the Properties tab, clear the Locked check box, and if presents, clear the Lock text check box .

A range of cells, table

1. Select the range of cells, table, or PivotTable for which you want to clear
conditional formats.
2. On the Home tab, in the Styles group, click the arrow next to Conditional
Formatting, and then click Clear Rules.
3. Depending on what you have selected, click Selected Cells, This Table.
Protect worksheet or workbook elements
To Prevent a user from accidentally or deliberately changing, moving, or deleting important data, you can protect certain worksheet or workbook elements, with or without a password.
Protect worksheet elements
When you protect a worksheet, all cells on the worksheet are locked by default, and users cannot make any changes to a locked cell. For example, they cannot insert, modify, delete, or format data in a locked cell. You can, however, specify which elements users will be allowed to change when you protect the worksheet.
1. Select the worksheet that you want to protect.
2. To unlock any cells or ranges that you want other users to be able to change, do the following:
· Select each cells 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.
3. To hide any formulas that you don't want to be visible, do the following: