Module IV

Spreadsheet

Q1) Write the type of data? 5 Marks

A1) Types of data

Spreadsheet data is information kept in a spreadsheet tool like Excel or Google Sheets. Data in a worksheet's cells can be used in calculations, graphed, and sorted and filtered to discover specific information.

Types of data

Spreadsheets are made up of rows and columns that form a grid of cells. Each cell usually contains only one piece of information. The three categories of data most typically utilized in spreadsheet programs are described below:

Label - Text data, often known as labels, is used to create worksheet headings and names for data columns. Letters, numerals, and special characters like! and & can all be found in text data. Text data in a cell is left-aligned by default.

Values - In calculations, number data, also known as values, is used. Numbers in a cell are right-aligned by default. Excel stores dates and timings as numbers in addition to actual numbers. Dates and timings are treated as a separate data category in other spreadsheet systems. When numbers are formatted as text data, issues arise. This makes it impossible to use them in calculations.

Formula - Formulas are mathematical equations that work with data from other cells on the spreadsheet to produce results. To add or subtract integers, simple formulas are utilized. Algebraic problems are solved using advanced formulas. Formulas implemented within Excel are known as spreadsheet functions.

Q2) Write about autocomplete, autocorrect? 8 Marks

A2) Autocomplete

This feature looks at the contents of the active column in the data above you and tries to guess what you're about to input. AutoComplete is a feature that automatically fills in your text when you start typing. It is enabled by default. This will cut down on the quantity of text you have to type by keeping you from typing the same words over and over.

Only specific cell entries, not individual words in a cell, are matched by AutoComplete. When entering or altering formulas, this does not work. Make sure your (Excel Options) are correct (Advanced tab, Editing Options, Enable AutoComplete for cell values). By default, it should be checked.

If you're entering data into a table with duplicate entries, this can be really helpful.

When you type text into a cell, Excel looks only in that column of the current region to determine whether the characters match a text string already in the column. If it discovers a text string that matches, it will automatically fill in the blanks.

Be mindful that if you're entering data that's similar but slightly different, this can lead to mistakes. Simply enter in the initial few letters or digits of an entry, and any matched entries will appear instantly.

If the first few letters you type match another cell in the same column, Excel will complete the text for you automatically.

Autocorrect

Excel Microsoft Office's AutoCorrect feature is a fantastic feature. This tool may repair common misspelled words, turn a brief phrase into a whole statement, or even display the full form of an abbreviation. This option will correct not only the spellings of the words, but also the capitalization of the first word after a full stop.

1. After you've opened Microsoft Excel, As indicated in the screenshot, go to the File Menu and select "Options."

2.     Go to the "Proofing" option in the Options menu.

3.     Click on Excel's "AutoCorrect choices" in Proofing.

4.     As illustrated below, the AutoCorrect settings window will display. The AutoCorrect tab is set as the default option in the AutoCorrect window.

By default, the autocorrect option in Excel is enabled. Uncheck the option for "Replace text as you type" if you wish to disable autocorrect. After unchecking this, the excel autocorrect option for spellings will no longer be shown.

Q3) What is autofill and autofit, also write about undo and redo? 8 Marks

A3) Autofill

When manually inputting data into a worksheet, the Auto Fill option comes in handy. This function is useful for a variety of things, but it's especially useful for entering data in a specific order, such as the numbers 2, 4, 6, 8, and so on, or nonnumeric data like the days of the week or months of the year. The instructions below show how to use Autofill to fill in the months of the year in Column A:

1. In the Sheet1 worksheet, click cell A3.
2. Press the ENTER key after typing the word January.
3. Activate cell A3 once more.
4. Place the mouse pointer in cell A3's lower right corner. In this area of the cell, you'll notice a little square called the Fill Handle. The white block plus sign will change into a black plus sign as the mouse pointer approaches the Fill Handle.

Fig 1: autofill

Autofit

1. Select the column or columns that you want to change.

2.     On the Home tab, in the Cells group, click Format.

3.     Click AutoFit Column Width under Cell Size.

Click the Select All button, then double-click any border between two column headings to swiftly autofit all columns in the spreadsheet.

Undo

Ctrl+Z can be used to undo an action.

If you want to use your mouse, go to the Quick Access Toolbar and click Undo. If you want to undo numerous steps, press Undo (or CTRL+Z) repeatedly.

Some activities, such as clicking commands on the File tab or saving a file, cannot be undone. The Undo command becomes Can't Undo if you can't undo an action.

To undo many activities at once, click the arrow next to the Undo , choose the actions you wish to undo from the list, and then click the list.

Redo

Press Ctrl+Y or F4 to redo something you've undone. (If pressing F4 doesn't seem to work, try pressing F-Lock or Fn, then F4).

Click Redo on the Quick Access toolbar if you want to use the mouse. (You can only use the Redo button after you've undone an action.)

Q4) How to move data in a worksheet? 5 Marks

A4) Moving data

You can move data in a worksheet to other areas once it has been input. The methods below show how to move data from one area on a worksheet to another:

1. By activating cell D2 and dragging down to cell D15, you can highlight the range D2:D15.
2. Bring the mouse pointer to cell D2's left edge. The white block + sign will transform into cross arrows. This means you can left-click the data and drag it to a new location.
3. Drag the mouse pointer to cell C2 with the left click.
4. Allow the left mouse button to be released. Column C now contains the data.
5. In the Quick Access Toolbar, click the Undo button. The data is returned to Column D as a result of this action.

Fig 2: moving data

Q5) How to insert columns and rows? 5 Marks

A5) Inserting columns and rows

Using pre-made Excel workbooks is a time-saving method of working because it avoids the need to construct data worksheets from scratch. However, you may discover that you need to add more columns or rows of data to achieve your objectives. In this situation, you can create a worksheet with blank columns or rows. The steps below will show you how to do it:

1. By hovering the mouse pointer over cell C1 in the Sheet1 worksheet and clicking the left mouse button, you can access it.
2. In the Home tab of the Ribbon, click the down arrow on the Insert button.

Fig 3: insert button

3.     From the drop-down menu, select Insert Sheet Columns. To the left of Column C, a blank column will be added. Column D now contains the contents that were previously in Column C. It's worth noting that columns are always placed to the left of an activated cell.

Fig 4: Insert Drop-Down Menu

4.     By hovering the mouse pointer over cell A3 in the Sheet1 worksheet and clicking the left mouse button, you can access it.

5.     In the Home tab of the Ribbon, click the down arrow on the Insert button.

6.     From the drop-down menu, select the Insert Sheet Rows option. Above Row 3, a blank row will be inserted. Row 4 now contains the contents that were previously in Row 3. It's important to note that rows are always placed above the activated cell.

Q6) Find and replace values. Spell check.  8 Marks

A6) Find and replace value

You may use the Find and Replace tool in Excel to rapidly locate and replace specific text. To swiftly select all cells with formulae, comments, conditional formatting, constants, data validation, and so on, use Excel's Go To Special function.

Find

Follow the instructions below to easily locate specific text.

1. Select Find & Select from the Editing group on the Home tab.

2. Select the Find option.

The dialog window for 'Find and Replace' appears.

3. Start typing the text you're looking for. Type Ferrari, for example.

4. Select 'Find Next' from the drop-down menu.

The first occurrence is chosen by Excel.

5. Select the second occurrence by clicking 'Find Next.'

6. Click 'Find All' to get a list of all the occurrences.

Replace

Follow the instructions below to rapidly locate specific content and replace it with new text.

1. Select Find & Select from the Editing group on the Home tab.

2. Select Replace from the menu.

The dialog box 'Find and Replace' displays (with the Replace tab selected).

3. Replace the text Veneno with the text you want to find (Diablo).

4. Select 'Find Next' from the drop-down menu.

The first occurrence is chosen by Excel. There has yet to be a replacement.

5. To make a single replacement, click 'Replace.'

To replace all occurrences, use 'Replace All.'

Spell check

When you choose the data and then click the review tab, the spelling command will appear in the left corner of the ribbon. Press F7 or left click on it. If the data contains any spelling errors, the spell check will recommend fixes in the spelling dialogue box. Look at this illustration:

Fig 5: spell check

Q7) illustrate Adding, removing, hiding and renaming worksheets? 5 Marks

A7) A worksheet is a set of cells in which you can store and manipulate data. Multiple worksheets can be included in an Excel workbook.

Select a Worksheet

When you open an Excel workbook, the first sheet is automatically selected. The worksheet's name is displayed on the sheet tab at the bottom of the document window.

Insert a worksheet

You are free to add as many worksheets as you like. Click the + sign at the bottom of the document window to rapidly insert a new worksheet.

Result :

Rename a worksheet

Execute the procedures below to give a worksheet a more descriptive name.

1. Right-click on Sheet1's sheet tab.

2. Select Rename.

3. Type, for example, Sales 2016.

Move a worksheet

To reposition a worksheet, click on the sheet tab of the worksheet you want to reposition and drag it into place.

1. For example, choose Sheet2's sheet tab and drag it ahead of Sales 2016.

Result

Q8) Add headers/Footers to a Workbook? 5 Marks

A8) It's simple to add a heading to an Excel worksheet. Here's how you go about it:

1. Select the Header & Footer button from the Insert tab > Text group. This will change the worksheet's display to Page Layout.

2.     In any of the three Header boxes at the top of the page, you may now input text, insert a photo, add a preset header, or specific items. The central box is selected by default:

Click the left or right box and add some information there if you want the header to appear in the top left or top right corner of the page.

3.     To exit the header section, click anywhere in the worksheet when you've completed. Press Esc to exit the header box without saving your changes.

The header will be duplicated on each page of your worksheet when printed.

Footer

A footer, like an Excel header, may be added in a few simple steps:

1. Click the Header & Footer button in the Text group on the Insert tab.

2.     Click Go to Footer on the Design tab, or scroll down to the footer boxes at the bottom of the page.

3.     Click the left, middle, or right footer box, and input some text or insert the element you want, depending on the intended location. Please follow these instructions to add a preset footer, and these guidelines to create a custom Excel footer.

4.     To escape the footer area, click anywhere in the spreadsheet.

Select one of the footer boxes and click Page Number on the Design tab, in the Header & Footer group, to insert page numbers at the bottom of the worksheet.

Q9) Explain Page breaks, preview? 8 Marks

A9) Page breaks

Dividers called page breaks divide a worksheet into distinct pages for printing. Based on the paper size, margin settings, scale options, and the placements of any manual page breaks that you insert, Microsoft Excel inserts automatic page breaks. Before printing a worksheet, you can edit the page breaks in the worksheet to get the precise number of pages you desire.

The dashed lines in the Page Break Preview are page breaks that Excel added automatically. Manually added page breaks are indicated by solid lines.

Inserting a page breaks

1. Select the worksheet you'd want to print.

2.     Click Page Break Preview in the Workbook Views group on the View tab.

On the status bar, you can also click the Page Break Preview Button graphic.

3.     Choose one of the following options:

●      Select the row below where you wish to insert the page break to insert a vertical page break.

●      Select the column to the right of where you want to enter the page break to insert a horizontal page break.

4.     Click Breaks in the Page Setup group on the Page Layout tab.

5.     Insert a page break by clicking Insert Page Break.

You can alternatively click Create Page Break after right-clicking the row or column below or to the right of where you want to insert the page break.

Preview

You can see a preview of how the data will appear on the printed if you pick one or more sheets and then click File > Print.

1. Choose the worksheet(s) you'd like to see a preview of.

2.     To access the Preview window and printing options, go to File, then Print.

Keyboard shortcut You can also press Ctrl+F2. 

Information

Click the arrows for Next Page and Previous Page at the bottom of the Print See window—or type the page number—to preview the next and previous pages.

Click any of the arrows in the upper-left corner of the Print Preview window to exit Print Preview and return to your workbook.

Click the Show Margins button in the lower right corner of the Print Preview window to see page margins.

Q10) how to create formulas? 8 Marks

A10) In Excel, formulas are used to compute a range of mathematical outputs and can be utilized to create nearly any unique calculation needed to achieve your goal. Furthermore, when creating a formula in Excel, you employ cell locations that become cell references when applied to a formula.

This means that when Excel calculates a mathematical output, it refers to the number input into the cell position. As a result, every time the numbers in the cell references change, Excel generates a new output.

We'll start with the Budget Detail worksheet in the Personal Budget workbook, which is illustrated in Figure, to show how to build a basic formula. We'll add a few formulas and functions to finish this worksheet.

Fig 6: budget detail worksheet

Each of the spend categories indicated in the range A3:A11 is defined in the table. These categories are determined by how you spend your money while creating a personal budget. Every person is likely to have distinct categories or to define the same categories differently. As a result, before going, it is critical to review the definitions in Table to understand how we are defining these categories.

Spend Category Definitions

The Monthly Spend numbers will be calculated using the first formula we add to the Budget Detail worksheet. The formula will be written in such a way that it divides the values in the Annual Spend column by 12. This will display how much money will be spent on each of the categories in Column A per month. The following steps will show you how to make this formula:

1. Open the CH2 Data data file and save it with the new name CH2 Personal Budget using the File/Save As command.
2. To access the worksheet, click the Budget Detail worksheet tab.
3. Cell C3 should be selected.
4. Put an equal sign (=) in the box. Excel performs a computation or generates a logical output when the first character typed into a cell location is an equal sign.
5. D3 is the third type. This changes the formula to include D3, which is now a cell reference. Excel will generate an output based on the value entered in cell D3.
6. Type / as a slash symbol. In Excel, this is the symbol for division. The mathematical operations in Excel differ slightly from those on a standard calculator, as indicated in Table 2.2.
7. 12 is the number to type. The value in cell D3 is divided by 12 as a result of this. Because the value of the number, or constant, will not change, it is used instead of a cell reference in this calculation.
8. Press the ENTER key.

Q11) Describe absolute and relative? 5 Marks

A11) Absolute

Absolute reference aids in the multiplication of several values by a single value.

Take a look at the image below. The items in list 1 are multiplied by 4, which equals the value in cell C3. In this scenario, the sign $ will be used in the formula.

So, after entering the formula, hit the F4 key to edit the formula. In the formula, there will be a $ sign. In the image, cell D3 is highlighted. Then, to retrieve the result, press the Enter key. The fill handle should now be dragged from cell D3 to cell D10. List 1's values will be multiplied by four.

Similarly, the additional marks are added to the students' grades in the graphic below by changing the calculation from =C5+E2 to =C5+$E$2.

Relative

You don't have to type the formula for each calculation in a column or row if you use relative referencing. Look at this illustration:

To get the result, type the formula in the first cell D3 and hit Enter, then use the fill handle. The formula in the respective fields will update automatically, and all values from list 1 will be multiplied by the values from list 2.

This method, you can simply multiply values from list 1 with their corresponding values from list 2, but it won't work if you wish to multiply all cells from list 1 with a single cell from list 2, such as C3.

Q12) Describe Financial functions: FV, PV, PMT? 8 Marks

A12) FV (future value)

Use the following formula to calculate the future value of a certain investment with a constant interest rate and monthly payments:

FV (Rate, Nper, [Pmt], PV, [Type])

●      Rate = It is the interest rate/period

●      Nper = Number of periods

●      [Pmt] = Payment/period

●      PV = Present Value

●      [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)

Example

In 2016, A made a $100 investment. The payment was made on a yearly basis. The annual interest rate is 10%. What will the FV be in 2019?

We'll write the equation in Excel as follows:

= FV (10%, 3, 1, – 100)

= US $129.79

PV(present value)

It's easier to figure out PV if you know how to calculate FV. Here's how it works:

PV = (Rate, Nper, [Pmt], FV, [Type])

●      Rate = It is the interest rate/period

●      Nper = Number of periods

●      [Pmt] = Payment/period

●      FV = Future Value

●      [Type] = When the payment is made (if nothing else is specified, the payment is presumed to be made at the end of the period)

Example

In 2019, the value of a $100 investment in the United States. The payment was made on a yearly basis. The annual interest rate is 10%. What would be the current PV?

We'll write the equation in Excel as follows:

= PV (10%, 3, 1, – 100)

= US $72.64

PMT

In Excel, PMT stands for periodic payments required to pay off a debt over a set length of time at a fixed interest rate. Let's have a look at how to do it in Excel.

PMT = (Rate, Nper, PV, [FV], [Type])

●      Rate = It is the interest rate/period

●      Nper = Number of periods

●      PV = Present Value

●      [FV] = An optional argument that concerns the loan's future value (if nothing is mentioned, FV is considered as “0”)

●      [Type] = When the payment is made (if nothing else is specified, the payment is presumed to be made at the end of the period)

Example

The $1000 must be paid in full within three years. The interest rate is 10% p.a., and the payment must be made once a year. Find out what the PMT is.

In Excel, we'll compute it as follows:

= PMT (10%, 3, 1000)

= – 402.11

Q13) Explain PPMT, IPMT, NPER, RATE? 12 Marks

A13) PPMT

It's a different type of PMT. The sole distinction is that PPMT uses a constant interest rate and periodic payments to calculate payments on principle. Here's how to figure out your PPMT.

PPMT = (Rate, Per, Nper, PV, [FV], [Type])

●      Rate = It is the interest rate/period

●      Per = The period for which the principal is to be calculated

●      Nper = Number of periods

●      PV = Present Value

●      [FV] = An optional input that refers to a loan's future value (if none is given, FV is assumed to be "0")

●      [Type] = When the payment is made (if nothing else is specified, the payment is presumed to be made at the end of the period)

Example

The $1000 must be paid in full within three years. The interest rate is 10% p.a., and the payment must be made once a year. In the first and second years, find out what the PPMT is.

In Excel, we'll compute it as follows:

1st year

=PPMT (10%, 1, 3, 1000)

= US $-302.11

2nd year

=PPMT (10%, 2, 3, 1000)

= US $-332.33

IPMT

Consider a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000.

The interest portion of the payment is calculated using Excel's IPMT function. The payment number is specified by the second argument.

The interest portion of the 5th payment is calculated using the IPMT function.

NPER

It simply refers to the number of repayment periods required to repay the loan.

NPER = (Rate, PMT, PV, [FV], [Type])

●      Rate = It is the interest rate/period

●      PMT = Amount paid per period

●      PV = Present Value

●      [FV] = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)

●      [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period.

Example

For a loan of $1000, a yearly payment of $200 is made. The interest rate is 10% p.a., and the payment must be made once a year. Find out what the NPER is.

NPER must be calculated in the following way:

= NPER (10%, -200, 1000)

= 7.27 years

RATE

We can determine the interest rate required to pay off the debt in full for a particular period of time using the RATE function in Excel. Let's have a look at how to use the RATE finance function in Excel to calculate rates.

RATE = (NPER, PMT, PV, [FV], [Type], [Guess])

●      Nper = Number of periods

●      PMT = Amount paid per period

●      PV = Present Value

●      [FV] = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)

●      [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)

●      [Guess] = An assumption of what you think RATE should be

Example

The payment for a loan of US $1000 over 6 years is $200 per year, and the payment must be made on a yearly basis. Find out what the RATE is.

= RATE (6, -200, 1000, 0.1)

= 5%

Q14) Write about ROUND, ROUNDDOWN, ROUNDUP, CEILING, FLOOR, INT, MAX and MIN? 12 Marks

A14) ROUND

To round an integer to a specific number of digits, use the round function.

Syntax

ROUND(number, number_of_digits)

ROUNDDOWN

The Rounddown function in Excel reduces a given integer to a defined number of decimal places before rounding it down to zero.

The function's syntax is as follows:

ROUNDDOWN( number, num_digits )

Where the arguments are as follows:

Number - The number that has to be rounded.

Num_digits - To how many decimal places should the input number be rounded.

Note that

●      A positive num digits value indicates the number of digits to the right of the decimal point;

●      a num digits value of 0 indicates rounding to the nearest integer; and a num digits value of -1 indicates rounding to the nearest integer.

●      The number of digits to the left of the decimal point is specified by a negative num digits variable.

Example

The Excel Rounddown function is demonstrated in numerous ways in Column B of the following spreadsheet:

The Rounddown function always rounds down to zero, as shown in the examples above. To put it another way, a positive number gets less positive, whereas a negative number becomes less negative.

ROUNDUP

The Roundup function in Excel takes a number and rounds it up to a specified number of decimal places, away from zero.

The function's syntax is as follows:

ROUNDUP( number, num_digits )

The following are the arguments:

Number - This is the number that should be rounded.

Num_digits - To how many decimal places should the input number be rounded?

Note that

●      A positive num digits value indicates the number of digits to the right of the decimal point;

●      a num digits value of 0 indicates rounding to the nearest integer; and a num digits value of -1 indicates rounding to the nearest integer.

●      The number of digits to the left of the decimal point is specified by a negative num digits variable.

Example

The Excel Roundup function is demonstrated in numerous ways in Column B of the following spreadsheet:

The Roundup function always rounds up, away from zero, as shown in the samples above. A positive number, for example, grows more positive, while a negative number becomes more negative.

CEILING

The Excel Ceiling function rounds a number supplied to the closest multiple of a given number away from zero.

The function's syntax is as follows:

CEILING( number, significance )

The following are the arguments:

Number - This is the number that should be rounded.

Significance - The significance multiple to which the input number should be rounded.

(The arithmetic sign (positive or negative) should be the same as the given number argument.)

Example

The Excel Ceiling function is used in column B of the following spreadsheet to round the positive and negative numbers 22.25 and -22.25 to different significance values.

When the number and significance parameters have the same arithmetic sign, the Ceiling function rounds away from zero, as seen in the instances above.

FLOOR

The FLOOR function in Excel rounds a number to the nearest multiple of a defined significance to the nearest multiple of zero.

The function's syntax is as follows:

FLOOR( number, significance )

The following are the arguments:

Number - This is the number that should be rounded.

Significance - The significance multiple to which the input number should be rounded.

(The arithmetic sign (positive or negative) should be the same as the given number argument.)

Example

The Excel Floor function is used in column B of the following spreadsheet to round the positive and negative values 26.75 and -26.75 to different multiples of significance.

When the number and significance inputs have the same arithmetic sign, the Floor function rounds the given number towards zero, as seen in the examples above.

INT

This function transforms a decimal value to an integer that is less than.

Syntax

INT(decimal number)

MAX and MIN

The MAX and MIN functions are the last two statistical functions we'll add to the Budget Detail worksheet. The highest and lowest values in a set of cells are identified using these functions. The methods below will show you how to add these features to the Budget Detail worksheet:

1. In the Budget Detail spreadsheet, click cell D15.
2. Put an equal sign (=) in the box.
3. Fill in the blanks with the word MIN.
4. Add an open parenthesis (.) to the end of your sentence.
5. The range D3:D11 should be highlighted.
6. If you type a closing parenthesis and press the ENTER key, Excel will close the function for you. The lowest figure in the Annual Spend column is $1,200, which is the result of the MIN function.
7. D16 is the cell to click.
8. Put an equal sign (=) in the box.
9. Fill in the blanks with the word MAX.
10. Add an open parenthesis (.) to the end of your sentence.
11. The range D3:D11 should be highlighted.
12. If you type a closing parenthesis and press the ENTER key, Excel will close the function for you. The MAX function returns $3,500 as a result. In the Annual Spend column, this is the highest figure.

Fig 7: MIN Function Added to the Budget Detail Worksheet

Fig 8: MAX Function Added to the Budget Detail Worksheet

Q15) Explain MOD, SQRT, ABS,SUM, COUNT, AVERAGE? 12 Marks

A15) MOD

This function is used to calculate the remainder after dividing two numbers.

Syntax

MOD(number,divisor)

SQRT

The Sqrt function in Excel determines the positive square root of a given value.

The function's syntax is as follows:

SQRT( number )

Where the number input is the numeric value whose square root you want to find.

The Sqrt function returns the #NUM! error if the provided number is negative.

Example

Three simple examples of the Excel Sqrt function are shown in the spreadsheet below.

ABS

The abs() method is used to return a number's absolute value. It's possible that the number is positive or negative. The following is an example.

SUM

When you need to calculate totals for a range of cells or a set of chosen cells on a worksheet, use the SUM function. The totals in row 12 of the Budget Detail worksheet will be calculated using the SUM function. It's worth noting that there are a few different ways to add a function to a worksheet. The following example shows how to add a function to a worksheet by typing it into a cell:

1. To access the worksheet, click the Budget Detail worksheet tab.
2. Cell C12 should be selected.
3. Put an equal sign (=) in the box.
4. SUM is the name of the function.
5. Add an open parenthesis (.) to the end of your sentence.
6. Drag down from cell C3 to cell C11. This inserts the C3:C11 range into the function.
7. Add a closing parenthesis at the end of the sentence.
8. Enter by pressing the ENTER key. The sum for the Monthly Spend column is $1,496 as calculated by the function.

Fig 9: Adding the SUM Function to the Budget Detail Worksheet

Fig 10: Results of the SUM Function in the Budget Detail Worksheet

COUNT

When we need to count the number of cells that contain a number, we utilize the count function. Remember, it's all about the numbers! Let's take a look at the function:

COUNT(value1, [value2], …)

So, let's see if we can figure out the answer to our first question: how many things were on sale?

There are 11 discounted items.

AVERAGE

The average is the most prevalent function we employ in our daily lives (or mean). The arithmetic mean of all the cells in a range is returned by the AVERAGE function:

AVERAGE(number1, [number2], …)

However, there is one major disadvantage to employing averages: they are prone to outliers. As a result, in our study, they can portray a very unrealistic picture. Let's figure out how many items were sold on average: dispersed

The average is ~ 365.2 percentile points. We'll be making similar cost estimations as well.

Q16) Write about Sorting, Subtotal? 8 Marks

A16) Data sorting is an important aspect of data analysis. You might want to alphabetize a list of names, make a list of product inventory levels from highest to lowest, or sort rows by colors or icons. Sorting data allows you to better view and understand your data, organize and find the data you need, and make more informed decisions.

In one or more columns, you can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), dates and times (oldest to newest and newest to oldest), and dates and times (oldest to newest and newest to oldest). You can also sort by format, such as cell color, font color, or icon set, or by a custom list you build (such as Large, Medium, and Small).

Sort text

●      Choose a cell in the column that you'd want to sort.

●      Do one of the following in the Sort & Filter group on the Data tab:

○      To quickly sort in ascending order in Excel, use (A to Z command).

○      To quickly sort in descending order in Excel, use (Z to A command).

Issues

Check that all data is stored as text If the column you want to sort contains both numbers and text, you'll need to format them all as numbers or text. If you don't use this format, the numbers stored as numbers will be sorted before the text numbers. To format all of the chosen data as text, use Ctrl+1 to open the Format Cells window, then click the Number tab, then General, Number, or Text under Category.

Remove any leading space Leading spaces may be introduced before data when data is imported from another application. Before sorting the data, remove the leading spaces. You can either do it manually or use the TRIM feature.

Subtotal

The SUBTOTAL function in Excel returns an aggregate result for the values passed in. The SUBTOTAL function can return an SUM, AVERAGE, COUNT, MAX, and other values, and it can include or exclude values from hidden rows.

Purpose - In a list or database, find a subtotal.

Return value - A number that represents a certain type of subtotal.

Syntax

=SUBTOTAL (function_num, ref1, [ref2], ...)

Function_num - A number that indicates which function should be used to calculate subtotals in a list. The complete list can be found in the table below.

Ref1 - A named range or a subtotal reference.

Ref2 - [optional] A named range or a subtotal reference.

To retrieve a subtotal in a list or database, use the SUBTOTAL function. SUBTOTAL, despite its name, can execute a wide range of math functions, including AVERAGE, COUNT, MAX, and many others (see table below for the complete list). SUBTOTAL excludes values in rows concealed by a filter by default, which makes it particularly helpful in Excel Tables.

To avoid double-counting, the SUBTOTAL function automatically ignores other SUBTOTAL formulas in references.

Q17) Pivot Tables- Building Pivot Tables, Pivot Table regions? 12 Marks

A17) Pivot table

1. Choose the cells from which you wish to make a PivotTable.
2. Select Insert > PivotTable.

3.     Select the data you want to study under Choose the data you want to analyze. Choose a table or a range of tables.

4.     Verify the cell range in Table/Range.

5.     Select New worksheet to place the PivotTable report in a new worksheet or Existing worksheet to place the PivotTable in an existing worksheet, and then select the location you want the PivotTable to appear under Choose where you want the PivotTable report to appear.

6.     Choose OK.

Building out your PivotTable

1. Select the field name checkbox in the PivotTables Fields window to add a field to your PivotTable.

(Non-numeric fields are moved to Rows, date and time hierarchies are moved to Columns, and numeric fields are moved to Values.)

2.     Drag a field to the target region to shift it from one location to another.

Pivot Table regions

You can use the Recommended PivotTables command in Excel if you are unfamiliar with Excel PivotTables or if you are unsure which fields would result in a relevant report. Recommended PivotTables provides you with all of the available reports for your data, as well as the layouts. In other words, the PivotTables that are customized to your data will be displayed as alternatives.

Follow these steps to generate a PivotTable from the Excel table SalesData-Table using Recommended PivotTables:

●      Select the table SalesData-Table from the drop-down menu.

●      Select the INSERT tab from the drop-down menu.

●      In the Tables group, select Recommended PivotTables. The Dialog Box for Recommended PivotTables displays.

The possible customized PivotTables that suit your data will be provided in the Recommended PivotTables dialog box.

●      To see the preview on the right side, click on each of the PivotTable options.

●      Click OK after selecting the PivotTable - Sum of Order Amount by Salesperson and Month.

The preview will appear on the right side.

In your workbook, the selected PivotTable appears on a new worksheet.

The PivotTable Fields Salesperson, Region, Order Amount, and Month have all been selected. Region and Salesperson are in the ROWS section, Month is in the COLUMNS section, and the Sum of Order Amount is in the VALUES section.

The data was summarized by region, salesperson, and month in the PivotTable. Each Region, each Salesperson, and each Month's subtotals are shown.

Q18) What do you mean by Rearranging Pivot Table? 5 Marks

A18) Rearranging Pivot Table

Using one of the four regions at the bottom of the layout section, you can rearrange or move existing fields:

To rearrange fields, pick one of the following commands after clicking the field name in one of the areas:

You can also hold down the mouse button on a field name and move it between the field and layout parts, as well as between the different areas.