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
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
To narrow the search
o Insert formulas to calculate subtotals
o Create summary tables based on the data in the range (usually with
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.
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:
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
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
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
o Arranges text from A Z.
o Numbers smallest to largest.
o Dates oldest to newest.
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
o After the data is filtered, you can then sort, copy, format, chart and print it.
o MacDataSort & Filter
Filtering Using One Column (pg. 70)
In an Excel table, there is a filter arrow. The filter arrow opens the AutoFilter
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
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:
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