Q1. What is MS Excel, and what are its key features?

Ans. INTRODUCTION: MS-EXCEL is a part of Microsoft Office suite software. It is an electronic

spreadsheet with numerous rows and columns, used for organizing data, graphically representing
data(s), and performing different calculations. It consists of 1048576 rows and 16384 columns in Excel
2007 and later versions, a row and column together make a cell. Each cell has an address defined by
column name and row number example A1, D2, etc. This is also known as a cell reference.

A. What is MS Excel 2021:

Microsoft Excel 2021 is the standalone version of the Excel spreadsheet application, released as part of
the Microsoft Office 2021 suite. It is designed for users who prefer a one-time purchase rather than a
subscription model (which is offered through Microsoft 365). Excel 2021 is available for both Windows
and macOS platforms.

Excel 2021 includes many of the essential features available in previous versions, as well as some new
functionalities and improvements. It is aimed at individuals, small businesses, and enterprises that do
not require the subscription-based cloud services or real-time collaboration features of Microsoft 365.

Microsoft Excel 2021 for Windows allows you to collaboratively work with others and analyze data easily
with new Excel capabilities including co-authoring, Dynamic Arrays, XLOOKUP, and LET functions.

You and your colleagues can open and work on the same Excel workbook. This is called co-authoring.
When you co-author, you can see each other's changes quickly — in a matter of seconds.

Note: Latest Model Microsoft 365 is the licensing model and how often the software is updated Cloud-
based services, Security

B. Key Features of Excel 2021:

 New Functions: XLOOKUP, LET, SEQUENCE, FILTER, UNIQUE, and XMATCH improve data
management and analysis.

 Advanced Data Visualization: New chart types like Map Charts, Funnel Charts, and
improvements to dynamic charts.

 Dynamic Arrays: Formula results automatically spill over into adjacent cells, simplifying complex
data tasks.

 Enhanced Performance: Faster calculation speeds and more efficient processing of large
datasets.

 Improved Security: Enhanced password protection, encryption, and data loss prevention tools.

 Collaboration: Commenting improvements and easier sharing (through OneDrive/SharePoint).

 VBA and Automation: Continued support for macros and automation using VBA.

 Better User Interface: Dark Mode, improved icons, and a more streamlined Ribbon interface.

 Cross-Platform: Consistent experience across Windows and macOS.

 Excel is a software application designed for creating tables to input and organize data. It provides a user-
friendly way to analyze and work with data. The image below provides a visual representation of what an
Excel spreadsheet typically appears like

Excel Interface
 Q2. Create and save a simple excel sheet/workbook?
a. Creating a New Blank Workbook:
Ans. To create a new blank work book when excel is already open:
 Step1: From excel Backstage view’s Select New tab (which has variety of
options for creating new Excel workbooks).
 Step2: Then click blank workbook which start from scratch.
 Step3: A new Blank Workbook will appear.
 {can select any existing Excel workbook template, and then make changes to
suit your needs}
 Keyboard Shortcut: Ctrl+N: To open a new workbook.

b. Saving a Workbook/Worksheet?

Ans. The Save and Save as Commands for 2021

Note: If you select Save to save a new workbook file, Excel 2021 automatically displays the Save As
screen, as you must specify a location and a file type when saving new files.

The Save As screen contains the commands and options you will use to select a location to save your
workbook files, either on your computer, on an attached storage device, or an remote location such as a
network share or an online file storage service.

Steps For Savings workbook/ as usual:

 Click the File tab and then select the save option to save the workbook.
 Choose Save as option if you would like to save the file for the first time.
 To save the workbook on your computer, Select the Save as, a dialog box will appear enter the
file name and click on save button.
 Q3. What is a Cell

Ans. A spreadsheet takes the shape of a table, consisting of rows and columns. A cell is created at the
intersection point where rows and columns meet, forming a rectangular box. Here’s an image illustrating
what a cell looks like:

Q4. What is Cell Address or Cell Reference?

Ans. The address or name of a cell or a range of cells is known as Cell reference. It helps the software to
identify the cell from where the data/value is to be used in the formula. We can reference the cell of
other worksheets and also of other programs.

 Referencing the cell of other worksheets is known as External referencing.

 Referencing the cell of other programs is known as Remote referencing.

There are three types of cell references in Excel:

1. Relative reference.

2. Absolute reference.

3. Mixed reference.
5Q. Add and Delete WorkSheets in excel workbook?

A. To Add a worksheet:
 Just Click/Select the New Sheet plus icon at the bottom of the workbook,
 or select Home Tab > then select Insert > Insert Sheet
 keyboard Shortcut : SHIFT+F11 or ALT+H+I+S

B. To Delete a Worksheet:
 Right-click the Sheet tab and select Delete or press D to Delete
 or select the sheet/Sheet Tab, and then select Home > Delete > Delete Sheet
 Keyboard Shortcut: ALT+H+D+S
6Q. Add and Delete Rows and Columns in Worksheet?

A. Insert or delete a column

 Select any cell within the column, then go to Home > Insert > Insert Sheet
Columns or Delete Sheet Columns.
 Alternatively, right-click the top of the column, and then select Insert or Delete.
 Keyboard Shortcut: For insert: ALT+H+I+C or ALT+I+C or CTRL+SHIFT++
For Delete: ALT+H+D+C or CTRL+ -
B. Insert or delete a row

 Select any cell within the row, then go to Home > Insert > Insert Sheet Rows or Delete
Sheet Rows.
 Alternatively, right-click the row number, and then select Insert or Delete.
 Keyboard Shortcut: For insert: ALT+H+I+R or ALT+I+R or CTRL+SHIFT++
For Delete: ALT+H+D+C or CTRL+ -
7Q. Find & replace data in Worksheets/Workbook of Excel
 A. Find/Finding data:

To find something, press CTRL+F, or go to Home > Editing > Find & Select > Find.

Note: In the following example, we've selected Options >> to show the entire Find dialog box. By
default, it displays with Options hidden.

Step1: In the Find what box, type the text or numbers you want to find, or select the arrow in
the Find what box, and then select a recent search item from the list.

Tips:

 You can use wildcard characters — question mark (?), asterisk (*), tilde (~) — in your
search criteria.

 Use the question mark (?) to find any single character — for example, s?t finds "sat" and
"set".

 Use the asterisk (*) to find any number of characters — for example, s*d finds "sad" and
"started".

 Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde
characters — for example, fy91~? finds "fy91?".

Step2: Select Find All or Find Next to run your search.

Tip: When you Select Find All, every occurrence of the criteria you're searching for is listed, and
selecting a specific occurrence in the list selects its cell. You can sort the results of a Find All search by
selecting a column heading.

Step3: Select Options>> to further define your search if needed:

 Within: To search for data in a worksheet or in an entire workbook,

select Sheet or Workbook.

 Search: You can choose to search either By Rows (default), or By Columns.

  Look in: To search for data with specific details, in the box,
select Formulas, Values, Notes, or Comments.

Note: Formulas, Values, Notes and Comments are available only on the Find tab; only Formulas are
available on the Replace tab.

 Match case - Check this if you want to search for case-sensitive data.

 Match entire cell contents - Check this if you want to search for cells that contain only
the characters that you typed in the Find what box.

If you want to search for text or numbers with specific formatting, select Format, and then make your
selections in the Find Format dialog box.

B. Replace/Replacing data in Worksheet:

To replace text or numbers, press CTRL+H, or go to Home > Editing > Find & Select > Replace.

Note: In the following example, we've selected Options >> to show the entire Find dialog box. By
default, it displays with Options hidden.

1. In the Find what box, type the text or numbers you want to find, or select the arrow in the Find
what box, and then select a recent search item from the list.

2. In the Replace with box, enter the text or numbers you want to use to replace the search text.

3. Select Replace All or Replace.

Tip: When you select Repla-ce All, every occurrence of the criteria that you're searching for is replaced,
while Replace updates one occurrence at a time.

4. Select Options>> to further define your search if needed:

 Within: To search for data in a worksheet or in an entire workbook,

select Sheet or Workbook.
  Search: You can choose to search either By Rows (default), or By Columns.

 Look in: To search for data with specific details, in the box,
select Formulas, Values, Notes, or Comments.

Note: Formulas, Values, Notes and Comments are available only on the Find tab; only Formulas are
available on the Replace tab.

 Match case - Check this if you want to search for case-sensitive data.

 Match entire cell contents - Check this if you want to search for cells that contain only
the characters that you typed in the Find what box.

5. If you want to search for text or numbers with specific formatting, select Format, and then make
your selections in the Find Format dialog box.

Tip: If you want to find cells that just match a specific format, you can delete any criteria in the Find
what box, and then select a specific cell format as an example. Select the arrow next to Format,
select Choose Format From Cell, and then select the cell that has the formatting that you want to
search for.

8Q. Data Entry – Manual and Automatic Cell Formatting?

Ans.

A. Manual Cell Formatting

Manual formatting involves directly applying specific styles or settings to cells.

Steps for Manual Formatting

i. Font and Text Styling:

 Select the cell(s) and use the Home tab to adjust font type, size, color, bold, italic,
underline, etc.

 Shortcut keys: ALT+H+FF, ALT+H+FS, ALT+H+1, ALT+H+2, ALT+H+3

ii. Number Formatting:

 Use the Number Format drop-down menu in the Home tab to apply formats like
Currency, Percentage, Date, Time, or Custom formats.

 Shortcut Keys: ALT+H+N THEN DOWN ARROW to select any Format.

iii. Cell Borders and Fill Colors:

 Add borders using the Borders button.

 Shortcut Key: ALT+H+B+A (will apply border for selected cells) or any border can be
selected. ALT+H+B+N to remove all borders
  Apply background colors with the Fill Color button.

 Shortcut key: ALT+H+H THEN select any color as background and to remove color select
the cells and press ALT+H+H+N.

iv. Alignment:

 Align text horizontally or vertically, or wrap text using alignment options in the Home
tab.

 Shortcut Keys: After selecting cells press ALT+H+AC, AT, AM, AL, AR. ALT+H+FQ,
ALT+H+W, to format based on your requirement.

v. Resize Rows and Columns:

 Manually adjust row height or column width by dragging edges or using the Format
menu.

 Shortcut Keys: ALT+H+O+I For Autofill column width and ALT+H+O+A for row height.

B. Automatic Cell Formatting: Automatic formatting uses predefined rules or styles to format cells
based on their values or conditions.

Methods for Automatic Formatting

a) Conditional Formatting

 Applies specific formatting automatically based on cell content or conditions.

 Found under the Home > Conditional Formatting menu.

 Common rules include:

o Highlighting cells greater than a certain value.

o Applying color scales, data bars, or icon sets.

o Creating custom formulas to define conditions.

Example:

 To highlight cells with values over 50:

1. Select the range.

2. Go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than.

3. Enter 50 and choose a formatting style.

b) Table Formatting (Styles)

  Convert a range into a table to apply pre-designed styles:

1. Select the data range.

2. Go to Home > Format as Table.

3. Choose a table style.

4. Keyboard Shortcut: ALT+O+A

 The table format includes alternate row shading, filters, and dynamic expansion for new data.

c) AutoFormat

 Though this feature is less prominent, Excel still supports autoformatting through:

 Custom templates.

 Add-ins or pre-defined styles.

d) Cell Styles

 Found in Home > Cell Styles, this feature offers predefined styles like "Heading", "Accent", or
"Bad".

 Useful for quickly applying consistent formatting across a workbook.

Data Entry – Manual and Automatic Cell Formattiong

9Q. What is Flash Fill? Create a table with data and execute Flash fill Function?

Ans. Flash Fill is a powerful feature in Microsoft Excel that automatically fills in data when it detects a
pattern. It's especially useful for tasks like formatting names, splitting or combining data, and extracting
specific parts of a string. Below are the steps to use Flash Fill:

Step 1. Enable Flash Fill (If Needed)

 Flash Fill is enabled by default in Excel. If it doesn’t work, ensure it’s turned on:

 Go to File → Options → Advanced.

 Under the "Editing options" section, check Automatically Flash Fill.

Step 2. Enter a Sample

 In a new column, manually type the first result that follows the pattern you want to create.

o Example: If you have a column of full names (John Doe) and want first names, type
"John" in the adjacent column.
 Step 3. Activate Flash Fill

 Select the next empty cell in the column.

 Use one of the following methods to apply Flash Fill:

 Press Ctrl + E (Windows) or Cmd + E (Mac).

 Go to the Data tab on the ribbon and click Flash Fill under the "Data Tools" group.

 Excel will automatically fill in the remaining cells based on the detected pattern.

 Check for accuracy, as Flash Fill may not always interpret patterns perfectly.

Examples of Flash Fill Use Cases

1. Extracting Data:

o From "[Link]@[Link]", extract the first name by typing "John" in the first cell.

2. Combining Data:

o Combine "John" (first name) and "Doe" (last name) into "John Doe" by typing the full
name in the first cell.

3. Formatting Numbers:

o Format phone numbers from 1234567890 to (123) 456-7890 by typing the formatted
version in the first cell.

4. . Flash Fill, Create a data with data and execute flash fill function?

First enter the data in a

pattern that you want
remaining cells to Autofill
and then Press CTRL+E
10Q. Uses of Copy, Paste and Move Options?

Ans. In Excel, Copy, Move, and Paste are essential tools for managing data efficiently. These options
allow you to duplicate, transfer, and reorganize data within a worksheet or across different worksheets
and workbooks.

A. Copying Data: The Copy command duplicates selected data without removing it from its original
location.

Steps to Copy Data

 Select the cell(s), range, or object you want to copy.

 Use one of the following methods:

o Keyboard Shortcut: Press Ctrl + C.

o Ribbon: Go to the Home tab and click the Copy button.

o Right-Click Menu: Right-click the selection and choose Copy.

 Navigate to the destination where you want to paste the copied data.

B. Moving Data: The Move command transfers data from one location to another, removing it from its
original location.

Steps to Move Data

 Select the cell(s), range, or object you want to move.

 Use one of the following methods:

 Cut Command:
  Keyboard Shortcut: Press Ctrl + X.

 Ribbon: Go to the Home tab and click the Cut button.

 Right-Click Menu: Right-click the selection and choose Cut.

 Drag and Drop:

 Click and hold the edge of the selection.

 Drag it to the desired location.

 Place the data in its new location using the Paste command if necessary.

C. Pasting Data: The Paste command inserts copied or cut data into a new location.

Steps to Paste Data

1. Go to the destination cell where you want the data to appear.

2. Use one of the following methods:

 Keyboard Shortcut: Press Ctrl + V.

 Ribbon: Go to the Home tab and click the Paste button.

 Right-Click Menu: Right-click the destination and choose Paste.

D. Paste Options: Excel provides multiple paste options to control how data is inserted. After pasting, a
Paste Options icon appears for additional customization.

Common Paste Options

 Paste All: Inserts all copied data, including formatting (default).

 Formulas: Pastes only formulas.

 Values: Pastes only the values (without formulas or formatting).

 Formatting: Pastes only the cell formatting.

 Transpose: Switches rows to columns and vice versa.

 Paste Link: Creates a link to the original data source.

 Picture: Pastes the content as an image.

 Column Widths: Applies the column width from the source.

Keyboard Shortcuts for Paste Special

 Open the Paste Special menu: Ctrl + Alt + V.

  Use the options in the dialog box to select the desired paste method.

E. Drag and Drop for Quick Copy/Move

 Copy:

 Hold the Ctrl key while dragging the selection to create a copy.

 Move:

 Simply drag the selection to a new location.

11Q. Date and Time Functions in MS Excel?

Excel provides a variety of date and time functions to handle, manipulate, and analyze dates and times.
These functions are essential for scheduling, financial calculations, and tracking tasks.

Date and Time Functions

a) TODAY

 Description: Returns the current date (no time included).

 Syntax: =TODAY()

 Example: If today is January 11, 2025, =TODAY() will display 11/1/2025 in cell.

b) NOW

 Description: Returns the current date and time.

 Syntax: =NOW()

 Example: If the current date and time are January 1, 2025, 10:00 AM, =NOW() will display
1/1/2025 10:00.

a) DAY

 Description: Extracts the day of the month (1–31).

 Syntax: =DAY(date)

 Example: =DAY("1/15/2025") → 15.

b) MONTH

 Description: Extracts the month (1–12).

 Syntax: =MONTH(date)

 Example: =MONTH("1/15/2025") → 1.

c) YEAR

 Description: Extracts the year.

 Syntax: =YEAR(date)

 Example: =YEAR("1/15/2025") → 2025.

d) HOUR
  Description: Extracts the hour from a time (0–23).

 Syntax: =HOUR(time)

 Example: =HOUR("10:30 AM") → 10.

d) Dateif: It calculates the difference in days between the dates in cells

 Use simple subtraction or the DATEDIF function =DATEDIF(start_date, end_date, unit)

o unit options:

 "d": Days

 "m": Months

 "y": Years

 Example: =DATEDIF("1/1/2024", "1/1/2025", "y") → 1.

e) DATE

 This functions Combines year, month, and day of different cells into a valid date in a cell

 Syntax: =DATE(year, month, day)

 Example: =DATE(2025, 1, 15) → 1/15/2025.

e) TEXT

 Formats a date or time into a specific format.

 Syntax: =TEXT(value, format_text)

 Example: =TEXT(NOW(), "dd-mmm-yyyy hh:mm AM/PM") → 01-Jan-2025 10:30 AM.

f) Eomonth Function: Here are the steps to use the EOMONTH function in Excel, summarized in a few
sentences:

Syntax: The formula is =EOMONTH(start_date, months) where:

i. start_date is the starting date.

ii. months is the number of months to move forward (positive) or backward (negative).

Enter the Formula: Click on a cell and type the following formulas for particulars results
required.
 iii. =EOMONTH(A1,0) It return the last date of the same month i.e., month their in the
Cell A1

iv. =EOMONTH(A1,1) It returns the last date of the next month

v. =EOMONTH(A1,-1) It returns the last date of the last month from the date mention in
the Cell A1.

vi. =EOMONTH(A1,-1)+!) It returns the First date of the same month in the cell A1

vii. =EOMONTH(A1, 2) (example: if A1 contains a date, this returns the last date two months
ahead).

Result: The function outputs the last date and first date of the specified month relative to the start
date.
12Q. Create a table to find values by applying basic Excel mathematical functions.

Ans. Basic Mathematical functions on the following table data and Steps to Apply Each Formula

a. Calculate Total price of each item:

o Formula: =B2*C2 (Quantity × Unit Price).

o Steps:

1. Click on the Total Cost ($) cell (e.g., D2) then Type = to start the formula=B2*C2

2. Click/ on the Quantity cell (B2), then Type * (multiplication symbol) then Click
on the Unit Price ($) cell (C2).

3. Press Enter to calculate.

4. Copy this formula to the rows below by dragging the small square at the bottom-
right corner of the D2 cell.

b. Calculate Discount Amount:

o Formula: =D2*(E2/100) (Total Cost × Discount Rate ÷ 100).

 o Steps:

1. Click on the Discount Amount ($) cell (e.g., F2) and Type = to start the formula.

2. Click on the Total cost ($) cell (D2) and Type * (multiplication symbol).

3. Type ( to group the next calculation. Then Click on the Discount (%) cell (E2). And
then Type /100) to divide by 100.

4. Press Enter to calculate.

5. Copy this formula down to the rows below.

c. Calculate Final Cost:

o Formula: =D2-F2 (Total Cost − Discount Amount).

o Steps:

1. Click on the Final cost ($) cell (e.g., G2) and Type = to start the formula.

2. Next Click on the Total Cost ($) cell (D2) and Type - (subtraction symbol). Click on
the Discount Amount ($) cell (F2).

3. Press Enter to calculate.

4. Copy this formula down to the rows below.

d. Calculate Sum, Average, Max, and Min on the bottom of the final cost sell to find the each
Formulas: =Average (G2 cells range), =Sum(G2 cells range), =Max(G2 cells range),
=Min(G2 cells range.
13Q. List the steps to convert a table into a pivot table?

Ans. A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see
comparisons, patterns, and trends in your data. PivotTables work a little bit differently depending on
what platform you are using to run Excel.

Step1. Organize Your Data

 Ensure your data is structured as a table with column headers and rows of data.

 Avoid blank rows or columns within the data range.

2. Select the Data: Click anywhere inside your table or manually highlight the range of cells containing
your data.

3. Open the Pivot Table Dialog: Go to the Insert tab in the Excel ribbon then Click on the PivotTable
button in the "Tables" group.

4. Configure Pivot Table Options: In the Create PivotTable dialog box:

i. Select the table or range: If your data is already selected, the range will appear
automatically.

ii. Choose where to place the pivot table:

 New Worksheet: Places the pivot table in a new sheet.

 Existing Worksheet: Allows you to select a specific cell where the pivot table
will be placed.

5. Design the Pivot Table Layout:

i. Once the pivot table is created, the PivotTable Fields pane will appear on the right side
of the Excel window.

ii. Drag and drop fields into the following areas:

 Rows: Add fields to display as rows in the pivot table.

 Columns: Add fields to display as columns in the pivot table.

 Values: Add fields to calculate data, such as sums, averages, or counts.

 Filters: Add fields to filter data dynamically.

6. Customize the Pivot Table

 i. Use the PivotTable Analyze and Design tabs in the ribbon to:

 Change the layout (e.g., compact, tabular, or outline format).

 Apply formatting and styles.

 Add totals, subtotals, or grand totals.

7. Update the Pivot Table

i. If your original table changes (e.g., data is added or updated), you can refresh the pivot
table:

 Right-click the pivot table and choose Refresh.

 Or, click the Refresh button in the PivotTable Analyze tab.

8. Save Your Work: Save your workbook to preserve the pivot table and its settings for future use.
14Q. Write down the steps to Create a pie chart from the table?

Ans. Following are the steps to create a pie chart form the table:

Step1: Enter Data: Ensure your data is organized in a table with labels in one column
and values in the next column.
 Ensure your table has two columns:
Category (e.g., items, labels, or names).
Values (e.g., numbers or percentages corresponding to each category).
Check that there are no empty rows or columns within your data range.

Step2: Select Data: Highlight the table, including both labels and values.
Step3: Insert Chart: Go to the Insert tab on the ribbon.
Step4: Choose Pie Chart: Click on the Pie Chart icon in the Charts group and choose your
preferred pie chart style.
Step5: Customize Chart: Use the Chart Design and Format tabs to add a title, change colors, or
adjust labels.
Step6: Save Chart: Drag the chart to the desired location in the worksheet or copy it elsewhere.

Clubs members
5; 3%
10; 7%
Sapphire
37; 24% Ruby
Emarald
35; 23% Topaz
Agate
35; 23% Turquoise
30; 20%

15Q. Steps to illustrate a table in column chart format.

Ans. Here are the steps to Illustrate the table in column chart format.

These steps to be performed on the given table data.

Step1: Select the Data: Highlight the entire table, including headers if present.

Step2: Insert the Column Chart: Go to the Insert tab and click on the Insert Column or Bar Chart
button.

Step3: Choose a Chart Type: Select a column chart style, such as clustered, stacked, or 3-D.

Step4: Customize the Chart: Add titles, labels, and adjust colors using the Chart Tools options
and save the file.
16Q. Create a table to find the values using different cell references in MS Excel?

Steps to Create a Table Using Different Cell References in MS Excel (Sales Data Example)

Ans. Step 1: Open Excel and Enter Sales Data Start by entering sample sales data for 3 months in
an Excel sheet.

Step 2: a) Relative Cell References: execute the below steps for relative cell reference.

 In Cell D2, enter the formula to calculate total sales for Product 1: =A2+B2+C2
 Copy the formula down to other rows (D3:D6), and Excel will adjust references
automatically.
  If we copy the formula =A2+B2+C2 from the cell “E2” to “E3”, the formula in E3 will
automatically become =A3+B3+C3 and so on to the subsequent cell in that column.

Total Sales (D2:D6) - Relative

Product Jan Sales (A2:A6) Feb Sales (B2:B6) March Sales (C2:C6) Reference
Product
1 100 120 130 350
Product
2 150 160 170 480
Product
3 200 210 220 630
Product
4 250 260 270 780
Product
5 300 310 320 930

Step 3: b) Absolute Cell References: following steps shows how Absolute reference functions

 In Cell E2, calculate the percentage of total sales compared to a fixed target (e.g.,
$1000): formula =D2/$G$1

 Use $G$1 as an absolute reference so it doesn’t change when copied.

 Copy down to other rows (E3:E6).

 To lock the reference G1, use $G$1 in a formula like =D2/$A$2. When copied to
other cells, $A$2 will remain fixed.
 E2 = D2/$G$1, when copied to E3, E3 becomes = D3/$A$2
 Note: D3 is next columns left to E3 in the same row.

Product Total Sales (D2:D6) Sales Target (G1 = 1000) % of Target (E2:E6) - Absolute Reference
Product 1 350 1000 35%
Product 2 480 1000 48%
Product 3 630 1000 63%
Product 4 780 1000 78%
Product 5 930 1000 93%

Step 4: c) Mixed Cell References

 In F2, calculate sales growth using mixed references:

=B2/A2 – 1 or B2/A$2

 Copy the formula to other rows (F3:F6), ensuring that column reference remains
relative while row reference is locked.

 So, when F2 is copied to I3, I3 = B3/A$2 because B3 comes from the relative
reference & A$2 comes from the absolute reference for row & relative reference
for Column.

Product Jan Sales (A2:A6) Feb Sales (B2:B6) Growth % (F2:F6) - Mixed Reference
Product 1 100 120 20%
Product 2 150 160 6.67%
Product 3 200 210 5%
Product 4 250 260 4%
Product 5 300 310 3.33%

Step 5: Use Whole Column Reference or row reference

 Calculate Total Sales of All Products using a whole column reference: =SUM(D:D)
and =SUM(2:2)
 The result will update automatically if new data is added.

 Total Sales of All Products

= 3170

By following these steps, you can efficiently calculate values using different cell references in
Excel. Relative references update automatically, absolute references remain fixed, mixed
references partially lock rows or columns, and whole column references dynamically include
all data.

1. Relative Cell Reference

Relative cell references are the default in Excel. They change automatically when a formula is
copied to another cell, maintaining the relative position between cells. There is no dollar ($)
sign in the relative reference for the cell.
Absolute Cell Reference
When copying or using AutoFill, there are times when the cell reference must stay the same. A
column and/or row reference is kept constant using dollar signs. So, to get an absolute reference
from a relative, we can use the dollar sign ($) characters.
To refer to an actual fixed location on a worksheet whenever copying is done, we use absolute
reference. The reference here is locked such that rows and columns do not shift when copied.

The $ sign can be manually typed in an Excel formula to adjust a relative cell relation to absolute
or mixed. You can also speed things up by pressing the F4 key. You must be in formula edit
mode to use the F4 shortcut.

Example: When you select a cell having only relative reference (i.e., no $ sign), say = B2:
 The first time when you press F4, it becomes =$B$2
 The second time when you press F4, it becomes =B$2
 The third time when you press F4, it becomes=$B2
 The fourth time when you press F4, it becomes back to the relative reference=B2

The F4 shortcut in Excel toggles between relative and absolute references.

 How it Works: Place the cursor in a cell reference within a formula and press F4 to cycle
through:

 $A$1 (absolute reference).

 A$1 (row locked).

 $A1 (column locked).

 A1 (relative reference).
This shortcut is a quick way to lock references as needed.

17Q. Create a table and find values using the Data Manipulation function -VLOOKUP.

Ans. Step 1: Prepare the Data Table

Ensure your data is organized in a table format, where:

✅ The lookup column (where you search for values) is the first column.
✅ The return values (the values you want to fetch) are in columns to the right of the lookup
column.

Create a dataset with columns for ID, Name, Address, Department, and Salary in an Excel
sheet.

Departmen
ID Name Address Salary
t
 101 John Muradabad HR 50,000
102 Alice Lucknow IT 60,000
103 Mark Auragabad Finance 55,000

104 Sara Hyderabad IT 65,000

Step 2: Then Apply BASIC VLOOKUP: To find the Department of an employee with ID 102,

=VLOOKUP(102, A2:D5, 4, FALSE)

OUTPUT RESULT: IT

VLOOKUP
Department
: Emp ID
102 IT

Step 3: Use VLOOKUP with a Dynamic Lookup Value to fetch salary

Steps to Create an EMP ID Drop-Down List in Excel (Cell G2)

i. Select the Cell then Click on cell G2 where you want the drop-down list.
ii. Go to Data Validation: Click on the Data tab in the ribbon. Select Data Validation from
the Data Tools group. Click on Data Validation from the drop-down menu.
iii. Set Up the Drop-Down List:
 In the Settings tab, under Allow, select List.
 In the Source field, select the range where your EMP IDs are stored (e.g.,
A2:A8).

4️Confirm and Apply:

 Click OK to apply the drop-down list

Then Apply the formula in cell H3 =VLOOKUP(G2, A2:D5, 5, FALSE)

Now, change the EMP ID in cell G2, and Excel will update the result automatically.

OUTPUTS:

Emp ID DEPARTMENT SALARY

103 Finance 55000
 Emp ID DEPARTMENT SALARY
104 Finance 65000

Step 4: Use VLOOKUP with Approximate Match

If the dataset has ranges, like grades, or tax slabs, use TRUE for an approximate match. Example:

Score Grade
0 F
40 D
60 C
80 B
90 A

For a student’s score in cell H2, use: formula

=VLOOKUP(H2, A2:B6, 2, TRUE)

This finds the correct grade based on score ranges that which is enter in cell H2

OUTPUTS RESULTS:

Sore Grade
79 C

Don’t write grey out (grey color) Content below REFER THIS
GREY OUT TEXT FOR VIVA

Step 5: Use VLOOKUP with Multiple Sheets

To fetch data from another sheet (Sheet2), use:formula

=VLOOKUP(A2, Sheet2!A2:D5, 3, FALSE)

This searches for A2’s value in Sheet2 and returns the matching column.

Handle Missing Data Using IFERROR

If a value is not found, VLOOKUP returns #N/A. To handle errors, use IFERROR:

=IFERROR(VLOOKUP(G2, A2:D5, 3, FALSE), "Not Found")

  If G2 is not in the table, it displays "Not Found" instead of #N/A.

 Sort the data when using TRUE for an approximate match.

 Use IFERROR to avoid errors and Use $ to lock ranges when copying formulas.
 VLOOKUP works only for searching left to right.
 For advanced lookups, use INDEX-MATCH or XLOOKUP (Excel 365/2019).
 Always use absolute references ($A$2:$D$5) if you want the table range to remain fixed when
copying formulas.
 If a value is not found, VLOOKUP returns #N/A. To handle errors, use
IFERROR:IFERROR(VLOOKUP(G2, A2:D5, 3, FALSE), "Not Found")
  LOOKUP only searches left to right. If you need to search backwards, use INDEX-MATCH or
XLOOKUP.
  XLOOKUP (Excel 365 & 2019) is an advanced alternative to VLOOKUP

18Q Create a table and find values using basic statistical functions in Excel Sheet?

Ans. Step 1: Create a Table with Sample Data

Open Microsoft Excel and enter data in a structured format.

Student Score 1 Score 2 Score 3

Faiza 96 90 98
Amtul 90 96 92
Alina 90 95 85
Ruqsar 92 98 88
Ayesha 88 80 95

Step 2: Apply Basic Statistical Functions

1️Calculate the Average (Mean) - Formula: =AVERAGE(B2:D6)

This gives the average score for all students.

2️ Find the Median (Middle Value) - Formula:=MEDIAN(B2:D6)

This returns the middle value of all scores.

3️Find the Mode (Most Frequent Value) - Formula: =[Link](B2:D6)

This gives the most frequently occurring score.

 4️ Find the Minimum and Maximum Values

 Minimum Score: =MIN(B2:D6)

 Maximum Score: =MAX(B2:D6)

5️Calculate Standard Deviation (Variability of Scores) - Formula: =STDEV.P(B2:D6)

This measures how spread out the student marks values are

6️Find the Sum of All Scores - Formula: =SUM(B2:D6)

This gives the total sum of all student scores.

Step 3: Display Results in a New Section for overall students performance.

OUTPUT RESULT:

Statistic Formula Used Result (Example)

Average Score =AVERAGE(B2:D6) 84.67

Median Score =MEDIAN(B2:D6) 85

Mode Score =[Link](B2:D6) 85

Minimum Score =MIN(B2:D6) 70

Maximum Score =MAX(B2:D6) 95

Standard Deviation =STDEV.P(B2:D6) 7.85

Total Score =SUM(B2:D6) 1270

Score Score Score TOTAL

Student MEDIAN
1 2 3 AVG MODE MAX MIN SD SCORE

Faiza 96 90 98 94.6 96 #N/A

7 98 90 4.1633 284

Amtul 90 96 92 92.6 92 #N/A

7 96 90 3.0551 278

Alina 90 95 85 90.0 90 #N/A

0 95 85 5.0000 270

Ruqsar 92 98 88 92.6 92 #N/A

7 98 88 5.0332 278

Ayesha 88 80 95 87.6 88 #N/A

7 95 80 7.5056 263
 MS ACCESS DATABASE
[Link] is MS Access? State its Key features and Uses?
Ans. Microsoft Access is a Relational Database Management System (RDBMS) developed by
Microsoft. It is part of the Microsoft Office suite and is used to store, manage, and retrieve data
efficiently. MS Access is designed for small to medium-sized database applications and provides
a user-friendly interface for creating databases without requiring advanced programming skills.
Key Features of MS Access
 Tables – These are like Excel spreadsheets but more powerful! They store structured
data in rows and columns. Each table holds specific information, like customer details or
sales records.
 Queries – Think of queries as smart searches! They help you retrieve specific data from
tables, filter information, or even perform calculations. You can use queries to quickly
find answers from large databases.
 Forms – Forms make data entry easy and user-friendly. Instead of typing directly into
tables, forms provide a neat layout where users can input data without errors. Forms can
also have buttons to automate tasks.
  Reports – Want to print or share your data in a professional format? Reports organize
information neatly for printing, summarizing, and presenting. You can create invoices,
summaries, or charts in reports.
 Relationships – Instead of storing the same data in multiple places, Access lets you link
tables together. For example, you can connect a "Customers" table to an "Orders" table,
so you don’t have to enter customer details repeatedly.
 Macros & VBA – These helps automate repetitive tasks. Macros are simple automation
tools, while VBA (Visual Basic for Applications) is for advanced customization. You can
create buttons to run actions automatically.
 Data Validation & Security – Access ensures that only correct data is entered. You can
set rules, restrict access, and even password-protect your database to keep it safe.
 Multi-User Support – Unlike Excel, multiple people can work on an Access database at
the same time without overwriting each other’s data. This makes it great for team
projects.
 Integration with Other Apps – MS Access works well with Excel, Outlook, and SQL
databases, making it easy to import/export data or use it alongside other Microsoft
tools.
MS Access is a powerful tool for managing large amounts of structured data efficiently while
keeping everything organized, secure, and easy to use. MS Access is specifically designed for
database management, it serves as essential tool for structured data management,
automation, and relational databases, while Excel, Word, and PowerPoint serve different
purposes

Uses of MS Access:
 It allows users to store large amounts of data in an organized manner.
 Provides easy-to-use tools for creating tables, queries, forms, and reports.
 Supports data relationships for efficient data management.
 Enables automation through Macros and VBA for advanced database functions.
 It provides a cost-effective solution for managing databases without the complexity of full-
fledged database management systems like SQL Server or Oracle.
 *DON’T WRITE THIS GREY COLOR TEXT JUST
REFER IT FOR VIVIA VOICE*

✔ Tables – Store structured data in rows and columns.

✔ Queries – Retrieve and manipulate data efficiently.
✔ Forms – Provide a user-friendly interface for data entry.
✔ Reports – Generate printable summaries of data.
✔ Relationships – Connect multiple tables to avoid data redundancy.
✔ Macros & VBA – Automate repetitive tasks and enhance functionality.

Difference Between MS Word, MS Excel, MS Access, and MS PowerPoint:

MS PowerPoint
Feature MS Word 📝 MS Excel 📊 MS Access 🗄
🎤
Used for creating Used for calculations, Used for storing, Used for creating
Purpose documents, reports, data analysis, and managing, and slideshows and
and written content. charts. organizing databases. presentations.
Relational database Designing slides,
Text formatting, Data entry, formulas,
management with adding
Main Function document creation, graphs, and pivot
tables, queries, and animations, and
and editing. tables.
reports. visual storytelling.
Presenting
Handling large
Writing letters, Managing budgets, information
datasets, business
Best For reports, resumes, financial analysis, and effectively in
records, and
and books. statistical calculations. meetings and
automation.
lectures.
Works with
Works with structured
Works with text and Works with numbers, multimedia (text,
Data Handling data across multiple
images. tables, and charts. images, videos,
tables.
animations).
Database
Word processor with Grid-based spreadsheet Slide-based design
management system
User Interface a toolbar for with cells, formulas, with themes and
with tables, forms, and
formatting. and functions. transitions.
queries.
Basic automation
Advanced formulas, Highly automated with
Limited (basic with slide
Automation functions, and macros queries, reports, and
macros). transitions and
(VBA). macros.
animations.
File Format .docx, .pdf .xlsx, .csv .accdb, .mdb .pptx, .pdf
 Multiple users can Can be shared but not Multi-user access for Multiple users can
Collaboration edit documents in ideal for simultaneous database co-edit
real time. editing. management. presentations.

Which One to Use?

 Use MS Word if you need to write and format documents.

 Use MS Excel for calculations, data analysis, and charting.

 Use MS Access for managing and organizing large databases.

 Use MS PowerPoint to create engaging presentations.

Each tool serves a different purpose, and together, they help in document creation, data management,
and professional presentations.
____________________________________________________________________________________

20Q. Write down the steps to Create and print the MS Access database?
Ans. Steps to Create and Print an MS Access Database
Step 1: Open MS Access
 Launch Microsoft Access on your computer.
 Click "Blank Database" and enter a name for your database.
 Choose a location to save it and click on "Create" to open a new database.
Step 2: Create a Table:
 Click on "Table Design" under the Create tab.
 Define fields (e.g., ID, Name, Age, Address) and set the Data Type (Text,
Number, Date, etc.). for each field.
 Set a Primary Key (e.g., "ID") by right-clicking the field and selecting
Primary Key.
 Click Save and give a name to your table.
Step 3: Enter Data
 Double-click on the table to open it in Datasheet View.
 Enter records in the rows.
Step 4: Create a Query (Optional):
 Click on Create > Query Design to filter or analyze data.
 Select the table and fields you need.
 Save the query.
Step 5: Create a Report
 Click Create > Report Wizard.
 Select the table/query fields you want to include in the report.
 Choose a layout and style, then click Finish.
Step 6: Print the Datasheet/Query/Report
 Click File > Print.
 Adjust print settings and select the printer.
 Click Print to generate a hard copy of your database Datasheet/Reports.
______________________________________________________________________________
21Q. Write the steps to Import data from MS Excel to MS Access database?
Ans. Steps to Import Data from MS Excel to MS Access Database
Step 1: Open MS Access
 Launch Microsoft Access (2016, 2019, or 2021).
 Open an existing database or create a new blank database.
Step 2: Open the External Data Tab
 Click on the External Data tab in the toolbar.
 Select New Data Source > From File > Excel.
Step 3: Select the Excel File
 Click Browse and locate the Excel file you want to import.
 Choose how you want to store data:
o Import: Copies data into a new or existing table in Access.
o Link: Keeps the data in Excel but allows Access to use it.
 Click OK.
Step 4: Select the Worksheet or Range
 If your Excel file has multiple worksheets, choose the one particular sheet
that needs to be import.
 If necessary, select a specific range of data.
 Click Next.
Step 5: Define Column Headings
 If the first row contains column headings, check First Row Contains Column
Headings.
 Then Click Next.
Step 6: Choose a Primary Key
 Let Access add a primary key,
 Or select an existing column as the primary key.
 Click Next.

Step 7: Name the Table & Finish

  Enter a table name to store the imported data.
 Click Finish > Close.
Step 8: Verify Imported Data
 Double-click on the newly created table to open it in Datasheet View and
verify the data.
 _________________________
____________________________________________________________

22Q. Write down the steps to Create a connection between MS Excel and MS Access
database?
Ans. You can connect MS Excel to an MS Access database to import, export, or query data efficiently.
Below are the steps to establish the connection:

➡ Steps to Create a Connection Between MS Excel and MS Access Database

➡Step 1: Open Excel and Go to Data Tab

➜ Open Microsoft Excel

➜ Click on the Data tab
➜ Select Get Data → From Database → From Microsoft Access Database

➡ Step 2: Select the Access Database File

 ➜ A File Explorer window appears
➜ Browse and select the Access (.ACCDB or .MDB) file
➜ Click Import

➡ Step 3: Choose the Table or Query

➜ The Navigator window opens

➜ Select the desired table or query
➜ Click Load (direct import) or Transform Data (modify before import)

➡ Step 4: Load Data into Excel

➜ The selected data appears as a table in an Excel worksheet

➜ Apply filters, formulas, and PivotTables for analysis

➡ Step 5: Refresh the Data (Optional)

➜ To update Excel with the latest Access data:

➜ Go to the Data tab
➜ Click Refresh All

By following these steps, you can establish a dynamic connection between MS Excel and MS Access for
seamless data integration and analysis

OUTPUTS:
Alhamdulillah! it’s over guys, we are done

NO MORE QUESTIONS
Formatting text or numbers can make them appear more visible especially when you have a large
worksheet. Changing default formats includes things like changing the font color, style, size, text
alignment in a cell, or apply formatting effects.

If you want text or numbers in a cell to appear bold, italic, or have a single or double underline, select
the cell and on the Home tab, pick the format you want:

Change font style, size, color, or apply effects

Click Home and:

 For a different font style, click the arrow next to the default font Calibri and pick the style you
want.

 To increase or decrease the font size, click the arrow next to the default size 11 and pick
another text size.
 To change the font color, click Font Color and pick a color.

 To add a background color, click Fill Color next to Font Color.

 To apply strikethrough, superscript, or subscript formatting, click the Dialog Box Launcher, and
select an option under Effects.
Change the text alignment

You can position the text within a cell so that it is centered, aligned left or right. If it’s a long line of
text, you can apply Wrap Text so that all the text is visible.

Select the text that you want to align, and on the Home tab, pick the alignment option you want.

Clear formatting

If you change your mind after applying any formatting, to undo it, select the text, and on
the Home tab, click Clear > Clear Formats.
 Number Formatting:

 Adjusts how numerical data appears (e.g., currency, percentage, date).

 Example: Formatting "1000" as "$1,000.00".

 Conditional Formatting

Conditional formatting is a powerful tool used in spreadsheets to enhance data visualization by

automatically applying formatting rules based on the content of cells. It highlights key patterns,
trends, and outliers, enabling users to analyze data effectively.

Key Features:

 Highlighting Specific Values: Emphasizes data meeting certain criteria.

 Color Scales: Uses gradients to represent value ranges visually.

 Data Bars: Displays proportional bars within cells for quick comparisons.

 Icon Sets: Adds visual cues like arrows or traffic lights to indicate trends or statuses.

 Custom Rules: Allows users to set specific conditions and formatting styles

 Conditional Formatting:
o Highlights cells based on criteria.
o Example: Highlighting cells with values over a certain threshold.

Features of MS Excel

Ribbon

Th eRibbon in MS-Excel is the topmost row of tabs that provide the user with different
facilities/functionalities. These tabs are:

Home Tab

It provides the basic facilities like changing the font, size of text, editing the cells in the spreadsheet,
autosum, etc.
Insert Tab

It provides the facilities like inserting tables, pivot tables, images, clip art, charts, links, etc.

Page layout

It provides all the facilities related to the spreadsheet-like margins, orientation, height, width,
background etc. The worksheet appearance will be the same in the hard copy as well.

Formulas

It is a package of different in-built formulas/functions which can be used by user just by selecting the cell
or range of cells for values.

Data

The Data Tab helps to perform different operations on a vast set of data like analysis through what-if
analysis tools and many other data analysis tools, removing duplicate data, transpose the row and
column, etc. It also helps to access data(s) from different sources as well, such as from Ms-Access, from
web, etc.

Review

This tab provides the facility of thesaurus, checking spellings, translating the text, and helps to protect
and share the worksheet and workbook.

View

It contains the commands to manage the view of the workbook, show/hide ruler, gridlines, etc, freezing
panes, and adding macros.

How to Create a New Spreadsheet

In Excel 3 sheets are already opened by default, now to add a new sheet :

 In the lowermost pane in Excel, you can find a button.

 Click on that button to add a new sheet.

 We can also achieve the same by Right-clicking on the sheet number before which you want to
insert the sheet.

 Click on Insert.
 Select Worksheet.

 Click OK.
How to Open an Existing Worksheet

On the lowermost pane in Excel, you can find the name of the current sheet you have opened.

On the left side of this sheet, the name of previous sheets are also available like Sheet 2, Sheet 3 will be
available at the left of sheet4, click on the number/name of the sheet you want to open and the sheet
will open in the same workbook.

For example, we are on Sheet 4, and we want to open Sheet 2 then simply just click on Sheet2 to open it.
Managing the Spreadsheets

You can easily manage the spreadsheets in Excel simply by :

 Simply navigating between the sheets.

 Right-clicking on the sheet name or number on the pane.

 Choose among the various options available like, move, copy, rename, add, delete etc.

 You can move/copy your sheet to other workbooks as well just by selecting the workbook in
the To workbook and the sheet before you want to insert the sheet in Before sheet.
How to Save the Workbook

1. Click on the Office Button or the File tab.

2. Click on Save As option.

3. Write the desired name of your file.

4. Click OK.

How to Share your Workbook

1. Click on the Review tab on the Ribbon.

2. Click on the share workbook (under Changes group).

3. If you want to protect your workbook and then make it available for another user then click on
Protect and Share Workbook option.

4. Now check the option “Allow changes by more than one user at the same time. This also allows
workbook merging” in the Share Workbook dialog box.

5. Many other options are also available in the Advanced like track, update changes.
 6. Click OK.

Ms-Excel shortcuts

1. ALT+N: To open a new workbook.

2. ALT+O: To open a saved workbook.

3. ALT+S: To save a workbook.

4. ALT+C: To copy the selected cells.

5. ALT+V: To paste the copied cells.

6. ALT+X: To cut the selected cells.

7. ALT+W: To close the workbook.

8. Delete: To remove all the contents from the cell.

9. ALT+P: To print the workbook.

10. ALT+Z: To undo.