Excel Tip #4: Investment Banking and Big 4 Accounting Bare Minimum Excel Basics

In the investment banking industry, analysts and associates are expected to use excel without a mouse. How is that possible you ask? The below excel tips can help you with that. These are the basic fundamental excel hot keys that will help you navigate throughout your excel sheet without a mouse.

Though it is not necessary in Big 4 accounting, it does save you time. You will appreciate these tips in January through March when you are in the busy season.

1. Move from your selected cell to the last cell either by row or column. 
Hot Keys : Hold Ctrl and use the directional keys (left, right, up, or down) 
Comments: If there is data in the cells, this will take you to the last cell with data either by row or column. However, if there is an empty cell in that row or column, this will take you to the first cell after the last empty cell.

2. Highlight from your selected cell to the last cell either by row or column.
Hot Keys: Hold Ctrl and shift then use the directional keys (left, right, up, or down)
Comments: Same rules apply as the shortcut above except instead of jumping to a cell, this highlights it.

3. Add an additional column or row 
Hot Keys: Hold Ctrl and shift then press +
Comments: From the “insert” box select whether or not you want to add the ‘Entire row’ or ‘Entire Column’. Pressing “r” and hitting ‘Enter’ adds entire row. Pressing “c” and hitting ‘Enter’ adds an entire column.

4. To use the toolbar at the top 
Hot Key: Hit Alt 
Comments: This will highlight all the functions at the top. Then it is a matter of time before you familiarize yourself with the different hot keys to navigate within the above toolbar. For example, if you hit “Alt” then “r” and finally “c” then you will add a comment box. 

5. To view/alter a cell’s formula
Hot Key: Hit F2 
Comments: Additional tip for creating absolute functions – Hit F4. This will help you cycle through a number of formulas. Remember the $ means that the coordinate is absolute even as you drag the formula across cells or copy the formula into another cell. 

6. Trace back a formula into another cell or tab. 
Hot Key: Hold Ctrl and then press [ 
Comments: This is particularly useful in determining where the formula is pulling from and prevents the need of toggling back and forth just to see where the formula is pulling from.

Read Full Post »

Windows Quick Tips Shortcuts

Anything to shave a couple seconds from your work day. Of the below, I use the ‘windows key + M’ shortcut the most.

1. Ctrl + Drag File
Makes a quick copy of the file/folder

2. Windows key + M
Minimizes all open windows

3. Alt + double click file/folder
Pulled up the ‘System Properties’ dialog box

Read Full Post »

Excel Tip #3: Why does my VLOOKUP return #N/A?

VLOOKUP FAIL


Have you ever had an Excel formula not work, but you’ve checked the formula multiple times already? You are certain that the formula is correct, but excel returns back #N/A or a blank. Do you wonder what is going on?

Due to Excel’s stupidity, the format of your data you want returned has to match the format of the data you are pulling from. From this is not the case, your vlookup will return a bunch of retarded #N/As or blanks.

For example, let’s say you are looking for the purchase order 6555 in the table below, and you want to return the amount from column 3 of that table. For one reason or another you may have extracted the below information from a PDF by saving it into notepad and then using the Text to Excel conversion to convert it to Excel. Somewhere along the way the format of the data went haywire.

Today is your lucky day, there is a solution. Find a blank cell and type in any number. Then copy the cell and paste special over the problem cells using past ‘values’ and ‘multiply. Now the number that Excel though was just text (or some other crap) is now a number that vlookup understands.

Read Full Post »

Excel Tip #2: How to Set Up an Automated Table of Contents in Microsoft Word

Three step process to setting up an automated table of contents in Microsoft Word

1) Type in the document the “title” (Prologue) of the first page of your table of contents
2) Highlight the title and click on the “Home” tab, in the “Styles” section select “Heading 1”
3) Click outside of your “title” and click on the “References” tab, in the “Table of Contents” section select “Table of Contents”

Poof! You should have something like this:

Contents
Prologue……………………………………………………………………………………………………………………1

Read Full Post »

Excel Tip #1 – Copy/Paste Sum of Selected Cells

I’ve booked about three hundred hours plus of work in March. This is not by choice, it’s just the nature of the business, but I am always looking for ways to be more efficient. As an auditor, there are a lot of menial tasks that can be eliminated with macros in excel files. We are interested in totals and it’s a pain to have to copy a set of numbers into another worksheet and sum them up to get the number you need. Typically when you highlight a set of numbers the total will appear on the bottom left hand corner. I wanted to be able to copy that total directly and paste it in a separate spreadsheet. 

Here is a macro that will help you do that. 
Refer to the below five step process: 

1) Hit Alt + F11 (this will open the VBE Window) 

2) Select ‘Module’ from the ‘Insert Menu’ and paste the code below. 

Sub mySum()

Dim MyDataObj As New DataObject

MyDataObj.SetText Application.Sum(Selection)

MyDataObj.PutInClipboard

End Sub

3) Select ‘References’ form the ‘Tool Menu’ and make sure ‘Microsoft Forms 2.0 Object Library’ is selected. If it’s not listed then click ‘browse’ and select ‘Fm20.dll’

4) Close the VBE window

5) Hit Alt + F8, select ‘mySum’, click ‘Options’ and set your shortcut key. 

Read Full Post »