Excel 2010 data tab

MS Excel 2010 Data Tab ( Get External Data and Sort & Filter Group)

1. PROJECT ON DATA TAB (Get External Data and Sort & Filter Group) Submitted By:- KULDEEP MALIK Roll No. – 123 Reg No. – CRO0414269 Submitter to:- MR. ALOK GUPTA

2. Contents Get External Data 3-21 Importing Data from Text Files 3-4 Exploring Connections 5 Exploring Data Ranges 6 7 6-7 Definig a Trust Location 8 8 Introducing Database 9 9 Introducing Queries 10,11,12, 13, 14 ,15 10-15 Importing data into pivottables and pivotcharts 16,17 16-17 Importind data from the Web Query18 19 20 21 18-21 Sort & Filter 22-25 Data Sorting 22 23 22-23 Data Filtering 24 25 24-24 New Perspectives on Microsoft Excel 2010 2

3. 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 New Perspectives on Microsoft Excel 2010 3

4. Starting the Text Import Wizard New Perspectives on Microsoft Excel 2010 4 Formatting and Trimming Incoming Data

5. Exploring Connections Defined process of retrieving data from an external file Importing data creates a connection between the workbook and the text file New Perspectives on Microsoft Excel 2010 5

6. 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 New Perspectives on Microsoft Excel 2010 6

7. Exploring Data Ranges New Perspectives on Microsoft Excel 2010 7

8. 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 New Perspectives on Microsoft Excel 2010 8

9. Introducing Databases Excel can retrieve data directly from most database programs A common field can match information from two tables into a single table New Perspectives on Microsoft Excel 2010 9

10. 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 New Perspectives on Microsoft Excel 2010 10

11. 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 New Perspectives on Microsoft Excel 2010 11

12. Creating a Data Source New Perspectives on Microsoft Excel 2010 12 Choose Table and Fields (Columns)

13. Filtering and Sorting Data Filter Data dialog box Sort Order dialog box New Perspectives on Microsoft Excel 2010 13

14. 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 New Perspectives on Microsoft Excel 2010 14

15. Importing Data from Microsoft Query Connection Properties dialog box New Perspectives on Microsoft Excel 2010 15

16. Importing Data into PivotTables and PivotCharts 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 New Perspectives on Microsoft Excel 2010 16

17. Importing Data into PivotTables Can set PivotTable layout, change labels, and format data values New Perspectives on Microsoft Excel 2010 17

18. 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) New Perspectives on Microsoft Excel 2010 18

19. Creating a Web Query Text is imported into worksheet as unformatted text New Perspectives on Microsoft Excel 2010 19

20. 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 New Perspectives on Microsoft Excel 2010 20

21. 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 New Perspectives on Microsoft Excel 2010 21

22. Sorting Data New Perspectives on Microsoft Excel 2010 22 Sorting Data is an integral part of data analysis. To sort the data, follow the steps given below: Select a cell in the database. 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.

23. Sort by Custom list New Perspectives on Microsoft Excel 2010 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 ok 2. Select a cell in a database 3. 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 list 5. Under Order, select Custom List. The Custom lists dialog box is displayed. 6. Select the list that the user wants.

24. Filtering a Database New Perspectives on Microsoft Excel 2010 24 A filter is used to select records that meet a specific criterian and temporarily Hide all the other records. Filtering for unique values

25. Using Auto Filter New Perspectives on Microsoft Excel 2010 25