Google Sheets Export to Excel values instead of formulas

Question

I have a google sheet where the cells in the first tab pull data from cells on the second tab.

for example Sheet1 cell A1 has =Sheet2!A1

This is true for every cell on Sheet1

When I do a File - Download As - Microsoft Excel (.xlsx)

It exports the cells with formulas. Is there a way to export the sheets as values and not formulas

In this case, Sheet1 cell A1 would not contain =Sheet2!A1 but the value of =Sheet2!A1?

Fabrício Ceolin · Answer 1 · 2018-03-29T13:58:01.390

2

You can copy your original google spreadsheet and, in the copy, change the formula for the first cell for each tab to import data from the original one:

=IMPORTRANGE("spreadsheet id","'tab name'!range")

Ex:

=IMPORTRANGE("1C-PS4wAHS8ssCNgVDfOsssREAz7PjuQGX23Rk0sssss","'measurement with spaces'!A12:F44")

The ID you can get via original spreadsheet URL:

https://docs.google.com/spreadsheets/d/1C-PS4wAHS8ssCNgVDfOsssREAz7PjuQGX23Rk0sssss/edit#gid=99999999

The exported xlsx file from the copy will have only the values

edited Mar 29 '18 at 13:58

answered Mar 29 '18 at 13:49

Fabrício Ceolin

456
5
7

sure, except now you just added yet another formula. There should be a way to modify the URL when exporting to specify that you need the "values only". For example you can enter "export?format=xlsx&type?format=valuesonly&gid" – Victor -------- Sep 30 '19 at 16:24

score 0 · Answer 2 · answered Feb 10 '21 at 18:05

@fabceolins answer is simple and good for normal scenarios, i noticed however Excel will still contain reference to IMPORTRANGE formula which can cause access issues.

I created Google App script to copy in the following method. If you can use Google App scripts, add the following functions:

function update_view(dup_id, TL="A1", BR="Z991") {
    // Open current Sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    // Supply a duplicate google doc ID. This document will be exported to excel
    var ds = SpreadsheetApp.openById(dup_id)
    // UI element for notifying in the google sheets
    var ui = SpreadsheetApp.getUi()
    //Copy each sheet one by one
    var sheets = ss.getSheets();
    for (i=0; i<sheets.length; i++) {
        src_sheet = sheets[i];
        sheet_name = src_sheet.getName();
        // If same sheet exists in the destination delete it and create an empty one
        dst_sheet = ds.getSheetByName(sheet_name);
        if (dst_sheet != null) {
            ds.deleteSheet(dst_sheet)
        }
        dst_sheet = ds.insertSheet(sheet_name);
        //set column width correctly
        for(j=1; j<=src_sheet.getLastColumn(); j++){
            dst_sheet.setColumnWidth(j, src_sheet.getColumnWidth(j))
        }
        src_range = src_sheet.getRange(TL + ":" + BR);
        dst_range = dst_sheet.getRange(TL + ":" + BR);
        //Note: DisplayValues is set as Values, formulas are removed in dup sheet
        dst_range.setValues(src_range.getDisplayValues());
        //Nice to haves for formatting
        dst_range.setFontColors(src_range.getFontColors());
        dst_range.setFontStyles(src_range.getFontStyles());
        dst_range.setBackgrounds(src_range.getBackgrounds());
        dst_range.setHorizontalAlignments(src_range.getHorizontalAlignments());
        dst_range.setVerticalAlignments(src_range.getVerticalAlignments());
        dst_range.setWraps(src_range.getWraps());

        dst_contents_range = dst_sheet.getDataRange();
        dst_contents_range.setBorder(true, true, true, true, true, true);
    }
    //Completed copy, Now open the dup document and export.
    ui.alert("Backup Complete, Please open " + dup_id + " sheet to view contents.")
}


function update_mydoc_view(){
// https://docs.google.com/spreadsheets/d/<spreadsheet_id>/
    update_view("<spreadsheet_id>")
}

To run the function, go to tools->macros->import , import the function and run update_mydoc_view().

After it is completed, export the google sheet into an excel document.

Rubén · Answer 3 · 2021-02-20T22:57:01.867

If you want to download a single sheet spreadsheet, instead of download it as .XLSX, download it as .CSV.

If by open the .CSV file by double clicking it shows strange characters, the default encoding of your computer is different from the one used by the Google servers, to fix this do the following:

Open Excel
Click File > Open
Select the .CSV file
The import wizard will be shown. One of the steps will allow you to select the file encoding, select UTF-8.
Once you finish with the import wizard save your file as .XLSX

score 0 · Answer 4 · answered Aug 10 '21 at 09:44

0

I did the following and it worked for me : Duplicate the file CTRL+A > CTRL+X > CTRL + V Paste Value only (from the paste icon displayed after pressing CTRL+V)

answered Aug 10 '21 at 09:44

kishan

1

score -1 · Answer 5 · edited Jul 11 '22 at 13:19

If you want to keep only the values from Sheet1, just select the data in the sheet, copy it, open a new Excel work book and when you paste, rather than using the conventional hotkeys Ctrl+V, right click cell A1 and select Paste values under the Paste options category of the right click menu.

If the problem is that the downloaded Excel does not have functional formulas that take information from your second sheet and instead show the formulas as text, do what the previous commenter said. check your view to make sure you are not in formula view. use the hotkey Ctrl+ ~, or go to the view tab to check your view options.

score -2 · Answer 6 · edited May 23 '17 at 12:24

-2

By default this is what happens - you will see the values not the formula.

Are you sure you are not in formula view in Excel?

If you check "Show formula" it will switch in formula view.

Or generally speaking you can try those:

MS Excel showing the formula in a cell instead of the resulting value

I would be surprised if it was indeed a google sheet problem - it's about Excel display.

edited May 23 '17 at 12:24

Community

1
1

answered Dec 08 '16 at 18:13

logicOnAbstractions

2,178
4
25
37

Google Sheets Export to Excel values instead of formulas

6 Answers6

Linked