Leave format and formulas, and be able to delete content

Copper Contributor

I have created a spread sheet to do payroll every week. I have to enter amounts every week, and then I have to go to every cell to delete the numbers I have entered in order to do next weeks payroll. I would like to delete the numbers I have put in and delete them all at once with out deleting my formulas or the information I have on my spread sheet.  

6 Replies

@Myrian26 

Would the following work? It depends on the layout of your worksheet.

  • Select the entire range where you enter numbers. It's OK if it includes some cells with text values and with formulas.
  • Press Ctrl+G or F5 to activate the Go To dialog.
  • Click Special.
  • Select Constants, then clear all check boxes except Numbers.
  • Click OK.
  • Press Delete.

S0355.png

For ease of use, you could create a copy of the sheet after you've cleared the numbers, and then use it as a template - each time you need to create a new payroll, make a copy of the template sheet.

@Myrian26 

 

During my working career (I'm now retired) I was for a period the director of the HR and Payroll database for a major corporation. What you've described here is worrisome in some ways, potentially. I wonder, for example:

  • How many employees are involved in this payroll? (I assume it's a fairly small organization.)
  • How does each new week's data get entered?
    • Manually, with some reference to each employee's hourly or weekly or monthly pay?
    • Automatically, using an Excel function like XLOOKUP or FILTER to extract current pay (and other data) from a separate database sheet?
  • How does the payroll actually get paid from this spreadsheet?
    • Are you using some sort of mail-merge to generate checks?
    • Does the data get sent to an outside service provider?
  • Does this payroll spreadsheet also compute all of the various taxes and withholdings?
  • If you end up deleting each week's data, what sort of history you're keeping of payrolls past? (Surely there are times when an accurate record is important.)

That's enough for starters. But your answers to those questions might well suggest an altogether different approach, if you're open to such.

I think I might of misspoke, I use the spread sheet like a calculator in order to do payroll. Example:
Truck Pay: 4596.00, We get 40% the driver gets 60% plus fuel surcharge.

I entered formulas on how much driver gets and how much we get in order to calculate, what we get pay and what the driver gets in order to enter it on Quickbooks. So it makes it easier on me . When we get the settlements every week I have to enter the pay amount and break it down. So it would be nice to freeze my formulas, and enter any amount and highlight the cells and delete what I enter with out missing up my calculating spread sheet.

Thank You,
Myrian

I think I might of misspoke, I use the spread sheet like a calculator in order to do payroll. Example:
Truck Pay: 4596.00, We get 40% the driver gets 60% plus fuel surcharge.

I entered formulas on how much driver gets and how much we get in order to calculate, what we get pay and what the driver gets in order to enter it on Quickbooks. So it makes it easier on me . When we get the settlements every week I have to enter the pay amount and break it down. So it would be nice to freeze my formulas, and enter any amount and highlight the cells and delete what I enter with out missing up my calculating spread sheet.

Thank You,
Myrian

@Myrian26 

 

You got two suggestions from @Hans Vogelaar. Have you tried either of them? They both look like possible solutions to me.

 

Other than that, I was going to ask if you could post a copy of the spreadsheet. The actual sheet, not an image. I'm assuming it contains no confidential info, since it's basically just a calculator, as you've now described it.

or you can save the blank spreadsheet as a template