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


  • 51 Views
  • 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

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?


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

G3


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

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


Select the data

Insert -> Table


Create table dialog box


Voila!


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

=SUBTOTAL(function_num,ref1,…)

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


Function_num

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