The purpose of this sheet is to serve as a study aid. You are, however, responsible for all material that I assigned to you and/or reviewed in class. I reserve the right to ask something that's not specifically stated on this sheet. Review all material from Lectures, Outlines and Files, Practice Exercises, Excel Assignments, and assigned sections from Excel Textbook Chapters 1 - 9.

Test Format

Multiple choice. Bring a pencil. I will provide the scantron.

Excel Basics

Know the parts of the MS Excel 2013 interface: Office Button, Ribbon, Tabs (including File and Contextual tabs), Name Box, Formula Bar, Select All button, Worksheet Tabs, Zoom controls, Groups and Icons
Know the difference between a worksheet and a workbook.
How do excel column and row headings differ?
What is a cell reference?
What are cell ranges? Adjacent ranges? Nonadjacent ranges? Named ranges?
How do you reference several adjacent and nonadjacent ranges/cells?
Know how to change the appearance, location, etc. of worksheets
What happens when numbers do not fit in a column.?
What types of data can be stored in worksheet cells?
What are methods for changing the width of columns and height of rows?
What is AutoFill? How does it work? What is the Fill Handle? What effect does AutoFill have on text, numbers, text/number codes, dates, months, formulas, etc.?
Know how dates are represented internally in Excel
Know how to change the format of the printed worksheet as well as how the worksheet appears on the screen.
How do you insert and delete rows and columns?
Know common keyboard shortcuts for MS Office

Excel Formatting

Know how to change: cell color, font color/size, alignment (horizontal & vertical) of a cell content
Know how to: indent, wrap text, add borders
Know how to: hide rows and columns, format sheet tabs
Formatting features: numeric formats; date formats; alignment; merge and center; data bars; comments
What are Accounting and Currency formats? How do they differ? How do you apply them?
How do you add and/or remove decimal places?
What is the effect using the $, Percent Style, and Comma Style buttons?
What is Format Painter and how is it used?
Why would you use Conditional Formatting? Know how to do it.
How do you freeze and unfreeze panes? Split and unsplit worksheet? Why would you use these features?
What are the various options to Clear a cell?
Printout - how to repeat the labels at the top of each printed page, change margins, change orientation, use (custom) headers and footers
Know the various ways to Sort in Excel and how to do them. What is a multi-level sort? Ascending vs. Descending?

Excel Formulas and Functions

What is a formula?
How are formulas set up and calculated?
What is the order of precedence of operators in a formula?
What is a function? What is an argument (also referred to as parameter)?
What is the difference between a formula and a function?
Know how/when to use absolute, relative and mixed cell references. Know the effect of copying or using the fill handle to drag a cell to another cell.
What is a Nested function? How does Excel process a nested function?
Know how to set up a Running Sum/Running Total.
Know what an Amortization table/schedule is, and why we use them.
Know how to use the Fill Growth Series analysis including the difference between Linear and Growth options.
For each function below, know 1) when to use each, 2) how to construct the arguments, 3) how to nest them, 4) what their arguments mean, and 5) what answers result from their use.
- Date Functions - TODAY, NOW, YEAR, MONTH, DAY, WEEKDAY
- Math, Statistical Functions - SUM, MIN, MAX, AVERAGE, COUNT, COUNTA, COUNTIF, ROUND
- Financial Functions - PMT, IPMT, PPMT
- Logical and Lookup Functions - IF, HLOOKUP, VLOOKUP, IFERROR
I will provide the syntax for only these functions:
- =PMT (rate,nper,pv,fv,type)
- =IPMT (rate,per,nper,pv,fv,type)
- =PPMT (rate,per,nper,pv,fv,type)
- =HLOOKUP (lookup_value,table_array,row_index_number,range_lookup)
- =VLOOKUP (lookup_value,table_array,column_index_num,range_lookup)
- =IFERROR (expression,value_if_expression_generates_error)
- =IF (logical_test, [value_if_true], [value_if_false])

Excel Charts

Know the common chart types and what each type is best at depicting.
How to set up charts. Know what cells to select as your Data Source for a chart.
Know how to format Pie and Column Charts.
Be familiar with elements of a chart: data series, category values, plot area, data markers, etc.
Know how to embed a chart as an object or place it on a new sheet
Know how to embed the source data with the chart object
What is it called when you pull out a slice of a pie chart?
What are the X and Y axes called in Excel charts?

Excel Tables and Pivot Tables

Know why you'd use tables, subtotals, filters, and pivot tables.
What are advantages/benefits of storing excel data as tables?
Know how to create, rename, and remove tables; how to create Totals row; how to change table formatting; how to split a table
How do you insert subtotals into a list? What are the levels of subtotals--how do they differ?
Know why we use filters, how to use Filtering, and how to set up a Custom Filter.
What is a PivotTable? How is it set up? When and how are its values updated? Can its style be changed?
What is a Slicer? How is it set up? Why is it used?

Excel Accessing Data on Multiple Worksheets, Workbooks

Know how to access cells in another worksheet of a workbook.
Know how to access cells in another workbook.