Data Referencing

What is Data Referencing?

Data Referencing allows you to reference a cell, range of cells, or sheet from one spreadsheet in another spreadsheet. This means no more toggling between spreadsheets and no more copy and pasting! Reference your data across sheets to avoid inefficiencies, improve communication and ensure your data is always up-to-date.

How does Data Referencing work?

Reference a cell, range of cells, or sheet

Saving time and always having up-to date data, this must be complicated to use right? Guess again! Follow the simple steps below and say goodbye to copy and paste.

1) Open and click into a spreadsheet, this can be a spreadsheet within a document or a stand alone spreadsheet. From the menu at the top of page click “Insert” then “Data Reference” or click “Sources” then “Data Reference.”

data1.png

You can also click “Sources” then “New Data Reference.”

data2.png

2) This window will pop up allowing you to search for the spreadsheet you would like to reference. Your recently viewed spreadsheets will automatically populate and you can search by spreadsheet name in the top left. Once you find the correct spreadsheet you can reference the data from a cell or range of cells by selecting the cell/cells you would like to reference and clicking “Reference” in blue on the far right.

data3.png

You also have the option to import the entire sheet click “Reference Sheet.”

data4.png

If you are a spreadsheet guru and love a good formula you can use the “=REFERENCERANGE” or “REFERENCESHEET” formula in the desired cell.

data5.png

Reference multiple cells, range of cells, or sheets

Why stop at referencing live data from one spreadsheet? Data referencing allows you to compile data from many different spreadsheets all in one spreadsheet. That way your data is always up to data and visible in one place. To add data from additional spreadsheets click into the cell where you would like to insert data and repeat the steps above for each new data set.

In the example below, we have an up to date summary budget spreadsheet referencing three different spreadsheets - 1) Marketing Budget 2) Sales Budget 3) Customer Success Budget. As each team updates their numbers they will automatically update here as well, keeping the total up to date.

data6.png

You may be thinking- if I have a spreadsheet with data from multiple spreadsheets, how do I keep track of what is referenced and where it came from? Don’t worry we’ve got you covered. Any cell with referenced data will be a light blue color. When you click into the cell you can then click “Data Reference” in the formula bar. From here you can open the data from its original source. You can also “sync formatting”, “refresh now,” “edit the range,” “unlink the range.” The data will automatically refresh whenever the spreadsheet is loaded and whenever the source data is updated. Keep in mind when data is updated from the original source it can take a couple of minutes to update on the referenced sheet. “Refresh now” will update immediately so you don’t have to wait. Sync formatting will automatically be on and will sync and formatting such as colors or bolding from the source spreadsheet. To turn this off just unselect “Sync Formatting.”

2020-01-15_17-40-43.png

Data Referencing FAQs

Who can create a data reference?
You must have access to the source spreadsheet in order to create a data reference. Once you create the data reference, others will be able to view the referenced data even if they do not have access to the source. If the last person who edited the reference formula loses access, the data reference will become invalid.

Who can update a data reference?
Anyone who has access to the source is able to update the data reference. If you do not have access to the source spreadsheet and attempt to edit the data reference, you will get an error.

Can I update the data in the original spreadsheet from the referenced spreadsheet?
No, you can’t update data from the reference spreadsheet. You would need to click into the original spreadsheet to make the update.

If I delete a data from the referenced sheet will it delete from the original spreadsheet?
Nope! If you delete from the referenced sheet it will not delete the data from the referenced sheet.

If I update the original data how long does it take for the data to be updated on the referenced sheet?
It could take several minutes for you to see the updated data. We fetch the data whenever you 1) open the referenced spreadsheet, 2) refresh the page, 3) change the original data.

Will my hidden columns and rows come through if I reference an entire sheet?
Yes, your hidden columns and rows will come through in the reference as if they are unhidden. Same goes for filtered out rows.

If I add rows or columns to the original spreadsheet will they automatically be added to the referenced sheet?
If you referenced the entire sheet by using the “REFERENCESHEET” function (check the formula bar to confirm), then new rows and columns added to the original sheet will be included in the reference. However, if you only referenced the range by using the “REFERENCERANGE” function, then that range is fixed - meaning if your range is A1:C3, only data in this specific range will be referenced. This also means if you add a row/column within this range, your new row/column will cause the original row/column to be cut off.

Can I use filters on referenced data?
Yes! Filters work on referenced data. For more information on filters check out this article.

Can I sort referenced data?
No, the sort functionality doesn’t work with referenced data.

When I reference @mentions and date reminders, will I get double notifications?
No, you will only get notifications for the source spreadsheet.

 

Fun ways to use data referencing

The possibilities are endless, but here are a few fun ideas for data referencing to get you started.

Managers always have their team’s up-to-date data

Susie is the manager of an engineering team and has each team member track their project progress in a spreadsheet and report progress out to her weekly. Now she uses data referencing to create one comprehensive project status spreadsheet, always knowing what stage projects are in without having to ask or meet with her team.

No more copy and paste

Vivek is on the Customer Success team and is regularly analyzing the same set NPS of data in different ways. He would copy and paste the data across spreadsheets and had difficulty keeping them all up to date when changes to one cell were made. Now he uses data referencing to analyze the same set of customer data differently, always knowing each spreadsheet is up to date.

Your Salesforce data all in one place

Jan is an event manager who closely tracks the cost per head to make sure her ROI is worth it. Calculating this used to be a manual process. Now she creates a spreadsheet where she tracks her budget and uses data referencing to pull in her guest registrations through a Salesforce report. As guests register, her new sheet adjusts by the minute, ensuring an update to date ROI metric.

Was this article helpful?
4 out of 5 found this helpful