Explore topic-wise InterviewSolutions in .

(a) In Excel, by default, the text is left aligned.

(b) Word wrapping distributes the text in multiple lines.

1. 26th column: Z

2. 28th column: AB

Excel is a spreadsheet software package.

(c) The default page orientation in Excel is Portrait.

Titles have not been entered in chart elements step. And, these cannot be added later once the chart is ready.

1. At cell F2, type = D2*E2 and then copy this formula using Fill handle in range F3: F5.

2. At cell G2, type = C2 – D2 and then copy this formula using Fill handle in range G3: G5.

3. At cell H2, type = IF (G2 > = 0, G2*E2, 0) and copy this formula using Fill handle in the range H3 : H5.

4. He should create chart of any type to graphically represent his data.

5. No, the recalculation is not again required because the formula and functions that are given changes according to the values in the cells.

Various components or parts of the chart are as follows: 

1. X-axis Refers to a horizontal axis, which is also known as category axis. 

2. Y-axis Refers to a vertical axis also known as value axis. 

3. X-axis title Conveys the full details of the X-axis values. 

4. Y-axis title Conveys the full details of the Y-axis values. 

5. Data series Refers to a set of data that you want to display in a chart. 

6. Chart area Refers to the total space that is enclosed by a chart. 

7. Plot area Refers to the main region of the chart in which your data is plotted. 

8. Chart title Denotes the type of data plotted in a chart. 

9. Legend In a chart showing different data series, a unique color or pattern is assigned to each data series. This unique color of the pattern is known as a legend. 

10. Gridlines These are the horizontal and vertical lines within the plot area in a chart. 

11. Data label Provides additional information about a value in the chart, that is coming from a worksheet cell.

MIN function will return O(zero) in this case.

Using electronic spreadsheets, a large volume of data can be stored in worksheets. Worksheets can be managed, edited, viewed, retrieved and printed easily in the desired format. Electronic spreadsheets support charts, which represent data pictorially.

Yes, it is possible to remove a worksheet. To remove a worksheet right-click on the Sheets tab of the sheet that you want to delete and choose Delete from the popup menu, or choose Delete Sheet from the pop-up menu of Delete option on cells group under Home tab.

In Excel, every formula must start with a = (equal) sign.

Since Sana has not put = sign before 49+30, thus she is not able to see the desired result will be displayed if she will use = (49 + 30) formula.

Excel’s COUNT() function can be used to total the number of cells in a selected range. The COUNT() function will add up the number of cells in a selected range that contains numbers.

1. = MAX(A1 : B20)

2. = AVG(E5:15)

3. =IF(J10 >= 40, “Pass”, “Fail”)

1. = MIN(A1 : C5)

2. = IF(D5>5,000, “Discount”, “NoDiscount”)

The only difference between the COUNT() and COUNTA() function is that the COUNTA() function computes the numbers as well as text entries, while the COUNT() function counts only numbers.

The cut and paste operations copy data from the source range to a target range and erase it from the source range.

When you copy or cut, the selected range gets surrounded by a moving border.

(b) Ctrl+End shortcut is used to bring the cursor to the last cell.

1. Decide on a total purchasing need for next night/week/month/year.

2. Change figures in spreadsheet.

3. Raise/lower/add/delete any value.

4. Compare results with predicted/total needed/target results.

5. To general use for budgeting like to calculate profit.

1. At cell F2, type =D2*E2 and then copy this formula using mouse Fill handle onto range F3: F5.

2. At cell G2, type =IF(C2=”Consumable”, F2*0.07, F2*0.11) and then copy this formula using mouse Fill handle onto range G3: G5.

3. At cell G6, type =SUM(G2: G5).

(d) AutoFill can apply from any side.

Microsoft Excel should be used because it cannot only be used for storing data but also be used to perform calculations and analysis of the data.

(d) Line chart emphasizes on a rate of change rather than volume of change over time.

(b) Pie chart can select only one range of the data series.

Excel supports three types of cell referencing, which are as follows:

1. Relative Every relative cell reference in formula automatically changes when the formula is copied down a column or across a row. As the example illustrated here shows, when the formula is entered (= B4 — C4) in Cell D4 then this formula copied in D5 then it will change into (= B5 — C5) related to the cell. 

2. Absolute An absolute cell reference is fixed. Absolute references do not change if you copy a formula from one cell to another. Absolute references have the dollar sign ($) like $S$9. As the shows, when the formula =C4*$D$9 is copied from the row, the absolute cell reference remains as $D$9. ‘ 

3. Mixed A mixed cell reference has either an absolute column and a relative row, or an absolute row and a relative column, e.g. $A1 is an absolute reference to column A and a relative reference to row 1. As a mixed reference is copied from one cell to another, the absolute reference stays the same but the relative reference changes.

(b) $ sign in a formula need to add before row number and column letter for absolute referencing.

(b) It represents mixed reference because we are using $ sign before 5 only. Hence, as we will drag this only B will change but 5 will remain the same.

A workbook is a collection of worksheets. By default, there are three worksheets in every workbook.

There are two methods of adding a new worksheet iasm Excel workbook; Insert worksheet tab at the bor >m and Insert button on cells group under Home tab.

1. SUM Function 

This function, as clear from the name, is used to add all the values provided as an argument and to display the result in the cell containing the function. 

Argument Type All Numbers Return Type Number 

Syntax = SUM(number1, number2, )

e.g. if you want to display the sum of values of cells Al, A2, A5, and A6 in cell A9, then you need to simply type = SUM(A1, A2, A5, A6) in cell A9 and press Enter. The sum will be displayed in cell A9. If you want to add a range of values, then provide that range in SUM function as an argument. 

e.g. if you want to add values from Al to A5, then write like =SUM(A1: A5). 

• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values or text in the array or reference are ignored. 
• If any arguments are error values or if any arguments are text that cannot be translated into numbers, Excel displays an error. 

2. AVERAGE Function 

This function calculates the average of all the values provided as an argument to this function. 

Argument Type All Numbers Return Type Number 

Syntax = AVERAGE(number1, number2, ) 

e.g. to calculate the average of the values of a range starting from Al to A5 in cell B9, you need to write = AVERAGE(A1: A5) in cell B9. 

3. COUNT Function 

This function counts the number of cells that contain numbers and numbers within the list of arguments. 

Argument Type Any Type Return Type Number 

Syntax = COUNT(value1, value2, ) 

e.g. if the values contained in cells Al, A2, A3, and A4 are 5, 7, TRUE and 10 respectively, then – COUNT(Al: A4) will return 3.

•  Arguments that are numbers, dates or text representation of numbers (e.g. a number enclosed in quotation marks, such as ‘1’) are counted. 
• Arguments that are error values or text that cannot be translated into numbers are not counted. 
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text or error values in the array or reference are not counted. 

4. COUNTA Function

This function is similar to the COUNT( ) funtion. The only difference is that the COUNTA() function also calculates the text entries even when the entries contain an empty string of length 0(zero), i.e. “ ’ ”, but empty cells are ignored. The COUNTA() function counts the total number of values in the list of arguments. 

Argument Type Any Type Return Type Number 

Syntax = COUNTA (number 1, number 2, …) 

e.g. if the value contained in cells Al, A2, A3, and A4 are 5, 7, TRUE and 10 respectively then = COUNTA (Al: A4) will return 4. 

5. MAX Function 

This function is used to return maximum value from a list of arguments. 

Argument Type All Numbers Return Type Number 

Syntax = MAX(number1, number2, ….) 

e.g. if the values contained in cells Al, A2, A3 and A4 are 5, 7, 2 and 10 respectively then = MAX(A1:A4) will return 10. 

• MAX will consider only numeric and logical values to compute maximum. 
• If an argument is an array or reference, only numbers in that array or reference are used. Empty cells or text in the array or reference are ignored. 
•  If the arguments contain no numbers, MAX returns 0 (zero). 
• Arguments that are error values or text that cannot be translated into numbers cause errors.

(b) You need to press Ctrl + Shift + Spacebar for selecting the entire worksheet in Excel.

The various types of charts in Excel are given below:

Line Chart: Data that is arranged in columns or rows on a worksheet can be plotted in a line chart. Line charts can display continuous data over time, set against a common scale and are therefore ideal for showing trends in data at equal intervals. In a line chart, category data is distributed evenly along the horizontal axis and all value data is distributed evenly along the vertical axis. 

Pie Chart: Data that is arranged in one column or row only on a worksheet can be plotted in a pie chart. Pie charts show the size of items in one data series, proportional to the sum of the items. The data points in a pie chart are displayed as a percentage of the whole pie. 

Scatter Chart: Data that is arranged in columns and rows on a worksheet can be plotted in an XY (scatter) chart. Scatter charts show the relationships among the numeric values in several data series or plots two groups of numbers as one series of XY coordinates. 

Bar Chart: Data that is arranged in columns or rows on a worksheet can be plotted in a bar chart. Bar charts illustrate comparisons among individual items. 

Area Chart: Data that is arranged in columns or rows on a worksheet can be plotted in an area chart. Area charts emphasize the magnitude of change over time and can be used to draw attention to the total value across a trend, e.g. data that represents profit over time can be plotted in an area chart to emphasize the total profit.

Creating a Chart:

Here is a worksheet that shows the marks of students in a class subject wise. To create a chart, do the following:

1. Select the data that you want to show, including the column titles and the row labels. 

2. Then click the Insert tab and in the Charts group, click the Column button. You could select another chart type, but column charts are commonly used to compare items. 

3. After you click Column, you’ll see a number of column chart types to choose from. Click Clustered Column, the first column chart in the 2-D Column list. 

A ScreenTip displays the chart type name when you rest the pointer over any chart type. The ScreenTip also provides a description of the chart type and gives you information about that, when to use each one. 

If you want to change the chart type after you create your chart, click inside the chart. On the Design tab under Chart Tools, in the Type group, click Change Chart Type and select another chart type.

(d) XY chart is also called Scatter chart.

(c) A chart is a pictorial representation of data that created on the basis of values in a spreadsheet.

(d) We can move data using cut and paste and also drag and drop method is the easiest one.

(d) You can place the chart legend in the chart at Bottom, Corner, Top, Right, and Left.

(b) = 2*(6-4) = 2*(2) = 4

(c) Text displayed at data points to indicate the specific category is called data label.

(d) A cell range always has the : (colon) symbol between the address of ranges.

(c) Because the logical values and numbers directly provided as an argument of COUNTA, a function will be counted.

(b) Marks of each student tor Mathematics will be required for calculating average marks of mathematics.

The correct answer is

(a) 22.167

Formula for adding values of cells A1 to A5 would be =SUM(A1 : A5).

=B1+$A$2 will be copied into B3.

Cells A2, A3, A4, and A5 will contain Tuesday, Wednesday, Thursday and Friday respectively.

Formula plays a major role in spreadsheet making. If we all needed to do is to put text and numbers in rows and columns, just about any Word processing program would fill the bill. Formula allows us to perform calculations using values from any cells to have the result appear in the formula cell.

-4 will be contained in the cell as a result.