CREATING A PIVOT (MS EXCEL)

Creating Pivot Tables
A pivot table is an interactive worksheet table. You can create a pivot table to quickly summarize, organize, and analyze large amounts of data. You will also add fields to and delete fields from a pivot table. The data that you use to create a pivot table is called the source data. Source data can come from Excel’s data, or an external database file.

E.g. The source data for the pivot table in this workbook is contained in the Sales worksheet. There is a large amount of data in this worksheet. The data represents sales projections for the Bem T. Group’s technology journals. This worksheet has nearly 1,200 rows of information. Getting specific information about a particular year, month, or product could be quite involved if your only options were to cut, paste, and write formulas. Fortunately, these are not your only options. Activate the Pivot Table sheet. This pivot table summarizes the data from the Sales sheet. The pivot table very clearly shows earned income for each month and year, for all the products. Excel also provides a special toolbar for manipulating pivot tables. To specify what portions of the source data to use in the table, you choose fields and items. Fields are categories of data, and items are subcategories in a field. For example, 1988 would be an item in a field called Year.

There are two types of fields: row/column/page fields, and data fields. Row/column/page fields usually contain a limited set of text values; for example, Year contains 1998, 1999, and 2000. Data fields usually contain numeric data used for summarizing, for example, sales amounts. The data presentation is called a pivot table because you can rotate the row and column headings to create different data configurations. You can also move the page data into the table to create different configurations as well.

E.g. creating a pivot table. Choose the cell that contains the name Year, choose Data, Pivot Table And PivotChart Report. (The PivotTable And PivotChart Wizard – step 1 of 3 will display and you need to confirm that the source data is an Excel list. This option is selected by default). Click on Next. In step 2 of 3, the wizard will requires you to specify the location of the data. Because you selected cell A4 before starting the wizard, Excel automatically selected the range of contiguous cells surrounding the active cell. Click on Next. In step 3 0f 3, the wizard will ask you whether you want the pivot table in a new or existing worksheet. The default is a new worksheet. Click on Finish. Notice that the field names from the list all appear at the bottom of the toolbar. You drag these onto the various areas of the pivot table grid to create the pivot table. Callouts have been added to explain the purpose of the various areas, but the best way to understand a pivot table is to build one.

Click on the Simulation button to observe the Year field being dragged from the PivotTable toolbar to the Drop Column Here area of the pivot table grid. You can also simply move the copies field out by deleting it from the table, and then move another in to add it. Point to the Sum of Copies field in cell A3, and drag it to cell A2. Observe the mouse pointer as you drag. You will remove any field from a pivot table by dragging it to any cell outside of the table. E.g. Drag the Income field from the PivotTable toolbar to the Drop Data items Here area. Drag the Product field from the PivotTable toolbar to the Drop page Fields Here area of the pivot table. Click on the Product drop-down arrow, select 2022, and click on Ok. consider what it would take to derive this level of detail by writing your own formulas.

Modifying Pivot Tables
After you create a pivot table, you’ll probably need to adjust it to make it work for your specific requirements. Here you will change and reorganize pivot table data, alter pivot table field information, and refresh pivot table data. An advantage of using pivot tables to analyze information is that pivot tables can be manipulated and changed easily. Drag the Product field from the Page Field area to the Row Field area, as indicated. Drag the Month field from the Row Field area to the Drop Page Fields Here area. In the Month drop-down box, select Jan. click on Ok.

When you create a pivot table, Excel formats the data by using the number format included in the Normal style for the worksheet. E.g. Right-click on the Sum Of Income field button and choose Field Settings. The PivotTable Field dialog box is displayed. From here you can change several settings for the selected field. In the Name box, type Total Income click on the Number button. Format the cells as Currency with 0 decimal places. Click on OK twice.

If your source data changes after you have created a pivot table, you will need to refresh the pivot table to reflect the changes to the source data. E.g. The value in cell B5 contains the January 1999 total income, which is $4,748. Activate the Sales sheet. Select the cell--- and change the value to 2000. Activate Sheet1 once again. On the PivotTable toolbar, click on the Refresh Data button.

Regards,

BEM T. CELESTINE

CREATING A PIVOT (MS EXCEL)

Creating Pivot Tables
A pivot table is an interactive worksheet table. You can create a pivot table to quickly summarize, organize, and analyze large amounts of data. You will also add fields to and delete fields from a pivot table. The data that you use to create a pivot table is called the source data. Source data can come from Excel’s data, or an external database file.

E.g. The source data for the pivot table in this workbook is contained in the Sales worksheet. There is a large amount of data in this worksheet. The data represents sales projections for the Bem T. Group’s technology journals. This worksheet has nearly 1,200 rows of information. Getting specific information about a particular year, month, or product could be quite involved if your only options were to cut, paste, and write formulas. Fortunately, these are not your only options. Activate the Pivot Table sheet. This pivot table summarizes the data from the Sales sheet. The pivot table very clearly shows earned income for each month and year, for all the products. Excel also provides a special toolbar for manipulating pivot tables. To specify what portions of the source data to use in the table, you choose fields and items. Fields are categories of data, and items are subcategories in a field. For example, 1988 would be an item in a field called Year.

There are two types of fields: row/column/page fields, and data fields. Row/column/page fields usually contain a limited set of text values; for example, Year contains 1998, 1999, and 2000. Data fields usually contain numeric data used for summarizing, for example, sales amounts. The data presentation is called a pivot table because you can rotate the row and column headings to create different data configurations. You can also move the page data into the table to create different configurations as well.

E.g. creating a pivot table. Choose the cell that contains the name Year, choose Data, Pivot Table And PivotChart Report. (The PivotTable And PivotChart Wizard – step 1 of 3 will display and you need to confirm that the source data is an Excel list. This option is selected by default). Click on Next. In step 2 of 3, the wizard will requires you to specify the location of the data. Because you selected cell A4 before starting the wizard, Excel automatically selected the range of contiguous cells surrounding the active cell. Click on Next. In step 3 0f 3, the wizard will ask you whether you want the pivot table in a new or existing worksheet. The default is a new worksheet. Click on Finish. Notice that the field names from the list all appear at the bottom of the toolbar. You drag these onto the various areas of the pivot table grid to create the pivot table. Callouts have been added to explain the purpose of the various areas, but the best way to understand a pivot table is to build one.

Click on the Simulation button to observe the Year field being dragged from the PivotTable toolbar to the Drop Column Here area of the pivot table grid. You can also simply move the copies field out by deleting it from the table, and then move another in to add it. Point to the Sum of Copies field in cell A3, and drag it to cell A2. Observe the mouse pointer as you drag. You will remove any field from a pivot table by dragging it to any cell outside of the table. E.g. Drag the Income field from the PivotTable toolbar to the Drop Data items Here area. Drag the Product field from the PivotTable toolbar to the Drop page Fields Here area of the pivot table. Click on the Product drop-down arrow, select 2022, and click on Ok. consider what it would take to derive this level of detail by writing your own formulas.

Modifying Pivot Tables
After you create a pivot table, you’ll probably need to adjust it to make it work for your specific requirements. Here you will change and reorganize pivot table data, alter pivot table field information, and refresh pivot table data. An advantage of using pivot tables to analyze information is that pivot tables can be manipulated and changed easily. Drag the Product field from the Page Field area to the Row Field area, as indicated. Drag the Month field from the Row Field area to the Drop Page Fields Here area. In the Month drop-down box, select Jan. click on Ok.

When you create a pivot table, Excel formats the data by using the number format included in the Normal style for the worksheet. E.g. Right-click on the Sum Of Income field button and choose Field Settings. The PivotTable Field dialog box is displayed. From here you can change several settings for the selected field. In the Name box, type Total Income click on the Number button. Format the cells as Currency with 0 decimal places. Click on OK twice.

If your source data changes after you have created a pivot table, you will need to refresh the pivot table to reflect the changes to the source data. E.g. The value in cell B5 contains the January 1999 total income, which is $4,748. Activate the Sales sheet. Select the cell--- and change the value to 2000. Activate Sheet1 once again. On the PivotTable toolbar, click on the Refresh Data button.

Regards,

BEM T. CELESTINE

Using the IF and VLOOKUP Functions (MS EXCEL

Using the IF and VLOOKUP Functions
The IF function is used to create a formula that returns different results under different conditions. You use the VLOOKUP function to create a formula that picks a return value from a range of values depending on an input value. E.g. three salespeople did not meet the quota of $60,000, and therefore they should not receive a commission. However, the current formula in the Comm column calculates a commission for each salesperson. The syntax for the IF function, and the practical application example. You use the IF function to perform one calculation if a certain condition is true, and a different calculation if that condition is false.

IF(logical_test, value_if_true, value_if_false)
Logical_test is any value or expression resulting in TRUE or FALSE.

Value_if_true is the value (or formula resulting in a value) that will be returned if logical_test is TRUE.

Value_if _false is the value (or formula resulting in a value) that will be returned if logical_test is FALSE. For example, given the following function: IF(A4>5, A4/2,0)
If A4 =8, then logical_test (A4>5) is TRUE, and the function returns value_if_true (A4/2).
If A4 = 2, then logical_test (A4>5) is FALSE, and the function returns value_if_false (0).

The range F6:F10 has been cleared. E.g. to build a new formula that awards commissions only to those salespeople who meet the quota. Click on the Paste Function button. In the Function Category list, select Logical. In the Function Name list, select IF. Click on OK.

Now completing the three sections of the IF function dialog box. In the Logical_test box, type E6>=Quota in the value_if_true box, type E6*Comm_Rate in the Value_if_false box, type 0 (this formula will test whether the sales total value is greater than or equal to the quota value. If the test is true, a commission value will be calculated. If the test is false, the commission value will be assigned a zero value). Click on Ok.

A function’s arguments can be constant values or formulas. When a function’s argument is another function, it’s called a nested function. Nested IF functions have their place, but they also have their shortcomings. Rather than nest IF functions, you can use the VLOOKUP function. The VLOOKUP function finds and returns a value located in a certain row and column of a lookup table, as long as the table contains an orderly arrangement of information.

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Lookup_value is the value that will be looked for in the first column the lookup table. It can be a value, reference, or text string.

Table_array is a reference to (or name of a reference to) the lookup table.

Vol_index_num is the column number in the lookup table from which value should be returned.
Range_lookup is an optional argument that specifies whether you require an exact match or an approximate match.

E.g. select the Bonus column contents. Click on the Paste Function button. In the Function Category list, select Lookup & Reference. Click on the vertical scroll bar below the scroll button. In the Function Name list, select VLOOKUP. (The structure and explanation of the VLOOKUP function will display at the bottom of the paste Function dialog box). Click on Ok. Then providing settings for the first three sections. In the Lookup_value box, type the cell ----  In the Table_array box, type Bonus   In the Col_index_num box, type 2   click on OK. The benefit of using the VLOOKUP function comes when you delete, add, or change the information within your lookup table. You won’t need to change the formulas because they’ll be managed automatically.

TEMPLATES AND NAMES (MS EXCE)

Using Templates
Templates make it easy for you to create workbooks that share common characteristics. Once you’ve created the template workbook, you’ll be able to use it as a basis for future, similar workbooks. Templates should contain only labels and formulas, and not raw data. Templates are also most useful when they do not include specific dates or references to the time of year. E.g. with Procedures: press the Delete key to delete the raw data in the selected range. Edit cell A3 to read Quarter? You need to save the generic workbook as a template. Template workbooks are saved to the Template system folder. Choose File, Save As. The Save As dialog box will display. From the Save As Type list, select Template (*.xlt). when you specify the Template format, Excel automatically changes the current folder to the Templates folder. All templates should be saved to this folder. Change the file name to ---------. Click on Save. Close the file.

You can now open a new Excel workbook by using the template you just saved. When you open a template file, Excel opens a copy of the template file and leaves the original intact. Excel also creates a default file name by using the template name and a sequential umber. Choose File, New. The New dialog box will display, providing you with a choice of templates on which to base your new workbook. Select My Expense Report.xlt. click on Ok.

Notice that when you edit a template and save to another name it does not affect the original file.  

Using Names

A name is a description that you assign to a group of cells as an alternative to its cell reference. You can use names to make formulas more powerful and flexible. Names should follow these guidelines.

The first character must be text or an underscore character. The remaining characters can be text, numbers, periods, and the underscore character. Names can consist of up to 255 characters.
A name cannot contain spaces.

You can use lowercase letters in names; however, Excel does not distinguish between uppercase and lowercase letters when using names.

E.g. Cell F6 contains a formula that calculates a commission. The commission rate and quota are contained within a box on the worksheet. The values in the Bonus column are based on the bonus table data. The formula in cell F6 uses the name Comm_Rate instead of a number or cell reference. In the Name Box drop-down list, select Comm_Rate. Choose Insert, Name, Define. The Define Name dialog box enables you to define new names and to see names that are already assigned.

Commission_Rate is displayed in the text field because Excel assumes that you want to use the text in the cell adjacent to the active cell to define a new name. however, Comm_Rate has already been define. In the Names In Workbook list, select Comm_Rate. Click on Close.

Regards,

BEM T.

TEMPLATES AND NAMES (MS EXCE)

Using Templates
Templates make it easy for you to create workbooks that share common characteristics. Once you’ve created the template workbook, you’ll be able to use it as a basis for future, similar workbooks. Templates should contain only labels and formulas, and not raw data. Templates are also most useful when they do not include specific dates or references to the time of year. E.g. with Procedures: press the Delete key to delete the raw data in the selected range. Edit cell A3 to read Quarter? You need to save the generic workbook as a template. Template workbooks are saved to the Template system folder. Choose File, Save As. The Save As dialog box will display. From the Save As Type list, select Template (*.xlt). when you specify the Template format, Excel automatically changes the current folder to the Templates folder. All templates should be saved to this folder. Change the file name to ---------. Click on Save. Close the file.

You can now open a new Excel workbook by using the template you just saved. When you open a template file, Excel opens a copy of the template file and leaves the original intact. Excel also creates a default file name by using the template name and a sequential umber. Choose File, New. The New dialog box will display, providing you with a choice of templates on which to base your new workbook. Select My Expense Report.xlt. click on Ok.

Notice that when you edit a template and save to another name it does not affect the original file.  

Using Names

A name is a description that you assign to a group of cells as an alternative to its cell reference. You can use names to make formulas more powerful and flexible. Names should follow these guidelines.

The first character must be text or an underscore character. The remaining characters can be text, numbers, periods, and the underscore character. Names can consist of up to 255 characters.
A name cannot contain spaces.

You can use lowercase letters in names; however, Excel does not distinguish between uppercase and lowercase letters when using names.

E.g. Cell F6 contains a formula that calculates a commission. The commission rate and quota are contained within a box on the worksheet. The values in the Bonus column are based on the bonus table data. The formula in cell F6 uses the name Comm_Rate instead of a number or cell reference. In the Name Box drop-down list, select Comm_Rate. Choose Insert, Name, Define. The Define Name dialog box enables you to define new names and to see names that are already assigned.

Commission_Rate is displayed in the text field because Excel assumes that you want to use the text in the cell adjacent to the active cell to define a new name. however, Comm_Rate has already been define. In the Names In Workbook list, select Comm_Rate. Click on Close.

Regards,

BEM T.

Creating and Using Styles (MS EXCEL)

Creating and Using Styles

A combination of formats for a cell is called a style. You can use styles to simplify the formatting of your documents. You’ll find that by using styles, you can provide a consistent look to all your documents.

Displaying the formatting attributes that you can specify in a cell style. Notice that cell A1 is selected. Procedure: choose Format, Style. The Style dialog box will display and it will show the six formatting attributes. These attributes are the settings of Normal style. By default, all cells in all worksheets use the Normal style. The settings of the Normal style do not precisely match the formatting of the current cell, A1. That’s because a cell can contain additional formatting on top of the style that is applied to it. If you assign a new style name to the combination of formats in cell A1, you’ll create a style. Procedure: in the Style Name box, type My Title, click on Ok.

when you create a style by assigning a name to a cell that has already been formatted, you are creating a style “by example.” Using the new style. Procedure: 1. select cell A3, choose Format, Style. From the Style Name drop-down list, select My Title. Click on Ok. another method you can use to create styles is called “by definition”. With this method, you assign the attributes you want from the Style dialog box. Procedure: select cell A5. As you press the Shift key, select cell D5. Display the style dialog box. Using the “by definition” method to create a style. In the Style Name box, type My Heading click on Modify. The Format cells dialog box will display. Select the Font tab. In the Font box, type Times New Roman select the Patterns tab. In the Cell Shadding, color area, select the light shade of gray. Click on Ok. click OK again. Click outside to deselect the cell.

You can apply cell formatting to any cell without affecting the style formatting, because any cell can contain additional formatting on top of the style that is applied to it. To redefine a style, you must change the style’s attributes by using the Style dialog box.

Regards,

BEM T. CELESTINE

TOOL BARS (MS EXCEL)

Working with Built-In and Custom Toolbars

You can add, delete, or move buttons on Excel’s built-in toolbars.

E.g. adding and rearranging buttons on the standard toolbar. Procedure: choose Tools, Customize. (The customize dialog will display. The standard and Formatting toolbars will display in the background when the Customize dialog box is open). Select the Commands tab. (This tab lists all command in all of Excel’s menus).

E.g. adding one of these commands as a button on the standard toolbar. From the Categories list, select Edit. On the vertical scrollbar, click three times below the button. Select the Clear Formatting button. (You can always get additional information about a command once it is selected). Click on the Description button. When you’re ready, click in the indicated area to close the description.

E.g.2. placing the Clear Formatting command on the Standard toolbar. Procedure: drag and drop the Clear Formatting button on the standard toolbar where indicated. To remove, Point to the AutoSum button and drag it off the toolbar.

In addition to adding and deleting buttons on toolbars, you can rearrange button positions. E.g. on the Standard toolbar, drag the Spelling button to the left of the Undo button. Close the Customize dialog box.

In the worksheet, cell A1 is selected. Click on the Clear Formatting button. (The formatting in cell A1 has been cleared). Reapplying the original formatting. Click on the Undo button.

You can restore any of the built-in toolbars to their original state at anytime. Right click on the Standard toolbar in the indicated area. On the Toolbar shortcut menu, choose Customize. (The Customize dialog will display and the Toolbars tab will be selected). In the Toolbars list, the Standard toolbar is selected. Click on Reset. (A dialog box prompts you to verify that you want to reset the toolbar). Click on Ok. click on Close.

How to create custom toolbars. From the Toolbars shortcut menu, choose Customize. Uncheck Standard to hide the Standard toolbar. Click on New. (A New Toolbar dialog box will display). Type My Toolbar and click on Ok. adding some buttons to it. On the commands tab, select File from the Categories list. Drag the Open  button to the new toolbar. The toolbar width automatically adjusts to accommodate the button that you added. Toolbars can also be docked at the edge of the application window.

You can change a toolbar’s position by either dragging it or by double-clicking on the toolbar’s background. You can also move a toolbar out of a docked position by pointing to the handle on the left side, pressing the mouse button, and dragging. When you’re finished with a custom toolbar, you can delete it. Procedure: Select the Toolbars tab. Scroll the Toolbars list and select My Toolbar. Click on Delete. Click on Ok.

Redisplaying the Standard toolbar. Scroll the Toolbars list and check Standard. Close the Customize dialog box. 

Regards,

BEM T.

FILTERING LIST (MS. EXCEL)

Managing a Filtered List
In Excel, you can work with a filtered list the same way you work with an entire list. In Filter mode, some Excel commands affect only the filtered list. For example, you can sort the sets of data in a filtered list to display them in a particular order without affecting the sets of data that are not included in the filtered list.

Example. The Filter.xls workbook is open. Filter the Payroll list to display only records for the Children’s department, then sort the filtered list. Choose Data, Filter, AutoFilter to display the drop-down arrows for each column. Display the DEPT drop-down list, select Children’s. choose Data, Sort. In the Sort By dialog box, select DIVISION. Verify that Ascending is selected. In the first Then By box, select LAST. Verify that Ascending is selected. In the second Then By box, select FIRST.  Verify that Ascending is selected. Click on Ok.

The AutoSum button works with a filtered list just as it does with the entire list. However, if you click on the AutoSum button in Filter mode, Excel does not use the SUM function. Instead, it uses the SUBTOTAL function. The SUBTOTAL function enables the sum to change according to the filtered data that is displayed. He function updates when you change the filter or display the entire list to  include all sets of data. E.g. using the AutoSum button on our filtered list. On the vertical scroll bar, click in the area below the scroll box. Select cell K99. Click on the AutoSum button. Press Enter. The value will display. (The SUBTOTAL function was entered. When you use the AutoSum button on a filtered list, Excel uses the SUBTOTAL function instead of the SUM function).

Regards,
Bem T. Celestine

SUNDAY MASS READINGS

Are you a catholic? Do you go to church? Here is the Catholic readings.

First Reading
Job 38:1, 8-11
1 Then the LORD answered Job out of the whirlwind: 8 "Or who shut in the sea with doors, when it burst forth from
the womb; 9 when I made clouds its garment, and thick darkness its swaddling band, 10 and prescribed bounds for it, and set bars and doors, 11 and said, `Thus far shall you come, and no farther, and here shall your proud waves be stayed'?

Responsorial Psalm
Psalms 107:23-26, 28-31
23 Some went down to the sea in ships, doing business on the great waters; 24 they saw the deeds of the LORD, his wondrous works in the deep. 25 For he commanded, and raised the stormy wind, which lifted up the waves of the sea. 26 They mounted up to heaven, they went down to the depths; their courage melted away in their evil plight; 28 Then they cried to the LORD in their trouble, and he delivered them from their distress; 29 he made the storm be still, and the waves of the sea were hushed. 30 Then they were glad because they had quiet, and he brought them to their desired haven. 31 Let them thank the LORD for his steadfast love, for his wonderful works to the sons of men!

Second Reading
2 Corinthians 5:14-17
14 For the love of Christ controls us, because we are convinced that one has died for all; therefore all have died. 15 And he died for all, that those who live might live no longer for themselves but for him who for their sake died and was raised. 16 From now on, therefore, we regard no one from a human point of view; even though we once regarded Christ from a human point of view, we regard him thus no longer. 17 Therefore, if any one is in Christ, he is a new creation; the old has passed away, behold, the new has come.

Gospel
Mark 4:35-41
35 On that day, when evening had come, he said to them, "Let us go across to the other side." 36 And leaving the
crowd, they took him with them in the boat, just as he was. And other boats were with him. 37 And a great storm of wind arose, and the waves beat into the boat, so that the boat was already filling. 38 But he was in the stern, asleep on the cushion; and they woke him and said to him, "Teacher, do you not care if we perish?" 39 And he awoke and rebuked the wind, and said to the sea, "Peace! Be still!" And the wind ceased, and there was a great calm. 40 He said to them, "Why are you afraid? Have you no faith?" 41 And they were filled with awe, and said to one another, "Who then is this, that even wind and sea obey him?"

Regards,
BEM T. CELESTINE

TOOL BARS (MS EXCEL)

Working with Built-In and Custom Toolbars
You can add, delete, or move buttons on Excel’s built-in toolbars.
E.g. adding and rearranging buttons on the standard toolbar. Procedure: choose Tools, Customize. (The customize dialog will display. The standard and Formatting toolbars will display in the background when the Customize dialog box is open). Select the Commands tab. (This tab lists all command in all of Excel’s menus).

E.g. adding one of these commands as a button on the standard toolbar. From the Categories list, select Edit. On the vertical scrollbar, click three times below the button. Select the Clear Formatting button. (You can always get additional information about a command once it is selected). Click on the Description button. When you’re ready, click in the indicated area to close the description.

E.g.2. placing the Clear Formatting command on the Standard toolbar. Procedure: drag and drop the Clear Formatting button on the standard toolbar where indicated. To remove, Point to the AutoSum button and drag it off the toolbar.

In addition to adding and deleting buttons on toolbars, you can rearrange button positions. E.g. on the Standard toolbar, drag the Spelling button to the left of the Undo button. Close the Customize dialog box.

In the worksheet, cell A1 is selected. Click on the Clear Formatting button. (The formatting in cell A1 has been cleared). Reapplying the original formatting. Click on the Undo button.

You can restore any of the built-in toolbars to their original state at anytime. Right click on the Standard toolbar in the indicated area. On the Toolbar shortcut menu, choose Customize. (The Customize dialog will display and the Toolbars tab will be selected). In the Toolbars list, the Standard toolbar is selected. Click on Reset. (A dialog box prompts you to verify that you want to reset the toolbar). Click on Ok. click on Close.

How to create custom toolbars. From the Toolbars shortcut menu, choose Customize. Uncheck Standard to hide the Standard toolbar. Click on New. (A New Toolbar dialog box will display). Type My Toolbar and click on Ok. adding some buttons to it. On the commands tab, select File from the Categories list. Drag the Open  button to the new toolbar. The toolbar width automatically adjusts to accommodate the button that you added. Toolbars can also be docked at the edge of the application window.

You can change a toolbar’s position by either dragging it or by double-clicking on the toolbar’s background. You can also move a toolbar out of a docked position by pointing to the handle on the left side, pressing the mouse button, and dragging. When you’re finished with a custom toolbar, you can delete it. Procedure: Select the Toolbars tab. Scroll the Toolbars list and select My Toolbar. Click on Delete. Click on Ok.

Redisplaying the Standard toolbar. Scroll the Toolbars list and check Standard. Close the Customize dialog box. 
Regards,
BEM T. CELESTINE

FILTERING (MS EXEL)

Filtering Using Custom Criteria
When you perform a single-column filter using the Data, Filter, AutoFilter command, you can select only one filter criterion at a time the unique entries listed in the AutoFilter drop-down list. The (Custom…) filter criterion enables you to filter a list to display sets of data contain either of two unique entries in a column. That is, it enables you to create an OR condition. To meet the filter criteria, a set of data must meet either the first filter criterion or the second filter criterion. E.g. The Filter.Xls workbook is open. Let’s filter the list to display information pertaining to either the Germany division or the Great Britain division in the same list. Display the Division drop-down list. Select (Custom…) (observe the Custom AutoFilter dialog box. The list is filtered on Great Britain, therefore it is automatically entered as a criterion in the Show Rows Where: DIVISION box). Click on the Or option. From the second operator drop-down list, select Equals. From the second criteria drop-down list, select Germany. Click on Ok. (information pertaining to either Germany or Great Britain is now displayed). Choose Data, Filter, Show All to once again display the entire list.

do you have a website or blog and you want to get money from it? Click here.

You can also use the (Custom…) criterion choice to find values that fall within a specified range. When you specify custom criteria, you select a comparison operator from the operator drop-down list, then either type in a value or select it from the criteria drop-down list. E.g. let’s display a list of those employees who work 35 or more hours. From the HRS drop-down list, select (Custom…) from the first operator drop-down list, select is greater than or equal to. (now set the criterion to find those sets of data where 35 or more hours is entered in the HRS column).  From the first criteria drop-down list, select 35. Click on Ok. (only those employees who work 35 or more hours will display). Choose Data, Filter, Show All to display the entire list.

You can use wildcards to search for sets of data in a list that have certain text in common within the unique entries, even though the entire entry might not match. For example, if you search for sets of data that have entries in the Last Name column that begin with “M”, you might end up with two Moores, a Madding, and a Martinez.
The following are types of wildcards that you may use:
* An asterisk finds any characters that are in the same position as the asterisk.
? A question mark finds any single character that is in the same position as the question mark.
~ A tilde finds a question mark or asterisk (e.g. Who~ finds the text “Who?”)
E.g. perform a filter using the asterisk (*) wildcard. Display the LAST drop-down list and select. (Custom…) verify that the operator is Equals. In the first criteria drop-down list box, type m* (to find all employees whose last names begin with M). click on Ok. select (Custom..,) in the first criteria drop-down list box, type *h (to find all employees whose last names begin with M and who have health insurance). Click on Ok.

Sometimes the AutoFilter criteria that you specify will not yield the results you need. For example, you cannot use AutoFilter to display a list with two AND conditions combined with an OR condition. You can display a list of the History and Science department records for Germany and Canada, for instance, but you cannot display either the Science department of the Germany division or the History department of the Canada division. Comparison criteria enable you to specify criteria that match the data you want to find in a list. To create a criteria range, you enter one row of criteria labels and at least one row of criteria. The criteria labels must be identical to the column labels. You enter criteria in an area of the worksheet that is above or below the list, so that the criteria will not be hidden when the list is filtered.
E.g. some general rules for creating a criteria range. Turn off AutoFilter by choosing Data, Filter, AutoFilter. On the horizontal scroll bar, click in the area to the right  of the scroll box to view columns I through Q.

For an AND condition, you enter all of the criteria that you want the sets of data to meet in the same row. For example, the criteria range shown above finds all of the employees who have a last name beginning with S and are in the Germany division and work 35 hours or more a week.

For an OR condition, you enter all of the criteria that you want the sets of data to meet in the same column. For example, the criteria range shown here finds all of the sets of data for employees who are in the Australia division or the Canada division or the Germany division.
To combine an AND condition with an OR condition, you combine rows and columns. This criteria range finds all of the sets of data for employees who are in the Australia division and work 35 hours or more a week, or are in the Germany division and work 40 hours a week, or are in t he Canada division. There is no hour criterion for the Canada division.

Example trying making a criteria range.  
Click once in cell M1 and type division, click in cell N1 and type dept. these column labels correspond to the criteria that you want to set. In cell M2, enter germany, in cell N2, enter Science, in cell M3, enter Canada, in cell N3, enter history.

Using two columns creates an AND condition. Data sets must contain both Germany in the DIVISION column and Science in the DEPT column.

Using two rows creates an OR condition. Data sets must contain either Canada in the DIVISION column and History in the DEPT column, or Germany in the DIVISION column and Science in the DEPT column.

After you set up your criteria in a range on the worksheet, you must provide Excel with the location in the worksheet where you entered the criteria range. for the list range to be automatically entered, we need to place the active cell somewhere within the Payroll list. Click on the indicated cell in the Payroll list. Choose Data, Filter, Advanced Filter. (in the Advanced Filter dialog box). The list range with column headings is automatically entered by Excel because the active cell was within the list when you chose Data, Filter, Advanced Filter.

You enter the criteria range in the Criteria Range text box either by typing, or by clicking on the Selection button to the right of the text box and dragging to select the range in the worksheet. In the Criteria Range text box, type m1:n3 click on Ok.

Regards,
BEM T. CELESTINE

FISH RAIN IN THAILAND

https://m.youtube.com/watch?v=mmAF5cBxvZI

SORTING OPTION AND FILTERING LIST (MS. EXCEL)

Sorting Options
ASCENDING and descending sort orders rearrange a list by alphabetical, numerical, reverse alphabetical, or reverse numerical order. For some types of data, such as months, you might need to use a different order. In these cases, you can one of the custom sort orders provided with Excel to rearrange you data in chronological order by day of the week or by month. The workbook Sort Data.xls is open. E.g. select the Cell. On th e standard toolbar, click on the Sort Ascending button. Choose Data, Sort. The sort By box displays the last sort: Month of Highest Sales, Ascending. Clicking on the Options button. Display the First Key Sort Order drop-down list. Select the January, February, March, April line. Click on Ok twice. (The list will now sorted chronologically by month).

Automatic subtotals help to summarize the data contained in a list. You create subtotals by using an Excel summary. E.g. select the sheet. Select the cell and click on the sort Ascending button to sort the list alphabetically by DIVISION. Choose Data, Subtotals. Display the At Each Change In drop-down list and select DIVISION. (observe that in the Use Function drop-down list, the SUM function is selected. In the Add Subtotal To list box, GROSS PAY is checked. The Sum function will add the gross pay for each division to create subtotals. Click on OK. When you insert automatic subtotals, Excel creates an outline of your data. The outline enables you to show or hide certain sections, or levels, of data by clicking on the outline buttons. Grand-total values are derived from the list data, not from the subtotal rows. You can use the Data, Subtotals command to remove data subtotals from a list. You can also remove subtotals by re-sorting the list. When the message is displayed informing you that the subtotals will be removed as a result of the sort, you click on OK. e.g. Select the cell. Choose Data, Subtotals. Click on Remove All.

Make money online here

Filtering a List
When you filter a list, you display only the sets of data that meet a set of search conditions called criteria. The AutoFilter feature enables you to use the list to specify those search conditions. When you use the AutoFilter command, drop-down list arrows are displayed to the right of each of t he column labels in the list. Clicking on a drop-down arrow displays a list of all the unique entries for that column. By selecting one of the entries, a filter criterion, from the drop-down list, you tell Excel what to search for. Excel then filters the list so that the sets of data that contain the entry you selected will be displayed in the worksheet. E.g. the workbook Filter.xls is open. The list contains 94 sets of data, and each set has the name of a country in the DIVISION column. Select the cell. Choose Data, Filter, AutoFilter.click on the drop-down arrow to the right of the Division column label. Select Germany.

When Filter mode is active, arrows for the columns from which you have selected the filter criteria are displayed in blue. Row headings that match the criteria are also displayed in blue, and the status bar displays “Filter Mode” although sets of data that do not meet the criteria remain in the list, the rows that contain them are hidden.
If you select a single cell in the list before choosing Data, Filter, AutoFilter, drop-down list arrows are applied to the entire column labels in you list. If you select multiple column labels in your list. If you select multiple column labels before choosing Data, Filter, AutoFilter, drop-down list arrows are displayed for only the selected columns, thus restricting which columns you can apply filters to. In either case, the entire list is filtered. You can filter only one list at a time on the worksheet.

To display all of the hidden rows so the entire list is visible, you use the Data, Filter, Show All command. This command displays any sets of data that were hidden because they did not meet the filter criteria.
Choose Data, Filter, Show All.

After you specify a filter criterion for one column, you can filter your list further by selecting a filter criterion for another column. This procedure creates an AND condition. To meet the filter criteria in an AND condition, a set of data must meet the filter criterion in both the first column and the second column. You can create AND conditions and continue to filter your list by as many columns as are contained in your list.
e.g. filtering the list so that only a certain data for row are displayed. With a cell still selected, select the ----- from the ----row drop-down list. From Dept drop-down list, select Science.

Using the Data, Filter, Show All command removes all filters and displays the entire list.  If you have used a multiple-column filter on a list and want to remove the filter from only one column, you can select (All) from a column-filter criteria list to remove the filter from the only that column. E.g. removing the “Germany” criterion from the list. From the DIVISION drop-down list, select (All). This displays the Science department information for all divisions. Remove the DEPT column filter. This displays the entire list.

You may want to find the empty cells in your worksheets. You can use the (Blanks) filter criterion choice to display all sets of data in a list that do not have entries in the selected column. This filter criterion enables you to display sets of data where you need to enter information. E.g. display those sets of data in the list where the employee has no benefits. From the BEM drop-down list, select (Blanks). The BEN column is blank for all the displayed data. You can also display all of the sets of data that have information entered in a specific column by selecting the (NonBlanks) choice from the AutoFilter list. From the BEN drop-down list, select (NonBlanks). (All the sets of data displayed will contain an entry in the BEN column). Display the entire list using the Data, Filter, Show All command.

Regards,
BEM T.

FORMATTING GRAPHIC OBJECTS (MS EXCEL)

Adding and Formatting Graphic Objects.
The buttons on the Drawing toolbar enable you to create lines, arrows, shapes, and text boxes that are displayed and print as part of a worksheet or a chart sheet. You can display the Drawing toolbar by using the toolbar shortcut menu or by clicking on the Drawing button. The Objects.xls workbook is open.  In the standard toolbar, click on the Drawing button to display the Drawing toolbar. You can use these tools to draw by using click, hold & drag method. A text box object will be added to the worksheet for you. When you add objects to a worksheet or chart, each object is on an individual layer. The layers are stacked in the order in which they are added. This stacking order is most noticeable when objects overlap each other. Because the top object covers a portion of the objects beneath it, you can obtain different effects by creating overlapping objects. You can change the order of the drawn objects at any time by selecting an object and clicking on Draw in the Drawing toolbar. From the Draw menu, you choose Order, and then select one of the options given. With the other object still selected, click on Draw. Choose Order, Send to Back. Click away to deselect the object.

When you select an object, then press the Tab key, Excel cycles through all of the objects on the worksheet, including the embedded chart.

Graphic objects can be formatted just as you would format chart items- - by selecting the object and clicking on any one of the formatting buttons on the Drawing toolbar. You can also double-click on an object to which you want to apply formatting to display the Format AutoShape dialog box. E.g. Draw a line and Select it. Click on the Line Style button. Select the 3pt line style. Click on the Line Color drop-down arrow. Select the line color.
Three-dimensional effect can be added to any drawn object by selecting the object and clicking on the 3-D button on the Drawing toolbar. A pop-up menu displays 20 different three-dimensional effects. Create a text box, select it and click on the 3-D button. Select the 3-D style indicated by the red arrow. (done) you can double-click on the border of the text box to format it. Therefore you can also select an object and delete by pressing the Delete key. You can select multiple objects in order to delete it as well as grouping them. Grouping enables you to move, resize, or format several objects at once.  

Enhancing Worksheets and Charts with Graphics
You can  use Excel’s drawing tools to create graphic objects that enhance and highlight your worksheet data. You can also use the drawing tools to create items on an embedded chart or a chart sheet. Graphic objects become part of the worksheet, and print with the worksheet data and any embedded charts. E.g. creating graphic objects to highlight data. Click on Oval button and draw over data cell in the worksheet data. By default, the oval is a filled object. As a result, it covers the numbers in the worksheet. To have the numbers show through the object, you need to change the Fill Color option to No Fill. Click on the Fill Color drop-down arrow. Select No Fill to display the numbers through the oval. Now you can draw lines connecting the cell figure and the chart then creating a Text Box. You can also use the drawing tools to add objects to your chart sheets, just as you added objects to the embedded chart.

Single and Multiple Level Data Sorting
A list is an organized collection of related information. Lists are usually arranged in a column-and-row format as a labeled series of rows that contain similar data. Common examples of lists are telephone books, checkbooks, and personal phone directories.
In Excel 2000, a list must contain at least one column, which represents one category of information. Each column is identified by a title called a column label. Column labels should contain a font, data type, alignment, formatting, pattern, border, or capitalization style that differs from the data contained in the rows beneath them. A row in a list is called a set of data. Each set of data contains information from one or more related columns. For example, a set of data in the telephone book contains the name, address, and phone information for one individual. 

Sorting is used to organize the sets of data in your list alphabetically, numerically, or chronologically. When you sort a list, Excel arranges the rows according to the contents of one or more columns. If you want to rearrange the sets of data in your list according to the information contained in only one column, you can perform a quick sort using the Sort Ascending or Sort Descending buttons. E.g. select a cell, in the standard toolbar, click on the sort Descending button. Excel automatically selects the entire list for sorting. It also compares the top rows of the list for formatting differences. If it finds that the top row is formatted differently from the following rows, Excel identifies that row as a column label and excludes it from the sort.

You can use the Edit, Undo Sort command to undo a sort immediately after it has been performed. Once you have issued another command, however, the Edit, Undo Sort command is no longer available. If your list is in no particular order and you want to assign an order to it, then you can number the sets of data in your list. By numbering the sets of data, you can easily return the list to its original order without relying on the Edit, Undo Sort command.

In lists, several sets of data might contain some of the same information. For instance, two or more employees might have the same last name. Sorting the list by the data contained in one column might not provide enough order because of the “ties” that occur with repeated information. To break these ties, the Data, Sort command enables you to sort by multiple columns of information. E.g. select the cell choose Data, Sort. In the Sort dialog box, display the Sort By drop-down list. Select LAST. Display the first Then By drop-down list, and select FIRST. Verify that Ascending is selected, and then click on OK. When you perform a sort, that sort order is maintained as a “tie-breaker” f or the next sort. Suppose that you sort a list alphabetically by last and first names, then sort again by hours employed. If there are ties in the  number of hours employed, those ties will be arranged in the previous sort order, alphabetically by last and first name. Consecutive sorts enable you to sort your lists by more than the three columns you can specify in the Sort dialog box in consecutive sorts, you must perform the sorts for the least important order first, then work your way up to the most important sort order. E.g. sort primarily by Hours, with a sub-sort on the Hourly Rate information. Any ties that remain will maintain the order from the previous sort (by last name, then by first name). Choose Data, Sort. (the sort dialog box maintains the settings of the last sort) In the Sort By drop-down list, scroll down and select HRS. click on Descending to sort by most to least. From the first Then By drop-down list, select HOURLY RATE and click on Descending. Click on Ok.

The Sort dialog box enables you to specify three columns by which to sort a list. The Sort By drop-down list specifies the primary sort order, the first Then By drop-down list specifies the secondary sort order, and the second Then By drop-down list specifies the last sort order. E.g. on three-level sort on the Payroll worksheet, primarily by Hours, then by Hourly Rate. Any ties that remain will be sorted by employee numbers. Choose Data, Sort Observe that in the Sort By dialog box, the previous settings are maintained. In the second Then By drop-down list, select EMP#. Verify that Ascending is selected and click on Ok.

Feel free to drop any question you may have.

Regards,
BEM T CELESTINE

ADDING AND DELETING CHART ITEMS (MS EXCEL)

Adding and Deleting Chart Items
You can add information to a chart to enhance its readability. Anything on  a chart that can be selected is considered an item and can be added to or deleted from the chart. For example, you can use the Chart, Chart Options command to display the Chart Options dialog box. From the Chart Options dialog box, you can add a chart title, a value-axis title, a category-axis title, and a second category-axis or value-axis title when appropriate. To edit text in a chart, you select the item you want to edit and double-click on it. Then you can either type to replace all of the text, or use the mouse to select a portion of the text to edit. Procedure: select the Chart sheet. Choose Chart, Chart Options. (The Chart Options dialog box is display). In the Chart Title text box, type & click on OK. The Chart Title is edited and complete.
Using the Legend button. Delete the chart legend. Click on the Legend button to reinsert it. Tick marks indicate measurement lines on an axis. Gridlines extend these lines of measurement from the axis across the plot area, and make it easier to compare the data markers to the axis values. You can add gridlines to both the category and value axes. To enable gridlines, you display the Chart Options dialog box and select the Gridlines tab. You can display major and minor gridlines for either axis. E.g. select the chart. Choose Chart, Chart Options.

You can label individual data points by adding data labels. Data labels can be in the form of values, percentages, labels, or both labels and percentages, depending on the type of chart you’re working with. Select the Data Labels tab. Click on Show Value, then click on OK. Unless you select a specific series before you display the Chart Options dialog box, Excel automatically enters data labels on the chart for all data series. E.g. select The data labels. Press the Delete key. You can select each data label individually to format or delete it. To delete the data labels for an entire series, you click on one of the data labels for the series. When all of the data labels for the series are selected, you press the Delete key. E.g. select The data labels. Click on the indicated label. Press the Delete key. You can add or remove data labels for an individual series by right-clicking on the series and choosing Format Data Series from the shortcut menu, or by selecting the series and choosing Format, Selected Data Series. In the Format Series dialog box, you select the Data Labels tab, and then selecte the desired options. Right-click on the -- data marker. Choose Format Data Series.

To hide the numeric values that were used to create data markers for the selected data series, we’ll use the None option on the Data Labels page. Select the Data Labels tab. In the Data Labels box, click on None. Click on Ok to hide the – data labels.

To link a chart title or axis title to a worksheet, you must start with a title item in a chart that has already been created. You select the title and then type an equal sign (=) in the formula bar. If necessary, you select the cells containing the data for the chart, and then select the cells containing the data for the title. When the cells are selected, you press the Enter key to complete the link. You can also link titles to worksheet cells. After you create a link, the chart text automatically updates to reflect any changes made to those cells on the worksheet. E.g. select the chart title. Type the equal sign (=). Select the – sheet. Select cell ---- and press the Enter key. Now the chart title displays on the contents of the cell ---- from the other worksheet. Select the --- sheet. Select cell ---. Type another name and press the Enter key. NB: the chat title updated when you changed the contents of the cell on the worksheet. You can revert by clicking on the Undo button.
Moving and Sizing Chart Items.

After the chart is been created you can move its items to provide extra emphasis or clarity. Reorganizing items on a chart can also increase its overall effectiveness and appeal. You can move chart items by selecting them and dragging them with the mouse. Procedure: select the chart-- sheet. Place the mouse pointer on the border of the title box and drag it to another location of the chart sheet. When you move a chart item by dragging it to an area of the chart that already contains information, the chart does not resize to make room for the item. As a result, any data under that chart item is no longer visible. Select the legend. Drag the legend to another location.
Using Format dialog box to move the legend. Double-click on the legend to display the Format dialog box. Select the Placement tab. This tab allows to position chart items without using the drag-and-drop method. Under placement, click on Bottom. Click on Ok to reposition the legend.

You can use the View, Zoom command to display the chart sheets at different magnifications. This makes it easier to view, navigate, and move objects within them. The View, Zoom command also enables you to enlarge the chart sheet to view more details, or to reduce it to see more of it on the screen at once. Excel provides several built-in zoom percentage options. You can enlarge the data marker area of the chart sheet by selecting the plot area and dragging its borders to the edges of the chart area. This feature can be helpful if you want to add extra chart items and need to make more room on the chart.

Another reason for enlarging the plot area is to increase the size of the data markers. For example, if you’re suing the chart to create an overhead transparency, you might want to make the data markers larger so that they can be seen from the distance. Click on the gray background of the chart. Place the mouse pointer on the selection handle and drag.

I hope you are enjoying the lesson? Drop down your comment in the comment box.

Regards,
BEM T. CELESTINE

SUNDAY MASS

Today is the great day being SUNDAY.
So let us look in the Bible in Ezekiel 17:22-24
It reads;

22 Thus says the Lord GOD: "I myself will take a sprig from the lofty top of the cedar, and will set it out; I will break off from the topmost of its young twigs a tender one, and I myself will plant it upon a high and lofty mountain;

23 on the mountain height of Israel will I plant it, that it may bring forth boughs and bear fruit, and become a noble cedar; and under it will dwell all kinds of beasts; in the shade of its branches birds of every sort will nest.

24 And all the trees of the field shall know that I the LORD bring low the high tree, and make high the low tree, dry up the green tree, and make the dry tree flourish. I the LORD have spoken, and I will do it."

Responsorial Psalm
Psalms 92:2-3, 13-16

1 It is good to give thanks to the LORD, to sing praises to thy name, O Most High;

2 to declare thy steadfast love in the morning, and thy faithfulness by night,

12 The righteous flourish like the palm tree, and grow like a cedar in Lebanon.

13 They are planted in the house of the LORD, they flourish in the courts of our God.

14 They still bring forth fruit in old age, they are ever full of sap and green,

15 to show that the LORD is upright; he is my rock, and there is no unrighteousness in him.

Secondly coming to the New Testament in
2 Corinthians 5:6-10 It reads;

6 So we are always of good courage; we know that while we are at home in the body we are away from the Lord,

7 for we walk by faith, not by sight.

8 We are of good courage, and we would rather be away from the body and at home with the Lord.

9 So whether we are at home or away, we make it our aim to please him.

10 For we must all appear before the judgment seat of Christ, so that each one may receive good or evil, according to what he has done in the body.

Now let's hear from the Gospel of the Lord. Taking the Gospel in Mark 4:26-34 it reads;

26 And he said, "The kingdom of God is as if a man should scatter seed upon the ground,

27 and should sleep and rise night and day, and the seed should sprout and grow, he knows not how.

28 The earth produces of itself, first the blade, then the ear, then the full grain in the ear.

29 But when the grain is ripe, at once he puts in the sickle, because the harvest has come.

" 30 And he said, "With what can we compare the kingdom of God, or what parable shall we use for it?

31 It is like a grain of mustard seed, which, when sown upon the ground, is the smallest of all the seeds on earth;

32 yet when it is sown it grows up and becomes the greatest of all shrubs, and puts forth large branches, so that the birds of the air can make nests in its shade.

" 33 With many such parables he spoke the word to them, as they were able to hear it;

34 he did not speak to them without a parable, but privately to his own disciples he explained everything.

Regards,
Bro.
BEM CELESTINE

FORMATTING CHART (MS EXCEL)

Formatting Chart Text
Making charts more attractive, you can format chart items by borders around the titles, the legend, and the text boxes. Then you can also change the color, thickness, and style of the border, and choose the color of the area within the border. Procedures: select the Chart sheet. Right-click on the chart title. Choose Format Chart Title. Under Border, select Custom. From the style drop-down list, select Dotted. From the Color drop-down list, select Red. From the Weight drop-down list, select Thickest. Click on Ok. Click off the chart to deselect it. In the Format dialog box, the Font tab contains the information for the default chart font. On this tab, you can change the font’s type, style, size, and color. You can also specify that the font be underlined with single or double lines, use an effect such as subscript or superscript, and have a transparent or opaque background.

When you modify the default font, any chart text that uses the default font settings is updated to reflect your changes. Procedure: double-click on the white chart background. The Format Chart Area dialog box is displayed with the Patterns tab selected. Select the Font tab. In the Font list box, select a Font. In the Size list box, select a size. From the Color drop-down list, select a color and Click on OK.

You can change the font of individual chart text items by either double-clicking on the chart item or choosing Format, Selected, and then changing the font settings on the Font tab. The same font formatting options are available for individual chart items as are available for changing the default chart font. You can also format individual characters by selecting them and applying font changes. However, if the chart text is linked to worksheet data, you will not be able to apply formatting to individual characters in the text, because you must first be able to select the text in order to format it. Double-click on the chart title, then the Format Chart Title dialog box will display. You can also format the category axis by double-clicking on it.

You can use the Alignment tab in the Format dialog box to change the alignment of chart text from the horizontal default. You can change the orientation of chart text by rotating it. You can specify the amount of text rotation either by entering a number in the Degree text box or by dragging the text. When you drag the text, the degree of rotation is reflected in the Degree text box. When you enter a positive number or drag upward, the text is rotated from the lower left to the upper right.

Formatting Labels.
A chart appearance can be improved by changing the format and alignment of chart items and chart text. You can apply different number formats, such as currency, percent, or decimal, to the numbers on chart by using the options available in the Format dialog box. Double-click on the value axis. (The Format Axis dialog box will display) select the Number tab. Under Category, select Currency. Click on OK.

Applying the same formatting to the data labels. Right-click on the indicated data label. Choose Format Data Labels. In the Category list box, select Currency.
Data labels alignment also can be change the way the text is being changed by double-clicking on the data labels to display the Format Data Labels dialog box, select the Alignment tab, and specify the orientation in degrees. E.g. select the Alignment tab. Change the value in the Degree text box to --. Display the Label Position drop-down list. Select Center. Click on OK. To enhance the appearance of a chart, you can apply different colors and patterns to the data markers for each series. You might find it useful to format data markers with patterns when you are printing to non-color printers, or when you are printing charts that you plan to use as overheads. To display the Format Data Series dialog box, you double-click on one of the markers in the series. You can apply formatting to the individual data markers within a series, but if you do, that data marker will not match the legend or the other markers in the series. In the dialog box, under Area, click on Fill Effects. In the dialog box, select the Pattern tab. Under Pattern, select the upper-left pattern. Select a foreground color and a background color. Click on OK, click on OK. 

Regards,
BEM T. CELESTINE

CHART TYPES AND MODIFICATION (MS EXCEL)

Chart Types
When you create a chart in Excel, you have many types of charts from which to choose. If you wanted to change an existing chart’s type, you would click on the Chart Type would then be displayed, providing buttons for 18 different chart types. E.g. Pie, Bar, Area, Column, Line, Radar, XY (Scatter), Bubble, 3-D Area, 3-D Line etc.
The type of chart you use depends on the type of data that you are charting. Select the Chart sheet. Click on the Chart Type drop-down arrow. Click on the Bar Char button.

In addition to using the Chart Type button to change the chart type, you can use the Chart Type dialog box to view chart sub-types, custom chart types, and user-defined chart types. To display the Chart Type dialog box, you select the embedded chart or display the chart sheet, and choose Chart, Chart Type. Choose Chart, Chart Type. Under Chart Type, select Area. Select the Custom Types tab. You can select from several built-in custom chart types, or you can create your own chart type. In the Chart Type list box, select B&W Area. Click on Cancel.

NB: besides the Char Type button, you can also use the menu to change the chart type by choosing Chart, Chart Type. Excel provides eighteen different types of charts.

Modifying Embedded Charts.
We’ll modify an Embedded Chart by changing its type and position. Select the Chart Data sheet. Select the embedded chart. Click on the Chart Type drop-down arrow. Select 3-D Pie Chart. The chart type changes to 3-D Pie, but the pie-slice labels remain intact. You can drag a pie slice out of the chart to “explode” it. This feature is helpful when you want to call attention to one slice of the chart.

You can print an embedded chart as part of the worksheet, or you can print it separately. When you print an embedded chart as part of the worksheet, the size, proportions and position of the chart on the printout correspond to how the embedded chart is sized and placed on the worksheet. To print an embedded char with the worksheet, you must first verify that the embedded chart is not selected. Then you choose File, Print to display the Print dialog box. Because the embedded chart was not selected prior to choosing File, Print, the Active Sheet(s) option is selected by default under Print What.

To print, you would just click on OK. If you want to print an embedded chart by itself, you select the embedded chart, and choose File, Print to display the Print dialog box. Because you selected the embedded chart first, the selection option under Print What is chosen by default. You can adjust the print settings before you send your print job to the printer by using print Preview command.

Procedure: choose File, Print, click on Preview. When you print an embedded chart with a worksheet, you must size and position it on the worksheet. The page Setup options enable you to control how the data and char print. The Print Preview window enables you to view and work with the setup of your printout before you actually print. Click on setup. The Page Setup dialog box is displayed. The printout is set for portrait orientation, at 100% of its normal size, on letter-sized paper. Select the margins tab. Under Center on Page, check Horizontally. Click on OK. Then click on Close.
click here to go to the previous lessen

Regards,
BEM T. CELESTINE

CHART (MS EXCEL)

Creating a Chart Sheet
Chart is a table, diagram or a graphical presentation of a data. Charts often make worksheet data clearer and easier to understand. Excel enables you to create a charts on chart sheets, which are separate sheets of a workbook file that contain only charts. Excel also enables you to create embedded charts, which are displayed on the same sheet as the worksheet. To create a chart on a chart sheet, you can either use the Chart Wizard or press the F11 key. The Chart Wizard feature leads you through a step-by-step process to create a chart. When you use the F11 key to create a chart, Excel applies the default chart format to the new chart. E.g. Input data in cells A4 to E9, click on cell A5. While pressing the Shift key, click on cell E9. Choose Insert, Chart. Click on Next> three times. The default chart format is a column chart with a legend displayed and some formatting applied. Click As New Sheet. Click on Finish.

When you create a chart, values from worksheet cells, called data points, create data markers that can be displayed as bars, columns, lines, pie slices, or other shapes. A data marker is a chart symbol that represents a single data point. All of the data points on t he worksheet combine on the chart to create data series. These are displayed on the chart as a group of data markers distinguished by the same color or pattern. A sample of each series’ marker color and pattern is displayed in the legend along with the series name. the legend identifies the series on the chart so that you can compare the chart easily with the worksheet data. The value axis is typically the vertical axis on the chart. It is also known as the y-axis. Values for data points are plotted against this axis. The category axis is typically the horizontal axis on a chart. It is also known as the x-axis. Category labels are plotted along the x-axis, and data markers for all series are grouped into these categories. Gridlines are lines that are drawn in the plot area, typically for the value axis, so that data markers can be compared easily with an axis value. You can also create a chart from non-adjacent data. To do so, you select the first range of data, and then press the Ctrl key while you select the second range of data. You can press F11 to create the chart from the selected ranges. E.g. select the Chart Data sheet tab. Click on cell A5. Press the Shift key and click on cell B9 to select the range A5:b9.

Another example, selecting the range for the fourth quarter and create a char. while pressing the Ctrl key, select cell E5 press the Shift key and click on cell E9. Choose Insert, Chart. Click on Next> three times. (putting the chart on its own sheet, too) select As New Sheet. Click on Finish. A second sheet has been added.
NB: Excel automatically names all chart sheets in numeric sequence, starting with ‘Chart1”.  You can rename it by Double-clicking on the Chart 2 sheet tab.

Embedded Charts
When you use the Chart Wizard feature, Excel draws the chart according to the selections you make in the Chart Wizard dialog boxes, and embeds that chart as an object on the worksheet. Embedding a chart enables you to work with it directly on the worksheet. Example.    Select cell A5. While pressing the Shift key, click on cell A9. While pressing the Ctrl key, click on cell F5. While pressing the Shift key, click on F9. Click on the Chart Wizard button.  The Chart Wizard provides a series of steps that guide you through the process of creating a chart. In the Chart Type list box, select Pie. Click on Next> the second step, Chart Source Data, is displayed. The range that you selected before you clicked on the chart Wizard button is entered in the Data Range text box as an absolute reference. Click Next> The Chart Options step contains options for the titles, legends, and data labels. Select the Data Labels tab. Under Data Labels, select Show Label And Percent. Click on Next>. Click on Finish.

As Embedded Chart an object on the worksheet, you can move it from one location to another by dragging it. You can size an embedded chart by using its selection handles. When you place the mouse pointer on one of the selection handles, the pointer changes to a two-headed arrow. You can then drag in the direction of either arrow to enlarge or reduce the size of the chart. When you size embedded chart, its dimensions remain proportional, but you might inadvertently truncate chart data.

You can delete any chart item that you can select by selecting it and pressing the Delete key. If you change your mind after you’ve deleted a chart item, you can click on the Undo button to reverse your action.

When you create a chart fro worksheet data, Excel creates an active link between the worksheet values and the chart data points. When you change a worksheet value, the chart data point changes automatically. The link to the worksheet data is the same whether a chart is embedded or is stored on a chart sheet.

You can use page setup options to control how the chart prints. Creating a chart on a separate sheet can be useful when you want to use the chart in a presentation. Embedded charts enable you to print a chart and its association data on the same page. Embedded charts can be useful in reports, when you want to present the chart and the actual worksheet data close together.
Regards,
Bem T. Celestine

PRINTING OPTION (MS EXCEL)

Now it is time for you to print your excel work, here in this printing option you can preview your work before printing.

To preview each page of a worksheet exactly as it will appear when it is printed, we can use Excels print preview feature. This feature will enable us to adjust the margins, header, footer, and page breaks. Click on the Print Preview button. The Print Preview window opens to display the worksheet as it will look when printed. When you place the mouse pointer over the worksheet, the cursor becomes a magnifying glass. Click on the preview’s header. The Print Preview window zooms in, focusing where you clicked the mouse button. In this case, it is the header. Click on the worksheet. The view of the worksheet has zoomed out so that a whole page is displayed. You can use this technique of zooming in and out to get a better look at any part of the worksheet when Excel is in Print Preview mode. The status bar tells us the current page and total number of pages. Click on Setup,  select the Margins tab. The Margins tab on the Page Setup dialog box enables us to control how much white space fills the margins, and to center the worksheet horizontally and vertically.

Under Center On Page, check Horizontally. Click on OK.  E.g. click on Margins. The dotted lines around the perimeter of the page are the margin, header, and footer markers. When you click and drag one of these margin lines. The status bar displays the distance in inches from the edge of the paper.

Click on Setup  select the Margins tab. Change the Left margin value and press Enter. Moving the left margin causes the text to shift to the left. We used the Page Setup dialog box to change the margins because we wanted more precision than we could get by dragging the margin lines.

Click on Setup  select the Header/Footer tab.   You can choose to add headers and footers from a selection of predefined formats, or you can create custom headers and footers. Click on Custom Header. In the Left Section box, type Draft Report. You can use the Custom Header and Custom Footer buttons to format or edit the selected header or footer.
">Click here for more information on printing option

Press Tab twice. Click on the Date button. Click on Ok.  E.g. under Footer, click on the down arrow. Select Page 1 of ?,  click on Ok and click on Close button.

Page Setup when a Long Report workbook has been opened, and now let’s use print titles. Choose File, Page Setup. Select the Sheet tab.  The Sheet tab enables us to choose which rows should be used as print titles. These rows will be repeated at the top of each page of the printout. In the Rows to repeat at top text box, type $1:$3. To see the print titles, go back into Print Preview mode. In the Page Setup dialog box, click on Print Preview. Press the Page Down key.

Click on Setup. Select the Page tab. Under Scaling, select Fit To. Click on the up arrow to change the settings. These setting will scale the printout to fit the number of pages you prefer. Apply these changes and view the results. Click on Ok. Press the Page Up key. NB: the status bar now reads “Preview. Page 1 of 2”, indicating that our attempt to reduce the number of pages has been successful. However, the break between the two pages now falls in an undesirable location. Click on Page Break Preview. Press the Page Down key.

To override the page breaks that were inserted automaticall by Excel, we can use the Page Break Preview to drag a page break to the correct row, or we can insert page break directly into the worksheet.
Choose View, Normal. Select row e.g 36  choose Insert, page Break. (the new page break will displayed as a dotted line between the rows).

Using a Multiple-Sheet Workbook
A default Excel workbook contains three worksheets, named Sheet1 through Sheet3. A sheet’s name is displayed on the sheet tab. When a sheet is active, its name is displayed in boldface.  Click on the tab scroll right button. The tab scroll right button allows us to see the sheet tabs that are hidden behind the scroll bar by scrolling the sheet tabs one to the left. Click on the tab scroll last right button. Click on the tab scroll far left button. We can use these tab scrolling buttons to get access to any of the sheet tabs. Then, we can just click on the tab of the sheet we want to work with. Click on the Sheet 2 tab. Double-click on the tab for Sheet 1 by double-clicking on the tabs for the sheets, we can rename sheet tabs. Also by clicking on sheets while holding down the shift key, we can easily create formulas with 3-D references. In order to sum the values in cell from each worksheet, we need to select the cells for our formula. E.g. select the sheet tab. Select a cell B4. While pressing the Shift key, select another sheet. Press the Enter key. Excel will updated the formula with a reference to cell B4 from each of the first four worksheets, and the result of the sum function is displayed.  Select the sheet tab. Choose Edit, Delete Sheet. Click on Ok. Select the other sheet tab. With the sheet deleted, the formula that we created in cell B4 on the other sheet has automatically been recalculated for us. Choose Tools, Options. Select the General tab. The options dialog box will display which allows us to see and change the default settings for Excel. E.g. change the number of sheets to 200 and press the Enter key. Click on Ok. Now, if we create a new workbook, it will contain 200 worksheets rather than 3.

Excel as a Web Tool
You can save all or part of an Excel workbook as a web page. When you save Excel data as a web page,  it is saved as an HTML file, which is assigned the extension .htm. You can then post this file to an Internet web site or an intranet site.

To save Excel workbook as a web page.
Choose File, Save as web Page. Click on the Change Title button. Type the file name and click on Ok. Now, when someone opens your web page, this title will be displayed on the title bar of the web browser. Click on the Web Folder icon. Change the filename to and click on Save.
To preview the page in a Web browser. Choose File, Web Page Preview. Now we are looking at the Web page as it will appear to others who view it by using a Web browser. If you place your mouse pointer on any of the sheet tabs on the bottom of the page, the cursor will change to a hand with pointing finger. This indicates that each sheet tab is a hyperlink.    Click on sheets tab to view.

NB: the worksheets do not contain grid lines, column headings, or row headings. Also take note that since you save an entire workbook, all sixteen sheets are contained in the web page—even those that contain no data. Choose View, Source. by choosing to view the source of our Web page, we can see all of the HTML tags that Excel generated. These tags format our work book's data to mimic the layout and format as it appears when viewed in Excel. Click on the Close(X) button.

Regards,

BEM T. CELESTINE

PRINTING OPTION (MS EXCEL)

Now it is time for you to print your excel work, here in this printing option you can preview your work before printing.

To preview each page of a worksheet exactly as it will appear when it is printed, we can use Excels print preview feature. This feature will enable us to adjust the margins, header, footer, and page breaks. Click on the Print Preview button. The Print Preview window opens to display the worksheet as it will look when printed. When you place the mouse pointer over the worksheet, the cursor becomes a magnifying glass. Click on the preview’s header. The Print Preview window zooms in, focusing where you clicked the mouse button. In this case, it is the header. Click on the worksheet. The view of the worksheet has zoomed out so that a whole page is displayed. You can use this technique of zooming in and out to get a better look at any part of the worksheet when Excel is in Print Preview mode. The status bar tells us the current page and total number of pages. Click on Setup,  select the Margins tab. The Margins tab on the Page Setup dialog box enables us to control how much white space fills the margins, and to center the worksheet horizontally and vertically.

Under Center On Page, check Horizontally. Click on OK.  E.g. click on Margins. The dotted lines around the perimeter of the page are the margin, header, and footer markers. When you click and drag one of these margin lines. The status bar displays the distance in inches from the edge of the paper.

Click on Setup  select the Margins tab. Change the Left margin value and press Enter. Moving the left margin causes the text to shift to the left. We used the Page Setup dialog box to change the margins because we wanted more precision than we could get by dragging the margin lines.

Click on Setup  select the Header/Footer tab.   You can choose to add headers and footers from a selection of predefined formats, or you can create custom headers and footers. Click on Custom Header. In the Left Section box, type Draft Report. You can use the Custom Header and Custom Footer buttons to format or edit the selected header or footer.
">Click here for more information on printing option

Press Tab twice. Click on the Date button. Click on Ok.  E.g. under Footer, click on the down arrow. Select Page 1 of ?,  click on Ok and click on Close button.

Page Setup when a Long Report workbook has been opened, and now let’s use print titles. Choose File, Page Setup. Select the Sheet tab.  The Sheet tab enables us to choose which rows should be used as print titles. These rows will be repeated at the top of each page of the printout. In the Rows to repeat at top text box, type $1:$3. To see the print titles, go back into Print Preview mode. In the Page Setup dialog box, click on Print Preview. Press the Page Down key.

Click on Setup. Select the Page tab. Under Scaling, select Fit To. Click on the up arrow to change the settings. These setting will scale the printout to fit the number of pages you prefer. Apply these changes and view the results. Click on Ok. Press the Page Up key. NB: the status bar now reads “Preview. Page 1 of 2”, indicating that our attempt to reduce the number of pages has been successful. However, the break between the two pages now falls in an undesirable location. Click on Page Break Preview. Press the Page Down key.

To override the page breaks that were inserted automaticall by Excel, we can use the Page Break Preview to drag a page break to the correct row, or we can insert page break directly into the worksheet.
Choose View, Normal. Select row e.g 36  choose Insert, page Break. (the new page break will displayed as a dotted line between the rows).

Using a Multiple-Sheet Workbook
A default Excel workbook contains three worksheets, named Sheet1 through Sheet3. A sheet’s name is displayed on the sheet tab. When a sheet is active, its name is displayed in boldface.  Click on the tab scroll right button. The tab scroll right button allows us to see the sheet tabs that are hidden behind the scroll bar by scrolling the sheet tabs one to the left. Click on the tab scroll last right button. Click on the tab scroll far left button. We can use these tab scrolling buttons to get access to any of the sheet tabs. Then, we can just click on the tab of the sheet we want to work with. Click on the Sheet 2 tab. Double-click on the tab for Sheet 1 by double-clicking on the tabs for the sheets, we can rename sheet tabs. Also by clicking on sheets while holding down the shift key, we can easily create formulas with 3-D references. In order to sum the values in cell from each worksheet, we need to select the cells for our formula. E.g. select the sheet tab. Select a cell B4. While pressing the Shift key, select another sheet. Press the Enter key. Excel will updated the formula with a reference to cell B4 from each of the first four worksheets, and the result of the sum function is displayed.  Select the sheet tab. Choose Edit, Delete Sheet. Click on Ok. Select the other sheet tab. With the sheet deleted, the formula that we created in cell B4 on the other sheet has automatically been recalculated for us. Choose Tools, Options. Select the General tab. The options dialog box will display which allows us to see and change the default settings for Excel. E.g. change the number of sheets to 200 and press the Enter key. Click on Ok. Now, if we create a new workbook, it will contain 200 worksheets rather than 3.

Excel as a Web Tool
You can save all or part of an Excel workbook as a web page. When you save Excel data as a web page,  it is saved as an HTML file, which is assigned the extension .htm. You can then post this file to an Internet web site or an intranet site.

To save Excel workbook as a web page.
Choose File, Save as web Page. Click on the Change Title button. Type the file name and click on Ok. Now, when someone opens your web page, this title will be displayed on the title bar of the web browser. Click on the Web Folder icon. Change the filename to and click on Save.
To preview the page in a Web browser. Choose File, Web Page Preview. Now we are looking at the Web page as it will appear to others who view it by using a Web browser. If you place your mouse pointer on any of the sheet tabs on the bottom of the page, the cursor will change to a hand with pointing finger. This indicates that each sheet tab is a hyperlink.    Click on sheets tab to view.

NB: the worksheets do not contain grid lines, column headings, or row headings. Also take note that since you save an entire workbook, all sixteen sheets are contained in the web page—even those that contain no data. Choose View, Source. by choosing to view the source of our Web page, we can see all of the HTML tags that Excel generated. These tags format our work book's data to mimic the layout and format as it appears when viewed in Excel. Click on the Close(X) button.

Regards,

BEM T. CELESTINE

FORMATTING (MS EXCEL)

Hello, you are highly welcome to the next lesson.
Today you will be learning something on a topic; Formatting.

Formatting a Worksheet
Excel allow formatting and formatting will change only the appearance of the worksheet, and not the value of the data.  Formatting toolbar can be use to change the appearance of the text. The font size, face, style color, e.t.c. In addition to changing the size and font of text, we can modify its position and alignment.
Procedure: display the shortcut menu by right-clicking, choose Format Cells. Select the Alignment tab and change the value in the Degrees text box and click on Ok.

You can change the position of text by indenting it.  Indenting text is another way to emphasize important data.
Procedure: click on the Increase Indent button.
Borders: Click on the down arrow beside the Borders button. Click on the Thick Box Border button. Click on the down arrow beside the Fill Color button.

Color: the Fill Color button allows us to change the background of the currently selected cells. Procedure: select the color turquoise,  press the Home key to deselect the range.

Applying formatting to non-adjacent cells.
Select a cell while pressing the Ctrl key, select
Number Formats and Text Alignment

In Excel, the default numeric format is the General format. However, you can use any of Excel’s built-in formats to change the appearance of numbers in your worksheets. You can select the entire worksheet by clicking on the Select All button, which is the intersection of the row and column headings in the upper-left corner of the worksheet. Click on the Select All button.

Notice that the entire worksheet is selected. Now the changes we make to the following will affect all the cells. Right-click on the selected worksheet. Choose Format Cells.  Select the Number tab.

The Number tab gives us access to Excel built-in number formats. Select Currency from the Category list box, the Decimal Places text box will display. The value in this box will determine how many digits will be appear after the decimal point. Click on OK.

All numbers are displayed in the currency format with two decimal places. Most of the columns have been widened automatically to display the additional formatting. We can change the column widths manually as well. Select a cell. Choose Format, Column, Width. Type 3.57 for the column width. Click on the OK button. Since the column width is too small for the contents of cell, Excel displays a series of number signs in the worksheet. The contents of the cell, which can still be seen in the formula bar, remain unchanged. When we double-click on the boundary between the column headings, Excel resizes the column to fit the widest number or text in the column. Click on the Decrease Decimal button twice as well as the Increase Decimal button twice.  You can also click on the Align buttons to align your text.

In our next lesson we will be learning on how to print and printing options.

Thanks for attending and I will always be here for you.

Regards,
Bem T. Celestine

INSERTING ROWS AND RANGES, MOVING AND COPYING DATA (MS EXCEL)

Inserting Rows and Ranges
You can select an entire column or an entire row by clicking on the column or row heading. To select a row or column, use the drag method. After selecting, Right-click on the selected items and choose Insert. (NB: the row insertion will affect the entire worksheet. Fortunately, Excel allows us to undo this insertion easily. Click on the Undo button.

One other thing is that when rows are inserted, the ranges of cells included in formulas are updated.

Moving and Copying Data
There are two ways to move data in Excel. One way is to drag the data from one location to another. Procedure: Position the mouse pointer over the border of a cell you want to move until the pointer changes into an arrow, press and hold the mouse button, drag to the cell and release the mouse button.

Another way you can move data in Excel is to cut the data from one location and paste it to another. Procedure: select the cell, click on the Cut button. (the marquee surrounds the selected range) click on the Paste button.
Another way to copy a text is by drag and drop method. Select the cell, click and hold the mouse button, press and hold the Ctrl key to display a mouse pointer with a plus sign, drag to cell and release the mouse button and Ctrl key.

Excel adjusts copied formulas so cell reference change according to their new locations. These “adjusting” references are known as relative references.
E.g. using the fill-handle method to see how the cell references adjust. Select a cell that contains the formula. Position the mouse pointer over the lower-right corner of the cell border until the mouse pointer change to a solid plus sign, drag to a cell you want to copy the formula and release the mouse button. As you copied the formula to each cell, Excel adjusted the formula to reflect the change in the formula’s location relative to the column into which it was copied.

One other method you can use to copy a formula is to use the Copy and Paste buttons.

Absolute References
When formulas are being copied from one cell location to another, the cell references adjust automatically. Most of the time, this adjustment is just what we want.

Sometimes, however, we may not want this adjustment to occur. To prevent this, we must write the formula by using absolute references. E.g. add a formula that will calculate sales commissions. Type=  select the cells to which the formula will refer. Click on the H5, type *    click on cell H9, press the Enter key. Cell I 8 display the value.  Now using the handle to copy the formula to cells I 9:I12. Select cell I8. Use the fill handle method to copy the formula to the range I 9:I 12. As the formula will copy, the references will change relative to their locations. In this situation, this is not what we want, because the commission rate resides in cell only. We make a cell reference absolute, rather than relative, by inserting dollar signs ($) before the column and row designation. You should know that a cell reference can include both relative and absolute parts. We can type the dollar signs, or we select the cell reference and press the F4 key. When you press the F4 key once when a relative cell reference is selected, Excel inserts dollar signs in front of the column and row references. Press the Enter key. Use the fill handle method to copy the formulas to the range. Once the percentage value is change the formulas in the commission cells recalculate because they all refer to the %cell.

Regards,
BEM T. CELESTINE