際際滷

際際滷Share a Scribd company logo

MS EXCEL 2007

Download as DOCX, PDF
pillai college
pillai college

This document provides step-by-step instructions for using Microsoft Excel 2007 to create a customer details table, format cells based on location, remove duplicate records, import data from Access, create a custom list, and generate a pivot table and chart report. It describes entering customer data, conditional formatting for "Mumbai" locations, removing duplicate IDs, importing an Access table, making a custom dropdown list, and building a pivot table and chart to analyze the customer detail table.

1 of 19
Microsoft Office Excel 2007 1 Create a data sheet for customer details. Field should be customer id, customer name, location, product name, quantity and price. Enter only 10 records and font size-18 and font- Arial. STEP of creating a customer table- Write the field name as follows Customer id Customer name Location Product name Quantity price Now enter the 10 record in the respective field and save the file as customer detail. -Click on office button then click on save and write the file and save it.
Microsoft Office Excel 2007 2 Question 1- Find out the record according to the given condition where location= Mumbai" and format the data bold italic, underline, fill color light red and font color of text should be black. STEP of finding and formatting the record where location=Mumbai- Select the location with cell reference to (C1:C11) Click on conditional formatting Select highlight cell rules and select text that contains.
Microsoft Office Excel 2007 3 Write Mumbai in label box In list box select custom format and format cell window will be open Select font style- bold italic, underline-single, color-black.
Microsoft Office Excel 2007 4 Go to fill menu and choose light red color Press ok and final change is shown below. Output:-
Microsoft Office Excel 2007 5 Question 2- Remove the duplicate record from the data sheet from the table (customer detail). Step of removing the duplicate record:- Select the record. Go to data menu and select remove duplicates.
Microsoft Office Excel 2007 6 If all check box is selected then click on unselect all. Now check or select customer id for removing duplicate record. Click ok and window will be appear which say that no. of duplicate data found and removed.
Microsoft Office Excel 2007 7 Output:- 2 duplicate data found and remove, 8 unique values remain.
Microsoft Office Excel 2007 8 QUESTION 3- Get external data from access. Step of importing access data in excel Existing table in the access which is going to be import in excel file.
Microsoft Office Excel 2007 9 In excel go to data menu. Click on from access. Go to the file location where access file is saved. Select the file and press open.
Microsoft Office Excel 2007 10 Select the option button table in how you want to view data in excel sheet. Select existing table in option button and select location as explain above. Output-
Microsoft Office Excel 2007 11 Question 4- Create custom list and display on excel sheet. Step of creating custom list on excel Select the cell Go to home menu and select sort and filter. In sort and filter select custom list in drop down menu.
Microsoft Office Excel 2007 12 While select custom list in sort and filter a new small window will appear name as sort. Now in order - select custom list.
Microsoft Office Excel 2007 13 Now custom list window will appear. In custom list select new list and write in list entries. Click on add command.
Microsoft Office Excel 2007 14 After clicking Add you can see the list is added in custom lists. Now press ok.
Microsoft Office Excel 2007 15 Now press ok. Now in cell write a data which you wrote in custom list. Example: - write Pirana and drag it. OUTPUT:-
Microsoft Office Excel 2007 16 Custom list was created and after dragging the data written in cell will automatically generated. QUESTION 5 Create a pivot table and pivot chart report on customer detail table.
Microsoft Office Excel 2007 17
Microsoft Office Excel 2007 18
Microsoft Office Excel 2007 19

More Related Content

MS EXCEL 2007

  • 1. Microsoft Office Excel 2007 1 Create a data sheet for customer details. Field should be customer id, customer name, location, product name, quantity and price. Enter only 10 records and font size-18 and font- Arial. STEP of creating a customer table- Write the field name as follows Customer id Customer name Location Product name Quantity price Now enter the 10 record in the respective field and save the file as customer detail. -Click on office button then click on save and write the file and save it.
  • 2. Microsoft Office Excel 2007 2 Question 1- Find out the record according to the given condition where location= Mumbai" and format the data bold italic, underline, fill color light red and font color of text should be black. STEP of finding and formatting the record where location=Mumbai- Select the location with cell reference to (C1:C11) Click on conditional formatting Select highlight cell rules and select text that contains.
  • 3. Microsoft Office Excel 2007 3 Write Mumbai in label box In list box select custom format and format cell window will be open Select font style- bold italic, underline-single, color-black.
  • 4. Microsoft Office Excel 2007 4 Go to fill menu and choose light red color Press ok and final change is shown below. Output:-
  • 5. Microsoft Office Excel 2007 5 Question 2- Remove the duplicate record from the data sheet from the table (customer detail). Step of removing the duplicate record:- Select the record. Go to data menu and select remove duplicates.
  • 6. Microsoft Office Excel 2007 6 If all check box is selected then click on unselect all. Now check or select customer id for removing duplicate record. Click ok and window will be appear which say that no. of duplicate data found and removed.
  • 7. Microsoft Office Excel 2007 7 Output:- 2 duplicate data found and remove, 8 unique values remain.
  • 8. Microsoft Office Excel 2007 8 QUESTION 3- Get external data from access. Step of importing access data in excel Existing table in the access which is going to be import in excel file.
  • 9. Microsoft Office Excel 2007 9 In excel go to data menu. Click on from access. Go to the file location where access file is saved. Select the file and press open.
  • 10. Microsoft Office Excel 2007 10 Select the option button table in how you want to view data in excel sheet. Select existing table in option button and select location as explain above. Output-
  • 11. Microsoft Office Excel 2007 11 Question 4- Create custom list and display on excel sheet. Step of creating custom list on excel Select the cell Go to home menu and select sort and filter. In sort and filter select custom list in drop down menu.
  • 12. Microsoft Office Excel 2007 12 While select custom list in sort and filter a new small window will appear name as sort. Now in order - select custom list.
  • 13. Microsoft Office Excel 2007 13 Now custom list window will appear. In custom list select new list and write in list entries. Click on add command.
  • 14. Microsoft Office Excel 2007 14 After clicking Add you can see the list is added in custom lists. Now press ok.
  • 15. Microsoft Office Excel 2007 15 Now press ok. Now in cell write a data which you wrote in custom list. Example: - write Pirana and drag it. OUTPUT:-
  • 16. Microsoft Office Excel 2007 16 Custom list was created and after dragging the data written in cell will automatically generated. QUESTION 5 Create a pivot table and pivot chart report on customer detail table.