Automate Excel Formatting and Combine Multiple Sheets with Power Query

Excel Data Cleanup Tutorial Plus How To Analyze Merged Data with Pivot Tables & Charts

Are you tired of the repetitive, time-consuming task of manually formatting Excel spreadsheets every month? In this tutorial, we'll guide you through the process of automating the formatting and merging of multiple sheets in Excel using Power Query. By the end of this tutorial, you'll be able to set up an automated workflow that saves you valuable time, reduces errors, and simplifies your data management.

Step 1: Open Excel and Prepare Your Data

First, create a folder where you will save your Excel reports that you want to consolidate, and save your reports in this new folder location. Note that your spreadsheets that get saved in this folder should have the same headings and formatting so that the formatting and transformation steps can be automatically applied to each new spreadsheet that is added to the folder. In our example, we have a monthly report with the month-end pay date and a list of employees' 401k elections.

Traditionally, you would need to perform various manual tasks such as:

• Deleting unwanted columns

• Reordering columns

• Adding calculated columns (like 401k deferral amount)

• Copying and pasting data into a master spreadsheet

This process can be tedious and error-prone, especially if you have to do it every month when a new report is delivered. But now, enters Power Query, a powerful tool in Excel that can help you automate these repetitive formatting tasks. With Power Query, you can create a workflow that automatically applies your desired formatting changes and combines multiple sheets. Here's how:

Step 2: Accessing Power Query

Start by closing your spreadsheet(s) (the one(s) you want to automate) and open a new blank workbook or a sheet where you want to receive your formatted data.

• Click on the "Data" tab in the Excel ribbon.

• Select "Get Data" and choose "From Folder."

Step 3: Connecting to the Data Folder

• In the "From Folder" dialog, navigate and select the folder where your reports are saved. Click "Open."

This step establishes a connection to the folder where your reports are stored.

Step 4: Combine and Transform Data

• You'll see a list of the files in the folder. To automate the process of combining and formatting, select "Combine" from the options.

• Choose "Combine and Transform Data" to proceed.

Step 5: Loading Your Data

• Power Query will automatically select the first file in the folder. This is usually what you want, but you can choose a different file if needed.

• The data from the selected file will load in a preview pane on the right side of the window.

• Click "OK" to continue.

Step 6: Using Power Query's Editor

You're now in Power Query's editor, where you can perform all the necessary formatting and data transformations.

• Begin by removing any columns you don't need. Simply right-click on the column header and select "Remove."

• Any changes you make in Power Query are recorded in the "Applied Steps" section in the right-hand panel. These steps will be automatically applied to future spreadsheets dropped into the same folder.

• To rearrange columns, drag and drop them where you want them. Power Query records this action as a transformation step.

• You can also adjust the data types of your columns by clicking on the icon next to the column header. For example, set columns containing currency values to the "Currency" data type.

• If you need to create calculated columns, go to the "Add Column" tab and choose "Custom Column." Here, you can define your calculations using Excel formulas. For instance, you can calculate the 401k deferral amount based on other columns.

Step 7: Load Your Data Back Into Excel

• Once you've made all the necessary transformations, head back to the "Home" tab in Power Query.

• Click "Close & Load" to load the data back into Excel.

Step 8: Analyze Your Data

Your data is now in a properly formatted and consolidated state. You can create pivot tables, charts, or perform any data analysis you need.

Automate for Future Reports

The beauty of this process is that it's not a one-time deal. Whenever a new report is saved to the same folder, simply go to the "Data" tab in your spreadsheet, click "Refresh," and Power Query will automatically apply your formatting changes to the new data.

By automating your Excel formatting with Power Query, you've streamlined your data cleanup workflow, saved time, and reduced the risk of errors. Say goodbye to repetitive, manual tasks, and say hello to a more efficient and accurate way of managing your data. Click here to see how to automate reports using Office Scripts. Watch my YouTube Channel for additional helpful tutorials.