- See full list on study.com.
- Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer. Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365. Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.
Converting a list to a table makes it very easy to format your data and to keep the formatting consistent as you make changes to the data. You can convert a list to a table in three short steps.
This feature works the same in all modern versions of Microsoft Excel: 2010, 2013, and 2016.
In order to format our data as a table, we follow these steps: Select the cells we want to format; Click Home tab Format as Table Table Style Light 9 Figure 4. Format As Table in menu. The Format As Table dialog box will appear. Verify the range B2:F7 for our data set and ensure that the checkbox is ticked for My table has headers.
- Select a cell within the list you wish to convert to a table.
- On the Insert tab, in the Tables group, click the Table command.
- In the Create Table dialog box, verify that Excel has correctly guessed the correct data range, check My table has headers if your table does have headers, and click OK.
I often find myself struggling to deal with data that has been rendered almost unusable by the data provider who has converted it to a cross tab format. Usually they think they are helping and have probably started with a nice flat list then spent ages formatting it so the data spreads out across the columns. Sometimes they have achieved this by putting the data into a pivot table but then have pasted the table as values and removed all links to the underlying data. Very helpful.
How To Create A Crosstab Table In Excel
Yesterday I had to deal with a data file that looked a little like this only it spread out across 455 columns and was frankly useless.
I wrote a post a while back that demonstrated how to convert a simple cross tab back to a manageable data list but I wanted to expand this with a slightly more complex example which had more field headings.
Excel table to flat list
So starting with the table above which was probably a pasted copy from a pivot table I applied a little bit of formatting to achieve the following starting table.
What I have done is remove the grouping that the pivot table applies and ensured that I have the relevant week number, project and department details against each name
Excel Table Function
I often wish to convert pivot table outputs back into a data file and the way I fill in the blanks caused by grouping data is to prepare a sheet with formulas that copy values from above (or the side as appropriate):
I then copy the pivot table data and then use the paste special command selecting paste values and skip blanks as the options.
This results in the following output, which I then copy and paste over itself as values, to give me the desired structure.
Having spent a bit of time on the structure of my starting file I can run the macro that will work through each row and transpose the data from a columnar layout to one based on rows.
The advantage of this format is that I can now create my own pivot tables and cut the data as I see fit to produce multiple summary formats according to my audience.
Here’s the macro I use:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | Sub CrossTabToList() 'written by Doctor Moxie Dim wsCrossTab As Worksheet Dim wsList As Worksheet Dim iLastCol AsLong Dim iLastRow AsLong Dim iLastRowList AsLong Dim rngCTab As Range 'Used for range in Sheet1 cross tab sheet Dim rngList As Range 'Destination range for the list Dim ROW AsLong Set wsCrossTab = Worksheets('Sheet1') 'AMEND TO SHOW SHEET NUMBER WITH THE CROSS TAB Set wsList = Worksheets.Add 'Find the last row in Sheet1 with the cross tab iLastRow = wsCrossTab.Cells(Rows.Count, 'A').End(xlUp).ROW 'Set the initial value for the row in the destination worksheet 'I set mine as 2 as I want to put headings in row 1 iLastRowList = 2 'Find the last column in Sheet1 with the cross tab iLastCol = wsCrossTab.Range('A2').End(xlToRight).Column 'Set the heading titles in the list sheet 'You will need to amend these to something appropriate for your sheet wsList.Range('A1:F1') = Array('NAME', 'PROJECT', 'TYPE', 'PLAN/ACTUAL', 'WEEK', 'HOURS') 'Start looping through the cross tab data For ROW = 3 To iLastRow 'START AT ROW 3 AS THIS IS WHERE DATA BEGINS IN MY CROSS TAB Set rngCTab = wsCrossTab.Range('A' & ROW, 'C' & ROW) 'initial value A3 SETS THE RANGE TO INCLUDE ALL STATIC DATA - IN THIS CASE NAME, PROJECT, TYPE Set rngList = wsList.Range('A' & iLastRowList) 'initial value A2 'Copy individual names in Col A (A3 initially) into as many rows as there are data columns 'in the cross tab (less 3 for Col A-C). rngCTab.Copy rngList.Resize(iLastCol - 3) 'SELECT THE HEADING ROW WITH FORECAST/ACTUAL 'Move up ROW (INITIALLY 3) rows less TWO and across 3 columns (using offset function). Copy. rngCTab.Offset(-(ROW - 2), 3).Resize(, iLastCol - 3).Copy 'Paste transpose to columns in the list sheet alongside the static data rngList.Offset(0, 3).PasteSpecial Transpose:=True 'SELECT THE ROW WITH THE WEEK NUMBERS 'Move up ROW (INITIALLY 3) rows less ONE and across 3 columns (using offset function). Copy. rngCTab.Offset(-(ROW - 1), 3).Resize(, iLastCol - 3).Copy 'Paste transpose to columns in the list sheet alongside the static data rngList.Offset(0, 4).PasteSpecial Transpose:=True 'Staying on same row (3 initially) copy the data from the cross tab rngCTab.Offset(, 3).Resize(, iLastCol - 3).Copy 'Past transpose as column in list sheet rngList.Offset(0, 5).PasteSpecial Transpose:=True 'Set the new last row in list sheet to be just below the last name copied iLastRowList = iLastRowList + (iLastCol - 3) 'increment ROW by 1 Next ROW EndSub |
Data Table In Excel
Here is a copy of file – you could copy the macro code from here: CrossTab-to-Flatfile