I ask not for a lighter burden but for broader shoulders jewish proverb
Sponsored Links
This presentation is the property of its rightful owner.
1 / 48

Day 10 : Excel chapter 5 PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Day 10 : Excel chapter 5

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

Day 10:Excel chapter 5

CS 101


February 10th, 2014

Large datasets

  • So far we have covered relatively small data sets

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

What are we looking at?

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?

Yes - freeze it!

Freeze options

Table 5.1

Freeze panes


Freeze panes – cont’d

Freeze Panes

Now scroll down, and voila!

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.

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

  • A field is an individual piece of data

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

Sample table

ID Field


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

Insert -> Table

Create table dialog box


Adding records (rows)

  • Click a cell below where you want the new record

  • Home Tab -> Cells Group -> Insert

  • Insert Table Rows Above

Before the new record

With the new 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)

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 styles

Sorting data

Table 5.3

Sort menu

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

Sort dialog box

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

Filter dialog box

Clearing filters

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

Example structured reference

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


***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

Between dialog box

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

New Rule Dialog Box

Adding/remove/modify rules

Home Tab -> Conditional Formatting

-> Manage Rules

  • Login