Module 04: Spreadsheet

Spreadsheet

1. What is sorting? How can you sort a cell range in MS Excel?

Sorting means arranging items in order from smallest to largest (ascending) or from largest to smallest (descending). 

To Sort:

i. Select the range;

ii. Go to Data tab;

iii. In the Sort & Filter Group, Click Sort A to Z or Z to A icon

This will sort the selected range.

2. What are the two orders in which you can sort your data? If you want to sort a name column in an alphabetical order then which sort order should you choose?

The two orders for sorting data are smallest to largest (ascending) or from largest to smallest (descending).

To sort a name column in alphabetical order-

i. Select the column;

ii. Go to Data tab;

iii. In the Sort & Filter Group, Click Sort A to Z or Z to A icon

This will sort the selected range alphabetically.

3. What is a Function in MS Excel? What function is used for counting the number of cells in a range?

A function allows us to calculate a result such as adding numbers together, or finding the average of a range of numbers.

To count cells functions available: COUNT: counts

cells containing numbers. COUNTA: counts the

cells that are not empty. COUNTBLANK: counts empty cells.

4. How can you select a range of cells not next to each other?

To select a range of cells next to each other-

i. Select the first cell;

ii. Keep the Ctrl key pressed;

iii. Select the next cell and so on.

5. How can you rename a worksheet?

To rename a worksheet-

i. Double-click on the sheet name in Worksheets tab

ii. Write the new name

iii. Press Enter

6. What is Numbers & Date formatting? How can you apply that in your MS Excel Worksheet?

Number formatting sets how to show number enter in a cell. Date formatting sets how to show date value entered in a cell.

To set Number format: Right click on cell > Click Format Cells> Select Number > Set Decimal places and Select an Negative number option > Press OK.

To Set Date Format: Right click on cell > Click Format Cells> Select Date> Select a format > Press OK.

7. What is the default text and number alignment in MS Excel?

By default:

Text is aligned to left within a cell

Numbers are aligned to right within a cell

 

8. What is Chart in MS Excel? Name three types of charts?

Charts are visual representations of worksheet data which makes it easier to understand the data in a worksheet because users can easily pick out patterns and trends difficult to see. Three type of chart:

i. Column chart 

ii. Line Chart

iii. Pie Chart

9. What is AutoFill in MS Excel 2007? What is the extension of an excel file.

Auto fill is a feature of Excel to identify and fill a series of text or numbers, automatically, in a given range. The extension of an Excel file is .xlsx.

10. What is a function in MS Excel? How can you display the maximum value within the range C4:C9?

A function allows us to calculate a result such as adding numbers together, or finding the average of a range of numbers.

To display Maximum value: Select cell where to display > Click Formulas tab > Click down arrow at right of AutoSum > Click Max > Adjust Cell Range to C4:C9 > Press Enter.

11. How can  you move the contents of a cell or a range of cell between worksheets (in different workbooks)

i.  Open both workbooks;

ii. Go to the workbook from where to move;

iii. Select the cell or range > Press Ctrl + X;

iv. Go To View Switch Tab > Click Switch Windows > Click the destination workbook name

v. Click on cell > Press Ctrl + V

12. Why do you use the absolute cell referencing?

If we use absolute cell reference in a formula, Excel will not adjust the cell reference while copying the formula.

13. How many rows and columns are there in a single worksheet?

There are 10,48576 Rows and 16384 Columns in MS Excel 2007.

14. How can you make Column items into row?

To make the column items into row-

i.  Select the range of columns;

ii. Place the cursor to the cell of the target row;

iii. Click on the down arrow down to the Paste icon in the Home tab;

iv. Select Paste Special; Paste Special dialogue box appears;

v. Check the Transpose box;

vi. Click OK, this will paste the column items into the row.

15. How should you copy a column heading of a list from one sheet and paste it as a row heading in another sheet?

i.  Select the Heading list and Press Ctrl+C

ii. Place the cursor to the target cell of other sheet;

iii. Click on the down arrow down to the Paste icon in the Home tab;

iv. Select Paste Special; Paste Special dialogue box appears;

v. Check the Transpose box;

vi. Click OK, this will copy the column heading of a list from one sheet and paste it as a row heading in another sheet.

16. Suppose cell A1 contains “JAN” and you want to print the rest of the series in the column then how should you do it?

To print the rest of the series in the column-

i. Move the Mouse pointer to the bottom-right corner of the cell A1; the mouse pointer shape will change to the shape of a small black cross.

ii. Keeping the mouse button pressed, drag to the cell A12,

iii. Now, release the mouse button; This makes Excel auto fill the series with the rest of the months, namely January to December.

 

17. How to change time format of an excel sheet?

i. Select the cell, range, row or column of date;

ii. Right click on the selected item;

iii. From the pop-up menu, select Format Cell command;

iv. Click on Date section; Select the desired date format;

v. Click OK, This will change the date format of a excel sheet.

 

18. How can you copy a content in excel worksheet?

i. Select the cell, range, row or column;

ii. Press Ctrl+C to copy the selected item to the clipboard;

iii. Click at the location where to paste;

iv. Press Ctrl+V; This will copy and paste the content of an excel sheet.

19. Write about #REF!, ####, #NAME?, #DIV/0!

#REF! indicates that the cell reference is invalid, often seen when the cell is deleted after applying the formula.

#### indicates that the contents of the cell can not be displayed correctly as the column is too narrow.

#NAME? indicates that Excel doesn’t recognize the text contained within formula.

#DIV/0! indicates that a number is tried to be divided by zero.

20. How can you change date format from 9-Jan-12 to 09-01-12? in MS Excel?

To change the date format-

i. Right click on the cell>Select Format Cell; Format Cell dialogue box appears;

ii. Click on Date section; Select the Date format as 09-01-12;

iii. Click OK, this will change the date format from 9-Jan-12 to 09-01-12.

21. How can you copy formula in Excel?

To copy formula in Excel-

i. Click the cell containing the formula we want to copy.

ii. Click and drag the cell’s fill handle across or down the number of cells to which we want to copy the formula.

This makes Excel copy the formula into each cell we drag over.

22. What are operators in Excel?

Operators specify the type of calculation that we want to perform on the element of a formula. Operators are-

•          + (Plus)
•         -  (Minus)
•          /  (Divide)
•        * (Multiply)
•          % (Percentage)

 

23. What are ‘IF Functions’ in Excel?

The IF function checks to see if the specified condition is true or false. If the condition is true, it returns the specified result, if not returns the other.

24. How can you print titles on every page when printing in MS Excel?

To print titles on every page when printing-

i. Click on the Page Layout tab;

ii. Click on Print Titles in within the Page Setup group; Page Setup dialogue box appears;

iii. Click Rows to repeat at top box; click the top row;

iv. Click OK; this command closes the dialogue box.

Now the Titles will be printed at the top of every printed page.

25. How can you print Excel row and column headings?

To print the Excel row and column headings-

i. Click on the Page Layout tab;

ii. Within the Sheet Options group, click on the Print check box under the Headings;

This will print the Excel row and column headings.