Highline Excel 2013 Class Video 40

Conditional Formatting Basic To Advanced 50 Examples

DESCRIPTION

Topics in this video:
1. Built-in Conditional Formatting. Conditional Formatting to cells: A logical test that comes out TRUE or FALSE. TRUE = get Formatting. FALSE = Not get Formatting.
2. Highlight Cell Rules: Type in textbox: Greater than 1000.
3. Highlight Cell Rules: Type in textbox: Contains “Fun”.
4. Highlight Cell Rules: Link textbox to cell: Greater than 1000.
5. Highlight Cell Rules: Link textbox to cell: Contains “Fun”.
6. Top Bottom Rules: Top 3.
7. Top Bottom Rules: Above Average.
8. Data Bar on top of numbers.
9. Data bars in cell next to numbers. Also RANDBETWEEN Conditional Formatting.
10. Manage Rules dialog Box.
11. Manage Rule dialog box keyboard: Alt, O, D.
12. Colour scales = Ranks number by colour.
13. Fun with CF, RAND, and Bubble Chart.
14. Icon: Arrow Icons on top of numbers.
15. Icon: Arrow Icons next to numbers.
16. Logical Formulas. Get Formatting = TRUE or Any Non-Zero Number. NOT Get Formatting = FALSE or Zero or Error.
17. Format number less than zero: 3 methods.
18. Format Actual when Actual Exceeds Budgeted: 2 methods.
19. Keyboard for New Format Rule dialog box: Alt, O, D, Alt + N or Alt, H, L, N.
20. Keyboard to open CF Rule Manager to edit or delete a rule: Alt, O, D, D, Enter.
21. Clear Content and CF still remains.
22. Format cell based on a different cell’s value: Format Net Sales based on Date.
23. Sort and CF will adjust.
24. Format cell based on a different cell’s value: Format Net Sales based on Invoice number.
25. Format whole row based on date.
26. Format whole row based on Invoice number.
27. Format Row when Actual Exceeds Budgeted.
28. Format whole column.
29. Format cell based on OR criteria: OR function.
30. Format cell based on OR criteria: MATCH function (Errors are ignored, Any non-zero number = TRUE).
31. Format whole row based on OR Criteria use MATCH (Errors are ignored, Any non-zero number = TRUE).
32. Format cell based on AND Criteria: Between Upper and Lower Date: AND Function.
33. Format whole row based on between Date Criteria.
34. Format whole row based on AND Criteria from different columns.
35. Format whole row based on OR and AND Criteria.
36. Format entire row when sales are above average.
37. Format every other row with MOD function (1 = TRUE, 0 = FALSE).
Copy Conditional Formatting:
38. Format Row In Checkbook when Reconciled.
39. Format Entire Row for the 3 Biggest Values.
40. Format lowest Price in Row.
41. Format intersecting cell.
42. Format row based on Approximate Match based on VLOOKUP.
43. Multiple Rules applied to each cell: Format row, column and intersection value.
44. Gantt Chart for Day Schedule Cell Chart.
45. Format Weekends and Holidays NOT WORKDAY.INTL NETWORKDAYS.INTL function.
46. Format Duplicates Built-in Method or Formula Method: 2 methods.
47. Format items in List 2 that are NOT in List 1.)
48. Format items that are in List 2 that are also in List 1 (Errors are ignored, Any non-zero number = TRUE).
49. Format stocks with a loss.
50. Format cells that are not empty, using COUNTA (Any non-zero number = TRUE, Zero = FALSE).
51. MAX For Seattle (Array Formulas work in Conditional Formatting dialog box).

WORKBOOKS

Download “Highline Excel 2013 Workbooks” Highline-Excel-2013-Video-Workbooks.zip – Downloaded 187 times – 21 MB




Be the first to comment

Leave a Reply