Microsoft Excel Table Structure Made Easy
4 No Blank cells, column, rows & Merging cells
In the previous article, we’ve talk about why you should plan ahead of the structure and design of a table. Also, the importance of keeping data in its column plus the after effect of having stacked headers. If you haven’t already read it, please go ahead and read it if you’re curious or having some issues with lists or tables.
Merge Cells
Blank cells
In this article, I will be talking about why merge cell and blank is one of the deadly sins! Excel has a lot of built-in intelligent in it, and it automatically detect the range of your data base on your current active cell is. In order for us to fully utilize the intelligent you will have to provide appropriate information in the right structure.
The blank cells could cause some of the functions or commands fail to work properly. Whenever there is a blank cell appear within a range, Excel will automatically assume that’s the end of the range but sometimes the cell just happened to be blank. Later I’m going to walk you through all the disadvantage of having blank cells.
Planning ahead is an extremely important task. The planning involved knowing what you need to record, how many columns you will be needing, what sort of report you would want to analyze down the road. You must know what goes into each of the column, in what situation what keyword will be used to represent the certain situation. All these activities that are being recorded and collected stores the trail of history. You definitely want to look back the records and understand what went on and the timeline. Using all these data you will be able to find the trend of the activities. Leaving blank cells will make you confuse what went on during that particular activity and why is it blank, and that will cause inaccurate analysis.
If you do notice, Excel will automatically detect the ranges of data that you have in the worksheet. When number related function like SUM() function, when it is activate through AutoSum command, it will sum the range of cells that contains number only. When Excel detects the content within the cell that cannot be used to sum, it will stop the selection.
Whenever a blank cell is detected, the selection stops as well.
Merge cell is a havoc and that one tool that I wish Microsoft will abolish in near future. I understand how sometimes we wish to tidy up the worksheet, placing as little data in the sheet so it is easier to view. But you have to understand, merging cells does not keep all the data from the selection. Only the cells of the most top left will hold the data only.
This is the warning dialog box that Excel will pop out whenever it detects the user attempt to merge some cells together. If you were to proceed with the OK button, I will see you in hell then . You must be curious why is the author so angry about merging cells. If you must know, the warning is letting you know, all the other cells will contain no data but only the most left cell. All your analysis result will no longer reliable nor accurate.
Check this out, when cells are merge versus cells aren’t merge.
With no merge cells
With merge cells
Once all the cells are unmerging or being covert into a table form, the rest of the cells has no value besides the most top left cell.
Unmerging cells
- Ctrl + T (Converting current range to table)
- Ctrl + A (Select All) and de-select Merge & Center commandThe worse part having blank cells within the range or table, big chaos is awaiting you when you use Pivot Table to summarize them. Pivot table treat each column as one single field. So, keeping the data type within the same column is utmost important. When a blank cell is found in a column that suppose to contain all numbers, Pivot Table get confuse with why a blank cell is in there. This is the reason why you never get to summarize certain number field like Sales with SUM function, Pivot Table uses COUNT instead because it confuses with the data type of that particular column.
Solution for blank cell
This is fully depending on what you needed to be record within the cell. Select a keyword that represent the current situation and add is as a remark, so that when you look back those records, you’re well aware of the progress or status.
If the column supposed to contain number, do not leave it blank, fill it in with a zero. And perhaps you could add in an extra column to explain why certain transaction is filled with zero.
Solution for merge cells
After unmerging all the cells, follow the instruction below so you will be able to fill in the blank cells by referencing to the most top of the select blank cells.
- Ctrl + A : Select All
- Apply keystrokes F5 à Special à Blanks à OK
- Type “=” and then use the up-arrow keystroke from the keyboard. What is does is, it will pick up the value by referencing to the cell above of the selected cells.
- Ctrl + Enter : So all the selected cells will be able to inherit the same formula.
- Ctrl + A : Select All
- One last step, copy and paste everything as value so the reference is now left with value only.
Learn Microsoft Excel training with Info Trek and turn data into insights. Enhanced by intelligence, Excel learns your patterns, organizing your data to save you time. Create spreadsheets with ease from templates or on your own, and perform calculations with modern formulas.
