Study Guides (248,410)
Canada (121,516)
Midterm

Comp Sci 1BA3 First Midterm Review

7 Pages
137 Views
Unlock Document

Department
Computer Science
Course
COMPSCI 1BA3
Professor
Nicole Wagner
Semester
Fall

Description
Comp Sci 1BA3 Mid-Term Review Intro To Excel Session 1.1  Spreadsheet software – collection of text and numbers laid out in a rectangular grid; often used in business for budgeting, inventory; electronic versions are created through Excel  What-if analysis – change one or more values in a spreadsheet and asses the effect those changes have on the calculated values  Workbook – can contain two types of sheets: worksheets and chart sheets  Worksheets – grid of rows and columns where data values and formulas are entered  Chart sheets – Excel chart; visual representation of worksheet data  Multiple sheets allow for more specific division – separating into categories  Include a documentation sheet to describe your workbook  Cell reference – identifies a cell within a worksheet; indicates column and row location  A1 is always the default active cell  Planning analysis sheet – finding workbook’s purpose and how to achieve the desired results (what do I want to solve; what data is needed; what calculations need to be entered; what form should it take)  Navigating – generally multiple ways to do things in excel; F1 is a good tool brings up Help function, search for shortcuts for things you do often  Text data (text string) – any combination of letters, numbers, and symbols that form words and sentences (left-aligned)  Number data – any numerical value used for mathematical calculations (right-aligned)  Date and Time data – recognized formats for date and time values (right- aligned)  AutoComplete – Excel tries to anticipate the remaining characters as you type text into the active cell by similarities in previous entries in the same column  Wrapping – the automatic reflow of text within a cell  Dates – Excel stores them as numeric values to perform date and time calculations; it might alter the date format but doesn’t affect the underlying date/time value  ISO – International Organization for Standardization; international date format of yyyy-mm-dd adopted by many international businesses  If a number exceeds its cell size, you see ##### for the truncated numeric value – increase the column width  You can type 8 characters in a cell before the entry is truncated  If you work on multiple computers you should set column widths based on max # of characters rather than pixel size  The height of an excel row is measured in points or pixels  Autofitting – eliminates empty space by matching the row height or column width to its tallest or longest cell entry  Inserting – when you insert a new column or row the existing columns/rows are shifted to the right/downwards  Clearing data – removes the data, leaving blank cells  Deleting data – removes both the data and the cells  Cell A1 contains May 3, 2012; why doesn’t Excel consider this entry a text string? Because Excel stores dates as a numeric value – allowing for date and time calculations between one date and another Session 1.2  Cell range (range) – a group of cells; can be moved, copied, inserted and deleted  Cell reference – indicates the location of an individual cell  Range reference – indicates the location and size of a cell range  Adjacent range: includes the cell reference of the upper-left and lower- right cells in the rectangular block separated by a colon  Non-adjacent range: separates each adjacent range reference by a semicolon  Formula – an expression that returns a value (must begin with = or Excel assumes it is text)  Operators – what formulas are written with; combine different values  Arithmetic operators – most commonly used operator; preform addition, subtraction, multiplication, division and exponentiation  * Most formulas contain references to cells rather than specific values – allows you to change the values in the calculation without modifying the formula itself  If a formula contains more than one arithmetic operator order of precedence applies (BEDMAS)  Formulas can be copied and pasted – beware of absolute vs. relative reference (absolute referencing $A$1 will ensure that the value is taken out of only that cell instead of continuing down the column)  Function – a named operation that returns a value, generally used to simplify formulas  AutoSum – used for Sum, Average, Count, Minimum, Maximum  Effective formulas – keep formulas simple (use functions when possible), don’t hide data values in formulas, show intermediate results to make it easier to follow the logic  Worksheets – can preform many actions on them; insert, delete, rename, move, copy, tab colour  Edit mode – to edit the cell contents you can enter editing mode (F2/double click/click on formula bar), press Enter when done, press Esc to exit without applying changes  Find and replace – enter a search string and replacement string to change every occurrence of a string in a worksheet with another  Page break preview – displays the location of the different page breaks within the worksheet  Formula view – a view of the worksheets contents that displays formulas instead of the resulting values Excel Tables Session 5.1  Field – represented by each column; an attribute or characteristic of a person, place or thing  Record – represented by each row; a specific, unique entry or occurrence  Header row – the first row in the table; contains column headings  Structured range – an Excel Table is a structured range of data that is managed independently from the rest of the data in a worksheet  Data definition table – documentation that lists the fields to be maintained for each record and a description of the information each field will include  Freezing – keeps headings visible as you work with the data in a large worksheet  An Excel table makes it easier to identify, manage, and analyze the groups of related data. You can preform the same operations as you can for a structured range of data while being more productive and reducing the chance of error with table features that let you focus on analyzing and understanding the data  Sorting data – can be applied to one or more columns, custom sorting lists can be created (ascending and descending order options)  Sort dialog box – the operation used to specify the sort criteria for the primary sort field, secondary sort field etc.  Custom list – indicates the sequence in which you want data ordered Session 5.2  Filtering data – temporarily hides any records that do not meet the specified criteria; can be applied to one of more columns; can filter by exact values or broader criteria filters  Criteria filters – enables you to specify various conditions in addition to those that are based on an “equals” criterion  Total row – calculating summary statistics on all the columns in an Excel table or on a filtered table with a Total row  Subtotals – can only be applied to ranges, not tables  Subtotal outline view – outlines your worksheet so you can control the level of detail that is displayed Session 5.3  PivotTables – allow us to summarize data into categories using functions (detailed data is not shown); quick and concise analysis; data can be easily pivoted or moved around in the table – useful because large tables can be difficult to interpret  Values fields – fields containing the data to be summarized  Category fields – fields that the value fields will be grouped by (rows; columns)  Report filter – used to filter the PivotTable to display summarized data for one or more field items or all field items in the Report Filter area  Filtering – filtering a field lets you focus on a subset of items in that field; you can filter items in the PivotTable by clicking the field arrow button in the PivotTable that represents the data you want to hide  Sorting – you can sort a PivotTable field either by its own items (Location, Category) or by the values in the body of the PivotTable  Adding fields – you can expand to create a more informative table by adding fields to the Values layout area  Slicer – provides a quicker and easier way to filter a PivotTable or PivotChart  Refreshing – you cannot change data directly in a PivotTable; must edit the Excel table, and then refresh to reflect t
More Less

Related notes for COMPSCI 1BA3

Log In


OR

Join OneClass

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

Sign up

Join to view


OR

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.


Submit