day 6 microsoft excel chapter 2 contd microsoft excel chapter 3 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Download Presentation

Loading in 2 Seconds...

play fullscreen
1 / 36


  • Uploaded on

Day 6: MICROSOFT EXCEL – CHAPTER 2 Contd. MICROSOFT EXCEL – CHAPTER 3. Akhila Kondai September 04, 2013. Microsoft Excel – Chapter 2 CONTD. Lookup Functions. Lookup functions are used to look up values in a table to perform calculations or display results

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Day 6: MICROSOFT EXCEL – CHAPTER 2 Contd. MICROSOFT EXCEL – CHAPTER 3' - patch

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
day 6 microsoft excel chapter 2 contd microsoft excel chapter 3



September 04, 2013

lookup functions
Lookup Functions
  • Lookup functions are used to look up values in a table to perform calculations or display results
  • A Lookup table is a range that contains data for the basis of the lookup and data to be retrieved.
  • Used to gauge an entered value against a range of corresponding results
  • Well suited for large tables of data, such as tax tables
  • Also to convert the number grades into letter grades
  • Has three mandatory arguments
vlookup contd
  • Syntax

=VLOOKUP(lookup value , lookup table , column index number , [range lookup])

  • The lookup value ─ value to look up in a reference table
  • The lookup table ─ a range of cells containing the reference table
  • The column index number ─ the column number in the lookup table that contains return values

Converting the

Number grades to

the letter grades

hlookup function
HLOOKUP Function
  • The HLOOKUP function is used when the breakpoints and return data are placed in rows
  • The third argument now lists the row index
financial functions
Financial Functions
  • Used for decisions involving payments, investments, interest rates, etc.
  • Allows you to consider several alternatives
  • PMT
pmt function
PMT Function
  • Used to calculate loan payments
  • Has three arguments:
    • the interest rate per period
    • the number of periods
    • the amount of the loan
  • Computes the associated payment on a loan
pmt contd
  • Syntax:

Pmt( interest_rate, number_payments, PV, [FV],[Type] )

  • Example:
    • Pmt(7%/12, 2*12, 5000, 0, 0)
    • returns the monthly payment on a $5,000 loan

at an annual rate of 7%. The loan is paid off in

2 years (ie: 2 x 12).

    • E2 - 7% ; E3 - 24 ; E4 - $5,000.00
    • PMT ( E2/12 , E3 , - E4 )
range names
Range Names
  • A range name is a word or phrase used to identify a cell or cell range
  • Range names make formulas easier to read
  • Range names use the following rules:
    • 1to 255 characters
    • Begin with a letter or underscore (_)
    • Contain letters, digits, period, underscore.
creating and maintaining a range name
Creating and Maintaining a Range Name
  • Type the range name in the Name Box area
  • Enter the name using New Name dialog box
  • Name Manager dialog box is used to edit or delete a range name
  • Insert a table of Range Names ( Use in Formula -> Paste Names)
rank function
Rank Function
  • The RANK function is useful for producing a ranking by using a formula
  • Syntax

=RANK(number, range, order)

number: the number/cell for which you want to find the ranking within the “range”

range: range of numbers/cells within which the “number” will be ranked

order: not really required for our purposes. Leave it blank do not put anything.

rank function example
Rank Function Example
  • Insert a Rank function in cell D3
  • Type =rank(c3,c3:c8)
  • Autofill the formula to remaining cells. Observe the cell references and make the references absolute
  • =rank(c3,$c$3:$c$8)
conditional formatting
Conditional Formatting
  • Formats the data in excel sheet if and only if the data satisfies the condition.
  • We wish to apply conditional formatting for the values in column3
  • Select all the values in column3

->Click on conditional formatting in styles group

->Select the type of condition which you want to give

->Give the condition

  • 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.
text files
Text files
  • The text file has .txt extension and contains only text (alphabets, numbers and symbols).
  • We must choose a delimiter for importing.

Ex: Space, tab or Comma etc

  • Columns align according to the delimiters.
csv files
CSV Files
  • CSV abbreviated as Comma-Separated Values
  • Text file with comma as delimiter.
  • Excel imports the text between the commas in the text file into individual cells.
importing a text file
Importing a Text File
  • ->Data Ribbon

->Click “from text” which is in External data group

->Browse the location of text file

->Open the text file

  • Text import wizard (3 steps)
    • Select the type that describes the data
    • Select the type of delimiter in your data
    • Select the format of column data
    • Select the location (where to import the data in your sheet)
importing an access database t able or query
Importing an Access Database Table or Query
  • ->Data ribbon

->Click “from access” in External data group

->Browse the location of file

->Open the file

  • Import Wizard
    • Select the table in database which you want to import into your sheet
    • Select the location where you want to place the imported data
  • Import data from other sources
create a web query
Create a Web Query
  • A Web query enables Excel to go to a particular Web site to obtain information
  • Web queries let you extract data from tables on a Web page
  • We need this to display live stock prices, traffic reports, airport delays etc.
  • If the Web address changes, you must change the Web query and URL to ensure you have the most accurate information and citation
  • If you have to log in to the Web site, the query generally will not work
create a web query1
create a web query
  • Select “FROM WEB” in Get External Data column.
  • Specify the ULR in ADDRESS ( u can see the page by clicking GO )
  • Select IMPORT
  • Select cells where to put data
manage connections
Manage Connections
  • When you import data using the options in the Get External Data group, Excel creates a link to the original data source
  • Refresh connections periodically to ensure you have the most up-to-date and accurate information
  • View or remove existing connections through the Workbook Connections dialog box

Refresh Connections

  • Display Connections
  • Set Connection Properties
convert text to columns
Convert Text to Columns
  • In the imported data, the data may not be structured in a way that meets your needs.
  • If data is displayed in one column, use the Text to Columns command to separate the data into multiple columns
  • Convert Text to Columns Wizard guides you through the process of separating data and choosing a delimiting character
text manipulation
Text Manipulation
  • Converting text to columns.
    • Select the cell or range which contains the delimited text
    • Go to Data ribbon->Data tools->Text to columns
  • Manipulating Text with functions.
    • Concatenate Function combines the text =CONCATENATE(text1,text2)
    • Proper Function capitalizes the first letter in a text string =PROPER(text)
    • Upper Function converts the text to upper case =UPPER(text)
    • Lower Function converts all upper case letters to lower case =LOWER(text)
text manipulation1
Text Manipulation
  • Other text functions
xml files
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
  • Steps to import
    • Go to Data tab->From Other Sources->From XML Data Import
    • Choose the XML file and click OK.
    • Choose the cell from where you want the data to be inserted from the XML file.
  • Homework 1 is now posted. It is due on 09/13/13 by 11:59 P.M.
  • A workshop is being conducted on ‘Installing a windows virtual machine on Mac’, this Friday (6th Sept 2013) at 1.00 P.M in 206 Armstrong hall.