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.