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.”
You can also click “Sources” then “New Data Reference.”
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.
You also have the option to import the entire sheet click “Reference Sheet.”
If you are a spreadsheet guru and love a good formula you can use the “=REFERENCERANGE” or “REFERENCESHEET” formula in the desired cell.
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.
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 “refresh now,” “edit the range,” “unlink the range,” or “delete”. The data will automatically refresh when the referenced 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 spreadsheet. Clicking “Refresh now” will update the data immediately so you don’t have to wait.
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.