How to Create Excel Reports with Power Query and Power BI

For many teams, month-end reporting follows the same routine: open several files, copy the latest figures, check formulas and rebuild charts. Next month, the work starts again. A better reporting process allows new data to move through the same steps, so the team can spend more time understanding results.

This guide takes you from raw data to a management report. A small sales example shows how to calculate useful figures and explain what they mean. The Excel menu steps refer mainly to Excel for Windows; some features and menu names vary by version and platform.

Start with the decision your report needs to support

Before choosing a template, ask yourself: "What should someone be able to decide after reading this report?"

A sales report might help a manager decide which branch needs a cost review. A project report might show which tasks could miss their deadlines. A staff-hours report might help the team plan next month's workload.

Then agree on the reporting period, departments, units and definitions. For example, does "revenue" mean sales before discounts or after discounts? Does it include VAT? Two reports can use the same label and still measure different things.

Choose a few figures that support the decision. In a sales report, revenue, gross profit and gross margin work well together. Revenue shows sales value. Gross profit shows what remains after the cost of goods sold. Gross margin expresses that profit as a percentage of revenue.

Choose Excel or Power BI based on how your team works

Excel is useful for entering data, changing assumptions, checking individual records and preparing files for printing. Power BI is useful for exploring connected data and publishing interactive reports through an online service.

You can use both tools. For example, keep data entry and detailed checks in Excel, then use Power BI for analysis and distribution. Agree on which file is the data source and who is responsible for correcting it.

Build a clean source table before creating charts

First, define what one row represents. It could be one sale, one expense or one employee's hours for one day. Keep that meaning consistent throughout the table.

Use these rules for your source data:

• Use one header row, with a clear, unique name for each column.
• Store dates as real date values and amounts as numbers. Use cell formatting to display currency.
• Keep IDs, branch names and amounts in separate columns. Store IDs as text when leading zeros matter.
• Use consistent names for categories and branches.
• Keep totals outside the source records so they are not added a second time.
• Avoid merged cells in the source table. Use them only where appropriate on a separate presentation sheet.

For Thai business data, check whether dates use Buddhist Era or Common Era years. Also check the order of day and month when importing text. Changing a cell's display format will not repair a date that was read incorrectly.

A sales example to use throughout this guide

The following figures are fictional. Each row represents one sale. Revenue is sales after discounts, excluding VAT. Cost is the cost of goods sold, so Profit means gross profit. It does not include deductions for administration or other operating expenses.

All amounts are in Thai baht, shown as THB in the report. Dates use Common Era years and the year-month-day format. Store them as actual dates in Excel.

Select the data, choose Insert > Table, confirm the headers and name the table Sales. An Excel Table lets you refer to columns by name and can expand as you add records. Before updating the report, check that every new row is inside the table.

Check exported data for double counting

An export from a planning system may show the same activity on several rows, split by day or department. If an eight-hour activity appears under two departments, adding both rows could produce 16 hours.

Check the activity ID and the allocation rule before calculating totals. To count activities, you may need to count unique IDs. To report hours by department, use the hours actually allocated to each department.

Repeated IDs are not always errors. An invoice can contain several product lines with the same invoice number. Decide whether you are counting invoices or product lines before removing any records.

Choose an Excel template that will still work next month

Excel report templates provide a useful starting point for expense reports, budgets, timesheets, project tracking and inventory records. Test a template with a small set of your own data before using it for regular reporting.

Check four things:

1. Does it include the fields you need, with input cells clearly separated from formulas?
2. Do its formulas include new records when you add more rows?
3. Are the period and units right for your work, such as months, hours or baht?
4. Can your team use its features in the Excel version they have?

Keep a clean master template separate from completed reports. In desktop Excel, a template without macros can be saved as an .xltx file. Include a short instruction sheet explaining what to enter and how to update the report.

For analysis across several months, keep historical records in a table with a consistent structure. Filter the reporting month in the summary. This makes comparisons easier than searching through separate monthly sheets.

Use Power Query to repeat data preparation

Power Query connects to data and records the steps used to prepare it. When new source files arrive, you can run those steps again instead of copying and cleaning everything manually.

Suppose each branch sends a sales file every month. In Excel for Windows, a typical process is:

1. Put the source files in one folder, using consistent column names.
2. Choose Data > Get Data > From File > From Folder.
3. Exclude temporary files and anything that is not source data.
4. Combine the files, then check headers and data types.
5. Standardise names, remove unwanted spaces and handle missing or repeated records using agreed rules.
6. Load the results into a worksheet table or the Data Model, which stores data for analysis.

Keep generated reports in a different folder from the input files. Otherwise, the next update may read its own previous output and count data twice.

Power Query is available across Excel for Windows, Mac and the web, but connectors and editing features differ. Check the features available to your team before designing the workflow.

Summarise the data with formulas and PivotTables

Use formulas when a figure needs to appear in a fixed place on a summary sheet. Use a PivotTable, an interactive summary table, when you want to group data by branch, month or another category. Both can be part of the same report.

Calculate monthly revenue and gross profit

In the Profit column of Sales, enter this formula:

=[@Revenue]-[@Cost]

On a summary sheet, set B1 to the first day of the reporting month and B2 to the first day of the following month. For September 2026, use 1 September 2026 and 1 October 2026. Both cells must contain real dates.

In B4, calculate revenue for the selected month:

=SUMIFS(
  Sales[Revenue],
  Sales[Date],">="&$B$1,
  Sales[Date],"<"&$B$2
)

This includes records from the first day of September up to, but excluding, the first day of October. It also includes records with a time value, such as 30 September at 18:00.

In B5, use the same formula but replace Sales[Revenue] with Sales[Profit]. Then calculate gross margin in B6:

=IF(B4=0,"",B5/B4)

Format B6 as a percentage. The formula leaves the result blank when revenue is zero, because the margin cannot be calculated in that case. A blank result has a different meaning from a zero margin.

Table and column names must match your workbook. Some regional settings use semicolons instead of commas between formula arguments.

Create a PivotTable by branch

Select a cell in Sales and choose Insert > PivotTable. Place Branch in Rows, then Revenue and Profit in Values. Check that both values use Sum.

If Excel shows Count instead of Sum, inspect the source column for numbers stored as text. For monthly comparisons, group dates by both year and month so the same month in different years is not combined.

You can add a Slicer, a set of clickable filter buttons, to let readers choose a branch. After adding records, refresh the PivotTable and check its totals again.

Design an Excel dashboard that explains the results

An Excel dashboard brings key figures, charts and tables together on a summary sheet. Clearly show the report name, period, units and data coverage.

Put the main figures at the top. Use a line chart for monthly trends and a bar chart to compare branches. Keep a detail table available for checking unusual results. Use colour consistently, with labels that remain clear without relying on colour alone.

Our sales example produces this summary:

Sales increased, but gross profit fell. That gives the manager a reason to review costs, discounts and the mix of products sold. These figures show where to investigate; they do not establish the cause.

Calculate the overall margin by dividing total gross profit by total revenue. A simple average of individual sales margins gives small and large sales equal weight and can produce a misleading result.

Also distinguish percentages from percentage points. A margin moving from 35.0% to 27.7% falls by about 7.3 percentage points.

Add a short observation beneath the figures, such as: "September revenue increased, but gross profit decreased. Review the cost of goods sold by branch." This helps the reader choose a useful next step.

Compare actual results with budget and forecast

A management report should clearly distinguish three views:

• Actual shows what has already happened.
• Budget is the approved plan used as a comparison.
• Forecast is the latest estimate of what may happen, based on current information and assumptions.

Suppose September's revenue budget was THB 240,000 and actual revenue was THB 220,000. If variance means actual minus budget, the variance is -THB 20,000, or about -8.3% of budget.

Revenue is therefore 10.0% higher than the previous month, while still 8.3% below budget. Both statements are correct because they use different comparisons. Label the comparison beside the figure.

Interpret the result according to the measure. A positive expense variance may mean overspending. A lower expense figure may reflect savings or an activity that has not yet taken place. If the budget is zero, show the amount and explain that a percentage variance cannot be calculated from that base.

When updating a forecast, identify which months contain actual results and which contain estimates. Record important assumptions, such as sales volume, selling price or unit cost. Keep the approved budget rather than overwriting it with the latest forecast.

For cash planning, include collections from customers, payments to suppliers and relevant inventory information. A credit sale can create profit before the cash is received. Month-end cash balances are amounts at specific dates; adding several closing balances does not give the cash balance for the whole period.

Give follow-up actions an owner and a due date. For example, ask the branch manager to review higher costs before the next reporting meeting.

Build a Power BI report from your Excel data

Once your source data is reliable, you can use Power BI Desktop to build an interactive report from it.

For the Sales table, follow these steps:

1. Open Power BI Desktop and choose Get Data > Excel workbook.
2. Select the file, then choose Sales in Navigator.
3. Choose Transform Data to check data types and prepare the records.
4. Choose Close & Apply to load the data into the model.
5. Create measures, then add charts, key figures and filters to the report page.
6. Compare the totals with Excel, save the .pbix file and publish to the Power BI service if your access allows it.

Importing the workbook brings in data. You still need to build the report's visuals and calculations in Power BI; existing Excel charts and worksheet layouts do not automatically become the same Power BI report.

Use measures that match your Excel calculations

A measure is a calculation that responds to report filters. DAX is the formula language used to create these calculations in Power BI.

Create the following as three separate measures:

Total Revenue = SUM(Sales[Revenue])

Gross Profit = SUM(Sales[Profit])

Gross Margin = DIVIDE([Gross Profit], [Total Revenue])

Format Gross Margin as a percentage. DIVIDE returns a blank when the denominator is zero unless you specify another result. When a reader selects a branch or month, the measures calculate values for that selection.

As the report grows, separate sales records from tables describing branches, products and dates. Connect them using IDs. The category table should have a unique ID for each item; the sales table can contain many records with that ID. This structure is called a star schema.

For time comparisons, use a date table with one row for every date in the required period, without gaps or duplicates. Check its relationship to the sales table before creating month or year comparisons.

Plan access before sharing the report

Power BI Desktop is free to use. Publishing and sharing through the Power BI service involve separate access and licensing rules. Sharing usually requires Power BI Pro or Premium Per User. Viewer requirements depend on the workspace licence and capacity.

Some eligible capacities, such as Microsoft Fabric F64 or larger, support viewing by users with a free licence. Reports in a Premium Per User workspace generally require viewers to have Premium Per User licences too. Check the chosen workspace and your team's licences before promising access to everyone.

Make data updates part of the reporting process

Opening a workbook or recalculating formulas does not necessarily retrieve new source data. Refresh the queries and connections that supply the report.

In Excel, first refresh the Power Query results and wait for them to load. Then refresh any PivotTables that depend on those results. Refreshing the preview in the Power Query editor alone does not update the worksheet or Data Model.

In Power BI, the semantic model, meaning the data model used by the report, needs access to its sources and a successful refresh. Local or network files may require an on-premises data gateway, a connection service that must be running and able to reach the source.

A file in a locally synced OneDrive folder can still be a local source if the connection uses a path such as C:\Data\Sales.xlsx. Using a cloud connector is a different connection method.

File synchronisation and data refresh are also different. A Power BI model refresh does not automatically make the original Excel workbook retrieve external data and save itself. If Excel first combines data from another system, update that workbook before refreshing the report that reads it.

For new workflows in 2026, use current import methods. The older workflow of uploading a local Excel workbook to a workspace and refreshing that workbook there is no longer supported. Use Power BI Desktop to publish a semantic model, or a supported OneDrive or SharePoint import method, with the appropriate source access.

Show two separate details on the report: the date covered by the data and the time of the last successful refresh. TODAY or NOW only displays a date or time; it does not prove that new records arrived.

Check refresh history and confirm that the latest expected records are present. Assign someone to follow up when a scheduled refresh fails.

Check the report before sending it

Use this checklist whenever the source, calculations or report scope changes:

• Revenue, costs and profit match the source for the same period.
• Record counts or unique-ID counts match what you intend to measure.
• Missing values are distinguished from zeros.
• Month, year, branch and status filters are set correctly.
• Percentages use the right base and consistent definitions.
• Data coverage and refresh information reflect the actual update status.
• Charts and tables are readable on the screen or in the exported file.

If totals do not match, check dates, filters, repeated records and data types before changing formulas. The error may have started during data preparation.

Frequently asked questions about Excel reports

For your next report, improve the step that takes the most time or causes the most errors. Use Power Query if file preparation is repetitive. Fix the source table and measure definitions if totals are unreliable. Once the data and calculations are dependable, you can build clearer dashboards and expand the process with Power BI.

Updated 5 October 2026

Read next

• Report guideExecutive Review
• ArticleWhat a monthly management report should contain, and in what order
• ArticleReading a budget variance: favourable, unfavourable, and what to check next
• Power QueryPower Query Studio
• DownloadDownload 01 Executive Review
• ArticleCopilot in Microsoft Excel for financial reporting: what it does, what it needs, what to check

Figures in this article are fictional, used to show the method. They do not come from the report guides' workbooks.