I ask not for a lighter burden but for broader shoulders jewish proverb
1 / 48

Day 10 : Excel chapter 5 - PowerPoint PPT Presentation

  • Uploaded on

I ask not for a lighter burden, but for broader shoulders . ~ Jewish Proverb. Day 10 : Excel chapter 5. CS 101 [email protected] 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.
Download Presentation

PowerPoint Slideshow about 'Day 10 : Excel chapter 5' - lakia

An Image/Link below is provided (as is) to download presentation

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 - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
I ask not for a lighter burden but for broader shoulders jewish proverb

I ask not for a lighter burden, but for broader shoulders. ~Jewish Proverb

Day 10 excel chapter 5

Day 10:Excel chapter 5

CS 101

[email protected]

February 10th, 2014

Large datasets
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
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?

Freeze options
Freeze options

Table 5.1

Other options
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
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.

Table advantages
Table Advantages

  • 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
Table Design

  • A field is an individual piece of data

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

Sample table
Sample table

ID Field


Creating a table from existing data
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

Select the data
Select the data

Insert -> Table

Adding records rows
Adding records (rows)

  • Click a cell below where you want the new record

  • Home Tab -> Cells Group -> Insert

  • Insert Table Rows Above

Delete a record
Delete a record

  • Select a cell in the record to be deleted

  • Home Tab -> Cells Group -> Delete

  • Delete Table Rows

Add delete fields columns
Add/delete fields (COLUMNS)

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

Sorting data
Sorting data

Table 5.3

Sort by multiple criteria
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 data

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

Things to filter by
Things to filter by

  • Dates

  • Numbers

  • Specific Words (Text)

To add a filter to a field
To add a filter to a field

  • Click a cell in the field

  • Data Tab -> Filter

  • Specify Criteria

Structured references
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
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 function

Subtotal calculates an aggregate for values in a range or database

Subtotal function form
Subtotal function form


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

Function num

Table 5.4

Conditional formatting
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
HighLIGHT Cells

  • Home Tab -> Styles group -> Conditional Formatting

  • Highlight Cells Rules

Top bottom 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
Custom Rules

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

  • Home -> Conditional Formatting -> New Rule

Adding remove modify rules
Adding/remove/modify rules

Home Tab -> Conditional Formatting

-> Manage Rules