slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
EXCEL 2007 PowerPoint Presentation
Download Presentation
EXCEL 2007

Loading in 2 Seconds...

play fullscreen
1 / 149

EXCEL 2007 - PowerPoint PPT Presentation


  • 116 Views
  • Uploaded on

EXCEL 2007. INTERMEDIATE. Nolan Tomboulian. Tomboulian@Yahoo.com. May 24, 2012. Intermediate Course Objectives.

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

PowerPoint Slideshow about 'EXCEL 2007' - oakes


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
slide1

EXCEL 2007

INTERMEDIATE

Nolan Tomboulian

Tomboulian@Yahoo.com

May 24, 2012

slide2

Intermediate Course Objectives

  • This class is for students who have some experience using and creating Excel workbooks, understand cell and worksheet formatting, worksheet navigation, basic mathematical operands (+, -, *, /, ^) , relative and absolute cells and Functions.
  • Quick Skill Review of:
    • Ribbon / Tabs / Groups / Commands / Quick Access / Customization
    • Workbook navigation and keyboard commands
    • Copy / Paste / Paste Special Options
    • AutoFill Options
    • Cell Formatting and selecting ranges
    • Formulas: Relative and Absolute cell reference
  • Conditional Formatting
  • Data Validation
  • Using DATE and TIME Functions
  • Logical functions (IF, AND, OR, NOT)
  • Lookup Tables and Functions
  • Data & Tables: Filtering / Sorting / and custom sort lists
  • Sub-Totals and groupings
  • Intermediate formulas and functions (Round, INT, MODE, MEDIAN, RANK)
  • Conditional Data Summaries (COUNTIF, SUMIF, AVERAGEIF, COUNTA)
  • Intermediate charts and graphics
  • Introduction and creation of Pivot Tables
housekeeping

RULES

INTRODUCTIONS

LOGISTICS

AGENDA

FILE INFORMATION

CONTACT INFORMATION

Housekeeping
logistics
Logistics
  • Parking
  • Bathrooms
  • Student Facilities
  • Smoking
  • Fire Alarm / Code Red
  • Lunch & Breaks
this class will not be
This class will not be…

Not too many people know that the word “LECTURE”is actually an acronym. It stands for

Lengthy

Endless

Continuous

Torture with

Unending

Repetition of

Explanations.

A

LECTURE

Class participation is welcomed and beneficial to you (and others)

code of conduct
Code Of Conduct
  • Respect each other (Talking)
  • Food and Drink
  • Participate
  • Patience (with me and yourself)
  • Ask questions
  • Have FUN
  • Have your own projects or ideas?
  • Turn in the Evaluation Survey!
about me
About Me
  • New Mexico State – Business Systems
  • Wake Forest – MBA
  • Carteret Community College MIS Director and Instructor
  • First Union Training and Technology, Legacy reporting, System interfaces
  • Edge Institute Team Leader for China exchange / teaching programs.
about you
About You
  • Introductions
    • Your name
    • What department/area do you work in?
    • What is your job? (Give a short description)
    • What is your skill level with Excel?
    • What do you want to take away from this class?
class flow
Class Flow
  • General Flow
    • Overview of Topic
    • Step-by-Step Exercises
    • Independent Practice
  • Questions
  • Collaboration
technical issues
Technical Issues
  • The Exercise Data Files could be:
  • 1) On your Desktop
  • 2) On the T: Drive
  • 3) On the CD
        • @FRC
          • _Excel_Workbooks
  • Most of the files should be in a READ ONLY mode to keep you from changing them.
  • Create a File Folder on the T: drive, Desktop or flash drive where you plan to save your work.
  • Do a SAVE AS to add files to YOUR File Folder.
  • The SAVE AS does not remove the Read Only status.
    • Press the Office Button and then Prepare – Mark as Final
nolan tomboulian tomboulian@yahoo com 252 675 0176
Nolan Tomboulian

Tomboulian@yahoo.com

252-675-0176

END OF SECTION

Housekeeping

the ribbon does appear overwhelming
The Ribbon does appear overwhelming!

There are some options you may never use!

There are many tutorials and resources available

How do you eat an elephant?

introducing excel
Introducing Excel
  • Microsoft Office Excel 2007 (or Excel) is a computer program used to enter, analyze, and present quantitativedata
    • Desktop Icon or
    • Start / All Programs / Microsoft Office
      • (Right Click and copy the program to the desktop for quick access)
  • A spreadsheet is usually a collection of text and numbers laid out in a rectangular grid. (Columns and Rows)
    • Often used for budgeting, inventory management, and decision making
    • Think of Excel as a big table or chart to fill in with data.
  • You can also insert other objects such as: pictures, word-art, smart-art, drawings/shapes and other OBJECTS (files).
slide16

Basic Worksheet Review

  • Review of:
    • Ribbon / Tabs / Groups / Commands
    • Quick Access Toolbar / Customization
    • Workbook navigation
    • Selecting Cell Ranges
    • Keyboard commands (Alt or <F10>)
    • Copy / Paste / Paste Special Options
    • AutoFill Options
    • Cell Formatting Options
    • Formulas – Relative and Absolute
conditional formatting
Conditional Formatting

Create special color coded and icon “views” of data based on various criteria and “Rules”

conditional formatting1
Conditional Formatting
  • Why use conditional formatting?
    • Emphasize data
    • Call attention to errors
    • Easy data correlation
  • Can use built-in conditional formatting rules, or create your own
  • Can “Filter” data based on Cell Format color
  • Built in rules can usually be modified:
    • (ie: Top 10 can be any Number: Top 2, Lowest 3, Top 15%)
  • Can reference a cell, use a “Constant” value, or even a formula: $A$17 70 =$A5>$B5 .
slide20

Conditional Formatting: Data Bars and Icons

You can “hide” or “display” the Data Value with the graphic..

Be careful with the use of colors for color blindness and printing

While there may be 3 to 5 Icons, you can define the rules

Icon Sets

Data Bars & Icons

custom conditional formats
Custom Conditional Formats
  • You can have multiple conditions that apply to the same range of data.
  • Each time you create a custom conditional format, you are defining a conditional formatting Rule.
  • A Rulespecifies the type of condition (such as formatting cells greater than a specified value) and the type of formatting to apply when that condition occurs.
  • When you have multiple rules, you should test your conditions to make sure the logic is applied in the correct or desired sequence.
  • Be careful with colors, because some people don’t see them! It also takes more ink!
exercise
Exercise
  • Highlight “Duplicate” records
  • Create/Edit Conditional Formatting Rules
  • Highlight an entire ROW of data based on a Formula
slide23

Average Analysis

using Conditional Formatting #1

slide24

Using Conditional Formatting – Example

This example is a more complex use of the Conditional Formatting Option to highlight an entire ROW of data based on a condition.

The worksheet is: Student_Data .

Highlight the Data Range (A2:E20)

[Home]

{Conditional Formatting}

Manage Rules

New Rule

Use a Formula to determine which cells to format

Enter the Formula:

=$ColumnRow =value

String data needs to be in “Quotes”

Use conditions: =, >, <, >=, <=, <>

Example:

= $C2=“W”

=$D2>25

(You need to use the ABSOLUTE Column Cell Reference

Select the FORMAT for the Rule.

Verify the APPLIES TO: range

$A$2:$E$20

http://www.free-training-tutorial.com/animations/conditional-formatting-row.html

conditional formatting3
Conditional Formatting

Use the

{Find and Select } option on the [HOME} tab to identify cells that may contain conditional formatting

nolan tomboulian tomboulian@yahoo com 252 675 01761
Nolan Tomboulian

Tomboulian@yahoo.com

252-675-0176

END OF SECTION

Conditional Formatting

data validation

G.

I.

G.

O.

Data Validation

Garbage In – Garbage Out

Checking values at the time of Data Entry

Or

Apply validation rules before processing a workbook you have been given from someone else!

slide28

When designing a workbook, a good design should consider who is entering the data and how important it is that the data being entered conforms to certain established rules.

Data Validation

What is the impact of invalid data?

For Example:

  • SEX might be defined to only allow for “M” or “F” (it is case specific!)
  • RACE might be limited to “B”, “W”, “H”, “A”, “O”
  • Zip-Code might be defined to be a 5 digit number (US standard)
  • Department might be limited by a select LIST of values
      • (Accounting, HR, Production, Audit)
  • Numeric Data might be defined to only be “Whole Numbers” or to be within a certain range or be bounded by limits
  • State or Name may be limited to a certain number of characters
  • Check input for a valid date or date range value
data validation1
DATA VALIDATION

Data Validation is used to “trap” errors at the time of Data Entry.

The IF and other Logic Functions can also be used to find “errors”

Select the Range of data for the Data Validation rule

RIBBON [Data] tab

Data Validation

There are three tabs

1) Data Validation for new rules

2) Circle Errors to identify errors that were entered BEFORE the validation rule was applied

3) Clear Circles to remove the indication of data validation exceptions.

Conditional Formatting could also be applied to provide a visual for incorrect data

data validation2
Data Validation
  • There are 3 tabs to a Data Validation Dialog:
  • {Settings} {Input Message} {Error Alert}

1) Allow

a) What kind of data do you want to check?

b) What do you want to do with “Blank” data?

c) Do you want the data entry to be via the keyboard or from a “Selection Box”

2) Input Message

I suggest leaving this blank since it will “repeat” for every cell. You could define two rules, 1 for the 1st cell with a PROMPT and then another rule for the additional cells.

3) Error Processing

STOP

Warning

Informational

nolan tomboulian tomboulian@yahoo com 252 675 01762
Nolan Tomboulian

Tomboulian@yahoo.com

252-675-0176

END OF SECTION

Data Validation

G.I.G.O.

objectives
Objectives
  • Using IF to evaluate a single condition
  • Using the AND function for multiple conditions
  • Using the OR function for multiple conditions
  • Using the NOT function to take the inverse result of an AND or OR function
  • Nested IF Functions to be used to calculate 3 or more different outcomes
    • Consider using a Lookup Table if the number of conditions to be tested is large.
logical functions and or not
Logical Functions (And / Or / Not)
  • These functions are not computational:
    • They return a “True” or “False” value based on the conditional test.
  • They are used to test for conditions to be used for further analysis.
  • When combined with other functions, they can be used to perform actions on data, based on returned value of the logical function.
  • They can be used in a Cell or nested within an =IF function.
working with the logical if function
Working with the Logical =IF Function
  • The IF function is a logical function that returns a TRUE value if the logical condition is true and a FALSE value if the logical conditions are false.
  • The TRUE and FALSE values can be flags that can be used in additional formulas, actual values or operations, or even other formulas
  • IF Function syntax:
    • IF(logical_test, Do_if_true, [Do_if_false])
if function
IF Function

String values must be in Quotes

It IS NOT case sensitive.

slide38

Game Bracket

Copy A2:C4 and paste into A6 to create a 2nd bracket of Games

=IF(B6>B8,A6,A8)

slide39

Game Bracket – Round 2

=IF(B6>B8,A6,A8)

Add values in D3 and D7 and create a formula in E5 to show the winner

=IF(D3>D7,C3,C7)

Can you copy the cells from A2:E8 into A10, and add the logic for ROUND 3?

Now copy the cells for the 8 teams and add the logic for ROUND 4

You now have the template for a 16 team Regional Tournament

slide40

Nested If

What happens now if a TIE score is entered in B2 and B4?

WHY?

=IF(B2=B4,”TIE”,IF(B2>B4,A2,A4))

This example demonstrates a Nested IF.

While in “real life” we assume there is only one “winner” and “Loser” what if we allowed for the possibility of a TIE.

=IF(B2=B4,”TIE”,IF(B2>B4,A2,A4))

working with and function
Working with =AND Function
  • The AND function is a logical function that returns a TRUE value if allof the logical conditions are true and a FALSE value if any of the logical conditions are false.
  • Can test up to 255 logical conditions
  • =AND(cond1, Cond2, Cond3….)
and function
AND Function

In E2: =AND(C2=“FT”,D2>=1)

In F2: =IF(E2,B2*.03,0)

working with logical functions1
Working with Logical Functions
  • A nested IF function is when one IF function is placed inside another IF function to test for multiple outcomes
  • Can allow for three or more outcomes, instead of just two
    • =IF([Pay Grade]=1,2500,IF([Pay Grade]=2,5000, IF([Pay Grade]=3, 7500,"Invalid pay grade")))
working with logical functions or
Working with Logical Functions=OR
  • The OR function is a logical function that returns
    • TRUEvalue if any of the logical conditions are true
    • FALSEvalue if all the logical conditions are false
  • =OR(logical_test_1, logical_test_2,…)
working with logical functions not
Working with Logical Functions=NOT
  • Takes the Opposite of a returned condition
  • =NOT(logical_test)
working with logical functions2
Working with Logical Functions

If Years is Less than or equal to 1 or Salary > 100,000, there is no Bonus

Otherwise, check the Pay Grade code to assign a constant value

  • =IF(OR([Years Service]<=1,[Annual Salary]>100000),0, IF([Pay Grade]=1,$T$1,IF([Pay Grade]=2,$T$2, IF([Pay Grade]=3,$T$3,"Invalid pay grade"))))
checking formulas
Checking Formulas
  • Check your parentheses
  • All functions have an opening and closing parentheses
    • Correct number
    • Correct position
  • Excel uses color coding to help you keep track of items in a formula
  • Don’t use the “=“ inside of Nested functions.
slide49

Example

Electoral College

Every four years, the United States has an election to pick the President. A system is used to assign “VOTES” based on the population of the State. And the number of representatives the state has. It is a “Winner Take All” – Whoever wins a majority of the votes in the state gets ALL the Electoral Votes

This example uses an =IF function to assign the votes to a Republican 1=(RED) or Democrat 2=(BLUE)

Maybe a Data Validation should be assigned to the input Cells for the Data Entry in the E and H columns

There is also a condition in Column J to compare the Predicted Result with the Actual Result..

exercise payroll record
Exercise – Payroll Record
  • Use the IF function to calculate Regular hours worked (<=40)
  • Use the IF statement to calculate if there are any Over-Time Hours
  • Use the If statement to calculate Overtime Rate If there was Overtime Hours.
exercise payroll record part2
Exercise – Payroll Record Part2
  • Use a Nested IF function to calculate Deductions based on a code.
  • The code of 1 = 3%
  • The code of 2 = 6%
  • The Code of 3 = 8%
  • You also need to code INVALID data entry codes.
nolan tomboulian tomboulian@yahoo com 252 675 01763
Nolan Tomboulian

Tomboulian@yahoo.com

252-675-0176

END OF SECTION

Logical

Functions

If / And / OR / Not

using the vlookup hlookup functions
Use a value in a Cell as a Key to index a sorted column in another table and return an associated piece of data to be displayed or used in another calculation.Using the Vlookup & Hlookup Functions
lookup tables and functions1
Lookup Tables and Functions
  • A lookup table is a table that organizes data based on different categories, in order to retrieve a value from the data
  • The category for the lookup table, called compare values or Key is located in the first column (VLookUp) or row (HLookUp) of the table.
  • To retrieve a particular value from the lookup table, a lookup value (the value you are trying to find) needs to match one of the compare values
lookup table
Lookup Table

The Actual values and cell locations used in the example are different than what is shown here.

lookup tables and functions2
Lookup Tables and Functions
  • Two types of lookup functions
  • VLOOKUP
    • VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • HLOOKUP
    • HLOOKUP(lookup_value, table_array, row_index_num, [range lookup]
  • range_lookup: Optional - True or False.
    • If Blank or TRUE, it looks for an Approximate Match
      • KEYS MUST BE IN ASCENDING SEQUENCE
    • If the KEY is not found, it returns the value of the prior key
    • If FALSE, The Key must be an Exact Match and the Keys DO NOT need to be in order.
lookup tables and functions3
Lookup Tables and Functions
  • Lookup functions can be used to find exact matches and approximate matches
  • Exact match looks for an exact match between the lookup value and a corresponding value in the lookup table
  • Approximate match looks for a correlation between the lookup value and a range of values
lookup tables and functions4
Lookup Tables and Functions
  • When using an approximate match lookup (TRUE), the compare value (Key) in a lookup table must be sorted by alphabetical order (if text) or low-to-high order (if numeric)
  • Excel searches the first column (vlookup) or row (Hlookup) of the lookup table (compare values) until it locates the largest value that is still less than the key.
exercise vlookup payroll record part 3
Exercise VLookUpPayroll Record Part-3
  • Use the VLOOKUP function to find an exact match for a code.
  • VLookUp is better than a Nested If when you have many possible values.
  • Management is so happy with the work you have done on the Payroll Record spreadsheet, they want to add a lookup to an Employee Information Data Table.
  • In addition, since the Employee name is used in other processes, they want to use a table that uses the ID to extract the Employee Name, Department , Pay Rate, Deduction code and Health Insurance Amount. The Employee Table will also contain another lookup to a Health Insurance Code Value Table.
nolan tomboulian tomboulian@yahoo com 252 675 01764
Nolan Tomboulian

Tomboulian@yahoo.com

252-675-0176

END OF SECTION

VLOOKUP

using tables and data ranges
Using Tables and Data Ranges

Table Creation

Sorting

Filtering

Subtotals

objectives1
Objectives
  • Create / Define / Name tables
  • Explore the Ribbon Table Tab
  • Add and delete records (Rows) and Fields (Columns)
  • Add Formulas and change column formatting
  • Sort data
    • Single Key Multi Key Custom Sort
  • Filter data
  • Use the Styles options
  • Use the Total Row to summarize a table
  • Use the Outline buttons to show or hide details
  • (Subtotal is a special group function that can be done on a Range of data using the [DATA] tab, BUT CANNOT BE USED ON A TABLE!)
structured range of data
Structured Range of Data
  • One of the more common uses of Excel is to manage data – It is not just a calculation tool!
  • Using Excel, you can:
    • Store and update data
    • Sort data
    • Filter or Search for and retrieve subsets of data
    • Summarize data
    • Create reports and graphs
structured range of data1
Structured Range of Data
  • In Excel, a collection of similar data can be structured in a range of rows and columns
    • Each column in the range represents a field
    • Each row in the range represents a record
  • You Cannot change the Excel Column Headings (A,B, C…, but you can create Headings to be used with your table)
creating fields columns
Creating Fields (columns)
  • Keep in mind these best practices
  • Create fields that require the least maintenance
    • Ex. Hire date vs time with company
  • Store the smallest unit of data possible
    • Ex. Store City, State, and Zip in separate fields
  • Apply a text format to fields with numerical text data (esp. if there are leading zeros)
    • Ex. Zip Code, Social Security Number, Product code
structured references
Structured References
  • Structured References
    • You can reference a specific cell or range in a table with a structured reference
    • Uses the field names, as opposed to the cell reference
    • Similar in functionality to a named cell or range
  • Easy to understand:
    • =SUM(G20:G123)
    • =SUM(Employee[Annual Salary])
structured references1
Structured References
  • To use a structured reference in a formula or function:
    • Type a left bracket - [ to open the field list for the table
    • Double-click the field name
    • Type a right bracket ]
structured references2
Structured References
  • Structured References can also use special qualifiers to refer to special portions of the table, such as the Total Row.
tables
Tables
  • Range of related data, managed separate from other data on a worksheet
  • Easy access to data management and analysis tools
  • Can have multiple tables in a worksheet
table features
Table Features
  • Table Formats and Styles (Depending on selected options, you have different STYLES for Row and Column highlighting)
  • Adding or inserting new rows or columns automatically expands the table range.
  • Easily add a Total Row to calculate summary statistics (Sum, Count, Average, etc)
  • Formulas applied in one cell will automatically be applied to all cells in that field (column) – no need to Copy Down!
  • Formatting can be applied to an entire column
  • Can use the table and field names as a cell reference in a formula. –(It looks complex when you see the formula – but when using – Point-and-Click it is more clear.)
    • For Example to add a formula to find an increase the Appraised value of 10%: =DATA[[#This Row],[Appraised Value]]*0.1
creating an excel table
Creating an Excel Table
  • Highlight / Select the range of data to be included in the table.
    • Tables don’t need “column Headings” but the features are more useful when they are defined.
    • Ctrl-A is a shortcut to select a range of data
  • Go to the Insert tab – Tables group
  • Click the Tablebutton
    • Ctrl-T is a shortcut to create a new table
  • Verify the RANGE is correct and Check if the table has column headings.

Excel will:

Create a context Ribbon [Table Design] Tab

Add Filter dialog commands to fields (Column Headings)

Enter a Table Name to reference the table (Optional)

slide76

Using a Structured Table

If you <click> outside the table, the <Table Tools> goes way.

Click ANYWHERE on the Table to reactive the [Table Tools]

and then <Click> the {Design Tab}

You may NAME the table so you can use the Table Name as a reference in formulas rather than Absolute Cell Addresses:

Inserting a column or row into the table will EXTEND the Table Name Range

Adding an Adjacent Column or Row will also EXTEND the Table Name Range.

(Use Resize if adding nonadjacent columns)

slide77

The Ribbon <Table Design> Tab GROUPS

Define or Rename a table range

Extend the size of a Table if Nonadjacent columns or rows were added

Pivot Tables are another Topic

Be Careful with REMOVE Duplicates – It actually deletes rows from the Table.

Convert to Range: Un-define the Table: Cell Styles will still be applied!- You must <CLEAR> Formatting. May also cause problems with Defined Formulas

slide78

The Ribbon <Table Design> Tab GROUPS

External Table Data is an Advanced function

Banded Rows and Columns allow for different Color Styles to be applied

Header Row – Turns on and off the Titles

First and Last Column allow the “MEDIUM” style formatting

Total Row is a fast way to get some Summary Statistics about the table.

using the total row
Using the Total Row
  • A Total row, which you can display at the end of the table, is used to calculate summary statistics for the columns in an Excel table
  • Total rows can be set to display different summary statistics for each field
  • Total rows usually automatically recalculate as records are added and deleted
using the total row1
Using the Total Row
  • Go to the Table Tools Design tab - Table Style Options group
  • Click the Total Rowcheckbox to insert a check mark
  • Scroll to the end of the table
  • Set the desired summary function
navigation in a worksheet review
Navigation in a Worksheet - REVIEW
  • <Tab> Move to the right
  • <Shift> <Tab> Move to the left
  • <Enter> Move <DOWN> a row
  • <Home> Move to the start of the Row
  • <Ctrl> <Home> Move to the top of the table
  • <End> <Down> Move to the last Row
  • <End> <Up> Move to the top Row
  • <Ctrl> <Left> Move to first column
  • <Ctrl> <Right> Move to last column
  • <Up>, <Down>, <Left>, <Right>
finding and editing records
Finding and Editing Records
  • Go to the [Home tab] - Editing group, click the Find & Select button, and then click Find
  • Type your search criteria in the Findwhatbox, and then click the Find & Selectbutton
  • You could also the Table <FILTER> to select certain record items.
exercise table creation
Exercise – Table Creation
  • Create a table
  • Renaming a table
  • Formatting a table
    • STYLES
    • Column Formats
  • Adding a Column
  • Adding and Deleting records
  • Adding the TOTAL ROW
slide84

SORTING

AND

FILTERING

sorting and filtering
Sorting and Filtering
  • Allows data to be viewed differently from how it was entered
  • Key functionality of Excel when used to manage data
  • Sort rearranges the data in a table or range, based on the sort criteria
    • (Any SUBTOTALS applied will be reset with a SORT
  • Filters present the data that meets the filter criteria.
  • Filters can be applied to multiple columns
  • When using Filters, care must be taken that you are reporting the data you really want to show!
sorting data
Sorting Data
  • You can rearrange, or sort, the records in a table or range based on the data in one or more fields (Old Limit was 3 levels of sorting)
  • The fields you use to order the data are called sort fields
  • You can sort data in ascending (A-Z) or descending (Z-A) order, unless using a custom list
sorting single columns
Sorting Single Columns
  • Click any cell in the column you want to sort by
    • (You used to have to highlight the range of data you wanted to sort – otherwise it would only sort that column!)
  • Go to the Data tab Sort& Filtergroup
  • Click the desired sorting button
      • (Sort A To Z or Sort Z To A)
sorting multiple columns
Sorting Multiple Columns
  • Click any cell in a table or range
  • Go to the Data tab - Sort & Filter group
  • Click the Multi Sortbutton to open the Sort dialog box
sorting multiple columns1
Sorting Multiple Columns
  • Set the primary sort criteria
    • Click the Sortby arrow and select the column heading that you want to specify as the primary sort field
    • Click the SortOnarrow to select the type of data
    • Click the Orderarrow to select the sort order
  • To sort by additional columns
  • Click the AddLevel button (additional levels are indicated by the words “Then By”)
  • Click the Sortby arrow and select the column heading that you want to specify as the primary sort field
  • Click the SortOnarrow to select the type of data
  • Click the Orderarrow to select the sort order
sorting using a custom list
Sorting Using a Custom List
  • A custom list allows you to indicate the sequence in which you want data ordered
  • Used when you want to sort text data outside of the normal Ascending and Descending methods
  • Can use predefined custom lists or define your own.
  • IE:
    • Months: Jan, Feb, Mar, April, May, June….
    • Days: Sun, Mon, Tues, Wed, …
    • Region: North, West, South, East, South West
    • Condition: Poor, Fair, Good, Excellent
sorting using a custom list1
Sorting Using a Custom List
  • Go to the Data tab - Sort & Filter group
  • Click the Sort button
  • Click the Order arrow, and then click CustomList
  • In the List entries box, type each entry for the custom list, pressing the Enter key after each entry
  • Click the Add button
  • Click the OK button
exercise 2 sorting
Exercise # 2 – Sorting
  • Sorting by One Column
  • Sorting Data using Multiple Columns
  • Sorting using a custom list
slide93

FILTERING

If the data range is not defined as a table and the column headings do not have the FILTER option, the FILTER Option can be turned on from the HOME tab using the dialog box

This option is also used to CLEAR all applied filters

filtering
Filtering
  • Excel automatically creates filters when a TABLE is created
  • Clicking the filter arrow in a column opens the Filter menu for that field
  • Data can be filtered by:
    • Cell or font colors
    • Apply Text or Numeric Filters and conditional logic
    • Select specific values
filtering using multiple columns
Filtering Using Multiple Columns
  • If you need to further restrict the records that appear in a filtered table, you can filter by additional columns
  • Each additional filter is applied to the currently filtered data and further reduces the records that are displayed
  • The Column Heading drop down option indicates if a column has been used to Sort or Filter the data.
creating criteria filters
Creating Criteria Filters
  • Criteria Filters enable you to specify various more complex conditions in addition to those that are based on an “equals” criterion
  • Different data types have different criteria that can be used in a filter

Criteria Filters and Selection Filters are mutually exclusive.

You cannot mix them!

creating criteria filters2
Creating Criteria Filters
  • Click on the filter arrow of the field (column) you want to filter by
  • Select the filter type (usually right above the unique values list). The data type of the field will determine the type of filter available
  • Select the filter operator
  • If necessary, provide criteria values
exercise filters
Exercise Filters
  • Filtering by One Column
  • Filtering Data using Multiple Columns
  • Clearing Filters
  • Selecting Multiple Filter Items in One Column
  • Create a NUMBER Criteria Filter
  • Create a TEXT Criteria Filter
calculating subtotals
Calculating Subtotals
  • Subtotals are used to summarize a range of data in Excel
  • Subtotals cannot be applied to an Excel table – if the data being analyzed is in a Table, it must be removed. (Design – Convert to Range)
  • The data must be sorted so it is grouped as desired BEFORE applying subtotals to do “Control Breaks” in the correct order:
    • IE:
      • City / Sex / Race Location / Criteria / Artist
      • Race / Grade / Sex Artist / Location / Criteria
      • Sex / Grade / Age
calculating subtotals1
Calculating Subtotals
  • Sort the data by the column for which you want a subtotal FIRST
  • If the data is in an Excel table, go to the Table Tools Design tab - Tools group, and click the ConverttoRangebutton
  • Go to the Data tab - Outline group, and click the Subtotalbutton
  • Click the At each change in arrow, and then click the column that contains the group you want to subtotal
calculating subtotals2
Calculating Subtotals
  • Click the Use function arrow, and then click the summary function you want to use
  • In the Add subtotal to box, click the check box for each column that contains the values you want to summarize
  • To calculate another category of subtotals, click the Replace current subtotals check box to remove the check mark, and then repeat the previous three steps
  • Click the OK button
using the subtotal outline view
Using the Subtotal Outline View
  • The Subtotal feature also applies an outline to the data so you can control the level of detail that is displayed
  • The three Outline buttons at the top of the outline area (left side of the worksheet area) allow you to show or hide different levels of detail in the worksheet
    • Level 3 – displays the most detail
    • Level 2 – displays the subtotal rows and the grand total
    • Level 1 – displays only the grand total
using the subtotal for student grades
Using the Subtotal for Student Grades

This is a subset select of Student Data you have been asked to summarize.

You have been asked to Count and average subject scores and Total Average by Grade, Sex and Race

and now for a break
And now for a break…
  • Can you name these famous Excel cells?
    • The steak sauce cell
    • The dog cell
    • The fighter jet cell
    • The Irish rock group cell
    • The explosive cell
    • The vegetable juice cell
    • The gun Cell
and now for a break1
And now for a break…
  • The steak sauce cell A1
  • The dog cell K9
  • The fighter jet cell F16
  • The Irish rock group cell U2
  • The explosive cell C4
  • The vegetable juice cell V8
  • The gun juice cell M16
nolan tomboulian tomboulian@yahoo com 252 675 01765
Nolan Tomboulian

Tomboulian@yahoo.com

252-675-0176

END OF SECTION

TABLES

Sorting and Filtering

conditional summary functions

CountIf

SumIf

AverageIf

Conditional Summary Functions

Another way to calculate summary information rather than using Subtotals or creating multiple =Count, =Sum or =Average functions on various ranges of data.

conditional summary functions1

CountIf

SumIf

AverageIf

Conditional Summary Functions

Improvement over the old functions : Dcount, DSum and DAverage Functions)

summarizing data conditionally
Summarizing Data Conditionally
  • Tests for a condition (or multiple conditions) before applying a summarization function
  • Calculates values based on the condition
  • Useful for summarizing a subset of data determined by another piece of data
    • Ex.
      • Calculate average salaries for employees in specific cities
      • Calculate Grades by Sex and Race
      • Count the data based on employees in a Department
summarizing data conditionally1
Summarizing Data Conditionally
  • COUNTIF which is sometimes referred to as a conditional count
    • =COUNTIF(range, criteria)

SUMIF which is also called a conditional sum

=SUMIF(range,criteria[,sum_range])

AVERAGEIFto calculate the average of values in a range that meet criteria you specify

=AverageIF(range, criteria[,average_range])

summarizing data conditionally2
Summarizing Data Conditionally

RANGE: Is the rows that are being compared to the CRITERIA

CRITERIA: This is a single Value or Cell that is being compared to the values in the RANGE

SUM_Range and AVERAGE_Range are the columns of data values to summarize. If left out, it uses the column defined in the RANGE.

summarizing data conditionally3
Summarizing Data Conditionally
  • COUNTIF, SUMIF, and AVERAGEIF only test for one condition
  • If multiple conditions are being tested to calculate a value, use:
  • COUNTIFs
  • SUMIFs
  • AVERAGEIFs
slide118

Conditional IF – SUMMARY FUNCTIONS

Payroll Record 5

Management continues to be impressed by your skills with Excel and they now want a summary report by Department of the data in the Payroll Record Worksheet

The CountIF formula has been done for you. You need to add the formulas for the other totals

Be careful with Relative and Absolute Cell ranges when creating/copying your formulas.

slide119

CONDTIONAL SUMMARY

STUDENTGRADES3_Conditional

The Student Grades3 workbook has details for students by :

Grade, Sex and Race

Conditional IF formulas can be defined to look at more than one variable to decide how to add, count or average the data.

Some Formulas have been done for you to Count and find the Average Math and Average Average Score.

You can add the formulas for the Average Science and Average English Scores.

The [Formulas] {Define Names} was used to define the columns of data that would be used for the selection criteria. This eliminates the need to use the Absolute Cell Reference to Define the Range of data and the “Name” of the range is easier to understand than cell addresses.

Use the [Formulas] {Name Manager} to see the defined named ranges

summarizing data conditionally4
Summarizing Data Conditionally
  • The COUNTIFS function counts the number of cells within a range that meet multiple criteria
    • COUNTIFS(criteria_range1,criteria1[,criteria_range2,criteria2...])
  • The SUMIFS function adds values in a range that meet multiple criteria
    • SUMIFS(sum_range,criteria_range1,criteria1[,criteria_range2, criteria2...])
  • The AVERAGEIFS function calculates the average of values within a range of cells that meet multiple conditions
    • AVERAGEIFS(average_range,criteria_range1,criteria1[,criteria_range2, criteria2...])

Notice the AVERAGE_Range and SUM_Range are at the beginning of the formula rather than at the end

nolan tomboulian tomboulian@yahoo com 252 675 01766
Nolan Tomboulian

Tomboulian@yahoo.com

252-675-0176

END OF SECTION

Conditional Summary

CountIF

AverageIF

SUMIF

pivot tables

PIVOT TABLES are another way to calculate summary information rather than using Subtotals or creating multiple =Count, =Sum or =Average functions on various ranges of data or using the CountIF, AverageIF or SumIF functions.

PIVOT TABLES

Pivot Tables get their name because you can define reports with fields of data and assign them to summarize as Rows or Columns.

speaking of having fun
Speaking of having fun…

If Excel were a car...

  • It would crash two or three times per day for no apparent reason. The driver is often hurt, but the car itself receives no permanent damage. You'd just accept this fact, restart the car, and begin your trip again.
  • You would be forced to buy a new model every 18 months, and your old model would have no resale value. Each new model would be bigger than the previous one, require more gas, and would operate differently. Furthermore, parts from the old car would not be interchangeable with the new car.
speaking of having fun1
Speaking of having fun…

If Excel were a car...

  • The oil, engine, gas and alternator warning lights would be replaced by a single warning light: "This car has performed an illegal operation.“
  • Before engaging, the airbag system would display a message, "Are you sure?"
  • Every time you looked under the hood, an obnoxious cartoon character would appear and ask if you need help. No matter how many times you refused help, it would keep appearing.
analyzing data with pivottables
Analyzing Data with PivotTables
  • A PivotTable is an interactive table that enables you to group and summarize either a range of data or an Excel table into a concise, tabular format for easier reporting and analysis
analyzing data with pivottables1
Analyzing Data with PivotTables
  • Value fields – fields that contain the data you want to summarize
  • Category fields – group values in a PivotTable
    • Category fields can be row labels, column headers, and report filters
  • Start by sketching out the PivotTable
    • How will it be organized? Columns, rows?
    • What values need to be summarized?
creating a pivottable
Creating a PivotTable
  • Click in the Excel table or select the range of data for the PivotTable
  • Go to the Insert tab - Tables group, and click the PivotTable button
  • Click the Selecta table or range option button and verify the reference in the Table/Range box
  • Click the NewWorksheet option button or click the Existingworksheet option button and specify a cell
creating a pivottable1
Creating a PivotTable
  • Click the OKbutton
  • Click the check boxes for the fields you want to add to the PivotTable (or drag fields to the appropriate box in the layout section)
exercise four
Exercise Four
  • Create a PivotTable
  • Add fields to a PivotTable
  • Apply PivotTable styles
  • Format PivotTable Value Fields
  • Rearrange a PivotTable
filtering a pivottable
Filtering a PivotTable
  • Filtering a PivotTable enables the user to view a subset of the total data in the PivotTable
  • Two kinds of filters can be used in a PivotTable
    • Report filter – can use a field not in the PivotTable
    • Field filter – filters existing fields in the PivotTable
adding a report filter
Adding a Report Filter
  • Drag the desired filter field to the Report Filter area in the PivotTable Field List dialog box
  • Select the filter values in the ReportFilterfielditem drop down (above the PivotTable itself)
filtering pivottable fields
Filtering PivotTable Fields
  • Filtering a field lets you focus on a subset of items in that particular field
  • Click the field arrow button in the PivotTable that contains the data you want to filter
  • Uncheck the check box for each item you want to hide
refreshing a pivottable
Refreshing a PivotTable
  • You cannot change the data directly in the PivotTable. Instead, you must edit the data it is based on, and then refresh, or update, the PivotTable to reflect the updated data
  • Click
    • PivotTable Tools Options tab
      • Data group,
        • click the Refresh button
grouping pivottable items
Grouping PivotTable Items
  • When a field contains numbers, dates, or times, you can combine items by utilizing groups. (IE: group dates by: Month or Year)
  • Excel creates groups automatically, based on the data.
  • You can create your own groupings by selecting the row labels you want to group together, and then clicking GroupSelection in the PivotTableOptions tab
creating a pivotchart
Creating a PivotChart
  • A PivotChart is a graphical representation of the data in a PivotTable
  • A PivotChart allows you to interactively add, remove, filter, and refresh data fields in the PivotChart similar to working with a PivotTable
  • Click any cell in the PivotTable
  • Go to the PivotTable Tools Options tab
    • Tools group
      • click the PivotChart button
exercise1
Exercise
  • Adding a report filter
  • Filtering PivotTable fields
  • Sorting PivotTable fields
  • Adding a second value field to a PivotTable
  • Grouping PivotTable items
  • Creating a PivotChart
nolan tomboulian tomboulian@yahoo com 252 675 01767
Nolan Tomboulian

Tomboulian@yahoo.com

252-675-0176

END OF SECTION

Introduction to

PIVOT

TABLES

online resources
Online Resources
  • Microsoft Office: office.microsoft.com
  • Microsoft Training: microsoft.com/learning
  • CustomGuide: www.customguide.com
  • Teach Excel: http://www.teachexcel.com/
  • Mr. Excel: www.mrexcel.com
  • The Spreadsheet Page: spreadsheetpage.com
  • ExcelTip.com:www.exceltip.com
  • Excel for Dummies: www.dummies.com
templates training and examples
Templates, Training and Examples

2003 to 2007 Commands (Interactive)

http://office.microsoft.com/en-us/excel-help/interactive-excel-2003-to-excel-2007-command-reference-guide-HA010149151.aspx

Templates

http://office.microsoft.com/en-us/excel/

2003 to 2007 Menu to Ribbon

http://office.microsoft.com/asstvid.aspx?&type=flash&assetid=XT010149329&vwidth=1044&vheight=788

slide142

Microsoft Training Tutorials

http://office.microsoft.com/en-us/training/excel-2007-training-courses-HA010218987.aspx

2003 vs 2007

http://ezinearticles.com/?Excel-2003-Vs-Excel-2007---Whats-New-in-Excel-2007&id=1695056

Tutorials

http://spreadsheets.about.com/od/excel101/a/tutorial_hub.htm

http://www.gcflearnfree.org/excel2007

slide143

Excel Functions

http://www.excelfunctions.net/

Cheat Sheet

http://www.computerworld.com/s/article/9028218/Excel_2007_Cheat_Sheet_Quick_reference_charts

PDF Migration Information

http://www.liv.ac.uk/csd/acuk_html/473.dir/473.pdf

slide144

Microsoft Excel Interactive Menu Commands

http://office.microsoft.com/en-us/excel-help/interactive-excel-2003-to-excel-2007-command-reference-guide-HA010149151.aspx

2003 vs 2007 Key Differences

http://www.add-ins.com/Excel%202003%20versus%202007.htm

Keyboard Shortcuts

http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx?pid=CH100947761033

slide145

Excel Functions

http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP010079186.aspx

Odd Excel Commands

http://spreadsheetpage.com/index.php/oddity/bahttext/

Tricks and Tips

http://www.teachexcel.com/excel-help/excel-how-to.php?i=70019

slide146

Tutorial of Functions

http://www.teacherclick.com/excel2003/a_4_4_2.htm

Another page of “Hints”

http://www.bettersolutions.com/excel/EDH113/QE813022021.htm

Code to Covert a Number to Words

http://support.microsoft.com/default.aspx?scid=kb;en-us;213360

http://www.xldynamic.com/source/xld.xlFAQ0004.html

http://www.pcreview.co.uk/forums/convert-numeric-text-t1770367.html

http://hackertrip.wordpress.com/2011/08/23/how-to-convert-baht-text-thailand-to-english-in-excel/

when you re really desperate
When you’re really desperate….

Feel free to contact me!

E-mail: Tomboulian@Yahoo.com

Phone:

252-675-0176

now that you are finished
Now that you are finished…

I can let you in on some secret Excel vocabulary:

  • Auto-Save
    • What your spreadsheet does immediately after you make a huge mistake and just before you press Undo. If you have a sound card, you may be able to hear your program laughing at you.
  • Cursor
    • What one becomes who sits in front of the same spreadsheet that won't balance for more than 2 hours.
  • Import
    • Ability to get huge amounts of data from an outside source allowing you to create hundreds and thousands of rows of seemingly meaningful data.
now that you are finished1
Now that you are finished…
  • Invalid File
    • Error you get once you have finally perfected your spreadsheet and attempt to load it the next day to print. Only occurs if you don't do regular backups.
  • Print Preview
    • Lets you know if your printout will be Portrait or Landscape. Information on the screen bears no resemblance to what actually prints out.
  • Table
    • Furniture used to spread out all of your papers and arrange them so they look like what is on the screen. See Cut, Copy and Paste.