# Day 10 : Excel chapter 5 - PowerPoint PPT Presentation

1 / 48

I ask not for a lighter burden, but for broader shoulders . ~ Jewish Proverb. Day 10 : Excel chapter 5. CS 101 cody.cutright@mail.wvu.edu February 10 th , 2014. Large datasets. So far we have covered relatively small data sets

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Day 10 : Excel chapter 5

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

## Day 10:Excel chapter 5

CS 101

cody.cutright@mail.wvu.edu

February 10th, 2014

### Large datasets

• So far we have covered relatively small data sets

• What if we need to navigate large amounts of data?

### How can we fix it?

• In the previous example we could not see our column titles

• This becomes very difficult with multiple columns of similar data

• Is there a way to keep our headers?

Table 5.1

G3

Freeze Panes

### Other options

• Freeze Top Row: Freezes only the top row of the excel sheet.

• Freeze Top Column: Freezes only the first column of the excel sheet.

### Data tables

A table is an area in the worksheet that contains rows and columns of related data formatted to enable data management and analysis.

• Freeze headings on screen during scrolling

• Filter lists

• Predefined table styles (i.e. table colors)

• Create and edit calculated columns

• Have a calculated total row with which you can perform a variety of functions

• Data exports/imports

### Table Design

• A field is an individual piece of data

• A record is a complete set of data for an entity

ID Field

Record

### Creating a table from existing data

• Click within the existing range of data

• Insert Tab

• Tables group -> Table

• Fill out the Create Table dialog Box

• Click OK

Insert -> Table

### Voila!

• Click a cell below where you want the new record

• Home Tab -> Cells Group -> Insert

• Insert Table Rows Above

### Delete a record

• Select a cell in the record to be deleted

• Home Tab -> Cells Group -> Delete

• Delete Table Rows

Sometimes you may need to add a field,to insert a field:

• Click a cell to the right of where the new field (column) will be

• (If you want a new field between columns A & B, click a cell in column B)

• Home Tab -> Cells Group -> Insert

• Insert Table Columns to the Left

Table 5.3

### sort by multiple criteria

• Select any cell in the table

• Data tab -> Sort & Filter Group -> Sort

• Select the primary sort level by clicking the Sort by arrow, selecting the column, then the sort order

• To add another level, click Add Level, select the second level by selecting Then By, select the order… repeat

### Filtering data

Filtering is the process of displaying only records that meet specific conditions.

### Things to filter by

• Dates

• Numbers

• Specific Words (Text)

### To add a filter to a field

• Click a cell in the field

• Data Tab -> Filter

• Specify Criteria

### Structured references

• Used to create results by performing calculations on columns

• Of the form =[Row1]-[Row2]

• Can still use semi-selection

• Make sure you use the brackets

### Total Row

A Total Row appears below the last row of records and enables you to display summary statistics.

• Design Tab

• Table Style Options -> Total Row

• Select each cell, then the function

### Subtotal function

Subtotal calculates an aggregate for values in a range or database

### Subtotal function form

=SUBTOTAL(function_num,ref1,…)

***The benefit of subtotal is that it will only display records that have been filtered.

Table 5.4

### Conditional formatting

Helps you and your audience understand a dataset better by adding a visual element to the cells.

(A failing grade, highlighted in red for instance)

### HighLIGHT Cells

• Home Tab -> Styles group -> Conditional Formatting

• Highlight Cells Rules

### Top/Bottom Rules

• Right Below Highlight Cells

• Allows you to format by either a percentage (i.e. top 10%), or by a number (i.e. the last 10)

Example use: Finding the ten lowest grossing car lots, so you can close them.

### Custom Rules

• If the default formatting options don’t fit your needs, you can create your own formatting rules.

• Home -> Conditional Formatting -> New Rule