assignment in our business application

Acas, Arnel M. Prof. Ildefe Villanueva
BSCS - II CSC 131 / TTH 5:30-7:00


1.) Spreadsheet:

A table of values arranged in rows and columns. Each value can have a predefined relationship to the other values. If you change one value, therefore, you may need to change other values as well.


2.) Parts of the spreadsheet:

Menu bars
Toolbars
Column and row labels
Formula Bar
Sheet tabs
Work Space
Cells
Active cell
Mouse pointer

3.) What is MS Excel?
Microsoft Excel (full name Microsoft Office Excel) is a proprietary spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables and, except for Excel 2008 for Mac OS X, a macro programming language called VBA (Visual Basic for Applications). It is overwhelmingly the dominant spreadsheet application available for these platforms and has been so since version 5 in 1993[citation needed], and is bundled as part of Microsoft Office. Excel is one of the most popular microcomputer applications to date.

4.) Features of MS Excel 2003

MS Excel 2003 is a Windows-based application package that can be used to automate tasks such as calculation and analysis of data, automate financial statements, business forecasting, transaction registers, inventory control, etc. It provides multiple facilities, such as making graphs and charts, analyzing situations, and helps users at the managerial level in taking decisions. The features provided by Excel are very useful for managers as well as the supervisors in any type of organization and help them to execute their complex tasks with a minimum effort. MS Excel can be customized in a way that suits a user to achieve his goal. It is extensively used in financial organizations. The features of MS Excel are as follows:

Workbooks: A document in MS Excel is called a workbook. Each workbook contains sixteen worksheets by default. A user can change this number by resetting the default options. Worksheets within workbooks make it easy to bind files of related information. When a user opens a workbook, he can use all the worksheets to perform a task. To create a workbook, a user will have to select the new command from the File menu. To open an existing workbook, a user will have to select the Open option from the File menu. A user can insert a worksheet between two worksheets by choosing the Worksheet option from the Insert menu.

Using keyboard: The following table provides different keyboard shortcuts for selecting a row, column, current cell, worksheet, etc.

A user can perform various kinds of entries in a cell. He can enter text, number, data, and time in a cell. He can also enter some special characters such as $ % + - / ( ). He can also enter date and time in a cell. He can select different formats for date and time according to his own requirements.

Entering Data in Series: A user can fill a range of cells either with the same value or with the series of values. This can be done using the Auto fill handle (Small Square on the bottom-right corner of the active cell).

Cell References: MS Excel provides three types of cell references: relative, absolute, and mixed. Relative references refer to the cell relative to the given position. Absolute references refer to the specific cell irrespective of the position of the formula. The '$' sign is used to denote an absolute reference. Mixed references are a combination of both relative and absolute references. It has one absolute co-ordinate and one relative co-ordinate. $CI and C$1 are both examples of mixed references.

Inserting or Deleting Rows and Columns: MS Excel provides the facility to insert rows or columns into the existing worksheet. Inserting a row of data shifts the rest of the rows down and removes the last row of the worksheet. Similarly, inserting a column shifts the rest of the columns to the right and deletes the rightmost column. This occurs because the total number of rows and columns remain the same in any case.

MS Excel provides several formatting features that enhance the appearance of the data, which is presented in the worksheets. Some of them are described as follows: MS Excel allows setting up a page before printing. To set up a page, a user will have to open the File menu and click on the Page Setup. The Page Setup dialog box will appear that will allow the user to set the paper size, orientation of the data, scaling of the area, set the left, right, bottom, and top margin, set the header and the footer, etc.

MS Excel provides the auto fit selection that adjusts the width of the column according to the widest entry in that column. A user can hide or unhide certain rows or columns. He can also provide the default column standard width.

MS Excel allows a user to align the characters in different ways by using the Alignment tab page of the Format Cells dialog box. The dialog box provides a number of alignment options.

Horizontal Alignment options:

General: The text is left aligned and the numbers are right aligned. This is the default status.
Left: Aligns the contents to the left of the cell.
Center: Aligns the cell contents to the center of the cell.
Right: Aligns the contents to the right of the cell.
Fill: Fills the selected cells with the single character.
Justify: This option is selected with multiple lines of text as it wraps the contents from left to right.

Vertical Alignment options:

Top: Aligns the text at the top of the cell.
Center: Places the text in the center of the cell.
Bottom: Aligns the text on the bottom of the cell.
Justify: Wraps the text from top to bottom.

Controls: The controls are the special objects, which enhance user interface and facilitate user input. MS Excel provides several custom controls such as list boxes, check boxes, and dialog boxes, etc. A large number of toolbars are found in the View menu. Users can add a toolbar or a number of toolbars into the working document depending upon the work involved and use different controls in their documents.

Functions and formulas: The built-in formulas are called functions. MS Excel provides analyzing data and manipulating text by using different functions. Users can easily calculate percentage, interest, average, etc. by using built-in functions. This can be done either by typing in the function-based formulas or by using function wizards. Formulas are widely used in simple computing (such as addition, subtraction, multiplication and division) and advanced computing. They provide the power to analyze data extensively.
Manual: In this type of calculation, the recalculation of the complete worksheet is performed by pressing the F9 key. This option can be selected on the Calculation tab page of the Options dialog box, which is opened by choosing Options from the Tools menu.

Charts: One of the most important features of MS Excel is a chart. MS Excel allows users to view data entered as tables in a graphical form as charts, which helps a user to easily understand, analyze, and compare data. Excel allows its users to create either two-dimensional charts or three-dimensional charts. A user can enhance the chart by adding chart items, such as data labels, a legend, titles, text, and gridlines. He can also do formatting on these items by using colors, alignment, fonts, and other formatting attributes. MS Excel also allows users to view charts along with the data by using embedded charts. These charts are included in the worksheet and can be copied, moved, and resized in the same way as can be done with any other graphical object.

Database: Data are raw facts, information is processed data, and a database is an organized collection of information. Every organization heavily depends upon databases to store, retrieve, and maintain different kinds of data. MS Excel provides all this in the form of its database feature. In MS Excel, database can be created in two ways:
Enter the data in the form of table in the worksheet.

Auto-calculation: MS Excel spreadsheet allows a user to automatically recalculate the whole worksheet every time a change is made in a single cell. There are basically two kinds of recalculations.

Automatic: In this type of calculation, the change in the value of the cell automatically recalculates the whole worksheet.
Use the Data Form command
Records can be inserted, deleted, and sorted by using the Data menu.



With the above-described features of MS Excel, users can perform almost all the operations they want in a very efficient and easy way. Its advanced features have made it the first choice for professionals working in a financial organization to perform their lengthy tasks in an easy way and in a quick manner. Therefore, MS Excel has become the most preferred choice for most of the users as well as professionals throughout the world.

5.) What are a Cell, Workbook, and Worksheet?

Cell:
A cell is a single location on a worksheet in which data is stored. A cell is referred to by its unique address, or cell reference, which is composed of the coordinates of the intersection of a column and a row. To identify a cell, specify the column letter immediately followed by the row number.

Workbook:
When Excel starts, it creates a new empty workbook (Book 1) that displays the active worksheet (Sheet1). The workbook is like a notebook. The workbook contains sheets, called worksheets. Each worksheet can contain sets of data and charts. Each sheet name appears on a sheet tab at the bottom of the workbook. You access different worksheets by clicking the sheet tab or using the tab scrolling buttons to the left of the sheet tabs. A new workbook opens with 3 worksheets. However, the number of worksheets per workbook is limited only by the amount of memory on your computer. Several workbooks may be open at one time.

Worksheet:
The worksheet is organized into a rectangular grid with 256 columns and 65,536 rows. A column letter above the grid, also called a column heading, identifies each column with the letters A through IV. A row number on the left side of the grid, also called the row heading, identifies each row with the numbers 1 through 65536.


6.) How many cells are there in MS Excel Worksheet?

A maximum of 32,000 characters can be entered in a cell. Of the 16,777,216 possible cells in a worksheet, only a small fraction of the active worksheet displays on the screen at one time. You view the portion of the worksheet on the screen in a worksheet window. Below and to the right of the worksheet window are scroll bars, scroll arrows, and scroll boxes which you can use to move around the active worksheet

7.) Illustrate the parts of MS Excel Windows:

8.) What are the common built-in commands of MS Excel?
The common built-in commands in MSExcel are the following:
§ AVERAGE ( ) function - we find the class's average (or arithmetic mean) grade and its syntax is =AVERAGE (number1, number2..).
§ MEDIAN ( ) function - the median gives the middle number in a set of numbers and its syntax is =MEDIAN (number1, number2..).
§ MODE ( ) function - mode gives the most frequently occurring value of a set of numbers and its syntax is =MODE (number1, number2..).
§ COUNT ( ) function - gives the number of cells that contain numbers. Its syntax is =COUNT (value1, value2..).
§ MAX ( ) function returns the largest value in a set of numbers. Its syntax is =MAX (number1, number2..).
§ MIN ( ) function returns the smallest value in a set of numbers. Its syntax is =MIN (number1, number2..)


The SUM Function
To add a column or a row of numbers follow the steps below:

Click on an empty cell. In the above example, we chose cell F1 in which to enter our formula.
With your keyboard type the equal sign (=)
Begin the function by typing SUM (* don’t forget to open the parentheses!
Either type A1:E1 or use your mouse to highlight cells A1, B1, C1, D1 and E1
Complete the function with a closing parentheses by typing )
Press the key
Note the sum of the numbers is displayed in cell F1 and the formula can be found in the formula bar.
The AVERAGE Function
To average a column or a row of numbers follow the steps below:
Click on an empty cell. In the above example, we chose cell F1 in which to enter our formula.
With your keyboard type the equal sign (=)
Begin the function by typing AVERAGE( * Don't forget to open the parentheses!
Either type A1:E1 or use your mouse to highlight cells A1, B1, C1, D1 and E1
Complete the function with a closing parentheses by typing )
Press the key
Note the average of the numbers is displayed in cell F1 and the formula can be found in the formula bar.
The SQRT (Square Root) Function


To find the square root of a number located within a worksheet cell follow the steps below:
Click on an empty cell. In the above example, we chose cell B2 in which to enter our formula.
With your keyboard type the equal sign (=)
Begin the function by typing SQRT( * Don't forget to open the parentheses!
Either type B1 or use your mouse to click on cell B1
Complete the function with a closing parentheses by typing )
Press the key


9.) Name at least five (5) charts of MS Excel and describe each briefly.

Floating Column Charts
Some kinds of data look very nice and are easily understood in the form of a "floating" column or bar, in which the column floats in the chart, spanning a region from a minimum value to a maximum value.

Special Format for Minimum and Maximum
Ever want to apply special formatting to just a certain point? This example shows you how to highlight the minimum and maximum values of a series by using a different marker for each, and data labels. We'll accomplish this with two extra series in the chart, one for minimum and one for maximum.

Use an Arrow to Indicate Special Points
People often want to use an arrow or other symbol to indicate a point in a chart. If you draw an arrow, or any AutoShape, in a chart, it is not in any way tied to the data or to the chart axes, so it will not move to keep up with a point as the axes change or the chart resizes. Even if the chart does not change, an AutoShape is not guaranteed to be in exactly the same position the next time the file is opened. This technique shows how to attach an indicator (arrow) to a point by creating custom markers for the Min and Max series.

Horizontally Banded Chart Background
A chart could be made more informative by selectively shading regions of the background with different colors. For example, a run chart may show colored bands to indicate standard deviations of a process value from the mean. Excel only provides the ability to add one color to the background, but multiple colors can be added by creating a combination chart with added area chart series colored as desired. This tutorial shows how to construct such a chart.

Make Gridlines Square
A common question people ask is "How do I format my chart so its gridlines make a square pattern?" Excel has no setting that forces equally spaced horizontal and vertical gridlines or horizontal and vertical axis ticks, but you can achieve this effect using VBA. Below is a chart after making the appropriate scale adjustment.