There’s nothing better than releasing the final version of your workbook – every bit of sacrifice, blood, sweat and coffee stain for the world to bask in its majesty, for small children to sing songs about and draw pictures of in kindergarten classrooms. Having finally realized Maslow’s theory of self actualization in this single project – you are the workbook messiah.
The real question is
“Are you eating a five star dinner on a table with a broken leg?”
What does this mean? It means you can put forth a great front end experience but if the building blocks are unstable it will be the only thing that matters when it comes to managing your finished project. Too often people are working harder, rather then smarter with managing their data, so right now I ask that you place your right hand on the workbook and repeat after me:
MsgBox “I ” & InsertNameHere & “do solemnly swear from this day forward to adhere to the laws of fields and records.”, vbOKOnly
The first step in correcting our problems is in admitting them and being given the tools to a successful workbook recovery.
What are the laws of fields and records?
Fields – commonly shared values – think of dates, state abbreviations, employee names, movie titles, authors – identifiable items that can be easily pooled together under a shared theme.
Records – individual data entries under each field type
How do I apply these laws?
1. First things first – no matter what it takes – and let me repeat this – no matter what it takes – for long term management in reporting you must find the commonalities within your data to get them into fields and records. If you can not find the similarities you should be using Microsoft Word because you are doing text editing not data analysis.
2. From this day forth the sky is blue, grass is green and data moves top to bottom. There are too many valuable functions you are leaving off the table (pun intended) if you are managing your data from left to right:
- Data Filtering
- Data Sorting
- Removing Duplicates
- Pivot Tables
No. Just No.
3. Getting your data into order is a lot easier than you think –
- Highlight and copy the current records
- Select a new cell and right click for paste options
- Select Transpose
4. Unlock your data’s true potential – convert your range into a table object
Learn it first –
- Highlight the cell range
- Go to the Insert tab and select table
- Ensure “My table has headers” is selected and press ok
Master it second –
- Ctrl + T
- Ensure “My table has headers” is selected and press ok
If you’ve made it this far I want you to stop, read again and apply the laws we have set forth on the example below because you are on the brink of your first big breakthrough in Excel.
Let’s take a quick breather, refresh our drinks and tell our loved one’s good bye because the next lesson is not for the faint of heart.
Hey there is better way to convert this “horizontal data-set” to a proper vertical tabular data-set format using pivot table to convert it. For this I would like to share with you the excel file so you can review it. Let me know what email to send it to. I think it will a good a blogpost for you write about 😉 Regards from Lima, Peru. Anybody else who would like it, please shoot me an message thru LinkedIn.
I’m all about finding new and better ways to do things. I’ve been able to tinker around with Power Query to do this as of late but given the fact that it’s an Add-In the majority of people may not have access to it in the workplace due to IT restrictions (BOO!). Shoot me the workbook over to – alexmpowers@gmail.com I would love to see what you have going on under the engine.
I guess i needed to revisit this article just for the laws of data proposed here.
Nice one mate!
Although your sugestion about the manipulating the data into tabular formart is nice, nothing – within Excel tools — (as of now) can beat PowerQuery, when it comes to these type of tasks!