To link a quantity from one worksheet to another in Excel, enter a cell reference such as =Inventory!B2 in the destination cell; it will show the source quantity and update when that source changes. There is no separate “quantity link” switch to enable. For a quantity in another workbook, use Paste Link or create an external reference, then manage whether Excel updates that workbook link.
What You’ll Need
- An Excel workbook with a source quantity and a destination cell where you want it to appear.
- For a link to another workbook, access to the source file and its saved location. Keep the file available if you expect links to update.
- A basic idea of which quantities you want to connect. For a single item, link its cell; for totals by item, use a summary formula such as SUMIF.
Step-by-Step
- Choose what the linked quantity represents. Identify the cell containing the authoritative quantity, such as the current stock count, and the cell that should display it. Decide whether you need a direct one-cell link, a total for a particular product, or a link between separate workbook files. Linking a single source cell is usually simplest. If the source is a manually typed number, changes to that number will flow through the link; the destination is not a separate quantity that updates the source.
- Set up clear source and destination locations. For example, name one worksheet Inventory and put a product name in A2 and its quantity in B2. On another sheet, such as Order, choose a destination cell for that quantity. Include headings such as Product and Quantity so you can tell what each value means later. Check that the source cell contains a number, not a text entry that only looks like a number. A number stored as text may not behave properly in totals or calculations.
- Enter a direct reference for a cell on another sheet. With the source quantity in Inventory!B2 and the destination on Order, select the destination cell and enter
=Inventory!B2, then press Enter. Excel displays the current value of Inventory!B2. If the sheet name contains spaces, use single quotation marks around it; for example, with the quantity in cell B2 on a sheet named Stock List, enter='Stock List'!B2. You can also start typing an equals sign, click the source sheet tab, select the source cell, and press Enter; Excel builds the reference for you. - Check that the reference updates. Change the number in the source cell to a temporary test value, then check the destination. It should show the new value. Undo the test or restore the original number when finished. If you see a formula instead of its result, check whether the destination cell is formatted as Text, or whether Show Formulas is turned on. A normal cell reference follows the source cell; it does not copy the source cell’s formatting or make the destination independently editable.
- Link more than one row carefully. If each row has a matching source row, fill the reference down. For example, if the first destination reference points to Inventory!B2 and the next row should point to Inventory!B3, drag the fill handle—the small square at the lower-right corner of the selected cell—down one row at a time. Excel normally adjusts relative references as it fills. Inspect a few resulting formulas to confirm they point to the intended source rows, especially if the source and destination lists are sorted differently.
- Use a lookup or total when rows do not match. A direct reference is not enough when an order row needs the quantity for a particular product code and the source list may be in a different order. With product codes in Inventory!A2:A100, quantities in Inventory!B2:B100, and the code to find in Order!D2, enter
=SUMIF(Inventory!A2:A100,D2,Inventory!B2:B100)in the destination. If Q-104 appears in the source with quantities 12 and 8, the result is 20. SUMIF adds every matching row, so it is useful when a product has multiple stock records. If each code is unique and you want one matching quantity rather than a sum, use a lookup formula instead, and verify that the code exists in the source. - Link a quantity from a separate workbook if needed. Open both workbooks, select and copy the source quantity cell, switch to the destination workbook, and select its target cell. On the Home tab, open the Paste menu and choose Paste Special, then select Paste Link if it is offered. The destination will refer to the source workbook instead of storing an independent value. Save both files. If the source file is moved or renamed later, Excel may not find it until you repair the link.
- Decide how to handle external workbook updates. When Excel asks whether to update links as you open a file, update only if you trust the source workbook and want the latest value. In Excel for Microsoft 365 on Windows, use Data > Workbook Links to review available links and their status; the exact controls can vary with updates and workbook type. Do not enable every external link simply to make a quantity appear. If you do not trust the source, keep links blocked and check the file with its owner before updating. On Mac or Excel for the web, the controls can differ; use the link or security prompt shown in that version, or open the workbook in desktop Excel to manage workbook links.
- Format and label the result. Apply an appropriate number format to the destination, such as a whole number for pieces in stock, and add a heading that identifies the quantity and product. Formatting changes how the value looks, not the value or the link. Avoid typing over the linked cell: doing so replaces its formula. If someone needs to enter an override, keep that input in a separate cell and make the distinction clear.
Common Mistakes
Typing a number instead of a formula: entering 20 in the destination copies the value only once. It will not follow later changes to the source. A linked destination should contain a formula, which you can inspect in the formula bar after selecting the cell.
Pointing to the wrong row: a valid reference can still return the wrong product’s quantity. This often happens after sorting one list but not the other, or filling a formula down beside a differently ordered list. Match items by a stable product code when row order is not guaranteed.
Replacing the link by accident: typing into a destination cell that contains a reference overwrites its formula. Restore it by undoing immediately, or re-enter the correct reference. If several people edit the sheet, consider protecting formula cells while leaving input cells unlocked.
Confusing a link with two-way syncing: a formula in the destination reads from the source; changing the destination does not change the source. Use the source cell as the place to update the quantity, or design a separate input process if users must make changes from different locations.
Using a fragile external file path: links may break when a source workbook is moved, renamed, or unavailable. Keep linked files in a stable shared location and avoid making multiple copies with different names unless you intend to update the references.
Tips
- Give worksheets descriptive names and use consistent product codes. Clear labels make references easier to create and audit.
- For a quick internal link, type
=, select the source sheet and cell, and press Enter. This avoids mistyping the sheet name or cell address. - When copying formulas, understand relative references. A reference such as B2 normally changes as it is filled down. If a formula needs to keep pointing to a fixed cell, use an absolute reference such as
$B$2; add the dollar signs only where the reference must stay fixed. - Use Data > Workbook Links to review external references in supported desktop versions of Microsoft 365. Remove or repair links you no longer need, rather than leaving old links to files that have been moved.
- Keep a clear distinction between entered quantities and calculated quantities. A color or a note can help users recognize that a cell is linked and should not be typed over.
- Save the workbook after setting up or repairing links, and test one source change before relying on the result for stock or order decisions.
Troubleshooting
The destination shows the formula text. Select the cell and check Home > Number Format. If it is set to Text, change it to General, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. Also check Formulas > Show Formulas; if that view is on, turn it off to display calculated results. On many Windows keyboards, Ctrl+` toggles Show Formulas; the key combination can differ on Mac keyboards, so the ribbon command is the dependable option.
The result is 0 or blank. Check that the reference points to the intended cell and that the source cell actually contains a value. If you used SUMIF, confirm the product code matches exactly, including spaces or punctuation, and that the quantity cells are numeric. A code that looks the same but has an extra space may not match. For a formula that should show blank when the source is blank, remember that a direct reference can display 0 depending on the cell and workbook settings.
Excel displays #REF!. The formula refers to a cell or sheet that no longer exists, or an external source that Excel cannot locate. For a same-workbook formula, edit the reference and select the correct sheet and cell again. For another workbook, check that the source file still exists and has not been renamed or moved. Use Data > Workbook Links, when available, to inspect or change the source path.
An external link does not update. Confirm that the source workbook is available, that you accepted an update prompt for a trusted file, and that the destination formula points to the intended source. Try saving and reopening the destination workbook. Excel for the web may not offer all the same link-management controls as desktop Excel; open the file in desktop Excel if you need to manage or repair a link there.
The number is wrong after filling down. Select the affected destination cells and inspect each formula in the formula bar. A relative reference may have shifted farther than intended, or row order may not match the source. Correct the first formula and refill only when rows correspond; otherwise match by product code with a lookup or SUMIF approach.
Frequently Asked Questions
Do I need to enable a setting to link quantities in Excel?
No. A reference between cells in the same workbook works as soon as you enter the formula. External workbook links may require you to approve an update or review a security prompt, but that is separate from creating the link.
Will the destination update when the source quantity changes?
Yes, for a normal cell reference, Excel recalculates the destination when the source changes. If it does not, check Formulas > Calculation Options and select Automatic, then verify the destination formula still points to the right cell.
Can I link cells between two worksheets?
Yes. Enter a reference to the source sheet and cell in the destination, such as =Inventory!B2. If the sheet name has spaces, put it in single quotation marks in the reference.
Can I link cells between two different Excel files?
Yes. Copy the source cell and use Paste Special > Paste Link in the destination workbook, or create an external reference. Keep the source file in a stable location and update external links only when you trust the source.
Can I change the quantity from the linked destination?
Not with a one-way formula link. The destination displays the source value; it does not write changes back. Edit the source quantity instead, or create a separate, deliberately designed data-entry workflow.
Why does my total show zero even though the item is listed?
Check that the lookup code matches the source code exactly and that the quantity values are numbers rather than text. If the code appears more than once, SUMIF adds all matching quantities, which may be different from the single-row result you expected.
Related
- Excel AutoSave: How to Turn It Off and Keep Changes Safe
- Delete a Protected Sheet in Excel: Steps and Fixes
- How to Delete Only Visible Rows in Excel Without Hidden Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Copy Only Visible Cells in Excel Without Hidden DataTo copy only visible cells in Excel for Microsoft 365 on Windows, select the range, choose Home >…
- Excel Import CSV: Does the First Row Become Headers?When you open a CSV file directly in Excel, its first row appears in row 1, but Excel…
- Create Labels in Word for Microsoft 365: Step by StepTo create labels in Word for Microsoft 365, open the desktop app, choose Mailings > Start Mail Merge…
- Add a Header in Excel on the First Page OnlyTo add a header only to the first printed page in Excel for Microsoft 365 on Windows, open…
- Excel Quick Print Shortcut Key: What Actually WorksExcel for Microsoft 365 on Windows has no built-in keyboard shortcut that sends a worksheet straight to the…
- Excel Consolidate Multiple Sheets: Steps and OptionsTo consolidate multiple sheets in Excel for Microsoft 365 on Windows, go to Data > Consolidate, choose a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.