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

Day 10 : Excel chapter 5 PowerPoint PPT Presentation


  • 45 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 [email protected] 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

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?


What are we looking at

What are we looking at?


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?


Yes freeze it

Yes - freeze it!


Freeze options

Freeze options

Table 5.1


Freeze panes

Freeze panes

G3


Freeze panes cont d

Freeze panes – cont’d

Freeze Panes


Now scroll down and voila

Now scroll down, and voila!


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

Record


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


Create table dialog box

Create table dialog box


Voila

Voila!


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


Before the new record

Before the new record


With the new record

With the new record


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


Table styles

Table styles


Sorting data

Sorting data

Table 5.3


Sort menu

Sort menu


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


Sort dialog box

Sort dialog box


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


Filter dialog box

Filter dialog box


Clearing filters

Clearing filters


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


Example structured reference

Example structured reference


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

=SUBTOTAL(function_num,ref1,…)

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


Function num

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


Between dialog box

Between dialog box


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


New rule dialog box

New Rule Dialog Box


Adding remove modify rules

Adding/remove/modify rules

Home Tab -> Conditional Formatting

-> Manage Rules


  • Login