6 Free Financial Modeling Lessons...and More.

Enter your e-mail below to get our new Free Modeling Tutorials ($200+ Value).

< previous

Stop Using Your Mouse
 

valuationGURU's picture
CF
 
Points)
 on 10/14/12 at 8:00pm
Excel Help

Moderator note (Andy) This was originally posted on 7/9/12
As summer has been moving along, I begin to notice more and more the inefficiencies out interns have with using Excel properly. Because of this, I have created a brief tutorial on some basic functions of Excel which we use every day. I have attached a spreadsheet with various functions already created within it. Please view the formulas in the cells highlighted and the other notes in the spreadsheet.

As you get better at this, you should not need to use a mouse to navigate through the model at all. Here are some quick tips to help with not using a mouse.

To navigate to the end of a column or row, press control and left or right, down or up. This will shoot you to the end of the data. You may have to press twice or more depending on if there is empty data in between the beginning and end. To highlight this data, press control + shift and left or right, down or up.

Shift + space bar highlights entire row
Control + space bar highlights entire column
… After doing either of those, press Control and + to add a column (or row).

Use Alt+tab to move between different programs (word, internet, excel, etc.)

In order to use the toolbar at the top, press ALT, which will then highlight all the functions at the top. The more you familiarize yourself with these letters, (ex: Alt + H + D + R, which deletes row in Excel 2007), the quicker they will be.

To enter a cell to adjust a formula or just to view which cells are in use, press F2.
Use the F4 function to create absolutes on formulas within cells. Based on how many times you press, you will receive varying functions, ex: $C$2, $C2, C$2. View the Code section of the DATA tab within my spreadsheet to see further. The $ keeps the cell from moving when copying formulas down or across.

When you are in a cell that is pulling data from another tab or another section within the tab you are in, press Control + [ to jump to that cell. You can use F5 + enter to return back to your original tab and cell.

Vlookups, Pivots, and various other stuff is shown in the spreadsheet. Practice understanding what is happening in the formulas and practice recreating some of these sections until you have it down. If anyone has anything to add or if I should go more in-depth in one of these areas, just let me know. Enjoy monkeys!

15% Off wall st. prep Financial Modeling Training Click Here

See my other WSO blog posts
Tags:

Comments

oR3DL1N3o's picture

You have interns that

IB
(Orangutan, 319
 
Points)
 on 7/7/12 at 8:12pm

You have interns that seriously don't know this shit? WOWWwwwwwww. Thanks for this though (+1). I think the kiddies might also benefit from knowing their way around creating data tables if they don't already. I use those on a daily basis... I'd be interested in seeing some advanced excel techniques/shortcuts you might be familiar with?

valuationGURU's picture

oR3DL1N3o: You have interns

CF
 
Points)
 on 7/7/12 at 8:18pm
oR3DL1N3o:

You have interns that seriously don't know this shit? WOWWwwwwwww.

Collectively, no they don't. They know simple functions such as sum and average but most of this stuff they are not 100% comfortable with.

I will have to think about some advance functions I can write a post about. Thanks

See my other WSO blog posts

DMVbro's picture

.

EN
(Monkey, 54
 
Points)
 on 7/10/12 at 5:23pm

.

That_Aston's picture

Helpful stuff=silver banana

CF
(Gorilla, 573
 
Points)
 on 7/7/12 at 8:32pm

Helpful stuff=silver banana for you! Will give this a look to freshen up later.

Here to learn and hopefully pass on some knowledge as well. SB if I helped.

Boreed's picture

CTRL + Page up/down to toggle

PE
 
Points)
 on 7/7/12 at 8:40pm

CTRL + Page up/down to toggle between sheets.

IF(Current Balance>5mn,"Baller Matt","BO")

WSO User Guidelines
WSO Interview Guides

quintessential's picture

it'd be nice to include the

O
(Monkey, 61
 
Points)
 on 7/7/12 at 8:51pm

it'd be nice to include the function or short cuts to remove circular reference for those folks in IBD :)

great spreadsheet TS!
props!

bonks's picture

Circ refs are the worst

CO
(Orangutan, 315
 
Points)
 on 7/7/12 at 9:12pm

Circ refs are the worst things in the world. They're almost always done by accident anyways, they freeze your computer, and they open the Help tab. Nothing pisses me off like a circ ref.

Very good thread, OP. Vlookups are essential

Nothing short of everything will really do.

HarvardOrBust's picture

Alt + T + O --> calculation

PE
 
Points)
 on 7/7/12 at 9:35pm

Alt + T + O --> calculation --> Alt I + space + tab + 1000 --> enter

DaCarez's picture

+1 SB to you for saving some

HF
 
Points)
 on 7/7/12 at 9:45pm

+1 SB to you for saving some interns a few hours during the summer - one thing I'd note though, and maybe this is just showing off - is that you could simplify your nested IFs on the first tab with a MATCH function

=MATCH($C4,$G$5:$G$8,1) in cell E4 and dragged down
Works just as well as that big nasty IF

or if your outputs aren't going to be 1-4, use a LOOKUP (doing it oldschool...)
=LOOKUP($C4,$G$5:$G$8,$F$5:$F$8)

Also could work as a VLOOKUP in this case, but you'd need to switch the order of the columns (ehh...)

mongoose's picture

Thanks a lot.

O
(King Kong, 1,028
 
Points)
 on 7/7/12 at 11:09pm

Thanks a lot.

hdavid57's picture

Thanks for the helpful post!

O
 
Points)
 on 7/8/12 at 12:32am

Thanks for the helpful post! I knew some of the keyboard shortcuts, but not all. F4 was particularly helpful, as was Control + the space bar.

Howard Schwartz

See my WSO blog

valuationGURU's picture

DaCarez: +1 SB to you for

CF
 
Points)
 on 7/8/12 at 12:41am
DaCarez:

+1 SB to you for saving some interns a few hours during the summer - one thing I'd note though, and maybe this is just showing off - is that you could simplify your nested IFs on the first tab with a MATCH function

Good stuff. Learn new things every day.

See my other WSO blog posts

Moneyball's picture

Thanks, this is great!

ER
 
Points)
 on 7/8/12 at 1:14am

Thanks, this is great!

Moneyball
See my other WSO blog posts

Isra's picture

The ALT key functionality is

HF
 
Points)
 on 7/8/12 at 2:03am

The ALT key functionality is one of the greatest efficiency producers in excel.
Its also one of the reasons excel for mac sucks, because they forgot to include that one little detail.

A few tips:
-Whenever you are performing a task that seems like its taking longer than it should on excel, know that there is a formula for it. Google it or ask around, it will save you hours.
-Like the title says, dont use the mouse! You will be much more efficient with keyboard shortcuts
-The ALT key example is misleading - a quicker way to delete rows Control + - (minus sign)

-A few of my favorite ALT shortcuts include:
ALT + W + A (To arrange windows) ---> by far the best one!
ALT + W + F (To freeze panes)
ALT + H + H + N (To remove cell color)
ALT + H + B + N (To remove cell boarders)
ALT + H + B (To add bottom boarder)
ALT + G + G + A (To group cells)

-Other shortcuts:
-F2 (To enter into a cell)
-F4 (To redo last function / absolute)
-Control + 9 (To hide a row)
-Control + 0 (To hide columns
-Control + - (To delete rows/columns - must be highlighted first)
-Control + space (to highlight a column)
-Shift + space (to highlight a row)

Hope this helps

Primeape's picture

Shift + F10: pulls up context

CF
 
Points)
 on 7/8/12 at 3:23am

Shift + F10: pulls up context menu in excel, love this one.

Another favorite, not sure how many of you need this though: alt + e + s + v= paste special values. You can do any of the other paste special commands, the v is just for values. It's a little long, but once you master it, it's a huge time saver.

Great post OP.

See my other WSO Blog posts

damngringo's picture

Alt + M + D - trace

O
(Baboon, 118
 
Points)
 on 7/8/12 at 3:23am
InDerSchweiz's picture

Boreed: CTRL + Page up/down

(Monkey, 40
 
Points)
 on 7/8/12 at 9:30am
blindthoht's picture

Shortcut threads (including

CO
(Monkey, 35
 
Points)
 on 7/8/12 at 9:59pm

couldn't let go of my pair of aces...

valitar's picture

This one needs a bit of

IB
(Chimp, 3
 
Points)
 on 7/9/12 at 1:25am
melvvvar's picture

good job people who don't

(King Kong, 1,693
 
Points)
 on 7/9/12 at 8:24am
EJS123456's picture

Another that I use all the

(Monkey, 33
 
Points)
 on 7/9/12 at 9:07am
Nabooru's picture

Customizable VBA function for

(Gorilla, 689
 
Points)
 on 7/9/12 at 10:03am

Who are you going to believe, me or your lying eyes?

rjr16's picture

This is very helpful. Thanks

PE
 
Points)
 on 7/9/12 at 10:24am
frgna's picture

bonks: Circ refs are the

O
 
Points)
 on 7/9/12 at 11:02am

if you like it then you shoulda put a banana on it

frgna's picture

Also one of my absolute

O
 
Points)
 on 7/9/12 at 11:08am

if you like it then you shoulda put a banana on it

valuationGURU's picture

frgna: Good analysts will

CF
 
Points)
 on 7/9/12 at 11:08am

See my other WSO blog posts

frgna's picture

valuationGURU: frgna: Good

O
 
Points)
 on 7/9/12 at 11:54am

if you like it then you shoulda put a banana on it

GED or Bust's picture

Great thread. Thanks

IB
 
Points)
 on 7/9/12 at 12:22pm
Rupert Pupkin's picture

Good thread. Honestly, this

IB
 
Points)
 on 7/9/12 at 12:24pm
RARORAC's picture

http://www.macabacus.com/macr

IB
(Chimp, 10
 
Points)
 on 7/9/12 at 12:50pm
FutureWaller's picture

ALT F S ALT F S ALT F S

O
 
Points)
 on 7/9/12 at 1:30pm
bonks's picture

frgna: bonks: Circ refs are

CO
(Orangutan, 315
 
Points)
 on 7/9/12 at 2:46pm

Nothing short of everything will really do.

EJS123456's picture

I probably use Paste Special

(Monkey, 33
 
Points)
 on 7/9/12 at 3:06pm
peterg's picture

I enjoy ctrl + alt + down

IB
(Baboon, 140
 
Points)
 on 7/9/12 at 4:07pm
leveRAGE.'s picture

In conjunction with the

ST
(King Kong, 1,161
 
Points)
 on 7/9/12 at 5:19pm
Wasserstag526's picture

oR3DL1N3o: You have interns

CF
 
Points)
 on 7/9/12 at 7:49pm

'We're bigger than U.S. Steel"

kop's picture

Ctrl + ~ Not really a

IB
(Monkey, 61
 
Points)
 on 7/9/12 at 8:43pm
frgna's picture

bonks: Shit I need to get on

O
 
Points)
 on 7/10/12 at 12:20am

if you like it then you shoulda put a banana on it

DaCarez's picture

Nabooru: Customizable VBA

HF
 
Points)
 on 7/10/12 at 1:23am
oR3DL1N3o's picture

Wasserstag526: oR3DL1N3o: Y

IB
(Orangutan, 319
 
Points)
 on 7/10/12 at 8:11am
Wasserstag526's picture

oR3DL1N3o: Wasserstag526:

CF
 
Points)
 on 7/10/12 at 8:15am

'We're bigger than U.S. Steel"

kingtut's picture

The TTS shortcut plugin is a

AM
(Gorilla, 581
 
Points)
 on 7/10/12 at 1:47pm
Wasserstag526's picture

Shout out to Valuation Guru

CF
 
Points)
 on 7/10/12 at 2:39pm

'We're bigger than U.S. Steel"

Spreadsheet_Ninja's picture

Cell C24 on the Random

IB
(Chimp, 13
 
Points)
 on 7/10/12 at 3:35pm
valuationGURU's picture

kingtut: The TTS shortcut

CF
 
Points)
 on 7/10/12 at 4:28pm

See my other WSO blog posts

valuationGURU's picture

Wasserstag526: Shout out to

CF
 
Points)
 on 7/10/12 at 4:30pm

See my other WSO blog posts

valuationGURU's picture

Spreadsheet_Ninja: Cell C24

CF
 
Points)
 on 7/10/12 at 4:31pm

See my other WSO blog posts

bonks's picture

frgna: bonks: Shit I need

CO
(Orangutan, 315
 
Points)
 on 7/10/12 at 6:07pm

Nothing short of everything will really do.

Nabooru's picture

bonks: frgna: bonks: Shit

(Gorilla, 689
 
Points)
 on 7/10/12 at 7:40pm

Who are you going to believe, me or your lying eyes?

DaCarez's picture

bonks: frgna: bonks: Shit

HF
 
Points)
 on 7/10/12 at 10:55pm

6 Free Financial Modeling Lessons...Straight to Your Inbox.

Confirm your name and e-mail below to get our best tips

Monkey Stats
Account information
We respect your Name and E-mail privacy. By joining you accept our terms of service.