Excel Tips for excelwizards

I’ve been hoarding a few neat excel tips over the last few months, so I thought I’d share them on a blog post.

TIP #1: A Tricky LEFT/FIND Combo Formula

Have you ever wanted to get everything to the left of a specific character (ex: dash, or colon) when the number of characters is not consistent? If you use Text to Columns, sometimes what’s left gets a funky formatting and then you’re data is incorrect. So here’s a neat formula to solve for that:

Assume I have the following in Cells A1 and A2:

LEFT Formula (A1)

In Cell, B1 type the following formula:

LEFT Formula (Formula).jpg

Cool excel wizardry at it’s finest.

TIP #2: A Sneaky Way to Add an Empty Row After Each Row of Data

Imports usually read a row at a time. Unfortunately, that means you need the first row of a journal entry, followed by the second in rows 1 and 2 of your Excel workbook. What if the export file has the debit and credit in the same row? Or just 1 single column for amount and you need to add the second row?

Sure, you could insert a row after each line of a 1000 row file, but do you really hate yourself that much? I like you, and your time, so I’ll share my magic with you 😉

Imagine you have the following file:

Insert Blank Line (File)

Step 1: In column A, number your rows consecutively. (Use the formula highlighted to help you do it).

Insert Blank Line (Number Rows)

Step 2: Copy the entirety of Column A and paste it also in Column A, below your last row. (When you copy and paste, if you used the formula, make sure you right click cell A7 and choose the clipboard with 123 to paste the values and not the formula).

Insert Blank Line (Copy Numbers).jpg

Step 3: Sort on Column A from smallest to largest.

Insert Blank Line (Sort).jpg

Step 4: Go get yourself a coffee and tell people you’re really busy formatting an Excel file to add empty rows between each row of data 😉

Insert Blank Line (Final).jpg

Helpful Tip: Copy anything that will be the same in row 2 (the blank row) when you copy the numbers, before sorting, save yourself any extra formatting. In my example, I might want to copy the amounts into the DEBIT column for all the blank rows I’m creating.

TIP #3: Auto-Number lines of a Journal Entry/Transaction File Using IF

I can’t count the number of times I’ve used this formula recently. While it’s incredibly simple, it’s a nice little time-saver.

Let’s say you have the following non-numbered Journal Entry:

Line Numbering (No Formula).jpg

You would like to start at 1 in cell D3, and number consecutively until you reach D8, where you’d then like the numbering to restart at 1 again.

Put the number 1 in cell D3, then use this formula for all cells in the column:

Basically, the IF formula checks the date of the current row to the date above it, if they match it takes the cell above it and adds 1. If the date is not the same, it assigns the number 1 to it.

Line Numbering (Formula).jpg

**Use the two tips above in combination (with a little removal of duplicates on the second step of copying and pasting the numbers from Tip #2) to add an empty row after each group (journal entry) to really blow someone’s mind. Or better yet, don’t tell anyone, let them marvel at how fast you are and give yourself an extra few minutes for some coffee.

Advertisements

INDEX – MATCH : VLOOKUP’s powerful brother

Now that you’ve mastered Vlookups (here) – and you probably got the job (woohoo!) – I thought it might be a good time to introduce Vlookup’s more advanced, powerful, and flexible brother: INDEX/MATCH.

While Vlookup is more popular, INDEX/MATCH is kind of like Vlookup’s older, wiser brother who’s in college – maybe he’s not as popular but he’s way more powerful.

In this post I’ll go over how to use INDEX/MATCH and a really cool application. (The example is similar to one of the first spreadsheets I created. It gets a lot of “oohs and ahs”. It’s pretty magical (but easy to create!)).

Soooo .. what is it?  Continue reading

Pivot Tables – the last piece of the interview puzzle

Happy New Year! I hope it was a great holiday filled with family, cookies, and lots of love. Hopefully Santa brought you everything you were hoping for. I thought I would start the year off by giving the gift of Pivot Tables! (Maybe not quite as great as the shiny, new Tesla wrapped with a gigantic, red bow that Santa left in your driveway  -but I’m sure it was a close second on your wish list).

It’s been a little while since my first post when I promised to teach the three components of Excel (VLookups, Macros, and Pivot Tables) that would set you apart in an interview. (Sometime before those great food and cookie coma’s of December). So let’s finish off the trifecta and get the job.

What are Pivot Tables? Continue reading

Magical Macros

Remember when I promised to introduce you to the wonderful, magical world of macros?

I told you that if you really want to impress your interviewer, you should drop 3 key terms: Vlookups, Pivot Tables, and Macros. So you just explained Vlookups to your interviewer -they were pretty impressed! Then they heard the word “Macros” and their eyes widened like you just placed a 10 pound turkey in front of them while juggling fire. Obviously they’re going to ask you about it. I want you to be able to not only name drop, but be able to back that up with knowledge of how it works and how you can use it in your job to kick butt.

I hope you’re prepared for your relationship with Excel (and probably life too) to change forever … because this is it! I would like to introduce you to The Magical World of Macros… Continue reading

I just graduated! – The real world of VLOOKUPS

So you just graduated from college? Congratulations! If you’re like me, I hope you had a fun summer off! So now you decided it’s time to get serious. Time to start looking for your first real job. Scary stuff.

When I first started on my job search I had the most successful person I know (my millionaire uncle) look over my resume. I was looking for a job in a similar field (finance / accounting) but on the opposite coast. He told me right away, key words to use on your resume that will instantly get you recognized and out of that dreaded stack of college grads is *drum roll* EXCEL! He instructed me to learn three things right off the bat: VLOOKUPS, Pivot Tables, and Macros. He said “those are really easy to learn – do a quick google search – drop those in your resume or phone interview and you’ll stand out immediately”. Those words were like some foreign language to me at the time. Looking back, I wish I had an easy tutorial (like this one *nudge* *nudge* *wink* *wink* get the hint?) to tell me all I needed to know. If you haven’t guessed already, I’ll be covering the basics of VLOOKUPS in this blog post. (Part two will be macros – which is my personal favorite and current addiction – I’m an excel geek).

1. What is a VLOOKUP? Continue reading