speedsitemighty.blogg.se

1. #Keep source formatting when pasting in excel for a mac how to#
2. #Keep source formatting when pasting in excel for a mac serial#
3. #Keep source formatting when pasting in excel for a mac windows 10#
4. #Keep source formatting when pasting in excel for a mac download#

If you can't rely on users, you can add a simple VBA procedure to the template that you distribute.

#Keep source formatting when pasting in excel for a mac how to#

SEE: How to use Excel's what-if tools to analyze business scenarios (free PDF) (TechRepublic) Solution 2: Use VBA, with a bit of caution In the Cut, copy, and paste selection, check the Show Paste Options button when content is pasted option.Click the File menu and select Options.If the Paste Options button isn't visible, do the following: Excel keeps the date value but removes the formatting that came with it. When Excel displays the Paste Options button, click the dropdown, and choose Values ( Figure E).Select B4 and press Ctrl+v as you normally would.Users might find it easier to use the Paste Options button instead: Instead, choose Values from the Paste dropdown ( Figure D) in the Clipboard group (on the Home tab).įigure D Paste Values copies only the value, so the target cell's format retains intact. Select B4, but don't press Ctrl+v to paste the value as you normally would.With C4 selected, press Ctrl+c to copy the date value to the Clipboard.If you're following along, press Ctrl+z to undo the last paste.Let's use the same example to illustrate how this simple feature works: The easiest way around this problem is to use Excel's Paste Values option to paste only the values. Figure C Pasting usurps the target cell's format. To further complicate the situation, Excel changes the format of the target cell to that of the pasted value! In other words, after pasting the value in Date-formatted C4 into the Custom-formatted B4, B4's format matches that of C4-B4 is now a Date-formatted cell instead of a Custom-formatted cell, as you can see in Figure C. However, if you paste a differently formatted date, Excel accepts the source formatting.

Figure A Apply a custom format to display dates in YYYY-MM-DD format. Regardless of how you enter the date May 10, 2018, Excel displays it as, as shown in Figure B. Figure A shows the YYYY-MM-DD custom format. To illustrate this problem, let's work through a quick example. If you're going to allow users to paste values, you must train them to paste only the values. You might try combining features such as data validation and conditional formatting, but again, Excel ignores your best efforts when a user pastes a valid date value.

You can change the cell's format to text and use a custom rule to catch string values that don't match the format you want, but the inherent problem still exists: When the user pastes a valid date value, Excel accepts it as a date value-and your data validation rule is out the window. For example, Excel stores as 43230, regardless of its format.

#Keep source formatting when pasting in excel for a mac serial#

Data validation can reject non-date entries, but I've yet to come up with a formula that also rejects date values that don't fit a specific format because the underlying value is a date serial value.

There are lots of features you might consider. Aditee wants an automated process that will warn users when they enter data in the wrong format. On the other hand, when someone pastes a date value, Excel ignores the destination cell's format, retaining the formatting from the original source-not what users expect. When entering date values, the template's formatting accommodates the YYYY-MM-DD format-it works as expected. SEE: 10 Excel time-savers you might not know about (TechRepublic) The problem: Excel paste task overwrites destination cell's formatĪditee collects data from several different vendors, who use an Excel template. The first solution works in Excel's browser edition the VBA procedure doesn't.

#Keep source formatting when pasting in excel for a mac download#

You can work with your own data or download the demonstration.

#Keep source formatting when pasting in excel for a mac windows 10#

I'm using Excel 2016 (desktop) on a Windows 10 64-bit system.