MS Excel and PowerPoint Long Answer Type Questions Part 3

MS Excel and PowerPoint Long Answer Type Questions Part 3

MS Excel and PowerPoint Long Answer Type Questions Part 3

Question 1.
What is a cell Reference? Explain the various types of Cell References.?
Answer:
The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column C and row 10 is CIO. In other words, A cell reference indicates a cell’s location. Cell references are often used in formulas to calculate values stored in the worksheet.

MS Excel and PowerPoint Long Answer Type Questions Part 3 1

Relative Reference: Whenever a formula is copied from one part of the worksheet to the other, the address of the cell changes, relative to the row or column into which the formula is copied as shown in the following fig.

This ability to change the formula to match the location where you copy it is called Relative Referencing (or Relative Addressing). The formula is always adjusted relative to its location.

Absolute Reference: An absolute reference is a cell reference that does not change when you copy or more a formula. To create an absolute reference, Insert dollar sign ($) in front of each column number and row number, like this =$B$5. You can copy this formula to any cell, and it will always reference cell B5. Thus, in absolute referencing, both row and column are fixed.

Mixed Reference: Mixed Reference is a combination of relative and absolute reference. That is, one part of the cell address is absolute and the other is relative. For example, in $B5 the column is absolute but the row is relative. Therefore, if you copy the formula ($B5) across the screen, it remains unaltered. Whereas if you copy the formula vertically, the row number automatically changes. Similarly in B$5, the column letter is relative but the row number is absolute.

Question 2.
How to change the font color, size, or type in Excel
Answer:
In Microsoft Excel, a user can change the properties of text in any cell, including font type, size, color, and make it bold, italic, or underlined. A user can also change the color of a cell’s background and the border around a cell. The following picture is a graphic illustration of the font and cell format bar in Excel with a description of each option.

Changing font type
To change the text font within a Microsoft Excel spreadsheet, follow the steps below.

  • Select the cell containing the text you want to change.
  • Click the down arrow next to the font field on the format bar. (If you want to change the font to bold, italic, or underlined, click the B, I, or U on the format bar.)
  • After clicking the down arrow for the font, select from each of the installed fonts on your computer. Click the font you want to use, and the text in the selected cell changes.

MS Excel and PowerPoint Long Answer Type Questions Part 3 2

Changing font size
To change the text size within a Microsoft Excel spreadsheet, follow the steps below.

  • Select the cell containing the text you want to change.
  • Click the down arrow next to the size box on the format bar. Usually, the default size is 11 or 12, as shown in the above example.
  • After clicking the down arrow for the size, there is a selection of different sizes to choose. Some fonts may not scale properly, so they may have limited size options.

Changing font color
To change the text color within a Microsoft Excel spreadsheet, follow the steps below.

  • Select the cell containing the text you want to change.
  • Click the down arrow next to the text color icon. It is usually displayed as the letter “A” with a red underline, as shown in the example above.
  • After clicking the down arrow for the text color, select the color you want to make the text.

Question 3.
Discuss about Charts and their Types in Excel?
Answer:
A chart is a tool you can use in Excel to communicate data graphically. Charts allow your audience to see the meaning behind the numbers,, and they make showing comparisons and trends much easier.

MS Excel and PowerPoint Long Answer Type Questions Part 3 3

Before creating a chart, it is important to plan the information that the chart is to show and how it is to look. Use the following guidelines when planning a chart.

  • Determine the purpose of the chart.
  • Identify the data relationships that are to be communicated visually.
  • Determine the results that are to be seen.
  • Decide which chart type is most appropriate to use.
  • Identify the worksheet data that the chart is to illustrate.
  • Sketch the chart and then use the sketch to decide where the chart elements should be placed.

Chart Types:
Column: This type of chart is useful for showing data changes over a period of time. It is also useful for illustrating comparisons among items. Data that is arranged in columns or rows on a worksheet can be plotted in a column chart. Categories are typically organized along the horizontal axis and i values along the vertical axis.

Line: This type of chart is used to display continuous data over a period of time. It is useful for showing trends in data at equal intervals. Category data is distributed evenly along the horizontal axis and the vertical axis. Data that is arranged in columns and rows in a worksheet can be plotted..

Pie or Doughnut: These chart types are used to display the contribution of each item to the total. Data for the chart can be selected either before or after the chart is created.

Bar: These charts are used to illustrate comparisons among individual items. This type of chart should be used when the axis labels are long or when the values shown are durations. Data that is arranged in columns or rows in a worksheet can be plotted in a bar chart.

Area: Area charts are used to emphasize the magnitude of change over time. They can be used to draw attention to the total value across a trend. These charts can also be used to show the relationship of parts to a whole.

Scatter (XY) or Bubble Chart: These chart types are used to compare pairs of values. To insert these two chart types, click * this button. Data for the chart can be selected either before or after the chart is created.

Stock, Surface, or Radar: This button is used to view a gallery of different types of stock, surface, or radar charts.

Combo Chart: This chart type is used to highlight different types of information. It can be used when range of data in the chart varies widely or when a wide range of mixed data is used.

Question 4.
Discuss about Chart elements in Excel
Answer:
There are several different elements to a chart. Each of the elements can be selected and formatted separately. The following paragraphs provides you with an illustration and explanation of each of these elements.

MS Excel and PowerPoint Long Answer Type Questions Part 3 4

Category (X) Axis: This is the horizontal or X-axis of the grid. It explains what each of the data series on the chart represents. The bars in a column chart represent a data series. It is possible to change the line, scale, font, number, and alignment attributes of this axis.

Chart Area: This is the entire area the chart covers. It is the background of the chart. When a chart is created, this area is usually white. The pattern used for the chart area can be changed, a border can be added, or a different font can be used.

Chan Title: This is the explanatory heading at the top of the chart. The title identifies the purpose of a chart.

Data Marker: This is a chart object, such as a circle, dot, or square that denotes a data point. In the chart in the illustration, the data points are the bars in the plot area of the chart.

Data Series: This element is a range of related data points in a chart, such as bars, columns, or pie slices. Each data series is represented with a certain color, identified in the legend. The outline and color used for each series, which axis the data are plotted on, the labels used, the series order, and the overlap and gap width between each series or point, can be changed.

Gridlines: These are the vertical and horizontal lines that appear in a chart. They are displayed in the plot area. A different line style, color, and weight can be used. It is also possible to select a different scale.

Handles: These are small circles that appear around the perimeter of a selected object, indicating that the object can be moved, resized, copied, or deleted.

Legend: This is the key to the chart, which shows by color plot what each series represents. The outline and color of the legend can be changed, as well as the font and place. For example, the legend can be moved to the top, bottom, comer, or left of the chart.

Plot Area: This is the area where the data series are plotted or graphed. It is the area that appears directly behind the data series; the rectangular shapes that are different colors. The pattern and color used can be changed, as well as the border of the plot area.

Value (Y) Axis: This is the vertical or Y-axis of the grid. This area represents the values that are being plotted in the worksheet. The values are determined by the cells selected in the worksheet when the chart is created. The pattern of the line, the format of the tick marks, the tick mark labels, the scale, the font, the number format, and the alignment of the axis labels can be changed.

Question 5.
Explain the steps to create a chart using chart wizard.
Answer:
A chart is also called a graph. It is a visual representation of selected data in a worksheet. A well-designed chart draws the reader’s attention to important data by illustrating trends and highlighting significant relationships between numbers.

  • For this exercise, you will use the High Tech Stock Club- Chart workbook shown below:

MS Excel and PowerPoint Long Answer Type Questions Part 3 5

    • This Column Chart will be used to compare the Initial Price and the Current Price.
    • With a Column Chart, either one data series or multiple data series may be selected.
    • In order to specify the Legend in a Column Chart, the column headings have to be selected.

Create Chart:

  • To create the Column Chart:
    • Select the range A2 through A7.
    • Hold down the Control key and select the following ranges:
      • E2 through E7.
      • G2 through G7.
    • When holding down the Ctrl key, it is possible to select a range of non-contiguous cells.

MS Excel and PowerPoint Long Answer Type Questions Part 3 6

  • Click the Insert Tab.
  • In the Charts Group, click the Column chart link.
  • A gallery of different styles of Column charts will appear.
  • Click the first option under 2-D Column. .
  • The Column Chart will appear in the window. The Chart Tools tabs will display.

MS Excel and PowerPoint Long Answer Type Questions Part 3 7

MS Excel and PowerPoint Long Answer Type Questions Part 3 8

Move Chart To Different Location Within Worksheet:

  • Move the mouse over the chart until the mouse pointer changes to a four-headed arrow.
  • Drag the chart to the desired location.

Resize Chart:

  • Select one of the resizing areas along the edge or comers of the chart.
  • Move the mouse button to one of these areas.
  • Click and drag the mouse pointer until the chart is resized appropriately.

Move the Chart to Different Sheet:

  • Click the Design Tab under the Chart Tools Contextual Tab.
  • Click the Move Chart button in the Location Group.
  • The Move Chart dialog box will display.
  • The choices in this dialog box are:
    • As new sheet – This option will insert a new sheet into the workbook. It will be titled Chart 1 unless otherwise specified.
    • As object in – Use this option to insert the chart directly into the worksheet that the chart is based on.

MS Excel and PowerPoint Long Answer Type Questions Part 3 9

  • Click the New Sheet option box.
  • Input a name for the Chart Sheet.
  • Click the OK button.
  • The chart will appear in its own window.
  • The tab will appear with the rest of the sheet tabs at the bottom of the window.

Question 6.
Discuss about Chart Editing and Formatting in Excel.
Answer:
Change the Chart Style:
When a Chart is created, it appears with a pre-formatted style. The colors in the Chart can be changed by using the Chart Styles feature.

  • Click the Chart Tools Design Tab.
  • In the Chart Styles Group, click the Change Colors button.

MS Excel and PowerPoint Long Answer Type Questions Part 3 10

  • Move the mouse pointer over each of the items to see a Live Preview of the colors.
  • Click one of the options to apply it to the Chart.
    or
  • Click one of the Styles in the Chart Styles gallery.
  • To see additional Styles click the More button.
  • Move the mouse pointer over each of the options to see a Live Preview of the Style.
  • Click one of the Styles to apply it to the Chart.

Format the Chart Title:

  • Click the Chart Title. A box with handles will display around the Chart Title.
  • Click the Home Tab.
  • In the Font Group, click the Font Size arrow.
  • Select a size of your choice to change the Font Size of the Title.
  • In the Font Group, click the Font Color button list arrow.
  • Choose a color from the Font Color palette.

Format the Chart Labels:

  • Click one of the data labels at the bottom of the chart.
  • A box will appear around the labels.
  • Click the Increase Font Size button in the Font Group, until the size changes to the desired size
  • Change the Font Color to the same color as the Title.
  • Click the Bold button in the Font Group to apply bold formatting to the labels.

Format the Chart Legend:

  • Click the Legend at the bottom of the chart.
  • Click the Increase Font Size button in the Font Group, until the size changes to 14 point.
  • Change the Font Color to the same color as the Title.
  • Click the Bold button in the Font Group to apply bold formatting to the Legend.
  • Click the Chart Tools Design Tab.
  • In the Chart Layouts Group, click the Add Chart Element button.
  • Move the mouse pointer over Legend.
  • Select a location for the Legend from the list.

Question 7.
Explain the procedure to Create Pie Chart in MS-Excel
Answer:
A pie chart is an easy way to show the relationship of items to the whole. In this exercise, you will be creating a Pie Chart that will show the relationship between the computer companies in the High Tech Stock Club. The difference between a Pie Chart and a Column Chart is that a Pie Chart can have only one data series while a Column Chart can have one or more.

MS Excel and PowerPoint Long Answer Type Questions Part 3 11

Create the Chart:

  • To create a Pie Chart, select the range A3 through A7, the names for the four computer companies. Notice for this chart that you don’t select the column heading.
  • Hold down the Control key and select the range H3 through H7, the data in the Current Value column.
  • Click the Insert Tab.
  • In the Charts Group, dick Pie.
  • A gallery of available 2-D and 3-D Pie Chart subtypes will display.
  • Click the 3-D Pie option.
  • The chart will appear in the worksheet

MS Excel and PowerPoint Long Answer Type Questions Part 3 12

  • Click the buttons along the right edge of the selected Chart to change different areas of the chart. The Chart Tools Contextual Tabs will appear.

Move the Chart:

  • Click the Design Tab under the Chart Tools Contextual Tab.
  • Click the Move Chart button in the Location Group.

MS Excel and PowerPoint Long Answer Type Questions Part 3 13

  • The Move Chart dialog box will display.
  • The choices in this dialog box are:
    • As new sheet – This option will insert a new sheet into the workbook. It will be titled Chart 1 unless otherwise specified.
    • As object in – Use this option to insert the chart directly into the worksheet that the chart is based on.
  • Click the New sheet option box.
  • Input a name for the Chart Sheet.
  • Click the OK button.
  • The chart will appear in its own window.

MS Excel and PowerPoint Long Answer Type Questions Part 3 14

Question 8.
Write about AutoFill option in MS-Excel
Answer:
Auto Fill is a process that allows the user to automatically number data in a column or row. It is an easy process to add sequential numbers to rows of data by filling a column with a series of numbers or by using the Row function

Fill Column with Series of Numbers:

  • Select the first cell in the range that is to be filled.
  • Type the starting value for the series, such as the number 1.
  • Type a value in the next cell to establish a pattern.
  • For example, if the series is to be 1, 2, 3. 4,…, type the number 1 in the first cell and then a number 2 in the second cell.
  • Select the two cells that contain the numbers.
  • The pointer wall turn to a black box with a little square in the right corner (see illustration).
  • Move the mouse pointer over the box by the little square until the mouse pointer turns to a black plus (+) sign (see illustration).

MS Excel and PowerPoint Long Answer Type Questions Part 3 15

  • Hold down the mouse button and drag down in the column until the desired cells are filled with numbers.
  • The cells should be numbered in consecutive order starting with number 1.
  • It is also possible to number the list non-consecutively. For instance, if you wanted to number the rows 2, 4, 6, 8…, type the number 2 in the first cell and the number 4 in the second cell.
  • Select the two cells and use the fill handle to copy the numbers down to the additional cells.

Fill Column or Rows with Month, Day, or other Series:
The process for filling columns or rows with the month or days is the same as for filling numbers. The only difference is that you input the day of the week or the month of the year in the column. It is also possible to fill a column or row with a series of consecutive or non-consecutive dates. An illustration of each of these fill patterns is shown on the next page.

Insert Days Of Week Into Column Or Row:

  • Input the first day, like Monday, into the first cell of the series.
  • Place the mouse pointer into the cell that contains the first day of the week.
  • Use the Fill Handle to copy the days down the column or across the row.
  • The days of the week will change as the copying is done.

Insert the Month of the Year:

  • Input the first month, like January, in the first cell of the series.
  • Place the mouse pointer into the cell that contains the first month of the year.
  • Use the Fill Handle to copy the months down in the column or across the row.
  • The months will change as the copying is being completed.

Insert Consecutive Dates:

  • Input the beginning date into the first cell of the column or row.
  • Place the mouse pointer in the cell that contains the date.
  • Use the Fill Handle to copy the date down the column or across the row.
  • A different date should appear in each of the cells in the series, starting with the first date and going in consecutive order.

MS Excel and PowerPoint Long Answer Type Questions Part 3 16

Insert Non-Consecutive Dates:

  • Input the beginning date into the first cell of the column or row.
  • In the second cell of the column or row, input the second date.
    • With this option it is possible to specify a period of time between dates, such as a month in between.
    • For instance, to specify the last day of each month, you would input January 31, 2020 in the first cell and. February 28, 2020 in the second cell.
  • Select the two dates and then use the Fill Handle to copy the information to the rest of the cells in the column or row.
  • The last day of the month should display in each consecutive cell.

NOTE:

  • It is also possible to number other items using the Auto Fill feature such as Versions or periods of a year.
  • For the version. Version 1 would be inserted into the first cell and then the Fill Handle would be used to insert Version 2, Version 3, and so forth. Each item would be numbered consecutively.
  • For the periods of a year, input something like 1st year then use the Fill Handle to insert the rest of the years. The years will be inserted in consecutive order.