Easy Microsoft Excel Data Alignment Tips
In this series of articles, I will be talking about things you shouldn’t be doing in Excel. No, you will not become power user after reading them. But, you will definitely have more understanding and be able to manuever Excel easier. Follow me around and I will make you realize there are a lot of things you’ve been doing may not necessary is good, and of course I will follow with some solution for a quick fix.
I once read this article from an author Kay Boogaard, she too wrote about thing you should know about Excel. In the article she mentioned that she met her husband who he converted her from a person who would rather lock themselves in the phone booth full of mosquitos than having her eye crossed starring at the rows and columns in Excel into an Excel lover. People start hating Excel reason is because they do not understand why Excel behave in such and things always turn out the other way around. I can totally relate that because majority of us are self-taught Excel user. And when you are a self-taught user, you may not necessary learn or use it by googling “Excel 101” or “Do’s and Don’ts with Excel”. You dive right into the worksheet, and when things don’t seem right, you google it only when you’re in trouble.
Totally can understand, not everyone knows where to start. Lucky you, you have come to the right place. I have gone though many situation and I’m going to share all of them with you. It’s gonna be quite a series, bear with me and you may start thanking me when you’ve done reading.
Excel is an extremely powerful tool only if you use it the right way. Let’s dig in….
Don’t! Do not align any of your data
I think everyone is familiar with alignment. If you’re not clear still, look at the image below, those are alignment command.
Yes, say no more to alignment. While you’re in Word dealing with paragraphs; in PowerPoints dealing with points, it is utmost important, definitely NOT in Excel. Well, you need to start learning how to communicate with Excel. Every single click, drag, entering data, scroll is a form of interaction with Excel. And of course, Excel responded to us every single interaction we have done with it. Sometimes Excel shows us errors or dialog box when we did something correctly. Believe me or not through alignment, Excel’s telling us whether it understand certain things that we’ve entered into the cell or not.
So, by default excel recognizes 2 different types of value entered by human being, values and labels. Try this, open a new sheet with no alignment applied, simply pick a cell and enter “apple”. Then, the cell on the right side of apple enter “5”. The result you get if you going to look like the figure shows as below.
Do you realize that apple is being align to the left while the 5 is align to right? Yea, so through alignment Excel is letting us know, which is value which is label. In short, Excel simply treat label as string. String is the combination of alphabet, numbers and symbols which Excel simply treat them as label. When the value is aligned to the right side in a cell, Excel is telling us that they can use that for calculation. But once you have forcefully aligned all data to left, centre or right, we simple destroy the way Excel’s telling us which is label which is value.
The solution for aligned data
To remove all the alignment, click the select all command. you can find it at the top left corner of the spreadsheet.
Then select the right align command twice. The reason being is because the list might contain some column which is align, some might not. So, the first time selecting the right align is to forcefully align all data to right alignment; while the second time is to remove the alignment.
Solution for text number
If you have had experience working for different company, you should realize different system might analyze data differently. Sometimes when you export a list from an external source, Excel get confuse with the exported data. But most of the time, with the built in intelligent in Excel, it will try to guess what are the data that is being store in a cell.
Have you ever seen the small green indicator shows at the left top side of a cell? That an indication of Excel telling us something. Besides all the numbers in the Qty is align towards the left, because Excel is treating the data in its column as label/string.
Select the indicator and see what Excel says.
Excel realize the data within the cell are number, but because the source is from external and Excel wasn’t sure what to do with it. Hence it says it you have numbers that are being stores as text, would you want to act by convert it into number. You can choose to ignore or select convert to number. If that column is not meant for calculation, you can definitely choose to ignore the error by selecting “Ignore Error”, else select “Convert to Number”.
Reason being there are a lot function in Excel will ignore texts and errors by default. Example your favourite SUM function.
You can either use the function VALUE() to convert all text numbers back to number, or highlight the whole column and from the indicator, select “Convert to Number”.
Solution for text number using VALUE function
Insert a column if needed right beside the column of the Qty column.
reference towards the cell containing the text number. Fill the formula Deadly sin.1downwards.
Always remember while you’re cleaning data as such, you will have to copy and paste the column as value. So, the column does not need to depend on the referenced column. Instead it is holding its own value only, not formula with a reference in it.
With Microsoft Office Excel 2016, learn to track and visualize data for better management and insight of large amounts of information. Learn Excel or Excel online now to see what simplifying data management can do for you.
