COPA Module 12: Advance Excel & Power BI – all 25 MCQ questions with answers

  1. Where would you find the option to insert a Pivot Table?

    Answer: Under the insert tab in the tables group – PivotTable is inserted from Insert tab → Tables group → PivotTable.

  2. Which format will display the value 32,500,000 as 32.5?

    Answer: ###.0, – A comma at the end of a number format scales the value down by 1,000 per comma, so large numbers like 32,500,000 can be shown in short form (32.5).

  3. On which application the principal arrival of PowerBI based on?

    Answer: Microsoft excel – Power BI grew out of Excel add-ins (Power Pivot, Power Query, Power View).

  4. Which shows the address of the current selection or active cell in MS Excel?

    Answer: Name Box – The Name Box (left of the formula bar) shows the active cell address, e.g. B3.

  5. Which date is created by applying the custom date format of dd/mm/yyyy?

    Answer: 28/07/2022 – dd/mm/yyyy shows 2-digit day, 2-digit month and 4-digit year: 28/07/2022.

  6. How many types of conditions are available in conditional formatting of Excel?

    Answer: Five – Conditional formatting has five types: Highlight Cells, Top/Bottom, Data Bars, Color Scales, Icon Sets.

  7. What is the first step for creating a PivotTable?

    Answer: Create data that needs to be analyzed – First prepare the source data with proper headings; then insert the PivotTable.

  8. What is the correct formula for shown in the figure?

    Answer: =HLOOKUP(A4,A1:F1,2,FALSE) – Data is in rows, so HLOOKUP searches row 1 and returns row 2; FALSE means exact match.

  9. Where will you find options to add a border around the outside of your chart?

    Answer: Under the Format tab in the Shape Styles group – Select the chart → Format tab → Shape Styles → Shape Outline adds a border.

  10. How macro can be restricted from running automatically while starting MS Excel?

    Answer: Hold down the SHIFT key during startup – Holding SHIFT while opening a workbook stops auto-run macros.

  11. Which function returns a value within a table?

    Answer: Index – INDEX(range, row, column) returns the value at that position in a table.

  12. How can we correspond to the category labels in a pie chart and series name in other chart?

    Answer: Legend – The legend identifies categories (pie chart) and series names (other charts).

  13. Which function referencing a cell to match values in another row or column against the cell?

    Answer: Lookup – LOOKUP searches a value in one row/column and returns the matching value from another.

  14. Which type of chart is suitable for single series of data?

    Answer: Column Chart – A column chart compares values of one data series clearly using vertical bars.

  15. Which dialog box allows you to change a field name in a PivotTable?

    Answer: Field settings – Value/Field Settings lets you rename a PivotTable field and change its summary.

  16. What contents will appeared in those cells, if you drag the fill handle of cell B2 downward into cell B3, B4 and B5?

    Answer: Mon, Tue, Wed – Excel's AutoFill knows day names, so dragging "Sun" gives Mon, Tue, Wed.

  17. Which conditional formatting option shows the Value in bar chart?

    Answer: Data Bar – Data Bars draw a bar inside each cell whose length shows the value.

  18. What is the data limit for free Power BI adoption?

    Answer: 1GB/client – The free Power BI service allows 1 GB of data storage per user.

  19. How will you change 25-09-2022 00.00.00 Format to 25-09-2022 format in power query shown in the figure?

    Answer: Date – Change Type → Date removes the time part and keeps only the date.

  20. Which is required to comment out a line of code in the Power Query Advanced Editor?

    Answer: Use // – In Power Query (M language) // starts a single-line comment.

  21. Which tab has number format option in Excel?

    Answer: Home Tab – The Number group (Currency, %, Date…) is on the Home tab.

  22. Which is a box in the PivotTable Fields List?

    Answer: Column Labels – Column Labels is one of the four areas of the PivotTable Fields list.

  23. Which ribbon tab has Conditional Formatting option?

    Answer: Home Tab – Conditional Formatting is in the Styles group of the Home tab.

  24. Which is a type of charts?

    Answer: Bubble – Bubble is a real Excel chart type; circle, land and square are not.

  25. How the result will be C2 in the formula =if(A1=B1, C1, C2) when?

    Answer: A1 does not equal to B1 – IF returns C1 when A1=B1 is TRUE and C2 when it is FALSE (A1 ≠ B1).