Microsoft Excel PSC Exam

on


Introduction : Microsoft Excel PSC Exam

Set 1: Excel Basics

  1. Question: What is Excel primarily used for? Answer: Excel is primarily used for creating and managing spreadsheets, performing calculations, and analyzing data.
  2. Question: Which part of an Excel worksheet displays the letter and number coordinates for each cell? Answer: The column headers and row numbers display the coordinates for each cell in Excel.
  3. Question: What is the maximum number of rows in an Excel worksheet? Answer: Excel 2016 and later versions support up to 1,048,576 rows.
  4. Question: Which function is used to add up a range of cells in Excel? Answer: The SUM function is used to add up a range of cells in Excel.
  5. Question: What keyboard shortcut is commonly used to save an Excel workbook? Answer: The keyboard shortcut “Ctrl + S” is used to save an Excel workbook.
  6. Question: In Excel formulas, what symbol is used to denote multiplication? Answer: The asterisk (*) symbol is used to denote multiplication in Excel formulas.
  7. Question: What is the default file extension for Excel workbooks? Answer: The default file extension for Excel workbooks is “.xlsx”.
  8. Question: How can you freeze panes in Excel to keep certain rows and columns visible while scrolling? Answer: You can freeze panes by selecting the cell where you want to split, and then going to “View” > “Freeze Panes”.
  9. Question: What is the purpose of the “AutoFill” feature in Excel? Answer: The “AutoFill” feature automatically fills cells with a series of values or patterns based on the initial selection.
  10. Question: Which Excel feature allows you to visually represent data using graphical elements such as bars and columns? Answer: Excel’s “Charts” feature allows you to visually represent data using graphical elements.

Set 2: Excel Functions and Formulas (Microsoft Excel PSC Exam)

  1. Question: What function is used to find the highest value in a range of cells in Excel? Answer: The MAX function is used to find the highest value in a range of cells in Excel.
  2. Question: Which Excel function is used to count the number of cells that meet specific criteria? Answer: The COUNTIF function is used to count cells that meet specific criteria in Excel.
  3. Question: How would you write a formula to multiply the values in cells A1 and B1? Answer: The formula would be “=A1*B1”.
  4. Question: What does the VLOOKUP function do in Excel? Answer: The VLOOKUP function is used to search for a value in the first column of a range and return a corresponding value from a different column.
  5. Question: How can you make a cell reference in a formula absolute (not change when copied)? Answer: You can make a cell reference absolute by adding a dollar sign ($) before the column and row references (e.g., $A$1).
  6. Question: What function calculates the average of a range of numbers in Excel? Answer: The AVERAGE function calculates the average of a range of numbers in Excel.
  7. Question: In Excel, what is the function of the CONCATENATE function? Answer: The CONCATENATE function is used to combine multiple text strings into one string.
  8. Question: What is the result of the formula “=10% * 150” in Excel? Answer: The result is 15, which is 10% of 150.
  9. Question: Which function can be used to round a number up to the nearest integer in Excel? Answer: The CEILING function can be used to round a number up to the nearest integer.
  10. Question: How do you nest functions in an Excel formula? Answer: You can nest functions by using the output of one function as an argument for another function within a formula.

Set 3: Excel Formatting and Data Analysis (Microsoft Excel PSC Exam)

  1. Question: What is conditional formatting in Excel used for? Answer: Conditional formatting in Excel is used to highlight cells based on specific conditions or rules.
  2. Question: How can you add a border around a cell in Excel? Answer: You can add a border around a cell by selecting the cell and then going to the “Home” tab > “Font” group > “Borders” dropdown.
  3. Question: Which Excel feature allows you to group and summarize large sets of data? Answer: PivotTables allow you to group and summarize large sets of data in Excel.
  4. Question: What is the purpose of the “Sort” feature in Excel? Answer: The “Sort” feature is used to arrange data in ascending or descending order based on selected criteria.
  5. Question: How can you apply a filter to a column in Excel? Answer: You can apply a filter to a column by selecting the column header and clicking on the “Filter” button on the “Data” tab.
  6. Question: In Excel, what is a “Data Validation” rule used for? Answer: Data Validation rules are used to restrict the type of data that can be entered into a cell.
  7. Question: What is the purpose of the “Wrap Text” formatting option in Excel? Answer: The “Wrap Text” option is used to display long text in a cell by wrapping it within the cell boundaries.
  8. Question: How can you apply a currency symbol to a range of cells in Excel? Answer: You can apply a currency symbol by selecting the range, going to the “Home” tab > “Number” group, and choosing the currency format.
  9. Question: Which Excel feature can be used to remove duplicate values from a column? Answer: The “Remove Duplicates” feature can be used to remove duplicate values from a column in Excel.
  10. Question: What is the purpose of the “Subtotal” function in Excel? Answer: The SUBTOTAL function is used to calculate subtotals within a range and can be used alongside functions like SUM, AVERAGE, etc.

Set 4: Advanced Excel Functions (Microsoft Excel PSC Exam)

  1. Question: How would you write a formula to find the length of text in cell A1? Answer: The formula would be “=LEN(A1)”.
  2. Question: What is the purpose of the CONCAT function in Excel? Answer: The CONCAT function is used to concatenate multiple text strings in Excel.
  3. Question: Which function in Excel is used to calculate the square root of a number? Answer: The SQRT function is used to calculate the square root of a number in Excel.
  4. Question: How can you round a number to a specified number of decimal places using a formula? Answer: You can use the ROUND function to round a number to a specified number of decimal places in Excel.
  5. Question: What is the IFERROR function used for in Excel? Answer: The IFERROR function is used to handle errors in formulas by returning a specified value if an error occurs.
  6. Question: How would you write a formula to extract a specific number of characters from a text string in Excel? Answer: The formula would be “=LEFT(A1, n)” where “n” is the number of characters you want to extract from cell A1.
  7. Question: What is the purpose of the INDEX-MATCH function combination in Excel? Answer: The INDEX-MATCH combination is used to look up a value in a table by matching criteria in another column.
  8. Question: What does the COUNTA function do in Excel? Answer: The COUNTA function counts the number of non-empty cells in a range.
  9. Question: How can you convert a number into text format with a specific number format in Excel? Answer: You can use the TEXT function to convert a number into text format with a specific number format.
  10. Question: What is the purpose of the TRANSPOSE function in Excel? Answer: The TRANSPOSE function is used to change the orientation of a range, converting rows to columns and vice versa.

Set 5: Excel Keyboard Shortcuts and Tips (Microsoft Excel PSC Exam)

  1. Question: What is the keyboard shortcut to open the “Find and Replace” dialog box in Excel? Answer: The keyboard shortcut is “Ctrl + H”.
  2. Question: How can you select an entire column in Excel using the keyboard? Answer: To select an entire column, press “Ctrl + Spacebar”.
  3. Question: What is the keyboard shortcut to undo the last action in Excel? Answer: The keyboard shortcut is “Ctrl + Z”.
  4. Question: How can you quickly autosum a column of numbers using the keyboard? Answer: Select the cell below the column of numbers and press “Alt + =” (equals sign).
  5. Question: What is the keyboard shortcut to copy the contents of a cell in Excel? Answer: The keyboard shortcut is “Ctrl + C”.
  6. Question: How can you navigate to the last cell in a worksheet using the keyboard? Answer: Press “Ctrl + End” to navigate to the last cell in a worksheet.
  7. Question: What is the keyboard shortcut to apply bold formatting to selected text in a cell? Answer: The keyboard shortcut is “Ctrl + B”.
  8. Question: How can you insert the current date in a cell using the keyboard? Answer: Press “Ctrl + ;” (semicolon) to insert the current date.
  9. Question: What is the keyboard shortcut to paste copied content in Excel? Answer: The keyboard shortcut is “Ctrl + V”.
  10. Question: How can you quickly create a new worksheet in Excel using the keyboard? Answer: Press “Shift + F11” to create a new worksheet.

Set 6: Excel Advanced Features (Microsoft Excel PSC Exam)

  1. Question: What is the purpose of the “Goal Seek” feature in Excel? Answer: The “Goal Seek” feature is used to find the input value needed to achieve a specific result in a formula.
  2. Question: How can you create a drop-down list in Excel for data validation? Answer: You can create a drop-down list by using the “Data Validation” feature and selecting “List” as the validation criteria.
  3. Question: What does the function CONCATENATEX do in Excel’s Power Query? Answer: CONCATENATEX is a function in Power Query that combines values from multiple rows into a single text string.
  4. Question: In Excel’s Power Pivot, what is a “Data Model”? Answer: A Data Model is a collection of tables and relationships created using Power Pivot to analyze data from multiple sources.
  5. Question: How can you create a slicer in an Excel PivotTable? Answer: You can create a slicer by selecting a cell in the PivotTable and then going to the “Insert” tab > “Slicer.”
  6. Question: What is the purpose of the “Flash Fill” feature in Excel? Answer: The “Flash Fill” feature automatically fills in values based on patterns it recognizes in adjacent cells.
  7. Question: What are Sparklines in Excel used for? Answer: Sparklines are small, data-rich charts that provide a visual representation of data trends within a single cell.
  8. Question: What is the “Data Validation” feature used for in Excel? Answer: The “Data Validation” feature is used to control what type of data can be entered into a cell.
  9. Question: How can you protect a worksheet or workbook in Excel with a password? Answer: You can protect a worksheet or workbook by going to the “Review” tab > “Protect Sheet” or “Protect Workbook” options.
  10. Question: What does the “PivotChart” feature in Excel do? Answer: The “PivotChart” feature allows you to create a chart directly from a PivotTable to visually represent data.

Set 7: Excel Macros and Automation (Microsoft Excel PSC Exam)

  1. Question: What is a macro in Excel? Answer: A macro in Excel is a series of recorded actions that can be replayed to automate tasks.
  2. Question: How can you record a macro in Excel? Answer: You can record a macro by going to the “View” tab > “Macros” dropdown > “Record Macro.”
  3. Question: What is the VBA (Visual Basic for Applications) Editor used for in Excel? Answer: The VBA Editor is used to write, edit, and debug custom macros and functions in Excel.
  4. Question: How can you assign a macro to a button in Excel? Answer: You can assign a macro to a button by going to the “View” tab > “Macros” dropdown > “View Macros,” selecting the macro, and clicking “Options.”
  5. Question: What is the purpose of the “Relative Reference” option when recording a macro? Answer: The “Relative Reference” option allows the macro to record actions based on relative positions, making it reusable for different cells.
  6. Question: How can you run a macro in Excel? Answer: You can run a macro by going to the “View” tab > “Macros” dropdown > “View Macros,” selecting the macro, and clicking “Run.”
  7. Question: What is the “Personal Macro Workbook” in Excel? Answer: The Personal Macro Workbook is a hidden workbook that can store macros you want available in all workbooks.
  8. Question: How can you open the VBA Editor in Excel? Answer: You can open the VBA Editor by pressing “Alt + F11” or going to the “Developer” tab > “Visual Basic.”
  9. Question: What is the purpose of the “Application.ScreenUpdating” property in VBA? Answer: The “Application.ScreenUpdating” property can be set to “False” to prevent screen flickering during macro execution.
  10. Question: How can you edit a macro’s code in the VBA Editor? Answer: You can double-click on a macro in the VBA Editor to access and edit its code.

Set 8: Excel Data Analysis and Power Query (Microsoft Excel PSC Exam)

  1. Question: What is Power Query in Excel used for? Answer: Power Query is used to connect, transform, and load data from various sources into Excel.
  2. Question: What is the purpose of the “Group By” operation in Power Query? Answer: The “Group By” operation in Power Query is used to group data based on selected columns and perform aggregate calculations.
  3. Question: How can you merge data from multiple Excel workbooks using Power Query? Answer: You can use the “Combine Files” option in Power Query to merge data from multiple Excel workbooks.
  4. Question: What is the “Append Queries” operation in Power Query used for? Answer: The “Append Queries” operation combines multiple queries vertically to create a single table.
  5. Question: How can you remove duplicate rows from a table using Power Query? Answer: You can use the “Remove Duplicates” option in Power Query to eliminate duplicate rows from a table.
  6. Question: What is the purpose of the “Unpivot Columns” operation in Power Query? Answer: The “Unpivot Columns” operation transforms wide data into a more compact, normalized form by converting column headers into attribute-value pairs.
  7. Question: How can you split a column into multiple columns based on a delimiter using Power Query? Answer: You can use the “Split Column” operation in Power Query to divide a column into multiple columns based on a specified delimiter.
  8. Question: What is the “M” language used for in Power Query? Answer: The “M” language is used to create custom transformations and queries in Power Query.
  9. Question: How can you load data into Excel from an external source using Power Query? Answer: You can load data by going to the “Data” tab > “Get Data” > selecting a data source > configuring the query using Power Query.
  10. Question: What is the purpose of the “Home” step in Power Query? Answer: The “Home” step indicates the initial state of the data before any transformations are applied in Power Query.

Set 9: Excel Data Visualization and Charts

  1. Question: How can you create a pie chart in Excel? Answer: You can create a pie chart by selecting data, going to the “Insert” tab > “Pie Chart,” and choosing a suitable style.
  2. Question: What is the purpose of a scatter plot in Excel? Answer: A scatter plot is used to visualize the relationship between two variables as points on a graph.
  3. Question: How can you add data labels to a chart in Excel? Answer: You can add data labels by right-clicking on the chart elements, selecting “Add Data Labels,” and customizing their appearance.
  4. Question: What is a trendline in Excel used for in charts? Answer: A trendline is used to show the general direction of data points in a chart, indicating trends and patterns.
  5. Question: How can you create a combination chart with multiple chart types in Excel? Answer: You can create a combination chart by selecting the data, going to the “Insert” tab > “Combo Chart,” and choosing the desired chart types.
  6. Question: What is the purpose of a bar chart in Excel? Answer: A bar chart is used to compare different categories or data points using horizontal or vertical bars.
  7. Question: How can you add a secondary vertical axis to a chart in Excel? Answer: You can add a secondary vertical axis by right-clicking on the data series, selecting “Format Data Series,” and enabling the secondary axis option.
  8. Question: What is the purpose of a waterfall chart in Excel? Answer: A waterfall chart is used to visualize incremental changes in values over a series of categories, showing the cumulative effect.
  9. Question: How can you create a 3D column chart in Excel? Answer: You can create a 3D column chart by selecting data, going to the “Insert” tab > “3-D Column,” and choosing a style.
  10. Question: What is the purpose of the “Chart Title” in Excel? Answer: The “Chart Title” is used to provide a clear description of the chart’s content, aiding in understanding the data.

Set 10: Excel Tips and Tricks

  1. Question: How can you quickly duplicate a worksheet in Excel? Answer: You can right-click on a worksheet tab and select “Move or Copy,” then choose “Create a copy” and select the target location.
  2. Question: How can you hide a worksheet in Excel? Answer: You can right-click on a worksheet tab and choose “Hide.”
  3. Question: How can you view and manage comments added to cells in Excel? Answer: You can view and manage comments by right-clicking on a cell and selecting “Show/Hide Comments.”
  4. Question: How can you view all formulas in an Excel worksheet at once? Answer: You can press “Ctrl + ` ” (grave accent) to toggle between displaying formulas and their results.
  5. Question: What is the purpose of the “Watch Window” in Excel? Answer: The “Watch Window” allows you to monitor the values of specific cells or named ranges while working in other parts of the workbook.
  6. Question: How can you split the Excel window to view two different parts of a worksheet simultaneously? Answer: You can go to the “View” tab > “New Window” and then arrange the windows using the “Arrange All” option.
  7. Question: How can you quickly fill a series of dates or numbers in Excel? Answer: You can enter the first value, select it, and then use the “Fill Handle” (a small square at the bottom-right corner) to drag and fill the series.
  8. Question: How can you convert a range of cells into a table in Excel? Answer: You can select the range and go to the “Insert” tab > “Table.”
  9. Question: How can you add hyperlinks to cells in Excel? Answer: You can select a cell, right-click, and choose “Hyperlink,” then specify the address or location.
  10. Question: How can you quickly navigate to the last cell in a column or row in Excel? Answer: You can press “Ctrl + Arrow key” (up, down, left, or right) to navigate to the last filled cell in that direction.

Comments

Post a Comment