Excel Practice Test

โ–ถ

Learning how to link files in excel is one of the fastest ways to turn scattered workbooks into a connected reporting system. Instead of copying numbers from one file into another and hoping nobody changes the source, you create a live reference that pulls the current value every time the workbook opens. This guide covers formula links, hyperlinks, Power Query connections, and repair tricks, with concrete examples you can follow in Excel 365, 2021, or 2019.

Learning how to link files in excel is one of the fastest ways to turn scattered workbooks into a connected reporting system. Instead of copying numbers from one file into another and hoping nobody changes the source, you create a live reference that pulls the current value every time the workbook opens. This guide covers formula links, hyperlinks, Power Query connections, and repair tricks, with concrete examples you can follow in Excel 365, 2021, or 2019. Practice with the free Excel practice test on PracticeTestGeeks (25 questions with answer explanations).

A linked file relationship has two sides. The source workbook holds the original data, such as monthly sales in a file called Sales_2026.xlsx. The destination workbook contains formulas that point to those cells. When the source changes, the destination can refresh automatically or on demand. This design keeps a single version of the truth, which matters for finance teams, schools tracking grades, and small businesses that keep a separate file for each region or department.

Most people first meet linking by accident. You copy a cell, paste it into another file, and later notice a formula that begins with a path in square brackets. That path is the external reference, and it tells Excel exactly which workbook, worksheet, and cell to read. Once you understand the pattern, you can write these references by hand, edit them in bulk, or build them dynamically with functions such as INDIRECT, VLOOKUP, and XLOOKUP.

There are three broad meanings of linking files. First, cell links bring numbers from one workbook into another through formulas. Second, hyperlinks let a reader click a cell and jump to a different file, folder, web page, or location inside the same workbook. Third, data connections, built with Power Query, import and transform whole tables from other files. Each approach solves a different problem, and choosing the wrong one is the most common reason linked reports break.

Linking has real costs, so use it deliberately. Every external reference creates a dependency, and moving or renaming the source can trigger the Update Values dialog or a #REF! error. Large networks of linked files also slow down opening time and make auditing harder. The benefits usually outweigh the risks when you follow naming conventions, keep files in stable folders, and document which workbook feeds which report. We cover those habits in detail later in this guide.

This article walks through the process in order. You will start with the quick click-based method, then learn the syntax behind it, then compare formulas, hyperlinks, and Power Query side by side. After that we cover maintenance, security prompts, cloud storage, and troubleshooting. Along the way you will see how related skills such as VLOOKUP in Excel, merging cells, freezing rows, and building drop-down lists fit into a well-designed linked workbook.

If you are preparing for an Excel certification, job assessment, or classroom exam, linking is a topic that appears more often than people expect. Questions typically ask you to identify the correct external reference syntax, explain what happens when a source file is closed, or choose between Break Link and Change Source. Use the practice tests linked below to check your understanding as you work through each section of this guide.

Free Excel Study Tools

Interactive Excel resources on PracticeTestGeeks, free to start:

Linking Files in Excel by the Numbers

๐Ÿ“Š
1,048,576
Rows per worksheet
๐Ÿ“‹
16,384
Columns per worksheet
โœ๏ธ
8,192
Max characters in a formula
๐Ÿ”„
4
Reference modes with F4
๐ŸŒ
3
Main ways to link files
Test Your Knowledge: How to Link Files in Excel

Step-by-Step: How to Link Two Excel Workbooks

๐Ÿ“‚

Open the source workbook that holds your data and the destination workbook where the result should appear. Having both open lets Excel build the reference for you and avoids typing mistakes in long file paths.

โœ๏ธ

In the destination workbook, click the cell where you want the linked value and type an equals sign. This tells Excel you are building a formula rather than entering plain text or a number.

๐ŸŽฏ

Switch to the source workbook using the View tab or the taskbar, then click the cell you want to pull. Excel inserts the workbook name, sheet name, and cell address, for example ='[Sales_2026.xlsx]Q1'!$B$4.

โœ…

Press Enter to finish. Excel returns you to the destination file with the value displayed. If you plan to fill the formula downward, press F4 inside the formula to remove the dollar signs and make the reference relative.

๐Ÿ’พ

Save the source first, then the destination. When you reopen the destination later, Excel will ask whether to update links. Choose Update to pull the newest numbers, or leave links alone to keep the last saved values.

Understanding External Reference Syntax in Excel

Every external reference follows the same pattern: the workbook name in square brackets, the sheet name, an exclamation mark, and the cell address. A complete example looks like ='C:\\Reports\\[Sales_2026.xlsx]Q1'!$B$4. When the source workbook is open, Excel shortens this to '[Sales_2026.xlsx]Q1'!$B$4. When the source is closed, the full folder path appears. Knowing both forms helps you recognize links inside inherited files and spot paths that point to a drive you no longer use.

Absolute versus relative references matter more with external links than with ordinary formulas. When you click a source cell, Excel pastes an absolute reference such as $B$4 by default. That is fine for a single value, but if you copy the formula down a column, every row will point at the same cell. Select the reference inside the formula bar and press F4 until the dollar signs disappear, then fill down to pull consecutive source rows.

Paste Link is a second method that many users prefer. Copy a cell or range in the source workbook, switch to the destination, right-click, and choose Paste Special, then Paste Link. Excel writes a formula for every copied cell, such as =[Sales_2026.xlsx]Q1!$B$4. This approach works well for a block of figures, like a twelve-month summary, because you create dozens of linked cells with a single operation instead of building each formula separately.

Security prompts surprise many first-time users. When you open a workbook containing external links, Excel may display a yellow bar saying that automatic update of links has been disabled, with an Enable Content button. Clicking it refreshes values from any source files it can find. You can change the default under Data, Queries and Connections, Edit Links, Startup Prompt. Organizations often set this centrally, so the behavior on a work laptop may differ from your home computer.

Linking works especially well with lookup functions. A typical example is =VLOOKUP(A2,'[Prices.xlsx]List'!$A:$C,3,FALSE), which finds a product code in the destination file and returns the third column from the price list stored elsewhere. VLOOKUP in Excel works even when the source workbook is closed, as long as the path is valid. XLOOKUP behaves the same way in Microsoft 365 and offers more flexibility because it can search in either direction.

Not every function tolerates closed source files. SUMIF, COUNTIF, and several similar functions return a #VALUE! error if the referenced workbook is not open at calculation time. If you need conditional totals from another file, either keep the source open, switch to SUMPRODUCT, which handles closed workbooks more reliably, or import the data with Power Query and summarize it locally. Testing with the source closed is a habit that prevents embarrassing surprises on report day.

Named ranges make linked formulas far easier to read. In the source workbook, select a cell and name it TaxRate using the Name Box. In the destination, the formula =Rates.xlsx!TaxRate replaces a cryptic address like '[Rates.xlsx]Config'!$D$12. If someone inserts rows in the source, the name moves with the cell, so your link keeps pointing to the correct value. This single habit removes a large share of broken-link tickets in shared workbooks.

EXCEL Practice Test: Video 1 Answers (25 Questions)
Check core Excel skills with 25 questions and clear answer explanations for each one.
EXCEL Practice Test: Video 2 Answers (25 Questions)
Practice formulas, references, and workbook features with 25 more answered Excel questions.

VLOOKUP Excel and Other Ways to Link Workbooks

๐Ÿ“‹ Formula Links

Formula links are the classic method. You write an external reference or a lookup such as VLOOKUP, and Excel stores both the formula and the last calculated value. They are ideal when a few specific cells or a lookup against a price list must stay current. The destination file remains small because only the results and the path are saved, not the entire source table.

The main drawback is fragility. If the source file is renamed, moved, or deleted, the formula keeps the old path and returns the cached value until you repair it with Change Source. Formula links also give you no easy way to reshape the data. When you need filtering, cleaning, or combining several files, a data connection is usually the better tool for the job.

๐Ÿ“‹ Hyperlinks

Hyperlinks do not pull numbers. They let readers jump to another file, folder, web page, or sheet. Select a cell, press Ctrl+K, and choose Existing File or Web Page, then pick the target. You can also use =HYPERLINK(path, friendly_name) to build links dynamically, for example combining a folder path in one cell with a file name in another.

Hyperlinks are perfect for dashboards and index sheets that guide people to supporting documents, such as a contract PDF or a monthly report. Use relative paths when the files travel together, since an absolute path like C:\\Users\\Sam\\Documents breaks the moment the folder is shared. Remember that a hyperlink opens the target but never refreshes data in your workbook.

๐Ÿ“‹ Power Query

Power Query, found under Data, Get Data, From File, imports entire tables or worksheets from another workbook, CSV file, or folder. It records every cleaning step, such as removing blank rows, changing data types, or merging tables, and replays them on each refresh. For recurring monthly reports built from many files, this saves hours compared with manual copying.

Loaded data lands in a table or the Data Model, and you refresh it with Data, Refresh All. Because the query stores the source location in one place, fixing a moved file takes seconds through Data Source Settings. The trade-off is a slightly steeper learning curve and the fact that Excel for the web has more limited refresh options than the desktop app.

Is Linking Files in Excel Worth It?

Pros

  • Keeps one source of truth, so numbers never drift between copies
  • Updates reports automatically when source data changes
  • Saves hours of repetitive copying and pasting each month
  • Lets teams maintain separate files while sharing one summary
  • Works with lookups such as VLOOKUP and XLOOKUP across workbooks
  • Keeps destination files smaller than embedding full data tables

Cons

  • Renaming or moving a source file can break every dependent link
  • Opening files with many links can be noticeably slower
  • Security prompts confuse users who are unsure about enabling content
  • Some functions, such as SUMIF, fail when the source is closed
  • Auditing a chain of linked workbooks takes patience and documentation
  • Sharing files by email often leaves the links pointing to unreachable paths

Pros and Cons at a Glance

ProsCons
Keeps one source of truth, so numbers never drift between copiesRenaming or moving a source file can break every dependent link
Updates reports automatically when source data changesOpening files with many links can be noticeably slower
Saves hours of repetitive copying and pasting each monthSecurity prompts confuse users who are unsure about enabling content
Lets teams maintain separate files while sharing one summarySome functions, such as SUMIF, fail when the source is closed
Works with lookups such as VLOOKUP and XLOOKUP across workbooksAuditing a chain of linked workbooks takes patience and documentation
Keeps destination files smaller than embedding full data tablesSharing files by email often leaves the links pointing to unreachable paths
EXCEL Practice Test: Video 3 Answers (23 Questions)
Work through 23 Excel questions covering common functions, formatting, and workbook management.
EXCEL Practice Test: Video 4 Answers (24 Questions)
Sharpen your skills with 24 Excel questions and detailed answer walkthroughs.

Checklist: How to Link Files in Excel Without Errors

Save the source workbook in a permanent, clearly named folder before linking.
Open both workbooks so Excel can build the reference by clicking.
Press F4 to switch between absolute and relative references as needed.
Use named ranges in the source for values that must survive row insertions.
Test the link with the source file closed to confirm it still calculates.
Open Data, Edit Links to review every source your workbook depends on.
Avoid SUMIF and COUNTIF across closed files, or switch to SUMPRODUCT.
Use relative paths for hyperlinks when files will be shared together.
Document which workbook feeds which report on a notes sheet.
Run Find on the opening bracket character to locate hidden external references.
Always Check Edit Links Before Sharing

Before you email a workbook, open Data, then Edit Links. Many files carry forgotten references to old budgets or drafts, and recipients will see a warning prompt. Remove what you do not need with Break Link, or paste values over formulas to deliver a clean, self-contained file.

How to Fix Broken Links and Manage Connections

Broken links announce themselves in a few predictable ways. You may see a dialog asking you to update values and browse for a file, cells showing #REF!, or formulas that display a path pointing to a drive that no longer exists. The first step is always to open Data, then Queries and Connections, then Edit Links. This window lists every source workbook, its status, and the last update time, giving you a map of the problem.

The Change Source button is the main repair tool. Select the broken source in the Edit Links list, click Change Source, and browse to the file's new location or replacement. Excel rewrites every formula that used the old path in one pass. This is the correct choice when a file was moved to a new folder, renamed, or replaced with a newer version that keeps the same sheet layout and cell positions.

Break Link serves a different purpose. It converts every formula that depends on a source into its current static value, permanently removing the connection. Use it when you want to archive a report or send numbers without the original data behind them. Be careful, because the action cannot be undone with Ctrl+Z after saving. Always keep a copy of the linked version before breaking links, especially in financial models you may need to update next month.

Sometimes Edit Links shows nothing, yet Excel still complains about external references. That usually means the link is hiding in a place the dialog does not examine well, such as a defined name, a chart series, a data validation list, a conditional formatting rule, or an object like a text box. Search the workbook with Ctrl+F, set Look in to Formulas, and search for .xls or the opening square bracket to find formulas.

Defined names are the most common hiding place. Open Formulas, then Name Manager, and scan the Refers To column for entries containing a file name in brackets. Delete names you no longer need or edit them to point at local ranges. Charts are another source: click a series and read its formula in the formula bar. Replacing an external series reference with a local range often clears a stubborn update prompt that survived every other cleanup.

Corrupted or very old workbooks occasionally carry phantom links that no standard tool can see. One reliable approach is to copy each worksheet into a brand new workbook using Move or Copy with the Create a copy box checked, then check Edit Links in the new file. If the prompt disappears after moving a particular sheet, you have isolated the culprit. Rebuild or clean that sheet and the rest of the workbook stays safe.

Prevention beats repair. Store linked files in a shared folder with a stable structure, avoid spaces and special characters in file names, and never rename a source without first updating dependents. Consider a version suffix only in archive folders, keeping the live file name constant. For teams, assign one owner per source workbook and write that owner's name on a notes sheet, so questions about a changed layout reach the right person quickly.

Linking Files in the Cloud and Following Best Practices

Cloud storage changes how links behave. When a workbook lives on OneDrive or SharePoint, Excel stores external references as web addresses beginning with https rather than as drive letters. That is helpful, because coworkers who sync the same library can open the destination without needing identical folder mappings. The trade-off is that renaming a SharePoint folder, or moving a library, can break links in ways that are harder to detect than a local path.

Co-authoring adds another wrinkle. If several people edit the destination file at once, link updates run on the machine of whoever opens it, and results may briefly differ until everyone saves. To limit confusion, designate a small number of source files as read-only inputs and let only the report owner edit formulas. Many organizations also schedule Power Query refreshes in Power BI or Power Automate to avoid depending on someone opening the file.

Layout choices in the source influence how stable links are. If a destination formula points to B4 and someone inserts a row above it, Excel adjusts the link only when both files are open. When the source is closed during the edit, the destination keeps pointing to B4 and quietly returns the wrong number. Excel tables and named ranges avoid this risk because references follow the data rather than the cell address, which is why professionals recommend them.

Freezing panes is a small habit that makes linked workbooks easier to audit. To learn how to freeze a row in Excel, select the row beneath the one you want to lock, open View, then Freeze Panes, and choose Freeze Panes. Keeping headers visible while scrolling a long list of linked values helps you spot a column that suddenly shows zeros or errors after a refresh, rather than discovering it three screens later.

Merged cells deserve caution. If you are wondering how to merge cells in Excel, select the range and choose Home, then Merge and Center, but avoid merging inside any range that other files reference. Merged cells keep only the top-left value, break sorting and filtering, and make Power Query imports messy. For titles and presentation sheets they are fine. For data that feeds other workbooks, use Center Across Selection instead and keep every cell separate.

Drop-down lists can also draw on linked data. To create a drop-down list in Excel, select a cell, open Data, then Data Validation, and set Allow to List. Older versions of Excel refuse a direct reference to another workbook, so name the source range in the file and use the INDIRECT function, or load the list into a local table with Power Query and point the validation to that table. This keeps menus consistent across departments.

Finally, document your design. A simple Read Me sheet that lists each source file, owner, refresh frequency, and the cells that depend on it turns a mysterious web of links into something a new colleague can maintain. Include the date of the last structural change, since layout edits are the most common cause of silent errors. Ten minutes of documentation can save an entire afternoon of detective work at quarter end.

Practice Linking Workbooks and Excel References

Practical Tips for Mastering Linked Workbooks

The best way to learn linking is to build a tiny practice system. Create a file named Source_Data.xlsx with a few rows of sales by region, then a second file named Report.xlsx that pulls totals using direct references, a VLOOKUP, and a Power Query import. Change values in the source, save, and watch each method respond. Seeing the three techniques react differently to the same edit teaches more than reading about them for an hour.

Practice breaking things on purpose. Rename the source file, close both workbooks, and reopen the report to see exactly what the update prompt says. Then use Change Source to repair it. Next, move the source to a different folder and repeat. When you have fixed the same failure three times, you will recognize it instantly in a real job, where the pressure of a deadline makes calm troubleshooting much harder than in practice.

Learn the keyboard shortcuts that speed up linking. Ctrl+K opens the hyperlink dialog, F4 cycles reference types, Ctrl+Tab or Ctrl+F6 switches between open workbooks, and Alt+A, then Z opens Edit Links on many keyboards. Alt+E, S, then L is the classic route to Paste Special options. Memorizing four or five of these shortcuts shortens every linking task and makes you noticeably faster during timed skills assessments used by employers.

Understand the difference between formulas that depend on other files and formulas that depend on other sheets. A reference such as =Sheet2!A1 stays inside the workbook and never triggers update prompts. Exams often test this by showing two formulas and asking which one creates an external link. The giveaway is the square bracket containing a file name. If you see brackets with an extension like .xlsx, the formula reaches outside the current workbook.

Build a personal troubleshooting routine. First, open Edit Links and read the status column. Second, search the workbook for brackets. Third, review Name Manager. Fourth, inspect chart series and data validation. Fifth, copy suspicious sheets into a new workbook to isolate them. Following the same order every time prevents the random clicking that wastes time, and it gives you a clear explanation to share with a manager or instructor when asked how you solved the issue.

Take practice questions regularly instead of cramming. Short sessions of ten or fifteen questions on formulas, references, and workbook management reveal gaps quickly, and the explanations teach you why a wrong answer looked tempting. Pay attention to questions about closed-workbook behavior, absolute versus relative references, and the difference between Break Link and Change Source, because those three concepts account for most linking mistakes seen in real spreadsheets and in certification practice material.

Finally, remember that linking is a design decision, not just a technique. Ask whether the data truly needs to stay live, who maintains the source, and what happens if the file disappears. Sometimes pasting values and recording the date is the more honest and robust choice. Strong Excel users know several ways to connect data and pick the lightest one that meets the need, which is exactly the judgment employers hope to see.

Free Excel Basic and Advance Questions and Answers
Review beginner and advanced Excel questions with explanations to build lasting confidence.
Free Excel Formulas Questions and Answers
Practice lookup, logical, and reference formulas with answers that explain every step.

Sample Excel Practice Questions

Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.

  1. Which Power Query feature lets you combine all CSV files in a folder into a single table with one query?

    • A. From Table/Range
    • B. From Web
    • C. From Folder with Combine Files
    • D. Data Validation

    Answer: C. From Folder with Combine Files

    The Folder connector plus Combine Files builds a sample transform and applies it to every file.

  2. In the Power Query Editor, what happens when you delete a step from the middle of the Applied Steps list?

    • A. Only that step is removed and later steps are re-evaluated
    • B. All steps after it are automatically deleted
    • C. The query is reset to its source
    • D. The step is hidden but still runs

    Answer: A. Only that step is removed and later steps are re-evaluated

    Removing a step removes only that step; subsequent steps still run and may error if they depended on it.

  3. After loading a query to a worksheet, the source CSV file receives new rows. What is the quickest way to bring those rows into Excel?

    • A. Re-import the file with a new query
    • B. Click Refresh All on the Data tab
    • C. Manually copy the new rows into the table
    • D. Delete the query and recreate it

    Answer: B. Click Refresh All on the Data tab

    Refreshing re-runs the saved steps against the current source data.

  4. A user wants to create a navigation menu on the first worksheet that links to specific cells in other worksheets within the same workbook. Which is the correct way to configure a hyperlink for this purpose?

    • A. In the Insert Hyperlink dialog, select 'Existing File or Web Page' and browse to the current file.
    • B. Use the HYPERLINK function with just the cell reference, like =HYPERLINK("D10", "Go to D10").
    • C. In the Insert Hyperlink dialog, select 'Place in This Document' and then choose the worksheet and enter the cell reference.
    • D. In the Insert Hyperlink dialog, select 'Create New Document' and link it back to the original workbook.

    Answer: C. In the Insert Hyperlink dialog, select 'Place in This Document' and then choose the worksheet and enter the cell reference.

    To create a link to a specific location within the currently open workbook, you should use the 'Place in This Document' option in the Insert Hyperlink dialog box. This allows you to select a specific worksheet and then type in the cell reference you want to link to.

Take the full Excel practice test

Excel Questions and Answers

How do I link one Excel file to another?

Open both workbooks, type an equals sign in the destination cell, switch to the source workbook, and click the cell you want. Press Enter and Excel writes the external reference for you. Save both files. When you reopen the destination, choose Update to refresh the linked values from the source file.

Why does Excel ask me to update links every time I open a file?

The workbook contains at least one external reference, so Excel warns you before pulling data from another file. You can change this under Data, Edit Links, Startup Prompt. If you no longer need the connection, use Break Link to convert the formulas into fixed values and the prompt will stop.

Can I link Excel files when the source workbook is closed?

Yes. Direct references and lookups such as VLOOKUP and XLOOKUP work with a closed source, as long as the file path is valid. Some functions, including SUMIF and COUNTIF, return #VALUE! unless the source is open. Test your formulas with the source closed before sharing the report.

How do I find all links in an Excel workbook?

Open Data, then Edit Links to see the main sources. For hidden ones, press Ctrl+F, set Look in to Formulas, and search for an opening square bracket or .xlsx. Also check Name Manager, chart series, data validation lists, and conditional formatting, because links often hide in those places.

What is the difference between Break Link and Change Source?

Change Source repoints the existing links to a different file, which is the right fix when a source was moved or renamed. Break Link permanently converts formulas into their current values and removes the connection. Break Link cannot be undone after saving, so keep a backup copy of the linked version.

How do I create a hyperlink to another file in Excel?

Select a cell and press Ctrl+K, then choose Existing File or Web Page, browse to the target, and click OK. You can also use the HYPERLINK function with a path and a friendly name. Hyperlinks open the target file but do not pull data into your workbook.

Does VLOOKUP in Excel work across different workbooks?

Yes. Write the table array with the workbook and sheet name, such as '[Prices.xlsx]List'!$A:$C. Build it while the source is open so Excel inserts the reference correctly. VLOOKUP then returns values even when the source is closed, though XLOOKUP offers more flexibility in Microsoft 365.

Why do my links break when I email a workbook?

External references usually store an absolute path, such as a folder on your own computer or network drive. The recipient does not have that path, so Excel cannot find the source. Send the source files too, store everything on SharePoint or OneDrive, or paste values before sending.

Is Power Query better than formula links?

Power Query is better when you need to clean, filter, or combine whole tables or multiple files, and it centralizes the source location for easy repair. Formula links are simpler for pulling a few cells or doing a quick lookup. Many professionals use both depending on the size and complexity of the task.

Can I use a drop-down list from another workbook?

Modern versions of Excel accept some cross-workbook references in data validation, but older ones do not. A reliable approach is to create a named range and use INDIRECT, or import the list into your workbook with Power Query and point the drop-down to that local table. Keep the source file accessible.
โ–ถ Start Quiz