Unit – IX

Microsoft Excel (Spreadsheet)

Q.1) What is Microsoft excel?

A1: Microsoft Excel is an electronic spreadsheet application that enables users to store, organize, calculate and manipulate the data with formulas using a spreadsheet system broken up by rows and columns. It also provides the flexibility to use an external database to do analysis, make reports, etc. thus saving lots of time.

Q.2) Write the concept of a spreadsheet?

A2:  In Excel, a file is referred to as a "Workbook." A workbook is made up of several “Worksheets” (Fig). Users have access to three sheets by default: Sheet 1, Sheet 2, and Sheet 3. Only one worksheet can be designated as a "Active Worksheet" at a time, and that worksheet is available to the user for actions. The name of an active worksheet appears in bold characters in the "Sheet Tab" at the bottom left of the screen. If necessary, additional sheets can be added by clicking the icon (which operates as Insert! Worksheet).

If necessary, the sheet names can be changed by right-clicking the mouse above Sheet1 or Sheet 2 or Sheet 3 after selecting and directing it on the sheet name to be changed and selecting “Rename.”

Columns are referred to by alpha characters from left to right, while rows are numbered numerically from top to bottom. There are 65536 rows in Excel 2007 that are numbered 1, 2, 3,... 65,536. These figures can be seen on the worksheet's left-hand side. Columns (a total of 256 in Excel) are labeled with letters such as A, B, C,... AA... IV and are displayed on the horizontal box right above Row 1. As a result, there are roughly 65,536 x 256 = 1,65,00,000 cells, which is a large work surface, sufficient for all application requirements (Fig) on a single sheet.

A cell in a spreadsheet stores a value, function, or arithmetic expression. A cell is the point where a row and a column meet. A cell is designated by a letter and a number combination that corresponds to a specific location inside the spreadsheet. For example, as seen in Figure at row 1 and column 1, the first cell in a worksheet is designated as A1 (A).

Q.3) How to create, save and edit a workbook?

A3: Workbooks are Excel documents. We'll need to generate a new workbook every time we start a new project in Excel. In Excel 2016, there are several ways to begin working with a workbook. We can either start from scratch or use a predesigned template to create a new worksheet, or we can open an existing one.

Create a new blank workbook

Follow these procedures to create a fresh blank workbook in MS Excel:

1. Go to the File tab, where you'll see the Excel Backstage view.

2. Choose New, then Blank Workbook from the drop-down menu.

3. This will result in the creation of a new blank workbook.

Saving and Editing a workbook

We'll need to know how to save a new worksheet in Excel so that we can access and edit it later. We can save files locally on our computer, just like in previous versions of Excel. Unlike previous versions, however, Excel 2016 allows us to save a worksheet to the cloud using OneDrive.

Additionally, Excel allows us to immediately export and share workbooks with others.

Save and Save as

Save and Save As are the two options for saving a file in Excel. These choices operate in a similar manner, with a few key distinctions:

Save - We'll use the Save command to save our changes when we create or modify a worksheet. The majority of the time, we'll utilize this command.

Only when saving a file for the first time do we need to choose a file name and location. After that, we can simply save it with a same name and location by clicking the Save command. You can also use the Ctrl+S keyboard shortcut.

Save as - This command will be used to make a copy of a workbook while maintaining the original. When we use Save As, we must provide the cloned version a different name and/or location.

Steps to save a workbook

Every time we start a new project or make changes to an existing one, we must save our workbook. Early and frequent saving can help us avoid losing our work.

1. On the Quick Access Toolbar, locate and pick the Save command.

2. The Save As pane will appear in the Backstage view if we're saving the file for the first time. Choose a new file name and a new location to save your file.

3. Click the Browse button here to save the workbook to our computer. Alternatively, we can save the file to your OneDrive by clicking OneDrive.

4. You'll see the Save As dialog box appear. Select the place in local storage where the workbook should be saved. Click Save after you've given the workbook a name.

5. You'll save the worksheet. As we make changes to the worksheet, we may use the Save command (Ctrl+S) to save our modifications.

Q.4) How to insert a cell?

A4: 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.

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.

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.

Q.5) How to delete a worksheet?

A5: Select Delete from the worksheet menu after right-clicking the worksheet you want to remove.

1. The targeted worksheet has been removed from our workbook, as you can see. There is only one worksheet left.

You can also go to the Cells Group's Home Tab, click Delete, and then pick Delete Sheet.

Q.6) How to enter data in cell?

A6: Five ways to enter data

There are several methods for entering data into an Excel worksheet. We often type directly into cells, but there are other ways to enter data that can help you speed up your data entry work.

1. Type directly into cell - Add your data by typing right into a cell. When a cell is highlighted with a darker border, you know it's active.

2.     Use the formula bar - This can be found beneath the ribbon. Enter your information directly into the formula bar. By typing the cell number directly into the Name field (found above the Column headings A – Z), you can navigate throughout the worksheet.

3.     Take advantage of Autocomplete. Excel will try to assist you in speeding up your data entry by guessing what you're typing based on the contents of your worksheet.  If the autocorrect option is right for you, just press enter.

4.     Copy and paste - You may have cells where you may copy and paste data within the same worksheet – saving you time when creating a sheet – or you can copy data to another worksheet within the workbook.

5.     Allow Autofill to handle the heavy lifting. Whether it's text or numbers, autofill choices can finish a series of data. When creating worksheets or entering data, this saves a lot of time.

Q.7) How to copy and move data?

A7: You can copy chosen information from the spreadsheet to the Clipboard, which is a temporary storage file in your computer's memory. You can paste any of the objects on the Clipboard into a cell in the same or a different spreadsheet using the Paste feature.

1. Duplicate one or more cells.
2. On the Standard toolbar, click the Copy icon.
3. The replicated cell's border looks like marching ants.

4.     Select the cell where you wish the duplicated data to go. A highlight will be applied to the cell. Click the first cell where you want the copied information to go if you're copying items into multiple cells.

5.     Enter the code with the Enter key. Your data gets transferred to the new location.

If you paste copied cell information into cells that already have data, be careful. Existing data will be overwritten if you do so.

You may paste data into an Excel spreadsheet from a variety of sources, including websites, emails, and other Office products like Word and PowerPoint

Moving data using drag and drop

The drag-and-drop approach is another option for moving data from one cell to another. The cursor is used to point to the information that has to be relocated, and then the cell is dragged to its new location.

●      The cell(s) you want to relocate to a new position should be highlighted and selected.

●      Place the mouse pointer towards one of the selected cell's outside edges (s). The mouse pointer transforms from a large white cross to a thin black cross with arrows on both sides.

●      Keep the mouse pointer on the outer edge of the selected cell and drag the cell(s) to a new location by clicking and holding the left mouse button.

●      To relocate the information to its new location, release the mouse button.

Q.8) Describe mathematical and statistical functions?

A8: 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.

ROUND

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

Syntax

ROUND(number, number_of_digits)

MOD

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

Syntax

MOD(number, divisor)

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.

INT

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

Syntax

INT(decimal number)

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.

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.

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

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.

Q.9) Write short notes on logical functions?

A9: To work with logical values, Microsoft Excel includes four logical functions. AND, OR, XOR, and NOT are the functions. When you wish to do many comparisons in your calculation or test multiple conditions instead of just one, you use these functions. When their parameters are evaluated, Excel logical functions return TRUE or FALSE, just like logical operators.

To assist you find the correct formula for a certain assignment, the table below provides a brief overview of what each logical function accomplishes.

Q.10) What are text functions?

A10: Within an Excel spreadsheet, the TEXT Function is used to transform integers to text. The function essentially converts a numeric number to a text string. TEXT is available in all Excel versions.

The syntax of this function translates a numeric value to text in a specified number format:

Text (Value, format text)

Where,

Value - A numeric value, a formula that evaluates to a numeric value, or a reference to a cell that has a numeric value are all examples of value.

Format text - is a number format that takes the form of a text string surrounded by quotation marks. By selecting Number, Date, Time, Currency, or Custom from the Category box of the Number tab in the Format Cells dialog box, and then viewing the formats provided, we may see a variety of numeric formats.

When is the Excel TEXT Function required?

The TEXT function is used in the following situations:

●      When we need dates to be displayed in a specific format.

●      When we want numbers to be displayed in a specific format or in a more readable way.

●      When we want to merge text or characters with numbers.

Example

I need to convert the following data to the “d mmmm, yyyy” format. When we use the text function, the following is the result:

Q.11) Explain financial functions?

A11: 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

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%

Q.12) What are date and time functions?

A12: DATE ()

The DATE function returns a sequential serial number that corresponds to a specific date.

The result is presented as a date instead of a number if the cell format was General before the function was entered. Adjust the cell format to see the serial number or to change the date formatting.

Syntax

DATE (year, months, day)

Example - The example below shows the serial data or the date derived by using year as 2004 and month as 12 and day as 22.

TIME () function

This function is used to get the decimal value for a specific period. If the cell format was General before the function, the result is formatted as a date.

TIME returns a decimal number ranging from 0 (zero) to 0.99988426, which represents times from 0:00:00 (12:00:00 AM) to 23:59:59. (11:59:59 P.M.).

Syntax

TIME (hour, minute, second)

Example - The example below returns the decimal part of a day, for the time specified in row 4 (14 hours, 10, minutes, 15 seconds)

TODAY ()

TODAY () in the blank worksheet, this is the function for today's date.

TODAY — The current date's serial number is returned. The serial number is a date-time code that Excel uses to calculate dates and times. Fractions of a day are used to denote times. Serial number 1 is assigned by default on January 1, 1900. As a result, the serial number for January 1, 2009 is 39814. (Because it is 39814 days after January 1, 1900).

Q.13) Describe function wizard?

A13: Through submenus and categories, the function wizard opens all of Excel's functions. To use the Function Wizard, go to the Insert menu and select Function, or go to the Standard toolbar and select the Function Wizard button "fx." Click on each Function category with your mouse to explore the Functions. The "Average" Function is used as an example in the following example.

The Function Wizard can assist you in locating a function and providing step-by-step instructions on how to utilize it. Click the Insert Function button on the Formula Bar or the Insert Function button on the Formulas tab to open the Function Wizard.

Type a description in the Search for a Function section and then click Go to find the function you're looking for. In the Select a Function Field, Excel will show you all of the functions that match your criteria. Then click OK after selecting the desired function. Excel will provide a dialog box tailored to the function you selected.

Alternatively, specify the category your function belongs to using the Or Select a Category Field. In the Select a Function Field, Excel will show you all of the functions in that category. Then click OK after selecting the desired function. Excel will provide a dialog box tailored to the function you selected.

There is a separate dialog box for each function. The Function Specific dialog box is how I refer to it. Each of the function's arguments is listed in the Function Specific dialog box. Type the appropriate value in each field, or select a range by clicking in the field and dragging. Click OK once you've entered all of your arguments. Excel will enter the function in your formula.

You can access the Function Wizard at any moment by clicking the Insert Function button when typing a formula into a cell or into the Formula Bar. Excel will send you immediately to the Function Specific dialog box if you type the function name or choose the function from the AutoComplete List, then click the Insert Function button.

Use the function wizard

1. Go to the cell where you wish the formula's results to appear.
2. Type in the cell or in the Formula Bar to get started.
3. Begin typing the function name when you need to utilize it.
4. Click the function name in the AutoComplete list when you see it.

5.     Select Insert Function from the drop-down menu. The function you selected opens in a dialog window.

6.     Put your points forward. When you wish to refer to a cell, simply click it. When you want to refer to a range, select it by clicking and dragging.

7.     Click OK when you've finished your function.

8.     Continue to type your formula in.

9.     When you've finished your entry, press Enter or click the Enter button on the Formula Bar.

Q.14) How to change data/cell alignment and changing font?

A14: Select the cell or cells you want to align first. Text in a single cell, a range of cells, a row or rows, a column or columns, or the entire worksheet can be aligned. (To select all cells, press Ctrl+A.)

1. Choose the cells you'd like to align.

2.     Select a horizontal alignment option in the Alignment group on the Home tab:

Align left  center   Align right

3.     Select a vertical alignment option in the Alignment group on the Home tab:

Top align  Middle align  Bottom align

You can adjust the font angle or change the text alignment to alter the appearance of data in a cell.

Change the orientation of text in a cell

1. Choose a cell, a row, a column, or a range of cells.
2. Select Home > Orientation  , and then select an option.

You can align text vertically or rotate it up, down, clockwise, or counterclockwise:

Rotate text to a precise angle

1. Select a cell, row, column, or a range.
2. Select Home > Orientation    > Format Cell Alignment.
3. Under Orientation on the right side, in the Degrees box, use the up or down arrow to set the exact number of degrees that you want to rotate the selected cell text.
   Positive numbers rotate the text upward. Negative numbers rotate the text downward.

Changing font

Excel uses the Calibri font style in size11 when you input data in a spreadsheet, but if you prefer a different font style or size, here's how to alter them:

1. Choose the cell or cell range in which you want to format the text or number.
2. Select a different font by clicking the arrow next to Font.

1. To alter the font size, select the desired size by clicking the arrow adjacent to the default Font Size.

We've modified the font style to Century and the size to 9 in the sample below.

Data entered in cells outside of the newly formatted cell range will be shown in the default style and size, and you'll have to repeat the formatting procedures. Before entering data, you can also choose blank cells and apply formatting.

Q.15) Write about printing worksheet?

A15: You can print complete or partial worksheets and workbooks one at a time or in batches. You can also simply print the Excel table if the data you wish to publish is in a Microsoft Excel table.

Instead of printing to a printer, you can save a workbook as a file. This is important if you need to print the workbook on a printer that isn't the same as the one you used to print it the first time.

Print one or several worksheets

1. Choose which worksheets you wish to print.

2.     Press CTRL+P or go to File > Print.

3.     Before you click the Print button, you can either alter the settings or click the Print button.

Print one or several workbooks

You must keep all workbook files in the same folder if you wish to print them.

●      Click File > Open.

●      CTRL-click the name of each worksheet you want to print, then click Print.

Print all or part of a worksheet

●      Select the range of data you want to print by clicking the worksheet and then selecting the range of data you want to print.

●      Then, under File, select Print.

●      Select the appropriate option by clicking the arrow next to Print Active Sheets under Settings.

●      Click print.

Q.16) How to add the border and colour?

A16: You may simply add a border around cells or ranges of cells by utilizing predefined border styles. You can construct a custom border if the standard cell boundaries do not match your needs.

Select the cell or set of cells on a worksheet for which you want to add a border, change the border style, or delete a border.

Do one of the following on the Home tab, in the Font group:

●      Pick the arrow adjacent to the Borders , then click a border style to apply a new or different border style.

●      Click the arrow next to the Borders , then click the No Border to remove cell borders.

The Borders button shows the most recent border style that has been used. To apply that style, click the Borders button (not the arrow).

When you apply a border to a cell, it is applied to any adjacent cells that share a bordered cell boundary. When you use a box border to surround the range B1:C5, for example, the cells D1:D5 get a left border.

When two types of borders are applied to a common cell boundary, the most recently applied border is shown.

A single block of cells is prepared from a selected range of cells. When you apply a right border to the range of cells B1:C5, only the right edge of the cells C1:C5 are bordered.

You can use an inner border to print the same border on cells separated by a page break, but the border will only appear on one page. This manner, you can print a border at the bottom of one page's last row and the same border at the top of the next page's first row. Perform the following actions:

●      On both sides of the page break, choose the rows.

●      More Borders can be accessed by clicking the arrow next to the Borders Button image.

●      Click the Inside button Button image under Presets.

●      Remove the vertical border in the preview diagram by clicking it under Border.

Colors

Excel allows you to apply borders to your cells, as you surely already know. This is useful for separating distinct types of information within the same data table, as well as simply improving the appearance of your data.

However, black borders aren't the only option. By following these instructions, you can change the color of your borders:

1. Change the border colors of the cells you want to change.
2. Make sure the ribbon's Home tab is visible.
3. Click the down-arrow next to the Borders tool in the Font group. You'll see a list of cell borders that you can use.
4. At the bottom of the list, select the More Borders option. The Border tab of the Format Cells dialog box appears in Excel.

5.     Select a color for the borders from the Color drop-down list.

6.     Set the cell's borders as you normally would using the controls in the dialog box.

7.     Click the OK button.

You can provide a distinct border type and color for each side of a cell, just like you can specify a different border type and color for each side of a cell. Simply choose the color you wish to use before clicking on the side of the cell where you want it to appear.

You can also utilize the border drawing tools Excel provides to modify the border color. Simply click the down-arrow next to the Borders tool on the Home tab of the ribbon (in the Font group). Excel displays a whole bunch of choices for applying borders.

Q.17) How to change data, number, character and currency format?

A17: In a spreadsheet, to format or change the format of numbers, dates, or currencies:

●      Open a Google Sheets spreadsheet on your PC.

●      Choose the range of cells you want to format or change.

●      Then select Number from the Format drop-down menu.

●      Choose a format for the cells in the range.

Custom formatting of numbers, dates, and currencies

Custom formatting is available for currencies, dates, and numbers. You may find the format that works for your spreadsheet in the text fields accessible in the formatting selections for each of them. If you can't find what you're searching for, you may now use the menus to design your own unique layout.

Date

To add a custom date or time format to your spreadsheet, follow these steps:

1. Open a Google Sheets spreadsheet on your PC.
2. Make a note of the data you want to format.
3. Select Format, Number, and More Formats from the drop-down menu.
4. More date and time formats can be found by clicking the link.
5. To choose a format, type it into the menu text box. In the text area, you can also provide your own unique date or time format.
6. Apply the changes.

By default, the time and date options that appear are based on the location of your spreadsheet.

Click the down arrow in the right corner of the menu text box and select an additional value if you want to add more detailed time or date data to your formatting, such as the hour or minute. By clicking on the arrows in the value and selecting an option, you can change the specific formatting for these values. To remove a value from your formatting, right-click it and choose Delete.

Number

To add a custom currency format to your spreadsheet, follow these steps:

1. Open a Google Sheets spreadsheet on your PC.
2. Make a note of the data you want to format.
3. Select Format, Number, and More Formats from the drop-down menu.
4. More currencies can be found by clicking on the link.
5. To choose a format, type it into the menu text box. In the text area, you can also specify your own unique currency format.
6. Apply the changes.

By selecting an option from the drop-down menu in the right corner of the input box, you can adjust a few aspects about the currency (for example, how many decimal places to show).

Currency and Character

To add a custom number format to your spreadsheet, follow these steps:

1. Open a Google Sheets spreadsheet on your PC.
2. Make a note of the data you want to format.
3. Select Format, Number, and More Formats from the drop-down menu.
4. Select Custom number format from the dropdown menu.
5. To choose a format, type it into the menu text box. In the text area, you can also provide your own custom number format.
6. Apply the changes.

When developing a custom format, keep in mind that it can have up to four components, each separated by a semicolon: positive; negative; zero; non-numeric. There are various financial formats that can be used.

Colors can be used in the formatting to distinguish positive and negative integers, for example, by putting a color in brackets (for example, [Red]) anywhere inside the relevant area of the style. In English, color formatting is required. The following colors can be used:

The following is a list of commonly used syntax characters for creating a bespoke number format:

Q.18) Describe a spreadsheet?

A18: A spreadsheet is a file with rows and columns of cells that can be used to organize, calculate, and sort data. Numeric values, text, formulas, references, and functions can all be found in a spreadsheet.

Spreadsheets are grid-based files that are used to maintain or conduct calculations on personal or business data. It is featured in MS Office and Office 365, and it allows users to generate spreadsheets. MS Office is an on-premise application, whereas Office 365 is a cloud-based solution. There are more than 400 functions and features, including pivot, coloring, graph, chart, conditional formatting, and many others, making it the greatest choice for users.

The term "workbook" in Excel refers to a "spreadsheet." This phrase is used by Microsoft Excel to stress the fact that a single workbook can contain several worksheets, each with its own data grid, chart, or graph.

The most typical reason for using spreadsheets is to organize and store data such as revenue, payroll, and accounting data. Spreadsheets allow the user to perform calculations and create graphs and charts using the data.

In a spreadsheet, the rows have numeric names (1, 2, 3, etc.) and the columns have alphabetic names (a, b, c, etc.), therefore cell names consist of a letter and a number that indicate their location in the grid.

Selection of rows

Row 3 is selected in the illustration above. By clicking on the number 3 in front of the row, you can do so.

Selection of column

Column B is selected in the illustration above. By clicking on the letter B above the column, you can do so.

Selection of cell

Cell B3 is picked in the image above. By clicking on the cell, you can do so.

Worksheet

One or more worksheets can be found in a spreadsheet (tabs). By pressing the ‘+' on the right side of the existing worksheet tab, you can add further worksheets (s).

Worksheet 3 is selected in the image above. A new worksheet will be added to the spreadsheet by clicking the plus sign.

Q.19) Explain pivot table?

A19: A pivot table is a data summarizing tool used in the context of data management. To summarize, sort, reorganize, group, check, aggregate, or standard record held in a database, pivot tables are used. It allows users to convert columns to rows and rows to segments. It allows for grouping based on any data column. When we need to summarize and analyze a large amount of data, pivot tables are the best option.

Starting with a blank Pivot Table

●      Open the GL Pivot Table Data.xlsx spreadsheet. A query is included in the spreadsheet, which includes FY11 costs from the Economic Data Warehouse.

●      Place our cursor in any cell in the data.

●      Open the insert Tab from the ribbon.

●      To access a list of options, click the Pivot Table down arrow.

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.

Q.20) What do you mean by goal seek?

A20: The Goal Seek Excel function (also known as What-if-Analysis) is a method of determining a desired output by altering an underlying assumption. The function effectively back-solves the problem by trial and error, entering in guesses until it finds the answer. If the revenue formula is the number of units sold multiplied by the selling price, Goal Seek may calculate how many units must be sold to generate $1 million in revenue if the selling price is known. The function comes in handy when it comes to sensitivity analysis in financial modeling.

If you know the formula result you want, utilize Excel's Goal Seek feature to discover the input value that delivers it.

Example

Find the grade on the fourth exam that results in a final grade of 70 using Goal Seek in Excel.

●      The final grade is calculated using the algorithm in cell B7.

●      The input cell is cell B5, which contains the grade from the fourth exam.

●      Click What-If Analysis in the Forecast group on the Data tab.

The dialog window for Goal Seek appears.

●      B7 is the cell to be selected.

●      Type 70 in the box labeled "To value."

●      Select cell B5 from the 'By changing cell' box.

●      Click ok.

Result. A final grade of 70 is obtained by receiving a 90 on the fourth exam.