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:
In Cell, B1 type the following formula:
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:
Step 1: In column A, number your rows consecutively. (Use the formula highlighted to help you do it).
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).
Step 3: Sort on Column A from smallest to largest.
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 😉
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:
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.
**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.