Excel - Copy/paste cell with a formula

Excel 2016/Win10

Hi everyone. Happy Friday. :)

I maintain a timesheet for my work that contains daily totals. e.g. for 7/21/16, I might have worked on three things. The time for each project is in column C e.g.

Cell D100: 1:00
Cell D101: 0:30
Cell D102: 3:30
Cell D103: =SUM(D100:D102) = 5:00

A second sheet contains a weekly calendar. I want to copy/paste (e.g) the 5:00 value in D103 (first sheet) into (e.g.) cell E22  (second sheet) which represents 7/21/16; however, Excel thinks I want to paste the formula (not the value).

I looked at paste special; however, it did not contain any options that seemed helpful.

Is there a workaround? (I'd rather avoid referencing the first sheet in the second sheet,)

Stephen KairysTechnical Writer - ConsultantAsked:
Who is Participating?
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
"Paste special" is correct. It should have an option for "values", which is what you need.
Another way is to create a reference to the cell: In the target cell, click to edit, type an equal sign, then change to the source cell. Press return, and done.
JohnConnect With a Mentor Business Consultant (Owner)Commented:
I agree with the above. In fact, I use the Customize Quick Access Toolbar to put Paste Value and Paste Formula icons right there. I use both all the time.
Stephen KairysTechnical Writer - ConsultantAuthor Commented:
OK, that worked...sort of. It seems that unless the cell is already formatted to be a time , it pastes a decimal value (e.g 0.20625) if I choose the first option. (Values).
Paste Special
Do I need to choose the 2nd option (Values and Number Formatting)?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

JohnBusiness Consultant (Owner)Commented:
Paste values will paste decimals. No way around that.

Solution I use:   =ROUND("formula", 0) to round to integers. Then paste values.
Stephen KairysTechnical Writer - ConsultantAuthor Commented:
For the "ROUND" solution, is that in cell FROM which I'm copying?

Or, maybe "Values and Number formatting" ? That seems to work, but loses my boldfacing, which is no big deal.

JohnBusiness Consultant (Owner)Commented:
I prefer to make main formulas Round (as that is better from a calculation point of view in the work I do). Then I copy / paste values.

If your way is better for you, then you should use that.. Always more than one way in Excel.
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:

Values and Number formatting option will retain the number formatting applied to the cell you copied, it will do the trick for you.
Stephen KairysTechnical Writer - ConsultantAuthor Commented:
Great collaboration all! Thanks!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Stephen! Glad we could help.
Rob HensonFinance AnalystCommented:
I was about to post a solution when I refreshed and the question had been closed.

A couple of points:

@John - he is looking at time values so rounding to 0 decimal places will either round up to a whole day or round down to zero hours

@Stephen - When adding up time values you may sometimes go over 24 hours, especially with a weekly timesheet. Standard hh:mm formatting will only go up to 23:59; to get round this format as [h]:mm this will allow the hours portion to go over 24.

For example, if you have 5 days of 7:30, adding up comes to 37.5 hours, formatted as hh:mm will show 13:30 (the first 24 rounded back to a day). Formatted as [h]:mm shows 37:30.

Rob H
Stephen KairysTechnical Writer - ConsultantAuthor Commented:
Thanks, Rob. Fortunately, I sum only on work performed during a particular day. Unless I move to a planet whose days exceed 24 hours, I'm fine there. :) Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.