Categories
Design

How to sync microsoft excel spreadsheets

How to sync microsoft excel spreadsheets

Related Articles

  • Terminal Permission Denied on OS X
  • Advantages & Disadvantages of Traditional File Organization
  • How to Transfer Your Blog Archives From Blogger to Web Host
  • The H303 & H505 Errors in QuickBooks
  • Types of File Encryption
  • Microsoft Licensing for Home Vs. Business

Your business, like most, probably uses a large number of Microsoft Excel spreadsheets to keep track of customers, sales, products and a wide variety of other data. When multiple employees need access to the most up-to-date file, however, there is a problem: keeping the same file synchronized across multiple computers and devices. There are several ways to achieve this goal, each with its own benefits and drawbacks. Keep in mind your business’s specific needs when choosing a method.

Step 1

Use networked storage to save files that are accessible by any computer on your network. Because there is only one copy of the file that lives on a network hard drive or server, that file is always the most up-to-date version. Also, because you must own the network drive, the file is kept securely within your property and is accessible by any computer connected to your office’s local network. However, it may be very difficult to access the file off-site using this technique, which is a benefit in terms of data security but may be a drawback if you need mobile or on-the-go access to your files. Another drawback is that (in general) only one user can be editing the file at a time; any other users who try to access the file will get a read-only version.

Step 2

Use cloud synchronization to keep folders synchronized on different computers. The most commonly used services are Dropbox and SugarSync. These programs store a copy of your files on the Internet using security that you cannot control. While your files will not be generally accessible to the public and these companies tend to take data security very seriously, it is possible for the companies to share your data as they deem appropriate. Both of these services allow multiple users to edit the same document at the same time, although they will not automatically merge any changes that are made. Both services offer excellent mobile apps that make it easy to access your files from anywhere.

Step 3

Use cloud storage to keep files synchronized and permit multiple users to work on the same file at once. Services like Google Drive and Microsoft SkyDrive allow you to upload your files and individually permit other users to access or change the files. These services allow multiple users to simultaneously view and change a single copy of the file, with changes propagated in real time to the other users. The data security and mobile access concerns with these services are identical to those of cloud synchronization services.

Step 4

Use the built-in cloud storage and synchronization features built into Windows 8 and Office 2013 if you are using these versions. By logging in with an Outlook.com, @MSN.com, @Live.com, or @Hotmail.com account with Windows 8 and Office 2013, you can save your spreadsheets to Microsoft SkyDrive and be able to transfer Windows and Office settings to multiple devices. Additionally, by using free OneNote apps on your Android, Windows Phone or iOS device you can access OneNote files — which can include pasted information from most other Office apps — on the go no matter what kind of smartphone or tablet device you have. This service has all of the same associated security concerns as other cloud synchronization services.

  • Dropbox
  • SugarSync
  • Google: Overview of Google Drive

Eric Fenton has been writing for journalistic and scientific publications since 2005. He has previously written for “The Pen,” where he was the opinion editor. He now works as a copy editor for the “News-Letter.” He is pursuing a Bachelor of Science in biomedical engineering from Johns Hopkins University.

How to sync microsoft excel spreadsheets

You can sync Microsoft Excel spreadsheets to ensure that changes in one will automatically be reflected in another. It is possible to create links between different worksheets as well as separate Excel workbooks. Let’s look at three ways to do this.

Sync Excel Spreadsheets Using the Paste Link Feature

The Paste Link functionality in Excel provides a simple way to sync Excel spreadsheets. In this example, we want to create a summary sheet of sales totals from multiple different worksheets.

Start by opening your Excel spreadsheet, clicking on the cell that you want to link to, and then selecting the “Copy” button on the “Home” tab.

How to sync microsoft excel spreadsheets

Select the cell that you are linking from, click the “Paste” list arrow, then select “Paste Link.”

How to sync microsoft excel spreadsheets

The address that the cell it is synced to is shown in the Formula Bar. It contains the sheet name followed by the cell address.

How to sync microsoft excel spreadsheets

Sync Excel Spreadsheets Using a Formula

Another approach is to create the formula ourselves without using the Paste Link button.

Sync Cells on Different Worksheets

First, click the cell you are creating the link from and type “=”.

How to sync microsoft excel spreadsheets

Next, select the sheet containing the cell you want to link to. The sheet reference is shown in the Formula Bar.

How to sync microsoft excel spreadsheets

Finally, click the cell you want to link to. The completed formula is shown in the Formula Bar. Press the “Enter” key.

How to sync microsoft excel spreadsheets

Sync Cells on Separate Workbooks

You can also link to a cell on the sheet of a different workbook entirely. To do this, you must first make sure that the other workbook is open before you begin the formula.

Click the cell you want to link from and type “=”. Switch to the other workbook, select the sheet, then click on the cell to link to. The workbook name precedes the sheet name in the formula bar.

How to sync microsoft excel spreadsheets

If the Excel workbook you have linked to is closed, the formula will shown the complete path to the file.

How to sync microsoft excel spreadsheets

And when the workbook containing the link to another workbook is opened, you will probably be greeted with a message to enable the update of links. This depends on your security settings.

Click “Enable Content” to ensure that updates in the other workbook are automatically reflected in the current one.

How to sync microsoft excel spreadsheets

Sync Excel Spreadsheets Using a Lookup Function

The previous methods of syncing two sheets or workbooks use links to a specific cell. Sometimes, this may not be good enough because the link will return the incorrect value if the data gets sorted and moved to a different cell. In these scenarios, using a lookup function is a good approach.

There are numerous lookup functions, but the most commonly used is VLOOKUP, so let’s use that.

In this example, we have a simple list of employee data.

How to sync microsoft excel spreadsheets

On another worksheet, we are storing training data about the employees. We want to search for and return the age of the employees for analysis.

This function requires four pieces of information: what to look for, where to look, the column number with the value to return, and what type of lookup you need.

The following VLOOKUP formula was used.

How to sync microsoft excel spreadsheets

A2 contains the employee ID to look for on the Employees sheet in the range A:D. Column 4 of that range contains the age to return. And False specifies an exact lookup on the ID.

The method you choose to sync Excel spreadsheets together is largely decided by how your data is structured and how it is used.

How to sync microsoft excel spreadsheets

You can sync Microsoft Excel spreadsheets to ensure that changes in one will automatically be reflected in another. It is possible to create links between different worksheets as well as separate Excel workbooks. Let’s look at three ways to do this.

Sync Excel Spreadsheets Using the Paste Link Feature

The Paste Link functionality in Excel provides a simple way to sync Excel spreadsheets. In this example, we want to create a summary sheet of sales totals from multiple different worksheets.

Start by opening your Excel spreadsheet, clicking on the cell that you want to link to, and then selecting the “Copy” button on the “Home” tab.

How to sync microsoft excel spreadsheets

Select the cell that you are linking from, click the “Paste” list arrow, then select “Paste Link.”

How to sync microsoft excel spreadsheets

The address that the cell it is synced to is shown in the Formula Bar. It contains the sheet name followed by the cell address.

How to sync microsoft excel spreadsheets

Sync Excel Spreadsheets Using a Formula

Another approach is to create the formula ourselves without using the Paste Link button.

Sync Cells on Different Worksheets

First, click the cell you are creating the link from and type “=”.

How to sync microsoft excel spreadsheets

Next, select the sheet containing the cell you want to link to. The sheet reference is shown in the Formula Bar.

How to sync microsoft excel spreadsheets

Finally, click the cell you want to link to. The completed formula is shown in the Formula Bar. Press the “Enter” key.

How to sync microsoft excel spreadsheets

Sync Cells on Separate Workbooks

You can also link to a cell on the sheet of a different workbook entirely. To do this, you must first make sure that the other workbook is open before you begin the formula.

Click the cell you want to link from and type “=”. Switch to the other workbook, select the sheet, then click on the cell to link to. The workbook name precedes the sheet name in the formula bar.

How to sync microsoft excel spreadsheets

If the Excel workbook you have linked to is closed, the formula will shown the complete path to the file.

How to sync microsoft excel spreadsheets

And when the workbook containing the link to another workbook is opened, you will probably be greeted with a message to enable the update of links. This depends on your security settings.

Click “Enable Content” to ensure that updates in the other workbook are automatically reflected in the current one.

How to sync microsoft excel spreadsheets

Sync Excel Spreadsheets Using a Lookup Function

The previous methods of syncing two sheets or workbooks use links to a specific cell. Sometimes, this may not be good enough because the link will return the incorrect value if the data gets sorted and moved to a different cell. In these scenarios, using a lookup function is a good approach.

There are numerous lookup functions, but the most commonly used is VLOOKUP, so let’s use that.

In this example, we have a simple list of employee data.

How to sync microsoft excel spreadsheets

On another worksheet, we are storing training data about the employees. We want to search for and return the age of the employees for analysis.

This function requires four pieces of information: what to look for, where to look, the column number with the value to return, and what type of lookup you need.

The following VLOOKUP formula was used.

How to sync microsoft excel spreadsheets

A2 contains the employee ID to look for on the Employees sheet in the range A:D. Column 4 of that range contains the age to return. And False specifies an exact lookup on the ID.

The method you choose to sync Excel spreadsheets together is largely decided by how your data is structured and how it is used.

To access Office files that are saved on your computer from your iPad, iPhone, Android device, or Windows device—you can upload the files to OneDrive, Dropbox, or a team site. Also, you can send email attachments to yourself and then open them on your mobile device.

In the sections below, you’ll find explanations on how to access files from various devices.

Upload and open files

On your computer, open your web browser and go to your OneDrive, Dropbox, or Microsoft 365 team site.

Tip: To set up a new online location, tap Open > Add a place, and select your service. To see additional locations that are available to you, tap Open > More.

Upload your file.

If you haven’t already done so, sign in to your iPad or iPhone.

Tap and sign in to the service in which you saved the file, and tap Open to find the file.

If you already have a file open, tap Back first.

Open email attachments

To open Word, Excel, PowerPoint, and Visio email attachments, do the following:

On your mobile device, open your mail app and find the email message with the attachment. You’ll see the attachments at the bottom of the message. You may have to tap the attachment once to download it.

Tap and hold the attachment until a group of icons appears.

Tap the icon to open your file in Word, Excel, PowerPoint, or Visio.

Upload and open files

On your computer, open your web browser and go to your OneDrive, Dropbox, or Microsoft 365 team site.

Tip: To set up a new online location, tap Open > Add a place, and select your service. To see all locations that are currently available to you, tap Open > Browse.

Upload your file.

If you haven’t already done so, sign in to your Android device.

Tap Open, tap the service in which you saved the file, and find the file.

How to sync microsoft excel spreadsheets

If you already have a file open, click the File icon or menu, tap Open, tap the service in which you saved the file, and then find the file.

Open email attachments

On your Android device, open your email app and then open the email message with the attachment. You’ll find the attachment at the bottom of the message. You may have to tap the attachment to download it.

Depending on its size, the download may take a few minutes. You’ll see a progress window while the file is downloading.

Tap the attachment to open it in Word, PowerPoint, or Excel.

Upload and open files

On your computer, open your web browser and go to your OneDrive, Dropbox, or Microsoft 365 team site.

Tip: To see all locations that are currently available to you, tap Open > Browse.

Upload your file.

If you haven’t already done so, sign in to your Windows Mobile device.

Tap the Word, PowerPoint, or Excel icon to open the app, locate your file, and tap to open it.

If you already have a file open, tap the File tab or menu, tap Open, and locate your file.

Open email attachments

On your Windows Mobile device, open your email app and open the email message with the attachment. You’ll find the attachment at the bottom of the message.

Tap the attachment to open it in Word, PowerPoint, or Excel.

I have an excel workbook which is shared with me and other members.

We “not the owner” of the excel workbook sometimes do not have the latest version of the file when we try to open via “Open in Excel” option within the OneDrive website location.

The “online” version of the file is the latest but not the one opened via “Open in Excel” option.

How can we force an update on the file opened via the “Open in Excel” option to equal the “Online” version?

** Moved thread to an appropriate category to get you proper assistance (Office > Word> Windows 10 > Office 365 for Business) by moderator

Replies (11) 

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Thanks for your post. Which version of Excel are you using, retail or an Insider build, and on what platform (OS)?

Was this reply helpful?

Sorry this didn’t help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Issue is reproduced with Office 365 Suite version 1709 (Build 8528.2139 Click to Run)

I would assume it is Retail version. (Comes with OneDrive subscription)

Issue was found on Windows 7 64bit and Windows 10 64 bit.

I temporarily fixed the issue with a work around by deleting all of the Cache file located in the following locations:

Hope this is enough information for Microsoft to find a solution for this problem.

Happened on 3 Windows 10 64bit and 1 Windows 7 64bit machines.

3 people found this reply helpful

Was this reply helpful?

Sorry this didn’t help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Glad to know the issue has been resolved.

When clicking on Open in Excel in a browser to open a workbook, normally we will open its latest version. In your cases, there might be cache issue in the systems. Hence, by deleting the cache, the issue is resolved.

Let us know if the issue occurs again. And please also share with us the following information:

1. Does the issue occur to all the people in your organization? What are their Office version?

2. What actions have the users performed before the issue occurred for the first time? For example, a system update and a software installation.

3. Does the issue occur to all the workbooks? If you create a new one, does the issue occur?

4. Where is the workbook stored?

Was this reply helpful?

Sorry this didn’t help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Issue hasn’t been reproduced yet, But this is just a workaround. There are some side effects, after deleting the Cache items.

1) When attempting to open a file which resides in “One Drive” Excel launching screen will appear then it will disappear. (No file is opened)

2) Try to open the same file from step one, will result in the Excel launching screen then some preview of the file being opened then any “Excel files” you already have open will be closed. (Seems like Excel crashed and closed all instances of Excel related files)

3) Attempting to open once more will be able to open the latest version on the “One Drive” account. (Web site contents)

But, if attempt to open another file which resides in “One Drive”, you will have to experience step 1 – 3 again.

So, this is not really a fix if you have a lot of files which reside in One Drive.

I still need a fix for this.

1) The issue did happen to all in the organization. 4 different people – 5 different machines.

The Office version are all the same – Office 365 Suite version 1709 (Build 8528.2139 Click to Run)

2) Not sure of a system update has been performed prior to issue occurance, but at least I am sure no program installation was performed.

3) Issue hasn’t occurred yet, It only happened on one file so far. (Excel File)

4) The workbook is stored on one User’s One Drive Account. (Her Documents folder)

File is shared to 3 other users with “READ ONLY” attributes.

Alan Murray has worked as an Excel trainer and consultant for the last 20 years, teaching people how to use Excel all over the world. He focuses on writing how-to articles and tips on using Microsoft Excel.

On most days, Alan can be found speaking about Excel in a classroom or seminar. When he’s not teaching, he can be found speaking about Excel online in YouTube videos and podcasts. Alan gets a buzz from helping people improve their productivity and working lives with it. His personal blog, Computergaga, offers Excel tutorials and training.

Alan lives in the UK and enjoys running, hiking and drinking real ale in his time away from spreadsheets.

About How-To Geek

How-To Geek is an online technology magazine and one of the top 500 websites in the US. Since it was created more than a decade ago, the site’s 10,000+ in-depth articles have been read over 1 billion times. More than 15 million people read our articles every month.

We’re the people you turn to when you want someone to explain technology, whether that means detailed troubleshooting guides or in-depth explainers on the latest tech. Our team of expert writers and editors have decades of experience in journalism and technology. Beyond the web, they’ve written books for publishers like Microsoft Press and have been published in print magazines like PCWorld and Consumers Digest.

How-To Geek has been recommended as an expert resource by industry groups like the Wi-Fi Alliance and newspapers like The New York Times. Organizations like the BBC and Wirecutter have directed their readers to us for our helpful tutorials. Our writers have been interviewed as experts on TV and radio programs. We’re an independent company that remains fully owned by our founders, too.

Money in Excel is Microsoft’s challenger to Mint, Quicken, and other personal-finance apps.

Senior Editor, PCWorld |

Today’s Best Tech Deals

Picked by PCWorld’s Editors

Top Deals On Great Products

Picked by Techconnect’s Editors

Microsoft has begun rolling out “Money in Excel,” a one-stop dashboard within Microsoft Excel for viewing your day-to-day finances, including bank and credit card transactions.

Microsoft announced Money in Excel in April as part of a broader rollout of a consumer view for Microsoft Teams, as well as a general rebranding of Office 365 as Microsoft 365. Now, Money in Excel is being offered as a downloadable template for either the Excel desktop app or the Excel online version, though you’ll need to subscribe to either Microsoft 365 Personal or the Family plan to be able to use it. (ZDNet’s Mary Jo Foley noticed that the template is now live for download.) You’ll also be able to open the template in the mobile Excel app, but most of the live functionality will be missing.

Microsoft recommends that users store the Money in Excel spreadsheet in their OneDrive Personal Vault, which adds two-factor authentication if it’s not already turned on, and imposes a significantly reduced timeout period.

Money in Microsoft Excel will pull in data from your bank and other financial sources.

Money in Excel is not Microsoft Money, though the two products certainly seem very similar. Microsoft discontinued Microsoft Money because of the competition banks themselves offered. “With banks, brokerage firms and Web sites now providing a range of options for managing personal finances, the consumer need for Microsoft Money Plus has changed,” the company said at the time.

Money in Excel consolidates your accounts from various services: your credit card provider, your bank, as well as any investments and retirement accounts you may own. That data is stored and tracked within Excel, using analytics to measure changes in your overall spending, for example, or to track recurring expenses like Netflix that carry over from month to month. (Money in Excel will also alert you if these change, Microsoft says.)

Money in Excel will also use a dashboard to provide an overview of your spending.

The connection between financial institutions like your bank will be managed by Plaid, which says that it has forged connections with 10,000 institutions across the United States. After granting permission for Plaid to connect a financial account with Money in Excel, Plaid will have access to the account’s balances, transaction history, and associated account information, like owner name and address, but not your Microsoft 365 login credentials, according to a Microsoft support document.

Money in Excel will divide up the worksheet into several tabs: a Welcome tab, an Instructions tab, and then separate tabs for a Snapshot overview, Categories, individual Transactions, and Recurring expenses.

Money in Excel will track recurring expenses.

To add an account, you’ll need to go to the Accounts tab and click Add an Account. According to Microsoft, transactions will not update automatically the spreadsheet, though the template will constantly keep an eye out for new additions. An “Update” button will appear if there are new transactions, and clicking it will update the spreadsheet.

After first connecting your financial accounts to Money in Excel, transactions from the past 30 days will appear in your transaction feed within a few minutes. New transactions will be ready for you to sync within a day of posting to your financial account, Microsoft says, after they’ve been cleared with your bank.

Money in Excel’s transaction view.

Microsoft’s support documents list two important caveats. First, Money in Excel is designed for individual use. If you wanted to share your data with someone else, they’d need to be logged in with your account and have the Money in Excel pane.

Second, there “may be occasions when Money in Excel engineers will need to look at anonymized data to fix a problem or to train our technology to be more accurate,” Microsoft warns. “Access is temporary and monitored.” It’s not quite clear what “anonymized” means in this context, so be warned.

Money in Excel is Microsoft’s entry into the personal-finance app world led by Mint, Quicken, and others. It may not look as pretty to begin with, but Microsoft’s banking (ahem) on its customers’ comfort with Excel to give it an edge.

As PCWorld’s senior editor, Mark focuses on Microsoft news and chip technology, among other beats.

How to sync microsoft excel spreadsheets

You can sync Microsoft Excel spreadsheets to ensure that changes in one will automatically be reflected in another. It is possible to create links between different worksheets as well as separate Excel workbooks. Let’s look at three ways to do this.

Sync Excel Spreadsheets Using the Paste Link Feature

The Paste Link functionality in Excel provides a simple way to sync Excel spreadsheets. In this example, we want to create a summary sheet of sales totals from multiple different worksheets.

Start by opening your Excel spreadsheet, clicking on the cell that you want to link to, and then selecting the “Copy” button on the “Home” tab.

How to sync microsoft excel spreadsheets

Select the cell that you are linking from, click the “Paste” list arrow, then select “Paste Link.”

How to sync microsoft excel spreadsheets

The address that the cell it is synced to is shown in the Formula Bar. It contains the sheet name followed by the cell address.

How to sync microsoft excel spreadsheets

Sync Excel Spreadsheets Using a Formula

Another approach is to create the formula ourselves without using the Paste Link button.

Sync Cells on Different Worksheets

First, click the cell you are creating the link from and type “=”.

How to sync microsoft excel spreadsheets

Next, select the sheet containing the cell you want to link to. The sheet reference is shown in the Formula Bar.

How to sync microsoft excel spreadsheets

Finally, click the cell you want to link to. The completed formula is shown in the Formula Bar. Press the “Enter” key.

How to sync microsoft excel spreadsheets

Sync Cells on Separate Workbooks

You can also link to a cell on the sheet of a different workbook entirely. To do this, you must first make sure that the other workbook is open before you begin the formula.

Click the cell you want to link from and type “=”. Switch to the other workbook, select the sheet, then click on the cell to link to. The workbook name precedes the sheet name in the formula bar.

How to sync microsoft excel spreadsheets

If the Excel workbook you have linked to is closed, the formula will shown the complete path to the file.

How to sync microsoft excel spreadsheets

And when the workbook containing the link to another workbook is opened, you will probably be greeted with a message to enable the update of links. This depends on your security settings.

Click “Enable Content” to ensure that updates in the other workbook are automatically reflected in the current one.

How to sync microsoft excel spreadsheets

Sync Excel Spreadsheets Using a Lookup Function

The previous methods of syncing two sheets or workbooks use links to a specific cell. Sometimes, this may not be good enough because the link will return the incorrect value if the data gets sorted and moved to a different cell. In these scenarios, using a lookup function is a good approach.

There are numerous lookup functions, but the most commonly used is VLOOKUP, so let’s use that.

In this example, we have a simple list of employee data.

How to sync microsoft excel spreadsheets

On another worksheet, we are storing training data about the employees. We want to search for and return the age of the employees for analysis.

This function requires four pieces of information: what to look for, where to look, the column number with the value to return, and what type of lookup you need.

The following VLOOKUP formula was used.

How to sync microsoft excel spreadsheets

A2 contains the employee ID to look for on the Employees sheet in the range A:D. Column 4 of that range contains the age to return. And False specifies an exact lookup on the ID.

The method you choose to sync Excel spreadsheets together is largely decided by how your data is structured and how it is used.

Users of UW-Madison’s institutional Tableau workbooks may need to pull data from one Microsoft Excel spreadsheet into another spreadsheet. This KB article explains how, by using an Excel formula called vLookup.

How to sync microsoft excel spreadsheets

  1. Locate where you want the data to go. Click that cell only once.

  • At the top, go to the Formulas tab and click Lookup & Reference.
    How to sync microsoft excel spreadsheets
  • Select vLookup
    How to sync microsoft excel spreadsheets

  • Excel’s vLookup wizard will pop up. We’ll walk through each part of the formula.
    How to sync microsoft excel spreadsheets
  • Lookup_value
    Find the Unique Identifier (lookup value). It is usually in the same row as the empty cell you selected.
    Click once on the Unique Identifier so that the cell position will automatically fill in. In this example it is cell B2.
    How to sync microsoft excel spreadsheets
  • Go to the next field, Table_array (click in it once). In Spreadsheet 2 highlight the table containing the info you want, starting with the Unique ID.
    How to sync microsoft excel spreadsheets

    In this example, Excel looks up Campus ID 555123123 in the first highlighted column of Spreadsheet 2.
    How to sync microsoft excel spreadsheets
    Note: Make sure each Unique ID is listed only once in the table_array (on the second spreadsheet) so that vLookup retrieves the correct value. For example, if 555123123 is duplicated in the table_array, where Student [email protected] is the email in one row and Student [email protected] in the other, Excel will choose one of the emails for you.

    Go to Col_index_num (click in it once). This identifies which column contains the information you want from Spreadsheet 2.
    Type the number of columns your field is from the Unique ID, where the Unique ID is 1. Here, the Email field is the third column.
    How to sync microsoft excel spreadsheets

  • Go to Range_lookup (click in it once). Type FALSE to search for exact matches. The result will look something like this:

  • Finally, copy and paste the formula to pull emails for the rest of the column.
    (Note: if your table array is in the same Excel workbook, put $ signs around the cell values, similar to the example below. This ensures that you reference the correct cells in the table array, meaning that the table array does not shift down when you paste the formula down. See Advanced Tip below for more details.)
    How to sync microsoft excel spreadsheets
  • vLookup Shortcut

    1. Type the beginning of the formula: =VLOOKUP(
      The formula guide will appear below.
      (Note: You may notice Excel displays the formula in 2 places: the formula bar above and directly in the cell. You can edit the formula in either place.)
      How to sync microsoft excel spreadsheets
    2. Follow the guide and enter each value. Remember to insert a comma between each value.

    Insert a closed parenthesis ) and hit Enter. The end result will look like something like this:
    =VLOOKUP(B2,'[Spreadsheet Name.xlsx]SheetName’!$B$1:$E$11,3,FALSE)

  • Finally, copy and paste the formula to pull emails for the rest of the column. Keep relative references in mind and use $ signs where necessary. (See Advanced Tip below for more details.)
  • The position of the lookup value (Unique ID) in relation to the vLookup formula is maintained when you copy and paste. If you paste the formula one cell down (to E3), it looks up the Unique ID that is also one cell down (B3). The same is true when copying right, left or up.

    In other words, the formula will stay x number of columns and y number of rows away from the lookup value – no matter where you paste the formula. In our example, the formula is the fourth column from the CampusID and in the same row. No matter where you paste the formula (in this example), it will always look up the cell that is the fourth cell to the left in the same row.

    • To lock in the lookup value in cell B1, insert $ signs before the column and the row:
      =VLOOKUP( $ B $ 1,’[Spreadsheet2.xlsx]SheetName’!$B$1:$E:$11,3,FALSE)
    • To lock in the column only, insert a $ before B only.
    • To lock in the row only, insert a $ before 1 only.

    How to sync microsoft excel spreadsheets

    Need More Information or Help?