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

Comp Sci 1BA3 First Midterm Review

7 Pages
Unlock Document

Computer Science
Nicole Wagner

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


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.