Learn Microsoft EXCEL advanced level


Télécharger Learn Microsoft EXCEL advanced level

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 :


 

TOPICS

 

6

Chapter 1: Introductiion to M.S Excel

Introduction to MS Excel

Microsoft Office Excel is a powerful spreadsheet tool used to organize, manipulate and analyze data. Spreadsheets are commonly be used to perform many different types of calculations. Excel is often used in the workplace to track statistics, create sales reports, financial modelling, scientific engineering, and making charts and graphics.

Cell The intersection of a row and column is called a cell. Cells are the basic rectangular buildingblocks of a spreadsheet. Each Cell is assigned with cell address. Cell Address/Cell Reference begins with Column Name followed by Row Number (e.g. Cell Address is C9, having column name C and row number 9). Each cell is made up of inbuilt calculator.

Title Bar Displays the name of the current workbook and it holds some control buttons toMinimise, Restore & Close the Excel Window.

Quick Access Toolbar Found next to the office button in the Title Bar provides quick access tocommonly used buttons. The default buttons are Save, Undo and Redo, It can also be customized.

Ribbon/Menu Bar Menu Bar consist of Menus such as File, Edit, View, Insert, Format, Tools,Data, Window and Help. In Excel 2007 the Dropdown Menu Bar is replaced by Tabs with Horizontal Ribbon such as Home, Insert, Page Layout, Formulas, Data, Review and View Tab. Within each tab, various related options may be grouped together for easy identification. The Ribbon is designed to make the features of the application more discoverable and accessible with fewer mouse clicks.

Formula Bar Displays the contents of the active cell. This could be a formula, data or just text.Formula always begins with = (equal to) otherwise Value is a Text or Constant. The Cell which contains formula will display only the end result of the formula where as Formula will be displayed in the Formula Bar.

Active Cell The cell you are currently working with is active cell. Thick Bordered Black outlinesurrounding the cell indicated the current active cell. To change which cell is the active cell, simply click on it or move to it using the arrow keys on your keyboard.

Name Box Displays the active cell address or the name of the selected cell/ range.Cells arenamed by giving the column letter and then the row number. For example, B3 means the active cell is located in column B and is in the 3rd row.

Chapter 2: Data Entry & Fill Series

DATA ENTRY IN EXCEL

Entering repetitive data into a spreadsheet can be time-consuming, and mistakes will often find their way in — with Excel’s simple built-in data entry tools you can improve both speed and accuracy.

things:

AutoFill using Fill Handle: It allows you to quickly fill data in a series into adjacent cells. (e.g.months, days of the week, or a numeric series)

?

•   Numeric Series: Auto fill can also be used to quickly enter numeric patterns

(e.g. 1, 2, 3or 10, 20, 30) into adjacent cells. To use Auto fill with numeric patterns, enter the first two values in the series, one value in one cell and the next in the cell immediately below or to the right. Now select both cells. Using Fill Handle, Click and drag down or across the cells you want to fill, and then release the mouse button. ?

 

•   Text Series: Type the first word of the series (e.g. January) then using fill handle clickand drag the series down or across the number of cells you would like to fill this series. ?  

   

Chapter 3: Inserting and Deleting Row/Column

Column is always inserted to the left hand side of the selected cell. Row is inserted above the selected cell.

4 WAYS TO INSERT ROWS/COLUMNS

o Click the single Cell >> Right Click & choose INSERT >> In Insert Dialog

Box, Select  

          Entire Column/Entire Row >> Click OK  o GoTo Cells Group from Home Tab >> Click down arrow from Insert  Icon >> Select         Insert Sheet Rows/ Insert Sheet Columns o Select the column Heading/Row Heading >> Right Click & choose

INSERT (if column heading is selected Column is inserted, if row heading is selected Row is inserted)

Shortcut Key  §

  Select a Single Cell >> CTRL “+” (in PC), CTRL + SHIFT “+” (Laptop)   >> From Insert Dialog Box, Press “C” to Insert Column & Press “R” to Insert Row.

§ Select Column/Row Heading >> CTRL “+” (in PC), CTRL + SHIFT “+” (Laptop)

4 WAYS TO DELETE ROWS/COLUMNS

o Click the single Cell >> Right Click & choose DELETE >> In Delete Dialog

    Box, Select  

Entire Column/Entire Row >> Click OK  

o GoTo Cells Group from Home Tab >> Click down arrow from Delete

Icon >> Select  

Delete Sheet Rows/ Delete Sheet Columns  

      o     Select the column Heading/Row Heading >> Right Click & choose

Delete (if column heading is selected Column is deleted, if row heading is selected Row is deleted)  

Shortcut Key  

       § Select a Single Cell >> CTRL “- (minus)” >> From Delete Dialog Box, Press “C” to

       Delete Entire Column & Press “R” to Delete Entire Row.

                 §                                         

Select Column/Row Heading >> CTRL “-(minus)”

INSERT ALTERNATE COLUMNS/ROWS

Use CTRL Key and select multiple Columns/Rows one by one then Press CTRL + “Plus(+)”. It inserts columns next to each selected column.

F4 – Repeats last action

F4 can also be used to insert Alternate Columns/Rows after inserting a

action.

Chapter 4: Cell References & Ranges

CELL REFERENCES

When we copy a reference from one cell to another, it gets updated automatically.

 

references will be updated to the corresponding row and column. By default, Excel creates relative cell references in formulas.

If you drag the formula in Row, row reference will change. If you drag the formula in Column, Column reference will change.

Example: F2=D2*E2 (D2 refers to the Column D & Row 2, E2 refers to Column E & Row 2).When this formula is copied in F5, Excel will have the updated references to the current row number F5=D5*E5

Absolute Reference

When you want to freeze a cell reference or you want a reference to change when you copy a formula, you can use absolute cell references. To make a cell reference absolute, we place dollar ($) sign before the column name and row number of the reference.

An absolute reference is one that does not change the cell references when copied to another cell. If you refer to a cell in a certain row and column and then copy that reference elsewhere, it will still refer to exactly the same cell or range.

“$” symbol is use lock the cell reference.

$A$1   Absolute column and Absolute Raw A$1   Relative Column and Relative Raw

$A1                                                                         Absolute Column and Relative Raw

A1                                                                           Relative Column and Relative Raw

Mixed Cell references

However at times you may want to freeze only the row or column in a cell reference. Example: $A1/A$1- Where both Absolute and Relative references are used.

F4 is the Shortcut Key to introduce Dollar Sign ($) or to lock a cell reference.

For example, if you enter =A1 to start a formula, pressing F4 converts the cell reference to =$A$1. Pressing F4 again converts it to =A$1. Pressing it again displays =$A1. Pressing it one more time returns to the original =A1. Keep pressing F4 until Excel displays the type of reference that you want.

Magical Shortcut F4

Outside Formula >> Repeats Last Action

Inside Formula >> Introduces “$” sign (Absolute Reference)

F2

Edit Cell

F4

To lock the cell or Range

CTRL + D

Fill Down

CTRL + R

Fill Right

CTRL + SHIFT + Arrow Keys

To select the Range or Table

FREQUENTLY USED EXCEL SHORTCUTS


3 different methods to select range

1. Using Mouse  

v   Single Range: Click & Drag from the Starting Cell address to Ending cellAddress

v   Multiple & Non-Adjacent Ranges: To Select 2nd, 3rd, 4th…. range PressCTRLKey, then click & drag from the starting cell address to ending cell addressfor

                                  each range.

2.Using Keyboard  

v Single Range: Use SHIFT Key + Arrow keys to select

               range.

v Multiple & Non-Adjacent Ranges: Press SHIFT +

F8, then use SHIFT +Arrow keys to select each

                                  non-adjacent range

3.Directly Enter Range Address in the Cell

NAMED RANGES

Many a times, when we write formulas/functions, we need to select a range of cells. However, doing this can be time consuming. Excel allows us to use a cell or a group of cells by its name. eg. Sum (Basic) sounds much easier compared to sum(H2:H101).However to do this, first we would need to name the range H2:H10 as Basic.

Creating a Named Range

2-ways you can create Named Ranges

•   Select the range and type the name in the Name Box. ?

?

•   Select a range >> Choose Formula Tab >> Under Defined Names >> Click Define Name >> New Name Dialog Box is opened >> Enter the Name you wish to give and Click OK ?

 

Chapter 5: Working with Formulas & Functions

FORMULAS

Formulais a calculation that can be entered into any cell and consists of a series of numbers (orcell references) and mathematical operators.

Formulas are equations that perform calculations on values. A formula starts with an equal sign    

(=). It contains at least two operands and at least one operation.

•   Begin EVERY formula with an equal sign (=). ?

•   After entering a formula, press ENTER to display the formula result. ?

Select the cell in which the formula result has to be displayed in. Formula can be constructed directly into that cell or in the formula bar. Close bracket is not mandatory for single Formula and it’s Mandatory for Nested Formulas.

Excel will calculate based on BODMAS RULE (Brackets, Order/Index (or Power), Division, Multiplication, Addition & Subtraction)

Example: If you type 2+3*4 in Excel end result will be 14 (Based on BODMAS RULE). To add 2&3 first and then multiply with 4, Use Brackets (2+3)*4 and follow BODMAS RULE result will be 20.

Operand in a formula can be functions, references or constants. Operators may be any arithmetic or logical operator

3 Ways of constructing formulas

?       ? If you know the range, directly type the range in the cell. (=C2+D2) ? ?  ? If you don’t know the range, Select the range by using mouse.?

?

You can also use Arrow Keys to select the range. (In certain places you cannot use mouse to select the previous cell because the existing cell content will overwrite  

the previous cell. In such cases only arrow keys can be used to select the range)

AutoFill the Formulas

   

•   Use an equal sign to begin a formula. ?

•   Specify the function name. ?

?

•   Enclose arguments within parentheses. Arguments are values on which you want to perform the calculation. Arguments can be either



?        numbers or cell reference having values to be added. ?

•   Use a comma to separate arguments.?

            The syntax of a function is as follows?              =Function_name(argument1,argument2,….) Example:

 =SUM (A10, B5: B10, 50, 37)

You don’t need to memorize all the functions available and the arguments necessary for each function. Instead, you can use Sigma sign (?) which is used for sum or click on the drop down for some more function like Max, Min, etc. Excel prompts you for required and optional arguments.

Shortcut: You can use "Alt + =" key combination to get the sum function on yourworksheet.

AutoComplete List

After you type the first letter of a function name, the AutoComplete list appears. There are two ways to pick functions from the AutoComplete

List:

•   Double –Click to pick the function from the list. ?

?

•   Use Arrow keys to select the function and then use tab key to pick the function from the list. ?

Once you pick the function from the AutoComplete List. Excel will complete the function name and enter the first parenthesis.

 

You can use the IF function to evaluate a condition. The IF function returns different values depending on whether the condition is true or false. The syntax for the IF function is:

If(logical_test, [Value_if_true], [Value_if_false])

The first argument is the condition that you want the function to evaluate. The second argument is the value to be returned if the condition is true and the third argument is the value to be returned if the condition is false. Second and third parameters are optional.

Example:

Examples:

Suppose you want to assign letter grades to numbers referenced by the name Average Score. See the following table. 

 

Tips: You can nest up to sixty four levels of If functions in a single formula.

IF with AND

AND is a logical function in excel which returns the combined truth value of two arguments or conditions. It returns false only when all the conditions listed are false.

Syntax:

IF with OR

OR is a logical function in excel which returns False if any one of the arguments returns false.

Syntax

Example:

Criteria for Quarterly Bonus is given to  everyone except people not met goal.

We would use the following formula.

=IF(NOT(H5="Not Met"),H5*$I$2,0)

 

Note the criteria argument is in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 20, "20", "=20", ">20", "East", "E*"

TEXT Functions
   

Date-Related Functions

END DATE – START DATE -To find the number of days between two dates

 

Chapter 6: VLOOKUP

V lookup is a function that searches for a value (lookup value) in the leftmost column of a given database (table array) and returns a value in the same row from a column you specify. 

Vlookup is used to Match data & Retrieve the result. A lookup formula essentially returns a value from a table by looking up another related value.

Syntax:

VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)

The VLOOKUP function’s arguments are as follows:

•   Lookup value is the value to be found in the first column of the table. It is the value thatyou are looking for. Lookup_value can be a value, a reference or a text string. ?

?

•   Table array is the table of information in which data is looked up. It is the sourcedatabase. Use a reference to a range or a range name. ?

?

•   Col_index_num is the column number in table_array from which the matching valuemust be returned. ?

?

•   Range lookup is a logical value that specifies whether you want VLOOKUP to find anexact match or an approximate match. If Range Lookup is set as FALSE or 0, VLOOKUP will find an exact match. If exact match is not found, the error value #N/A is returned. If it is set to TRUE or non?zero, it finds the nearest value that is less than lookup value. ?

In the above figure lookup value is in Column D; table array G3:H12; Column index number is 2.

Example: =VLOOKUP(D3,G3:H12,2, FALSE)

The lookup table resides in a range that consists of three columns (G3:H12). Because the column index number for the VLOOKUP function is 2, the formula returns the corresponding value in the second column of the lookup table. Note that an exact match is required.

Excel’s data validation feature enables you to set up certain rules that dictate what can be entered into a cell. Restrict the cells to accept only certain value based on condition.

Data Validation is a process which restricts the users from entering invalid data for individual cells or cell ranges. It limits the data entry to a particular type, such as whole numbers, decimal numbers or text and sets limit on valid entries.

For example, you may want to limit data entry in a particular cell to whole numbers between 1 and 12. If the user makes an invalid entry, you can display a custom message.

Chapter7: Data validation

SETTING DATA VALIDATION

? Select the Cells to which Data validation to be applied ?

?

? Click Data Tab >> Data Tools Group >> Click Data Validation >> Data Validation Dialog Box is Opened ?

?

•   On the Settings tab, Data Validation dialog box enables you to specify a wide variety of data validation criteria. From the Allow list, select

?       a data validation option. ?

•   Any Value: Selecting this option removes any existing data validation.?

?

•   Whole Number: The user must enter a whole number. For example, wholenumber greater than or equal to 100. ?

?

•   Decimal: The user must enter a number. For example, greater

?                  than or equal to0 and less than or equal to 1. ?

•   List: The user must choose from a list of entries you provide.?

?

•   Date: The user must enter a valid date. For example, greater than or equal toJanuary 1, 2007, and less than or equal to

?                  December 31, 2007. ?

•   Time: The user must enter a valid time. For example, greater than 12:00 p.m.?

?

•   Text Length: The length of the data (number of characters) is limited. Forexample, length of the entered data be 1 (a single alphanumeric character). ?

?

•   Custom: You must supply a logical formula that determines the validity of theuser’s entry (a logical formula returns either True or False). You can enter the formula directly into the Formula control or you can specify a cell reference that contains a formula. ?

?

•   From the Data list, select the operator you want. Then complete the remaining entries ?

?

•   Enter the Input Message if required in Input Message tab. Enter the error message if required in Error Alert tab. Click OK to set the validation rule and close the dialog box. ?

USAGE OF DATA VALIDATION

 

Allow Numbes within Limits

In the Allow box, click Whole Number or Decimal. >> In the Data box, select the type of restriction you want. For example, to set upper and lower limits, select Between. >> Enter the minimum, maximum, or specific value to allow.

Allow Dates or Times within a Timeframe

In the Allow box, select Date or Time. >> In the Data box, select the type of restriction you want. For example, to allow dates after a certain day, select greater than. >> Enter the start, end, or specific date or time to allow.

Allow Text of a Specified Length

In the Allow box, click Text Length. >> In the Data box, click the type of restriction you want. For example, to allow up to a certain number of characters, click less than or equal to. >> Enter the minimum, maximum, or specific length for the text.

Chapter 8: Security & Protection

To prevent a user from accidentally or deliberately changing, moving, or deleting important data from a worksheet or workbook, you can protect certain worksheet or workbook elements, with or without a password. You can remove the protection from a worksheet as needed.

•   Protecting worksheet elements ?

•   Using a password to control access to protected elements ?

•   Protecting the structure and windows of a workbook ?

?

?

Protecting Worksheet

•   Home Tab >> Under Cells Group >> Click Down arrow below Format Button >> Click Protect Sheet from the Drop-Down >>

Protect Sheet Dialog Box Display >> Type Password & Click

Ok. ?

?

•   Review Tab >> Under Changes Group >> Click Protect Worksheet >> Protect Sheet Dialog Box Display >> Type Password & Click Ok. ?

STEP 1: Select the range of cells to be accessible >> Right Click &

Select Format Cell>> Format Cells Dialog Box is Opened >>

Activate Protection Tab >> UnCheck  

?Locked? >> Click OK

Follow Step 2 only if you wish to hide your formulae or proceed to step 3.

STEP 2: Select the range of cells with formulae to hide from users >>

Rightclick andSelect Format Cells >> Format Cells Dialog Box is Opened >> Activate Protection Tab      

>> Check ?Hidden? check box along with ?locked? >> Click OK

Follow step 3 to password protect the worksheet.

STEP 3: Click Review Tab >> In the Changes ribbon group, click Protect Sheet toopen the Protect Sheet dialog box >> Type a password and click OK.

Protecting a Workbook

Workbook level protection can be done in two ways:

Ø Workbook structure (prevent changes like worksheets being moved, deleted, inserted, hidden, unhidden, or renamed) 

Ø Workbook window (ensure that a workbook’s window is the same size and position each time it is opened.) 

To protect a workbook,

Click Review Tab >> In the Changes ribbon group, click Protect Workbook >>

Protect Workbook dialog box appears >> Select either or both the options (Structure or Windows) >> To prevent others from removing workbook protection, you can set a password >> Click OK.

Password Protecting a File

File can also be password such that any user would be asked for a password before he is able to view or modify your file.

To do this, follow the steps below,

Click on office button > Save as >> In the save as dialog box, click on tools > General options >> Set the password to open or modify as the case may be >> Save the file.

 

Chapter 9: Sorting & Filtering in Excel

DATA SORT

Sorting rearranges the rows based on the contents of a particular column.

Note: If your spreadsheet contains formulas, be careful when using the sort feature. Formulasrely on cell references to perform their calculations and moving the data with the sort feature may destroy these references.

To sort a list of data:

? Select a single cell in the column containing the data you want to sort. ?

?

? Select the Home tab, Under the Editing group, press the Sort and Filtering button and select the order you want your data to be sorted. ?

?

Note:If you select an entire column, Excel will sort only that column and will mismatchthe data contained in the other columns. ?

Single Level Sorting

Sorting done with respect to the contents of one column

 Select any cell in the column by which you want to sort >> Activate the Data Tab >> In the Sort & Filter group, click the Sort Ascending or Sort Descending button >> This will sort the entire database. 

Multi-Level Sorting

Sorting done with respect to the contents of multiple columns. Data sort can be done with any number of columns. STEP 1: Open Sort Dialog Box

Sort Dialog Box can open by three ways:

Sort Dialog Box can open by three ways:

 Choose Data Tab >> In the Sort & Filter Group, >> Click Sort Button >> Sort dialog box is opened. 

•   Choose Home Tab ? Editing ? Sort & Filter ? Custom

Sort. ?

?

•   Right-click any cell in the table and choose Sort ?

Custom Sort from ?

?

the shortcut menu. In the Sort Dialog box, click the Add Level button to insert any number of ?

STEP 2: Add Levels to Sorting

•   From the Sort by list, select the column heading of the column by which you want to sort the list, and then select a sorting order. All records will be sorted first based on the column and the sorting order you selected. ?



?

•   Then click “Add Level” and select the next column by which you want to sort. If necessary, add more "Then by" fields by clicking "Add ? Level." ?

•   When all "Then by" fields are complete, click OK. ?

?

?

Customized Sorting

When we sort the data, region wise, it sorts either in the ascending or descending order. However, if you want to sort your data in a customized order, for Example East, West North, and South, we would need to perform a custom sort. 

•   Select any cell in the list >> Data Tab >> In Sort & Filter Group, click sort >> Sort Dialog Box is opened >> From the Sort By list, select the column heading of the column by which you want to sort the list >> Select Custom List under order drop down Menu >> Custom List Dialog Box gets opened >> Type the sequence by which you wish to sort >> Click Add ?

•   Click on any cell in your spreadsheet. ?

?

•   Select the Home tab, Under the Editing group, press the Sort and Filtering button and select the Filter button. ?

?

•   Drop-down menus will appear next to each cell heading. Clicking on any drop-down menu will provide you with options for sorting or filtering. ?

For example:

If you want only Chennai & Bangalore Region to be displayed under Area Column, Place the check mark next to Chennai & Bangalore alone, click OK.

You can filter a table using any number of columns.

For example, you may want to see only the Chennai listings in which the “Type” is Single Family. Just repeat the operation using the “Type” column. Tables will display only the rows inwhich the “Area” is Chennai and the “Type” is Single Family.

Number, Text or Date Filters

Once you add filter to data, you also get a Number, Text or Date Filter Option in each fields depending on the type of data in that column. These can be used for field specific filtering like ?Begins with?, ?Contains? for text fields, ?Greater than?, ?Lesser than?,

?Between? for number fields or ?Before?, ?After? for date fields.

From the drop-down list of the column for which you want to create criteria, choose Text Filters, Date Filters or Number Filters, click on Custom to display the Custom AutoFilter dialog box.

 

                                      Data Filter                                                  Text Filter

Advanced Filter

While using Advanced Filter, we need to have a criteria range and a list range. List range is your database. To create a criteria range, we need to make a copy of the column header of the database.

Ø In the List Range box, select the cell range you want to filter. The cell  range must include the associated column headings. 

Ø In the Criteria Range box, select the cell range that contains your criteria. 

Chapter: 10 Pivot Table

Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data. Pivot Tables are interactive tables that allow the user to group and summarize large amounts of data in a concise, tabular format for easier reporting and analysis.

A Pivot Table is an interactive worksheet based table that quickly summarizes large amounts of data using the format and calculation methods you choose. It is called a Pivot Table because you can rotate its row and column headings around the core data area to give you different views of the source data. As source data changes, you can update a pivot table. It resides on a worksheet thus; you can integrate a Pivot Table into a larger worksheet model using standard formulas.

Grouping and summarizing large amount of data into a simple report

First identify the common field column and draw sample report & create the pivot.

Group the data to create monthly, quarterly and yearly reports. Shortcut for date grouping (F12)

Steps to create pivot table

 To insert a new pivot table in to your spreadsheet, go to Insert ribbon  and click pivot table icon and select pivot table option.  

 Excel will display a pivot table wizard where you can specify the table range & choose the target location where pivot table has to be placed. Select “New worksheet” option and your pivot table will be placed in newly created worksheet or select “Existing Worksheet” with location to place pivot table in the current worksheet.

?

 

?

Examining PivotTables

The data on which a PivotTable is based is called the Source Data. Each column represents a field or category of information, which you can assign to different parts of the PivotTable to determine how the data is arranged. We can add four types of fields Report Filter, Row Labels, Column Labels & ? Values.

You can use a PivotChart to graphically display data from a PivotTable. A single PivotChart provides different views of the same data .When you create a PivotChart, the row fields of the PivotTable become the categories, and the column fields become the series.

To create a PivotChart,

 Select any cell within a PivotTable >> Click Chart in the Tools group on the Options tab >> Select options for the chart >> click OK. 

You can also create a new PivotChart and PivotTable at the same time by selecting a cell in the source data, and selecting PivotChart from the PivotTable list in the Tables group on the Insert tab.

 
Chapter:11 Conditional Formatting

Conditional formatting enables you to apply cell formatting selectively and automatically, basedon the contents of the cells.

Conditional formatting allows you to change font style, underline, and color. Using conditional formatting, you can also apply strike-through as well as borders and shading to the cells. However, you cannot change the font or the font size of the contents in the cell.

For example, you can set cell background color as Red for Negative values & Green for Positive values. To apply a conditional formatting

?? Click the cell you want to format. ?

?? Click Home Tab >> Styles >> Conditional Formatting ?

•   Then use any of the choices below as per your need, ?

?

•   Highlight Cell Rules: Rules include highlighting cells that are greater than a particularvalue, between two values, contain specific text string, or are duplicated. ?

?

•   Top Bottom Rules: Rules include highlighting the top 10 items, the items in the bottom20 percent, and items that are above average. ?

?

•   Data Bars: Applies graphic bars directly in the cells, proportional to the cell’s value.?

?

•   Color Scales: Applies background color, proportional to the cell’s value.?

?

•   Icon Sets: Displays icons directly in the cells. The icons depend on the cell’s value.?

?

•   New Rule: Enables you to specify other conditional formatting rules, including rulesbased on a logical formula. ?

?

•   Clear Rules: Deletes all the conditional formatting rules from the selected cells.?

?

•   Manage Rules: Displays the Conditional Formatting Rules Manager dialog box, in whichyou create new conditional formatting rules, edit ? rules, or delete rules. ?

 

                             Chapter 12:   Page setup & Print                              

When you want a hard copy of worksheet data, Excel gives you many tools for choosing how that printout will look. Swapping the page orientation between portrait and landscape, scaling the size of the printout and choosing whether to print worksheet gridlines are but a few of the options that can be set. Excel automatically assigns headers and footers to your worksheet data, but you can change or enhance these very easily.

Print Preview

Before sending anything to print, it is always a good idea to preview what you will see on the page. Print Preview shows you what the worksheet will look like when it is printed.

To access Print Preview: Select Office Button >> Print >> Print Preview

Page Setup

When you want to alter headers and footers, orientation and so on, you use the Page Setup dialog box. You can open Page Setup dialog box by clicking dialog box launcher from the Page Setup Group, under Page Layout Tab. There are four tabs within this dialog; each gives oneaspect of your printout.

PAGE TAB  

o    Orientation: Use this setting to swap between portrait and landscape printouts.o Scaling: The size of your printout can be increased or decreased           manually.

o    Paper Size: Size of the paper in which you wish to print the content can beselected. 

*  Margin Tab: The Margins tab lets you set top, bottom, left and right  margins and centeryour data horizontally and vertically on the page. 

*  Header & Footer Tab: use this setting to include the Header/Footer in your print can also add Picture instead of text to header &  footer. 

SHEET TAB: Use this tab to set a print areao Print Area- Use this to set the range of cells that you want to print. If

            you do notset a Print Area, Excel will print all the data on the active worksheet.

o Print Titles: Rows to Repeat at Top, Columns to Repeat at Left.When yourprintouts run to several pages you may have column

repeat when you have a multi?page printout. 

Print – Collate
 

Chapter 13:  Macros?

You can use macros to automate complex and repetitive tasks. A Macro is a series of instructions that execute automatically with a single command.

 

Recording a Macro

Just as a Tape Recorder can be used to record sounds which can be played back later, so the Macro Recorder can record your actions into a Macro.

These Macros can be run whenever you wish, automatically repeating your recorded actions and so saving your time and effort.

•   Recording a Macro which uses Absolute Cell References ?

?

•   Recording a Macro which uses Relative Cell References

 

begin with a letter and cannot contain spaces. They can include letters,

            numbers, and underscores. 

 Click OK to start recording the macro. 

 Perform the actions you want to include in the macro. As you work, Excel records the sequence of steps. 

When you’re finished, click the Stop Recording button on the status bar.

 This Workbook – Records a macro in the current workbook 

 

Macros that are contained on all currently open workbooks are displayed in the list. To run a macro, select macro from the list and click Run. The Macro dialog box provides several additional capabilities:

Step: Click this button to run the macro step by step. Stepping through a macro isuseful for debugging purposes

Edit: The module containing the macro is activated, and the macro is displayed inthe visual basic editor for editing purposes.

The above image shows the editor when book1 is open. If you have macros in this workbook, you would see an additional folder in the project explorer called "Modules". All macros in a file are stored in Modules. These modules are by default named as Module1, Module2 etc.

 



23