Textbook Notes (367,752)
Canada (161,368)
Dr.Hurst (18)

Excel Session 5.docx

10 Pages
Unlock Document

Computer Science

Comp Sci Session 5.1: An Excel Table Planning a Structured Range of Data (pg. 54)  Common use of worksheet is to manage data such as: o List of clients o Products o Transactions  Using excel you can o Store and update data o Sort data o Search for and retrieve subsets of data o Summarize data o Create reports  Data definition table is a documentation that lists the fields to be maintained for each record and a description of the information each field will include  You can work with data by applying the following operations o Add, edit, and delete data in the range o Sort the data range o Filter  To narrow the search o Insert formulas to calculate subtotals o Create summary tables based on the data in the range (usually with PivotTables) Freezing Rows and Column (pg. 56)  Freezing a row or column lets you keep headings visible as you work the data in a large worksheet. So when scrolling down the tile goes away. By using the freezing it does not matter how far you scroll down, the title will still be visible. MaclayoutWindow section Comp Sci Session 5: Working with Excel Tables, PivotTables and PivotCharts 1 Creating an Excel Table (pg. 57)  You can convert structured range into Excel tables.  Excel tables makes it easier to: o Identify data o Manage data o Analyze data When using an Excel table, you can perform all the same function/operations as you can on structured range of data. In addition, the use of Excel data is more productive and it reduces errors by performing the following functions: o Format the Excel table quickly using a table style. o Add new rows and columns that would automatically expand the range. o Do calculation of a total row by performing summary functions such as:  SUM  AVERAGE  COUNT  MIN  MAX o Enter a formula in one table cell and it will automatically copy that formula in all the cells in that column. o Create formulas that reference cells in a table by using table and column names instead of cell addresses. Renaming an Excel Table (pg. 58)  Excel assigns the name Table1 to the first Excel table you create. Then Table2 on the 2 and so forth.  Table names must start with a letter or underscore and you can use any combination for the rest of the name. How to rename on a Mac:  Tables  Tools  Rename Formatting an Excel Table (pg. 59)  The check box in the Table enables you to quickly and easily add or remove table elements or change the table’s formatting. Maintaining Data in an Excel Table (pg. 59)  Deals with updating your data. It can involve insertion of new data or deletion of data. Comp Sci Session 5: Working with Excel Tables, PivotTables and PivotCharts2 Adding Records (pg. 60)  You add a new record in an Excel table in blank rows. o You would enter a new data record in the first blank below the last record. Adding a Record to a Excel table o Click in the row below the last row of the Excel table. o Type the values for the new record o Use the Tab key to create another record or press Enter to finish. Finding and Editing Records (pg. 61)  Using the Find Command is an easy and accurate way of search for a particular data point.  Editing data in a field is the same way in editing data in a worksheet. Deleting a Record (pg. 62)  Using the Find Command, then delete the desired data point. o Use Find & Select button. Under the edit button. Sorting Data (pg.62)  You can either sort your data in ascending or descending order  Ascending Order o Arranges text from A Z. o Numbers smallest to largest. o Dates oldest to newest.  Descending Order o Arranges text from Z  A. o Numbers largest to smallest. o Dates newest to oldest. You can quickly sort a column by using the sort button A  Z or Z  A. Sorting Multiple Columns Using the Sort Dialog Box pg. 63) Obviously, in this case, you will have to sort more than one column at the same time.  The first sort field is called primary sort field, the second sort field is called secondary sort field and so on. o You can use up to 64 sort fields in a single sort Comp Sci Session 5: Working with Excel Tables, PivotTables and PivotCharts 3 Sorting Data Using Multiple Sort Fields o Click on any cell in a table or range. o In the Sort & Filter group on the Data tab, click sort. o If necessary, click the Add Level button to insert the Sort by row. o Click the column you want to sort, then click the Sort button o To sort a second column, click the add level button. Click the Then by arrow, select the column heading for the secondary sort field, click the Sort On arrow to select the type of data, and then click the Order arrow to select the sort order. o To sort additional columns, click the add level button and select appropriate Then by, Sort on, and Order values. o Click OK Sorting Using a Custom List (pg. 65)  Sorting by ascending and descending order is different than using a custom list.  A custom list indicates the sequence in which you want your data ordered.  Excel offers 4 predefined custom sort lists. Creating a Custom List o In the Sort & Filter group on the Data tab, Click the Sort button o Click the Order arrow, and then click Custom List o In the List entries box, type each entry for the custom list, pressing the Enter key after each entry. o Click Add. o Click OK. ****Session 5.1 Review Questions pg. 67**** Comp Sci Session 5: Working with Excel Tables, PivotTables and PivotCharts 4 Comp Sci Session 5.2: Filtering Table Data Filtering Data (pg. 70)  Filtering data temporarily hides the unwanted records that do not meet the specified criteria. o After the data is filtered, you can then sort, copy, format, chart and print it. o MacDataSort & Filter Filtering Using One Column (pg. 70)  In an Excel table, there is a filter arrow. The filter arrow opens the AutoFilter menu. o AutoFilter menu includes the option to Sort by colour and Filter by colour.  AutoFilter menu has three types of filters. You can filter the column of data by: o Cell colours o Font colours o Specified by  Text  Number  Date filter The filter choice depends on the data in the column. After you have filtered a column, the Clear Filter command becomes available to remove any filters.  You can use different text filters to display the records you want. If you want to match a certain text or pattern, you can use: o Begins With o Ends With o Contains operators These three functions can be used to filter a text field to match the pattern you specify. Filtering Using Multiple Columns (pg.72) If you want to narrow your search to a certain combination of records for example: o New home, 4 Bedrooms, Under $400,000 then you can filter by more than one column. Each additional filter is applied to the currently filtered data and it further reduces the records. Clearing Filters (pg. 73) If you want to redisplay all your data you must clear/remove the filters.  By re-clicking the filter button, you can clear the filters or click on the arrow key attached to the filter button. Comp Sci Session 5: Working with Excel Tables, PivotTables and PivotCharts 5 Selecting Multiple Filter Items (pg. 73) Before, we have been dealing with filtering a column to a single item. But now we want to filter so that we can narrow down to more than one entity in that single column. o Ex. Bedroom category for a new home.  Price: 3 bedrooms or 4 bedrooms. ***NOTE: These two conditions still fall under the same category, Bedroom. ***  The AND CONDITION requires that all of the selected criteria be true for the record to be displayed. o The AND will be used when inputting your formulas into Excel. Creating Criteria Filters to Specify More Complex Criteria (pg. 74)  Criteria Filters enables you specify various conditions such that it will meet all your criteria’s. (Mac  Arrow key for filters, under “Choose One”) o Ex. Having a home with 4 bedrooms and Less than $400,000. The type of Criteria Filter is available for change depending on the type of context you have in each column. Context such as: o Text o Date o Numbers Problem solving questions on pg. 75. Filter Criteria Records Displayed Equals Exactly match the specified text. Does Not Equal Does not match the specified text. Begins With Begins with the specified text. Text Ends With Ends with the specified text. Contains Have the specified text anywhe
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.