Excel workshop
1 / 44

Excel Workshop - PowerPoint PPT Presentation

  • Uploaded on

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

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

PowerPoint Slideshow about ' Excel Workshop' - aron

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


  • 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


  • 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


  • 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


  • 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


    • 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


  • 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(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



    • Checks if a referenced cell is empty


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


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

  • ISNA

    • Checks if a value is #N/A


    • Checks if a value is a number


    • Checks if a value is text


  • 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


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


  • 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


    • 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