ICAI

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

ICAI :

ICAI INFORMATION TECHNOLOGY CENTRE

PERSONAL DETAILS:

Name : Akash Kumar Bhetwal Regd No. : IPCC/ITT/FRO0002860 Batch No. : 113 Roll No. : 02 Branch : Noida Branch of CIRC of Code No.1027 Address : WA-12A,Shakarpur,Delhi PERSONAL DETAILS

PowerPoint Presentation:

ITT PROJECT MS-EXCEL 2007

ACKNOWLEDGEMENT:

ACKNOWLEDGEMENT I would like to take this opportunity to convey thanks to my mentors Mr. Anoj Kumar and Mr. Om Prakash for their valuable guidance , support and encouragement for this project. The project on MS-EXCEL 2007 has truly been a great learning experience to me.

PowerPoint Presentation:

MS-EXCEL 2007

MS-EXCEL 2007:

MS-EXCEL 2007 A Spreadsheet is a computer application that simulates a paper worksheet. It displays multiple cells that together make up a grid consisting of rows and columns, each cell containing either alphanumeric text or numeric values. MS-EXCEL 2007 is a spreadsheet application. It also contains together several worksheets together known as WORKBOOK.

Blank MS-EXCEL 2007 Spreadsheet :

Blank MS-EXCEL 2007 Spreadsheet

DETAILS OF SPREADSHEET:

DETAILS OF SPREADSHEET

DETAILS OF SPREADSHEET:

DETAILS OF SPREADSHEET ACTIVE CELL : Cell can be identified with the black outline. Data is always entered into the active cell. FORMULA BAR : This area displays the contents of the active cell .It can also be used for entering or editing data and formulas. ROW NUMBER : Identifies a horizontal row in the worksheet. COLUMN LETTER : Identifies a vertical column in the worksheet. NAME BOX : Displays the cell reference or the name of the active cell

PowerPoint Presentation:

6) SHEET TAB : Switching between worksheets in an Excel 2007 file is done by clicking on the sheet tab at the bottom of the screen. 7) QUICK ACCESS TOOLBAR : Allows user to add frequently used commands. 8) OFFICE BUTTON : It displays a drop down menu containing a number of options such as open , save and print.

PowerPoint Presentation:

OFFICE BUTTON

OFFICE BUTTON:

OFFICE BUTTON Located on the top-left of the window , replaces the File menu and provides access to functionality common across all Office applications, including opening , saving , printing and sharing a file. It can also close the application . Users can also choose color schemes for the interface.

RIBBON:

RIBBON

RIBBON:

RIBBON The Ribbon, a panel that houses the command buttons and icons, organizes commands as a set of tabs, each grouping relevant commands. It is designed to make the features of the application more discoverable and accessible with fewer mouse clicks .It is not possible to remove or modify it.

Shortcut keys:

Shortcut keys Show a shortcut menu : SHIFT + F10 Make the menu bar active : F10 or ALT Show the program icon menu : ALT + SPACEBAR Select the next or previous command on the office button submenu : DOWN ARROW OR UP ARROW Select the menu to the left or right or with a submenu visible, switch between the main menu and the submenu : LEFT ARROW OR RIGHT ARROW

PowerPoint Presentation:

Select the first or last command on the menu or submenu : HOME OR END Close the visible menu and submenu at the same time : ALT Close the visible menu or with a submenu visible, close the submenu only : ESC

PDF Format :

PDF Format PDF is a common format for sharing documents. PDF is a fixed-Layout electronic file format that when the file is viewed online or printed , it retains exactly the format that is intended and that data in the file cannot easily be changed The file saved as PDF cannot be directly changed.

CELL REFERENCING:

CELL REFERENCING Intersection of a column and row is called a cell. Each cell on the spreadsheet has a cell address that is the column letter and the row number. A reference identifies a cell or a range of cells on a worksheet and tells MS-Excel where to look for the values or data the user wants to use in a formula.

PowerPoint Presentation:

With references ,the user can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. The user can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links.

TYPES OF CELL REFERENCING:

TYPES OF CELL REFERENCING RELATIVE CELL REFERENCING ABSOLUTE CELL REFERENCING MIXED CELL REFERENCING

RELATIVE CELL REFERENCING :

RELATIVE CELL REFERENCING

PowerPoint Presentation:

RELATIVE CELL REFERENCING This is the most widely used type of cell reference in formulas. Relative cell references are basic cell references that adjust and change when copied or when using Autofill. EXAMPLE: =SUM(E4:E7) changes to =SUM(F4:F7) when copied across to the next cell.

PowerPoint Presentation:

ABSOLUTE CELL REFERENCING When a formula or function is copied or moved to another location, any cell references in the formula or function get adjusted as well. However, there are some situations where a cell reference inside a formula must ALWAYS refer to the same cell.

PowerPoint Presentation:

If salary of people A to D is to be apportioned into savings on the percentage decided, Then we need to keep the percentage same (absolute) and the salary needs to change for every person (relative). Hence when entering the formula we need to lock the cell address of the percentage of savings. A cell can be locked by using the “$” symbol in the formula . ( See the fig.1 .1)

Fig(1.1):

Fig(1.1)

PowerPoint Presentation:

In the above formula we can see that the cell E5 has been locked which will keep the cell E6 as an absolute cell reference. Now once you copy the formula below the respective salaries will get multiplied to the cell E6. The cell E6 does not change once you copy the formula below. ( See fig.1.2)

Fig (1.2):

Fig (1.2)

PowerPoint Presentation:

In a given case if you need to provide the savings of the highest salary to the fixed percentage you need to do an absolute cell referencing i.e. both the cells needs to be fixed. See fig.(1.3) Now where ever you copy this formula you will get the same value in all the cells. Its primarily because both the cells have been made an absolute reference. See fig. (1.4)

Fig (1.3):

Fig (1.3)

Fig (1.4):

Fig (1.4)

PowerPoint Presentation:

The logic and concept behind making a cell absolute and relative is the row and the column address in the cell. Any row or column can be made absolute and relative. For instance you want to keep the column same but need to change the rows put a “$” sign before the column name only. See fig (1.5)

Fig (1.5):

Fig (1.5)

PowerPoint Presentation:

In the above picture you can see the “$” sign is only stated. Now once the formula is copied in the next column “F” as well the values do not change. It happens because the column reference has been made a absolute. See fig. (1.6)

Fig. (1.6):

Fig. (1.6)

PowerPoint Presentation:

MIXED CELL REFERENCING To create a mixed reference, make part of a cell address absolute and part relative, by locking in either the column or row. Use mixed references to copy a formula down and across and to have a reference change relatively in one direction but not in the other. For example, For $E4, the column letter is fixed while the row number is allowed to change when copied to other cells. For F$6, the row number is fixed while the column letter changes. See fig(2.1)

Fig (2.1):

Fig (2.1)

FORMULAS:

FORMULAS When using a formula in the spreadsheet (referencing other cells), the sum will automatically change as other cell values referenced in the formula change. This feature is very valuable when editing or adding information to the worksheet Generally begins with an equal (=) sign followed by combination of numbers ,texts and cell references.

FINANCIAL FUNCTIONS:

FINANCIAL FUNCTIONS Fv : Future value; what a loan or investment will be worth at a future time when all payments have been made. Nper : Number of periods; the number of months, years ,days or other periods for an investment or loan Pmt : Payment; the amount periodically received from an investment or are paid on a loan.

PowerPoint Presentation:

Pv : Present value; the initial value of an investment or loan Rate : The interest rate on a loan; the discount or interest rate on an investment

MATHEMATICAL FUNCTIONS:

MATHEMATICAL FUNCTIONS ABS : returns the absolute value of a number COUNTIF : counts the number of cells within a range GCD : returns the greatest common divisor of two or more integers MOD : returns the remainder after number is divided by divisor. The result has the same sign as divisor.

PowerPoint Presentation:

MROUND : returns a number rounded to the desired multiple POWER : returns the result of a number raised to a power PRODUCT : multiplies all the numbers given as arguments and returns the product RAND : returns an evenly distributed random number greater than or equal to 0 and less than 1 RAND BETWEEN : returns a random number between the numbers the user specify

PowerPoint Presentation:

SIGN : determines the sign of a number SQRT : returns a positive square root SUBTOTAL : returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command ( Data menu). Once the subtotal is created, the user can modify it by editing the SUBTOTAL function. SUMIF : adds the cell by a specified criterion

STATISTICAL FUNCTION:

STATISTICAL FUNCTION AVEDEV : returns the average of the absolute deviations of data points from their mean. AVERAGE : returns the average of the arguments COUNT : counts the number of cells that contain numbers and also numbers within the list of arguments.

PowerPoint Presentation:

MAX : returns the largest value in a set of values MIN : returns the smallest number in a set of values MEDIAN : returns the median of the given numbers. The median is the number in the middle of the set of numbers MODE : returns the most frequently occurring or repetitive value in an array or range of data

FORMULA ERROR MESSAGES:

FORMULA ERROR MESSAGES #DIV/0! : contains a blank cell, which Excel considers to be 0 value #NAME? : invalid function name or cell reference in the formula #VALUE : contains data such as text that cannot be used in a formula ##### : column not wide enough to display the results of the calculation #REF : refers to a cell that is not valid, such as a cell that was deleted

SPLITTING PANES:

SPLITTING PANES Splitting panes is another feature that can help handle a large worksheet. By using the Split Panes feature located under ‘WINDOW’ in the VIEW tab , different areas of the worksheet can be viewed simultaneously. Splitting sheets into panes offers synchronized scrolling capability.

PowerPoint Presentation:

From the windows ribbon in the VIEW tab, chose the SPLIT command to split the window into vertical and horizontal panes simultaneously. The worksheet split into 4 panes

PIVOT TABLE:

PIVOT TABLE Pivot tables are a powerful tool for data analysis. It summarizes the columns of information in a database in relationship to each other. A Pivot table is away to present information in a report format. The idea is that user can click drop down lists and change the data that is being displayed. Pivot tables are easier to grasp when user sees them in action.

HOW TO CREATE THE PIVOT TABLE?:

HOW TO CREATE THE PIVOT TABLE? Open your original spreadsheet and remove any blank rows or columns Make sure each column has a heading, as it will be carried over to the Field List Make sure your cells are properly formatted for their data type Highlight your data range .Click the Insert tab Select the Pivot Table button from the Tables group. Select PivotTable from the list. (See fig 3.1)

Fig(3.1):

Fig(3.1)

PowerPoint Presentation:

8. Double-check your Table/Range: value. 9. Select the radio button for New Worksheet . 10. Click OK . ( See fig. 3.2 )

(Fig 3.2):

(Fig 3.2)

MACRO:

MACRO A Macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever the user need to perform the task. The user must decide which Excel task to record. Once the user starts recording, everything the user done is recorded, even the errors.

PowerPoint Presentation:

Can be viewed by selecting on the Developer tab in the code group – RECORD MACROS-USE RELATIVE REFERENCES

PowerPoint Presentation:

Record the Macro When a macro is recorded, Excel stores information about each step as the user perform a series of commands. Perform the action to make into a macro . All of the actions will be recorded as part of the macro so try not to make any errors. If user makes a major error and wish to start over, click the STOP RECORDING icon (See Fig. 4.1)

(Fig 4.1):

(Fig 4.1)

PowerPoint Presentation:

RUNNING THE MACROS The only way to access the macro is to go to Developer Tab in the Code Group – MACROS-select your macro – RUN.) . Click the Options button to tell Excel how user would like to access the Macro A new dialog box will come up with the name of your macro and description. ( See fig 4.2)

(Fig 4.2):

(Fig 4.2)

authorStream Live Help