- Roll On Through: Create an Iterative List in Excel Without Any Real Coding
- Dynamic Tension! Creating and Using Dynamic Named Ranges in Excel
- Active-X Gonna Give it to Ya
- Your Invitation to the Excel Pivot Party
- Slicing Excel: The Way of The Knife
- Yes...If(And)): Excel’s Mighty “IF” Statement
- Getting Iffy With it: Conditional Statements in Excel
- Taking Out the Trash: Dirty Data in Excel
- Dealing with Dirty Data in Excel
- Doing the Excel Data Dance
- Adventures in Excel: Power to the Pivot
- The Art of Excel Pivot Tables
- The One Excel Formula to Rule Them All
- Excel Showdown: Index Match vs. The Lookups
- How I Stopped Worrying and Learned to Love the Formula
- Excel Data Manipulation: a Guide For Everyone
Alright, so unlike the other two guys, I work in corporate, which means I work with people who only use their computers to send emails, schedule meetings they'll cancel, and occasionally write a memo plus, because they're terrified of anyone associated with IT, they'll never ask for help. As such, I'm forced to do a similar job as my comrades using the tool that everyone has: Excel. Luckily, Excel has nearly infinite functionality for those that seek to explore further... Join me in this first of a series of indefinite length: Excel data manipulation, where we get you hooked on the gateway drug of data analysis, starting from absolute ground zero.
So, for a fitting first entry... how to turn Excel into more than just a fancy calculator.
Step 1: Click file, options, customize ribbon. Then see how there's one box on the right that isn't checked by default that says "developer", check it and see how deep the rabbit hole goes. Congrats, You're now officially more advanced than 85% of people who use Excel.
Step 2: Hit alt (or command...I guess) F11, don't ask why, just do it. This will open up the Excel VB Editor where you can actually engage in making Excel do whatever you want it to. Also, a fun fact for later, keyboard shortcuts are your best friend.
Step 3: Google for whatever it is you want to do followed by the word "Excel," because someone already wrote the code for it. Feel free to copy and paste it without mercy. This is how you learn, this is how everyone starts. Perhaps one day you will be able to write code that will assist another in this journey.
Step 4: After copying the charitable programmer's code, click back to that VB editor write click the name of the sheet you're working on, hit insert, then module, then paste that code in there. Then hit save, and feel free to close the VB editor.
Here's a useful tool to get you started: TEXTJOIN
----copy this whole thing---- Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant) For Each cellrng In cell_ar For Each cell In cellrng If ignore_empty = False Then result = result & cell & delimiter ElseIf cell <> "" Then result = result & cell & delimiter End If End If Next cell Next cellrng TEXTJOIN = Left(result, Len(result) - Len(delimiter)) End Function ----stop copying---
Step 5: Go back to that VB editor, right click the name of the sheet you're working on, click insert, and then paste everything in the last step into the window. Hit save, and close the editor. Don't save it in "Personal" just yet, that's for another time.
Step 6: start with the formula
=textjoin (we'll talk more about formulas later), and watch as a formula that didn't exist before suddenly does because YOU DID THAT!
What this does is take the information in any row or column and combine it with a "delimiter" that you choose (the ";" semicolon is popular for reasons we will discuss later) into one giant cell. No longer do you need to type =concatenate(click, ";", click) 1000 times to combine the cells in a list!
Step 7: Save this file as a Macro enabled workbook, because if you want to use it again, your workbooks now need to be extra special.
Step 8: The next time you need to concatenate an entire table of values for whatever reason, plop that in there, and do what they assume is going to be a full day's work in 45 seconds.
So, I hope that helped just a little bit, and we'll be back next time to learn how to use Excel's overly complex series of formulas to do things that Excel really shouldn't be used for, but who are we kidding? It's all we've got!