If one software program/application/product could be said to be omnipresent in any business setting, it has to be Microsoft Excel. Spreadsheets were one of the driving forces in bringing personal computers into offices decades ago, and have remained ubiquitous ever since.

An integral part of the Microsoft Office suite, Excel is the go-to program for a lot of data analysis and management. Given that, it is very unlikely to find anyone who has not worked with it in some capacity — but how much do you really know about its features and functions?

Certifications are available for various versions of Excel and all require a base knowledge (or higher) of how to use it for data analysis and modeling, with many branching into how it can be used for project management, inventory management, and even scheduling. Could you identify the shortcuts and features that have been around for a while?

What follows is a self-test of 25 questions based on the general concepts and topics related to all current versions. The answers appear at the end of the questions. Good luck!

1. Which keyboard shortcut will put the current date in the active cell?
A) Ctrl + ;
B) Ctrl + Shift + 2
C) Shift + Insert
D) Alt + Enter

2. You have copied all the data in a worksheet’s column and now want to paste it into another worksheet in a row (rather than a column). Which option of Paste allows you to do this?
A) Shuffle
B) Rearrange
C) Swap
D) Transpose

3. What function does the circled item shown in the portion of the Microsoft Excel ribbon perform?

A) Consolidates data
B) Removes duplicate entries
C) Collapses columns
D) Expands rows

4. Which keyboard shortcut will hide the column where the cursor currently resides?
A) Ctrl + U
B) Ctrl + H
C) Ctrl + 0
D) Ctrl + 9

‍5. Using data bars to embed bar chart information into cells as shown in the following figure is accomplished through which of the following?‍

A) Data Analysis
B) Themes
C) Styles & Borders
D) Conditional Formatting

6. You would like a column of numbers to display in Zip Code + 4 format. Which of the following number categories will allow you to pick this option?
A) Custom
B) Text
C) Special
D) Other

7. Which of the following icons is used in the Excel ribbon for the AutoSum function?
A) ∑
B) ∏
C) [
D) &

8. Which of the following is NOT true regarding Defined Names in Microsoft Excel?
A) Range names are not case sensitive
B) Range names are limited in length to 255 characters
C) You can use blank spaces, underscores, and numbers in range names
D) You can change attributes associated with the range name by using Name Manager

9. Which dialog box is shown in the following figure?

A) Validation Catalyst
B) Data Validation
C) Criteria Dashboard
D) Ticket Manager

10. Which of the following is an error that can occur when a cell(s) references itself, whether directly or indirectly?
A) Logic bomb
B) Nested shell
C) Trace dependent
D) Circular reference

11. Which keyboard shortcut will bring up the Print Preview dialog box?
A) F1
B) Ctrl + F2
C) Alt + F3
D) Shift + F4

12. With Excel for Microsoft 365, which of the following is true regarding comments and notes?
A) Notes are threaded and allow for discussions with others while Comments are intended for annotations about the data
B) Comments and Notes are synonyms and can be used interchangeably for the same purpose(s)
C) Comments are threaded and allow for discussions with others while Notes are intended for annotations about the data
D) The Comment feature has replaced Notes in order to be more in line with other products in the Microsoft Office Suite

13. Which of the following is the correct format for conditional functions?
A) =IF(logical test, value if false, value if true)
B) =IF(logical test, value if true, value if false)
C) =IF(value if true, value if false, logical test)
D) =IF(value if false, value if true, logical test)

14. To scroll two documents at the same time in order to look for line-by-line differences, you can use Synchronous Scrolling. To be able to enable this, you must first select which of the following?
A) View Side by Side
B) Switch Windows
C) Freeze Panes
D) Arrange All

15. Which of the following does the circled icon represent?‍

A) Clean
B) AutoFilter
C) Siphon
D) Collect

Please visit GoCertify to attempt the remaining 10 questions of this quiz.

‍

ANSWERS

1. A: The Ctrl + ; keyboard shortcut will put the current date in the active cell. Ctrl + Shift + 2 will copy the value from the cell above into this one. Shift + Insert will paste whatever is in the clipboard. Alt + Enter will add a new line in the cell.
2. D: The Transpose option will paste data from a column into a row (or from a row into a column).
3. B: The circled item shown in the portion of the Microsoft Excel ribbon can be used to remove duplicate entries.
4. C: Ctrl + 0 will hide the column where the cursor currently resides while Ctrl + 9 hides the row. Ctrl + U toggles underlining, while Ctrl + H brings up the Replace dialog box.
5. D: Using data bars to embed bar chart information into cells as shown is accomplished through Conditional Formatting.
6. C: Beneath the Special format category are options for numbers to display in Zip Code and Zip Code + 4 formats.
7. A: The ∑ symbol is used for AutoSum.
8. C: You cannot use blank spaces in range names. Those names are not case sensitive and are limited to 255 characters in length. Once created, you can edit attributes associated with them by using Name Manager beneath Formulas.
9. B: The image shows the Data Validation dialog box.
10. D: A circular reference is an error that can occur when a cell(s) references itself, whether directly or indirectly.
11. B: The Ctrl + F2 keyboard shortcut will bring up the Print Preview dialog box. F1 brings up Help, while Alt + F3 does nothing, and Shift + F4 will repeat the last Find operation.
12. C: Comments are threaded and allow for discussions with others while Notes are intended for annotations about the data. In other words, Notes are now used for what Comments used to be used for in earlier versions of Excel.
13. B: The correct format for conditional functions is =IF(logical test, value if true, value if false).
14. A: To use Synchronous Scrolling, you must first select View Side by Side. The other options given are not directly responsible for allowing you to use Synchronous Scrolling (you cannot freeze and split panes at the same time).
15. B: The icon is that of the AutoFilter and can be used to find, show, or hide values within column(s) of data.

‍