Wall Street Prep Excel Crash Course Exam 246 Questions with Verified Answers
Keyboard Versus the Mouse - CORRECT ANSWER Almost everything that can be
... [Show More] done in Excel using a mouse can also be done using the keyboard shortcuts
Best way to learn is to disconnect the mouse and work through Excel using only the keyboard
Name Box - CORRECT ANSWER Tells you what cell you are in (top left below the ribbon)
Formula Bar - CORRECT ANSWER When you insert a formula into a cell and hit return, the cell will show you the output
The formula bar, however, will show you the formula
Next to "fx" right below the ribbon
Worksheets - CORRECT ANSWER An Excel file is called a workbook;
You start with 1 worksheet but you can add/delete more
The active worksheet is highlighted in Excel
Moving Between Worksheets - CORRECT ANSWER Ctrl + PageDown/PageUp
(Option + RightArrow/LeftArrow)
Adding Worksheets - CORRECT ANSWER (Fn Shift F11)
Columns - CORRECT ANSWER Alphabetically labeled (A, B, C, etc.)
Rows - CORRECT ANSWER Numerically labeled (1, 2, 3, etc.)
Main tabs - CORRECT ANSWER Although we focus on shortcuts, virtually all commands, functions, and Excel features can be accessed through the task-oriented tabs which organize them into nine logical categories:
(1) Home
(2) Insert
(3) Draw
(4) Page Layout
(5) Formulas
(6) Data
(7) Review
(8) View
(9) Developer
How to Access Ribbon on Mac - CORRECT ANSWER Ctrl Fn F2
Use arrow keys to peruse the Mac ribbon
Mac Settings to Disable - CORRECT ANSWER (1) Function Keys: Settings - Keyboard - Use F1, F2, etc. keys as standard function keys
(2) Mission Control: System Preferences - Keyboard - Shortcuts - Mission Control - Disable "Move a space left" and "Move a space right" to use Ctrl RightArrow or Ctrl LeftArrow
Using Function Keys - CORRECT ANSWER Hit Fn and then the function key to use the function keys themselves on a Mac
Open a New Workout - CORRECT ANSWER Ctrl N
Cmnd N
The File Tab - CORRECT ANSWER Many Excel features (Open file, Save file, Print file, etc.) are located in this tab
Excel also has alternative keyboard shortcuts (using Ctrl) for many of these features
Open a File: Ctrl O (Cmnd O)
Save a File: Ctrl S (Cmnd S)
Print a File: Ctrl P (Cmnd P)
Save a File - CORRECT ANSWER Ctrl S
Cmnd S
Print a File - CORRECT ANSWER Ctrl P
Cmnd P
Open a File - CORRECT ANSWER Ctrl O
Cmnd O
Toolbars - CORRECT ANSWER Within each of the main tabs you will find all of Excel's features, grouped by commands
Navigating to the Toolbars Without the Mouse - CORRECT ANSWER Hit Alt and the appropriate letter (or use the right/left arrow keys) to get to the desired tab
Once there, use the Tab and Shift Tab keys to navigate around, the Space to open a drop down, and Enter to select
Bold Command - CORRECT ANSWER Ctrl B
Cmnd B
The Home Tab - CORRECT ANSWER Includes most formatting properties:
Font type, size, and color
Background color
Text/cell alignment
Changing currency, decimal, percent formats
Inserting, deleting, and hiding rows and columns
Adjusting columns and rows width
Inserting, deleting, and renaming worksheets
The Insert Tab - CORRECT ANSWER Important Features: Pivot Table, Charts, Header & Footer
Page Layout Tab - CORRECT ANSWER Important Features: Print Area, Fonts, Gridlines
Formulas Tab - CORRECT ANSWER Important Features: Insert Function, Function Library, Name Manager, Formula Auditing Tools (Trace Precedents, Trace Dependents)
Data Tab - CORRECT ANSWER Important Features: Filter, Sort, Text to Columns, Flash Fill, Data Validation, Data Tables (Goal Seek), Group/Ungroup
Review Tab - CORRECT ANSWER Important Features: New Comment
View Tab - CORRECT ANSWER Important Features: Zoom, Gridlines
Developer Tab - CORRECT ANSWER Important Features: Record Macro, Add-Ins
Accessing Settings (Excel Options) - CORRECT ANSWER File > Options (Alt F T or Alt T O)
MAC: Excel > Preferences
Excel Settings Changes - CORRECT ANSWER General:
Change "Sheets in New Workbook" to 3
Uncheck "Show Workbook Gallery when opening Excel"
Calculation/Functions:
Change Calculation Options to "Automatic except for data tables"
Check "Enable iterative calculation"
Edit/Advanced:
Uncheck "After pressing Enter, move selection"
*Optional - Check "Automatically insert a decimal point"
Protection - CORRECT ANSWER Go to File > Info > Password Protecting Files (File > Passwords)
Alt F T I
You can password protect files "Encrypt with Password"
You can also protect individual worksheets so that people can't see or edit formulas "Protect Current Sheet"
Shortcuts to Allow the User Input to Add More Sheets to a Workbook - CORRECT ANSWER Alt i w
Alt h i s
Shift F11
Alt Shift F1
Find or Find and Replace Shortcut - CORRECT ANSWER Ctrl F
(Cmnd F)
What is the recommended workbook calculation setting for Excel? - CORRECT ANSWER Automatic Except for Data Tables
Add a Cell Reference From a Different Worksheet into an Existing Formula - CORRECT ANSWER Hit F2 to get into the existing formula; delete any incorrect formulas or operators
Hit F2 again to enable "Enter" mode on the bottom-left corner of the Excel sheet
Holding down Ctrl, use PageUp or PageDown to find the desired worksheet
Let go of the Ctrl and PageUp/Down keys
Use the arrow keys to located the desired cell reference
Hit Enter
Autofit Row Height Command - CORRECT ANSWER Alt H O A
Autofit Column Height Command - CORRECT ANSWER Alt O C A
Assign Column Width Command - CORRECT ANSWER Alt H O W
(Home > Format > Column Width)
Assign Row Height Command - CORRECT ANSWER Alt H O H
(Home > Format > Row Height)
Command to Change Zoom Size - CORRECT ANSWER Alt+V+Z
(Ctrl+MouseScroll)
Autofit the Column Width Command - CORRECT ANSWER Alt H O I
(Home > Format > Autofit Column Width)
Basic Excel Drills - CORRECT ANSWER Most keyboard shortcuts involves Alt or Ctrl keys
Alt: Press each key and let go (do you NOT need to hold to the Alt key)
Ctrl: Ctrl key must be held down as you press the other key in the shortcut sequence
Ctrl Commands - CORRECT ANSWER Most commands involving Ctrl are shortcuts that are automated by default to make Excel more efficient and user friendly
Save As Shortcut - CORRECT ANSWER Alt F A
Alt Commands - CORRECT ANSWER Most commands involving Alt are shortcuts to the commands and functions inside the default eight Main Tabs
Select Column & Range of Columns - CORRECT ANSWER Ctrl Space
Range: Ctrl Space Shift+RightArrow
Select Row & Range of Rows - CORRECT ANSWER Shift Space
Range: Shift Space Shift+DownArrow
Undo - CORRECT ANSWER Ctrl Z
Excel Formulas - CORRECT ANSWER Start with the = sign
The = sign tells Excel that the info that will follow the = sign should be treated as a formula and not as plain text
Once you type in the = sign, use the arrow keys to navigate around the Excel workbook to find the cells you need for your formula
Operations in Excel - CORRECT ANSWER
Copying Across Formulas - CORRECT ANSWER Ctrl + C to copy, Ctrl + V to paste
Open Format Cells Dialog - CORRECT ANSWER Ctrl+1
(Cmnd+1)
Navigating Format Cells Dialog - CORRECT ANSWER Using the Arrow keys to get around the tab
To get in a tab, either use Tab key or use Alt and the relevant letter
Use Space to toggle check boxes
Highlight a Contiguous Range - CORRECT ANSWER Ctrl + Shift + Arrows
(Cmnd + Shift + Arrows)
Combining Data in Two Sheets into Another Sheet - CORRECT ANSWER (1) Copy and paste the relevant headers into the third sheet
(2) Type "=" in the relevant column in the third sheet and then use Ctrl PageUp/Down (Option Right/LeftArrow) to get to the first sheet and find the right data
(3) Press "+" and then find the relevant data in the second sheet and press "Enter"
(4) Ctrl+C to copy that formula and then apply it to the rest of the table
(5) Delete and retype any important formulas (ex. Net Profit)
(6) Add in formatting by Ctrl+C the table in sheet 1 or 2, moving to the upper corner of the table in sheet 3 and using Paste Special and then format: Alt+E+S+T or Ctrl+Alt+V+T (Ctrl+Cmnd+V+T)
Paste Special - CORRECT ANSWER Alt+E+S
(Ctrl+Cmnd+V+T)
Let's the user dictate the specific attributes of a cell or highlighted region that should be pasted
Open a New Workbook - CORRECT ANSWER Ctrl+N
Toggle Between Workbooks - CORRECT ANSWER Ctrl+Tab
(Cmnd ~)
Referencing Cells from Other Workbooks - CORRECT ANSWER (1) Hit "="
(2) Hit Ctrl+Tab (Cmnd ~) to go to the other workbook
(3) Find the relevant data and hit Enter
How to Autofit a Range of Columns - CORRECT ANSWER (1) Select the columns by hitting Ctrl+SpaceBar
(2) Hold down the shift key and use left and right arrow keys to highlight the range of columns
(3) Hit Alt+H+O+I to autofit the columns
Moving Around the Format Cells Dialog - CORRECT ANSWER Move across tabs with Ctrl+Tab
Move counterclockwise across form elements with Shift+Tab
Select a checkbox with Spacebar
Freezing and Splitting Panes - CORRECT ANSWER Excel worksheets often become too large to allow users to view all of their contents on one screen
'Freezing Panes' and 'Splitting Panes' options provide users with the flexibility to select specific rows and columns that always remain visible when scrolling in the worksheet
Panes - CORRECT ANSWER Portions of the worksheet that are bounded and separated by vertical and/or horizontal bars
To Freeze Panes - CORRECT ANSWER Click the cell below the desired row and to the right of the desired column where you want to freeze panes
Hit Alt+W+F+F to freeze
To unfreeze, hit Alt+W+F+F again
(View>Freeze Panes and then View>Unfreeze Panes)
*Note: To create a horizontal freeze, go to the leftmost column!
Splitting Panes - CORRECT ANSWER Allows users to scroll in both areas of the worksheet, while rows and columns in the non-scrolled area remain visible
Directions:
(1) Click the cell below the desired row and to the right of the desired column where you want to split panes and hit Alt+W+S
(2) To un-split, click Alt+W+S again
(3) Press F6 to move from pane to pane in a clockwise direction; press Shift+F6 to move from pane to pane in a counter-clockwise direction
(View>Split)
*Note: To create a horizontal split, go to the leftmost column!
Redo - CORRECT ANSWER Ctrl+Y
Entering an Active Cell - CORRECT ANSWER F2
(Fn+F2)
Go to the Beginning of an Active Cell Formula - CORRECT ANSWER Ctrl+Home
(Fn+Ctrl+LeftArrow or Cmnd+UpArrow)
Go to the End of an Active Cell Formula - CORRECT ANSWER Ctrl+End
(Fn+Ctrl+RightArrow or Cmnd+DownArrow)
Jumping from One Formula Element to the Next - CORRECT ANSWER Ctrl+Arrows
(Cmnd+Arrows)
Highlight Formula Elements & Maintain Contiguous Elements - CORRECT ANSWER Ctrl+Shift+Arrows
(Cmnd+Shift+Arrows)
Highlight the Whole Formula of an Active Cell - CORRECT ANSWER Shift+DownArrow
(Shift+Up/DownArrow)
How to Revert Back to Original Formula after Editing an Active Cell - CORRECT ANSWER Esc
How to Exit Cell Edit Mode in an Active Cell - CORRECT ANSWER F2
(Fn+F2)
Clearing a Cell - CORRECT ANSWER Alt+H+E (Home>Clear)
Clear All: Alt+H+E+A
Clear Format: Alt+H+E+F
Clear Comments: Alt+H+E+M
Add Comment - CORRECT ANSWER Shift+F2 (Shift+Fn+F2)
Esc twice to exit
Right Fill from Cell Left - CORRECT ANSWER Highlight the cell you want to copy
Shift+RightArrow to the cells that you want to be filled
Hit Ctrl+R
Down Fill from Cell Up - CORRECT ANSWER Highlight the cell you want to copy
Shift+DownArrow to the cells that you want to be filled
Hit Ctrl+D
Inserting Rows - CORRECT ANSWER (1) Go to any cell in the row below the desired row
(2) Press Alt+I+R
OR
(1) Highlight the row below the desired row with Shift+Space
(2) Press Ctrl Shift + to insert the new row
Inserting Columns - CORRECT ANSWER (1) Go to any cell in the desired column
(2) Press Alt+I+C
OR
(1) Highlight the desired column by pressing Ctrl+Spacebar
(2) Insert a column by pressing Ctrl Shift +
Deleting Rows and Columns - CORRECT ANSWER Row: Alt+H+D+R
Column: Alt+H+D+C
OR
Highlight the row/column and press Ctrl -
Paste Only Formulas - CORRECT ANSWER Alt+E+S+F
Paste Only Formatting - CORRECT ANSWER Alt+E+S+T
How to change a list of numbers quoted in 1,000s to 1s - CORRECT ANSWER In another cell, enter the number 1000
Copy this cell, then highlight your list of numbers
Press Alt+E+S+M (Paste Special Multiply)
Paste Special Operations - CORRECT ANSWER Allows you to apply operations to large amounts of data
You can convert large amounts of data to positive to negative or vice versa
*Note: You should ONLY paste on numbers that are hard inputs
Converting Positive Numbers to Negative - CORRECT ANSWER Ctrl+C the data values
Special paste the new numbers using the Subtract operation - Alt+E+S+S
OR
In another cell, enter the number -1
Ctrl+C -1 and special paste on the relevant numbers using the multiply operation (Alt+E+S+M)
Paste Special Transpose - CORRECT ANSWER Allows users to convert a vertical list of data into a horizontal list of data, and vice versa
(1) Highlight and copy the list of numbers
(2) Move your cursor outside of the list range
(3) Press Alt+E+S+E (Paste Special Transpose)
*Note: Be careful when applying a paste special transpose on formulas -- they don't work on formulas with relative references
Ctrl Shortcuts - CORRECT ANSWER Cut: Ctrl+X
Cut Cells - CORRECT ANSWER Ctrl+X
Note: Dependent calculations DO NOT change when you cut cells!
Number Format: 2 decimals, 000 separator - CORRECT ANSWER Ctrl+Shift+!
Currency Format: 2 decimal places - CORRECT ANSWER Ctrl+Shift+$
Percentage Format with No Decimal Places - CORRECT ANSWER Ctrl+Shift+%
Date Format with the Day, Month, and Year - CORRECT ANSWER Ctrl+Shift+#
Boldface - CORRECT ANSWER Ctrl+B
Italicize - CORRECT ANSWER Ctrl+I
Underline - CORRECT ANSWER Ctrl+U
(Cmnd+U)
Remove All Borders - CORRECT ANSWER Ctrl+Shift+_
Naming Cells - CORRECT ANSWER Ctrl+F3 > Alt+N to name a new cell
(Ctrl+L)
Anchoring Cells - CORRECT ANSWER Anchoring (or "fixing") cells that are being referenced in a formula tells Excel that even if you copy the formula to another cell, the cells in the formula that are anchored should not change
(Fn+)F4 on the name of the cell in a formula
Keep clicking F4 to toggle the type of anchor
Naming Worksheets - CORRECT ANSWER Alt+H+O+R
Inserting & Deleting Worksheets - CORRECT ANSWER Add a new worksheet by pressing Alt+H+I+S
Delete a worksheet by pressing Alt+E+L
Grouping & Hiding Columns - CORRECT ANSWER (1) Hit Ctrl+Spacebar to select the desired column
(2) Hit Shift+Alt+RightArrow (Option+Shift+RightArrow) to create the group
(3) Hit Alt+A+H to hide the columns
(4) Hit Alt+A+J to unhide the columns
(5) Hit Shift+Alt+LeftArrow (Option+Shift+LeftArrow) to remove the group
Grouping & Hiding Rows - CORRECT ANSWER (1) Hit Shift+Spacebar to select the desired row
(2) Hit Shift+Alt+RightArrow (Option+Shift+RightArrow) to create the group
(3) Hit Alt+A+H to hide the columns
(4) Hit Alt+A+J to unhide the columns
(5) Hit Shift+Alt+LeftArrow (Option+Shift+LeftArrow) to remove the group
Group - Don't Hide - CORRECT ANSWER You can hide data by hitting Alt+H+O+U+R for rows and Atl+H+O+U+C for columns
Don't ever use this method because there are no indications as to the data's hidden location - stick to grouping
Grouping Worksheets - CORRECT ANSWER Helpful if you would like to format data in the same manner or enter the same data across multiple sheets
By grouping multiple worksheets, any data and formatting that you would perform in one of the grouped worksheets would automatically be reflected in all of them
Hold down Ctrl+Shift and press Page+Up/Down to reach the worksheets you would like to group (Shift+Click on the desired worksheets)
All of the grouped worksheets are highlighted and the file name on top of the Excel screen should show [Group] are it
To ungroup, press Ctrl+PageDown
Auditing Cells - CORRECT ANSWER Good: (Fn+)F2 -- go to a desired cell and hit the F2 key
Excel will highlight (in different colors) all the cell components of an existing formula present in that cell
Better: Ctrl+[ and Ctrl+] -- hitting Ctrl [ on a cell will highlight the precedent cell(s)
Keep hitting Ctrl [ and it will take you to the next precedent
Hitting Ctrl ] jon a cell will do the same thing for dependent cells
Best: Excel Auditing Functions with Alt+M+P for precedents and Alt+M+D (Functions > Trace Precedents or Functions > Trace Dependents)
Remove arrows with Alt+M+A+A
Allows you to navigate to connected worksheets -- hit (Fn+)F5+Enter to go back to original cell
Center Across Selection - CORRECT ANSWER Highlight the region you want to center across
Go to Formatting with Ctrl+1 (Cmnd+1)
Go to Alignment
Click Center Across Selection in Horizontal Alignment
Go To Special - CORRECT ANSWER Useful to quickly format constants vs. formulas
(1) Highlight the relevant region
(2) Hit Fn+F5 for Go To menu
(3) Hit Alt+S for Special
(4) Hit Constants and Uncheck Text, Logicals, and Errors
(5) Hit Enter to select all constants
(6) Use Ctrl+1 (Cmnd+1) to add special formatting [Show Less]