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?
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
@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.
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:
.CSV file.XLSXRelated
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.
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.
If that isn't the answer, I can't help you. I'm having a similar problem where my formulas don't export correctly. that's why I came here for answers.
