...ADVANCED EXCEL TRAINING Quality Management Objective To provide the participants with an understanding of the advanced features of MS Excel Today is 7/22/2011 2 Advanced ® 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 ...
Words: 1579 - Pages: 7
...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
...Using Microsoft Excel Getting Started Using Microsoft Excel About Excel What is a Spreadsheet? Microsoft Excel is a program that’s used for creating spreadsheets. So what is a spreadsheet? Before personal computers were common, “spreadsheet” referred to large sheets of lined paper, which were used by people in various businesses to record facts and figures in rows and columns, and then make calculations based on the information. When personal computers first began appearing, one of the first applications was a program released in 1979 called VisiCalc. It was used as a tool for performing spreadsheet style calculations that would have been to difficult to do on a calculator. The program quickly became so popular that people began buying personal computers for their businesses just so they could use VisiCalc. VisiCalc Since then, many other spreadsheet programs have been popular over the years, such as Quattro Pro and Lotus 123. Microsoft Excel was first released in 1985 with newer versions being released every couple of years. The most recent version is Excel 2003 (version 11). © Stephen O’Neil 2005 Page 1 Of 17 www.oneil.com.au/pc Using Microsoft Excel Getting Started How Do Spreadsheets Work? Computer spreadsheets are based on their old paper formats. A spreadsheet on a computer uses rows and columns to record information such as text and numbers, such as the example below. Graduates from University 1996 1997 1998 1999 2000 2001 Total Nursing...
Words: 4397 - Pages: 18
...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
...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
...↑ 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
...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
...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
...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
...How to use Microsoft Excel for financial calculations? Microsoft Office Excel is a great computer program that is widely used throughout the financial industry. Excel is an invaluable tool in Finance and Accounting. Of course professionals also use special financial calculators. But a financial calculator can be expensive for students. It is not intuitive to use and unless they become investment bankers or realtors, most students will never use it after finishing the financial course. Fortunately, it is very easy to create a financial calculator for free if you have Excel on your computer. In fact, an Excel calculator can do much more than a dedicated financial calculator. Another advantage is the fact that if you learned how to use financial functions of Excel you will be able also to use a special financial calculator should the need arise. Below is a short introduction of how to start using MS Excel for financial calculations. Inserting Functions Excel comes with a wide array of functions that can easily be inserted into a spreadsheet. Adding a function is as easy as clicking on the "Insert Function" button on the top tool bar. Insert Function Button If you don't see the "Insert Function" button on your tool bar, you can easily add it by clicking: Tools > Customize > Commands > Find and click on Insert in the left-hand scroll-down menu. Then, find "Insert Function" in the right-hand scroll-down menu and click and drag it to your tool bar. Clicking on the "Insert...
Words: 488 - Pages: 2
...Excel Tutorial to Improve Your Efficiency (2007 Version) Introduction My purpose with this Excel tutorial is to illustrate some Excel tips that will dramatically improve your efficiency. I make no attempt to be as encyclopedic as some of the 800-page Excel manuals available. I concentrate on common tasks, not every last thing that can be done in Excel. Also, I presume that you have some Excel knowledge. For example, I assume you know about rows and columns, values, labels, and formulas, relative and absolute addresses, and other basic Excel elements. If you know virtually nothing about Excel, you probably ought to work through an “Excel for Dummies” book and then work through this tutorial. The style of this tutorial should be easy to follow. Main topics appear in bold black type. Specific direction headings are in yellow, and these are followed by detailed directions in red. Additional comments about the directions appear in blue. Then there are “Try it!” exercises in green. These “Try it!” exercises are a key feature of this tutorial. I have embedded numerous sample Excel spreadsheets so that you can try out the directions right away—without switching into Excel. When you double-click on one of these spreadsheets, you launch Excel, and the spreadsheet “comes alive.” The menus and toolbars even change to those for Excel. By clicking outside one of these spreadsheets, you’re back in Word. The easiest way to maneuver around this tutorial is to switch to outline view. To do...
Words: 6379 - Pages: 26
...estructurado Excel 2010? En Excel, cada archivo se denomina Libro, ya que está compuesto por múltiples Hoja de cálculo y otros elementos como gráficos y hojas de macros. Cada hoja de cálculo es una cuadrícula que está conformada por Filas y Columnas. Cada cuadro de esta cuadrícula se denomina Celda y su nombre esta dado por la columna y la fila donde se ubica. Ej: La celda B3 se encuentra destacada en esta imagen. Muchas de las funciones que realiza este programa pueden resolverse con una calculadora; sin embargo, la posibilidad de desplegar la información en una hoja de trabajo en pantalla nos permite comparar y analizar datos, establecer relaciones, realizar proyecciones y estimaciones, además de generar mayor impacto visual y facilitar la comprensión de la información gracias a la presentación gráfica de los datos. Al iniciar Excel 2010 accedemos a un nuevo libro que contiene, de forma predeterminada, tres hojas de trabajo, aunque esta cantidad de hojas se puede modificar, eliminando las que no necesitamos o agregando hojas adicionales. Las celdas de la hoja de cálculo son unidades de información independientes entre sí, en las que es posible escribir texto, números, fechas, etcétera. Para realizar diferentes tipos de operaciones podemos relacionar a las celdas utilizando fórmulas y funciones a través de operadores aritméticos, tales como =, +, -, etcétera y operadores lógicos o de comparación, como por ejemplo, >, =, entre otros. Elementos de Excel 2010 A continuacion...
Words: 547 - Pages: 3
...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 Click Link Below To Buy: http://hwcampus.com/shop/acct-370-excel-project/ PART 1: Excel Project: Part V Instructions Common Sized Statements and Trend Analysis on WALMART Stores Inc. The tenth tab in your Financial Statement Analysis Template must be labeled Common Sized Balance SheetsThe eleventh tab in your Financial Statement Analysis Template must be labeled Common Sized Income StatementsThe twelfth tab in your Financial Statement Analysis Template must be labeled Horizontal Analysis of Balance SheetsThe thirteenth tab in your Financial Statement Analysis Template must be labeled Horizontal Analysis of Income StatementsInclude the latest three years for the common sized (vertical analysis) and horizontal analysis in Tabs 10–13. *PLEASE WORK OFF OF ATTACHED EXCEL SPREADSHEET. When you open the document, you will see that there are already 9 tabs. Follow the instructions above. You will need to add 4 more tabs labeled appropriately with the above titles (see instructions). Please save the work and send it back to me. PART 2: - Use WORD to complete this part COMPARITIVE ANALYSIS REPORT. - WALMART Examine common sized financial statements and ratios for the company you selected for the Excel project and two of its competitors. Write a 1000-word report in current APA format. Include the following in your report: Compare and contrast the selected company, its competitors, and industry averages Identify the selected company’s strengths...
Words: 280 - Pages: 2
...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
