Excel Crash Course ;Wall Street Prep ACTUAL EXAM
NEWEST UPDATE WITH CORRECT EXPERT VERIFIED
ANSWERS FOR GUARANTEED EXAM EXCELLENCE |
ALREADY GRADED A+
Inserting & Deleting Worksheets
Add a new worksheet by pressing Alt+H+I+S
Delete a worksheet by pressing Alt+E+L
Grouping & Hiding Columns
(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
(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
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
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
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
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
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
Distinguishing Constants
Constants are usually distinguished from formulas, such as by blue text vs. black text
Identifying Where Blanks Are
(1) Highlight the relevant region
(2) Use Go To Special and select blanks
(3) Hit Enter
Identifying Where Comments Are
(1) Highlight the relevant region
(2) Use Go To Special and select comments
(3) Hit Enter
Bottom Bar Customization
Right click on the bottom bar to change what you see from your selection (ex. Average,
Count, Max, Sum, etc.)
Conditional Formatting
Allows you to create your own conditional formats or use a preset from Excel
,Alt+O+D or Home > Conditional Formatting > New Rule / Alt+H+L > New Rule for
customs
Alt+H+L or Home > Conditional Formatting > Highlight Cells Rules for presets
Identify which numbers in a column are above some number
(1) Highlight the relevant range
(2) Hit Alt+O+D
(3) Select "Use a formula to determine which cells to format"
(4) Write "=" and select the first number in the column
(5) Anchor the column
(6) Write ">" and some number or cell that is your reference
(7) Press Enter
Ex. "= $C3>500"
And Functions
=and([first statement],[second statement)
Dynamic Headers & Text
Name: ="Income Statement for "&[Cell]
Date: ="Share price as of "&TEXT([Cell], "mm/dd/yy")
Custom Formatting
(1) Type the number
(2) Go to format tab
(3) Go to "Custom"
(4) Type in your custom format as [positive numbers];[negative numbers];[zero];[text]
Multiple: 0.0x_);(0.0x);@_)
1 = True/0 = False: "True";"Invalid";"False"
n "Years": 0 "years"
Insert Line Breaks
Alt+Enter (Option+Enter)
Custom Format: Aligning Decimal Points
Add "_)" after and "_(" before the positive numbers and zero formatting
Ex. _(0.0_);(0.0)
Custom Format: Adding a Comma Separator
Add "#,##" before the zeros
Ex. _(#,##0.0_);(#,##0.0)
Custom Format: Aligning Non-Multiple Numbers with a Multiple
Add "x_" after the end _ for positive numbers and after the closing parenthesis for
negative numbers
Ex. _(#,##0.0_x_);(#,##0.0)_x
Custom Format: Negative number in parenthesis, aligned with positive number format,
"Balance" when result is 0
#,##0.00_);(#,##0.00);"Balance"
Custom Format: Negative number in parenthesis, aligned with positive number format
#,##0.00_);(#,##0.00)
Custom Format: Multiple "x" format. Negative numbers in parenthesis, aligned with
positive number format
_(#,###0.0x_);(##,##0.0x)
, Custom Format: Negative number in parenthesis, aligned with positive number format
and multiple "x" format
_(#,###0.0_x_);(##,##0.0)_x
Custom Format: L + [] Basis Points
L + 0 "bps"
Custom Format: Changing the Color of Negative Numbers to Red
Add [red] at the beginning of the negative numbers format
Ex. _(#,##0.0_);[Red](#,##0.0)
Most Common Excel Errors
#DIV/0! Divided by zero
#REF! Referencing a previously deleted cell
#NUM! Number not valid
#NAME? Text not valid (ex. incorrect function name)
#VALUE! Incorrect arguments (ex. Using text as a number)
####### Column not wide enough
Find and Replace
Ctrl+F
Write what you want to find
Click Replace
Write what you want to replace
Use Options to restrict where you're searching, match the case, etc.
Note: You can replace references in formulas as well by finding "[cell name]" and
replacing with "[new cell name]"; Ex. Find "j7" and replace with "b2"
Preparing a Page for Printing
(1) Go to Page Layout > Page Setup
(2) Change to Fit to 1 pages wide by [however many you want] pages tall or change
from landscape to portrait
(3) Change the margins and center on page horizontally and vertically in the Margins
tab
(4) Add custom headers and footers in the Header/Footer tab (page numbers, date,
timestamp, file name, etc.)
(5) Limit the print area in the Sheet tab, or add rows to repeat at the top of each page
What are all the ways to trace precedent cells?
Ctrl+[
Alt+M+P
Alt+T+U+T
Create a Dropdown Menu Command
Alt+D+L
Change Text Color
Alt+H+F+C
Change the Cell to a Number Format
Ctrl+Shift+1
Functions
Pre-built combinations of operations in Excel that facilitate spreadsheet analysis
Structure of Functions
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller Bestnursesteve. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $17.99. You're not tied to anything after your purchase.
