CODEWITHSUNDEEP
google-sheetsexport-to-excel
Eric Hendershott
Eric Hendershott

Reputation: 643

Google Sheets Export to Excel values instead of formulas

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?

Upvotes: 5

Views: 17272

Answers (6)

Just A Guy
Just A Guy

Reputation: 1

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.

Upvotes: -1

kishan
kishan

Reputation: 1

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)

Upvotes: 0

Wicket
Wicket

Reputation: 38130

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:

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

Related

Upvotes: 0

clueless
clueless

Reputation: 1

@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.

Upvotes: 0

Fabr&#237;cio Ceolin
Fabr&#237;cio Ceolin

Reputation: 554

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

Upvotes: 2

logicOnAbstractions
logicOnAbstractions

Reputation: 2580

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?

enter image description here

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.

Upvotes: -2

Related Questions