excel 2010 data tab

PROJECT ON DATA TAB(Get External Data and Sort & Filter Group)

Submitted By:-KULDEEP MALIKRoll No. – 123Reg No. – CRO0414269

Submitter to:- MR. ALOK GUPTA

XPXPXP

New Perspectives on Microsoft Excel 2010

Contents1. Get External Data 3-21a) Importing Data from Text Files 3-4b) Exploring Connections 5c) Exploring Data Ranges 6 7 6-7d) Definig a Trust Location 8 8e) Introducing Database 9 9f) Introducing Queries 10,11,12, 13, 14 ,15 10-15g) Importing data into pivottables and pivotcharts 16,17 16-17h) Importind data from the Web Query18 19 20 21 18-21

2. Sort & Filter 22-25a) Data Sorting 22 23 22-23b) Data Filtering 24 25 24-24

2

XPXPXP


Importing Data from Text Files• Text Import Wizard determines whether data is fixed-

width or delimited format• Steps of Text Import Wizard

– Start the wizard– Specify starting row; edit column breaks

• Delimited format: delimiter determines column breaks

• Fixed-width format: wizard guesses locations of column breaks

– Format data in each column

3

XPXPXP


Starting the Text Import Wizard

4

Formatting and Trimming Incoming Data

XPXPXP


Exploring Connections• Defined process of retrieving data from an

external file• Importing data creates a connection between

the workbook and the text file

5

XPXPXP


Exploring Data Ranges• External data range

–Each location in which a connection is applied

• Each external data range is given a name, which can be edited

• Excel can update (refresh) data ranges and connections manually or automatically

6

XPXPXP


Exploring Data Ranges

7

XPXPXP


Defining a Trusted Location• Once a trusted location is defined, Excel will access

the connection to the data source without prompting for confirmation that the connection is secure

8

XPXPXP


Introducing Databases• Excel can retrieve data directly from most database

programs• A common field can match information from two

tables into a single table

9

XPXPXP


Introducing Queries• Create a query to look at only specific information

from a database• A query can:

– Contain criteria (conditions that limit the number of records in the results)

– Specify how you want the data to appear

10

XPXPXP


Steps in the Microsoft Query Wizard• Create a data source• Choose table and fields (columns) to include• Determine whether to retrieve all records or to filter

data to retrieve only records that satisfy particular criteria

• Specify how to sort the data

11

XPXPXP


Creating a Data Source

12

Choose Table and Fields (Columns)

XPXPXP


Filtering and Sorting Data• Filter Data dialog box

• Sort Order dialog box

13

XPXPXP


Importing Data from Microsoft Query• Query Wizard – Finish dialog box options

– Return (import) data into the Excel workbook– Display results in Microsoft Query for further

editing and query definition• Import data in an Excel table, a PivotTable,

a PivotTable and PivotChart, or create the connection without importing the data

14

XPXPXP


Importing Data from Microsoft Query• Connection Properties dialog box

15

XPXPXP


Importing Data into PivotTables andPivotCharts• Can select and display values from only one area at a

time, adding only a single table and chart to the workbook

• Data used in the table and chart can be stored in an external data source

• Initial table and chart are empty until you define where to place fields from data source

16

XPXPXP


Importing Data into PivotTables• Can set PivotTable layout, change labels, and format

data values

17

XPXPXP


Creating a Web Query• Specify the URL and use Excel Web Query to

select portions of the Web page to import– URL form if file is stored on a Web server

– URL form if file is stored locally (not on Web server)

18

XPXPXP


Creating a Web Query• Text is imported into worksheet as unformatted text

19

XPXPXP


Importing Data from the Web• Use a Web query to retrieve online data and

import it into a workbook• Excel has several Web query files stored in a

library of built-in Web queries– Some include parameters for specifying exact

information to be imported

20

XPXPXP


Importing Data from XML• XML author can:

– Define what makes up a valid document (which elements are required, which are optional)

– Indicate the type of values each element can contain

• Schemas, though not required in XML, ensure that data inserted into an XML document follows predefined rules for content and structure

21

XPXPXP


Sorting Data

22

Sorting Data is an integral part of data analysis.

To sort the data, follow the steps given below:1. Select a cell in the database.2. On the Data Tab, in the Sort and Filter Group, click Sort. The sort dialog Box will appears. Or On the Home Tab, in the Editing Group, click sort & Filter and the select custom sort.

XPXPXP


Sort by Custom list

23

• Select a range that user want to sory by• Click the File tab, click options, and then click the Advanced category• Under General, Click Edit Custom Lists button. The dialog box appears.

• Click Import• Click ok• Click ok2. Select a cell in a database3. On the data tab, in the sort & filter group, click sort. The sort dialog box appears.4. Under column, in the sory by or Then by box, select the column to be sorted by a custom list5. Under Order, select Custom List. The Custom lists dialog box is displayed.6. Select the list that the user wants.

XPXPXP


Filtering a Database

24

A filter is used to select records that meet a specific criterian and temporarily Hide all the other records.

Filtering for unique values

XPXPXP


Using Auto Filter

25

excel 2010 data tab

Education