MS Excel Pivot Table Quiz 18

Test your Excel Pivot Table skills with this 20-question MCQ quiz! This MS Excel Pivot Table Quiz is perfect for students, statisticians, data analysts, and data scientists preparing for exams or job interviews. Master Excel Pivot Table concepts like grouping, filtering, calculations, and custom lists to boost your data analysis efficiency. Excel Pivot Table Quiz, PivotTable MCQ, data analysis test, Excel interview questions, PivotTable exam prep. Let us start MS Excel Pivot Table Quiz now.

Online MS Excel Pivot Table Quiz with Answers

1. Converting your data into a Table before creating a PivotTable is a good idea because:

 
 
 

2. If you want to filter by a field that you do not necessarily want to show in the PivotTable itself, you should move this field to which quadrant?

 
 
 
 

3. What would moving the Europe cell to elsewhere in the PivotTable do?
MS Excel Pivot Table Quiz

 
 
 

4. Which of the following buttons would we need to click to group a few rows from our PivotTable?
MS Excel PivotTable Quiz

 
 
 
 
 

5. To change the calculation within your PivotTable, you get more options by right-clicking one of the values, compared to customizing the calculation in the Values quadrant.

 
 

6. The main difference between Label Filters and Value Filters is that Label Filters are for filtering based on text.

 
 

7. Which of these are correct statements about custom lists in Excel?

 
 
 
 

8. To clear a filter in a PivotTable, you could:

 
 
 
 

9. You can choose to display a sub-total as a sum and as an average, and both these items will appear separately at the bottom of the respective section in your PivotTable.

 
 

10. Before you create a PivotTable, you need to ensure that you have nice clean data with

 
 
 

11. If you have full dates and you create a PivotTable, Excel will group these dates, such as grouping by month. You can remove this by choosing Ungroup.

 
 

12. Rather than seeing discrete values, if you want to see percentages instead, you should go to

 
 
 

13. When does Excel automatically give you sub-totals for your PivotTable?

 
 
 
 

14. A PivotTable currently occupies some of Columns A and B as per the screenshot. The formula that is displayed is typed into cell D4. What would happen to the result of the formula if the filters of the PivotTable were modified?
MS Excel Pivot Table Quiz 4

 
 
 

15. One way to filter which columns and which rows appear in your PivotTable is to use the drop-down menu that appears next to Row Labels and Column Labels.

 
 

16. If you choose Show Report Filter Pages after having selected multiple items in a filter, what would happen?

 
 
 
 

17. When in PivotTable Fields, if you tick a field that contains non-numeric data, it will automatically populate which quadrant?

 
 
 
 

18. Choosing the option Rank Smallest to Largest will result in the PivotTable being sorted and displayed from smallest to largest values.

 
 

19. Which of the following buttons would we need to click to group the dates below?
MS Excel PivotTable

 
 
 
 
 

20. When in PivotTable Fields, if you tick a field that contains numeric data, it will automatically populate which quadrant?

 
 
 

Question 1 of 20

Online MS Excel Pivot Table Quiz with Answers

  • Before you create a PivotTable, you need to ensure that you have nice clean data with
  • Converting your data into a Table before creating a PivotTable is a good idea because:
  • When in PivotTable Fields, if you tick a field that contains numeric data, it will automatically populate which quadrant?
  • When in PivotTable Fields, if you tick a field that contains non-numeric data, it will automatically populate which quadrant?
  • To change the calculation within your PivotTable, you get more options by right-clicking one of the values, compared to customizing the calculation in the Values quadrant.
  • Rather than seeing discrete values, if you want to see percentages instead, you should go to
  • Choosing the option Rank Smallest to Largest will result in the PivotTable being sorted and displayed from smallest to largest values.
  • When does Excel automatically give you sub-totals for your PivotTable?
  • You can choose to display a sub-total as a sum and as an average, and both these items will appear separately at the bottom of the respective section in your PivotTable.
  • If you have full dates and you create a PivotTable, Excel will group these dates, such as grouping by month. You can remove this by choosing Ungroup.
  • Which of the following buttons would we need to click to group the dates below?
  • Which of the following buttons would we need to click to group a few rows from our PivotTable?
  • What would moving the Europe cell to elsewhere in the PivotTable do?
  • Which of these are correct statements about custom lists in Excel?
  • One way to filter which columns and which rows appear in your PivotTable is to use the drop-down menu that appears next to Row Labels and Column Labels.
  • To clear a filter in a PivotTable, you could:
  • The main difference between Label Filters and Value Filters is that Label Filters are for filtering based on text.
  • If you want to filter by a field that you do not necessarily want to show in the PivotTable itself, you should move this field to which quadrant?
  • If you choose Show Report Filter Pages after having selected multiple items in a filter, what would happen?
  • A PivotTable currently occupies some of Columns A and B as per the screenshot. The formula that is displayed is typed into cell D4. What would happen to the result of the formula if the filters of the PivotTable were modified?
Online MS Excel Pivot Table Quiz 4

Try Macroeconomics MCQs Test

Power BI Quiz Questions 1

Prepare for Power BI Quiz Questions for exams and job interviews with this comprehensive quiz featuring 23 MCQs! Test your knowledge on data import, DAX functions, visualizations, time intelligence, and more. These Power BI Quiz Questions are perfect for statisticians, data analysts, and researchers. Boost your Power BI skills today! Let us try the Online Power BI Quiz Questions now.

Please go to Power BI Quiz Questions 1 to view the test

Online Power BI Quiz Questions with Answers

  • Which of the following are views in Power BI?
  • From which of the following sources can we import data into Power BI?
  • Power BI will automatically create the relationship between tables if field names match across tables (with a few extra checks).
  • Which of the following would help us create a calendar table in Power BI?
  • What is the purpose of the Mark as date table button?
  • Which of the following DAX functions returns a table with a single column named Date that contains a contiguous set of dates?
  • If you click on a blank area of the canvas before clicking on a visualization, Power BI will add the visualization to this blank area.
  • In Power BI, when we change the theme, we change the colour scheme for which of the following?
  • When you click on New measure, the formula bar is automatically populated with “Measure = “. Here, the word on the left-hand side of the equation is the name of the measure, which is a name you can choose.
  • Sometimes, a single number is the most important information you need to track in your Power BI report. Which visualization is best suited to showing a single numeric value?
  • Which of the following are displayed when you hover over a violin plot?
  • When you click on Show as a table on any visualization in the report, what happens?
  • ————- is a data analysis language. ———- is a transformational language.
  • What does dragging and dropping one field onto another do in the Fields pane?
  • Unlike Excel pivot tables, filters in Power BI allow us to filter all the visualizations on the page or all pages in the report.
  • By default, slicers in Power BI apply to all pages in the report.
  • If you hover over a filter icon on any of the visualizations in a report, it will let you know all the filters that have been applied.
  • Which of the following are valid time intelligence functions in DAX?
  • When using time intelligence DAX functions to create a visualization that shows increases and decreases over time, which of the following visualizations would be most effective?
  • What is the advantage of the DIVIDE function in DAX compared to using the standard divide numerical operator?
  • If you want to publish your Power BI report in a format that users can interact with, a good option is to publish it.
  • Once you publish to the Power BI Service, you will see at least two entities in the workplace it was published to: the report as well as the dataset.
  • Dashboards can be created in the Power BI Service, but reports cannot.
Online Power BI Quiz Questions with Answers

Try General Knowledge Quizzes

SAS Functions and Procedures

Discover key differences between SAS functions and procedures, when to use SUM() vs. ‘+’ operator, and INPUT vs. INFILE statements in SAS Software. Learn with clear examples and practical use cases for efficient data analysis. Perfect for SAS beginners and professionals!

SAS Functions and Procedures Questions And Answers

What is the difference between SAS Functions and Procedures?

The SAS Functions and Procedures (PROCs) serve different purposes and operate in distinct ways. The breakdown of the key differences between SAS Functions and Procedures is:

SAS Functions

Perform computations or transformations on individual values (usually within a DATA step). The SAS Functions are used to (i) operate on single values or variables, (ii) return a single result for each function call, and (iii) are often used in assignment statements or expressions.

## SAS Functions Example

data example;
    x = SUM(10, 20, 30);  /* Returns 60 */
    y = UPCASE('hello');  /* Returns 'HELLO' */
    z = SUBSTR('SAS Programming', 1, 3);  /* Returns 'SAS' */
run;

The following are some important types of SAS Functions:

  • Numeric Functions (e.g., SUM(), MEAN(), ROUND())
  • Character Functions (e.g., UPCASE(), SUBSTR(), TRIM())
  • Date/Time Functions (e.g., TODAY(), MDY(), INTCK())
  • Statistical Functions (e.g., NORMAL(), RANUNI())

SAS Procedures (PROCs)

SAS procedures, or PROCs, are used to perform data manipulation, analysis, or reporting on entire datasets. The usage of PROCS is to (i) operate on entire datasets (not just single values), (ii) generate tables, reports, graphs, or statistical analyses, and (iii) execute in a PROC step, not a DATA step.

## SAS Procedures (PROCs) Examples

proc means data=sashelp.class;  /* Computes summary statistics */
    var age height weight;
run;

proc sort data=sashelp.class;  /* Sorts a dataset */
    by descending age;
run;

proc freq data=sashelp.class;  /* Generates frequency tables */
    tables sex age;
run;

The types of SAS Procedures are:

  • Data Management PROCs (e.g., PROC SORT, PROC TRANSPOSE)
  • Statistical PROCs (e.g., PROC MEANS, PROC REG, PROC ANOVA)
  • Reporting PROCs (e.g., PROC PRINT, PROC TABULATE, PROC REPORT)
  • Graphical PROCs (e.g., PROC SGPLOT, PROC GCHART)

What are the key differences between SAS Functions and SAS Procedures?

The following are the key differences between SAS Functions and SAS Procedures:

FeatureSAS FunctionsSAS Procedures (PROCs)
OperationWork on single values/variablesWork on entire datasets
ExecutionUsed in DATA stepsUsed in PROC steps
OutputReturns a single valueGenerates reports, tables, or datasets
ExamplesSUM(), UPCASE(), SUBSTR()PROC MEANS, PROC SORT, PROC FREQ
Usage ContextCalculations within a variableDataset processing & analysis

Describe when to use SAS Functions or SAS PROCs

  • Use Functions when you need to transform or compute values within a DATA step.
  • Use Procedures when you need to analyze, summarize, or manipulate entire datasets.

What is the Difference Between the “Sum” Function and using the “+” Operator in SAS?

In SAS, both the SUM function and the + Operators can be used to perform addition, but they behave differently in terms of handling missing values and syntax. The breakdown of the differences between the SUM Function and the + Operator is:

SUM Function (SUM())

The SUM Function is used to add values while ignoring missing values (.). The general syntax of the SUM Function in SAS is

sum(var1, var2, var3, ...)

The behaviour of the SUM() is that if any argument is non-missing, the result is the sum of non-missing values. If all arguments are missing, the result is missing (.). The SUM() Function is best for

  • Summing multiple variables where some may have missing values.
  • Avoiding unintended missing results due to missing data.
## SAS SUM() Function Example 

data example;
    a = 10;
    b = .;  /* missing */
    c = 30;
    sum_result = sum(a, b, c);  /* 10 + 30 = 40 (ignores missing) */
run;

+ Operator

The ‘+’ operator performs arithmetic addition but propagates missing values. The general syntax of the ‘+’ operator in SAS is

var1 + var2 + var3

The behaviour of ‘+’ is:

  • If any variable is missing, the result is missing (.).
  • Only works if all values are non-missing.

The use of ‘+’ operator is best for:

  • Cases where missing values should make the result missing (e.g., strict calculations).
## + Operator Example

data example;
    a = 10;
    b = .;  /* missing */
    c = 30;
    plus_result = a + b + c;  /* 10 + . + 30 = . (missing) */
run;

What are the Key Differences between the SUM() Function and the ‘+’ Operator in SAS?

FeatureSUM Function (SUM())+ Operator
Handling Missing ValuesIgnores missing values (10 + . = 10)Returns missing if any value is missing (10 + . = .)
Syntaxsum(a, b, c)a + b + c
Use CaseSumming variables where some may be missingStrict arithmetic (missing = invalid)
PerformanceSlightly slower (function call)Faster (direct operation)

When to Use the SUM() Function and ‘+’ Operator in SAS?

  • Use SUM() when:
    • You want to ignore missing values (e.g., calculating totals where some data is missing).
    • Example: total = sum(sales1, sales2, sales3);
  • Use + when:
    • Missing values should make the result missing (e.g., strict calculations where all inputs must be valid).
    • Example: net_pay = salary + bonus; (if bonus is missing, net_pay should also be missing).

What is the difference between the INPUT and INFILE statements?

In SAS, both the INPUT and INFILE statements are used to read data, but they serve different purposes and are often used together. Here’s a breakdown of their differences:

INFILE Statement

The INFILE Statement in SAS specifies the source file from which data is to be read. It is used to

  • Defines the external file (e.g., .txt, .csv, .dat) to be read.
  • Can include options to control how data is read (e.g., delimiters, missing values, encoding).

The general Syntax of the INFILE Statement in SAS is:

INFILE "file-path" <options>;

The Key Options of the INFILE Statement are:

  • DLM=’,’ (specifies delimiter, e.g., CSV files)
  • DSD (handles quoted values and missing data correctly)
  • FIRSTOBS=2 (skips the first line, e.g., headers)
  • MISSOVER (prevents SAS from moving to the next line if data is missing)
## INFILE Statement Example
    DATA sample;
      INFILE "/path/to/data.csv" DLM=',' DSD FIRSTOBS=2;
      INPUT name $ age salary;
    RUN;

INPUT Statement

The INPUT Statement defines how SAS reads raw data (variable names, types, and formats). It is used to

  • Maps raw data to SAS variables (numeric or character).
  • Specifies the layout of the data (column positions, delimiters, or formats).

The general Syntax of the INPUT Statement is

INPUT variable1 $ variable2 variable3 ...;

The types of Input Styles are:

  • List Input (space/comma-delimited): INPUT name $ age salary;
  • Column Input (fixed columns): INPUT name $ 1-10 age 11-13 salary 14-20;
  • Formatted Input (specific formats): INPUT name $10. age 2. salary 8.2;
## INPUT Statement Example

    DATA sample;
      INFILE "/path/to/data.txt";
      INPUT name $ age salary;
    RUN;
Statistics Data Analysis SAS Functions, SAS Procedures

Learn String Manipulation in R Language