- By
**aron** - Follow User

- 118 Views
- Uploaded on

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

### Basics, Formats, and Formulas

### Charts, Pivot Tables, and Pivot Charts

### Visual Basic for Applicationsfor Excel

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

- 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

- 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

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

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
- =VLOOKUP(Grade, GradePointsTable, 2, False)

- Do some Excel magic
- Try yourself: use pivot tables

Common (Advanced) Formulas

- Vlookup/Hlookup
- Match & Index
- Sumproduct
- Offset

Michelle Shieh

Charts

Where to make one:

- In Excel 2003, Chart Wizard.
- In Excel 2007, the ribbon!
If you forget, look for: InsertChart

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

- 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

- 2003: Tools>Add-ins >Analysis Toolpak
- 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
- Add -> OK

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

- 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

Koji Hamada

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 Coding macros directly under the Project Window

- Record Macro function - User friendly way
- Records your inputs onto a Visual Basic module
- Storage place for your macros under the current workbook

- Records your inputs onto a Visual Basic module

- Under the Visual Basic button
- Essentially the same as above

Initializing VBA Office 2003

- Office 2007
- First enable it under options (show developer tab in Ribbon)

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

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

- Do loops
- While
- Example – Do While x <= [integer]

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

- End with loop

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

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

- 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

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

Download Presentation

Connecting to Server..