Conditional Formatting Basic To Advanced 50 Examples 
Conditional Formatting Basic To Advanced 50 Examples
by Highline
◀ ← Video Lecture 40 of 50 → ▶
Not yet rated
Views: 1,659
Date Added: March 14, 2015

Lecture Description

Download workbook: people.highline.edu/mgirvin/AllClasses/214_2013/214/Busn214_2013.htm

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

Course Index

  1. Back To Basics: Number Format, Keyboards, PivotTables, Formulas
  2. Comprehensive Excel Formula Types & Elements Video 12 Examples
  3. How Formulas Calculate: Order of Precedence in Excel
  4. Percentage Increase or Decrease Formula
  5. Cell References: Relative, Absolute & Mixed (8 Examples)
  6. Scenarios To Save Sets Of Formula Inputs, Paste Special Trick
  7. Worksheet & Workbook References, 3-D Cell References
  8. Excel Table Formula Nomenclature / Structured References 22 Ex.
  9. Defined Names & Aggregate Functions
  10. AND & BETWEEN Criteria: SUMIFS, DSUM, COUNTIFS, AVERAGEIFS
  11. Calculations With OR Criteria: COUNTIFS, DCOUNTA, SUMIFS, DSUM
  12. AND & OR Criteria Calculations: D Functions like DSUM, DCOUNT
  13. AND & OR Criteria Calculations: Table Feature
  14. AND, OR, IF Functions For AR Dept (Multiple Logical Tests)
  15. IF & IS Functions To Deliver Numbers, Text, Formulas, Nested IFs
  16. Round Functions: ROUND, MROUND, ROUNDUP, CEILING, more...
  17. Introduction To SUMPRODUCT Function
  18. VLOOKUP Function 20 Examples, VLOOKUP Formula, Excel VLOOKUP
  19. Text Functions RIGHT, LEFT, MID, SEARCH for Partial Text VLOOKUP
  20. VLOOKUP, DGET & MATCH For Looking Up Records 7 Examples
  21. INDEX and MATCH Functions For Unusual Lookups 4 Examples
  22. CHOOSE function to lookup table for VLOOKUP With Multiple Tables
  23. VLOOKUP and IFNA for Commissions
  24. Getting Top 5 Values With LARGE And Bottom 5 With SMALL
  25. Goal Seek: Get Formula To Evaluate To Desired Result
  26. Excel Compare Two Lists Formula: Item In List? Item Not In List?
  27. How To Track Down Excel Formula Errors (16 Examples)
  28. Excel Data Validation: List, Date, Time, Custom (22 Examples)
  29. Flash Fill: Extract, Combine, Insert, Reverse Data (25 Example)
  30. Custom Number Formatting & Text Functions, (33 Examples)
  31. Date Functions: EDATE, EOMONTH, DATE, YEAR, MONTH, DAY, More
  32. Introduction To Array Formulas and Array Functions
  33. Sorting In Excel. Excel Sort (13 Examples)
  34. Excel Subtotals, Add Subtotals based on One or Two Columns
  35. Excel 2013 PivotTables: Basic To Advanced 58 Examples
  36. Excel Filter For Extracting Records with AND or OR Criteria
  37. Excel Advanced Filter Extract Data 26 Examples Basic - Advanced
  38. Excel Text To Columns To Split or Convert Data 7 Examples
  39. Excel Import Data: Text File, CSV File, Access, Excel File
  40. Conditional Formatting Basic To Advanced 50 Examples
  41. Review Of Chart Basics For Excel 2013
  42. Save Chart As Template in Excel 2013
  43. Set Default Chart in Excel 2013, Keyboard Shortcuts Charts
  44. Pie within Pie Chart in Excel 2013
  45. Combining Chart Types and Secondary Axis in Excel 2013
  46. Statistics: Histogram Formulas & Chart in Excel 2013
  47. Statistics: Dot Plot (Cell Chart)
  48. Conditional Formatting: Bar Chart with Data Labels
  49. Break Even Analysis Formulas & Chart, Plotting Break Even Point
  50. Recorded Macro Basics, including Absolute & Relative References

Course Description

This is the Highline Community College Class, Spreadsheet Construction taught by Michael Girvin: Basic To Advanced Excel. It contains 50 video lectures teaching the basics of Excel, as well as advanced concepts.

Be sure to download the workbook to follow along with the examples. They are found on the official course website.

Comments

There are no comments. Be the first to post one.
  Post comment as a guest user.
Click to login or register:
Your name:
Your email:
(will not appear)
Your comment:
(max. 1000 characters)
Are you human? (Sorry)