# Excel

In:

Submitted By LiamGuile
Words 1579
Pages 7
Quality Management

Objective
 To provide the participants with an understanding of the advanced features of MS Excel

Today is 7/22/2011

2

® EXCEL

Topics
Working with Data Lists

Naming Cells and Ranges

Conditional Functions
Lookup Functions

Pivot Tables
Data Validation

Today is 7/22/2011

3

Naming Cells and Ranges

Today is 7/22/2011

4

Name cells by using labels
Name cells by using existing row and column labels
Select

the range you want to name, including the row or column labels.

On
In

the Insert menu, point to Name, and then click Create.

the Create names in box, designate the location that contains the labels by selecting the Top row, Left column, Bottom row, or Right column check box.

Note A name created by using this procedure refers only to the cells that contain values and does not include the existing row and column labels.

Today is 7/22/2011

5

Name a Cell or Range Using Names Box
 1. Select the cell or range you want to name.  2. Click the Name box on the formula bar.  3. Type a name for the range. A range name can include uppercase or lowercase letters, numbers, and punctuation, but no spaces. Try to use a simple name that reflects the type of information in the range, such as BookTitle.  4. Press Enter. The range name will appear in the Name box whenever you select the range.

Today is 7/22/2011

6

Conditional Functions

Today is 7/22/2011

7

Using IF – Basic and Nested
Syntax
IF(logical_test,value_if_true,value_if_false)

Using IF Function - Basic
Enter

the formula =IF(condition,expression1,expression2), where condition is any condition that is either true or false, expression1 is the value of the formula if the condition is true, and expression2 is the value of the formula if the

### Similar Documents

#### Excel

Words: 377 - Pages: 2

#### Excel

...Microsoft Excel 2010 Formulas & Functions  Table of Contents Excel 2010 Formulas & Functions ……………………………………………….….. o Formula Basics ……………………………………………….….. o Order of Operation ……………………………………………….….. Conditional Formatting ……………………………………………….….. Cell Styles ……………………………………………….….. Formulas & Functions ……………………………………………….….. Basic Formulas ……………………………………………….….. o Add the Values in Rows or Columns ……………………………………………….….. o Find the Average, Maximum, or Minimum ……………………………………………….….. Ranges & Individual Cells ……………………………………………….….. Copy a Formula ……………………………………………….….. o Using the Fill Handle ……………………………………………….….. o Using Traditional Copy & Paste ……………………………………………….….. Referencing Cells in Formulas ……………………………………………….….. o Relative References ……………………………………………….….. o Absolute References ……………………………………………….….. o Mixed References ……………………………………………….….. o References to Other Worksheets ……………………………………………….….. o Other Workbook References ……………………………………………….….. Quick Reference Guide ……………………………………………….….. o Add, Subtract, Multiple & Divide ……………………………………………….….. o Understanding Error Values ……………………………………………….….. Practical Purposes of Excel Formulas ……………………………………………….….. o Using Date & Time Formulas ……………………………………………….….. Commonly Used Functions & Formulas ……………………………………………….….. o Math & Trig Icon ……………………………………………….….. Logical Icon ……………………………………………….….. Lookup & Reference Icon ……………………………………………….….. Date & Time Icon ……………………………………………….….. AutoSum Icon ……………………………………………….….. Statistical...

Words: 5629 - Pages: 23

#### Microsoft Excel

Words: 4397 - Pages: 18

Free Essay

#### Excel Tips

...1 Microsoft Excel 2010 Tutorial Excel is a spreadsheet program in the Microsoft Office system. You can use Excel to create and format workbooks (a collection of spreadsheets) in order to analyze data and make more informed business decisions. Specifically, you can use Excel to track data, build models for analyzing data, write formulas to perform calculations on that data, pivot the data in numerous ways, and present data in a variety of professional looking charts. The Ribbon Understanding the Ribbon is a great way to help understand the changes between Microsoft 2003 to Microsoft 2010. The ribbon holds all of the information in previous versions of Microsoft Office in a more visual stream line manner through a series of tabs that include an immense variety of program features. Home Tab This is the most used tab; it incorporates all text and cell formatting features such as font and paragraph changes. The Home Tab also includes basic spreadsheet formatting elements such as text wrap, merging cells and cell style. Insert Tab This tab allows you to insert a variety of items into a document from pictures, clip art, and headers and footers. Page Layout Tab This tab has commands to adjust page such as margins, orientation and themes. Created By: Amy Beauchemin Source: office.microsoft.com 1/13/11 2 Formulas Tab This tab has commands to use when creating Formulas. This tab holds an immense function library which can assist when creating any formula or function...

Words: 7496 - Pages: 30

#### Excel Formulas

...Microsoft Excel: Fo rmulas, F or mulas.. . Northern New York Library Network 6721 US Hwy 11 Postdam, NY 13676 877-833-1674 or 315-265-1119 www.nnyln.org Jim Crowley C3 - Crowley Computer Consulting 9148 State Highway 37 Ogdensburg NY 13669 315-394-7008 fax 315-394-7009 www.crowleycomputers.com jim@crowleycomputers.com Description: Excel has hundreds of functions and nobody knows them all, but spend some time and learn to create more powerful formulas. Students will dive into decision making functions like if() and lookup() and make Excel work for them! Students will also explore efficient use of multiple sheets in a workbook, creating multi-sheet and multi-workbook formulas. Students will learn about: if() functions, lookup() functions, conditional formatting, and using multiple worksheets in a workbook. 1. Course objectives. a. To provide the class with an exploration of spreadsheet design. b. To develop computer and spreadsheet literacy. c. To address the class' questions. 2. Schedule a. 9:00 Registration b. 9:30 Morning session c. 12:00 Departure 3. Handouts available in Adobe Portable Document Format (PDF) for download at www.crowleycomputers.com/handouts.htm 1. 2. Formulas: All formulas begin with = or basic math function a. Simple operators Monthly weather (1) + averages.xls (2) (3) * multiply (4) / divide (5) ^ exponent (6) = < > comparison b. Order of operator precedence (or Algebraic Order of Operations, for those of you as old as me!) determines what order...

Words: 1729 - Pages: 7

Free Essay

#### Microsoft Excel

...↑ up Arrow ফা ↓ down Arrow তত রিক করয অ঩নায ঩ড়ায ঳ু রফধা ঄নু ঳ারয স্ক্রর স্পীড রিক করয রনন। Microsoft Excel ঴রে একরি Spreadsheet তরাগ্রাভ। আ঴া Microsoft Office এয একরি ঄ং঱। এরি একরি Worksheet Analysis তরাগ্রাভ঑। ভূ রতঃ এরেররয ঳া঴ারমে ডািা এরি এফং ঄োনারাআর঳঳ উবয় ঳ু রফধা ঩া঑য়া মায়। অভারদয ঩যীক্ষায Marksheet এয ঳ারথ অভযা ঳ফাআ ঩রযরচত। Spreadsheet ঴রে ভাকক঱ীরিয ভত এক ধযরণয ছক। Microsoft Word খুররর তমভন কা঳কয঳঴ একরি ঳াদা ঩াতা ঩া঑য়া মায় এফং কী-তফাডক তথরক ররখরত শুরু কযরর ঳াদা ঩াতায় তরখা শুরু ঴য়। ততভরন Excel খুররর ঳াদা ঩াতায ঩রযফরতক তিরফররয ভত একরি ছক ঩া঑য়া মায় এফং ররতরি ঘরয করয়করি ঄ক্ষয তরখা মায়। এছাড়া এরি ভাকক঱ীরিয ভত করাভ (Column) এফং ঳ারযরত (Row) রফবক্ত থারক। A, B, C, D এবারফ করারভয নাভকযণ কযা থারক এফং 1, 2, 3, 4 এবারফ ঳ারযয নাভকযণ কযা থারক। এআ ঳ভস্ত ঘরয ঄ফরিত গারণরতক ঳ংখোরক উ঩য, রনচ, ডান ফা ফাভ, তম তকান রদক তথরক তমাগ, রফরয়াগ, গুণ, বাগ আতোরদ কযা মায়। ঳াধাযণত তমাগ, রফরয়াগ, গুণ, বাগ আতোরদ঳঴ জরির গারণরতক ঳ভ঳োয ঳ভাধান, ডািায উ঩য রবরি করয রফরবন্ন ধযরণয গ্রাপ ততযী, ডািারক ঳াজারনা঳঴ রফরবন্ন ধযরণয ডািা ভোরনজরভন্ট, ঩রয঳ংখোরনয রফরবন্ন কাজ Excel রদরয় কযা ঴য়। ঄োকাা্ন্টর঳য রফরবন্ন কাজ, তমভন- তরজায, রাব ক্ষরত র঴঳াফ, Balance Sheet আতোরদ কাজ঑ Excel রদরয় কযা ঴রয় থারক। এজনে Microsoft Excel তক Accounting Package ProgramI ফরা ঴য়। facebook /gmail/skype: - http://tanbircox.blogspot.com Want more Updates  http://facebook.com/tanbir.ebooks রনরচয উদা঴যণ রক্ষে করুন। Excel ফেফ঴ায করয একরি ডািারফজ এফং ঳ংরিষ্ট চািক তদ঑য়া অরছ। ঳঴জবারফ তফাঝারনায জনে একরি ররতষ্ঠারনয PC, Disk এফং CD...

Words: 4815 - Pages: 20

#### Excel Final

...data. * C - (TCO 3) Explain how you will visually represent the comparison of the total annual sales at the individual stores for each of the three years. Describe methods to make your strongest performing store stand out. * D - (TCO 7) Once you have finished the above tasks, your Excel workbook will be placed on your company server so that all of the managers can view it. You know that they may have questions regarding your work, and want them to know that you created the workbook so that they can contact you. Describe how you will convey the information regarding worksheet creation. A I was lucky enough to be provided with a .csv file with the weekly sales for all three stores for the last three years. This makes this part of my job easy because CSV stands for comma-separated value file which means that when I import this file into Excel it will already put all of the information into columns for me. The most I would have to do is make sure the columns all have headings and are formatted to my liking. All I would have to do to open the file is download the file onto my computer. Click the file and it should open directly into Excel. B There will be a lot of data in my new Excel spreadsheet, so organizing the data to be visually appealing is very important. Not only do I want the data to be appealing I also want it to be functional. I would name this tab (this would be the second tab, the first would be the documentation page) Original...

Words: 868 - Pages: 4

#### Mt Excel Gfwuihvl

...Instructions- 1. There are 2 deliverables for this assignment- a) An excel file with the necessary computations. b) A word doc with the corresponding explanations. (For the question involving macro, a separate file could be submitted. However, a single excel file is preferred). 2. Solve each question in a separate sheet in one excel file. 3. You’re required to state all your assumptions in the first sheet of the excel file. These assumptions should be written for all the sub-parts separately, however in the same sheet, wherever applicable. 4. Maintain uniformity in structure and formatting. The assignment would be penalized for lack of hygiene. 5. Name all your sheets appropriately. 6. Naming Convention for the file Name_Roll_No_Section.doc/.xls. Example- Hermione_Granger_91023_G.xls Assignment- i) Use column V to create a new character variable of length 10- ABCDEFGHIJ where AB=Region, CD=Province, E=Product Category, FG=Customer Segment, H=Ship Mode, I=Order Priority, J=Product Container. (You’re free to use your own nomenclature. Specify the same in the assumptions sheet. Eg- Use initial 1(or 2) characters of the corresponding variable.) ii) Summarize the data using a pivot table by region and province. Subsequently, find the province with highest profit per shipment and highest profit per Unit. iii) Create a pivot table where you provide a summary of sales, cost and profit statistics. How will you summarize? State the variables used...

Words: 607 - Pages: 3

#### Personla Experance with Excel

...experience with Microsoft Excel Randall W. Broyles Trident University International From being designed primarily for personal productivity, Microsoft Excel is inherently a personal productivity database meant for individual and the small business. While enterprise database management applications and platforms are meant for much greater and wide-spread use across organizations. Microsoft Excel has progressed from a relative simple calculation program to a more advanced database application through the use of Microsoft Visual Basic and other programming languages that have been integrated into the baseline application. Microsoft Excel has many wonderful features to offer the small business and personal user. Excel is the most widely known for its spreadsheet function, though it has numerous other uses. Spreadsheets are useful to any type of circumstances. For example, a spreadsheet can be used as a personal checkbook, or a professor’s grade book. Invoices and budgets can be prepared with Excel as well. Excel is extremely useful in the decision making process and the Goal Seek command can even be used to return specific results that you are looking for. Another popular function of Excel is the ability to prepare and maintain to produce results that meet a specific criteria. For instance, a certain stock broker might use these commands to return a list of customers who have sold certain stocks over the past two years or more. Lists created in Excel can be converted to...

Words: 495 - Pages: 2

Free Essay

#### Excel Financial Functions

Words: 488 - Pages: 2

Free Essay

#### Prueba 1 Excel

Words: 6379 - Pages: 26

Free Essay

#### Instruccion Excel 2010

Words: 547 - Pages: 3

#### Excel Intermediate Skills

...EXCEL INTERMEDIATE SKILLS Customer’s Name: Institution Affiliation: Date: WORKDAY.INTL Function This is only available in Microsoft Excel 2010. It is a modification of the WORKDAY date function in Microsoft Excel 2007. While the WORKDAY function is useful in cases where the weekend days are predetermined, WORKDAY.INTL is useful in cases where the weekend days are neither Saturday nor Sunday. It is also useful in a location where they are single weekend days. WORKDAY.INTL is useful in finding the date that a project is ending or starting given the duration the project will take (Microsoft, 2012). This function automatically omits the days regarded as weekend days and the statutory holidays. Unlike the WORKDAY function, it allows the user to spell out which days are weekdays and which days are weekend. In fact, the user is at liberty to do away with all the weekends. It does not automatically set two days as weekend days without the user’s consent. A user can employ WORKDAY.INTL in calculating several applications. These include calculating the due date of an invoice; the probable delivery dates for materials or goods; the date a project is ending if the starting date and number of workdays of the same project is specified; and the date a project is starting given the end date of the same project and the number of workdays (Microsoft, 2012). Syntax and Argument Syntax is the layout of a function. It comprises of the name of the function, bracket, and its arguments. The...

Words: 617 - Pages: 3

#### Acct 370 Excel Project

Words: 280 - Pages: 2

#### When to Use Access and/or Excel

...James Geffen Kelly Edwards CG1101 10458 19 October 2011 When To Use Access and/or Excel Microsoft Access and Excel Versions 2010 are very powerful tools designed to improve your productivity as your needs change. With Microsoft Excel it is easy to calculate, design and manipulate data quickly. Access 2010 is the tool to use when your needs require a more fluid relational database that becomes more of an interactive system. When should you use Microsoft’s Excel platform? When you want and need to manipulate numbers. The program allows for you to create sophisticated tables, charts and formulas. Excel is a fairly easy program for even the computer novice to use. Version 2010 has an easy to use menu and online help system that enables you to get the job done quickly and efficiently. For example Excel is a great program to use to create spreadsheets for use in determining sales, employee time sheets and annual reports. Cost-benefit analysis is another great way to utilize the features found in Excel. Excel can be used by more of your friends and co-workers simply because more people are familiar with Excel rather than Access. Microsoft’s Access 2010 is a powerful relational database that is part of the office suite of applications. When should you consider using Access? If users need to add data on a regular basis, have a need to create reports and with Access you have more ways to query data than ever before. Microsoft Access 2010 would be a better choice...

Words: 432 - Pages: 2