Study Guides (248,283)
Canada (121,453)
Dr.Hurst (16)

Excel Midterm Review

4 Pages
Unlock Document

Computer Science

Hanson 1 Excel Midterm Test Review 5.1 An Excel Table Freezing a row or column lets you keep headings visible as you work with the data in a large worksheet Creating an Excel Table  Can convert a structured range of data to an table  Makes it easier to identify, mage and analyze groups of related data  You can rename tables for better organization  Check boxes in the Table Style Options group enable you to quickly add or remove table elements/change the table’s formatting o i.e., you can display or hide the header row Maintaining Data in an Excel Table  To “Add Records” it’s most efficient to enter data in the first blank row below the last record  To “Find Records” it’s easiest to use the “Find” command Sorting Data  Primary sort field and Secondary sort fields can be used to sort multiple columns with different criteria 5.2 Filtering Table Data  To filter using one column, click on the filter arrows and use the drop-down menu  To filter multiple columns, click on the filter arrows and check box desired categories Using Total Rows to Calculate Statistics  You can calculate the sum, average, count, maximum and minimum  Located in “Design” menu, checkbox Total Row Inserting Subtotals  You must reorder the data, convert it back to a range, then re convert back to table  The subtotal outline view can be switched using the tabs on the left 5.3 Pivot Tables  Helps organize data into meaningful summary  Quick and concise analysis, data can be “pivoted” Hanson 2 7.1 Logical Functions Using the IF Function  Evaluates whether a condition is true or false  Returns one value if true, another value if false Using the AND Function  Allows you to test multiple conditions  Returns either true or false  Only returns true if ALL tested values are true Using the OR Function  Allows you to test multiple conditions  Returns either true or false  Returns true if ANY values are TRUE  Returns false if ALL of the tested values are FALSE Structured References  The reference you use of a column header or table name when inputting into a formula  You can replace the specific cell/range address with a structured reference 7.2 Nested IFS and Lookup Tables Nested IF Functions  Is when one IF function is place inside another, to test an additional condition  Flowcharts are often used to illustrate  Important to use “absolute referencing” so the value is consistent for all rows o Placing ‘$’ beside coordinates Using Lookup Tables and Functions  Is a table that organizes data you want to retrieve into diff. categories  Allows you to use tables of data to “look up” values and insert them in another worksheet location  Compare values—categories for the lookup table (found in table’s first column/row
More Less

Related notes for COMPSCI 1BA3

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.