Excel workshop
This presentation is the property of its rightful owner.
Sponsored Links
1 / 44

Excel Workshop PowerPoint PPT Presentation


  • 87 Views
  • Uploaded on
  • Presentation posted in: General

Excel Workshop. Presented by: California Actuarial League. Basics, Formats, and Formulas. Eric Chao. Interface. Worksheet and Workbook Rows and Columns Excel 2003: Rows from 1 to 65536, Columns from A to IV Resizing – Auto, Manual, Exact Cells Edit cells – F2 or double-click

Download Presentation

Excel Workshop

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


Excel workshop

Excel Workshop

Presented by:

California Actuarial League


Basics formats and formulas

Basics, Formats, and Formulas

Eric Chao


Interface

Interface

  • Worksheet and Workbook

  • Rows and Columns

    • Excel 2003: Rows from 1 to 65536, Columns from A to IV

    • Resizing – Auto, Manual, Exact

  • Cells

    • Edit cells – F2 or double-click

    • Cancel changes – ESC

  • Formula Bar

  • Multiple ways to do most things

  • Help! – F1


Formats

Formats

  • Format Cells – Ctrl+1 or right-click

    • “Number” “Font,” “Border,” “Fill” Categories

  • Paste Special (Alt + E + S)

  • Conditional Formatting

    • Allows cells to be formatted a certain way based on criteria

    • Home  Styles  Conditional Formatting


Common formats

Common Formats

  • General Format (Ctrl + Shift + ~)

    • 182893

  • Accounting Number Format

    • $ 182,893.00

  • Currency Format (Ctrl + Shift + $)

    • $182,893.00

  • Comma Format (Ctrl + Shift + !)

    • 182,893.00

  • Percent Format (Ctrl + Shift + %)

    • 18289300%

  • Date Format (Ctrl + Shift + #)

    • 27-Sep-00

  • Text Format


Tables

Tables

  • Example – NBA Player Stats

  • Auto-Filter

    • Data  Filter  Auto-Filter

    • The Filter will hide the entries you do not want to see

  • Sort

    • Can sort alphabetically or reverse alphabetically


Two places at once

Two Places at Once

  • Freeze Panes

    • 2007: View  Freeze Panes

    • 2003: Window  Freeze Panes

    • Allows you to keep header in view

  • Splitting the Worksheet

    • Allows you to look at two far away parts of the worksheet at the same time

  • New Windows

    • 2007: View  New Window

    • 2003: Window  New Window

    • Can look at two different Worksheets in the same Workbook at the same time


Formulas

Formulas

  • Always start with an “=“ sign

  • Operators

    • Numerical : +, -, /, *

    • Text: & (concatenate)

  • Quotes

    • Using “quotes” will designate something as text, otherwise Excel will think you are referring to a cell


Common formulas

Common Formulas

  • SUM – sums the referenced cells or values

  • AVERAGE – averages the referenced cells or values

  • COUNT – counts the number of cells or values

  • MAX – returns the maximum value

  • MIN – returns the minimum value

  • SUMIF

    • Sums cells based on criteria

    • SUMIF(range, criteria, [sum_range])

    • Example: SUMIF(B3:D3,"=500",B5:D5)


Using formulas

Using Formulas

  • Use Help files for more detail on a function’s syntax, i.e. its layout and order of the function and its arguments

  • Relative references vs Absolute references

  • Autofill


Excel workshop

IF

  • IF(logical_test, [value_if_true], [value_if_false]

  • Examples

    • IF(B8>1, "flag", "")

    • If the value in cell B8 is greater than 1, the if statement returns the text “flag”; if not, then it returns nothing.


And or

AND, OR

  • AND(logical1, logical2, logical3, …)

    • All logical tests must be true for the entire function to be true

    • Example: IF(AND(B4=C4,C4=D4),"","check dates")

  • OR(logical1, logical2, logical3, …)

    • At least one logical test must be true for the entire function to be true


Excel workshop

IS…

  • ISBLANK

    • Checks if a referenced cell is empty

  • ISERR

    • Checks if a value is an error, excluding #N/A

  • ISERROR

    • Checks if a value is an error, including #N/A

  • ISNA

    • Checks if a value is #N/A

  • ISNUMBER

    • Checks if a value is a number

  • ISTEXT

    • Checks if a value is text


Sumproduct

SUMPRODUCT

  • SUMPRODUCT(array1, array2, array3, …) or SUMPRODUCT((array1)*(array2)*…)

  • Multiplies corresponding values in arrays and sums the results

  • Useful for obtaining sums based on a criteria (when used with logical statements)

  • Example:

    • SUMPRODUCT(B5:D5,B8:D8)/SUM(B5:D5)

      • Finds the weighted average of the values in B8:D8

    • SUMPRODUCT((B3:D3=500)*(B5:D5))

      • The first array is a logical, so the returned value is the value of B5:D5 for which its corresponding cell in B3:D3 is equal to 500


Vlookup function

Vlookup Function

  • Allows you to retrieve data that exist in a list in another sheet or workbook.

  • Requirements for the list:

    • First column of the list must contain the value that matches a value in your worksheet

    • List should be sorted on the first column in Ascending order


Vlookup lookup value table array col index num range lookup

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

lookup_value: The value to search in the first column of the table array 

table_array: Cell Range or Name of cell range that makes up the cell range of your list

col_index: Number representing the column in that list that contains the value you want to achieve.

range_lookup: Logical value of True or False. True (or left empty) - closest lowest value

False - Exact Match


Gpa example steps

GPA Example – Steps

  • Create a Full Name column

    • = FirstNameCell&“ ”&LastNameCell

  • Create a Grade Point column

    • =VLOOKUP(Grade, GradePointsTable, 2, False)

  • Do some Excel magic

  • Try yourself: use pivot tables


Common advanced formulas

Common (Advanced) Formulas

  • Vlookup/Hlookup

  • Match & Index

  • Sumproduct

  • Offset


Charts pivot tables and pivot charts

Charts, Pivot Tables, and Pivot Charts

Michelle Shieh


Charts

Charts

Where to make one:

  • In Excel 2003, Chart Wizard.

  • In Excel 2007, the ribbon!

    If you forget, look for: InsertChart


Charts types

Charts: Types

  • Bar Charts (Column Charts)

  • Line Charts

  • Pie Charts

  • Area Charts

  • XY (scatter) Charts , i.e. a scatterplot

  • Stock Charts

  • Surface Charts

  • Donut Charts

  • Bubble Charts

  • Radar Charts

  • Histograms (from Data Analysis Tools Add-in)

    and more…!


Scatterplot graphing

Scatterplot: Graphing

  • Have two columns with labeled data (a data range)

  • Open the Chart Wizard

    • Select both columns (including the header for that column)

    • click Insert  Chart

  • Follow the dialog prompts and customize!

  • Example:

    1. Plot Temperature against Time

    2. Overlay trend line


Histograms loading add ins

Histograms: Loading Add-ins

  • 2003: Tools>Add-ins >Analysis Toolpak

  • 2007: Excel Options


Histogram graphing

Histogram: Graphing

  • Data Data Analysis  Histogram

  • Choose data range

  • Bin width (predefined or automatic)

  • Chart output

    • Example: Temperature data

    • Reduce gaps between bars


Pivot tables

Pivot Tables

  • An easy way to summarize table

  • Designed for

    • Subtotaling or aggregating data by groups

    • Expanding and collapsing levels of data to focus your results

    • Filter, sort, group, conditionally format most useful subset


Pivot tables intro

Pivot Tables: Intro

Filters (eg Country)

Count, sum, mean, etc

Data you want to summarize

Ways to group data

  • Insert Pivot Table Check Range Ok

    • 2003: Data Pivot Table


Pivot tables example

Pivot Tables: Example

Using the Sales data, create:

  • Total Category Sales

  • Total Category Sales by Quarter

  • Product sales per Quarter

  • Top 3 Products in each Category

  • Using filter to see only US data


Pivot tables calculated field

Pivot Tables: Calculated Field

  • We can do calculation on the summarized field as well

  • Options -> Formulas -> Calculated Field

  • Name: Formula 1

  • Formula: Using Insert Field

  • Add -> OK


Pivot tables adjustments

Pivot Tables: Adjustments

  • Sorting data

  • Number formatting

  • Adjust Labels

    • Note: your custom name cannot be the same as your source name

  • Multiple data fields

    • Rearrange them by dragging the gray Data button


Pivot charts

Pivot Charts

  • Used to create stylized charts from a PivotTable report

  • Differences between PivotChart and standard graphing:

    • Chart types : PivotChart reports cannot be changed to an xy (scatter), stock, or bubble chart.

    • Source data: cannot change the data source range

    • Formatting: some formatting is not preserved when you refresh a PivotChart report, i.e. if any underlying data is modified

    • Row/Column orientation: Unlike a standard chart, you cannot switch the row/column orientation of a PivotChart report by using the Select Data Source dialog box. However, you can pivot the Row and Column labels to achieve the same effect


Visual basic for applications for excel

Visual Basic for Applicationsfor Excel

Koji Hamada


Excel workshop

VBA???

  • Microsoft based programming language for MS Office and other programs

  • Write programs within a program so you can program on a program

  • Write macros (user defined functions) within Excel

  • Macros are shortcuts!!!


How to write macros

How to write Macros

  • Record Macro function - User friendly way

    • Records your inputs onto a Visual Basic module

      • Storage place for your macros under the current workbook

  • Coding macros directly under the Project Window

    • Under the Visual Basic button

    • Essentially the same as above


  • Initializing vba

    Initializing VBA

    • Office 2007

      • First enable it under options (show developer tab in Ribbon)

  • Office 2003

    • Under “Tools” tab and under “Macros”

    • Or right click on your tool bar and check Visual Basic


  • Best way to learn vba

    Best way to Learn VBA

    • Play Around with the Record Macro function

    • Then go into the Project Window or Visual Basic Editor

    • Debug tab

    • Step Into funtion

      • Press F8 to go line by line

    • Then recreate macro by code with out the Record Macro function


    Quick example with record macro

    Quick Example with Record Macro

    • Go crazy and have fun :D

    • Use Relative References to apply macros on all spaces

    • Suggestions

      • Input values and play around with them

      • Apply functions (discuss later)


    Recreating your recording

    Recreating your Recording

    • Always begin with Sub [name of macro] ()

    • Use . to see what functions are available

      • Some functions are guided too

    • Check your code by using debug + F8


    Example 1 loops

    Example 1 - Loops

    • Do loops

      • While

        • Example – Do While x <= [integer]

      • Until

        • Example – Do Until [variable] = [integer]

      • End with loop

    • For loops

      • Example – For [variable] = [integer] to [integer]

    • End with Next [looping variable]


    Example 2 find function

    Example 2 – Find Function

    • Use to find an integer, string, or fuctions

    • Applications

      • Replace empty observations with values

      • Delete or modify certain values

      • Count the number of unique items


    Example 3 delete empty rows

    Example 3 - Delete Empty Rows

    • ...or columns or empty observations (but most effective to use find function)

      • See previous example

    • Cleans up the data

      • Useful for large datasets

      • Makes it easier to apply functions with having to write logical statements if data is not there

        • Example – if cell(x, y) = Not “”

          • If missing some data

    • Uses loops


    Example 4 creating arrays

    Example 4 – Creating Arrays

    • Arrays are storage vectors or matrices

    • Types of Array Values

      • Integers => Sub [name] () as

      • Long Integers => Sub [name] () as Long

      • Variant (characters and numbers) => Sub [name] () as Variant

    • Type of Arrays

      • Static – User defined number of values

      • Dynamic – Unknown number of values


    Example 4 creating arrays1

    Example 4 – Creating Arrays

    • 1 Dimensional Array (vector of data)

      • A row or column of data

    • 2 Dimensional Array (matrix of data)

      • A table of data

    • To Make larger sets of data, consider using a For Loop

    • May seem odd to contruct data like this BUT....


    Example 5 array applications

    Example 5 – Array Applications

    • Can apply some powerful fuctions to each table simply through one macro

    • Make quick tables with varying numbers

      • Example apply math operators (logs, invert matrix, etx)

    • Create a predefined Pivot tables out of multiple Arrays


    Summary

    Summary

    • Best way to learn is to use the Record Macro feature and debug your code

      • Next time when you’re doing analysis in your workbook, turn on the Record Macro button

        • ...remember to turn it off and reset periodically

      • If you have a job which requires repetitive Excel analysis, VBA will be your best friend

        • Applies to functions, transforming data, making pivot tables, etc


  • Login