Excel Certification Exam Questions Answered Correctly Latest Update

Excel Certification Exam Questions Answered Correctly Latest Update Every cell in a Microsoft Excel worksheet has a cell address that notes the row and column where the cell lives. What is this address called? - Answers cell reference What is a quick way to search for blank data in a column? - Answers Highlight the first cell in the column and press Ctrl-[down arrow] to highlight to the first empty cell in that column. What is one way to preserve the value of a calculation without retaining the calculation in the cell? - Answers Select the cells, copy, and use the Paste Special option to paste the values of the calculations, not the calculations themselves, in the cells. Microsoft Excel's auto-sequencing capabilities enable you to number rows or columns sequentially without typing each of the numbers. How do you auto-sequence a row of numbers? - Answers Type the first cell of numbers (1), click on the "magic box" in the lower right corner of the cell, press the Ctrl key, and drag across the number of cells you want numbered automatically. A powerful tool in Microsoft Excel automatically enters data when it senses a pattern in what you are entering. What is the name of this tool? - Answers Flash Fill What is the purpose of the Convert Text to Columns wizard? - Answers It splits data in a single column containing multiple pieces of data into multiple columns of data. What does the From Web button enable you to do? - Answers Import data from a web page into Microsoft Excel If you sort one column of data in a table without sorting all of the columns, what happens? - Answers Only the column you sorted will sort, which will mean rows of information are now out of alignment. If you are creating a formula and want column N to be absolute and row 95 to be relative, how do you enter the cell into the formula? - Answers $N95 Every formula needs to begin a specific way to indicate that it is a formula. How do formulas begin? - Answers With an equal sign (=) In creating worksheets and analysis, what task likely requires the most time? - Answers Data preparation and cleanup What does the Flash Fill feature do? - Answers Fills out subsequent data when it senses a pattern in the data being entered What is the keyboard shortcut for forcing the Flash Fill feature? - Answers <Ctrl> + E What does the VLOOKUP() function enable you to do? - Answers Join data columns in Excel What is the exact match command, which you will use when using the VLOOKUP() function? - Answers FALSE What is one requirement for using the TRUE command when using the VLOOKUP() function? - Answers Data in the table must be sorted. When merging two tables, what data would be included in an Outer Join? - Answers All data from both tables The first step in using Power Query is to turn your data table into an Excel table. How do you do this step? - Answers Press the <Ctrl> + T key combination. All of the following statements regarding the Power Query tool in Excel are true EXCEPT: - Answers Power Query works only if the tables getting merged both have the exact same number of rows with data. What is the term used for synthesizing multiple pieces of data into a single piece of data? - Answers Aggregation What is the result of the function MIN(B17:B52)? - Answers The result is the lowest value of the values in cells B17 through B52. Pivot tables are powerful aggregation tools that first do what with the data before aggregating? - Answers Sort the data in categories To prepare your data for creating a pivot table, the data must be "tidy." What does it mean to have tidy data? - Answers Every column has a column header, and the cells within that column contain the data indicated by that header. Choose the best answer. Which of the following contains data that a pivot table can help aggregate? - Answers 1. Average sales by day for an online clothing business during the month of May 2. Average weight loss by gender for participants eating a keto diet vs. counting calories 3. Total sales by product for six new products released for the summer line Which of the following types of charts enables you to visually represent part-to-whole relationships? - Answers Treemap What is one significant difference between a bar chart and a histogram? - Answers A histogram requires one set of data whereas a bar chart requires two sets of data. All of the following chart types can be created from within a pivot table except: - Answers Scatter plot. What is the name of an important filter in pivot tables that filters the entire worksheet before it is ever aggregated? - Answers Report filter What are slicers in a pivot table? - Answers Visual filters What is data stratification? - Answers The process of dividing a population into subpopulations, each of which is a group of sampling units that have similar characteristics What is the main reason you should consider sampling rather than polling an entire population? - Answers You do not have available resources to poll the entire population. What is a probability distribution? - Answers The mathematical function that provides the chance that each of several possible outcomes will occur The classic educational or classroom bell curve is what type of distribution? - Answers Normal What is a simple test to determine whether variables are distributed symmetrically? - Answers The mean is equal to the median. What does the function AVERAGE() enable you to determine? - Answers The mean of a data set ______________ is a standardized measure of dispersion. - Answers Standard deviation When would you use the VAR.P() function? - Answers When determining the variance when you have all of the data, not just a sample of the data Excel's CORREL() function can be used to calculate the correlation between two variables. What is an important item to keep in mind when determining correlation? - Answers Correlation does not necessarily mean causation. Why would investors looking to have a diversified stock portfolio view a negative covariance in stocks as a good thing? - Answers A negative covariance means the stocks would not behave the same way, which would hedge against significant loss. Microsoft Excel's Remove Duplicate (Data/Remove Duplicates) enables you to remove duplicates more quickly than finding and removing them manually. What is one disadvantage of this feature? - Answers You do not have as much control over the process.