Chapter-4 (Spreadsheet Tool)

Question: Write Introduction to Spreadsheets?

Answer: A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets.

Question: Write Concept of Worksheets and Workbooks?

Answer: In Microsoft Excel, a workbook is simply an Excel file that stores entered related data. Workbooks are capable of holding an almost infinite number of worksheets, depending on the size and the relevance of the data. It is, essentially, a book filled with the data from multiple worksheets. Workbooks are usually labeled by the data which is on each worksheet – if all the pages of the workbook hold the same type of data, that workbook will be named for the relevant data that it holds.

In Excel, a worksheet is an amalgamation of a number of cells that hold data pertaining to a certain piece of information. It is also known as a spreadsheet. A user is able to enter, modify, and manipulate the data that is entered in the spreadsheet. With a spreadsheet, a user is essentially entering information onto a page of a workbook.

Question: How to Creating and saving a worksheet?

Answer:

 

Starting EXCEL

By selecting the Start button located at the bottom left hand side of your screen, then selecting Programs then

Microsoft Excel.  

SAVING WORKBOOKS

Go to File> Save/ Save As, and Excel will display the Save As dialog box.

Question: How to Use the Fill Handle and Fill Button to Autofill Numbers and Dates?

Answer:

 

Autofill a Growth Series (Geometric Pattern)

Specifying growth series in newer versions of Excel is easy. The fastest way is to enter the first two numbers and then right-click on the Fill Handle.

1. In the Excel worksheet, enter 1 in the first cell and 2 in the cell immediately below.
2. Highlight the two cells, and hover the cursor over the Fill Handle (+).
3. Press the RIGHT mouse button, drag down the column, and release. Click Growth Trend.
4. Because you entered two numbers, Excel knows that the step value is 2. The autofill results are shown.

 

 Autofilling Dates (Days, Weekdays, Months, and Years)

 

1. Type the beginning date in the cell. For our example, we've entered the first day of the year. Right-click on the Fill Handle and drag down the column.
2. When the mouse button is released, a menu displays as shown in the first image. We select Months in our example.
3. Excel autofills the column with the first day of each consecutive month as shown.

Question: How to Editing and formatting a worksheet including changing colour, size, font and alignment of text?

 

Answer:

 

Change the default font or font size for new workbooks

 

1. Click the Microsoft Office Button, and then click Excel Options.
2. In the Popular category, under When creating new workbooks, do the following:
1. In the Use this font box, click the font that you want to use.
2. In the Font Size box, enter the font size that you want to use.

Changing the alignment or orientation of data

For the optimal display of the data on your worksheet, you may want to reposition the text within a cell. You can change the alignment of the cell contents, use indentation for better spacing, or display the data at a different angle by rotating it.

Question: How to inserting or deleting cells, rows and columns in ms-excel?

Answer:

Insert blank cells on a worksheet

1. Select the cell or the range of cells where you want to insert the new blank cells. Select the same number of cells as you want to insert. For example, to insert five blank cells, select five cells.
2. On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Cells.
3. In the Insert dialog box, click the direction in which you want to shift the surrounding cells.

Insert rows on a worksheet

1. To insert a single row, select either the whole row or a cell in the row above which you want to insert the new row. For example, to insert a new row above row 5, click a cell in row 5.
2. On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Sheet Rows.

Insert columns on a worksheet

1. To insert a single column, select the column or a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of column B, click a cell in column B.
2. On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Sheet Columns.

Delete cells, rows, or columns

1. Select the cells, rows, or columns that you want to delete.

On the Home tab, in the Cells group, click the arrow next to Delete, and then do one of the following:

·         To delete selected cells, click Delete Cells.

·         To delete selected rows, click Delete Sheet Rows.

·         To delete selected columns, click Delete Sheet Columns.

2.      If you are deleting a cell or a range of cells, in the Delete dialog box, click Shift cells left, Shift cells up, Entire row, or Entire column.

Question: How to insert Formula- Entering a formula in a cell in ms-excel?

Answer:

Create a simple formula by using constants and calculation operators

1. Click the cell in which you want to enter the formula.
2. Type = (equal sign).
3. To enter the formula, do one of the following:
• Type the constants and operators that you want to use in the calculation.

Example formula	What it does
=5+2	Adds 5 and 2
=5-2	Subtracts 2 from 5
=5/2	Divides 5 by 2
=5*2	Multiplies 5 times 2
=5^2	Raises 5 to the 2nd power

• Click the cell that contains the value that you want to use in the formula, type the operator that you want to use, and then click another cell that contains a value.

Example formula	What it does
=A1+A2	Adds the values in cells A1 and A2
=A1-A2	Subtracts the value in cell A2 from the value in A1
=A1/A2	Divides the value in cell A1 by the value in A2
=A1*A2	Multiplies the value in cell A1 times the value in A2
=A1^A2	Raises the value in cell A1 to the exponential value specified in A2

1. Press ENTER.
2. You can enter as many constants and operators as you need to achieve the calculation result that you want.
3. Excel follows the standard order of mathematical operations. For example, the formula =5+2*3, multiplies two numbers and then adds a number to the result – the multiplication operation (2*3) is performed first, and then 5 is added to its result.

Question: Describe between Switch between relative, absolute and mixed references?

Answer:

Switch between relative, absolute and mixed references

By default, a cell reference is relative. For example, when you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A), and in the same row (2). A formula that contains a relative cell reference changes as you copy it from one cell to another. As an example, if you copy the formula =A2+B2 from cell C2 to D2, the formula in D2 adjusts downward by one row and becomes =A3+B3. If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (A and B) and row (2) with a dollar sign ($). Then, when you copy the formula (=$A$2+$B$2) from C2 to D2, the formula stays exactly the same.

In less frequent cases, you may want to make a cell reference "mixed" by preceding either the column or the row value with a dollar sign to "lock" either the column or the row (for example, $A2 or B$3). To change the type of cell reference:

1.      Select the cell that contains the formula.

2.      In the formula bar, select the reference that you want to change.

3.      Press F4 to switch between the reference types.

Question: How to printing a worksheet in ms-excel?

Answer:

Print several workbooks at once

1. Click Microsoft Office Button, and then click Open. Keyboard shortcut you can also press CTRL+O.
2. Hold down CTRL and click the name of each workbook that you want to print.
3. On a computer that is running Windows Vista. Right-click the selection, and then click Print.

Question: Explain Use simple Statistical functions: SUM(), AVERAGE(), MAX(), MIN(), IF()?

Answer:

In order to have a cell showing the result of a statistical calculation (like the sum or the average of some other cells), we use what is called a “Function”.

We get the function by clicking a button, or by writing a short code composed of few letters.

Here is a description of the functions learned, with the relevant “function code” appearing at the beginning of each paragraph:

SUM – A sum function. Use it when you want a cell to show the sum of numbers written in a selected range of cells.
For example: you took 5 different loans during the past year, and you want to calculate their total sum.

AVERAGE – An average function. Use it when you want a cell to show the average of numbers written in a selected range.
For example: you have made nice income during the past week, and you want to calculate your average income per day.

MAX – The highest number. Use it when you want a cell to show the highest number within a selected range.
For example: you have a list of test scores, and you want a certain cell to show the highest score among them.
Another example: you have a list of sales made during this month, and you want a cell to show the highest sale.

MIN – The lowest number. The same as MAX, but relates to the lowest number.

COUNT – Counts numbers. Use it when you want to count how many numbers are there in a selected range.
For example, you have a list of students, of which some of them got scholarships. Using the COUNT function you can know how many students got scholarships. What you will do is count using this function the cells containing the scholarships amounts.

Another example: you have a big table of products with their prices. Because every product has only one price, counting the prices will tell you how many products you have.

COUNTIF – Use Excel Countif function when you want to count how many occurrences of a specific criteria appear in a selected range.
For example: How many times does the name “Jack” appear in a list of worker names?
Another Countif Excel example: How many prices higher than $1000 are there in a given price list?

COUNTA – Counts all the cells in a selected range that contain any value (cells that are not empty). This is a broader version of the COUNT function, because it counts cells containing numbers, as well as texts.
For example, you have a large amount of data, and you want to count how many items does it consist. These items can be student names, ID numbers, salary payments, addresses, dates etc.

COUNTBLANK – Counts all the empty cells in a selected range. Use it when you want to know how many cells in a selected range do not contain any value.

For example: a few students didn’t hand their homework, therefore didn’t get a score. If you want to know how many students didn’t hand their work, use the COUNTBLANK function on the score list, and see how many cells are empty, meanings: didn’t get a score.

Question: How to Inserting tables in worksheet?

Answer:

 

Insert a table

1.      On a worksheet, select the range of cells that you want to include in the table. The cells can be empty or can contain data.

2.      On the Insert tab, in the Tables group, click Table.

Keyboard shortcut  You can also press CTRL+L or CTRL+T.

3.      If the selected range contains data that you want to display as table headers, select the My table has headers check box.

Question: How to insert Embedding Charts of various types: Line, Pie, Scatter, Bar and Area in ms-excel?

Answer:

Create a Chart

• Click the Insert tab.
• Click the chart type from the Charts section of the ribbon. The sub-type menu displays.
• Click the desired chart sub-type. The chart appears on the worksheet.
• If you want to create a second chart, click somewhere in the worksheet to "deselect" the current chart first, or the new chart will replace the current chart.

Type	Description	When to Use
Pie Chart	Displays the percentages of a whole for each member in a series.	Excellent chart for comparing values in a single series as percentages of a whole.
Column Chart	Using vertical columns, displays values for one or more series over time or other category.	This chart type is especially effective in comparing values for multiple series. The 3-D Column chart displays multiple series over three axes (X, Y, and Z).
Bar Chart	Displays values for one or more series using horizontal columns.	Though useful for single or multiple series, this chart type is especially effective in comparing a large quantity of values in a single series.
Line Chart	Displays values as equally spaced points connected with a line.	This chart is especially useful in displaying trends over time or other ordered category for single or multiple series of data.