Top 10 Excel Interviews Questions – How to pass any Excel Assessment Test | Summary and Q&A

181.3K views
July 29, 2022
by
M Tech Training
YouTube video player
Top 10 Excel Interviews Questions – How to pass any Excel Assessment Test

TL;DR

Learn essential Excel functions for interviews, including conditional formatting, absolute and relative cell references, spell checking, removing duplicates, table formatting, commonly used charts, different views, functions, VLOOKUP, and pivot tables.

Install to Summarize YouTube Videos and Get Transcripts

Key Insights

  • 🔍 Conditional formatting can be used to highlight cells based on rules, such as marking scores above or below a certain threshold or highlighting profits above the average. It can be found in the Home tab under the Style section. Clearing formatting rules is also possible in the Conditional Formatting menu.
  • ➗ Absolute cell references should be used when calculating the difference between each mark and the average, as the average value remains constant. Relative references will change when the formula is applied to other cells.
  • ✏️ Spell checking in Excel is simple, just go to the Review tab and select Spelling. It will show mistakes and offer suggestions for replacements. Click on Change to correct the errors.
  • 🔀 Removing duplicated data is important for data analysis. In Excel, go to the Data tab, Data Tools section, and click on Remove Duplicate. This will remove any duplicate values.
  • 🖼️ Adding borders to tables can be done by selecting the table and using the border options in the Home tab and Fonts section. Cells can also be highlighted using the highlight tool.
  • 📊 Line, column, and pie charts are some of the most frequently used charts in Excel. Line charts are useful for showing multiple data sets, while column charts are effective for comparing data. Pie charts are best for displaying proportions of a single data set.
  • 👀 Different views in Excel allow you to preview and edit your document. Page Break Preview shows page breaks and allows you to move them. Page Layout view shows how the printed document will look and allows you to edit headers and footers.
  • 📚 Using functions in Excel can help automate tasks. For example, the IF function can be used to determine whether a student has passed or failed based on their score, and the TEXT function is used to write text within quotations.
  • ️ The VLOOKUP function is a powerful tool for searching and retrieving data from a table. It requires the value to search, the table range, the column number to return, and can use either an approximate or exact match.
  • 🔢 Pivot tables are a useful feature for data analysis and summarization. They can be created from the Insert tab and allow for easy manipulation of data by specifying columns and rows, changing value settings, and displaying data in different formats, such as percentages of grand total.

Transcript

Read and summarize the transcript of this video on Glasp Reader (beta).

Questions & Answers

Q: How can I highlight specific cells in Excel based on certain rules?

In Excel, you can utilize conditional formatting to highlight cells that meet certain criteria. For example, you can highlight scores above 60 in green and scores below 60 in red. To do this, go to the Home tab, select Conditional Formatting, and choose the appropriate rule to apply the formatting.

Q: What is the difference between absolute and relative cell references in Excel?

Absolute cell references remain constant when a formula is copied to other cells, while relative cell references change. This is important when performing calculations that require a constant value, such as an average. To indicate an absolute cell reference, simply add a dollar sign before the column and/or row reference in the formula.

Q: How can I remove duplicate data in an Excel sheet?

To remove duplicate data in Excel, go to the Data tab, select the Data Tools section, and click on Remove Duplicates. Excel will analyze your data and remove any duplicate values or rows, keeping only the unique entries.

Q: What are some commonly used chart types in Excel?

Line charts are often used to show trends over time, while column charts are useful for comparing data sets. Pie charts are suitable for displaying proportions of a whole. These charts can be accessed through the Insert tab, and you can customize them to fit your data.

Q: How can I use pivot tables to analyze data in Excel?

Pivot tables are a valuable feature in Excel for analyzing large data sets. To create a pivot table, select the data range and go to the Insert tab. Choose PivotTable and set the desired columns and rows. You can then manipulate the values and apply different calculations, using functions like sum, count, or average, to gain insights from your data.

Summary & Key Takeaways

  • Conditional formatting can be used to highlight cells based on specific rules, such as marking scores above 60 with green and below 60 with red.

  • Understanding the difference between absolute and relative cell references is crucial for accurate calculations in Excel.

  • Spell checking, removing duplicate data, applying borders to tables, and using various chart types are fundamental skills in Excel.

  • Utilizing different views, such as page break preview and page layout, can help with printing and formatting Excel sheets.

  • Learn how to use functions like IF statements and VLOOKUP for data analysis and manipulation.

  • Pivot tables are a powerful tool for organizing and analyzing large data sets in Excel.

Share This Summary 📚

Summarize YouTube Videos and Get Video Transcripts with 1-Click

Download browser extensions on: