D. Nixon: CS 12 > Tutorials > Excel

# 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:

### 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.

### 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.

### Week 4: Excel tutorials (the IF function)

On the Excel IF function:

Excel's comparison operators:

On the data types of Excel values:

### 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`:

• There are other ways to combine  `IF`  functions in a formula besides nesting one inside the other. For example, for some problems, the most appropriate formula is one which does arithmetic with the values computed by two or more distinct (non-nested)  `IF`  functions. Nesting  `IF`  functions may or may not be the most appropriate way to combine them, depending on the problem.
• Some of the tutorials listed on this page mention that Excel 2016 has introduced a new function,  `IFS`,  intended as an easier-to-use replacement for nested  `IF`  in some cases. Do NOT use the  `IFS`  function in homework this week or on next week's quiz. We will discuss  `IFS`  in class next week. Some of the other tutorials also mention  `VLOOKUP`,  which we will not be discussing until Week 10.

Problem-solving with nested  `IF`  functions:

Date functions review:

### 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?

### 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.

### 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:

• Some of the above tutorials mention array formulas (of the kind that need to be entered using Ctrl-Shift-Enter). Array formulas are covered in an extra-credit exercise assigned this week. However, do NOT use array formulas on quizzes, on the final exam, or in any homework other than this week's extra-credit excercise. Array formulas are powerful but have many limitations, and there are good alternatives to array formulas in most cases where you might be tempted to use them.
• Contrary to claims in some of the above tutorials, formulas of the form  `=SUM(SMALL(E5:E20,{1,2,3}))`  (which sums the 3 smallest numbers in range  `E5:E20` )   and  `=SUM(LARGE(D5:D30,{1,2,3,4,5}))`  (which sums the 5 largest numbers in range  `D5:D30` )   do NOT need to be entered as array formulas using Ctrl-Shift-Enter. They work fine as normal formulas, entered using Enter.

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.

### Week 10: Excel tutorials (VLookup)

On Excel's VLOOKUP function:

### 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.

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

String concatenation (joining multiple text strings into one):

COUNTIF (revisited):

SUMIF (revisited):

AVERAGEIF (revisited):

D. Nixon: CS 12 > Tutorials > Excel