Data Management in Excel

for more contents: http://tests.guru/

1. Session 05 Data Management in Excel Muhammad Yasir [email protected]

2. Sort To arrange data in specific order is called sorting Can sort data By text (A to Z or Z to A) By numbers (smallest to largest or largest to smallest) By dates and times (oldest to newest and newest to oldest) in one or more columns. [email protected]

3. [email protected]

4. 1. Ascending Sort Ascending sort is a sorting technique in which the smallest data is placed at first position and the largest data is placed at last position. [email protected]

5. 2. Descending Sort Descending sort is a sorting technique in which the largest data is placed at first position and the smallest data is placed at last position. [email protected]

6. Data Sort Home > Editing Data > Sort & filter [email protected]

7. Sort text Select a column of alphanumeric data in a range of cells On the Data tab, in the Sort & Filter group, do one of the following: To sort in ascending alphanumeric order, click Sort A to Z . To sort in descending alphanumeric order, click Sort Z to A . [email protected]

8. Sort dates or times Select a column of dates or times in a range of cells or table. On the Data tab, in the Sort & Filter group, do one of the following: To sort from an earlier to a later date or time, click Sort Oldest to Newest . To sort from a later to an earlier date or time, click Sort Newest to Oldest . To reapply a sort after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply . [email protected]

9. Sort rows Select a row of data in a range of cells, or make sure that the active cell is in a table column. On the Data tab, in the Sort & Filter group, click Sort . The Sort dialog box is displayed. Click Options . In the Sort Options dialog box, under Orientation , click Sort left to right , and then click OK . Under Column , in the Sort by box, select the row that you want to sort. [email protected]

10. Conditional Formatting [email protected]

11. Conditional Formatting A conditional formatting changes the appearance of a cell range based on a condition (or criteria). If the condition is true, the cell range is formatted based on that condition; if the conditional is false, the cell range is not formatted based on that condition. [email protected]

12. Conditional Formatting Cont… [email protected]

13. Conditional Formatting. Select Cell Range. From Home in Styles groups Select conditional formatting type. [email protected]

14. Format all cells by using a two-color scale Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting , and then click Color Scales . Select a two-color scale. [email protected]

15. Color scale [email protected]

16. Format all cells by using data bars Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Style group, click the arrow next to Conditional Formatting , click Data Bars , and then select a data bar icon. [email protected]

17. Data bar [email protected]

18. Format all cells by using an icon set Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Style group, click the arrow next to Conditional Formatting , click Icon Set , and then select an icon set. [email protected]

19. Icons set [email protected]

20. Format only cells that contain text, number, or date or time values Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Style group, click the arrow next to Conditional Formatting , and then click Highlight Cells Rules . Select the command that you want, such as Between , Equal To Text that Contains , or A Date Occurring . Enter the values that you want to use, and then select a format. [email protected]

21. Format cell that are greater than 50 [email protected]

22. Format only unique or duplicate values Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Style group, click the arrow next to Conditional Formatting , and then click Highlight Cells Rules . Select Duplicate Values . Enter the values that you want to use, and then select a format. [email protected]

23. Format only unique or duplicate values 32 52 [email protected]

24. Clear conditional formats Worksheet    On the Home tab, in the Styles group, click the arrow next to Conditional Formatting , and then click Clear Rules . Click Entire Sheet . A range of cells, table, or PivotTable    Select the range of cells, table, or PivotTable for which you want to clear conditional formats. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting , and then click Clear Rules . Depending on what you have selected, click Selected Cells , This Table , or This PivotTable . [email protected]

25. Data Filter [email protected]

26. Filtering Data The process of finding or selecting information based on certain conditions is called filtering Filtering does not rearrange a list, it only temporarily hides the rows that do not satisfy the conditions [email protected]

27. Data Filter Home > Editing > Sort & Filter > Filter Data > Sort & filter > Filter [email protected]

28. Filter text …. Select a range of cells containing alphanumeric data. On the Data tab, in the Sort & Filter group, click Filter . Click the arrow in the column header. In the list of text values, select or clear one or more text values to filter by. [email protected]

29. Filter text …. Click on arrow Select grade A [email protected]

30. Filter for top or bottom numbers Select a range of cells containing alphanumeric data. On the Data tab, in the Sort & Filter group, click Filter . Click the arrow in the column header. Point to Number Filters and then select Top 10 . In the Top 10 AutoFilter dialog box, do the following. In the box on the left, click Top or Bottom . In the box in the middle, enter a number. In the box on the right, do one of the following: To filter by number, click Items . To filter by percentage, click Percent . To reapply a filter after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply . [email protected]

31. Filter top students in AEE-301 [email protected]

32. Clear a filter for a column To clear a filter for one column in a multicolumn range of cells or table, click the Filter button on the heading, and then click Clear Filter from <Column Name> . [email protected]

33. Clear all filters in a worksheet and redisplay all rows On the Data tab, in the Sort & Filter group, click Clear . [email protected]

34. [email protected]