Microsoft Excel Lessons on Functions and charts (2)

Examples of Functions in Action

     

            Excel offers nine categories of functions, including database, logical, statistical, and text functions, etc. The following functions are the functions you’re most likely to find useful.

SUM

Adds all the numbers in a range of cells.

AVERAGE

Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contains numbers.

SQRT

Returns the square root of a number

MAX

Returns the largest value in a set of values. Ignores logical values and text.

MIN

Returns the smallest number in a set of values. Ignores logical values and text.

MATCH

Returns the relative position of an item in an array that matches a specified value in a specified order.

MEDIAN

Returns the median, or the number in the middle of the given set of given numbers

Mode

Returns the most frequently occuring, or repetitive, value in an array or range of data.

Count

Counts the number of cells in a range that contains number.

CountA

Counts the number of cells in a range that are not empty

Countblank

Counts the number of empty cells in a specified range of cells.

CountIF

Counts the number of cells within a range that meet the given condition

Roman

Converts an arabic numeral to roman, as text.

Power

Returns the result of a number raised to a power

Round

Rounds a number to a specified number of digits.

Lower

Converts all letters in a string to lowercase.

Upper

Converts a text string to all uppercase letters.

Proper

Converts a text string to proper case; the first letter in each word in uppercase, and all other letters to lowercase.

MOD

Returns the remainder after a number is divided by a divisor.

Concatenate

Joins several text strings into one text string.

And

Checks whether all arguments are true, and returns TRUE if all arguments are TRUE.

IF

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

Lesson 6

working with OBJECTs and charts in Worksheets

          Excel can create both embedded charts (charts positioned on a worksheet page alongside other data) and charts that appear on their own worksheet page. Embedded charts are useful for charting smaller amounts of data and for experimenting with the best ways to chart data that you need to edit while creating the chart. But for maximum effect, you’ll generally want to create each chart on its own worksheet page.

Component	Explanation
X-axis	The category axis of the chart. Usually horizontal, but some charts have a vertical X-axis.
Y-axis	The series axis (the vertical axis on which the categories are plotted).
Z-axis	The value axis (the depth axis of the chart; 3D charts only).
Axis titles	A title (name) for each of the axes used.
Chart title	The name of the chart.
Data series	The set or sets of data from which the chart is created. Some charts, such as pie charts, use only one data series. Other charts use two or more data series. The chart represents the data series as data markers.
Data marker	The chart’s representation of a point in a data series. You may want to display data markers in
	different data series as differently shaped points to distinguish them from one another.
Data labels	Text that appears on or near points in the data series to identify them.
Legend	Notes on the color, pattern, or other identification used to distinguish each data series.
Gridlines	Reference lines drawn across the chart from the axes so that you can see the values of the data series.
Categories	The distinct items in the data series. For example, in a chart showing the sales performance for each
	of a company’s regions, each region would be a category.
Chart area	The area occupied by the entire chart, including legend, labels, and so on.
Plot area	The area occupied by the data plotted in the chart (not including legend, labels, and so on).

Lesson 7

Create a Chart with the Chart Wizard

          The Chart Wizard is the fastest and easiest way of creating a chart. To use the Chart Wizard, follow these steps:

1.   Select the range of data from which you want to create the chart.

2.   Click the Chart Wizard button on the Standard toolbar or choose Insert | Chart from the menu. The Chart Wizard displays the Chart Type screen.

3.   Choose the type of chart you want to create and then choose the subtype (for one of the standard types). On the Standard Types tab, you can click the Press and Hold to View Sample button to have Excel build a preview of the chart type using the data you’ve selected.

4.   Click the Next button to display the Chart Source Data screen of the Chart Wizard. The left screen in Figure below shows the Data Range tab; the right screen shows the Series tab.

Choose the Right Type of Chart for Your Data

          As you saw in the Chart Type dialog box, Excel offers an extremely generous range of charts—14 standard types, each with two or more subtypes, and 20 built-in custom types.

Such a wide choice of chart types can make it difficult to decide which type to use. Should you use a conventional bar chart or line chart; go for an area chart, a doughnut, or radar; experiment with a
Pie Explosion; or visit the Outdoor Bars?

In general, you should use the simplest type of chart that can present your data satisfactorily. Don’t feel you must use an unusual type of chart just because Excel makes doing so easy or because the standard chart type seems boring or conventional. As a rule of thumb, if you don’t know what a chart type is for, take a quick look at the example in the Chart Type
box and see if it’s easy to understand. If not, leave that chart type alone.

Lesson 8

Format Charts

          Excel gives you fine control over how your charts behave and how they look. You can resize embedded charts, zoom chart sheets, and apply formatting to either the entire chart area or just about any item in the chart.

                Before formatting an embedded chart, you may want to display it in its own window so that you can see it at a larger size. To do so, right-click the chart and choose Chart Window from the shortcut menu.

Resize a Chart

         

          To resize an embedded chart, select it and drag one of the sizing handles to the size you want. You shouldn’t need to resize a chart on a chart sheet, because Excel automatically expands the chart to fill the size of paper you’re using. However, you can zoom the chart in and out to see it at different sizes.

Format the Chart Area

          When formatting a chart, typically you’ll want to start by formatting the chart area, because the chart area exercises the greatest influence over how the chart looks as a whole. For example, you can set a background color or pattern for the chart area, specify a border for it, and set overall font formatting for the chart. You can then apply further formatting to the elements of the chart as necessary to pick them out.

     To format the chart area, select it so that its handles appear, right-click, and choose Format Chart Area from the shortcut menu to display the Format Chart Area dialog box.

          Use the Patterns tab o applies a border and a background color. You can also apply round corners to the chart. You may also want to apply a pattern, fill, or a picture.

          Use the Font tab to apply font formatting. This tab contains standard font-formatting options except for one: the Auto Scale check box. Select this check box to have Excel rescale the fonts automatically when the chart is resized. (Excel applies automatic scaling to many chart types by default.)

    

           

Format Different Data Series Using Different Chart Types

         

If you need to differentiate two data series strongly, try using a different chart type for each series.

     You’ll need to experiment with this technique to get striking and comprehensible results. You’ll quickly find that some chart types work well with others, while other combinations create a truly horrible chart that will confuse most sentient beings.

          To use two different chart types in the same chart, follow these steps:

1.   Create the chart as usual, and format it using the chart type that you want to have applied to most of the chart.

2.   Select the data series you want to affect.

3.   Choose Chart | Chart Type, or right-click the data series and choose Chart Type from the shortcut menu, to display the Chart Type dialog box.