Transcript Slide 1
DAY 4: MICROSOFT EXCEL Aliya Farheen [email protected] January 22, 2013. REMINDER • MyITLab Lesson A is due on 1/27/2015 by 11.59pm. • Be sure to complete the Post test in each chapters in Lesson A. • NO POST TEST- NO CREDIT. ANNOUNCEMENT • Homework #1 is posted on the website at http://cs101.wvu.edu/instructors/farheen/assi gnments/ • Due date for Homework #1 is 2/6/2015 by 11:59 pm. IMPORTING DATA • Importing –Inserting data from one application or file into another. • Text, CSV, XML and Database files are the commonly imported files. • Before importing we have to check if we want to manage the data as a separate entity in Excel or you want a connection to the original data source/application. XML FILES • XML is eXtensible Markup Language • Files can contain a significant amount of machine readable data and allow us to easily import it into Microsoft Office to work with. IMPORTING XML FILE • Make sure that the data XML Files are downloaded on your system • To import the data of the file into excel sheet open Excel and follow these steps: 1) Select the "Data" ribbon 2) From the "Get External Data" group (should be top left), click on "From Other Sources" • 3) From the drop down list, select "From XML Data Import“. 4) Find the .xml file on your computer and Open it 5) Then a message might pops up asking for creating a schema, just hit OK 6) "Import Data" box is going to pop. Select the "XML table in existing worksheet" and put the starting cell appropriately($A$3). CONDITIONAL FORMATTING • Conditional Formatting highlights or emphasizes cells that meet certain conditions • • • • • Highlight cells rules Top/Bottom rules Data bars Color scales Icon sets • Can create new rules based on your own conditions or using formulas EXAMPLE • A conditional formatting rule might be: • “If the value is less than $2000, color the cell red.” CREATE THE RULE • In our example ,we'll create a conditional formatting rule for any cells containing a value higher than 4000. • Select the desired cells for the formatting • From the Home tab, click the Conditional Formatting comman d. A drop-down menu will appear. • Hover the mouse over the desired conditional formatting type, then select the desired rule from the menu that appears. In our example, we want to highlight cells that are greater than $4000 • A dialog box will appear. Enter the desired value(s) into the blank field. In our example, we'll enter 4000 as our value. • Select a formatting style from the dropdown menu. In our example, we'll choose Green Fill with Dark Green Text, then click OK. • The conditional formatting will be applied to the selected cells. REMOVE CONDITIONAL FORMATTING • Click the Conditional Formatting command. A drop-down menu will appear. • Hover the mouse over Clear Rules, and choose which rules you wish to clear. In our example, we'll select Clear Rules from Entire Sheet to remove all conditional formatting from the worksheet. IN CLASS PROJECT Objectives Covered • Rename and delete worksheets • Import XML data • Insert rows and columns • Apply conditional formatting • Apply cell formatting • Write and AutoFill a formula TO DO • Download the following files at http://cs101.wvu.edu/instructors/farheen/le cture-notes/ ---Instructions.pdf ---Schools.xml • Read the instructions and start the project SUBMISSION • To submit your homework, go to 'Course Tools‘ section on the website, log-in with your MyID and select 'Submit Assignments'. • Make sure to select the appropriate assignment. • Browse for your file on computer and click on 'Submit Assignment'.