.png)
It’s almost December! That means the end of the year, and for most, the start to the “clean up” month. It’s that time you look forward to during the summer, when things are slammed, yet feel overwhelmed when you see the volume of cleanup work ahead of you.
To help you with that problem, over the next few weeks I’m going to explain five life-changing, time-saving functions in Excel that will make you wonder how you survived without them. Those functions are VLookup, Concatenate, Text-to-Columns, Subtotal, and basic Macros. You may have heard of or even tried some of these for yourself, but most of these functions have several applications.
As part one of the six-part series I’ll be sharing with you on Excel function must-haves, let’s first get your data clean and organized! Even the coolest functions don’t work if you haven’t prepped your data. Think of it like primer before you paint – everyone tries to go without it at least once, but halfway through that third coat of paint, you really wish you would have just primed it in the beginning.
The key to prepping your data for cleanup is conformity. Every column needs to be consistent from top to bottom. That means all numbers are formatted as numbers, dates are formatted as dates, and alphanumeric fields all follow the same format throughout. Since we’re talking Oil and Gas, I’ll use some common O&G examples – Section/Township/Range formatting.
If you have a single column that shows your Section/Township/Range, the format must be exact. Is that lease in section 2 or in section 02? Or even 002? You may think those all mean the same thing, but Excel doesn’t think so. When Excel sorts, it starts with the first number, then moves on to the next number. That means, without consistent formatting, you will get lists like this:
Even though all of the leases are in 9N9W, the sections with leading zeroes show up first, then the numbers, based on the first digit of the number. So, section 11 comes before section 2, and there are entries for section 2 as well as section 02. If you tried to create a summary based on this data, you would have several duplicate sections, or very inaccurate totals.
Decide which format you’ll be seeing the most, and make your data conform to that version. It’s not hard to add or remove zeroes, but if your data mainly exports as 02 instead of 2, you might as well stick with that format and make the outliers conform to it, rather than having to reformat the main bulk of your data every time you export it.
Once your columns are consistent, go ahead and sort the data. This will make double-checking easier, as well as make any incorrect data easier to identify. Using the same data set from above, let’s say you know that you have four leases in section two. Which example is easier to see whether you’ve accounted for all your leases?
It’s much easier to see, on the right, that there are only three leases listed in section two. While your data set may be far too large to visually check each section, making your data conform to identical formats will make any anomalies much more obvious.
Now that your data is beautiful, SAVE your workbook! As you build formulas, frequently SAVE your workbook. There is no spreadsheet function more heartbreaking than when Excel decides to shut down and restart while you’re working. SAVE FREQUENTLY. Maybe turn on your auto-recovery feature, too. Not sure if it’s on? Go to File, then Options, then Save:
There! Now your data is prepped! It may seem tedious at first, but these steps will become second nature. Up next? Vlookup!
Need some help with the basic cleanup steps, such as filtering, adding/removing leading zeroes, or getting fonts, colors, and borders under control? Drop me a message, and I’ll be happy to walk you through it.
Commentaires