Solved

Excel Table Formatting new rows

Posted on 2015-01-17
11
57 Views
Last Modified: 2015-01-29
I have created a table from some data.  There may have been other data and formats used on the workbook.  I have added to the table as well as structured references.  When adding rows, the structured references come up fine, however, some of the cell formatting for the cell does not follow the previous row. (Like number format).  I have tried clearing all formatting and deleting rows except for the first row and still the same issue.
0
Comment
Question by:ekaplan323
  • 6
  • 5
11 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40555725
Can you attach your workbook?
0
 

Author Comment

by:ekaplan323
ID: 40555860
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40555885
When I add a row I just select the row, right-click, and choose 'Insert'. When I do that the fields seem to be OK. If that's the way you do it then please give me the details of the problem like what cells are formatted wrong. If you don't use Insert, how do you add a line?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:ekaplan323
ID: 40556205
When you tab through the line to create a new row, or even more importantly when you copy and paste just values from another area into the table.  Lots of times there is data from another source that can be just pasted to add to the table.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40556260
When you paste, use Paste Special|Values.
Paste ValuesScreenshot-1-18-15--7-55-AM.jpg
0
 

Author Comment

by:ekaplan323
ID: 40556305
I did that when I pasted.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40556337
Sorry to ask you to do this again, but could you possibly create a workbook with two tables that are the same except that the second contains extra pasted data, with the data that was copied and pasted being from some cells someplace in the sheet.
0
 

Author Comment

by:ekaplan323
ID: 40556705
I pasted the data from Sheet 1 to the second table below the first.  I copied the first table to the second table below.  As you see, the formatting did not happen.
Table-Cell-Format-Issue.xlsm
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40556778
Try this. Go to the Visual Basic environment, insert a module, add this macro to the module and assign a shortcut key (I used "t") to it. If you need help with any of that please let me know.

Then copy whatever rows you want, select a destination cell and type ctrl+t.

Sub CopyTableRows()

    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Open in new window

0
 

Accepted Solution

by:
ekaplan323 earned 0 total points
ID: 40568154
In experimenting with the tables, I found there must have been formatting of the cells in the worksheet.  I highlighted the entire rows of the affected data.  I cleared all the formats form the Home Tab on the ribbon. Then formatted by table cells with the format desired.  After that when I added new lines, the formatting was correct.
0
 

Author Closing Comment

by:ekaplan323
ID: 40577053
Table formatting of cells  are supposed to continue as you add rows.  This solution worked for me.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 76
excel 2016 program to loop through scripts to apply filename 2 21
Excel formula Sumif not working 4 28
Excel VBA - Constants 4 12
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question