Those habituated with working with data in their line of work, know for a fact and from experience that the odds of consistently getting data in a specified format for analysis are extremely low. Those who are involved with doing a lot of data cleaning work or data reformatting usually face this problem of inconsistently rearranged format in data so they use scripting languages like Python to get the job done. The top problem faced by the IT companies today is the intensity of demand. But even o the plain old Excel spreadsheets can you play with a lot of data. Here area few tips on how to convert and/or manipulate data in MS Excel.

* Finding Current Age- If you have someone's date of birth you can decipher his/her current age regardless of the day you open the spreadsheet. You can find the current date using the DATEDIF and = TODAY functions. The latter obviously as apparent from its name, gives the current age whereas the former provides the difference between two dates in the units of days, months and years. So you can use this formula to get current age in years.

* Splitting of Dates- By using the formula Year (Cell with date) Month (Cell with Date) Day (Cell with date) you can extract the day, month and year into separate fields. You can split dates according to year, month and day even in Microsoft Access following the same formula. Apart from this, you can get the day of the week for a particular date in MS Excel by applying the Command WEEKDAY (Cell with date). The default displays numbers of the days of the week with 1 for Sunday, 2 for Monday and so on, instead of the names of days. In case you want to display the names of the days instead of the number, you can apply a custom format to the cells and add in the names of the days of the week. Use Format cells>custom then in the text type box add ddd t get the abbreviations of the names of the days or dddd to get the full names of the days of the week.

* Multiple Rows out of One- Sometimes you need your data in a format that displays each observation in one row but what you get instead is each row full of multiple observations. Tableau Visualizations is one such software that allows for one data per row for proper formatting of observations, not multiple data in each row. The makers of this software created a Tableau Reshaping software that is compatible with the latest version of Microsoft Excel. You can download this software from the Tableau website to avail of this formatting feature with Excel.

* Create a New Column- Almost all those who are familiar with Excel knows that they can do a search and replace in Excel with a typical text editor function for find and replace. But apart from this function, the lesser known feature is that you can also create new columns in Excel by basing the old column through search and replace. Just use the syntax SUBSTITUTE (''cell with text'' ''old text'' ''new text'')

So it is really easy to manipulate and convert data on Excel using the many formatting tools available.