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