1 / 44

# Excel Workshop - PowerPoint PPT Presentation

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.

Excel Workshop

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

## 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

• Cancel changes – ESC

• Formula Bar

• Multiple ways to do most things

• Help! – F1

### 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

• 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

• 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

• 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

• Operators

• Numerical : +, -, /, *

• Text: & (concatenate)

• Quotes

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

### 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

• 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

• 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

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

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

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

• Create a Full Name column

• = FirstNameCell&“ ”&LastNameCell

• Create a Grade Point column

• Do some Excel magic

• Try yourself: use pivot tables

• Vlookup/Hlookup

• Match & Index

• Sumproduct

• Offset

## Charts, Pivot Tables, and Pivot Charts

Michelle Shieh

### Charts

Where to make one:

• In Excel 2003, Chart Wizard.

• In Excel 2007, the ribbon!

If you forget, look for: InsertChart

### 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

• Histograms (from Data Analysis Tools Add-in)

and more…!

### 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

• 2007: Excel Options

### 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

• 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

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

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

• We can do calculation on the summarized field as well

• Options -> Formulas -> Calculated Field

• Name: Formula 1

• Formula: Using Insert Field

• Sorting data

• Number formatting

• 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

• 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 Applicationsfor Excel

### 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

• 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

• 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

• 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

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

• 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

• 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

• 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

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

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

• 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

• 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