COMP SCI excel review

7 Pages
95 Views

Department
Computer Science
Course Code
COMPSCI 1BA3
Professor
Anthony Hurst

This preview shows pages 1 and half of page 2. Sign up to view the full 7 pages of the document.
Description
th COMP SCI 1BA3: Exam review: 28 -sep-13 - look at visual overview in the text book – pg 4 Spreadsheet software: computer program used to enter, analyze and present quantitative data. A collection of text and numbers lay out in a rectangular grid.  it organises and allows to perform a ‘what-if analysis’ change values and assess the effects A workbook can have 2 different types of sheets: worksheets & chart sheets Worksheet: grid of rows and columns - each cell is identified with a cell reference- where rows and columns intersect Chat sheet: excel chart that provides a visual representation of worksheet data Creating effective workbooks: - plan out your work book  what problems do you want to solve? What data is needed? What calculations are required? Include a documentation sheet to describe your workbook BUTTON: f1  brings up the help function to search for shortcuts Autocomplete: makes assumptions therefore must always verify! 3 data types: - text: letters, numbers and symbols = form words & sentences -Number: any numerical value for calculation - Date and Time Truncated = when text entry gets hidden when cell is too small... in numbers it comes out like #### To enter lines within same cell = alt+enter Editings: click F2, double click, or in formula bar Enter when done, ESC to exit without changes Column widths expressed in pixels (a single point) standard is 64 pixels = 8.43 characters A row is usually 15 points = 20pixels = 1/72 of inch Change column width and row height (also applies to multiple selections) - drag if certain number of characters wanted… click entire column – cells – format – column width – insert character # OR.. you can go to cells- format – auto fit Inserting rows or columns - click inset button when the full row/column is selected and inserts to the right -right click Deleting rows or columns - right click – clear contents – delete row/column – rows below shift up Cell and cell ranges cell range – group of cells which have references if there is a colon :: between then it is everything taken in [adjacent] if a comma,,,, then it is nonadjacent Moving and copying cell ranges (drag and drop) - select the range and put cursor over border – changes and move it - to copy click ctrl - to copy/paste = clipboard tab on the home Inserting and deleting a range select range - home tab – inset button Formulas Formula – is an expression that returns a value begins with = Operators – combine different values - arithmetic operators -numbers stored within cells -order of precedence applies ^you can do a combination of cell references or cell reference with a number To enter formula - type the cell reference in onto the cell for the formula -instead of typing the cell reference – click on the cells that need to be used Copying and pasting formulas: click on the cell with the formula copy it into other cells = makes a pattern Absolute vs relative references relative: when creating a formula the references to cells or ranges are based on the position relative to the cell that contains them.. therefore the patterns emerge absolute: if you don’t want excel to adjust to a pattern or reference if you want A5 constant and to multiply it with C1, C2, C3.. you can’t just copy and paste.. you need to create an absolute reference… therefore … =$A$5*C1 Paste special: to paste to particular parts of a cell selection home tab – paste – paste special : select what needs to be paste, and which operation View all formulas by pressing “ctrl+” …Functions To add a group =SUM(A1:A3) Home – editing – auto sum  sum, average, count, min, max Excel Tables: structured range of data that is managed independently from the rest of the data Each column represents a ‘field’ (characteristic of a person, place or thing) Each row represents a ‘record’(unique entity or occurrence) First row = header row= contains column headings Excel table vs structured range - Format table quickly - Range is expanded - Can add a total row - Sort, and filter - Create formulas using table names than cell addresses Sorting: puts rows of the table in particular order home ribbon – data – sort button Filtering: hides rows that don’t meet criteria home ribbon – data - filter Totals & subtotals totals – available through the ‘total row’ box tables tools – design – total row (last row appears on the table with value) Subtotals: cannot be used in a excel table can only be applied to ranges tools – table tools – convert to range—subtotal button – at each change Outline levels: allow you to view the data in various levels of detail. – each group summarises Structured references – specific cell or range address with a structured reference/table name/column header. THIS STRUCTURED REFERENCE IS EASIER TO UNDERSTAND: THAN THIS CELL REFERENCE: =SUM(DeptSales[SaleAmt]) =Sum(C2:C7) qualified vs unqualified references: - unqualified: don’t include the table name: use in a calculated column or formula within an excel table *doesn’t exist in the table+ - when referring to excel table: use a qualified reference [exists in the table] PIVOT TABLES: when large tables are difficult to interpret - allows us to summarize data into categorie
More Less
Unlock Document

Only pages 1 and half of page 2 are available for preview. Some parts have been intentionally blurred.

Unlock Document
You're Reading a Preview

Unlock to view full version

Unlock Document

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