2.5 Importing and refreshing data

In real life, data changes. You may receive a new export every week, or you may want to combine data from multiple sources. Excel can import data from many sources, including CSV files, text files, databases, and web pages.

If you had to manually copy and paste the data into Excel every time it changed, you would waste a lot of time. Power Query automates the import and refreshes the data when it changes.

2.5.1 Extract, transform, and load (ETL)

Power Query is Excel's data transformation and preparation engine. It implements the standard ETL (Extract, Transform, Load) pipeline:

1. Extract (Connect): Connect to source data (such as CSV files, Excel workbooks, databases, or web pages) and read it into memory.
2. Transform (Clean & Shape): Apply transformation steps such as filtering rows, changing data types, and renaming columns, to clean and restructure the data. Power Query records each step so they can be repeated automatically.
3. Load: Output the transformed data into an Excel Table for analysis in formulas, PivotTables, and PivotCharts.

Figure 1 shows this data pipeline. The process remains the same regardless of your data source.

flowchart LR
  subgraph Sources["Connect to a data source"]
        direction TB
        CSV["CSV / Text (.csv, .txt)"]
        XLS["Excel Workbooks (.xlsx)"]
        DB["Databases (SQL, Access)"]
        WEB["Web Pages & APIs"]
    end

  subgraph PQ["Power Query"]
    EXT["Extract\nConnect"]
    TRANS["Transform\nFilter, clean, reshape"]
    EXT --> TRANS
    end

  subgraph Dest["Load into Excel"]
    TBL["Excel Table\nWorksheet"]
    end

    Sources --> EXT
    TRANS --> TBL

Figure 1: Power Query connects to files, databases, and web sources, transforms the data, and loads the results into an Excel Table.

2.5.2 Save the data in a project folder

Power Query keeps a link to the file and records every step. Refresh repeats those steps on the current version of the file. A refresh only works when the file stays in a fixed place with a fixed name (Save the data in a project folder).

Save the files in a project folder before you import them. Each query stores the full path of its file.

Folders, files, and paths

Your computer keeps files in folders, and a folder can hold other folders. On Windows, the folders sit on a drive, such as C:. Every user has a user folder, C:\Users\<name> on Windows and /Users/<name> on a Mac.

A path names every folder from the drive down to the file. After the steps at the end of this section, the path of the consultation data is C:\Users\<name>\excel-patients\data\consultation_history.csv on Windows. On a Mac, it is /Users/<name>/excel-patients/data/consultation_history.csv.

In File Explorer, click the address bar to see the full path of the folder you have open. In Finder, choose View > Show Path Bar.

Where downloads go

Your browser saves downloaded files in the Downloads folder, unless you choose another folder. Move the files from Downloads into your project folder before you import them.

File name extensions

The extension is the end of a file name, such as .csv in consultation_history.csv or .xlsx in Patients.xlsx. Turn on extensions so you can see them:

• Windows 11: in File Explorer, select View > Show > File name extensions.
• Windows 10: in File Explorer, open the View tab and select File name extensions in the Show/hide group.
• Mac: choose Finder > Settings (Finder > Preferences on macOS 12 or earlier), open Advanced, and select Show all filename extensions.

With extensions hidden, a file you rename to consultation_history.csv can end up as consultation_history.csv.csv.

OneDrive and synced folders

OneDrive can change the path of your files and keep some files only online (Figure 2 (a) for Windows, Figure 2 (b) for a Mac). Keep the following limits in mind:

• OneDrive folder backup can move Desktop and Documents into OneDrive, so the paths of those folders change.
• Online-only files must be downloaded before another program can read them.
• With OneDrive folder backup, a full path including the file name must stay under 260 characters. File names can't start with a space or contain any of these characters: \ : / * ? < > " |.

Put the project folder in your user folder. If the project folder has to be in OneDrive, right-click the project folder and choose Always keep on this device.

(a) OneDrive on Windows

(b) OneDrive on a Mac

Figure 2: OneDrive can change file paths and online availability on both Windows and Mac.

Good habits for data that changes

The following habits keep your queries working when a new version of the data arrives:

• Folders and names:
  • Use one folder per project.
  • Keep the file names in data the same, because the queries always read those names.
  • Use short names without spaces or accents, and avoid names like final_v2_new.xlsx.
  • Put dates only in the names of files in data/archive.
• A new version of a file:
  1. Move the old file to data/archive and put the date at the start of its name, for example 2026-09-12_consultation_history.csv.
  2. Put the new file in data under the usual name.
• Safety:
  • Save a dated copy in data/archive before you change a file you received.
  • Copy the project folder to a backup location before you make big changes, such as deleting rows or changing formulas.

Create the folders and download the files

The project folder excel-patients holds the CSV files in data, earlier versions in data/archive, and your Excel files in workbooks (Listing 1).

Listing 1: Project folder structure for the patient data workbook and CSV files used in the imports.

excel-patients/
  data/
    consultation_history.csv
    patient_data.csv
    archive/
  workbooks/

1. Open your user folder. In File Explorer, type %USERPROFILE% in the address bar and press Enter. In Finder, choose Go > Home.
2. Create a folder named excel-patients. In File Explorer, choose New > Folder on the toolbar. In Finder, choose File > New Folder. Type the name and press Enter (Mac: Return).
3. Inside excel-patients, create the folders data and workbooks the same way.
4. Inside data, create the folder archive the same way.
5. Download consultation_history.csv and patient_data.csv. Move both files from Downloads into data.
6. Check that both file names end in .csv. The path of data should be C:\Users\<name>\excel-patients\data on Windows, or /Users/<name>/excel-patients/data on a Mac.

2.5.3 Import the CSV files with Power Query

The 2 CSV files match the 2 worksheets in Patients.xlsx, and you already saved them in data (Save the data in a project folder).

1. Open Excel and create a new blank workbook.
2. Go to Data > Get Data > From File > From Text/CSV. On a Mac (Microsoft 365, version 16.69 or later), choose Data > Get Data (Power Query) > Text/CSV.
3. Select consultation_history.csv in the data folder of excel-patients and click Import.
4. In the import preview, make sure the delimiter is a comma (,). Click Transform Data.
5. In the Power Query Editor (Figure 3), check the following:
   • Headers. Power Query usually promotes them already (Promoted Headers in Applied Steps). Use Home > Use First Row as Headers (Figure 4) only when the headers still show as the first data row. Clicking it again turns the first data row into headers.
   • Data types. Check whether each column holds whole numbers, decimal numbers, or text. If something looks wrong, click the data type icon in the column header to change it. If 101.38 shows as 10138, right-click the column header and choose Change Type > Using Locale. Select Decimal Number and English (United States), and choose Replace current if Power Query asks.
   • Blank rows (optional). Remove them with Home > Remove Rows > Remove Blank Rows.
6. Click Home > Close & Load > Close & Load To. Choose Table and New worksheet, then click OK.
7. Rename the created worksheet to consultation_history.
8. Repeat steps 2 to 7 for patient_data.csv (load it as a Table into a new worksheet and rename the sheet to patient_data).
9. Save your workbook in the workbooks folder as Patients.xlsx.

Figure 3: The Power Query Editor in Excel, where you can review data types, transform columns, and prepare the data before loading it into a worksheet.

Figure 4: Using the Power Query editor to promote the first row of data to column headers.

To check the import, open Data > Queries & Connections. The Queries tab lists both queries, each with "2,100 rows loaded".

NoteCheckpoint: your rebuilt workbook

Delete the empty sheet Sheet1 (right-click its tab and choose Delete). You should now have a workbook with 2 worksheets named consultation_history and patient_data. The steps in Keep the workbook up to date use this workbook.

Microsoft Support:

• About Power Query in Excel
• Create, load, or edit a query in Excel (Power Query)

2.5.4 Keep the workbook up to date

When the CSV files change, refresh the workbook to load the new data.

What a refresh does

A refresh makes Excel read the file again from its saved path and repeat every step the query recorded. Excel then replaces the loaded Table with the result.

Values you type into the loaded Table can be overwritten by the next refresh. Add or change data in the source file, and put your own calculations on other sheets.

Refresh one query or everything

To refresh one query, select a cell in its Table. Then go to Data, click the arrow next to Refresh All, and choose Refresh (Alt+F5 on Windows). You can also right-click the query in Queries & Connections and choose Refresh.

To refresh everything in the workbook, choose Data > Refresh All (Ctrl+Alt+F5 on Windows).

On a Mac, choose Data > Refresh All, or right-click a cell in the Table and choose Refresh.

While a refresh runs, the status bar shows Retrieving Data. Press Esc to cancel it.

Refresh when the workbook opens

Excel can refresh a query each time you open the workbook. The following steps are for Windows.

1. Open Data > Queries & Connections and go to the Connections tab.
2. Right-click Query - consultation_history and choose Properties.
3. On the Usage tab, select Refresh data when opening the file.
4. Click OK.

Repeat the steps for patient_data.

PivotTables built on the loaded data

Refresh All updates both the queries and the PivotTables in the workbook. A PivotTable can refresh before its query finishes and miss the new data. On Windows, open the query's Properties again, clear Enable background refresh on the Usage tab, and click OK. The query then finishes before the PivotTables refresh.

Change a value and refresh

Use Patients.xlsx from the workbooks folder. The steps change 1 value in consultation_history.csv and restore the value at the end.

1. Copy. Right-click consultation_history.csv in data and choose Copy. Open archive, right-click an empty spot, and choose Paste (Mac: Paste Item). To rename the copy, select it and press F2 on Windows or Return on a Mac. Put the date at the start of the name, for example 2026-09-12_consultation_history.csv.
2. Open. Right-click data/consultation_history.csv and choose Open with > Notepad (Mac: Open With > TextEdit). Double-clicking a CSV file opens it in Excel, and a file that is open in Excel can block the refresh.
3. Edit. Press Ctrl+F (Mac: Command+F) and search for 101.38. The value appears once, on the line 543,2,2,2,male,79,56.69,10,VW180,101.38,. Change 101.38 to 1000 and save the file.
4. Refresh. In Excel, choose Data > Refresh All.
5. Check. On the sheet consultation_history, check that cell A181 shows 543 and cell J181 shows 1000. Queries & Connections still shows "2,100 rows loaded".
6. Restore. In Notepad or TextEdit, change 1000 back to 101.38 and save the file. Refresh again and check that J181 shows 101.38.

When a refresh fails

A refresh can fail for the following reasons:

• The file moved or was renamed. Put the file back, or point the query to the new location:
  • On Windows, open Data > Get Data > Data Source Settings and keep Data sources in current workbook selected. Then select the file and choose Change Source.
  • On a Mac, choose Data, click the arrow next to Get Data (Power Query), choose Data Source Settings, and select Change File Path.
• The file is online-only in OneDrive and you are offline. Connect to the internet and refresh again.
• A column was renamed or removed in the new file. Right-click the query in Queries & Connections and choose Edit. The Power Query Editor shows a yellow bar with an error such as "Expression.Error: The column '...' of the table wasn't found". Select Go To Error to see the step that failed.
• The CSV file is open in another program. The file may be locked, and unsaved changes are not included. Save and close the file, then refresh again.

Excel for Mac can also show a Grant File Access dialog. Choose Select..., select the excel-patients folder, and click Grant Access.

2.5.5 Exercise: a dashboard that follows the data

Build the dashboard

The sheet dashboard has 5 numbers, a summary by type, and 2 charts, all based on the loaded Table consultation_history. The Table changes when you refresh its query. Formulas that read the Table update right away, and PivotTables and PivotCharts update on Refresh All. The tutorial covers Tables and PivotTables (Excel table, Pivot table).

Power Query names a loaded Table after its query. Select a cell on the sheet consultation_history and check that Table Design > Table Name shows consultation_history, the name the formulas use (Table 1). If your Table has another name, use that name in the formulas. UNIQUE and SORT need Microsoft 365, Excel 2021, or Excel 2024, on Windows or Mac.

Table 1: Dashboard layout

Cells	Label	Formula
A3, B3	Surgeries	=COUNT(consultation_history[Registered duration])
A4, B4	Average duration (minutes)	=AVERAGE(consultation_history[Registered duration])
A5, B5	Longest surgery (minutes)	=MAX(consultation_history[Registered duration])
A6, B6	Shortest surgery (minutes)	=MIN(consultation_history[Registered duration])
A7, B7	Surgery types	=COUNTA(UNIQUE(consultation_history[Surgery type]))
A13, A14	Surgery type	=SORT(UNIQUE(consultation_history[Surgery type]))
B13, B14	Surgeries	=COUNTIF(consultation_history[Surgery type], A14#)
C13, C14	Average duration (minutes)	=AVERAGEIF(consultation_history[Surgery type], A14#, consultation_history[Registered duration])

1. Sheet. Open Patients.xlsx from the workbooks folder. Add a worksheet and rename it to dashboard.
2. Numbers. Type the labels in A3:A7 and the formulas in B3:B7 (Table 1).
   • consultation_history[Registered duration] refers to the whole Registered duration column of the Table.
   • Use the column Surgery type in the formulas. Surgery types, the last column, is a separate list of the 27 codes.
   • Format B4:B6 with 2 decimals.
3. Summary by type. Type the headers in A13:C13 and the formulas in A14, B14, and C14. If your Excel uses ; as the list separator, replace the commas in B14 and C14 with semicolons. Each formula spills into the cells below, with 1 result per surgery type. A14# refers to the whole spilled list that starts in A14 (Dynamic arrays). Keep the cells below row 14 in columns A to C empty. Anything in those cells blocks the spill, and the formula shows #SPILL!. Format C14:C40 with 2 decimals.
4. Chart by type.
   • Select a cell in the Table on the sheet consultation_history and choose Insert > PivotTable. In the window that opens, Table/Range shows consultation_history. Choose New Worksheet and click OK.
   • Rename the new sheet to pivot_type. Drag Surgery type to Rows and to Values, where it shows as "Count of Surgery type". Right-click a count and choose Sort > Sort Largest to Smallest.
   • Choose PivotTable Analyze > PivotChart, select Column, and click OK. On a Mac, select a cell in the PivotTable, choose Insert, click the column chart button, and choose Clustered Column.
   • Right-click an empty area of the chart and choose Move Chart (Mac: Chart Design > Move Chart). Select Object in, choose dashboard, and click OK. Drag the chart so it starts in column E.
5. Chart by day. Build another PivotTable the same way on a new sheet pivot_day, with Day in Rows and Surgery type in Values. Don't sort it, so the days stay in order from 0 to 4. Insert a column PivotChart, right-click a bar, and choose Add Data Labels (Mac: Chart Design > Add Chart Element > Data Labels > Outside End). Move the chart to dashboard, below the chart by type.
6. Check. Compare the dashboard with the Before column (Table 2), then save the workbook.

Delete rows and refresh the dashboard

The steps delete 11 surgeries from consultation_history.csv (the 9 surgeries of type AX939, the longest, and the shortest). You then refresh the workbook, compare the dashboard with the values before the deletion, and restore the file at the end.

1. Copy. Save a dated copy of consultation_history.csv in archive, as in step 1 of Change a value and refresh. If the copy from that exercise is still in archive, you can use it.
2. Open. Right-click data/consultation_history.csv and choose Open with > Notepad (Mac: Open With > TextEdit). Don't open or save the file in Excel. With Dutch settings, Excel saves a CSV file with semicolons and decimal commas, and the import no longer works.
3. Delete. Delete the 11 lines in the following order, then save the file and close Notepad or TextEdit.
   1. AX939 rows. Search for ,AX939,, with a comma on each side. With the commas, the search matches the code only in the Surgery type column. Without them, the search also finds line 3, where AX939 sits at the end of the line in the Surgery types list.
      • On Windows, press Ctrl+Home to start at the top of the file. Press Ctrl+F, type ,AX939,, and press Enter. Press Esc, then Home, then Shift+Down arrow to select the line, and press Delete. Repeat until the search finds nothing (9 lines).
      • On a Mac, press Command+Up arrow to start at the top of the file. Press Command+F, type ,AX939,, and press Return. Click the highlighted line in the text. Press Command+Left arrow, then Shift+Down arrow to select the line, and press Delete. To find the next line, click in the search field and press Return. Repeat until the search finds nothing (9 lines).
   2. Longest surgery. The file is sorted by Registered duration, longest first, so the longest surgery is on the first line under the header. Press Ctrl+Home, then Down arrow, then Shift+Down arrow (Mac: Command+Up arrow, then Down arrow, then Shift+Down arrow). The selection is the line for patient 70, 70,0,1,1,male,83,62.44,13,WP174,160.61,AF241. Press Delete. The line also held AF241 from the Surgery types list, which the dashboard doesn't use.
   3. Shortest surgery. The shortest surgery is on the last line. The value 2.09 appears 3 times in the file, so find the line by its position. Press Ctrl+End, then Shift+Up arrow (Mac: Command+Down arrow, then Shift+Up arrow). The file ends with a line break, so the cursor moves to an empty line below the last line. Shift+Up arrow then selects the whole last line. The selection is the line for patient 853, 853,4,0,0,female,60,58.0,12,ID810,2.09,. Press Delete.
4. Refresh. In Excel, choose Data > Refresh All. If background refresh is still on, wait until Retrieving Data disappears from the status bar and choose Refresh All again (PivotTables built on the loaded data). If the refresh fails, see When a refresh fails.
5. Check. Compare the dashboard with the After column (Table 2). Queries & Connections shows "2,089 rows loaded". The AX939 row is gone from the summary by type, and row 40 is empty with no error. In the chart by day, 3 bars are below 420. Day 4 lost the 9 AX939 surgeries, day 1 lost the longest surgery, and day 0 lost the shortest.
6. Restore. Delete consultation_history.csv from data. Copy the dated file from archive into data and rename the copy to consultation_history.csv. Choose Data > Refresh All and check that the dashboard shows the Before column and Queries & Connections shows "2,100 rows loaded".

Table 2: Dashboard before and after the deletion

Check	Before	After
Queries & Connections	2,100 rows loaded	2,089 rows loaded
B3 (Surgeries)	2100	2089
B4 (Average duration)	65.89	65.68
B5 (Longest surgery)	160.61	158.21
B6 (Shortest surgery)	2.09	2.27
B7 (Surgery types)	27	26
Summary by type	Rows 14 to 40	Rows 14 to 39
Row 15	AX939, 9, 111.83	BP285, 49, 62.91
WP174 row	WP174, 254, 81.89	WP174, 253, 81.58
ID810 row	ID810, 90, 76.04	ID810, 89, 76.87
Chart by type	27 bars, AX939 (9) last	26 bars, NO290 (15) last
Chart by day, days 0 to 4	420, 420, 420, 420, 420	419, 419, 420, 420, 411

If the dashboard doesn't match the After column, look for the following problems:

• A (blank) row or bar. The PivotTable uses a fixed range of cells as its source. Select a cell in the PivotTable, choose PivotTable Analyze > Change Data Source > Change Data Source, and type consultation_history in Table/Range.
• Charts with the old values. The PivotTables refreshed before the query finished. Choose Refresh All again, or turn off background refresh (PivotTables built on the loaded data).

2.5.6 Practice with larger files

Download the following datasets and try to import them into Excel:

• Forecasts for Product Demand (Kaggle)
• Sales history (S. P. Richards Company)
• Status and dimensions of SKUs (S.P. Richards Company)

The downloads are .zip files. Extract them first (right-click the file and choose Extract All).

Give each dataset its own project folder, and move the extracted files into its data folder.

Small steps! Import a couple of lines first. Check if it works, then proceed with the rest.

Excel has a limit of 1,048,576 rows and 16,384 columns per sheet (see Excel specifications and limits).