Introduction
Data formatting refers to the process of changing the appearance of data so as to make it visually appealing or easy to read and understand. One can perform data formatting tasks on text, numbers or even dates.
The following are the most common types of data formatting:
1. Text formatting
This involves changing appearance of text in your data. We can use excel functions such as UPPER, LOWER and PROPER.
Upper()
This changes the text to uppercase. For instance, when one wants to change the content of one particular column to uppercase, we first of all need to create a helper column. This is done by inserting a column to the right of the column we want to modify. Then on the second cell of the helper column after the header cell we need to write =UPPER(cell reference) then press enter. Cell reference refers to the second cell in the column we want to modify(The cell after the header). For example, one can navigate to cell B2 and write =UPPER(A2) where A2 is the cell directly below header of column A while B2 is the cell below the header of helper column.
This will display the uppercase equivalent of column A items on column B. The final step is to copy the items of column B to column A. Select column B, click copy then put cursor on column A and choose paste values option. After this you can now delete column B. Note that there is no shortcut to go about this, if you try deleting column A in order to remain with data in column B this will not work since column B is a reference to column A.
Lower()
Converts text to lowercase. To use this function we follow the same steps as we did in the UPPER function but in this case we use the formula =LOWER(cell reference).
Proper()
This modifies our data by converting every first letter of text to uppercase then the remaining letters are converted to lowercase. For example, when we have the word emma and we apply PROPER function on it this word becomes Emma. To use it we follow the same steps we used as in the previous functions while we only change the formula to =PROPER(cell reference).
2. Date formatting
Sometimes we receive data that has date values but the way the dates are written does not appeal well with us. To change the way your dates appear, select the first cell in the column you want to modify. While in your excel home screen, navigate to the number format dialog box then click the arrow on the lower right corner. This will give you a dialog box where you can choose the type of date format you want. Select the format you want then click ok. The new date format will appear on the cell you selected. To apply this to all the other cells in the column, drag the mouse downwards or double click on the flash fill icon on the right of highlighted cell.
3. Removing whitespace and unwanted space
Find and replace
Select all the data in your worksheet. Navigate to excel home then click find and select tab on the ribbon. This will give you a dialog box. In find what box put double space. You can use a tab key or backspace. In replace with box put a single space then click replace.
Trim()
When we want to only get rid of a single space, find and replace method will not be effective. This is where the TRIM function comes in. To use this function, we need to follow the same steps we used when using UPPER function the only difference being the formula used. Here the formula will be =TRIM(cell reference).
Kindly note that the above examples apply when we want to modify data in columns. If you want to modify data in rows, the same steps can be followed only that in this case a helper row is used and also to drag a formula to apply to other cells in the same row, we drag horizontally.
Conclusion
Data formatting is a skill that every data analyst should have. Mastering this skill will help make your life as a data analyst easier and will enable you gain meaningful insights from data.
Top comments (0)