EXCEL with multiple sheets PDF trainin


Télécharger EXCEL with multiple sheets PDF trainin

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


 

Computer Center, C-61 IPCL Township, Nagothane – 402 125 MICROSOFT EXCEL

Microsoft Excel is used to prepare a spreadsheet.  A spreadsheet is the computer equivalent of a paper ledger sheet. It consists of a grid made from columns and rows. Intersection of one column and one row is called as a cell. It is an environment that can make number manipulation easy.  Data is entered in a cell.  This data can be operated upon using mathematical formulae to get required results.

Once a formula is entered using data entered in the cells, we can alter the data to get different result.  In such case we need not give formula once again, the result is altered automatically.

STARTING MS EXCEL—

Start --- Programs --- MS Excel

The window in MS Excel application shows similar toolbars like MS Word with some different buttons carrying out commands that are required specifically for excel sheet.  The toolbars are as follows:

1.    Title bar

2.    Menu bar

3.    Standard toolbar

4.    Formatting toolbar

5.    Formula bar – This bar shows Name box on its left side.  This name box shows cell reference or cell address of the active cell.

6.    Workarea – It shows gridlines i.e. the workarea is divided in rows and columns. There are 256 columns and 65536 rows in a sheet.

 The dark border of the selected cell is called as a Cell pointer.  A cell can contain text, numerical data or formula.

7.    Horizontal scroll bar – It shows navigation buttons and sheet tabs on its left side.

8.    Drawing toolbar

9.    Status bar

bar

MOVING BETWEEN CELLS:

1.    To move horizontally – press tab key.

2.    To move vertically – press enter key.

3.    To move horizontally in reverse direction – press shift + tab key simultaneously.

4.    To move vertically in reverse direction – press shift + enter key simultaneously.

5.    To go to same row last column – ctrl +

6.    To go to same column last row – ctrl + 7. To go to same row first column – press Home key

8.    To go to A1 cell – ctrl + Home key.

9.    To go to next workbook -- ctrl+tab key.

go to last column of data entered – double click the right border of active cell.

SELECTION:

1.    Single cell – Click on cell.

2.    A cell range – Select a cell and drag.

3.    Entire column – Click on column heading.

4.    Entire row – Click on row number.

5.    Entire sheet – Click on blank square above row no.1.

6.    Cells at scattered locations – ctrl + click required cells.

7.    A large data (e.g. from A1 to F3000) – click A1 cell, go to name box type A3000, press shift and press enter.

8.    To select all the cells until Excel hits a blank row and column, select any cell in the range, and then press ctrl+?.

ENTERING SAME DATA ON MULTIPLE SHEETS:

1.    Select required sheets by clicking on sheet tabs.

2.    Enter data on one sheet.

3.    Press enter.  Same data is entered on all selected sheets.

Note: To select adjacent cells or sheets, click first cell or sheet as the case may be, press shift key and click last cell/sheet.  And to select nonadjacent sheets or cells, press ctrl key and click required cells or sheets.

OTHER WAY TO ENTER COMMON DATA ON MULTIPLE SHEETS;

You can use fill option to copy cell contents across several sheets. Enter the data on the first sheet. Select the range you wish to fill, & then select the sheets you wish to copy the data to by pressing ctrl key.

Choose Edit menu ----- Fill ------ Across worksheets.

In the box, choose All, Formats or contents-------- click ok.

PASTE SPECIAL: (To copy only formula or value from cell which contains both)

1.    Select cell/cell range, give command to copy

2.    Select destination cell, go to edit menu, click  ‘paste special’ option.

3.    Click value under ‘paste’ option.

4.    Click OK.

LINKING CELLS OR FILES:

1.    Select a cell, Give command to copy.

2.    Select another (destination cell), go to Edit menu, click ‘paste special’.

3.    Click ‘Paste link’.

4.    Changes made in source cell are reflected in destination cell.

INCREMENTING VALUES BY A SERIES:

1.    Fill data in one cell.

2.    Select that cell, Go to Edit menu.

3.    Select Fill option, Click on Series.

4.    Click columns option, if data is to be filled vertically.

5.    Give step value and stop value.

6.    Click OK.

 

AUTOSUM:

To find out sum total of a column, click blank cell at the bottom of the data, click on ‘Autosum’ tool button on standard toolbar.  Press enter.

NOTE:  To find totals of columns and rows at a time, select entire data with a blank row and a column at the end and then click on ‘Autosum’ button.  It calculates all the sum totals at a time.

TO VIEW FORMULAE USED: Press ‘ctrl’ key + ‘`’ key above tab key. To get back the original data press same combination once again.

INSERTING A COMMENT:

1.    Select a cell.

2.    Go to insert menu, click comment option.

3.    Type comment/text/formula.

4.    Click outside.

To edit/delete comment: Right click on the commented cell.

HYPERLINKING:

Hyperlink is a coloured and underlined text or a graphic that represents a link to a file on the local computer, a network, or the Internet. When you click the hyperlink, the location that is specified by the hyperlink opens.

Creating a hyperlink to a specific location in a workbook:

To link to a location in the current workbook or another workbook, you can either define a name (given to a cell as its address) for the destination cells or use a cell reference.

1.    To use a name, name the destination cells in the destination workbook.

2.    In the source workbook, select the text, which is to be hyperlinked. Go to Insert menu and click Hyperlink option.

3.    To link to a location in the current workbook, under Link to click Place in this document.  Select sheet number and the cell address. Additionally screen tip can be given to display informative text when the mouse rolls over the hyperlinked text.

4.    To link to a location in another workbook, under Link to click Existing file or Web page. Under Browse for, click File. Select the workbook that is to be linked.

5.    Click the Bookmark button.  In the list under Cell Reference, click the sheet you want to link to, and then type the cell reference in the Type in the cell reference box. Click OK.

6.    In the list under Defined Names, click the name that represents the cell or cells you want to link to. Click OK.

7.    To assign a tip to be displayed when you rest the pointer on the hyperlink, click ScreenTip and then type the text you want in the ScreenTip text box. Click OK.

INSERTING DATE AND TIME:

To insert date, press ctrl + ; keys and to insert time, press ctrl + shift + : keys.

MACRO (For recording frequently used commands):

1.    Go to tools menu, select ‘Macro’ option

2.    Click on ‘Record new macro.’

3.    Give macro name.  Give a shortcut key. (E.g. ctrl+w).

4.    Store macro in ‘Personal macro workbook’. Click OK.

5.    Recording starts.  Give commands one by one.

6.    Once the required commands are given, click ‘stop recording’.

Note: To run macro, press the short cut key, i.e. ctrl+w.

AUTOFILL (to fill data in sequence):  


 

SPELL CHECK—ADD TO DICTIONARY:

To check the spellings of entire worksheet:

1.    Go to Tools Menu, click Spelling option.

2.    Select the word from the list of suggested words.

3.    Click on ‘Change’.

To add a particular word to the dictionary, click on Add tab.

TRACKING CHANGES:

1.   On the Tools menu, go to Track Changes, and then click Highlight Changes.

2.   Click the Track changes while editing check box. This check box turns on workbook sharing and the change history.

3.   Select the When check box, click Not yet reviewed in the When box, and then click OK.  The option ‘Highlight changes on screen’ is checked by default.  If you click ‘List changes on a new sheet’, the history of all the changes made is created on a new sheet with all the details.

4.   Save the workbook.

5.   Make the changes you want on the worksheet.

6.   Microsoft Excel marks cells that you change, insert, or delete with a highlight color.

To view the changes made, once again go the Tools menu, Track changes and click Accept or Reject Changes.  Excel will take you to the cells with changed data one by one, which can be accepted or rejected.

GOAL SEEK:

Goal seek facility is used to find a specific result for a cell by adjusting the value of one other cell.

1.   On the Tools menu, click Goal Seek.

2.   In the Set cell box, enter the reference for the cell that contains the formula you want to resolve.

3.   In the To value box, type the result you want.

4.   In the By changing cell box, enter the reference for the cell that contains the value you want to adjust. This cell must be referenced by the formula in the cell you specified in the Set cell box.

 SOLVER  : Solving a problem by using Solver.

1.   On the Tools menu, click Solver.

If the Solver command is not on the Tools menu, you need to install the Solver add-in.  For that go to Tools menu, click Add in and select Solver option.  The Solver will be added to Tools drop down menu.

2.   In the Set Target Cell box, enter a cell reference or name for the target cell. The target cell must contain a formula.

3.   To have the value of the target cell be as large as possible, click Max. To have the value of the target cell be as small as possible, click Min.

To have the target cell be a certain value, click Value of, and then type the value in the box.

4.   In the By Changing Cells box, enter a name or reference for each adjustable cell. The adjustable cells must be related directly or indirectly to the target cell. You can specify up to 200 adjustable cells.

To have Solver automatically propose the adjustable cells based on the target cell, click Guess.

5.   In the Subject to the Constraints box, enter any constraints you want to apply.

6.   Click Solve.

7.   To keep the solution values on the worksheet, click Keep Solver Solution in the Solver Results dialog box.

To restore the original data, click Restore Original Values

DATA BASED UTILITIES:

The following utilities are available in Data menu.  Sort option is used to arrange data in proper order, Filter option is used to view required data onlytemporarily hiding unwanted data and Subtotal option is used to get some results on the numerical data.

a.    SORTING:

i.    Select entire data, go to Data menu.

ii.  Click ‘Sort’ option

iii.    Under ‘Sort by’, select column head (e.g. sort by Dates or

Invoice nos. etc) iv. For further sorting, if required, click ‘then by’ and select option

v.   Click OK.

b.    FILTERING:

i.Select entire data, go to Data menu.

ii.Select ‘filter’ option and click on ‘Auto filter’

iii.Black arrows appear at each column heading.  Click on required arrow (e.g. arrow near Date heading)

iv.Select a date from drop down list.

v.Only those records that fall under selected date are shown and rest of the data is temporarily hidden

vi.     The clicked black arrow becomes blue indicating filter is applied on that column

vii.   To get back all the records, click the blue arrow and click on ‘All’.

viii.  Note :        1. To see top records, click on black arrow and select ‘Top 10’ option.    

ix.     2.   To see records based on some conditions, click on ‘Custom’. Give condition.  Computer shows only those records, which satisfy the given conditions.

c.    ADVANCED FILTER:

i.     Give criteria in a row above the data.

ii.    Select first cell in the data, go to Data menu. iii. Select Filter option, click Advanced Filter. iv. Click Copy to another location.

v. In List range, select data (if it is not selected automatically). vi. Select criteria and select the location where the filtered data is to be displayed. vii. Click OK.

d.    SUBTOTALS:

i.Sort the data first.

ii.Select entire data, go to Data menu. iii.Click Subtotals option.

iv.Select option under ‘At each change in’ (ex. Date)

v.Select a function under ‘Use function’ option

vi.Under ‘Add subtotals to’ select column headings

vii.Click OK.

Note:            1.    It shows result of the function used within the selected data.  Three buttons appear in top left corner.  Click button 1 to view only grand total.   Click button 2 to view GT and criteria wise total and click 3 to view all the records.

                       2.    To remove subtotals – Go to Data menu  --- Subtotals

|

Click    ‘Remove all’.

e. PIVOT TABLE:

It is an interactive table that summarizes and analyzes data from existing lists, tables, and databases. After you create a PivotTable report, you can display and organize the data.

i.          Creating Pivot Table: ii.       Open the workbook where you want to create the PivotTable report. iii. If you are basing the report on a Microsoft Excel, click a cell in the list.

iv.    On the Data menu, click PivotTable and PivotChart Report.

v.      In step 1 of the PivotTable and PivotChart Wizard, click Microsoft Excel list and click Pivot Table under What kind of report do you want to create? Click Next. vi.        In step 2 of the wizard, select data range, if not given. vii.   In step 3 of the wizard, click ‘Layout’ tab.

viii.   Drag the fields that you want onto the ROW and COLUMN areas in the diagram. In PivotTable reports, Row field and item labels appear on the left. Column field and item labels appear across the top Drag the fields that contain the data that you want to summarize onto the DATA area.

ix.     By default, the summary function used in the data is Sum. To change the summary function, double click the data area and select the required function.

x.      Click OK in the PivotTable and PivotChart

Wizard Layout dialog box, and then click Finish to create the report.

DATA VALIDATION:  Specifying data restrictions:

Display a message that stops incorrect data entry:

1.    Select the cells for which you want to display a message and prevent incorrect entries.

2.    On the Data menu, click Validation, and then click the Settings tab.

3.    Under validation criteria, select an option like Whole number. Specify the data restrictions for which you want to display the message.

4.    Click the Error Alert tab.

5.    Make sure the Show error alert after invalid data is entered check box is selected.

6.    In the Style box, click Stop. The message will have a Retry button that returns to the cell for further editing and a Cancel button that restores the previous value in the cell.

7.    If you want to display your own text for the message, type the text in the Error message box, up to 225 characters. If you don't enter any text in the Error message box, the message displays the following: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

Note   Microsoft Excel displays the message only when a user types data in the cell. Formulas that calculate to invalid data, data placed in the cell by a macro, and data that has been copied or filled do not cause the message to appear.

FREEZING PANE: To keep a part of sheet fixed –

1.    Select a row.

2.    Go to ‘Window menu’, click ‘Freeze pane’.

3.    The part above selected row is fixed and rest of the sheet can be scrolled.

To get back normal sheet – Window menu --- click ‘Unfreeze pane’.

SPLITTING PANE: To view two parts of large data on the screen:

                    Drag the split situated above vertical scrollbar.  OR

Select a row, go to Window menu, and click ‘split pane’ option. The sheet gets divided in two scrollable parts.

To remove split, either go to Window menu and click ‘remove split’.  OR double click on the split.

WORKING WITH MULTIPLE SHEETS:

1.    Inserting a sheet –

a.    Right click on sheet tab, click on insert.

b.    Click worksheet, click OK.

2.    Deleting sheet –

a.    Right click on sheet tab, click on delete.

b.    Click OK.

3.    Renaming sheet –

a.    Right click on sheet tab, click on Rename.

b.    Type required name.

4.    Rearranging sequence of sheets –

a.    Right click on sheet tab, click on ‘Move or copy’.

b.    Select proper option, click OK.

5.    Copying entire sheet –

a.    Right click on sheet tab, click on ‘Move or copy”

b.    Click ‘create a copy’, click OK.

ADJUSTING OVERFLOWING TEXT:

1.    Place cursor between two column heads and drag to required column width. Notice the cursor changes to a vertical line with arrows pointing left and right.



2.    Double click between two column heads.  Computer searches for the biggest entry and increases column width accordingly.

3.    Autofit selection: - Select column with overflowing text, click Format menu, go to Column option and click on ‘autofit selection’.

4.    Specifying width of column: - Select column, go to Format menu, select Column option, click ‘width’ option and specify width.

5.    Shrink to fit: -Right click on column with overflowing text, click ‘Format cells’ option. Click Alignment tab, click ‘Shrink to fit’ and click OK.

PROTECTING THE WORKSHEET PARTLY:

You can give password to your worksheet, keeping part of it accessible or open to make any modifications.

1.    Keeping some columns open for modifications:

Select those columns—Format menu—Cells—protection tab—Remove tick mark from ‘Locked’—Click OK.

2.    Giving password to rest of the sheet:

Tools—Protection—Protect Sheet—Give Password—OK

   

a.  To change the default colors of graph, double click and select colour.

b.  To change elevation and rotation of 3-D graph, right click on graph.  Click 3-D view option; change the degrees of rotation and elevation.

c.  To get a simple graph, select data and press F11 key.

Giving background  colour to the chart

Double click on ‘Plot area’.  Click Fill effects in ‘Format plot area’ dialog box. Select colour.

Adding picture in background

Double click on ‘Plot area’.  Click Fill effects in ‘Format plot area’ dialog box. Select ‘Picture’ tab.  Select a picture.  Click Insert.

Changing scale

To change scale and format axis—Right click at starting point of an axis, say Value axis—click open the Scale tab and make changes.

You can double click X-axis and change alignment.

Double click on Y-axis and change the intervals.

Making line smooth in the chart

Select line in the line chart—Double click—Under Marker, click None.  This makes the line smooth by removing the diamonds.

Drag-and-Drop Chart Editing

Select the cells containing a new series of data to add to an existing chart, then drag and drop the new values onto the chart and Excel will redraw it to include the new data.

Adding columns and lines in a graph—

 

XY (scatter) graphsplot data points according to x and y axes

Surface graphsshow data trends in a two-dimensional view

Bubble chartscompare three sets of data

Stocks chartsare actually high-low-close charts, which show three numeric values along each bar

 2-D and 3-D chartsoffer cylinder, pyramid, and cone shapes

Each type of graph includes a number of subtypes. In addition, each of these graph types is available in 3-D (which means that the Line graph category becomes 3-D line graph).

Once you choose a basic chart type, you select individual options to create the kind of graph you want. You might start with a bar chart, for example, and then select options that enable you to create a stacked bar chart-with data ranges stacked one on top of another. 

NAMING CELLS/CELLRANGE:

1.   Select a cell or cell range to be named.

2.   Click Name box and type a name.

3.   Press enter.

      Note: a. The cell address of that cell is now changed to the given name.  This name can be used directly in the formula.

                b. If a cell range is given a name, when that particular name is clicked in the name box, that cell range gets selected automatically helping user in searching important data in large tables.

CONDITIONAL FORMATTING:

1.    Select the column by clicking the column heading.

2.    Go to format menu, select conditional formatting.

3.    Give condition, click format.

4.    Change format (like changing font color, size etc.)

5.    Click OK twice.

The data below shows Quantity issued from stores department.  To find out the Fast Moving Items depending on the issued quantity, we can change the

 

Functions are used to calculate results used in statistics, finance, engineering, math and other fields.  Functions calculate a specific result: a total, an average, the amount of a monthly loan payment, or the statistical mean of a group of numbers.  Each function has a specific order or syntax that must be used to the function to work properly.

Selecting a function:

1.    Type ‘=’ to start the formula.  The Name box will change to a Function box, displaying the last function used.  Click the Function drop down arrow to open the list of recently used functions.

2.    Or click ‘fx’ button on standard tool bar.

USING FUNCTIONS FOR CALCULATIONS:

1. Statistical Functions:

a.    COUNT: returns the number of cells containing numbers in a range.

b.    COUNTA: returns the number of entries, including text entries, in a range.

c.    AVERAGE:  sums the numbers in a range and divides the total by the number of numbers.

d.    MEDIAN: Another kind of average that returns the middle value in a range of numbers.

e.    MODE: Returns the value that occurs most frequently.

f.  MAX: Returns the largest value in a range.

g.    MIN: Returns the smallest value in a range.

Text Functions: Using Upper function to Change Case:

There are three functions viz. UPPER, LOWER and PROPER which convert text to uppercase, lowercase, or mixed case entries.  For example, a cell C2 contains “prices”, then the function can be written as:

           =UPPER (C2)

, which gives result as “PRICES”.

Math and Trig category:

 FLOOR

Rounds number down, toward zero, to the nearest multiple of significance.

Syntax FLOOR(number,significance)

Number   is the numeric value you want to round.

Significance   is the multiple to which you want to round.

Remarks

•     If

•     either argument is nonnumeric, FLOOR returns the #VALUE! error value.

•     If number and significance have different signs, FLOOR returns the #NUM! error value.

•     Regardless of the sign of number, a value is rounded down when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.

Examples

FLOOR(2.5, 1) equals 2

FLOOR(-2.5, -2) equals -2 FLOOR(-2.5, 2) equals #NUM!

FLOOR(1.5, 0.1) equals 1.5

FLOOR(0.234, 0.01) equals 0.23

Ceiling

CEILING

Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.

Syntax CEILING(number,significance)

Number   is the value you want to round.

Significance   is the multiple to which you want to round.

Remarks

•    If either argument is nonnumeric, CEILING returns the #VALUE! error value.

•    Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.

•    If number and significance have different signs, CEILING returns the #NUM! error value.

Examples

CEILING(2.5, 1) equals 3

CEILING(-2.5, -2) equals -4

CEILING(-2.5, 2) equals #NUM!

CEILING(1.5, 0.1) equals 1.5

CEILING(0.234, 0.01) equals 0.24

h. Round

ROUND

Rounds a number to a specified number of digits.

Syntax ROUND(number,num_digits)

Number   is the number you want to round.

Num_digits   specifies the number of digits to which you want to round number.

•    If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.

•    If num_digits is 0, then number is rounded to the nearest integer.

•    If num_digits is less than 0, then number is rounded to the left of the decimal point.

Examples

ROUND(2.15, 1) equals 2.2

ROUND(2.149, 1) equals 2.1

ROUND(-1.475, 2) equals -1.48 ROUND(21.5, -1) equals 20

                                                        i.    Roundup

ROUNDUP

Rounds a number up, away from 0 (zero).

Syntax

ROUNDUP(number,num_digits)

Number   is any real number that you want rounded up.

Num_digits   is the number of digits to which you want to round number.

Remarks

•    ROUNDUP behaves like ROUND, except that it always rounds a number up.

•    If num_digits is greater than 0 (zero), then number is rounded up to the specified number of decimal places.

•    If num_digits is 0 or omitted, then number is rounded up to the nearest integer.

•    If num_digits is less than 0, then number is rounded up to the left of the decimal point.

Examples

ROUNDUP(3.2,0) equals 4

ROUNDUP(76.9,0) equals 77

ROUNDUP(3.14159, 3) equals 3.142

ROUNDUP(-3.14159, 1) equals -3.2

ROUNDUP(31415.92654, -2) equals 31,500

                                                        j.    Rounddown

ROUNDDOWN

Rounds a number down, toward zero.

Syntax

ROUNDDOWN(number,num_digits)

Number is any real number that you want rounded down. Num_digits   is the number of digits to which you want to round number. Remarks

•    ROUNDDOWN behaves like ROUND, except that it always rounds a number down.

•    If num_digits is greater than 0 (zero), then number is rounded down to the specified number of decimal places.

•    If num_digits is 0 or omitted, then number is rounded down to the nearest integer.

•    If num_digits is less than 0, then number is rounded down to the left of the decimal point.

Examples

ROUNDDOWN(3.2, 0) equals 3

ROUNDDOWN(76.9,0) equals 76

ROUNDDOWN(3.14159, 3) equals 3.141

ROUNDDOWN(-3.14159, 1) equals -3.1

ROUNDDOWN(31415.92654, -2) equals 31,400

k. Power

POWER

Returns the result of a number raised to a power.

Syntax POWER(number,power)

Number   is the base number. It can be any real number.

Power   is the exponent to which the base number is raised.

Remark

The "^" operator can be used instead of POWER to indicate to what power the base number is to be raised, such as in 5^2.

Examples

POWER(5,2) equals 25

POWER(98.6,3.2) equals 2401077 POWER(4,5/4) equals 5.656854

                                                        l.    Odd

ODD

Returns number rounded up to the nearest odd integer.

Syntax ODD(number)

Number is the value to round. Remarks

•    If number is nonnumeric, ODD returns the #VALUE! error value.

•    Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an odd integer, no rounding occurs.

Examples

ODD(1.5) equals 3

ODD(3) equals 3

ODD(2) equals 3

ODD(-1) equals -1

ODD(-2) equals -3

m. Fact

FACT

Returns the factorial of a number. The factorial of a number is equal to 1*2*3* * number.

Syntax FACT(number)

Number   is the nonnegative number you want the factorial of. If number is not an integer, it is truncated.

Examples

FACT(1) equals 1

FACT(1.9) equals FACT(1) equals 1

FACT(0) equals 1

FACT(-1) equals #NUM!

FACT(5) equals 1*2*3*4*5 equals 120

n. Int

INT

Rounds a number down to the nearest integer.

Syntax INT(number)

Number   is the real number you want to round down to an integer.

Examples

INT(8.9) equals 8

INT(-8.9) equals -9

The following formula returns the decimal part of a positive real number in cell A1:

A1-INT(A1)

o. Mod

MOD

Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

Syntax MOD(number,divisor)

Number   is the number for which you want to find the remainder. Divisor   is the number by which you want to divide number. If divisor is 0, MOD returns the #DIV/0! error value.

Remarks

The MOD function can be expressed in terms of the INT function:

MOD(n, d) = n - d*INT(n/d)

Examples

MOD(3, 2) equals 1

MOD(-3, 2) equals 1

MOD(3, -2) equals -1

MOD(-3, -2) equals -1

p. Product

PRODUCT

Multiplies all the numbers given as arguments and returns the product.

Syntax

PRODUCT(number1,number2, )

Number1, number2,    are 1 to 30 numbers that you want to multiply.

Remarks

•    Arguments that are numbers, logical values, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers cause errors.

•    If an argument is an array or reference, only numbers in the array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.

Examples

If cells A2:C2 contain 5, 15, and 30:

PRODUCT(A2:C2) equals 2,250

PRODUCT(A2:C2, 2) equals 4,500

q. Rand

RAND

Returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated.

Syntax RAND( ) Remarks

•    To generate a random real number between a and b, use:

RAND()*(b-a)+a

•    If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number.

Examples

To generate a random number greater than or equal to 0 but less than 100: RAND()*100

                                                        r.   Sign

SIGN

Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.

Syntax SIGN(number)

Number   is any real number.

Examples

SIGN(10) equals 1

SIGN(4-4) equals 0

SIGN(-0.00001) equals -1

Using Date and Time Functions:

We can extract the day and month from a column containing date, using two functions viz. DAY and MONTH.  For example, a cell C2, contains date as 12/5/2004, we use function =DAY (C2) in cell D2.

D2 shows result as 5.  Same for MONTH function.

s.    Adding days to a Date: To add a number of days to a date, simply add the cell containing number of days to the cell containing date.  The result cell gives the new date.

t.     Subtracting days from a Date: Same as addition.

u.   Adding Months to a Date: Adding number of days is very easy, we are just adding a number.  To add months we have consider whether to add 28, 29, 30 or 31 days. But we need not consider this, as the task is made easier using MONTH Function which has in built calendar.  So to add 2 months to a date, we write the following formula. =DATE (YEAR (C2), MONTH (C2)+2,DAY (C2)) where C2 is the cell containing date.  Same formula can be used to increment year or days.

v.   Getting number of days between two dates: Using a Function DATEDIF we get number of complete years, months or days between two dates.

Syntax for the function is:

=DATEDIF (startdate, enddate, timeunit). Use timeunit in double quotes, y for year, m for month and d for day. For example,  =DATEDIF (3/15/2004,5/15/2004,”m”) gives result as 2.

2.Using Logical Functions:

Logical functions are used to take different actions based on conditions. Using IF Function  (to calculate two results based on one condition):

a.    Select result cell

b.    Click ‘fx’ button on standard toolbar.

c.    Under function category, select ‘Logical’

d.    Under function name, select ‘if’.  Click OK.

e.    In front of logical test, give condition.

f.  Type text/formula, if condition is true.

g.    Type text/formula, if condition is false.

h.    Click ok.  Select result cell and drag down to calculate for other records.

3.Using Lookup Functions:

The lookup functions use a value to find (lookup) a record in a table and return information from other columns in the table.  There are three lookup functions: LOOKUP, VLOOKUP AND HLOOKUP.

VLOOKUP and HLOOKUP search vertically (through a column) and horizontally (through a row) to find a specified value.  VLOOKUP is more frequently used function as Excel stores data in rows representing records.

a.    VLOOKUP: The syntax for VLOOKUP function is-=lookup (lookup value, table array, column index number, range lookup) where,

Lookup Value = The cell that contains the value you want to find in the first column of the table.

Table Array = The range of table/database

Column Index Number =The number of the column that contains the value to be returned.  The first column in a table is column 1.

Range Lookup = Type False if the lookup should look for an exact match for the Lookup value.

b.   HLOOKUP: It is similar to VLOOKUP except it looks horizontally across the top row of the lookup table rather than down the first column.   Here row index number is to be specified rather than a column index number.

c.    LOOKUP: This function searches either horizontally or vertically.  Use LOOKUP when the column or row you are searching is not the first column or row in the table and the values you need to return are on the left or above the value column or row you are searching.

The LOOKUP Function takes three arguments viz. value, lookup vector, and result vector.

NOTE: While using the LOOKUP Function, the lookup vector must be sorted in ascending order AUTOCALCULATE:

1.    Right click on status bar.  Select a function (ex. Max)

2.    Select a cell range.  The status bar shows the result of selected cell range using the selected function.

A FEW MORE USEFUL FUNCTIONS:

Hiding Unused Rows & Columns

Hiding rows and columns does not just have to be for concealing data or formulas. If your spreadsheet only uses a small area of the 16,777,216 available cells in an Excel worksheet, maybe it would be nice to hide some of that vast unused region to keep users from scrolling off the edge of the working area.

Here are the simple steps to accomplish this task:



1.    Select the row header just beneath the used area of your spreadsheet, where you want to start hiding rows.

2.    Press Ctrl + Shift + Down Arrow. This will highlight everything from your selected row through the bottom of the worksheet.

3.    From the worksheet's Format menu, choose Row, and then Hide.

Follow the same basic steps to hide columns. The difference will be that you should begin by selecting a column header in the first empty column to the right of your used area, then press Ctrl + Shift + Right Arrow.

Finally, from the worksheet's Format menu, choose Column, then Hide.

IsError Function

Sometimes it is necessary to include formulas in a worksheet that will require the sheet to be completed by a user before they will display a correct result.

In this case, you could have error values throughout the spreadsheet until you get all the necessary input. The most common workaround to suppress #DIV/0! and other errors from is to apply the ISERROR function in your formulas. The syntax for the IsError function is:

=IF (ISERROR (A1/B1),"",A1/B1)

This would say if cell A1 divided by B1 caused an error, then make the value in the cell equal to nothing -- otherwise (ELSE), make it whatever A1 divided by B1 really should be. This is a good way of handling 'can't divide by zero' and similar errors.

You can also substitute just a 0 (zero) or even a text message in the place of the empty double-quotes. For example,

      =IF (ISERROR (A1/B1),"Input Incomplete",A1/B1)

Ranking Values:

Some of the various techniques for ranking values include using the LARGE, SMALL, and RANK worksheet functions.

Referring the following table we apply these functions:

LARGE Worksheet Function

The LARGE Worksheet function returns values from a list in rank order from the greatest to the least value (descending). While it seems like this task should be quite straightforward, the LARGE function becomes especially useful whenever there are multiple items in the list tied for a rank.

There are two required arguments. The first is the address of the range of values to be evaluated -- that is, the list. The second argument is the position of the value to be returned. For instance, 1 returns the highest value, 2 returns the second highest value in the list, and so on.

Whenever multiple items in the list are tied for a position, each will be counted as a separate item within the ranking.

Let's see how it works.

=LARGE (A2:A7, 5)

Using the spreadsheet above, the range to be evaluated in all the examples will encompass cells A2:A7. The second argument in our example is the number 5, meaning we want to know the value of the number that is in the fifth largest position.

The result happens to also be 5. The largest number was 12, then 9, and 8 is repeated twice -- so, the fifth number occurring is the value of 5.

SMALL Worksheet Function

The SMALL Worksheet function works the same way as the LARGE function, except in ascending order.

=SMALL (A2:A7, 4)

In this example, the formula returns a value of 8. It starts looking at the smallest number in the list, being 4. In position #2 is the value 5. Since 8 is repeated in our range, it occupies positions #3 and #4 - therefore, the answer becomes 8.

RANK Worksheet Function

The RANK Worksheet function is much like the former two functions, except:

it returns the position number of a specified value in the list;

  - and -

it counts multiple instances of a value in a list as separate but equal items

in the ranking.

The RANK function has three arguments - the first two are required. The first argument is the criteria value. This is the value for which you want to know its position within the list. The second argument, is the range to be evaluated -- the list, itself.

The third argument tells Excel whether to evaluate the list in descending order or ascending order. A value of 0 (zero), or omitting the argument, ranks the list by default from greatest to least. Using a 1 as the third argument indicates that the items in the list should be evaluated in ascending order.

Here is an example:

=RANK (9,A2:A7, 1)

This formula returns a value of 5. It gives the position of the number 9 within the list, ranked from the smallest to the largest value.

Since the number 8 appears twice in our list, it is counted as position #3, and position #4 will be skipped.

Here is another example demonstrating this, except in descending order:

=RANK(8,A2:A7)

In this case we want to know the position of the number 8 from the largest number in the list. In this final example, the answer is 3. The number 12 occupies position #1, then 9 is in position #2 -- therefore, the number 8 returns a 3.

Putting a Dropdown ListBox in a Cell

A single cell (or even an entire column) can be given a data validation setting so that, when selected, a dropdown list appears with a finite list the possible entries for that range.

To create a dropdown list, restricting entries in a range to those values, follow these steps:

1.    Select the range that is to contain the validation dropdown listbox. This may be either a single cell or a larger range, such as an entire column.

2.    From the Data menu, choose Validation.

3.    On the Settings tab, in the Allow dropdown list, select List.

4.    The Source box can refer to a range of cells for its values, or it can contain them directly.

o    a direct list :

You may type the source list directly in the Source box.

In this example, the list is simply days of the week, separated by commas. Notice there are no quotes around the text.

o    a referenced list :

Click in the Source box, and then either type a range reference manually [ preceded by an = (equals) sign ] or use the mouse to select a range on the same worksheet that has the source list of possible entries.

If a user tries to manually enter anything other than these values, a stop message appears and will not allow the cell to keep the invalid entry. The only options for the user will be to Retry or Cancel.

It will be possible to make valid entries (i.e., MON, TUE, WED, etc.) manually in a cell to which this validation setting has been applied; a user is not required to actually select these values from the list.

A FEW TIPS…

1. What's the Current Date or Time?

You are familiar with Excel's NOW function, which plugs the current date and time into your spreadsheet. Just enter =NOW() into any cell, and Excel displays the date and time formatted according to the regional options you've set for Windows. If you just want to see the date, use the TODAY function, =TODAY().

But if you want to use either of these functions as a type of "timestamp" you'll find that they don't do the job, because they're automatically updated whenever your worksheet is calculated or re-opened. The cell that displays today's date will display tomorrow's date when you open the spreadsheet tomorrow.

To insert a fixed date into a cell--a date that you don't want to change--hold down Ctrl while you press the semicolon ( ;) key. To insert the current time (without a date), press Ctrl-Shift-Semicolon.

These commands are useful if you like to document your work. For example, you might want to keep track of when you added new data to your worksheet.

2. Secret Document: Do Not Print

When it's time to print your Excel worksheet, you may prefer that some information is left unprinted. For example, you may have some confidential information (such as employee salaries) in a column, or your sheet may have some intermediate calculations that the top bosses aren't interested in seeing.

To avoid printing specific rows or columns, just hide them before printing. To hide rows, select them by clicking the row numbers (click and drag to select a block of rows; hold down Ctrl while clicking to select non-adjacent rows). Then right-click one of the highlighted border row numbers and click Hide.

Use the same procedure to hide columns (but right-click a highlighted column letter to choose Hide).

When you're finished printing, you can quickly unhide all rows or columns by selecting the entire worksheet (click the blank gray box formed by the intersection of the row and column borders at the top left corner of your spreadsheet). Then right-click a row or column border and choose Unhide.

4. Shade Alternate Rows

Excel's Conditional Formatting feature has many uses, and here's one that's particularly handy. Conditional Formatting makes it simple to apply cell shading (green or otherwise) to every other row in a worksheet range. For a lengthy list, shading alternate rows can improve legibility. Here's how to do it:

1.    Highlight the range of cells or rows or columns that you want to format.

2.    Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.

3.    Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.

4.    Click the Format button to bring up the Format Cells dialog box.


 

5.   Select the Patterns tab and specify a color for the shaded rows. You'll probably want to choose a light color, so that the default black text will still be legible. Or, you can go all out and change the text color as well (do this in the Font tab of the Format Cells dialog box). For example, you might select a dark blue background, accompanied by a mellow yellow text color.

6.   Click OK twice to return to your worksheet.

FINDING AND CORRECTING FORMULA ERRORS:

Sometimes while calculating, excel gives some errors which are required to be corrected.  To correct the errors we should know the meaning and causes of errors. 

There are eight error codes as under:

 

Error

Causes/meaning

####

Data too wide for the cell or result is negative

#DIV/0

Number is divided by either zero or blank cell.

#N/A

A required value is omitted in a function

#NAME

Text is used in a formula without quotes or wrong spelling of name of a range.

#NULL

A space used between two ranges in argument

#NUM

Text or a blank cell is used in an argument that requires a number

#REF

Invalid reference/some cells are deleted that are required in a formula.

#VALUE

Entered text when formula requires a number or a Boolean value (TRUE or FALSE).

RESOLVING CIRCULAR REFERENCES:

Circular references are a special type of error. It occurs when a formula refers to the cell that it is in.  For example, when a formula is entered in a cell J20 and calculates sum total from J2 to J20, then excel tries to add J20 over and over again eventually showing the error message of Circular reference.

ADVANCED EXCEL                                                                              41

 

FORM DESIGNING:

To get radio buttons, check boxes, list boxes, combo box, scroll bar etc. on the excel sheet, right click on menu bar which gives a list of toolbars.  Click on Forms toolbar. Click the required option button.  Drag to required size.  To change the text, right click the button and click ‘Edit text’.

To link these buttons, right click on the button, click Format control option. Click Control tab, select required cell range.

PRINTING WATERMARK ON EACH PAGE:

1.    Go to View menu.

2.    Click Header and Footer option.

3.    Click Options, click Watermark tab.

4.    Select a watermark from default list.  To create your own watermark, click new and type the text.

ADDING IPCL LOGO ON EACH PAGE ON TOP WHILE PRINTING:

Insert a line on top of the data.  Select first cell, go to Insert Menu, select Picture option and click ‘from file’.  Select file with logo, cut it to required size.

While printing, repeat this row.

PRINTING THE DATA:

Printing the worksheet:

When you click the print button on the standard toolbar, excel prints one copy of the selected worksheet using current settings from the Print and Print Setup dialog boxes.  To print as per you specifications, use the following options.

1.   Setting print area :

Go to File menu,

Click ‘print area’ and then click ‘set print area’. Select the area to be printed.

2.   Using print preview :

choose File---Print preview or click the Print Preview button on the Standard toolbar to preview the selected worksheets.  You can set margins, see page break preview, change page set up and change the page break in this preview.

ADVANCED EXCEL                                                                              42

 

3. Page set up :

Go to file menu, Click ‘page set up’.

Click Page tab and change settings for orientation, scaling, paper size, print quality or first page number.

Orientation is changed to landscape if your worksheet is wider than it is long.

Scaling is used to reduce or enlarge the print.  If you need to make the print larger, use the Adjust To control and choose a size greater than 100%. 

The Fit To control instructs Excel to reduce a worksheet that exceeds a specific number of pages so it will fit.

To print a different range, click the Collapse button in the Print Area text box to collapse the dialog box, select the area you want to print.  If the Print Area contains non-adjacent cells, each contiguous range prints on a separate page.

First page number is used to start printing from a page number other than 1.

Under Print Titles, specify the rows to be repeated at top, so that the titles or column headings appear on each page.

Click Margins tab; give the page order, and the row and columns to be repeated on each page.

Click if gridlines are required so that the print shows table with lines dividing row and columns as seen on the excel sheet.

4. Printing:

Use the Print Range controls to print some of the pages of a multiplepage print job.

In the Print What control, specify which part of the worksheet or workbook you want to print.

 The selection option provides another way to print specific area— select the cells you want to print, and then print the selection.

Choose workbook to print every worksheet that has content in the active workbook.  To print some, but not all, worksheets in a workbook, select the sheets before opening the Print dialog box.

ADVANCED EXCEL                                                                              43

 
 

-----------

 

ADVANCED EXCEL

 

44

 



6