D. Nixon: CS 12 > Tutorials > Excel

Home (CS 12) | Assignments | Tutorials | Examples | Quizzes & Exam | Other Info | Chat

Computer Science 12


Tutorials on


Microsoft Excel




Week 1: Excel tutorials (general topics, formulas, cell references)

Problem-solving in Excel:

On the use of formulas:

On cell references, absolute vs. relative:

General tutorials on Excel 2007 and later versions:


To all information for Week 1  |  To all tutorials about Excel



Week 2: Excel tutorials (simple functions, line breaks)

On Excel functions in general, and specifically the SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, and MEDIAN functions:

Line breaks in cells:

How to merge cells:

It is recommended that you also use Excel's Help feature to get more information on all of the above.

See also the Excel tutorials listed for Week 1.


To all information for Week 2  |  To all tutorials about Excel



Week 3: Excel tutorials (charts, tables, date functions)

On Excel charts:

On Excel tables:

Date functions:

Time functions:

More about dates in Excel, including DAYS360 and YEARFRAC functions, and the financial conventions they are based on:

For an example spreadsheet showing various different ways of subtracting dates to get the length of a time interval in days (via simple subtraction vs DAYS360) or years (via DAYS360 or YEARFRAC with default basis vs. YEARFRAC with basis = 1), download this Excel file.

Line breaks in formulas (as well as in literal text in cells):

It is recommended that you also use Excel's Help feature to get more information on all of the above.

See also the Excel tutorials listed for Week 1.


To all information for Week 3  |  To all tutorials about Excel



Week 4: Excel tutorials (the IF function)

On the Excel IF function:

Excel's comparison operators:

On the data types of Excel values:


To all information for Week 4  |  To all tutorials about Excel



Week 5: Excel tutorials (AND, OR, and nested IF)

On  IF  and other logical functions  (AND,  OR,  NOT):

Note: Although Excel has a  NOT function, chances are you'll only very rarely need it. You can usually write a simpler formula without  NOT.  For examples, see Eliminating unnecessary  NOT.  functions in Notes on logic in Excel.

On nested  IF  functions:

Some important notes about nested  IF:

Problem-solving with nested  IF  functions:

More about problem-solving:

See also the Week 1 tutorials on solving math word problems and this week's tutorials on algorithmic problem-solving skills.

Date functions review:

See the Excel tutorials listed for Week 3.


To all information for Week 5  |  To all tutorials about Excel



Week 6: Excel tutorials (IFS and more about nested IF, AND, and OR)

On nested  IF  functions, and  AND  and  OR. 

Review the the Excel tutorials listed for Week 5. See especially the section titled "13. Limit IFs with AND and OR" in 19 tips for nested IF formulas on the ExcelJet site. You will be responsible for knowing, on quizzes and the final exam, when and how a nested  IF  can be simplified using  AND  or  OR.

Excel 2016's  IFS  function.

Excel 2016 has a new  IFS  function, a simpler substitute for nested  IF/ELSE.  See:

Note that the  IFS  function is new as of Excel 2016 and will not work on older versions of Excel. Hence it should not be used in spreadsheets you intend to share with people who may have older versions of Excel. Also, it might not be supported on your own computer if you have Excel 2013 or earlier. Since you are not required to have the latest version of Excel (as long as you have at least Excel 2010), there will be no homework assignments involving the  IFS  function. However, you ARE required to understand it and be able to write a correct  IFS  function on a quiz or the final exam. If you don't have Excel 2016 at home, it is recommended that you get some practice with it after class or in a public lab on campus.

Pay particular attention to the use of  TRUE  as the last condition in some  IFS  tutorial examples. What is the purpose of doing that?


To all information for Week 6  |  To all tutorials about Excel



Week 7: Excel tutorials (COUNTIF, SUMIF, AVERAGEIF)

COUNTIF:

SUMIF:

AVERAGEIF:

COUNTIFS, SUMIFS, and AVERAGEIFS:

Range names:

Note: If you use Excel range names, cell names, etc. on a quiz or exam, you must provide a table indicating what range(s) or cell(s) your name(s) refer to.


To all information for Week 7  |  To all tutorials about Excel



Week 8: Excel tutorials (SMALL, LARGE, Goal Seek, Solver, array functions)

On the SMALL and LARGE functions:

Sums with SMALL and LARGE functions:

Important notes:

On Goal Seek:

Range names:

Note: If you use Excel range names, cell names, etc. on a quiz or exam, you must provide a table indicating what range(s) or cell(s) your name(s) refer to.

On the Excel Solver tool (used in extra credit assignment):

On array formulas (used in extra credit assignment):

Important: Do NOT use array formulas (of the Ctrl-Shift-Enter variety) on quizzes, on the final exam, or in regularly-assigned homework. While they can be useful, they also have serious limitations, and there are good alternatives to array formulas in the vast majority of cases.


To all information for Week 8  |  To all tutorials about Excel



Week 10: Excel tutorials (VLookup)

On Excel's VLOOKUP function:


To all information for Week 10  |  To all tutorials about Excel



Week 11: Excel tutorials (financial functions, e.g. PMT, PV, FV)

On the PMT (payment), PV (present value), and FV (future value) functions:

It is recommended that you also use Excel's Help feature to get more information on Excel's financial functions.


To all information for Week 11  |  To all tutorials about Excel



Week 12: Excel tutorials (String concatenation, and criteria revisited)

String concatenation (joining multiple text strings into one):

COUNTIF (revisited):

SUMIF (revisited):

AVERAGEIF (revisited):


To all information for Week 12  |  To all tutorials about Excel

D. Nixon: CS 12 > Tutorials > Excel