Back To Home Page

Busn 218: Spreadsheet Construction: Excel 2013 From Beginning To End

Syllabus

Introductory Video

NOTE: All Excel files in this class will be listed as 214 because that was the old class number... (Sorry About That)!

Week 1:
Topics: Cells, Worksheets, Sheet Tabs and Workbooks; Keyboards; Number Formatting as Façade; Types of Data in Excel; Alignment; Proper Data Sets, Non-Proper Data Sets; Raw Data Into Information using Formula or PivotTable; Bad Data; Excel Tables; Style Number Formatting; Page Setup; Reminder About Sorting, Filtering and PivotTables From Excel Basics in Busn 216; Charts; Excel’s Golden Rule
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
Busn214-Week01.xlsm
Excel'sGoldenRule.docx <<== Download and read (hint for one of the quiz questions).


Videos to watch:
Highline Excel 2013 Class Video 01: Back To Basics: Number Format, Keyboards, PivotTables, Formulas  (1:42:38 min)

Homework Problems to complete:
Numbers 1 to 15 at end of Download Excel File


Week 2:
Topics: Formula Elements, Formula Types and Cell References, Assumption Tables/Formula Elements, and Scenario Manager
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
Busn214-Week02.xlsx
Busn214-Week02-SecondWorkbookWithTableTricks.xlsx
Videos to watch:
Highline Excel 2013 Class Video 02: Comprehensive Excel Formula Video 12 Examples (33:55 min)
Highline Excel 2013 Class Video 03: How Formulas Calculate: Order of Precedence in Excel  (15:23 min)
Highline Excel 2013 Class Video 04: Percentage Increase or Decrease Formula (4:41 min)
Highline Excel 2013 Class Video 05: Cell References: Relative, Absolute & Mixed (8 Examples)  (37:12 min)
Highline Excel 2013 Class Video 06: Scenarios To Save Sets Of Formula Inputs, Paste Special Trick (6:00 min)
Highline Excel 2013 Class Video 07: Worksheet & Workbook References, 3-D Cell References  (11:20 min)
Highline Excel 2013 Class Video 08: Excel Table Formula Nomenclature / Structured References 22 Ex. (25:46 min)

Homework Problems to complete:
Numbers 1 to 21 at end of Download Excel File


Week 3:
Topics: AND and OR Criteria. Important Functions like: SUMIFS, IF, AND, OR, and more…
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
Busn214-Week03.xlsx
Videos to watch:
Highline Excel 2013 Class Video 09: Defined Names & Aggregate Functions (13:15 min)
 Highline Excel 2013 Class Video 10: AND & BETWEEN Criteria: SUMIFS, COUNTIFS, AVERAGEIFS, more (39:18 min)
Highline Excel 2013 Class Video 11: Calculations With OR Criteria: COUNTIFS, DCOUNTA, SUMIFS, DSUM (22:57 min)
Highline Excel 2013 Class Video 12: AND & OR Criteria Calculations: D Functions like DSUM, DCOUNT (9:00 min)
Highline Excel 2013 Class Video 13: AND & OR Criteria Calculations: Table Feature (5:43 min)
Highline Excel 2013 Class Video 14: AND, OR, IF Functions For AR Dept (Multiple Logical Tests) (13:36 min)
Highline Excel 2013 Class Video 15: IF & IS Functions To Deliver Numbers, Text, Formulas, Nested IFs  (26:17 min)
Highline Excel 2013 Class Video 16: Round Functions: ROUND, MROUND, ROUNDUP, CEILING, more...  (8:33 min)
Highline Excel 2013 Class Video 17: Introduction To SUMPRODUCT Function  (10:01 min)

Homework Problems to complete:
Numbers 1 to 12 at end of Download Excel File


Week 4:
Topics: Lookup Formulas, a few Statistics functions, Goal Seek
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
Busn214-Week04.xlsx
Busn214-Week04-second workbook.xlsx
Videos to watch:
Highline Excel 2013 Class Video 18: VLOOKUP Function 20 Examples, VLOOKUP Formula, Excel VLOOKUP  (42:00 min)
Highline Excel 2013 Class Video 19: Text Functions RIGHT, LEFT, MID, SEARCH for Partial Text VLOOKUP  (11:12 min)
Highline Excel 2013 Class Video 20: VLOOKUP, DGET & MATCH For Looking Up Records 7 Examples  (20:56 min)
Highline Excel 2013 Class Video 21: INDEX and MATCH Functions For Unusual Lookups 4 Examples  (19:33 min)
Highline Excel 2013 Class Video 22: CHOOSE function to lookup table for VLOOKUP With Multiple Tables  (06:45 min)
Highline Excel 2013 Class Video 23: VLOOKUP and IFNA for Commissions  (2:23 min)
Highline Excel 2013 Class Video 24: Getting Top 5 Values With LARGE And Bottom 5 With SMALL  (05:25 min)
Highline Excel 2013 Class Video 25: Goal Seek: Get Formula To Evaluate To Desired Result  (03:37 min)
Highline Excel 2013 Class Video 26: Excel Compare Two Lists Formula: Item In List? Item Not In List? (6:14 min)
Additional Excel File to download and Video to watch:
            Video: Excel Magic Trick 1055: Grading: Calculate Current Percentage & Decimal Grade (9:19 min)
            Excel File to Download: YouTubeExcelIsFun/EMT1055.xlsx

Homework Problems to complete:
Numbers 1 to 13 at end of Download Excel File

 

Week 5:
Topics: Data Validation, Text Formulas, Date Formulas, Flash Fill and Array Formulas
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
Busn214-Week05.xlsm
Notes about Array Formulas: Array Formula Notes.docx

Videos to watch:
Highline Excel 2013 Class Video 27: How To Track Down Excel Formula Errors (16 Examples)  (21:35 min)
Highline Excel 2013 Class Video 28: Excel Data Validation: List, Date, Time, Custom (22 Examples)  (24:24 min)
Highline Excel 2013 Class Video 29: Flash Fill: Extract, Combine, Insert, Reverse Data (25 Example) (12:21 min)
Highline Excel 2013 Class Video 30: Custom Number Formatting & Text Functions, (33 Examples)  (30:47 min)
Highline Excel 2013 Class Video 31: Date Functions: EDATE, EOMONTH, DATE, YEAR, MONTH, DAY, More  (11:36 min)
Highline Excel 2013 Class Video 32: Introduction To Array Formulas and Array Functions (34:05 min)

Homework Problems to complete:
Numbers 1 to 14 at end of Download Excel File

 

Week 6:
Topics: Sort, Subtotal, PivotTables
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
    Busn214-Week06.xlsm
    Busn214-Week06-Second WorkbookSTART-FILE.xlsm - This is the Start File you should use for Practice and to get Homework.
    Busn214-Week06-Second WorkbookFINISHED-FILE.xlsm - This is the Finished File that has completed examples AFTER the video is over.
Videos to watch:
Highline Excel 2013 Class Video 33: Sorting In Excel. Excel Sort (13 Examples)  (18:10 min)
Highline Excel 2013 Class Video 34: Excel Subtotals, Add Subtotals based on One or Two Columns  (12:48 min)
Highline Excel 2013 Class Video #35: Excel 2013 PivotTables: Basic To Advanced 58 Examples  (01:21:50 min)

Homework Problems to complete:
Numbers 1 to 7 at end of each of the two Download Excel Files

 

Week 7:
Topics: Filter, Advanced Filter, Text To Columns, Import Data
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
Busn214-Week07StartFile.xlsm (Use this file to follow along and try the examples)
Busn214-Week07FinishedFileWithCompletedExamplesAfterVideosDone.xlsm (This has the finished examples as they would appear at the end of the video)
Products.zip
LotsOfData.xlsx
Videos to watch:
   Highline Excel 2013 Class Video 36: Excel Filter For Extracting Records with AND or OR Criteria (15:05 min)
   Highline Excel 2013 Class Video 37: Excel Advanced Filter Extracting Data 26 Examples  (30:25 min)
   Highline Excel 2013 Class Video 38: Excel Text To Columns To Split or Convert Data 7 Examples   (10:13 min)
   Highline Excel 2013 Class Video 39: Excel Import Data: Text File, CSV File, Access, Excel File   (10:03 min)

Homework Problems to complete:
Numbers 1 to 7 at end of the Download Excel File

 

Week 8:
Topic: Conditional Formatting
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
    Busn214-Week08StartFile.xlsm
    Busn214-Week08FinishedFile.xlsm
Videos to watch:
    Highline Excel 2013 Class Video 40: Conditional Formatting Basic To Advanced 50 Examples  (01:28:15 min)
Homework Problems to complete:
No Problems. Just do all examples from video.

 

Week 9:
Topic: Charts.
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
Workbook for Video #41: Video43-Excel2013Basics25-Charts.xlsm
Helpful 3 Page Written Summary of Chart Types: Video43-Excel2013Basics25-Charts.docx
Workbook for videos #42-49: Busn214-Week09.xlsm
Videos to watch:
    Highline Excel 2013 Class Video 41: Review Of Chart Basics For Excel 2013  (43:38 min)
    Highline Excel 2013 Class Video 42: Save Chart As Template in Excel 2013  (3:47 min)
    Highline Excel 2013 Class Video 43: Set Default Chart in Excel 2013, Keyboard Shortcuts Charts  (2:02 min)
    Highline Excel 2013 Class Video 44: Pie within Pie Chart in Excel 2013  (2:44 min)
    Highline Excel 2013 Class Video 45: Combining Chart Types and Secondary Axis in Excel 2013  (3:58 min)
    Highline Excel 2013 Class Video 46: Statistics: Histogram Formulas & Chart in Excel 2013  (12:34 min)
    Highline Excel 2013 Class Video 47: Statistics: Dot Plot (Cell Chart)  (2:37 min)
    Highline Excel 2013 Class Video 48: Conditional Formatting: Bar Chart with Data Labels  (2:23 min)
    Highline Excel 2013 Class Video 49: Break Even Analysis Formulas & Chart, Plotting Break Even Point  (13:25 min)
Homework Problems to complete:
Numbers 1 to 6 at end of the "Video43-Excel2013Basics25-Charts.xlsm" Download Excel File
Numbers 1 to 4 at the end of the " Busn214-Week09.xlsm" Download Excel File.

 

Week 10:
Topic 1: A Few Finance Tricks
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
Busn214-Week10.xlsm
Videos to watch:
    Highline Excel 2013 Class Video 50: Financial Functions: PMT, RATE, NPER and FV 12 Examples  (41:05 min)

Homework Problems to complete:
Numbers 1 to 5 at end of the Download Excel File

 

Week 11:
Topics: Recorded Macros
Download Excel File (contains notes, class projects, video projects, homework, and homework solutions):
    Busn214-Week11StartFile.xlsm
    Busn214-Week11FinishedFile.xlsm
Videos to watch:
    Highline Excel 2013 Class Video 51: Recorded Macro Basics, including Absolute & Relative References (33:15 min)

Homework Problems to complete:
None

 

 

 

 

 

 

Just out of curiosity:
Old Class 1: Excel 2010
Old Class 2: Excel 2007

 

Michael GirvinOffice Hours and contact information

 

 

 

In Class Files:
Week 3: In class file: Week3 Busn 214, AND OR Criteria.xlsx
Week 5: In Class Array Formula Practice: In Class Busn 214 Array Formula Practice.xlsx
In Class Bad Data and Date Examples: n Class Busn 214 Bad Data and Date Calculations.xlsx
Week 6: File 1: In Class Busn 218 PivotTables May 6 2015Start.xlsx,    File 2: In Class Busn 218 PivotTables May 6 2015Finished.xlsx,    File 3: In Class Busn 218 Huge Data May 6 2015.xlsx
Week 10: InClassWedJune03-2014Busn218Fun.xlsx

 Sunday, 27 September 2015 09:42 AM -0700 Last Updated: