Google sheets help, possibly just general spreadsheet q

[Carpet_pissr]

Does anyone use Google Sheets and have a decent working knowledge?

I created a Sheet to track monthly personal finances (actually I just yoinked an existing template and modified it heavily), and can't figure out how to CLEAR contents in a cell. I thought in Excel that CLEAR means "surface level delete, but keeps formatting, etc etc" - is that not true? If it IS true, is there a similar function in Sheets?

The reason I ask is because I have two columns that I update monthly-ish. I'm using the same tab, and just copying and pasting VALUES first, then FORMATTING. The problem is that now I have a properly formatted copy of last month's data (to the right of last month's). So when I am filling it in with the new data, it's hard to remember what's old and what's new. SURELY there is a better way to do this? CLEAR in Google Sheet speak I think means DELETE THE WHOLE THING, INCLDING FORMAT.


Here's what I found online in terms of a suggestion:

You will need to select only the cells without formulas and delete the static values. There isn't a master function that will do this for you though.
Generally, when people have a sheet that needs to be repeatedly filled in, they will duplicate a blank tab and leave one blank. Then each time you need to start over, just copy that blank one and rename it to the new (month/week/day/whatever) name so and use it.

So maybe my problem is one of process? Is it better practices to just create a new tab for every month's entry? I'm not crazy about that idea since I like to be able to see several months' comparison just at a glance, instead of having to click every tab (I assume you can run reports and whatnot, or lock tabs maybe, to do this more efficiently?)

But assuming this IS the best practice for what I am doing, I still don't really get how that will help with the formulas and formatting that I've painstakingly honed to the way I like it. If I just DUPLICATE an existing tab, it just makes a copy of an existing tab, with all its formulas, values and formatting. Back to same problem, in that I need to clear JUST the values, not the formatting or formulas...so I can input the new values.

Does that make sense? I get the feeling this is one of those things that I am coming at it from a weird angle and there are better, easier ways to do this. Thus is my brain.

[LawBeefaroni]

Why are you copying values? Can't you just copy formatting?

[Carpet_pissr]

I don't think so, as I have already tried it. If I start a new couple of columns (say for this month's entries), and COPY a previous month's column, then PASTE, SPECIAL, FORMULA ONLY, then it pulls in the values from the COPIED cells as well for some reason. Which brings me back to the original issue of wanting to CLEAR those values, but maintining the formatting and formulas.

Something else to consider (though I would be loathe to do it) is to just switch back to Excel. Not even sure why I am doing this in Sheets! Maybe there is some kind of "IMPORT" function in Excel now that pulls in Google sheets.

[Jaymann]

Have you tried openoffice.org? They have a spreadsheet in their free suite. I prefer their word processor as it is much less bloated than Word. And can convert to Word if needed.

[coopasonic]

I'm having a hard tie imagining what you are doing. Sheets just isn't that hard unless you are doing seriously weird ass shit.

On the sheets to excel thing, from sheets you can download (file menu) as an xlsx file.

[stessier]

I can't get to Sheets at work, but will take a look when I get home. I think what you're trying is possible, but I may be confusing Excel and Sheets.

[LawBeefaroni]

I don't think so, as I have already tried it. If I start a new couple of columns (say for this month's entries), and COPY a previous month's column, then PASTE, SPECIAL, FORMULA ONLY, then it pulls in the values from the COPIED cells as well for some reason. Which brings me back to the original issue of wanting to CLEAR those values, but maintining the formatting and formulas.

Something else to consider (though I would be loathe to do it) is to just switch back to Excel. Not even sure why I am doing this in Sheets! Maybe there is some kind of "IMPORT" function in Excel now that pulls in Google sheets.

Paint format? Pretty sure there's a paint format button.

[Carpet_pissr]

I'm having a hard tie imagining what you are doing. Sheets just isn't that hard unless you are doing seriously weird ass shit.

On the sheets to excel thing, from sheets you can download (file menu) as an xlsx file.

From a macro perspective, pretty simple, basic stuff: a spreadsheet that I update monthly with current balances and expenses, etc. But it could be "weekly payroll", anything that needs regular updating, that uses the same format every time.

Mine happens to be just two columns that repeat every month: the date, and % change (from the previous month)

Rows are things like: net worth, assets, cash, retirement account, home equity, college savings, credit card debt, etc etc.

I use quite a few formulas so I don't have to calculate everything, especially when there are lots of dependencies (like net worth, which sums up a few different rows).

The 'problem' is that every month when I create two new columns (for the new month's data), I have to 1. copy and paste the values 2. copy and paste the formatting 3. and this is the big one: have to remember what value is from this month, vs last, and keep up with it, because the "new" columns are pre-populated with last month's values (which I have to change).

It's not horrible, but I thought there was a function that just CLEARED the values only, but not the underlying formatting and formulas acssociated with the cell. I'm having a hard time getting Google Sheets to do that for some reason.

[stessier]

It's not horrible, but I thought there was a function that just CLEARED the values only, but not the underlying formatting and formulas acssociated with the cell. I'm having a hard time getting Google Sheets to do that for some reason.

The formatting clearing should be possible so that there are no values but it everything greater than, say, 100 was green text, that will stay the same, but you can't keep a cell formula but delete the value. What would that even look like? Do you know how to use relative references in formulas? That may be what you're thinking - so the new output column points to new input columns automatically.

[Carpet_pissr]

I don't think so, as I have already tried it. If I start a new couple of columns (say for this month's entries), and COPY a previous month's column, then PASTE, SPECIAL, FORMULA ONLY, then it pulls in the values from the COPIED cells as well for some reason. Which brings me back to the original issue of wanting to CLEAR those values, but maintining the formatting and formulas.

Something else to consider (though I would be loathe to do it) is to just switch back to Excel. Not even sure why I am doing this in Sheets! Maybe there is some kind of "IMPORT" function in Excel now that pulls in Google sheets.

Paint format? Pretty sure there's a paint format button.

Just tried that. If you do that individually, it works, but I am trying to get the whole column, which has about 50 rows. When you select the whole column, then paste, it pastes the old values, even if you specify formula, or formating only.

THIS close to moving this whole thing to Excel!

[Carpet_pissr]

It's not horrible, but I thought there was a function that just CLEARED the values only, but not the underlying formatting and formulas acssociated with the cell. I'm having a hard time getting Google Sheets to do that for some reason.

Do you know how to use relative references in formulas? That may be what you're thinking - so the new output column points to new input columns automatically.

Ohhhh, I remember that now, back when I used Excel all the time, yes! That would work. Gah...completely forgot about that! Thanks

[coopasonic]

Based on what you are saying, I'm imagining I would just be copying the two columns from last month, paste and then select the raw value cells from the pasted cells and hit delete, then enter in the new month's data. The formatting and formulas should carry over without any fuss. In fact, I do this every month for work. I could see there being a challenge is your raw value data is not easily selected as a group which would just be a something I would immediately reorganize to support.

[Carpet_pissr]

Based on what you are saying, I'm imagining I would just be copying the two columns from last month, paste and then select the raw value cells from the pasted cells and hit delete, then enter in the new month's data. The formatting and formulas should carry over without any fuss. In fact, I do this every month for work. I could see there being a challenge is your raw value data is not easily selected as a group which would just be a something I would immediately reorganize to support.

But that's the thing, when I copy over, then hit DELETE (after selecting the column obviously), it wipes out everything...values (yay), formatting (boo) and formulas (boo)

[coopasonic]

It's not horrible, but I thought there was a function that just CLEARED the values only, but not the underlying formatting and formulas acssociated with the cell. I'm having a hard time getting Google Sheets to do that for some reason.

Do you know how to use relative references in formulas? That may be what you're thinking - so the new output column points to new input columns automatically.

Ohhhh, I remember that now, back when I used Excel all the time, yes! That would work. Gah...completely forgot about that! Thanks

Unless you manually force it not to($ prefix), sheets and excel should both update the reference when you copy/paste so they are effectively relative.

[coopasonic]

Based on what you are saying, I'm imagining I would just be copying the two columns from last month, paste and then select the raw value cells from the pasted cells and hit delete, then enter in the new month's data. The formatting and formulas should carry over without any fuss. In fact, I do this every month for work. I could see there being a challenge is your raw value data is not easily selected as a group which would just be a something I would immediately reorganize to support.

But that's the thing, when I copy over, then hit DELETE (after selecting the column obviously), it wipes out everything...values (yay), formatting (boo) and formulas (boo)

Like stess said, you don't delete any cells with formulas, only raw inputs. Formatting should not be affected by delete (I mean hitting the delete key not selecting Delete Cells from the context menu) . I just checked and confirmed.

[Carpet_pissr]

Based on what you are saying, I'm imagining I would just be copying the two columns from last month, paste and then select the raw value cells from the pasted cells and hit delete, then enter in the new month's data. The formatting and formulas should carry over without any fuss. In fact, I do this every month for work. I could see there being a challenge is your raw value data is not easily selected as a group which would just be a something I would immediately reorganize to support.

But that's the thing, when I copy over, then hit DELETE (after selecting the column obviously), it wipes out everything...values (yay), formatting (boo) and formulas (boo)

Like stess said, you don't delete any cells with formulas, only raw inputs. Formatting should not be affected by delete (I mean hitting the delete key not selecting Delete Cells from the context menu) . I just checked and confirmed.

Like I initially hinted at, I MUST be missing something, That may be it. BUT...one last try here:
I have my existing spreadsheet with let's say 5 months starting from left. To the RIGHT of those entries, I want to create my new month. Blank cels, right? Nothing in 'em. If I COPY an older cel with a value in it (AND A FORMULA), then PASTE it into a new cel for the new month - I get options (under PASTE SPECIAL):
VALUES ONLY
FORMAT ONLY
FORMULA ONLY
and a couple more below those.

If I click FORMULA ONLY, it puts a zero in as value, and I can clearly see it has copied over the formula bc when I look at the entry box at the top, I can see it. If I then select that new cel with the zero (and pasted formula) in it, it deletes both the value (0) AND the formula. When I select that cel now, I see no formula in the entry window thingie at the top.

Again, if that behavior above would work with an entire column, I would be golden (zeros for all the NEW values, but still keeping formatting and formulas, would be great!), but I can only get that to work when done at a cel to cel level.

[coopasonic]

If we were in a room together this conversation would be three minutes max. I really feel like you are trying to overcomplicate things. I can't post any examples from my work machine.

My process is:
1 copy last month's cells
2 paste (not paste special) to new location
3 delete the RAW values from the newly pasted cells (leave the formulas alone, they will break or go to zero when the raw values are removed)
4 type in the new raw values
5 drink

Hell, I could even create a form that you could use to enter the data that would also create the new columns for you. Hmmm... I may even do that for my work use case!

[Isgrimnur]

If only there were some sort of screen-sharing application that also mimicked a telephone call...

[coopasonic]

If only there were some sort of screen-sharing application that also mimicked a telephone call...

wouldn't that be handy (he says as he sits on a zoom call right this moment and for most moments of most days)

[LawBeefaroni]

I don't think so, as I have already tried it. If I start a new couple of columns (say for this month's entries), and COPY a previous month's column, then PASTE, SPECIAL, FORMULA ONLY, then it pulls in the values from the COPIED cells as well for some reason. Which brings me back to the original issue of wanting to CLEAR those values, but maintining the formatting and formulas.

Something else to consider (though I would be loathe to do it) is to just switch back to Excel. Not even sure why I am doing this in Sheets! Maybe there is some kind of "IMPORT" function in Excel now that pulls in Google sheets.

Paint format? Pretty sure there's a paint format button.

Just tried that. If you do that individually, it works, but I am trying to get the whole column, which has about 50 rows. When you select the whole column, then paste, it pastes the old values, even if you specify formula, or formating only.

THIS close to moving this whole thing to Excel!

Don't paste. Select the first column, click on the paint button, then select the second column.

[Anonymous Bosch]

FWIW, you may find it simpler and easier to use a proper budget tracking/personal finance tool. Money Manager Ex is a FOSS (Free & Open Source Software) alternative to Quicken, and Homebank is another free option.

[Carpet_pissr]

If we were in a room together this conversation would be three minutes max. I really feel like you are trying to overcomplicate things.

Yes, I am sure this is true. I do it with most things in life. Overthink. Overcomplicate.

This is just a perfect example of what happens to me all the time - I approach a problem in a way that seems to be clearly and obviously The Way. Only to later find out (usually after a lot of head scratching and frustration) that THAT way is not even close to The Way that most people see as being the first and best choice. It's not a fun way to live/be, but it's usually interesting.

Thanks again guys...trying all the above suggestions now.

[stessier]

You can't just delete the value. The formula will calculate and display the result. So you want to see the formula? If you want the cell to have no value, you have to delete the data it is using to calculate.

Edit: I swear there was another post where you referenced step 3.

[Carpet_pissr]

I am such an idiot. Just realized my mistake when I tried to do this in Excel (and BTW, COMPLETELY painless to open this Google Sheets....sheet in Excel...took all of 3 seconds and it was perfectly intact! Surprised a bit at that!).

I'm sorry I have wasted your time!

I was trying to do the impossible...start with a completely blank slate for each month's new columns, but ALSO retain the formulas. Of course if you DELETE a cel with a formula in it, you will delete not only the formula, but the value...in fact, in a sense, the ONLY thing real there IS the formula.

All I have to do is quickly go through each cel from top to bottom, and just delete the cels WITHOUT formulas in them, and then as you said, Coop, the cels containing formulas would break or go to zero. GAH!!! I was trying to just do a whole column DELETE to clear everything out so I wouldn't get confused about what was last month's value vs the new values I was about to put in, but I guess you can't do that when you have formulas. Right?

[Carpet_pissr]

You can't just delete the value. The formula will calculate and display the result. So you want to see the formula? If you want the cell to have no value, you have to delete the data it is using to calculate.

Bammed Guess we were posting at the same time.

[Carpet_pissr]

Jesus, I feel stupid. SORRY everybody, go home! Have a beer on me! Nothing to see here!

[Isgrimnur]

It happens to us all at some point.

[Carpet_pissr]

I submit this thread as evidence for adding the capability to nuke an entire thread if you created it.