Power Query Problems

How to Automatically Import CSV Data into an Excel Calculation Template

Maira Mehtab Sep 24, 2026 870 views

Question details

The user wants to automate the process of importing CSV blueprint measurements into an Excel spreadsheet, allowing the file to automatically categorize data, calculate totals, and run formulas without requiring manual cell selection or copying.

Product: Excel
Device & OS: not provided
Scenario: Importing blueprint measurements from CSV files for automated auditing and category calculations.
Observed behavior: The current workflow requires manual copying, pasting, and cell selection to organize data into sections and calculate totals. The goal is a fully automated import and calculation process.

Before you start

Ensure all your incoming CSV files follow a consistent column structure (e.g., Subject, Layer, Measurement, Unit) before setting up your automated template, as changing headers later can break the data query.

Solution 1Recommended

Automate CSV Import and Calculation Using Power Query and SUMIFS

Set up a reusable Excel template that uses Power Query to pull in new CSV data, combined with structured tables and SUMIFS formulas to automatically categorize and sum the measurements.

By utilizing Power Query, you can establish a permanent connection to your CSV file. When combined with Excel Tables and structured references, your summary formulas will automatically adjust to accommodate any amount of newly imported data without manual auditing.

Create a structured destination dashboard

Open a new workbook and set up a summary sheet. Define the categories you want to calculate (e.g., Subject, Layer) and prepare the layout where your automated totals will reside.

Import the CSV via Power Query

Navigate to the Data tab on the Excel ribbon, click 'Get Data' (or 'From Text/CSV'), and select your source CSV file containing the blueprint measurements. This will open the Power Query Editor.

Transform and load the data

In the Power Query Editor, verify that your data types are correct, especially setting the Measurement column to a decimal or whole number. Click 'Close & Load To...', choose 'Table', and select a dedicated source data sheet to load the records.

Apply SUMIFS for automated calculations

On your summary dashboard sheet, use the SUMIFS function referencing the newly loaded Power Query table (using structured references like Table1[Measurement]) to automatically calculate totals based on your predefined categories.

Refresh to update with new data

When you receive a new CSV blueprint file, simply replace the old CSV file in your computer's folder with the new one using the exact same file name. Open your template and click 'Refresh All' on the Data tab to instantly update all calculations.

Automation Tip: Using structured table references (e.g., Table1[Column Name]) instead of static cell ranges (e.g., A1:A100) ensures your formulas will automatically expand and cover all imported rows correctly.

Efficient Spreadsheet Automation

Automate Your Data Imports with WPS Spreadsheets

WPS Spreadsheets provides a powerful, seamless environment for importing CSV files, formatting structured tables, and utilizing advanced formulas like SUMIFS to automate your daily workflows.

1. Open WPS Spreadsheets: Launch WPS Office and create a new blank spreadsheet or open your existing calculation template.
2. Import the CSV file: Navigate to the Data tab, select 'Import Data', choose 'Import Data' again, and select your target CSV file.
3. Configure data mapping: Follow the Text Import Wizard to properly delimit your data by commas and format your columns accurately.
4. Apply table calculations: Highlight the imported data, format it as a Table, and set up your SUMIFS formulas on a separate dashboard sheet to automate the total calculations.

Fully compatible with Microsoft Excel formulas and CSV formatsLightweight application that runs smoothly on any deviceEasy-to-use Data Import wizards for seamless integrationBuilt-in automated templates to save time on data entry

microsoft office alternative - wps office

Frequently Asked Questions

Can I import multiple CSV files into the same template automatically?

Yes, you can use the 'From Folder' option in Power Query to combine multiple CSV files contained in a specific folder. When you add a new file to the folder and click 'Refresh All', it automatically appends the new data to your master table.

Why are my SUMIFS formulas returning zero after importing the CSV?

This typically occurs if the imported measurement numbers are accidentally stored as text. Check your Power Query transformation steps or use the 'Text to Columns' feature to ensure the measurement column is explicitly formatted as numerical values.

How do I update the data when I receive a new blueprint measurement CSV?

Save the newly received CSV over the old file using the exact same file name and folder location. Then, open your automated Excel template and click 'Refresh All' on the Data tab. The new data will instantly populate and recalculate.

What happens if the new CSV has extra columns?

If Power Query is set up dynamically, it may pull in the extra columns alongside your normal data. However, your existing SUMIFS formulas will continue to work correctly as long as the original column headers they reference remain unchanged.

Excel & Data Problems