I started a new job and have been asked to create a new workbook from a current workbook in order to use the workbook: My workbook contains 20 sheets of cost data on work projects in different areas. And has a summary sheet and other analyses based on the data from those sheets. This workbook is not allowing me to perform tasks I need and was somehow blocked by another user. I copied the sheets to another workbook in hopes I could use the data as I choose. The new workbook is not calculating the totals properly and some of the formulas in the summary are referring to the old workbook sheets (I want the formulas to refer to the sheets in the new workbook) How do I save the data so it only refers to the new workbook sheets?

I have also started a new sheet in the new workbook and entered many values in 2 columns and created a formula to total the values in each column and each row. The formulas do not work. (I want this resolved as well). This data in this new sheet will also be used to create more data on the same sheet.

I have tried formatting the cells to data and it doesn't work. Since it is a work item I can not provide the workbook.

a) You save the old workbook with new name. Now you have a new workbook where all formulas refer to ranges in new workbook. After that you can make any changes, delete abundant data, add new data, edit existing data, etc.

b) You create a new workbook and copy table headers and column widths from old workbook sheets into new workbook sheets (use PasteSpecial > Values and PasteSpecial > ColumnWidths). Then copy header and 1st data row from every table on old workbook, and use PasteSpecial>Formats to paste formats into according tables in new workbook.
After that you have to select a field with formula in it, copy the formula in formula bar field, and copy it to according field on new workbook. Repeat this process for every formula field in top rows of old workbook tables. Or you copy a top row from every old workbook table and use PasteSpecial > Formulas to paste it to new table, then clear all cells without formulas, and edit all cells with formulas removing references to old workbook with references to new workbook (usually you can use ReplaceAll to do this).
Now you can copy every table down for as much rows as you need, and enter your data into fields without formula (manually, or copy)

Copying workbook to a different MS Office version causes misalignment. In such conditions you have to use the Paste special option.

i would do as ArviLaanemets suggested, the safest way to keep all formulas and formatting the same, especially in a multi worksheet workbook.

it is possible to select all sheets, right click on the worksheet tab, move or copy, tick create a copy and select new book.

But there isnt look of difference between the 2