Textbook Notes (367,976)
Canada (161,540)
Dr.Hurst (18)

Computer Science - Excel Notes.docx

12 Pages
Unlock Document

Computer Science

Chapter 1  Use excel to do quantitative analysis (numerical analysis)  Know what rows, columns, etc. are  What are the things you want to keep in mind when creating a workbook (planning, etc.)?  Three different data types used in excel: Dates, Numbers, Text  Why is it important to know the difference between those  On first tab create documentation tab telling people how to use it, what its about, etc. Introducing Excel  Spreadsheet: collection of text and numbers laid out in rectangular grid (used in business for budgeting, inventory management, decision making)  What-if-analysis: when you change one or more values in a spreadsheet and assess the affect those changes have on the calculated values  Use excel to store data, generate reports, analyze data  Workbook can have two types of sheets: o Worksheet: contains grid of rows and columns where user enters data o Chart sheet: contains Excel chart providing visual representation of worksheet data  Having multiple sheets allow you to better organize data in the workbook  Cell reference: the column and row location identifying each cell in the worksheet (ex. B6)  Can move directly to a specific cell using the “Go To” dialog box or by typing in a cell reference in the Name box Planning a Workbook  Should develop a plan before entering data into workbook  Use a planning analysis sheet: 1. What problems do I want to solve? The answer defines the goal or purpose of the workbook. 2. What data do I need? The answer defines the type of data that you need to collect and enter into the workbook. 3. What calculations do I need to enter? The answer defines the formulas you need to apply to the data you’ve collected and entered. 4. What form should my solution take? The answer defines the appearance of the workbook content and how it should be presented to others. Entering Text, Numbers and Dates  Text data: any combination of letters, numbers and symbols that form words and sentences  Number data: any numerical value that can be used in a mathematical calculation  Date and Time data: formats for date and time values (left-aligned)  AutoComplete: helps make entering repetitive text easier (does not work with dates or numbers)  To make two lines of text in a cell press Alt key while pressing Enter  Date as numerical value: number of days between date and January 0, 1900  Storing dates/times as numerical values lets excel perform date/time calculations (ex. Determining elapsed time between two dates) Working with Columns and Rows  To make cell content easier to read, resize columns and rows in worksheet  Default column width: 8.43 standard-sized characters (64 pixels)  Default row height: 15 points (20 pixels)  Can define specific column width/row height using Format command  When insert new column, has same width as one to its left  When insert row, has same height as row above it  Clearing: removes data from worksheet leaving blank cells  Deleting: removes data and cells from worksheet Working with Cells and Ranges  Range reference: indicates location and size of a cell range  Adjacent ranges: upper-left and lower-right cells in rectangle (ex. A1:G5)  Non-adjacent range: separate each adjacent range by semi-colon (ex. A1:A5;F1:G5)  Drag and drop: select range and drag it to new location  Cutting: places cell contents on to Clipboard so can paste to new location  When insert cells, existing cells shift down when selected range is wider than it is long, shift right when longer than it is wide Working with Formulas  Operators: combine different values resulting in single value  Arithmetic operators: perform addition, subtraction, multiplication, division and exponentiation  Formulas contain cell references so you can change value without having to modify formula  Order of precedence: set of predefined rules used to determine sequence in which operators applied in calculation (BEDMAS)  Can copy formulas down a row to save time and avoid potential mistakes in retyping same formula over and over Introducing Functions  Function: named operation that returns a value (used to simplify formulas)  Less likely to make a mistake (ex. =SUM(F1:F10))  Avoid putting data values within formulas, make an outside cell reference  Break up formulas to show other people all the sums calculated in separate places instead of one formula so they can better understand final result  AutoSum can quickly generate: o Sum of values in column/row (SUM) o Average value in column/row (AVERAGE) o Total count of numeric values in column/row (COUNT) o Minimum value in column/row (MIN) o Maximum value in column/row (MAX) Editing Worksheet Content  Use Find & Select button to replace certain things in workbook with different things  Search string: string of characters you need to locate to change  Replacement string: new text characters that replace the previous characters Previewing and Printing a Workbook  Normal view: shows contents of worksheet  Page Layout view: shows how worksheet will appear when printed  Page Break Preview: shows location of different page breaks within worksheet (can control what content appears on what page)  Can change worksheet from portrait to landscape orientation to print entire worksheet on one page  Formula view: view of worksheet contents that displays formulas instead of resulting values (use Ctrl + ‘)  Scaling: reduces width and height of printout to fit number of pages by shrinking text size Chapter 5  No questions about Pivot Charts only tables  Structured range of data a bunch of records/fields that represent some set of data  We use tables to be more organized, automatic, use names of columns in references  Sorting, filtering of subtotals  Use pivot tables to categorize things more and analyze at a higher level  Looking at more summarized data Planning a Structured Range of Data  More common use of worksheet is to manage data (lists of clients, products, transactions)  Can store/update data, sort, search/retrieve data, summarize, create reports  Structured range of data is a list or table  Data definition table: documentation that lists fields to be maintained for each record and description of info each field will include  Freezing: keeps the headings of a row/column visible while scrolling down the worksheet  Makes it easier to identify data in each record Creating an Excel Table  Excel table makes it easier to identify, manage and analyze groups of related data  Productive and error reducing table features: o Add new rows/columns to table that automatically expand range o Add Total row to calculate summary function (ex. SUM, COUNT, MAX) o Enter formula in one cell that is automatically copied to all other cells in that table column o Create formulas that reference cells in a table by using table/column names instead of cell addresses  Can assign specific name to table making it easier to identify table by content  Table names must start with letter or underscore and cannot include spaces  Check boxes in Table Style Options on Design tab can remove/add elements and change formatting of table (ex. Display/hide header row) Maintaining Data in an Excel Table  Use Find command to locate a record and edit data in this field Sorting Data  Can sort the data in the column you want by using it as the sort field  Ascending order: arranges text from A-Z, smallest to largest, oldest to newest  Descending order: arranges from Z-A, largest to smallest, newest to oldest  Sometimes need to sort more than one column (should use Sort dialog box)  Primary sort field: first sort field  Secondary sort field: second sort field (etc.…)  Can use up to 64 sort fields in a single sort  Custom list: indicates the sequence in which you want the data ordered  Predefined custom sort lists: two days-of-the-week, two months-of-the-year  Use Sort & Filter group on Data tab, click Sort, click Order arrow then Custom List, enter each entry for custom list, click Add Filtering Data  Filtering data temporarily hides any records that do not meet specific criteria  In excel table, each column header has a filter arrow  Can filter column of data by: cell colours/font colours; specific text, number, data; or by selecting exact values you want to filter in column  Can filter using Begins With, Ends With and Contains operations  Ex. “Begins with Sm” would show all records beginning with “Sm”  Can further filter data; each additional filter applied to currently filtered data  AND condition requires all of selected criteria be true for record to be displayed (ex. “Category equals Painting” AND “Condition equals Poor”)  OR condition requires only one of selected criteria be true for record to be displayed (ex. “Category equals Installation” OR “Category equals Sculpture”)  Criteria filters: enable you to specify various conditions in addition to those that are base on “equals” criterion  Ex. All art objects valued greater than $20,000 acquired after 7/1/2011  Filtering limits data enabling you to more effectively analyze data  Use Sort dialog box to perform more complex sort Using the Total Row to Calculate Summary Statistics  Can calculate summary statistics (sum, average, count, max, min) on all columns in Excel table or on filtered table  Click “Total Row” in Table Style Options group on Table Tools Design tab  Can split worksheet window into two or four separate panes (use Split)  Can easily view data from several areas of worksheet at same time  To create two panes: select cell in row 1 or column A  To create four panes: select any other cell Inserting Subtotals  Summarize data in a range by inserting subtotals  Subtotal command: inserts subtotal row into the range for each group of data and adds grand total below last row  Must sort data before using Subtotal command (because inserts subtotals wherever value in specified field changes)  Subtotal command cannot be used in Excel tables  Subtotal feature also outlines worksheet to control level of detail displayed  Level 3: individual records, subtotals and grand total (most detail)  Level 2: subtotals and grand total  Level 1: only the grand total Analyzing Data with PivotTables  PivotTables help organize data into meaningful summaries  Must specify which fields in data source you want to summarize  Value fields: fields containing summary data  Category fields: fields that group values in PivotTable (condition, location, year acquired, etc.)  Category fields used as row labels, column labels and report filters  Can easily hide, rearrange and display different categories in table  Best to create a layout of how you want PivotTable to look before  Great way to summarize data to help support specific points  Can display data as percentages of totals (useful for comparing product sales with total sales/comparing expense categories to total expenses) Creating a PivotTable  Must specify where to find data for PivotTable (ex. Excel table/range)  Specify whether to place in new or existing worksheet  If in existing worksheet must specify cell that upper-left corner will be in  Place fields from field list (from table) into four areas: o Report Filter: field used to filter report by selecting one or more items, enabling you to display subset of data in PivotTable report o Row Labels: fields you want to display as row (one row displayed for each unique item, can have nested row fields) o Column Labels: fields you want to display as columns (one column displayed for each unique item, can have nested column fields) o Values: fields you want to summarize  Default: uses SUM function for numbers in Values area, uses COUNT function for text/nonnumeric values (can change this to whatever function you need)  Can view data in different ways by rearranging fields  Compact report layout: places all fields from row area in single column and indents item from each field below outer fields  Outline report layout: each field in row area takes column in table (shows subtotals for each group at the top of every group)  Tabular report layout: displays one column for each field and leaves space for column headers (total appears at bottom of each group)  Filtering a field lets you focus on subset of items in that field (like in table)  Can sort PivotTable like you sort an Excel table  Can hide field headers and expand/collapse buttons in Show group options  Slicer: provides quicker and easier way to filter PivotTable Refreshing a PivotTable  Cannot change data directly in PivotTable  Edit data in Excel ta
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.