Textbook Notes
(363,149)

Canada
(158,223)

McMaster University
(8,211)

Computer Science
(45)

COMPSCI 1BA3
(24)

Anthony Hurst
(6)

Chapter

# Excel.docx

Unlock Document

McMaster University

Computer Science

COMPSCI 1BA3

Anthony Hurst

Fall

Description

Comp Sci Notes: EXCEL
Session 1:
• Excel stores spreadsheets in workbooks
• Name box- displays cell reference of active cell
• Select All- selects all active cells
• Row Headings- numbered rows
• Formula bar-displays value
• Workbook is made of sheets- worksheets and chartsheets
• Sheet Tab scrolling scrolls through sheets
• Zoom controls zoom in out
• Column headings- letters identifying different columns
• What-if analysis- change one value, changes the others
• Ribbon- top heading bar
• Chart sheets contain visuals and can be placed in Worksheets
• Ctrl page up and down changes sheets
• Planner sheet- what problems solved? What data needed? What calculations
entered? What form should my solution be in?
• Text data= text string
• Text if left aligned, numbers are right aligned
• Truncated- hidden texts (cells too small)
• Autocomplete-feature that tries to predict what you are typing in the cell
reference box
• Alt and Enter make multiple lines in a cell
• A pixel is a single point on a computer monitor • A point Is 1/72 of an inch
• Autofitting fits all data into biggest cell throughout column
• Clearing-removes data Deleting- removes data and cells
• Spellchecker
• Page Layout tab- changes way worksheets are appeared
• Group of cells is a cell range
• Status bar- ready or in edit mode
• Non adjacent range- two or more distinct non adjacent ranges
• View- changes how viewed
• Adjacent range- group of cells in single block of cells
• Auto Sum- Sum average etc, formulas
• Range Reference- location and size of range
• Drag and drop- drag range and replace it
• Cutting- cut and paste
• Operators- used to make formulas combining diff values making one value
• Arithmetic operators- perform additions subtraction etc
• Order of precedence- is a set of predefined rules used to determine sequence in
which operators are used.
• Functions- named operation performs a value
• Edit mode- shortcuts only apply to texts
• Find command to locate characters- search string-replacement string
• Normal View, Page Break Preview (page breaks), Page layout view (printed)
• Portrait and Landscape
• Formula view- only formulas viewed
• A formula is an expression that returns a value Begins with =
Operators combine different values
Generally arithmetic operators ex Sum, Divide
Usually use numbers stored within cells
Order of precedence applies (OOO)
• To enter a formula:
Type in a cell
Select cells
Verify via F2 (which cells are in formula)
• Scaling- fitting pages to print
• Rows and Columns intersect at cells- cell reference
• Intersection of a row and column is a cell
• The file tab opens backstage view- file level options and settings
• Related commands are organized in groups on a tab
• The dialog box launcher opens options for a tab
• F1- help key
• 3 types of DATA
- Text: a combination of letters, numbers, and some symbols that form words and
sentences
-Number: any numerical value that can be used in a mathematical calculation
-Date and Time: commonly recognized formats for date and time values
• The main reason to use excel is to display values calculated
Session 5 • To enter a function:
- Type
- select cells
- Function wizard
• AutoSum can be used for Sum, Average, Count, Minimum, and Maximum
- Be cautious as this function makes assumptions, use F2 or double click to verify
• To edit, F2 editing mode, esc to erase edits and enter to complete them
• Table name
• Each column represents a field
• Header row, beginning row
• Rows represent a record
• Filter order- filters records
• An Excel Table is a structured range of data that is managed independently
from the rest of the data in a worksheet. Why do we use excel tables?
- Can format table quickly
- The table range is expanded automatically
- Can add a total row to calculate summary functions
- Formulas entered in one table cell are automatically copied down
- Can create formulas that reference cells in a table using table and column
names, rather than cell addresses
- Can sort and filter data easily
• Sorting - columns filtered
-Can be applied to one or more columns
-Custom sorting lists can be created
• Filtering -Can be applied to one or more columns
-Can filter by exact values or broader criteria filters
• Data definition table- list fields to be maintained for each record
• Freezing lets you keep headings
• Totals and Subtotals
-Totals available through the “Total Row” box
-Subtotals can only be applied to ranges
-Sort the data by the column you want to subtotal
-“Convert to Range”
-Data tab, Outline group, Subtotal button
-“At each change in” (sorted column)
-Outline levels allow you to view the data in various levels of detail
• Convert table to range for subtotals etc hides total row
• Structured References
-You can replace the specific cell or range address with a structured reference,
the actual table name or column header
-Syntax:
-Tablename[qualifier]
-Example: Summary [Sales]
• Qualified vs. unqualified references
-Unqualified references don’t include the table name.
-You can use unqualified references in a calculated column or formula within an
Excel table, however you need to use qualified references outside the table or in
another worksheet when referring to an Excel table or a portion of the table.
• Ascending order filters a-z, descending z-a
• First sort field is primary, second is secondary • Custom list- indicates sequence you want data to be ordered
• Selections list- displays unique items in selected column
• Split bar- division between panes
• Clear- removes filters
• Criteria filters- enable you to specify various conditions in addition to those that
are based on an equal’s criterion.
• The layout is the four categories you can see in the bottom right corner of the
pivot chart layout
• A Pivot table- puts data in a concise table
-Sometimes large tables can be difficult to interpret
-PivotTables allow us to summarize data into categories using functions (detailed
data is not shown)
-Quick and concise analysis
-Named as such since data can easily be “pivoted” or moved around in the table
• Terminology of Pivot Tables
-Values fields- the fields containing the data to be summarized
-Category fields- the fields that the value fields will be grouped by
-Rows
-Columns
• PivotChart- of a table
• Slicer- filters pivot charts and tables
• Category Fields- are the fields that group the values in a pivot table.
• Filtering a field helps focus on one thing Session 7:
• Structured Reference- When you create a formula that references all or parts
of an Excel table, you can replace a specific cell or range address with a
structured reference which is the actual table name or column header
• The AND FUNCTION- logical function that returns a TRUE value if ALL are
true, and a FALSE value if any or all of the logical conditions are false, used
for multiple conditions.
-Syntax:
=AND (logical1 [,logical2]…)
Can nest in an IF statement as the logical test
Reminder: =IF(logical_test, value_if_true,[value_if_false])
Example: =IF(AND(A1=1,a2=1),”yes”,”no”)
• Functions Argument box, box to build functions
• The IF FUNCTION is a logical function that evaluates a condition, and then
returns one value if the condition is true and a different value if it’s false
-Syntax:
=IF(logical_test, value_if_true, [value_if_false])
• Calculated Column- formula in one cell=all cells
• Use AND function as logical test of IF function to test two or more conditions
to see if all are TRUE
• To determine if ANY are TRUE, use OR function as logical test of the IF
function
• OR Function
-Allows you to test multiple conditions
-Returns either TRUE or FALSE
-Returns TRUE if any tested values are TRUE and FALSE if all of the tested
values are FALSE
-Syntax: =OR (logical1 [,logical2]…) -Can nest in an IF statement as the logical test
-Reminder: =IF(logical_test,value_if_true,[value_if_false])
-Ex. =IF(A1=1,A2=1),”yes”,”no”)
• A logical condition is an expression such as H2=”Y” that returns either a
TRUE or FALSE value.
• A NESTED IF function is when one IF function is placed in another to
evaluate another condition
-Nested IF Functions
=IF([Pay Grade]=1,2500,
IF([Pay Grade]=2,5000, 7500))
Structured references make the formula easier to write, use absolute
reference b/c this value is the same for all rows
• LOOKUP TABLE
-A table that organizes data you want to retrieve into different categories
-The categories for the lookup table, called compare values, are located in
the table’s first column or row
-To retrieve a particular value from the table, a lookup value (the value you
are trying to find) needs to match the compare values • Using the lookup table to find an exact match
-VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Change range to make an approximate lookup match
• The LOOKUP VALUE is the value you are trying to find.
• Invalid code- IFERROR
• VLOOKUP function searches vertically down the first column of the lookup
table and retrieves the appropriate value from the table
• Lookup functions allow you to use tables of data to lookup values insert them
in another worksheet location.
• Absolute reference- when you only reference on condition and one formula
that changes throughout the field, this is when you need to use absolute
referencing
• Relative Referencing- when using one formula that relates to all the other
fields you can just drag it down and relative reference the formula with all
cells.
• An EXACT match lookup occurs when the value entered must match one of
the values in the first column of the lookup table.
• Approx match lookup occurs when the value entered falls within a range of
values in the first column of the lookup tables
• HLOOKUP function searches horizontally across the top row of the lookup
table and retrieves the value in the column you specify.
• HLOOKUP syntax: HLOOKUP(lookup_value, table_array, row_index_num,
[range_lookup])
• Errors values indicate that some element of the formula or a cell referenced in
a formula is preventing excel from returning a calculated value.
• Formatting (colors, highlighting etc.)
• Conditional Formatting- change format when something specific occurs
• Create a new rule in conditional formatting box- Rules manager- type of
formatting when something occurs • It defies the condition cell and range to put the rule on
• Rules manager-edit conditions and rules
• Error values such as #DIV/0!, #N/A, and #VALUE! indicate that some element
in a formula or a cell referenced in a formula is preventing Excel from
returning a calculated value
• The IFERROR function can determine if a cell contains an error value and
display the message you choose rather than the default error value
• You can calculate the number of cells in a range that match criteria you
specify using the COUNTIF function, which is sometimes referred to as a
conditional count
-Syntax:
=COUNTIF(range,criteria)
• You can add the values in a range that meet criteria you specify using the
SUMIF function, which is also called a conditional sum
-Syntax:
=SUMIF(range,criteria[,sum_range])
• You use the AVERAGEIF function to calculate the average of values in a
range that meet criteria you specify
-Syntax:
=AVERAGEIF(range,criteria[,average_range])
• You use the AVERAGEIF function to calculate the average of values in a
range that meet criteria you specify -Syntax:
=AVERAGEIF(range,criteria[,average_range])
Session 10.1-10.2
• What if Analysis
-What is it?
-Different methods available
-Goal Seek
-One-Variable Data Table
-Two-Variable Data Table
-Scenarios
Solver
• Cost-volume-profit (CVP) analysis (sometimes called a break event analysis)
expresses the relationship between a company’s expenses, its volume of
business, and the resulting profit or net income
-Types of expenses
-Variable expenses change in proportion to the amount of business a
company does (i.e. manufacturing materials)
-A fixed expense is an expense that must be paid regardless of sales volume
(i.e. rent)
• The break-even point is the point where revenue equals expenses
-Breakeven, Net income = 0
• We can input an income statement into Excel and then perform:
-What if analysis:
-Change the

More
Less
Related notes for COMPSCI 1BA3