BTechibtechi.in
Loading Loading
Free IIT Madras BS notes, PYQs and mock tests at btechi.in

BDM · Excel tools & charts

15 questions · 18 marks · 5 papers

Answer key

Indian Institute of Technology Madras

BS Degree Programme · Examination & Study Paper

Business Data Management

Excel tools & charts

Official Paper

Examination Instructions & Guidelines

Solve on notebook/paper first before checking solutions.
MCQ: Exactly 1 option is correct. Negative marking applies.
MSQ: One or more options correct. Select all to score.
Scientific calculator allowed for all calculations.
Official answer key is sealed at the end of this sheet.

End Term · 31 Aug 2025 (FN)End Term

Q1MCQ1 Mark·Choose one option
Asked 3 times Key ↓
Which Excel function would you use to find a value in the first column of a table and return a value from a specified column?
1. AINDEX
2. BMATCH
3. CVLOOKUP
4. DHLOOKUP
Q2MCQ1 Mark·Choose one option
Asked 3 times Key ↓
Which chart type is most appropriate for showing the relationship between two continuous variables?
1. ABar chart
2. BPie chart
3. CScatter plot
4. DHistogram
Q3MCQ1 Mark·Choose one option
Asked 3 times Key ↓
Which Excel function would you use to calculate the average of a filtered range, ignoring hidden rows?
1. AAVERAGE
2. BSUBTOTAL
3. CAGGREGATE
4. DFILTER
Q4MSQ2 Marks·Choose all correct options
Asked 3 times Key ↓
Which of the following is NOT a typical function of pivot tables?
1. ASummarizing large datasets
2. BCreating new records in source data
3. CGrouping data by categories
4. DCalculating aggregated values

End Term · 31 Aug 2025 (AN)End Term

Q5MCQ1 Mark·Choose one option
Asked 3 times Key ↓
Which Excel function would you use to find a value in the first column of a table and return a value from a specified column?
1. AINDEX
2. BMATCH
3. CVLOOKUP
4. DHLOOKUP
Q6MCQ2 Marks·Choose one option
Key ↓
For the given data, which formula correctly calculates total sales for 'Electronics' category sold by 'John'? Data Range A2:C10 contains: Column A: Salesperson names Column B: Category Column C: Sales amount
1. A=SUMIFS(C2:C10,A2:A10,"John",B2:B10,"Electronics")
2. B=SUMIF(A2:A10,"John",C2:C10)+SUMIF(B2:B10,"Electronics",C2:C10)
3. C=VLOOKUP("John",A2:C10,3,FALSE)
4. D=COUNTIFS(A2:A10,"John",B2:B10,"Electronics")

Quiz 2 · 3 Aug 2025 (AN)Quiz 2

Q7MCQ1 Mark·Choose one option
Key ↓
1. AThe reference to cell B1 will adjust when the formula is copied to other cells.
2. BThe reference to cell B1 is absolute and will not change when the formula is copied to other cells.
3. CThe reference to cell A1 is absolute and will not change when the formula is copied to other cells.
4. DThe reference to cell A1 is absolute, but reference to B1 is relative.
Q8MCQ1 Mark·Choose one option
Asked 3 times Key ↓
In Excel, the SUBTOTAL function is used to perform calculations on a filtered range of data. Which of the following statements about the SUBTOTAL function is correct?
1. ASUBTOTAL can only calculate the sum of a range and ignores hidden rows in the filtered data.
2. BSUBTOTAL can perform various calculations, including sum, average, count, and more, and it ignores rows hidden by filters.
3. CSUBTOTAL performs calculations only on rows that are manually hidden, not those hidden by filters.
4. DSUBTOTAL can calculate results based on data in any worksheet, regardless of whether the data is visible or hidden.
Q9MCQ1 Mark·Choose one option
Asked 3 times Key ↓
Which of the following is typically NOT performed when using a pivot table in data analysis ?
1. AFiltering data to display only specific records.
2. BCreating new records in the original dataset.
3. CGrouping data by categories or fields to analyze trends and patterns.
4. DSummarizing data by aggregating values, such as sum, count, or average.
Q10MCQ1 Mark·Choose one option
Asked 3 times Key ↓
Which chart can be used to represent the intensity or size of a data ?
1. AScatter Plot
2. BBubble Chart
3. CHistogram
4. DBox Plot

End Term · 13 Apr 2025 (FN)End Term

Q11MCQ1 Mark·Choose one option
Key ↓
Which function would you use to count the number of cells that meet a specific condition?
1. ACOUNT
2. BCOUNTA
3. CCOUNTIF
4. DCOUNTSUM
Q12MSQ2 Marks·Choose all correct options
Asked 3 times Key ↓
In Excel, the SUBTOTAL function is used to perform calculations on a filtered range of data. Which of the following statements about the SUBTOTAL function is correct?
1. ASUBTOTAL can only calculate the sum of a range and ignores hidden rows in the filtered data.
2. BSUBTOTAL can perform various calculations, including sum, average, count, and more, and it ignores rows hidden by filters.
3. CSUBTOTAL performs calculations only on rows that are manually hidden, not those hidden by filters.
4. DSUBTOTAL can calculate results based on data in any worksheet, regardless of whether the data is visible or hidden.

End Term · 22 Dec 2024 (AN)End Term

Q13MCQ1 Mark·Choose one option
Asked 3 times Key ↓
Which Excel function can be used to search for a certain value from another table and retrieve a reference value based on the original table data?
1. ASUM
2. BIF
3. CLINEST
4. DVLOOKUP
Q14MCQ1 Mark·Choose one option
Asked 3 times Key ↓
Which of the following scenarios can be best expressed through a Bar chart?
1. AComparing expenditure trends of students.
2. BComparing no. of scooter owners, no of bike owners vis- a- vis no of car owners.
3. CShowing relationship between temperature and time of the day.
4. DAnalyzing correlation between no. of hours students study and their scores in exam
Q15MCQ1 Mark·Choose one option
Asked 3 times Key ↓
Which of the following is typically NOT performed when using a pivot table in data analysis ?
1. AFiltering data to display only specific records.
2. BCreating new records in the original dataset.
3. CGrouping data by categories or fields to analyze trends and patterns.
4. DSummarizing data by aggregating values, such as sum, count, or average.

Official Key

Master Answer Key & Explanations

Official answer keys from IIT Madras BS past papers. Click any question to review.

Reveal Key

Q1C

Q2C

Q3B

Q4B

Q5C

Q6A

Q7B

Q8B

Q9B

Q10B

Q11C

Q12A, B

Q13D

Q14B

Q15B

Questions and answer keys are reproduced from the official IIT Madras BS papers. Topics and repeated question types are BTechi’s own grouping. BTechi is not affiliated with IIT Madras.