Converting formula results to absolute values

Duplicate the table.
Select the range of "interesting" cells
Copy
_Paste Values_ .
Now, in the replicate you may remove the unwanted rows and columns.

Alternate way:

In the original table
Select the range of "interesting" cells
Copy
_Paste Values_ in a new table.

It's fine, but you will drop attributes/formats available in the original table.

Yvan KOENIG (from FRANCE mercredi 29 juillet 2009 21:42:24)

Unless everyone has Numbers, they won't be able to read this spreadsheet. You might want to print that particular page to a PDF instead and have the PDF posted on the website. This isn't the same as Export to PDF, you Print and select "Save to PDF" in the lower left corner of the Print window. You will want to have that one table on a page by itself and you can print just that one page. If you don't see the expanded Print window with all the options for what to print, click on the triangle next to the printer name.

I have a similar problem (and I'm not yet 71, or new to Apple products -- been using Macs since 1990).

Does anyone know of an easy way to replace all formulas in an entire Sheet with their values, in one command or script or.....?

I have a sheet with 4 tables, all of which are set to show only rows that match certain criteria (using the Reorganize dialogue).

I want to make a copy of this sheet in the same file WITHOUT losing all the formatting, so that it creates a record of its state/calculations prior to updating the source info and having changes ripple through the whole file.

Possible?

Have you tried this:

Make a copy of the table by selecting the table (so it has the little squares in the corners), copy, paste it to the canvas.

Select all cells in this new table (click on a cell in the table then press Command A), Cut, Paste Values.

Drag the new table to another sheet afterward, if you want to, to get it out of the way.

Yes, thanks. I know how to do this one table at a time. I was curious if anyone's found a way to automate duplicating a whole sheet at once (with multiple tables) and making the formulas into values at the same time, or with a minimum of hand selection, copying, pasting, etc.

In Excel, you just hit Command-A, Command-C, then select Paste Values without moving your cursors. Bam. Done.

(Not that I love Excel, but this particular task seems much easier in that application.)

You can duplicate an entire sheet pretty easily. Right click on the sheet name and choose "duplicate". You still have to cut/paste values of each table one at a time, though.

I will look and see how it works with the reorganize filters on. In general it is as below.

Excel's Command A, Command C, Paste Values is the exact same as in Numbers. It replaces the formulas in an entire table with their values. The difference is that a "sheet" in Excel is a single table. The Excel equivalent of a Number's sheet that has four tables is four Excel worksheets/tables. You could design your Numbers document with one large table just like Excel but you have probably chosen to do otherwise because it looks better and makes more sense with it presented as four tables.

Comparing apples to apples, in Excel it takes these steps to duplicate four tables and replace them with their values:
1. Duplicate sheet 1
2. Cut and Paste Values for that duplicated sheet
3. Repeat 1 and 2 three more times for the other three sheets.
That is 8 steps total and requires going from tab to tab and you end up with four more sheets in your document. That can add up fast.

In Numbers it is:
1. Duplicate the sheet
2. Cut and paste values for first table
3. Cut and paste values for three more tables.
That is 5 steps total and you do most of it on one sheet. You end up with one additional sheet that includes four tables.

Looking into Cmd-A, Cmd-C, Paste Values with filters on, Excel 2008 won't let me do it. All rows must be showing or the operation fails. Numbers will do the operation but the formulas in hidden rows are unaffected.

Okay, you guys are confirming what I already know. So it seems there's really no better way to do it than the way I'm doing it. Okay, I can live with that.

Thanks for all your help.

This seems to work for me:

1. Press command-a to select all the tables
2. Press command-c to copy all the tables
3. Press delete to delete all the tables
4. In Edit menu, select Paste Values

All the tables should be pasted with only the values. Of course, if you like, instead of deleting all the tables, you can select another sheet and then Paste Values.

That does do all tables at once. Note that formatting (such as borders, text color) gets removed in the process.

Yes, and retaining the formatting is part of what I need to do. I need to duplicate the whole sheet exactly as it was, but with the values locked.

My own tip is to duplicate the file then copy - paste values table by table.

So, the original remains a living document and only the duplicate is frozen.

Yvan KOENIG (VALLAURIS, France) mardi 29 septembre 2009 19:08:14