Define worksheet

The term Worksheet used in Excel documents is a collection of cells organized in rows and columns. It is the working
surface you interact with to enter data. Each worksheet contains 1048576 rows and 16384 columns and serves as a giant
table that allows you to organize information.

State the term Data Validations.

Data validation in Microsoft Excel is a feature that allows you to control the type of data entered into a worksheet to
improve the accuracy of the data. For example, Excel data validation enables users to limit data entries to a selection from
a drop-down list and to restrict certain data entries, such as dates or numbers outside of a predetermined range. Data
validation can also help control formulas and the input from those formulas. You can even craft custom Excel data
validation messages that help guide users toward the correct data entry when they hit a limit. As a result, Excel data
validation helps reduce the amount of unstandardized data, errors, or irrelevant information in worksheets.

What is mean by Pivot tables?

A pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data in a spreadsheet or
database table to obtain a desired report. The tool does not actually change the spreadsheet or database itself, it simply
“pivots” or turns the data to view it from different perspectives.

Identify the term Text functions.

The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It's useful
in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or
symbols.
More often than not, MS Excel is used to tabulate and organize number data. However, it can also deal with logical, error
and text data. Microsoft Excel has a unique set of functions for each data type. A text function (an Excel string function)
is a built-in formula for modifying, locating and extracting textual data in MS Excel.

List out the types of Charts

Advanced Excel charts are more sophisticated than standard ones. They enable you to present your data more
comprehensively. One advantage of using advanced Excel charts is that they enable you to visually present complex data
appealingly.
 Column chart. Data that is arranged in columns or rows on an Excel sheet can be plotted in a column chart. ...
 Line chart. ...
 Pie chart. ...
 Bar chart. ...
 X Y (scatter) chart. ...
 Area chart. ...

1
  Stock chart. ...
 Surface chart.

5m

Write about lookup and reference functions.

Lookup & Reference functions help you to work with arrays of data, and are particularly useful when you need to cross
reference between different data sets. They perform tasks such as providing information about a range, returning the
location of a given address or value, or looking up specific values.

Lookup and Reference Functions

The following table lists all the Lookup & Reference functions −

S.No. Function and Description

1 ADDRESS
Returns a reference as text to a single cell in a worksheet

2 AREAS
Returns the number of areas in a reference

3 CHOOSE
Chooses a value from a list of values

4 COLUMN
Returns the column number of a reference

5 COLUMNS
Returns the number of columns in a reference

6 FORMULATEXT
Returns the formula at the given reference as text

7 GETPIVOTDATA
Returns data stored in a PivotTable

8 HLOOKUP
Searches for a value in the top row of a table and then returns a value in the same column from a row you specify in the
table

9 HYPERLINK
Creates a shortcut that opens a document on your hard drive, a server, or the Internet

10 INDEX
Uses an index to choose a value from a reference or array

11 INDIRECT
Returns a reference indicated by a text value

12 LOOKUP
Returns a value either from a one-row or one-column range or from an array

13 MATCH
Returns the relative position of an item in an array

2
 14 OFFSET
Returns a reference offset from a given reference

15 ROW
Returns the row number of a reference

16 ROWS
Returns the number of rows in a reference

17 RTD
Returns real-time data from a program that supports COM automation

18 TRANSPOSE
Returns the transpose of an array

19 VLOOKUP
Searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify
in the table

Write in detail about Specifying a list of valid values with example.

Or
(b) Discuss about the Sorting tables. (K2)

What Does it Mean to Sort in Excel?

Data is the fundamental basis of spreadsheets, so after you have entered it, you must be able to rearrange it to your liking.
The SORT function is a search and reference function in MS Excel that enables sorting the contents of a range of matrices
based on four arguments:
 Alphabetical: Sort your texts alphabetically, from A to Z or Z to A.
 Numeric: Sorts data in ascending order (smallest to largest) or descending order (largest to smallest). This sorting
type is ideal for organizing quantitative data such as sales, scores, or quantities.
 By date: organize dates from oldest to most recent (ascending order) or vice versa, from most recent to oldest
(descending order). This type of sorting is intuitive for managing histories or schedules.
 Custom: Define your custom sorting criteria, useful for specific cases.
In this article, we will explain some ways or criteria that you can use to order your databases and be able to manage them
effectively.
=SORT(array, sort_index, sort_order, by_col)
Where,
array = Range or array of cells to be sorted.

Stay updated with the latest blogs on online courses and skills
Enter Mobile Number
Register Now
sort_index = Specifies the column or row to sort by (optional argument). If this argument is omitted, the first column is
sorted by default.

3
 sort_order = Optional argument specifying the sort order. The default sort order is ascending, but you can specify
descending by typing “-1”.
by_col = Specifies whether the sort should be performed by columns (TRUE) or by rows (FALSE). The default is by
columns. It is an optional argument.
Why Sorting Data in Excel is Useful?
Sorting data in Excel is a fundamental aspect of data analysis because it allows users to organize and understand their data
quickly. It helps in rearranging data to find values quickly. Here's why sorting data is necessary:
 Improved Data Visualization: Sorting helps to visualize and understand data better. Arranging data in a meaningful
order makes identifying patterns, trends, and outliers easier.
 Efficient Organization and Decision Making: Sorting helps organize and find your desired data. Whether it's
arranging names alphabetically or numbers from highest to lowest, sorting provides a structured view of the
information.
 Effective Decision Making: By organizing data, sorting enables more effective decisions. Organized data allows for
quick comparisons and assessments, leading to better insights.
 Multiple Levels of Sorting: Excel allows sorting by more than one column or category, providing greater control over
data arrangement.
 Quick Reorganization: Sorting data can quickly reorganize content. For example, a list of contact information can be
easily organized by last name.
Sorting Data from the Home Tab
Sorting data in Excel from the “Home” tab involves the sorting options available in the Excel ribbon under the “Home”
tab. This method allows you to sort data without accessing the “Data” tab. Here are the steps to sort data from the “Home”
tab.
 Open the spreadsheet containing the data you want to sort.

 Click on a cell or the cell range within the column you want to sort.
 Go to the “Home” tab in the Excel ribbon. Hover on to the Editing => Sort & Filter

4
 To sort the data in ascending order (A to Z or smallest to largest), click the “Sort A to Z” button (represented by an
“A to Z” icon) to arrange the data in ascending order based on the selected column.

 To sort the data in descending order (Z to A or largest to smallest), click the “Sort Z to A” button (represented by a
“Z to A” icon).

5
  Excel may prompt you to expand the selection if your data has headers. Ensure the “Expand the selection” option is
selected to sort the entire range, including headers. Click “Sort” to confirm your choice.

Sorting Data from the Data Tab

The option of sorting data from the “Data” tab in Excel is available in Excel’s “Sort & Filter” group. The Data tab
involves using the more advanced sorting options. This method gives you greater control over sorting criteria and allows
you to sort data with multiple levels of sorting. Here are the steps to sort data from the “Data” tab:
 Launch Microsoft Excel and open the spreadsheet containing the data you want to sort.

 Click on a cell within the column you want to sort.

 Go to the “Data” tab in the Excel ribbon. Locate and click the “Sort & Filter” option.

 You will get a prompt – Expand the Selection. Click Sort.

 You can select the column in which you want to sort the data. Pick your preference and click OK.

 Since the calorie data is numeric, Excel will ask you to sort the data from Largest to Smallest or Smallest to
Largest. Choose the preferable option.

6
 Date Sorting in Excel
You can also sort the date range using the Sort function. Here is how –
 Select any date of your data set.

 Go to Sort & Filter and select A to Z or Z to A.

 Your dates are sorted accordingly

Sorting by Color
 Go to the header of the data range you want to sort by cell color. Apply Filter.
 Right-click on the filter of your header and click the “Sort by Color” option.
 Choose a column corresponding to the cell color you want to sort by. The column you select should contain the same
background color for the cells you want to sort together.

Key Takeaways
 Excel allows you to sort data in ascending (A to Z, Largest to Smallest) or descending (Z to A, Smallest to Largest)
order.
 You can sort data by single or multiple columns to create more complex sorting criteria.
 Excel offers custom sorting where you can define your own sorting rules.
 Excel understands numeric values and sorts them accordingly, not just alphabetically.
 You can sort dates chronologically, and Excel recognizes date formats.
 Text is sorted alphabetically, and it’s case-insensitive by default.
 You can sort by cell color or icon in conditional formatting.
 Ensure you have headers in your data before sorting to maintain context.
 Pivot tables can be sorted within the pivot table options.
 Sorting data can affect the order in charts, so be aware of this when charting sorted data.
 To avoid unexpected results, format numbers as text if you need to sort them as text.

.
OK

18. (a) Examine the concept Pivot charts.

Excel Easy #1
Excel tutorial
on the netAsk us
Pivot Chart in Excel

A pivot chart is the visual representation of a pivot table in Excel. Pivot charts and pivot tables are connected with each
other.

7
Below you can find a two-dimensional pivot table. Go back to Pivot Tables to learn how to create this pivot table.

Insert Pivot Chart

To insert a pivot chart, execute the following steps.

1. Click any cell inside the pivot table.

2. On the PivotTable Analyze tab, in the Tools group, click PivotChart.

The Insert Chart dialog box appears.

3. Click OK.

Below you can find the pivot chart. This pivot chart will amaze and impress your boss.

8
Note: any changes you make to the pivot chart are immediately reflected in the pivot table and vice versa.
Filter Pivot Chart
To filter this pivot chart, execute the following steps.

1. Use the standard filters (triangles next to Product and Country). For example, use the Country filter to only show the
total amount of each product exported to the United States.

2. Remove the Country filter.

3. Because we added the Category field to the Filters area, we can filter this pivot chart (and pivot table) by Category. For
example, use the Category filter to only show the vegetables exported to each country.

9
 Change Pivot Chart Type
You can change to a different type of pivot chart at any time.

1. Select the chart.

2. On the Design tab, in the Type group, click Change Chart Type.

3. Choose Pie.

4. Click OK.

10
Result:

Note: pie charts always use one data series (in this case, Beans). To get a pivot chart of a country, swap the data over the
axis. First, select the chart. Next, on the Design tab, in the Data group, click Switch Row/Column.

Or
(b) Illustrate the concept of external data sources with Examples.

Managing External Data Connection in Advanced Excel

Last Updated: 12 Mar, 2023
A
Ayush Dey
Follow
External Data Connections are SQL Server database, another workbook of Excel, or any other database that can easily get
connected in Excel. External data will help to add extra features or information to the data model in excel. There is a
refresh button in Excel that will represent the connection with its recent data whether the new data has been inserted or
deleted.
Follow the further steps to add external data connection using advanced excel.
Step 1: Select the Data tab from the ribbon.

Step 2: Select Connections from the Connections section.

11
Step 3: Add new connections by selecting Add in Workbook Connections.

Step 4: Select the external connection which we want to insert from Existing Connections.

12
Step 5: If we want to add more external connections then select Browse for More in the Existing Connections tab.

Step 6: Go back and select the required connection from Workbook Connections.

13
Step 7: The connection Properties tab will appear where we can choose four types of refresh under Refresh Control.
 Enable background refresh: It enables the user to use excel without waiting for several minutes but in the
meantime, we can’t use any query to retrieve the data from Data Model.
 Refresh every: This will refresh the data automatically after the selected time duration.
 Refresh data when opening the file: This will refresh the file when the file will be opened so that the file will be
updated according to the insertion or deletion of the data.
 Refresh this connection on Refresh All: The connection will update itself every time when Refresh All is selected
from the Data tab as shown below.

Step 8: You can also refresh under the data tab then in the connections group select Refresh All.

14
10m
21. Describe in details about Nested VlookUP with Exact Match. (K2)

Tip: Try using the new XLOOKUP and XMATCH functions, improved versions of the functions described in this article.
These new functions work in any direction and return exact matches by default, making them easier and more convenient
to use than their predecessors.

Suppose that you have a list of office location numbers, and you need to know which employees are in each office. The
spreadsheet is huge, so you might think it is challenging task. It's actually quite easy to do with a lookup function.

The VLOOKUP and HLOOKUP functions, together with INDEX and MATCH, are some of the most useful functions in
Excel.

Note: The Lookup Wizard feature is no longer available in Excel.

Here's an example of how to use VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

In this example, B2 is the first argument—an element of data that the function needs to work. For VLOOKUP, this first
argument is the value that you want to find. This argument can be a cell reference, or a fixed value such as "smith" or
21,000. The second argument is the range of cells, C2-:E7, in which to search for the value you want to find. The third
argument is the column in that range of cells that contains the value that you seek.

The fourth argument is optional. Enter either TRUE or FALSE. If you enter TRUE, or leave the argument blank, the
function returns an approximate match of the value you specify in the first argument. If you enter FALSE, the function
will match the value provide by the first argument. In other words, leaving the fourth argument blank—or entering TRUE
—gives you more flexibility.

This example shows you how the function works. When you enter a value in cell B2 (the first argument), VLOOKUP
searches the cells in the range C2:E7 (2nd argument) and returns the closest approximate match from the third column in
the range, column E (3rd argument).

15
The fourth argument is empty, so the function returns an approximate match. If it didn't, you'd have to enter one of the
values in columns C or D to get a result at all.

When you're comfortable with VLOOKUP, the HLOOKUP function is equally easy to use. You enter the same
arguments, but it searches in rows instead of columns.

Using INDEX and MATCH instead of VLOOKUP

There are certain limitations with using VLOOKUP—the VLOOKUP function can only look up a value from left to right.
This means that the column containing the value you look up should always be located to the left of the column containing
the return value. Now if your spreadsheet isn't built this way, then do not use VLOOKUP. Use the combination of INDEX
and MATCH functions instead.

This example shows a small list where the value we want to search on, Chicago, isn't in the leftmost column. So, we can't
use VLOOKUP. Instead, we'll use the MATCH function to find Chicago in the range B1:B11. It's found in row 4. Then,
INDEX uses that value as the lookup argument, and finds the population for Chicago in the 4th column (column D). The
formula used is shown in cell A14.

VLOOKUP Example at work

Copy the following data into a blank spreadsheet.

Tip: Before you paste the data into Excel, set the column widths for columns A through C to 250 pixels, and click Wrap
Text (Home tab, Alignment group).
Density Viscosity Temperatu
0.457 3.55 500
0.525 3.25 400
0.606 2.93 300
0.675 2.75 250
0.746 2.57 200
0.835 2.38 150
0.946 2.17 100
1.09 1.95 50

16
Density Viscosity Temperatu
1.29 1.71 0
Formula Description Result
=VLOOKUP(1,A2:C10,2) Using an approximate match, searches for the value 1 in column A, 2.17
finds the largest value less than or equal to 1 in column A which is
0.946, and then returns the value from column B in the same row.
=VLOOKUP(1,A2:C10,3,TRUE) Using an approximate match, searches for the value 1 in column A, 100
finds the largest value less than or equal to 1 in column A, which is
0.946, and then returns the value from column C in the same row.
=VLOOKUP(0.7,A2:C10,3,FALSE) Using an exact match, searches for the value 0.7 in column A. #N/A
Because there is no exact match in column A, an error is returned.
=VLOOKUP(0.1,A2:C10,2,TRUE) Using an approximate match, searches for the value 0.1 in column A. #N/A
Because 0.1 is less than the smallest value in column A, an error is
returned.
=VLOOKUP(2,A2:C10,2,TRUE) Using an approximate match, searches for the value 2 in column A, 1.71
finds the largest value less than or equal to 2 in column A, which is
1.29, and then returns the value from column B in the same row.

17