day 8: microsoft excel chapter 3, 4, 5 - cs101 · day 8: microsoft excel chapter 3, 4, 5 daksha...
TRANSCRIPT
DAY 8:
MICROSOFT EXCEL
CHAPTER 3, 4, 5
Daksha Yadav
September 11, 2013
1
ANNOUNCEMENTS
• Homework Help Live! for HW #1 - Thursday at 7pm
• Able to view a video walk-through of a HW #1-like
project on YouTube, work through it on their own,
and ask questions/get responses in real time.
• YouTube
channel: http://www.youtube.com/wvucs101.
• Instructions:http://cs101.wvu.edu/resources/homewo
rk/homework-help-live/homework-1/.
2
TRENDLINES
• Used to predict future or past values by
extrapolating the lines already plotted on a
chart
• Types:
– Linear
– Exponential
– Linear Forecast
– Two Period Average
ADDING TRENDLINES
Select Data Series (In this case C grades)
ADDING TRENDLINES - CONT’D
THE TRENDLINE
PREDICTING THE FUTURE/PAST
WITH TRENDLINES
• Right-click on the trendline you want to predict the values into the future or past
• Click on Format Trendline
• Forecast Group -> Set Periods
• Click on the different Trend types to view how they will predict the data
(If you see it predicts negative gasoline prices, for instance, you may want to change trend types)
R-SQUARED VALUES
• Gauge the accuracy of how a trendline fits
the underlying data
• Range from 0 to 1, with higher numbers
(closer to 1) being better fits, not likely to get
a value of 1
• At the bottom of the Format Trendline dialog
box, “Display R-squares value on chart”
• Select between the different chart types to
view which fits the data the best
TRENDLINE EQUATIONS
Under the Format Trendline dialog box, you
can also view the trendline equations.
IMPORTIN G DATA
• Importing is the process of inserting data
from one application or file into another.
• Excel lets us import different types of
data!
– Text file
– Access DB
– XML, etc.
10
IMPORT(ANT) DECISIONS
Decide ahead of time if you want to embed data or link to it.
Embedded data: Is not linked to the original source, and can be edited. A change in the data in one place does not affect the other.
Data as a connection: Linked to the original data, Excel can then be refreshed from the source and the data updated if any changes are made.
11
IMPORTING TEXT FILES
• Delimiters: Special characters that
separate the data, so it doesn’t run
together.
• Most common: TAB (Tab delimited) ,
Comma (Comma Separated)
12
TAB DELIMITED TEXT
13
TAB DELIMITED IMPORT
14
CSV
15
CSV IMPORT
16
IMPORT STEPS
• Data Ribbon
• Get External Data
• From Text
• Select File
• Follow Wizard Prompts
17
THE WIZARD
18
THE WIZARD (CONTD.)
19
THE WIZARD (CONTD.)
20
OTHER DATA SOURCES
Source Definition
SQL Server Create a connection to an SQL server
table and import data as a table or
PivotTable.
Analysis Services Create a connection to an SQL server
analysis services cube, and import
data as a table or PivotTable
XML Data Import Open or map an XML file into Excel
Data Connection Wizard Import data for an unlisted format by
using the Data Connection Wizard and
OLEDB.
Microsoft Query Import data for an unlisted format by
using the Microsoft Query Wizard and
ODBC.
21
22
HIDING AND UNHIDING ROWS,
COLUMNS, AND WORKSHEETS
• Hidden refers to a state in which rows,
columns, and sheets are invisible
• Often done to conceal nonessential
information, information not needed at a
particular time, confidential information, or
sensitive data
– Examples include Social Security numbers,
salary or rate of pay, pricing data, and trade
secret information
LARGE DATABASES
• Freezing rows and columns: to keep
selected rows and columns visible even if
you scroll down or sideways
• Printing Large Databases
– Set Print Area
– Page Breaks
– Page Break Preview
23
EXCEL TABLES
• A table is an area in the worksheet that contains rows and columns of similar or related information
– Can be used as part of a database or organized collection of related information
– Worksheet rows represent the records; worksheet columns represent the fields in a record
• The first row contains the column labels or field names – Identifies data to be entered in the columns
• Each row in the table contains a record
24
EXCEL TABLES
• Every cell in the table area, except the field
names, contains a specific value for a specific
field in a specific record
• Every record (row) contains the same fields
(columns) in the same order as every other
record
25
EXCEL TABLES (CONTD.)
26
First row
contains field
names
Each row is
a record
TABLE TOOLS
• Has all the options related to formatting
the table
• Change the Table Style of the Current
Table
27
FIND AND REPLACE
• Allows global editing of data
28
SORTING DATA
• Sorting arranges records in a table by the
value in field(s) within a table
• The sort command puts lists in ascending
or descending order according to specified
keys
• Keys are the fields on which records are
sorted
• FILTERS: Text filters, Number filters,
Custom filters
• TOTAL ROW
30