10.9 Refresh and Load Options
Home › Close & Load ▾ › Close & Load To… gives the choices:
| Option | What happens | When to use |
|---|---|---|
| Table | Loads to a worksheet as an Excel Table | You need to see/use the rows |
| PivotTable Report / PivotChart | Loads straight into a pivot | Summary only |
| Only Create Connection | Query stored, nothing on a sheet | Staging queries used by Append/Merge |
| Add this data to the Data Model | Loads to Power Pivot's in-memory model | Large data (beyond 1,048,576 rows), relationships, Distinct Count, DAX |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Aamir Khan) are made up for this lesson on refresh and load options.
Steps in Excel – refresh
- Data › Queries & Connections › Refresh All (Ctrl + Alt + F5), or right-click a query in the Queries & Connections pane › Refresh.
- Query Properties (right-click query › Properties…): Refresh every n minutes, Refresh data when opening the file, Enable background refresh.
- Change load destination later: right-click the query › Load To….
- Data › Get Data › Query Options – global settings such as privacy levels and default load.
Ravindra Bagale's Tip
If staging queries (FreshGo, MarketRush) are also loaded as Tables, the workbook gets heavy and fills up with sheets – many students end up with 5–6 such sheets. Keep the in-between queries as Only Create Connection, and load only the final query. And for 10 lakh+ rows, load into the Data Model, not onto a sheet.
Ravindra Bagale's Tip – मराठी
Staging queries (FreshGo, MarketRush) पण Table म्हणून load केल्या की workbook जड होतं आणि sheets भरतात – बरेच students अशा 5-6 sheets बनवतात. मधल्या queries Only Create Connection ठेवा, फक्त final query load करा. आणि 10 लाख+ rows असतील तर Data Model मध्ये load करा, sheet वर नाही.
Ravindra Bagale's Tip – हिंदी
Staging queries (FreshGo, MarketRush) को भी Table के रूप में load किया तो workbook भारी हो जाता है और sheets भर जाती हैं – बहुत से students ऐसी 5-6 sheets बना लेते हैं. बीच वाली queries को Only Create Connection रखो, सिर्फ़ final query load करो. और 10 लाख+ rows हों तो Data Model में load करो, sheet पर नहीं.
Worked example — refresh and load options
Why
Neha Shah in Pune and James O'Neill abroad both need a clear, repeatable way to use refresh and load options on a Orbit Logistics sheet without breaking totals.
What
Refresh And Load Options is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for refresh and load options.
- Enter a small made-up value for Neha Shah using $ where money appears.
- Add a second value for James O'Neill using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once refresh and load options is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of refresh and load options on a blank sheet for Summit Schools.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Neha Shah and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Oliver Bennett.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply refresh and load options to a 5-row made-up Summit Schools table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with refresh and load options.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as SummitSchoo_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Rows in the import (Easy): How many rows are in the Order ID column?
- Clean one customer name (Easy): Remove the extra spaces from the first Customer name.
- Fix the case of a city (Easy): Show the first City in normal case, like "Pune".
- Total quantity (Easy): Add up the Qty column.
- How many spaces were extra? (Medium): How many extra spaces does the first Customer name have?
- Duplicate order check (Medium): How many times does Order ID "ORD-502" appear?
- Orders from one city, any case (Medium): How many rows have City "Berlin", in any case?
- Amount by channel (Medium): Add up the Amount column for rows where Channel is "App".
- Average order amount (Medium): Find the average of the Amount column, rounded to 0 decimals.
- Rows after Remove Duplicates (Hard): How many rows are left after the extra copies of "ORD-502" are removed?
- Biggest store order (Hard): What is the largest Amount where Channel is "Store"?
- Price per unit (Hard): For the first row, divide Amount by Qty.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
