YOU MUST LAUGH

A girl was very proud of her boy friend, she always tell her friends how good her boy friend is. On the day of her birthday, she invited all her friends including her boy friend who happens to be the special guest of honour.

Everybody brought their gifts to the girl and was waiting for the arrival of the girl's boy friend who promised to bring a very nice gift for his girl.

As everybody was waiting, behold! The boy was coming with a gift which was inside a poly bag. The girl was very happy at the arrival of his boy friend and was ready to see the gift that he brought to her.

Do you know what happened?
The boy just opened the poly bag in front of all the guest's
and invitees. And he brought out a loaf of bread. And said to the girl..."take this bread, that's what i brought for you as your birthday gift"

The girl got angry immediately and was highly disappointed at her boy friend. She grab the bread and threw it away with anger. The boy friend also got disappointed and he rushed and pick up the bread.
He opened the bread in front of his girl friend, behold!!!.... there was a car key and an engagement ring inside the bread.

When the girl noticed that...... She began to beg, but then, it was too late for her.

Moral: always learn how to appreciate things, weather big or small.

Prayer: i pray that your mistake will never take away your
favour, before the end of the year, God must surely surprise you with a miracle.

WORKING WITH WORKBOOK (MS EXCEL)

Working with workbooks
Multiple worksheets enable you to do some useful and powerful data analysis. You can think of multiple worksheets as working with a three-dimensional file. Each sheet has a height and width. The depth dimension comes from the layers of the worksheets. The first or last sheet in the workbook summarizes the detailed data contained on the other sheets. Here you will work with a workbook that uses multiple worksheets.

You can change the order of sheets within a workbook by moving them, but you must be careful. Just as moving rows before or after the last reference in a cell range can cause formulas to be incorrect, the same is true when moving sheets. E.g. point to a sheet tab. Press and hold the mouse button. Drag the mouse pointer with the sheet icon until the black triangle is pointing between the other sheets. Then release the mouse button.

Copying a sheet within a workbook. Select the sheet. Hold down the Ctrl key and drag the sheet to the right, on top of sheet the other sheet. Release the Ctrl key and the mouse button.

In Excel, you cannot use the Cut, Copy, and Paste commands to move or copy sheets. You can, however, use the shortcut menu to insert and delete sheets. Right-click on the sheet tab you just created. Choose Delete and click on ok. you have just deleted the sheet. Now when you insert a sheet, the new sheet is inserted sheet and becomes the active sheet. Right-click on the Summary sheet tab and choose Insert. Click on Ok.

Linking Cells in Different Workbooks
You can link workbooks by writing a formula in one workbook that refers to a value in another workbook. Here you will build, save, and edit formulas that link workbook files. You’ll also use a summary workbook to establish and maintain links to cells in other workbook files. E.g. the files, open dialog box is displayed. Select Hanover.xls. press the Ctrl key and select Monder.xls, Rene.xls, and Summary.xls. Selecting the files is the first step in opening several workbooks at the same time. Click on Open. Choose window, Rene. The worksheet shows item code and price, along with quantity, sales, and commission. If you’d like, activate the workbooks for Monder and Hanover to observe their sales worksheets.

E.g. entering a linking formula that will contain external references to the three salespersons’ workbooks. Cell A5 is selected. Type the equal sign (=) to begin to enter the formula. Choose Window, Rene. Click on the Total Value. Observing the formula bar. An external reference to the Rene.xls workbook is contained in the formula. This formula links the Summary.xls and Rene.xls workbooks. The cell reference is absolute: you can change that by editing the formula manually. Type the plus sign (+) to continue the formula. Choose Window, Monder. Click on the total value. Type the plus sign (+) choose Window, Hanover. Click on total value. Then press Ctrl + Enter to complete the formula. When there is any change Totals will change in the summary workbook. The summary workbook is called the dependent workbook, since it contains the linking formula with external references. The workbooks to which the linking formula refers are called the source workbooks.  

To ensure that source workbook formulas have been calculated and that document names are current, save the source workbooks before saving their dependent workbooks.

When you open a file that contains one or more linking formulas, you can us4e the Edit, Links command to open source workbooks, update the formula calculations, or change the source workbooks. E.g. open New summary.xls. The workbook contains links. Because you do not have all the source documents open, Excel asks if you want to update the linked formulas with the latest data from the source workbooks. Click on Yes. The New summary workbook is identical to the My Summary workbook you created. However, you can verify the source workbook links. Choose Edit, Links. The Links dialog box enables you to see the workbooks upon which the current workbook depends. You can open any or all of these files. Select Rene.Xls. Click on Open Source. close the Rene.xls workbook. Choose Edit, Links. Select Rene.xls. click on Change Source. The change Links dialog box enables you to select a new workbook to which to link New Summary. In the first list, select My Rene.xls. Click on Ok. Click on Ok again.

Using Comments
A comment is a text that contains information about a worksheet cell. Here you will create and control comments to documents your workbooks more effectively. Comment indicators are displayed automatically for cells that have comments attached. A comment indicator is a small triangle in the upper-right corner of the cell. They do not print when you print a worksheet. E.g. select a cell. From the shortcut menu for the cell, choose Insert Comment.

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