The Top 10 Excel Tricks Every 1st Year Needs in Their Arsenal (VIDEO)


From the mailbag:

Hey Andrew,

I wanted to follow up with you in regards to the webinar yesterday. I really enjoyed it. I recently discovered The Bean Counter and am really glad that I did. I really like all of the information that you put out and I am looking forward to continue following you.

Little background about myself, I just graduated this past December in accounting and went immediately into a busy season internship at KPMG. It was ok but I decided to pursue other opportunities and will be joining a regional accounting firm in Atlanta this summer.

In the meantime, I am trying to get as much of the CPA exam out of the way as possible. I am starting with FAR and I am progressing through the material much slower than I thought I would. I was expecting to wiz though it like all my friends did. Any tips on what I could possibly do to increase my speed or should I be okay with moving at the slower pace?

I could also use your advice relating to transitioning into a professional. I was given feedback from my KPMG internship that I could learn to utilize excel more (i.e. I work too slow). I purchased a, excel, “teach me” course but it seems to cover the basics only. Do you have any tips on what I could do to increase my work speed overall?

If you have not noticed, there is a correlation of slowness about me – studying for FAR and my work speed. It is an overall skill that I really need to improve upon and not just in one particular area (i.e. work, studying, etc).

Once again, enjoyed the webinar and glad I discovered you. I look forward to getting some solid advice from you.

KMFHD (keep my effin’ head down) Studying,

Bean Counter Reader

This is a great question and in accounting this is a tough balance for a lot of people. You want to be right, but you need to ACTUALLY take the CPA exam and actually complete work papers! I tend to lean towards speed rather than accuracy in my first year because no matter how long you take, you are going to get things wrong! Better to be wrong and done in one hour than wrong and done in three!

You should start here for how long you should be studying for each CPA exam section. Today I want to cover the top 10 excel tricks and shortcuts I learned to speed up my work in the first year of public accounting:

1. Alt + Tab

This allows you to skip between different programs or difference instances of the same program rapidly. It works with excel and all other programs. Rather than taking your hand off the keyboard, grabbing the mouse, dragging the mouse to the bottom of the computer, and clicking around to find your program, use this shortcut!

2. Shift + Control + up/down arrow

This will help you move between pages in the spreadsheet rather than having to click through each one.

3. Alt + e + s + v

So many times you want to paste something in excel, but it’s a formula. If you paste it, it gets all screwed up. Instead, copy it, then hit “Alt + e + s + v” and you will paste the values of the previous cell without copying the formula. This is a amazing quick trick!

4. Print formatting in excel

While I didn’t use this often, there were some partners that required all the workpapers they needed to review to be printed out. If that’s your partner, watch this video!

5. Filtering Colors

6. Vlookup

7. Ctrl + Arrow Key (Excel)

This will help you jump around the worksheet much faster than using the mouse. Watch the video below to see what I mean:

8. Highlight Duplicate Values

9. Alt + F + D + E (varies slightly by firm)

I can’t demostrate this one for you as I don’t have my old PwC laptop! But I used this every single day. If you use this shortcut, whatever document you are currently in will be attached to a new mail file to be emailed. Rather than searching for the document on your hard drive after you create a new message, this skips through that and allows you to start writing the recipients, subject, and body with the file already attached.

10. Subtotal Function (VERY COOL)

Email me if you have questions on any of these functions or want resources to better learn excel tricks! –

  • Adria Jordan Kitchens

    Awesome article! I’m going to watch all of these and I use excel all the time. Thanks!!

    • TheBeanCountercom

      Glad I could help! Let me know if there are any good excel tricks you know that I missed!

  • Savannah Camp

    My current favorite excel short-cut is: ATL+; it allows you to copy only whats visual on the excel, or copy information into the visual cells. Comes in handy when you deal with lots of filtering for data.

    • TheBeanCountercom

      That’s an awesome one! Thanks for sharing!

  • Kevin R. Elliott CPA

    knew most of these but I like the last one will be trying it out in my work today!!!

    • TheBeanCountercom

      BOOM! Glad it helped Kevin! Hope you’ve been well!

  • Jennifer Eden Harvey

    The email attachment alone is going to save me SO much time! Thanks!!!

    • TheBeanCountercom

      That’s my favorite too!

  • JLB

    I just tried the Shift+Ctrl+up/down arrow.. it didn’t work to switch worksheets, it tried to highlight various sections of my worksheet. ?!

    • Mark

      Shift+Ctrl+up/down arrow highlights blocks of data (it stops where there is an empty cell). I use this hundred of times of day. Another shortcut I use constantly is one that I think Andrew meant for this. Using Ctr+page up/page down switches between tabs.

  • George

    Concatenate and left, mid, right for manipulating text from multiple columns to 1 and vise-versa are useful for TB imports.

  • Fernando

    Great article! I really enjoyed the information on Vlookups.

  • scots ahoy

    What is the command to select and go from column a to Ee then down to row 10000

  • Mark

    Another shortcut I use constantly is “Alt+=.” This activates the Auto-sum feature.