excel presentation
Post on 15-Jan-2015
939 Views
Preview:
DESCRIPTION
TRANSCRIPT
Excel Access
Putting the
Data in Data Visualization
A typical Excel spreadsheet
Excel is a spreadsheet program that allows you to view, create, modify, and visualize data. It can also help manage simple databases
Excel Interface
Excel Interface
Ribbon
The Ribbon displays many of the most commonly used tools. There are multiple ribbons that organize tools by category.
Excel Interface
The Formula Bar displays the formula behind the contents of the selected cell
Formula Bar
Selected Cell
Excel Interface
Moving and Selecting in Excel • Arrow keys move to adjacent cells • Double-click a cell to enter the formula bar and edit contents
• Tab to end edit and go to cell in next column • Return to end edit and go to cell in next row
• Click and drag to select multiple cells • Click a row number or column letter to select the entire thing • Click the Select All Arrow to select all cells in the worksheet
• Ctrl + Arrow goes to the end of a range • Shift + Arrow selects adjacent cells • Ctrl + Shift + Arrow selects range • Ctrl + Scroll to zoom in and out
Select All Arrow
Excel Prepping a dataset
1 2
3
1. Click on the View ribbon 2. Click on Freeze Panes 3. Click Freeze Top Row
Freezing rows or columns makes it easier to navigate around. To freeze the top row:
Excel Prepping a dataset
1
2
3
The Auto Filter makes it easy to filter and sort your data. Here’s how to turn it on:
1. Click on the Home ribbon
2. Click on Sort & Filter
3. Click Filter
Excel Prepping a dataset
1
You can change the height of a row or the width of a column to see your data better.
Click on the divider bar and drag to resize
Selecting multiple columns or rows modifies them all at once
Double-clicking the divider bar auto-sizes to the largest value
Excel Prepping a dataset
1
You can hide rows or columns to make your data easier to view. Hidden columns are marked by a thicker divider bar
1. Select one or more columns
2. Right-click in the selection and click Hide
2
Excel Prepping a dataset
1
You can change the way numbers display to make them easier to read.
1. Select the cells, row, or column
2. Click in the bottom-right corner of the number options
2
3. Edit the number format or display
3
Excel Prepping a dataset
1
Sometimes numbers are stored as text. To operate on these, they need to be numbers.
1. Select cell(s) with a left-justified number
2. Click on the exclamation mark
2
3. Click Convert to Number
3
Excel Prepping a dataset
How Excel handles dates and times:
Days are numbered starting at January 1, 1900 (Today is day 40975)
Time is a fraction of a day (Midnight = 0, Noon = 0.5, etc.)
Excel Functions
Inserting a Row or Column
1. Select the row/column before which you want to insert new rows/columns
2.Right-click and select Insert or click Insert in the Ribbon
Tip: If you Select multiple rows/columns, Excel will insert an equal number of rows/columns
1
2
2
1
Excel Functions
Using Functions to Calculate Cell Values
1. Type = in Cell BG2
2. Click on cell BD2 to “refer” Excel to the value in it
1
2
Excel color-codes your cell references for easy auditing
3. Type / and click on cell AG2
The formula bar displays the function you enter
Excel Functions
More on Formulas Basic mathematical operators can be used by entering a value or cell reference, the operator, and another value or cell reference.
+ Add - Subtract * Multiply / Divide ^ Exponent () Order of Operations
Excel Functions
Anatomy of a Formula
=SUMPRODUCT(A2:A7,B2:B7)
A function tells excel to perform a specific set of operations on the cells you refer it to
Parenthesis denotes start of a function
Ranges or numbers separated by commas are arguments
Colon indicates a range – all cells between A2 and A7
Comma denotes the next argument
Parenthesis denotes end of a function
When you enter a function, a box appears below the cell telling you the arguments Excel expects
Excel Functions
Common Formulas and Their Arguments SUM(Range1,Range2,…) Returns the sum of the values
PRODUCT(Number1,Number2,…) Returns the product of the values
SUMPRODUCT(Range1,Range2,…) Multiplies each value in range 1 by the corresponding value in range 2, then sums these
AVERAGE(Range1,Range2,…) Returns the average of a set of values
MAX(Number1,Number2,…) Returns the maximum value of a set
MIN(Number1,Number2,…) Returns the minimum value of a set
COUNT(Range1,Range2,…) Counts the number of items in a range
SUMIF(Range,Criteria,[Sum_Range]) Returns the sum of the values in the “sum range” whose associated cell in the “range” is equal to the “criteria”
COUNTIF(Range,Criteria) Counts the number of items in a range equal to the “criteria”
Tip: You can browse all of Excel’s functions, their output, and their arguments by clicking the fx next to the formula bar
Excel Functions
Using IF Statements for Conditional Calculation
1. Enter =IF( 2. Enter the condition you’d like to test (e.g. B2=0)
3. Enter the value or calculation if the condition is true (e.g. “”) **
4. Enter the value or calculation if the condition is false (e.g. A2/B2)
** Note: “” is the symbol for a null (blank) cell
To propagate a formula to all rows, select the cell with the formula, & double click the black handle in the bottom right corner of the cell
Excel Functions
Using Lookups to Associate New Data
1. Enter =VLOOKUP(
2. Enter the value or cell you will use to look up the data (e.g. B2)
3. Enter the range from which you will look up (e.g. D1:F6) **
4. Enter the index number of the column that contains the data you want
Excel only looks for the value you specify from the first column of the “table_array”, so make sure your lookup range is arranged accordingly
Excel Functions
Using Lookups to Associate New Data
By default, when you propagate a formula Excel uses relative references, changing the input cells each time. For lookups, you usually want to be looking up from the same range, so these can be converted to absolute references, which are denoted by a $ before the column and row labels
Convert to absolute cell references for “table_array” inserting a $ before the column and row of the reference. This can be done by selecting the reference and hitting F4 once (⌘+T on a mac).
Excel Charts
SUMIF Allows you to summarize data for different categories and help create charts
Excel Charts
1. Select the columns you want as categories and as values
2. Select the Insert ribbon and click the type of chart you want
1
2
1
Tip: Hold Ctrl to select multiple columns at once if they’re not adjacent
Creating a Chart
Excel Charts
You can modify any part of a chart by selecting that part, then double-clicking it
To change the values displayed on an axis: 1. Select and double-click the axis 2. Under Axis Options, enter the values you want
Formatting a Chart
1
2
Excel Pivot Tables
• Pivot tables allow you to summarize your data quickly and flexibly
• Within the input range, all cells in the same row is considered to be associated with each other, and each column is assumed to contain a different piece of information
• Pivot tables then allow you to use columns as categories and others as values to create summary tables of your data
About Pivot Tables
Excel Pivot Tables
1. Select the data you want to use in the table, including the column headers
2. Select the Insert Ribbon and click PivotTable
Creating a Pivot Table
3. Select New Worksheet
1
2
3
Excel Pivot Tables
1. Drag the fields you want to use for rows and columns from the field list to the label boxes
2. Drag the information you want to populate the table to the values box
Using a Pivot Table Field List
Excel Pivot Tables
To change how the values are displayed:
1. Click on the arrow next to the field in the Values Box and select “Value Field Settings…”
Using a Pivot Table
2. Select how you want your data to display
1
2
top related