Ms-office Excel Notes(by meher)
Posted by meherchilakalapudi on January 1, 2009
Excel : Microsoft Excel is an electronic spreadsheet program. It can perform several functions on text and numericals simultaneously.
To Get into Ms Excel
Steps : Click on Start button – programs – Microsoft Excel
Or
Click on start button – Run – Type excel – click ok button
Window components / Window Screen Elements
Control Menu box – It controls the size and position of the excel window. It is located in the left corner of the screen.
Title bar – It is located at the top of the screen. It displays the name of the application and the active Bookname[Book1].
Menu bar – It is just below the title bar, where the list of various menus are displayed.
Scroll bars – These are placed on the right and bottom of the window. It is used to move up, down, left and right in the window.
Minimize Button – This is located at the left side of the three buttons at top right corner. It reduces the size of the application window to a button on taskbar.
Short cut command : Alt + space bar + n
Maximize Restore Button – It is located besides the minimize button, controls the size of the application window.
Short cut command : Alt + space bar + x
Close Button – It is on the top right corner of the window and this is used to close the current Book.
Short cut command : Alt + F4
Standard Toolbar – It contain icons which represent commands present in the menu bar. It is below the menu bar.
Dialogue box – These are used to collect information from the user or to present information to the user.
Format toolbar– To change the appearance of the document is called formatting. It is below the standard toolbar.
Status bar – This is located at the bottom of default sheets. It displays the status of the document. It also displays like count, sum, avg, min, max functions result without using any formulas.
Worksheet : The term worksheet refers to the row & column matrix on which you work upon. In otherwords, the area on which work done. It is also called as Spread sheet. A worksheet can contain 256 columns named A through IV and 65,536 Rows in a worksheet.
Work book : The term workbook will refer to the book of pages. The workbook can contain worksheets, chart sheets, calculations or macros.
Note : Many worksheets can be stored in a workbook. A workbook is saved with the extension of .xls
Workspace : A group of workbooks can be opened in one step by creating a workspace file. It can saves information about all open workbooks. When the workspace is opened excel automatically opens all the existing workbooks in it. However, workbooks must be saved individually.
Note : A workspace is saved with the extension of .xlw
Cell : Intersection of Row and a column forms a cell.
Cell references are the combination of column letter and row number.
For example, the upper-left cell of a worksheet is A1.
Active cell : A cell is active when the border is highlighted in black color. When you enter information, the information is stored in the active cell and the text will displayed in two areas.
1. active cell 2. formula bar
Name box : By naming here, we can reference a particular or group of cells. It is leftmost of the formular bar.
The Cancel and Enter buttons (û,ü). These buttons are visible at the edit mode only (data entry). û is for cancel(or press backspace button in key board) ü is for enter (or press enter key in key board)
Workbook : 1. Workbook is a collection of worksheets.
2. Save option from file menu is selected to save.
3. Saved with a extension .xls
4.Worksheets within workbook are saved automatically.
Workspace : Workspace is a collection of workbooks
Save workspace option from file menu is selected to save.
Saved with extension .xlw
Workbooks within workspace have to be saved individually.
Creating a new workbook : To create a new blank Book.
Steps : 1. open file menu
2. click new command
3. In the dialog box select workbook
4. click ok button
Creating a new workspace : To store all the opened books in one file. (for eg: comparing employee details file, department details file, admin details file etc.,)
Steps : 1. open all the workbooks that are to be opened as a group. (window – arrange command)
2. resize all the workbooks to make them appear within the screen.
Creating a duplicate file : Here, instead of data, we are changing the file name to create a duplicate file.
Steps : 1. open file menu
2. click save As command
3. In the dialog box give a filename in the filename box
4. click save button
Note : While creating a duplicate file, we have to change the file name that was specified previously (if we are saving in the same location) else with the same name only we can store in different location.
Opening an existing worksheet : To open already existed/saved files.
Steps : 1. open file menu
2. click open command
3. choose the file from the path where you saved
4. click open button
Moving or copying worksheet : To move or copy a worksheet from its original location.
Steps : 1. click on edit menu
2. select the option move or copy sheet or right click on the sheet tab of the sheet to be moved or copied
3. select move or copy option from the pop-up menu
4. click ok button
Note : Worksheets can be moved within the same workbook or to another book.
To move to another workbook, the bookname in the To Book box must be same as the active bookname. To move to the another workbook, select the (new book) option from To book box.
To create a copy of worksheet select the create copy option and click ok button.
Inserting a worksheet : To insert a new worksheet
Steps : 1. open insert menu
2. select the worksheet option
or 1. right click on the sheet tab
2. select insert option
3. choose worksheet option
4. click ok button
Deleting a worksheet : When a worksheet is no longer required, it can be deleted permanently. We can’t undo 2 get back again like deleting text or images.
Steps : 1. open edit menu
2. select delete sheet option
or 1. right click on the sheet tab
2. choose delete option
Renaming a worksheet : Every sheet is given a name by default. It may be necessary to change this name
Steps : 1. Right click on the sheet tab
2. choose rename option
3. assign a name
4. press enter key to confirm
Saving a worksheet :
Saving a book means all the inside worksheets are saved automatically. To save a workbook,
Steps : 1. Open file menu
2. choose save command
3. type an appropriate name and click save button
Note : Excel saves the workbook with the extension .xls
Saving a worksheet in web page format (html) :
Saving a book means all the inside worksheets are saved automatically. To save a workbook in web page format
Steps : 1. Open file menu
2. choose save as web page command
3. type an appropriate name and click save button
Note : Here, excel saves the workbook with the extension of .htm
We can view the file in Internet Explorer.
Saving a workspace :
When a collection of workbooks have to be manipulated simultaneously, it is necessary to save them in a single workspace.
Steps : 1. Open file menu
2. Select save workspace command
3. The save dialogue box appears
4. type a name to save the workspace
5. click on save button
Note : The workspace file is saved with the extension .xlw.
Closing a workbook : When work on a sheet is completed, it is necessary to close workbook.
Steps : 1. open file menu
2. choose close command
or 1. close x button on the menu bar
Note : 1. Before closing, workbook is has to be saved, if not it displays a message box enquiring if the changes in the workbook have to be saved or not.
2. Closing a workbook implies the quitting the current workbook but not application.
Exiting Ms Excel :
Steps : 1. open file menu
2. choose exit command
or 1. select the option x on the title bar
Inserting cells : Sometimes cells need to be inserted in an already created datatable.
Eg : Roll # Name Gender
1 james Male
2 Arpit Male
3 Careena Female
4 Varsha Female
Now to insert a cell in between James and Arpit,
Steps : 1. open insert menu
2. select cells command
3. choose shift cells down or appropriate option
4. click ok button
Inserting rows : To insert a row in between roll nos. 2 and 3
Steps : 1. open insert menu
2. click row command
or 1. select the 3rd row and right click with mouse
2. choose insert option
Inserting column : To insert a column in between name and gender
Steps : 1. open insert menu
2. select column command
or 1. select the gender column and right click with mouse
2. choose insert option
Inserting Picture : Pictures can be inserted from ClipArt or from existing files
Steps : 1. open insert menu
2. click picture command
3. select clip art option
4. The insert dialog box will appear
5. From the picture tab choose any category
6. Select a picture and click insert clip option to insert the picture.
Inserting Charts : Charts are graphical representations of tabular data. They can be inserted into spreadsheets. Excel provides several types of charts which can be created using the data provided by the user. Charts can be inserted into two ways.
Steps : 1. open insert menu
2. choose chart option
or 1. by clicking on chart wizard icon on standard toolbar
By choosing any way from the above options, the chart wizard guides the user through a series of steps. All those steps are necessary for creation of chart.
Steps to create a chart :
1. Choose one of the type of chart from the list of standard charts available.
2. In the second step specify the data range. The data range is the area of tabular data, based on chart is being made. It can be selected directly from the worksheet, or starting and ending cell references can be specified separated by a colon.
3. The third step enables the user to show the legend and decide its placement. A legend is the box that identifies the patterns or colors assigned to the data sheet in a chart. It can be placed at the bottom, top, corner, right or left of the chart.
4. The final step enables the user to place the chart either as a new sheet or in any location of the existing worksheet.
Cell formatting: The cell formatting command contains several formats to the selected cells. The number, alignment, font, boarder, patterns and protection of cell contents can be manipulated using the cells option from format menu.
Number tab : It specifies the manner in which numerical data will be displayed. Currency and Accounting operations offer specialized formatting for monetary value. Date and time options display the date and time in different formats. Numbers can also be represented as percentages and fractions.
Alignment tab : It changes the arrangement of cell contents, varying in between vertical or horizontal as desired. Orientation option is used to change the direction of text in a cell. However, if any other alignment options are selected rotation options are not available.
Font tab : It allows specifications for the text in the cells. The font, font style, size underline style, color and effects can all be mentioned here.
Border tab : It allows the user to apply presets, border, line styles and colors to the cells. Presets or borders are used to apply borders to the cells that are selected. Line style are used to specify the line size and style for a border. Different styles are available such as dotted, dash, thickline, double line etc. color option provides the colour pallete from which any colour may be chosen.
Note : The entire border can be removed by selecting the option none from presets or certain border areas can be added or removed from the border models that are available.
Patterns tab : The patterns tab allows the user to choose the color and patterns for the cells. A background color has to be selected in the color box and then a pattern selected in the pattern box to format the selection with color patterns. To apply the patterns, the cell or the data range has to be selected.
Protection tab : The protection tab protects the data by locking the cells or hiding the formulae. However, this has effect unless the worksheet itself is protected.
To protect the worksheet, click on tools menu, protection and select the option protect sheet.
Formatting Rows and Columns : It means their height and width can be altered to fit in data.
Steps : 1. Click on format menu
2. select rows command
3. select height option
4. mention row height and click ok button
to change width of the column
1. click on format menu
2. select column command
3. select width option
4. mention column width and click ok button
Auto format : These are various built-in-styles or templates that can be applied to cell ranges. To apply autoformats to an area,
Steps : 1. select the cells in that area
2. click on format menu, autoformat option
3. Related dialog box appears
4. select any one of the available format and click ok button
Note : The autoformat can be applied to the datarange only when it is selected.
Conditional formatting : This option applies formats to selected cells that meet the criteria specified by the user. To apply conditional formatting
Steps : 1. select the range of cells
2. click on format menu choose the conditional formatting
3. In condition 1 fill the condtion
4. Now click the format button.
5. from the font tab, choose a color for cell-shading
6. click on add>> button to insert another condition.
7. then click on format button and choose another colour and
8. click on ok button.
9. clicking on the delete button, will enable the user to delete the conditions that are specified. Such conditional formatting can also be applied to text.
Goal seek : It is a tool with the help of which numerical data in a worksheet can be altered, depending on desired change in result.
Note : It always done on a cell containing a formula. The Goal seek status box gives the Goal seek solution.
Eg: create a list of student details with total, average and rank. Let us assume that your total marks are add upto 450, and you desire to get 500. Thus your target total is 500. If you want to know in which subject you must get more marks to get a total of 500, you can perform goal seek.
Steps : 1. open tools menu
2. select the option goal seek
3. In set cell box, give the cell reference of your total marks cell by clicking on it.
4. In the To value box, type 500.
5. In the By changing cell box, give the cell reference of the cell whose marks should be increased, by clicking on it.
6. Click on the ok button to finalise the changes.
Auditing : It is the tool used to find the precedents and dependents of given numerical data.
Eg : The precedents of total mark is each subject mark;
And the dependents of each subject mark is the total mark.
Steps : 1. click tools menu
2. select auditing option
3. choose trace precedents or trace dependents
4. If any error exists in your list, click on trace error.
5. To remove all arrows that exist, click remove all arrows option
Sort : Sorting is a process of arranging data in ascending or descending order.
Steps : 1. open data menu
2. select sort command
3. select the column to sort ascending or descending and click ok button.
Note : It has to be specified whether the list has a header row or not.
Clicking on options button, opens the sort options dialog box, where the sort order is to be chosen. Sorting can also be done row wise by changing orientation to sort left to right. Case sensitive sorting is done to sort capitalized data.
Filter : It is a way to find and display only specific information on a database. A filter is used to select records that meet a specific criteria and temporarily hide all other data.
Autofilter : It is the quickest way to filter a database. To use autofilter,
Steps : 1. open data menu
2. click on filter command
3. choose autofilter option
4. excel reads every record in the database and creates a filter criteria list for each field.
5. clicking on the arrow that appears beside a field name, will display that fields criteria list.
Formulas :
Note : In Excel every formula begins with equalto (=) sign
Round : This function rounds a decimal number to a specified number of digits.
Syn : round(number, num-digits)
|
8.99999 |
9 |
=round(a2,2) |
|
45.5555 |
45.56 |
|
|
23.56566 |
23.57 |
|
Sum : This function is used to add two or more numbers.
Syn : sum(number1,number2,number3… numbern)
Or we can use å from the standard toolbar.
or sum(number1 : numbern)
product : This function is used to multiply two or more numbers.
Syntax : product(number1, number2,….number n)
Eg : product (2,5)
mod : This function returns the remainder after a number is divided by a divisor.
Syntax : mod(number, divisor)
Eg : mod(10,3) o/p – 1
sqrt : This function returns the square root of a number.
Syntax : sqrt(number)
Eg : sqrt(9) o/p – 3
ceiling : This function rounds a number up to the nearest integer or nearest multiple.
Syntax : ceiling(number, significance)
Eg : ceiling(5.1,1) o/p = 6
floor : This function rounds a number down to the nearest multiple
Syntax : floor(number, significance)
Eg : floor(5.1,1) o/p – 5
average : This function returns the average of its arguments, which may be numbers or names that contain numbers.
Syntax : =average(a:a20)
Eg : =average(2,18,20,45,23,89)
Max : This function returns the largest value in a set of numbers. It ignores logical and text values and can only work on numerical values.
Syntax : max(number1,number2, … number n)
Eg : max(23,56,2,569)
Min : This function returns the smallest value in a set of numbers. It ignores logical and text Error! Not a valid link.
Syntax : min(number1,number2, … number n)
Eg : min(5,34,67,34,3,)
Text functions :
Trim : This function removes all proceeding and trailing spaces in a text, except for single space between words
Syntax : trim(text)
Eg: trim(“ram hari friends”)
Output(o/p) = ram hari friends
concatenate : This function joins several text strings into one text string without blank space.
Syntax : concatenate(“sachin tendulkar”)
o/p = sachintendulkar
len : Returns the number of characters in a text string. Spaces are also counted as characters.
Syn : len(“ramhari”)
o/p=7
replace : replaces part of a text string with different text string.
Syn : replace(old_text, start_num,num_chars,new_text)
Old_text=”koyeli”
Start_num=3
Num_chars=2
New_text=”ey”
Upper : converts text string to capital letters
Syn : upper(text)
Eg: upper(“ram”)
o/p=RAM
lower : converts a text string to small letters.
Syn : lower(text)
Eg : lower(“RAM”)
o/p = ram
