Friday, October 19th, 2018 / by Excel Champ
Tables come in many styles and formats. For reporting purposes, the better a table is formatted, the easier it is to read. However, formatting is often our enemy when we attempt to use the data for analysis, database, and data integration purposes. In this blog post, we will demonstrate how to unformat a table and turn it into a backend data table that is ready for other uses.
Below is a perfect example of a well-formatted table. It has a title, clearly labeled row and column headings/subheadings, numbers with 2 decimal places, and footnotes. This table is a truncated version of a larger table from the source cited. For the purpose of data preparation, our goal is to convert this into a table with single column headings and data, but no row headings or subheadings.
The first step is to unmerge all merged cells in the table. For this table, there are merged cells in the column header, and in each taupe colored bar designating the nutrient groups.
In the above image, columns B and D contain totals that are calculated from values in other columns. These columns can be deleted because the totals can always be obtained again by recalculating. Also, add a new field called Nutrient, in column G, in preparation for eliminating the Nutrient subheadings. Populate the Nutrient field with the appropriate text. Add “Demographic Group” as a heading for Column A. The rearranged table should now look like this:
After deleting the taupe shaded rows with the Nutrient subheadings, the table should look like this:
In the above table, the 5 headers of “At Home” to “Other” (Columns B through F) are all related to the location where the nutrients were consumed. As a result, the header descriptions will become values of a location field. The 5 columns will become 2 columns: Location and Intake Amount. To prepare the table for this transformation, we will add Location columns and populate them with the location from the adjacent header, as shown in the green highlighted cells below.
The second part of this step is to rename the “At home,” “Restaurant,” “Fast Food,” “School,” and “Other” headers to “Intake Amount.” This will reflect that those columns contain the intake values associated with the location that is now in the Location column.
At this point, we’re close to having a final data table with 4 columns: Demographic Group, Location, Nutrient, and Intake Amount. All we need to do is transform it a bit more and rearrange the columns. Once done, it looks like the image below. Our truncated table that contained 20 rows of data in 8 columns now contains 75 rows of data in 4 columns.
Now that the data is extracted and in usable format, you can convert it into a table. While in any cell within the table, press Ctrl A to select the whole table. Then select the Insert Tab and select “Table.” Check the box next to “My Table has Headers.” Now your table can easily be sorted or appended:
If the above steps seem tedious and time consuming, there is a better way, especially if you need to transform the same type of data repeatedly. With VBA programming and the experts at ExcelHelp.com, your data transformation tasks can be automated and completed with the click of a button.
Let us help you design and develop a rock-solid solution for your firm. Contact our team to schedule a free consultation by calling 1-800-682-0882 or visit our website at ExcelHelp.com to submit an inquiry online.
I can not recommend ExcelHelp more highly. We had been struggling to complete a critical project and finding them was a lifesaver. Tom was knowledgable, professional, personable, patient and responsive. Look no further for your database programming needs. Thanks Tom and ExcelHelp.com!Randy Hartnell
We anticipate a long business partnership with ExcelHelp and look forward to working with them. I have been nothing but impressed with your firm’s services, your team members tenacity and the overall results. Sometimes you have to travel a bumpy road trying different firms until you find the one that fits.Posted By: Charlene Faber
Chief Operation Officer, Current Builders
I’d like to express my gratitude for the help and cooperation we’ve received from your entire organization during the development of our Modeling Utility. Over the past several weeks we’ve engaged key customers worldwide...We have confidence that this effort will add significant capability and value to the overall success of the program.Business Development Manager
Network Architecture and Strategy
Posted By: Marty M.