Chater-3 (Spread Sheet (MS-Excel))

Question: How to table create in MS-Excel?

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.

Question: How to implement various charts 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.

Question: What is cell reference in MS-Excel?

Answer: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate.

In one or several formulas, you can use a cell reference to refer to:

·         Data from one cell on the worksheet.

·         Data that is contained in different areas of a worksheet.

·         Data in cells on other worksheets in the same workbook.

Question: Define functions in MS-Excel?

Answer: A function is a preset formula in Excel and Google Spreadsheets.
Like formulas, functions begin with the equal sign ( = ) followed by the function's name and its arguments. The function name tells Excel what calculation to perform. The arguments are contained inside round brackets.

For example, the most used function in Excel is the SUM function, which is used to add together the data in selected cells. The SUM function is written as -

= SUM ( D1 : D6 )

Here the function adds the contents of cell range D1 to D6 and displays the answer in cell D7.

Question: How to sorting and filtering data in MS-Excel?

Answer:

Sorting

Sorting lists is a common spreadsheet task that allows you to easily reorder your data. The most common type of sorting is alphabetical ordering, which you can do in ascending or descending order.

To sort in alphabetical order:

• Select a cell in the column you want to sort (In this example, we choose a cell in column A).
• Click the Sort & Filter command in the Editing group on the Home tab.
• Select Sort A to Z. Now the information in the Category column is organized in alphabetical order.

Filtering cells

Filtering, or temporarily hiding, data in a spreadsheet is simple. This allows you to focus on specific spreadsheet entries.

To filter data:

• Click the Filter command on the Data tab. Drop-down arrows will appear beside each column heading.
• Click the drop-down arrow next to the heading you would like to filter. For example, if you would like to only view data regarding Flavors, click the drop-down arrow next to Category.
• Uncheck Select All.
• Choose Flavor.
• Click OK. All other data will be filtered, or hidden, and only the Flavor data is visible.

Question: What is conditional formatting in MS-Excel?

Answer: Conditional Formatting (CF) is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 100.

Question: What is workbook and worksheets in MS-Excel?

Answer: A workbook is the MS Excel file in which you enter and store related data. A worksheet (also known as a spreadsheet) is a collection of cells on a single “sheet” where you actually keep and manipulate the data. Each workbook can contain many worksheets.

Question: How to protect workbook and worksheets in MS-Excel?

Answer:

Protect worksheet elements

1.      Select the worksheet that you want to protect.

2.      To unlock any cells or ranges that you want other users to be able to change, do the following:

a.       Select each cell or range that you want to unlock.

b.      On the Home tab, in the Cells group, click Format, and then click Format Cells.

c.       On the Protection tab, clear the Locked check box, and then click OK.

3.      To hide any formulas that you do not want to be visible, do the following:

a.       In the worksheet, select the cells that contain the formulas that you want to hide.

b.      On the Home tab, in the Cells group, click Format, and then click Format Cells.

c.       On the Protection tab, select the Hidden check box, and then click OK.

4.      To unlock any graphic objects (such as pictures, clip art, shapes, or Smart Art graphics) that you want users to be able to change, do the following:

a.       Hold down CTRL and then click each graphic object that you want to unlock.

This displays the Picture Tools or Drawing Tools, adding the Format tab.

b.      On the Format tab, in the Size group, click the Dialog Box Launcher next to Size.

c.       On the Properties tab, clear the Locked check box, and if present, clear the Lock text check box.

5.      On the Review tab, in the Changes group, click Protect Sheet.

6.      In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.

Protect workbook elements

1.      On the Review tab, in the Changes group, click Protect Workbook.

2.      Under Protect workbook for, do one or more of the following:

o    To protect the structure of a workbook, select the Structure check box.

o    To keep workbook windows in the same size and position every time the workbook is opened, select the Windows check box.

3.      To prevent other users from removing workbook protection, in the Password (optional) box, type a password, click OK, and then retype the password to confirm it.